1

I am using the code below from this stackoverflow answer to copy a row in a table and create a new row in the same table, but with the same data and a unique primary key. I am running the code using the mysql-connector-python module.

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1;
UPDATE tmptable_1 SET primarykey = NULL;
INSERT INTO table SELECT * FROM tmptable_1;
DROP TEMPORARY TABLE IF EXISTS tmptable_1;

This code runs on the production database, but gives the following error on my local test db.

IntegrityError: 1048 (23000): Column 'primaryKey' cannot be null

Local DB:

  • Version: MYSQL==5.6.47
  • Compiled For: osx10.15 (x86_64)
  • DB Client: mysql-connector-python==2.1.3

Production DB:

  • Version: MYSQL==5.6.40
  • Compiled For: Linux (x86_64)
  • DB Client: mysql-connector-python==2.1.3

I use MYSQL Workbench to export the schema and data from the production data base and import it into my local test database.

Is there some configuration setting for a MYSQL DB that is the problem? I am not sure what the cause is for the difference in database behaviour.

colio303
  • 81
  • 10

1 Answers1

0

Just drop that column from the INSERT side and you can insert into yourself:

INSERT INTO table (a, b, c) SELECT a, b, c FROM table;

Where you must specify the columns in exactly the same order, omitting the id one.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • This would definitely work, but the table I'm copying from has about 30 columns and is changing all the time. I'd rather figure out why the two databases are behaving differently when executing the same code. Writing an explicit INSERT statement would mean I'd have to change the code after every git pull, and make sure each stated column still existed in the table, and that any new table columns got added to the statement as well. – colio303 May 08 '20 at 02:12
  • Why is your schema "changing all the time?" That's a recipe for confusion if not disaster and sounds like you need to apply more rigorous database normalization to the structure to make it more adaptable. That being said, you can easily pull the schema in Python, enumerate the columns for this table, remove `id`, and auto-generate the required query. – tadman May 08 '20 at 03:09