0

I am porting a MySQL database to PostgreSQL. So far I have managed to rebuild all the tables and columns and import the data. However, the auto incremented, unique id fields have not been converted properly. I used the pgAdmin GUI to correct one column in a table. I think I am probably not the first one converting from MySQL to Postgres so there probably is an robust and tested method to do this as an automated task. I would appreciate it if someone could provide such a small script.

The main reason, however, why I would like a tested solution for this is because I ran into an issue after adding a sequence and setting the default value for the column in question. The current value of the sequence was set correctly to 2069, the value of the last table entry. When I ran my program and added a row I got an exception which said that the unique value 2069 was already assigned. When I ran the program again it created the new row but the auto incremented id was 2071. It skipped 2070.

So now there are 3 possible sources of error:

  • Bug in PostgreSQL 9.5
  • Bug in the Java JDBC connector
  • I made a mistake with the auto increment field and sequence.

By using a tested solution I could eliminate the last possibility.

  • 1
    There's no obligation on the part of the Postgres sequence generator to avoid holes. It's intended to generate unused numbers sequentially and these might be discarded, unused, if there's a transaction it needs to back out of due to an error. – tadman Oct 24 '16 at 20:31
  • @tadman I am well aware of this. The problem is not the 'hole' it leaves but rather the unexpected behavior. The program did not insert the data the first time but threw an exception instead. I would like to investigate why this happened. I have more tables to correct and I want to make sure that this erratic behavior does not happen again in the future. –  Oct 24 '16 at 20:38
  • Does it increment by two each time? Perhaps your code is calling `nextval()` somehow before the insert? But like tadman said, skipped ids aren't going to hurt anything unless you don't handle things correctly. – Jay K Oct 24 '16 at 21:23
  • 2
    Obviously, something went wrong with `adding a sequence and setting the default value for the column`, but we have no chance to help because you did not show the code you used. – Erwin Brandstetter Oct 24 '16 at 21:37
  • @JayK No, it only skipped **and** through an exception because it used the current last value instead of the next one. As I have already stated, the exception is the problem, not the skipped index. I ran the program again without any modification and the data was inserted into the database. –  Oct 25 '16 at 21:35
  • @ErwinBrandstetter There was no code. I used the GUI from pgAdmin to make the changes. I edited the post to emphasize this. I was hoping that maybe someone else has observed this behavior and can point me to the error source or provide some instructions that have proven to migrate a database from MySQL to Postgres in a reliable, error-free manner. –  Oct 25 '16 at 21:48

1 Answers1

1

Old question but anyway. I have the same "issue" here.

I've migrated my database from MySQL to PostgreSQL with help of application dbMigration.NET (http://fishcodelib.com/DBMigration.htm).

After checking results I see that sometimes the sequences does not have the correct "last values". For example instead of id 2976 it would start with 1. Next value should be 2 in this case.

In my old database some rows were deleted, so if I (as same as you do nautical) save the application again postgresql is trying to save next value. If this id is "free" save is successful done. Maybe this is the same case for you. This is not a bug or something else.

As result ... the sequences have to set to the correct, current last value of the table. Do this with following query for each sequence:

SELECT SETVAL('testtable_id_seq', (select max(id) from testtable));

or, if sqeuencename is not known:

SELECT SETVAL(pg_get_serial_sequence('testtable', 'id'), (SELECT max(id) FROM testtable));

Hope I no not misunderstand your question. But maybe this a helpful for others with equal "issues".

Thomas
  • 81
  • 1
  • 4