0

I receive daily emails with a CSV file and have code that automatically uploads the file to my server and then creates a MySQL table with column names based on the data of Row 1 in the CSV file. Following the creation of the table, the script is meant to import the 100 or so next rows of records into the table with column "Survey ID" being a unique value that also serves as my PRIMARY KEY. All other columns besides "Survey ID" are text(999) with no other columns acting as primary, unique, auto-increment, etc.

The problem now is that every time the records get imported, the script throws me the following error:

ERROR: There was an error writing row 2 to the table. [Duplicate entry '2147483647' for key 'PRIMARY']

The first record gets imported fine, but all following records fail to import and quote the same error and the same '2147483647' key which is the Survey ID of the first record. Each record has a unique 'Survey ID' number on file and I can see that the 'INSERT' for each record shows a unique ID, so the only reason why the error comes up must be from the table / column configuration.

Since everything is automated by the script, I run the following queries once the column headers are created to assign 'Survey ID' as primary and set up the structure as I need it.

$sql = "ALTER TABLE `" . $temp_table . "` CHANGE `Survey ID` `Survey ID` INT(11) NULL";
if(!$result = $db->query($sql)){ die('Error while changing Survey ID to INTEGER [' . $db->error . ']'); }

$sql = "ALTER TABLE `" . $temp_table . "` ADD PRIMARY KEY(`Survey ID`)";
if(!$result = $db->query($sql)){ die('Error setting Survey ID as PRIMARY [' . $db->error . ']'); }

$sql = "ALTER TABLE `". $temp_table . "` CHANGE `Survey ID` `Survey ID` INT(11) NOT NULL AUTO_INCREMENT";
if(!$result = $db->query($sql)){ die('Error while changing Survey ID to INTEGER [' . $db->error . ']'); }

$sql = "ALTER TABLE `". $temp_table . "` ADD UNIQUE(`Survey ID`);";
if(!$result = $db->query($sql)){ die('Error setting Survey ID as UNIQUE [' . $db->error . ']'); }

I spent a few hours playing around with NULL, NOT NULL, AUTO_INCREMENT, UNIQUE, PRIMARY, etc. but all to no avail, the error persists.

Appreciate if anyone has any guidance, thank you.

Armitage2k
  • 1,164
  • 2
  • 27
  • 59
  • 3
    `2147483647` is the max value of a [signed int](https://dev.mysql.com/doc/refman/8.0/en/integer-types.html). You might have reached the maximum entries – Cid Oct 04 '19 at 06:52
  • I think it can be useful for you: https://stackoverflow.com/questions/14393672/why-cant-i-insert-10-digits-when-my-column-is-int10 – hNczy Oct 04 '19 at 06:53
  • 1
    use `BIGINT` instead of `INT`, this might help you – Cid Oct 04 '19 at 06:56

1 Answers1

1

You reached the max int value. You can use bigint instead:

$sql = "ALTER TABLE `" . $temp_table . "` MODIFY COLUMN `Survey ID` BIGINT PRIMARY KEY NOT NULL";
if(!$result = $db->query($sql)){ die('Error while changing Survey ID to BIGINT [' . $db->error . ']'); }
johannesdz
  • 423
  • 1
  • 4
  • 11