Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #450 > unrolled thread

migration mysql to pgsql?

Back to article view | Back to comp.databases.postgresql

Contents

  migration mysql to pgsql? Magnus Warker <warker@mailinator.com> - 2013-06-05 08:46 +0200
    Re: migration mysql to pgsql? Harry Tuttle <SOZRBLNTLEEE@spammotel.com> - 2013-06-05 10:09 +0200
      Re: migration mysql to pgsql? Magnus Warker <warker@mailinator.com> - 2013-06-05 11:43 +0200
        Re: migration mysql to pgsql? Harry Tuttle <SOZRBLNTLEEE@spammotel.com> - 2013-06-05 11:59 +0200
          Re: migration mysql to pgsql? Magnus Warker <warker@mailinator.com> - 2013-06-05 13:06 +0200
            Re: migration mysql to pgsql? Robert Klemme <shortcutter@googlemail.com> - 2013-06-05 21:30 +0200
        Re: migration mysql to pgsql? Stefan+Usenet@Froehlich.Priv.at (Stefan Froehlich) - 2013-06-05 10:59 +0000
    Re: migration mysql to pgsql? Walter Hurry <walterhurry@lavabit.com> - 2013-06-07 21:52 +0000
      Re: migration mysql to pgsql? gargoyle60 <gargoyle60@example.invalid> - 2013-06-08 09:12 +0100
    Re: migration mysql to pgsql? johannes falcone <visphatesjava@gmail.com> - 2013-06-18 12:03 -0700

#450 — migration mysql to pgsql?

Hi,

I am planning to migrate an existing mysql (innodb) database to pgsql.

Since the database is already in production, I would like to 
successively prepare the migration by making small adaptations step by 
step so that the single downtimes can be kept short.

First of all I wanted to adapt the usage of table names, because I 
already know some names used in the mysql database that would case 
problems in pgsql. My idea is to use quotation marks for table names. 
Then, I could achieve two goals at once: a) avoid conflicts with key 
words, and b) preserve upper and lower case letters, which I use in 
mysql and which is not supported in pgsql without quotation marks. Would 
you agree that this would be a reasonable first step?

However, the adaptation of the mysql db to use qoted table names already 
is a problem, because mysql does not seem to like it.
Does someone have an idea how to deal with this?

I also noticed that there are many differences with the date and time 
column types.

Does someone have experience with migrating from mysql to pgsql?

To avoid unnecessary discussions: I already used both systems for some 
years and I know why I want to migrate.

Thank you!
Magnus

-- 
This is Unix-Land. In quiet nights, you can hear the Windows machines 
reboot.

[toc] | [next] | [standalone]

#451

Magnus Warker, 05.06.2013 08:46:
> First of all I wanted to adapt the usage of table names, because I
> already know some names used in the mysql database that would case
> problems in pgsql. My idea is to use quotation marks for table names.
> Then, I could achieve two goals at once: a) avoid conflicts with key
> words, and b) preserve upper and lower case letters, which I use in
> mysql and which is not supported in pgsql without quotation marks.
> Would you agree that this would be a reasonable first step?
>
> However, the adaptation of the mysql db to use qoted table names
> already is a problem, because mysql does not seem to like it. Does
> someone have an idea how to deal with this?

