SCM

Forum: bugs

Monitor Forum | Start New Thread Start New Thread
RE: Missing START WITH value for serial's [ reply ]
By: joe speigle on 2007-12-11 12:33
[forum:1002934]
thank you for the excellent informative message. I have made the following changes..... (I can now login via CVS but do not have write privileges to the repository despite having set my appropriate role). Here is the diff for the change.




[joesp@localhost mysql2pgsql]$ diff mysql2pgsql.perl ../new/mysql2pgsql.perl
66a67
> my $auto_increment_seq= ''; # so we can easily substitute it if we need a default value
377c378,387
< s/AUTO_INCREMENT=\d+//i; # possible AUTO_INCREMENT starting index, it is used in mysql 5.0.26, not sure since which version
---
> # possible AUTO_INCREMENT starting index, it is used in mysql 5.0.26, not sure since which version
> if (/AUTO_INCREMENT=(\d+)/i) {
> # should take < ---- ) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
> # and should ouput ---> CREATE SEQUENCE "rhm_host_info_id_seq" START WITH 16;
> my $start_value = $1;
> print $auto_increment_seq . "--\n";
> # print $pre_create_sql . "--\n";
> $pre_create_sql =~ s/(CREATE SEQUENCE $auto_increment_seq )/$1 START WITH $start_value /;
> }
> s/AUTO_INCREMENT=\d+//i;
440a451
> $auto_increment_seq="";
476c487,488
< $pre_create_sql.= "CREATE SEQUENCE $seq \;\n\n";
---
> $pre_create_sql.= "CREATE SEQUENCE $seq \;\n\n";
> $auto_increment_seq = $seq ; # save in case we have the AUTO_INCREMENT=16 to default to
492c504,505
< $pre_create_sql.= "CREATE SEQUENCE $seq \;\n\n";
---
> $auto_increment_seq= $seq ; # save in case we have the AUTO_INCREMENT=16 to default to
> $pre_create_sql.= "CREATE SEQUENCE $seq \;\n\n";
845c858,859
<
---
> $auto_increment_seq = "";
>
[joesp@localhost mysql2pgsql]$ diff test.sql ../new/test.sql
6a7,24
> -- test for setting
> -- auto_increment to the start value for the sequence
> -- which is created
> CREATE TABLE `rhm_host_info` (
> `id` smallint(5) unsigned NOT NULL auto_increment,
> `hostname` varchar(60) NOT NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
>
> -- test for setting
> -- auto_increment to the start value for the sequence
> -- which is created ***using a numeric argument****
> CREATE TABLE `rhm_host_info` (
> `id` numeric(1,0) unsigned NOT NULL auto_increment,
> `hostname` varchar(60) NOT NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
>
[joesp@localhost mysql2pgsql]$

Missing START WITH value for serial's [ reply ]
By: Dave Paper on 2007-11-27 20:50
[forum:1002873]
Greetings,

Newbie migrating from mysql to postgres, and the mysql2pgsql perl script is awesome, however, I think I found a bug.

mysql dump of a table:

DROP TABLE IF EXISTS `rhm_host_info`;

CREATE TABLE `rhm_host_info` ( `id` smallint(5) unsigned NOT NULL auto_increment,
`hostname` varchar(60) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

Note the AUTO_INCREMENT=16 towards the end of the line.

Here is the converted output from mysql2pgsql:

DROP TABLE "rhm_host_info" CASCADE\g
DROP SEQUENCE "rhm_host_info_id_seq" CASCADE ;

CREATE SEQUENCE "rhm_host_info_id_seq"

CREATE TABLE "rhm_host_info" (
"id" integer DEFAULT nextval('"rhm_host_info_id_seq"') NOT NULL,
"hostname" varchar(60) NOT NULL,
primary key ("id")
) ;

The table gets created okay, but there's no indication at which value to start the nextval incrementing. It should look something like this:

CREATE SEQUENCE "rhm_host_info_id_seq" START WITH 16;

The other two keypair values (ENGINE= and DEFAULT CHARSET=) may be important too, but If they are, I haven't stumbled on that yet.

Ya'll rock!


Powered By FusionForge