Invalid INSERT statement during schema sync

[Darren Hennessy]

Hi,

I have noticed an issue where invalid SQL is generated for for the INSERT statement when a schema synchronization script is created. This occurs in the situation where the table is being re-created and new columns have been added (It may happen on other occasions as well but I haven't noticed).

In this situation the column list in the INSERT statement contains the columns from the 'new' re-created table, instead of the original table. This results in an incorrect number of columns supplied in the SELECT part of the query and the following error:

Error: near line 23: 1 values for 2 columns

The destination sync CREATE TABLE statement is:

-- WARNING: Re-creating the table 

ALTER TABLE alarm_lockout_link_sets RENAME TO alarm_lockout_link_sets_tmp;

CREATE TABLE alarm_lockout_link_sets (link_set_id INTEGER NOT NULL,

                                      link_set    TEXT NOT NULL,

                                      CONSTRAINT pk PRIMARY KEY (link_set_id));

INSERT INTO alarm_lockout_link_sets (link_set_id, link_set) SELECT link_set FROM alarm_lockout_link_sets_tmp;

DROP TABLE alarm_lockout_link_sets_tmp;

In the above statement the link_set_id column should not be included in the column list.

The original source CREATE TABLE statement is:

CREATE TABLE alarm_lockout_link_sets(

  link_set_id INTEGER PRIMARY KEY NOT NULL,

  link_set    TEXT NOT NULL

);

The original destination CREATE TABLE statement is:

CREATE TABLE alarm_lockout_link_sets(

  link_set TEXT PRIMARY KEY NOT NULL

);

The databases are SQLite. I am also working with MySQL but I have not tested that yet.

I can also see that the sync would fail (assuming the above error was already fixed) with an error like this:

Error: near line 1: table_name.some_field may not be NULL

if a column is added and defined like this:

some_field INTEGER NOT NULL

and no DEFAULT was added. Since it is not really possible for you to guess a sensible default value it might be a nice feature to present a warning during script creation so the user can go back and add a default value.

[DB Solo Admin]

This should be fixed now. You can find the latest version here

dbsolo.com/preview/

thanks

marko

[Darren Hennessy]

Thanks I'll give it a try.

Darren