MySQL does support standard ANSI quoting (using " instead of the dreaded `) if configured correctly.

Another good approach would be to change MySQL to be more ANSI compliant by using the following sql_mode options:

   ANSI
   NO_BACKSLASH_ESCAPES
   STRICT_ALL_TABLES
   NO_AUTO_VALUE_ON_ZERO
   NO_ZERO_DATE
   NO_ZERO_IN_DATE
   PIPES_AS_CONCAT
   ONLY_FULL_GROUP_BY
   ERROR_FOR_DIVISION_BY_ZERO

But I would take the opportunity and get rid of quoted identifiers alltogether if you are at it.
In my experience they don't really add any advantages but generate problems in various tools.

[toc] | [prev] | [next] | [standalone]

#452

On 06/05/2013 10:09 AM, Harry Tuttle wrote:
> Magnus Warker, 05.06.2013 08:46:

> MySQL does support standard ANSI quoting (using " instead of the dreaded
> `) if configured correctly.

Ok, this must be the ANSI_QUOTES mode. Thanks!

> Another good approach would be to change MySQL to be more ANSI compliant
> by using the following sql_mode options:
>
> ANSI
> NO_BACKSLASH_ESCAPES
> STRICT_ALL_TABLES
> NO_AUTO_VALUE_ON_ZERO
> NO_ZERO_DATE
> NO_ZERO_IN_DATE
> PIPES_AS_CONCAT
> ONLY_FULL_GROUP_BY
> ERROR_FOR_DIVISION_BY_ZERO

ANSI means standard and this is always an advantage, but does pgsql use 
all these standards by default?

> But I would take the opportunity and get rid of quoted identifiers
> alltogether if you are at it.

Well, I never used them but I acutally tend to begin using them (see below).

> In my experience they don't really add any advantages but generate
> problems in various tools.

However, I often had problems with reserved words in the past, e. g. 
with identifiers needed to build an application-level user management 
("User", "Group", ...). I always had to use some unnatural words 
instead, and using unnatural words in data models is something that one 
should avoid absolutely in my opinion. But this is only a secondary reason.

The main reason for thinking about quoting is that pgsql does not 
support letter cases with unquoted identifiers. This would be a great 
loss for me, since letter cases also contain information when using 
identifiers that are composed of several nouns.

That you see disadvantages with quotes conserns me, but I do see 
advantages with them...

Magnus

-- 
This is Unix-Land. In quiet nights, you can hear the Windows machines 
reboot.

[toc] | [prev] | [next] | [standalone]

#453

Magnus Warker, 05.06.2013 11:43:
>> ANSI
>>NO_BACKSLASH_ESCAPES
>>STRICT_ALL_TABLES
>>NO_AUTO_VALUE_ON_ZERO
>> NO_ZERO_DATE
>>NO_ZERO_IN_DATE
>>PIPES_AS_CONCAT
>>ONLY_FULL_GROUP_BY
>> ERROR_FOR_DIVISION_BY_ZERO
>
> ANSI means standard and this is always an advantage, but does pgsql
> use all these standards by default?

Yes.

Actually except for "NO_BACKSLASH_ESCAPES" there is no way to configure Postgres to behave differently.
You can never make it accept '2013-02-31' or '0000-00-00' as a date or run "select 1/0" without an error.

> However, I often had problems with reserved words in the past, e. g.
> with identifiers needed to build an application-level user management
> ("User", "Group", ...).

It helps if you always use plural (users, groups), but personally I don't like that either.

I never had problems finding good names that are not keywords (user_profile, group_definition).


> The main reason for thinking about quoting is that pgsql does not
> support letter cases with unquoted identifiers. This would be a great
> loss for me, since letter cases also contain information when using
> identifiers that are composed of several nouns.

Most people simply settle for underscores (more_than_one_word).

But I guess it depends on where you come from.
Using underscores and non-quoted identifiers is pretty much standard in the Oracle and Postgres world (DB2 and Informix as well, as far as I can tell).

MySQL (and SQL Server) users differ here as those systems are non-standard when it comes to case-preserving, case-sensitiv object names (at least in the default installation).

The problem with MySQL's upper/lowercase handling is, that it is not consistent across platforms and installations.

The statements:

   create table foo (id integer);
   select * from FOO;

might or might not run on a specific MySQL installation (depending on the filesystem, the default storage engine and the configuration of the storage engine)

They will *always* run on any Postgres (or Oracle) system no matter how they were configured or which operating system they use.


> That you see disadvantages with quotes conserns me, but I do see
> advantages with them...

I work with a variety of tools (and DBMS) and I have yet to see a tool that does not eventually have one or two problems with quoted identifiers.

And last but not least I simply hate typing the double quotes ;)

[toc] | [prev] | [next] | [standalone]

#455

> And last but not least I simply hate typing the double quotes ;)

Me, too! :-)

Magnus

-- 
This is Unix-Land. In quiet nights, you can hear the Windows machines 
reboot.

[toc] | [prev] | [next] | [standalone]

#456

On 06/05/2013 01:06 PM, Magnus Warker wrote:
>> And last but not least I simply hate typing the double quotes ;)

;-)

I agree to Harry: here MySQL is really the odd one out.  And you have 
less trouble using just unquoted names and settle with underscore_names.

Kind regards

	robert

[toc] | [prev] | [next] | [standalone]

#454

On Wed, 05 Jun 2013 11:43:40 Magnus Warker wrote:
> The main reason for thinking about quoting is that pgsql does not support
> letter cases with unquoted identifiers. This would be a great loss for
> me, since letter cases also contain information when using identifiers
> that are composed of several nouns.

You can use CamelCase names with pgsql without any quotes as long as
you _never_ quote them. pgsql converts them into lowercase internally,
but that does not have to bother you.

This was the way I choose after coming from MySQL and facing the same
problems - and I'm feeling quite fine with that up to now.

Stefan 

-- 
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike

Für den anspruchsvollen Herren - tauchen mit Stefan!
(Sloganizer)

[toc] | [prev] | [next] | [standalone]

#461

On Wed, 05 Jun 2013 08:46:18 +0200, Magnus Warker wrote:

> Hi,
> 
> I am planning to migrate an existing mysql (innodb) database to pgsql.
> 
> Since the database is already in production, I would like to
> successively prepare the migration by making small adaptations step by
> step so that the single downtimes can be kept short.
> 
> First of all I wanted to adapt the usage of table names, because I
> already know some names used in the mysql database that would case
> problems in pgsql. My idea is to use quotation marks for table names.
> Then, I could achieve two goals at once: a) avoid conflicts with key
> words, and b) preserve upper and lower case letters, which I use in
> mysql and which is not supported in pgsql without quotation marks. Would
> you agree that this would be a reasonable first step?
> 
> However, the adaptation of the mysql db to use qoted table names already
> is a problem, because mysql does not seem to like it.
> Does someone have an idea how to deal with this?
> 
> I also noticed that there are many differences with the date and time
> column types.
> 
> Does someone have experience with migrating from mysql to pgsql?
> 
> To avoid unnecessary discussions: I already used both systems for some
> years and I know why I want to migrate.
> 
Bite the bullet. Use (unquoted) lowercase names with words separated by 
underscores. It will save a lot of hassle in the long run.

[toc] | [prev] | [next] | [standalone]

#462

On Fri, 7 Jun 2013 21:52:06 +0000 (UTC), Walter Hurry <walterhurry@lavabit.com> wrote:

>Bite the bullet. Use (unquoted) lowercase names with words separated by 
>underscores. It will save a lot of hassle in the long run.

Agreed, I faced the same conundrum and the hassle simply isn't worth it.

[toc] | [prev] | [next] | [standalone]

#467

there are tools to do that

goood man!!

you made right call

mysql and oracle must die!

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web