Bulk Import missing lead_id

[Cyberwolf2]

I'm having issues with bulk import. It looks like I screwed something along the way. The database is rejecting the insert commands because the 'lead_id' field is '' instead of having a iterative number.

Here is the output of
perl VICIDIAL_IN_new_leads_file.pl --new-list-for-each-file --new-list-active=Y --new-list-campaign=testerino --test --debug

Code: Select all

||INSERT INTO vicidial_list (lead_id,entry_date,modify_date,status,user,vendor_lead_code,source_id,list_id,gmt_offset_now,called_since_last_reset,phone_code,phone_number,title,first_name,middle_initial,last_name,address1,address2,address3,city,state,province,postal_code,country_code,gender,date_of_birth,alt_phone,email,security_phrase,comments,called_count,last_local_call_time,rank,owner)

values('',"2024-10-02 13:25:40","","NEW","","38007590","REDACTED","202410021","0","N","1","REDACTED","","Ricardo","","REDACTED","","","","Colorado Springs","CO","","","USA","male","","","","","REDACTED,"0",'2008-01-01 00:00:00',"0","");


values('' << here is the issue, the lead_id is blank and mariadb complains. Instead of being a serial integer it's blank.

What controls a new 'lead_id' ?? Is this normal? Could it be a mysql config issue?

Fresh database, was messing with my.conf prior to breaking....

[Cyberwolf2]

Turn out is is a normal function and it was my mysql config that screwed it. restored earlier version and now working on optimizing config.


For the sake of documenting this, here is what I strongly believe the culprit:

sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

That was to optimize a Wordpress site, just commenting it out or changing it to:

sql-mode = ""

fixed it

[carpenox]

sql_mode="NO_ENGINE_SUBSTITUTION" is what it should be

[williamconley]

Also possible:

Code: Select all

mysqldump asterisk vicidial_list | grep increment -i

Should result in:

Code: Select all

  `lead_id` int(9) unsigned NOT NULL AUTO_INCREMENT,
) ENGINE=MyISAM AUTO_INCREMENT=12792 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


If auto-increment is off, but the lead_id field is still set to primary index, it will reject the second blank value and all those after.

A restore fixes this, since the table structure is stored in the dump.

[Cyberwolf2]

Thank you both, it was a bunch of unneeded options I promise.

sql_mode="NO_ENGINE_SUBSTITUTION" fixed it

William, I was too impatient and just refreshed the database and went ham, but that is awesome. I'll keep that in mind.

[williamconley]

hitting the problem with a sledgehammer is often a simple solution and there's NO reason not to use it ... unless the problem keeps cropping up.

[carpenox]

glad its working jose