Invalid INSERT statement during schema sync

20 views
Skip to first unread message

Darren Hennessy

unread,
May 25, 2021, 8:54:32 PMMay 25
to DB Solo
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

unread,
May 31, 2021, 8:05:10 AMMay 31
to DB Solo
This should be fixed now. You can find the latest version here


thanks
marko

Darren Hennessy

unread,
May 31, 2021, 11:48:30 PMMay 31
to DB Solo
Thanks I'll give it a try.

Darren

Reply all
Reply to author
Forward
0 new messages