Hi,
Odd error here and pulling my hair out, I'm processing a CSV file with some 37,800 plus records
All get inserted into MySQL except 5. The csv will change over time, modified and added too. I download from source once a week and insert records into DB.
There are 5 records in the current csv that produce an error "ER_PARSE_ERROR", same records every time.
I extracted those records and created just a csv of those 5, all 5 fail (expected).
First thought is must be something in the data, however if I dump out the msg.topic via node warn or a catch node. Nothing jumps out and if I run the SQL in the terminal monitor of MySQL (MySQL running in linux) the SQL is fine and inserts as expected.
CSV
partnumber,name,brand,shortdescription,lastmodified
534-202,"Throttle Position Kit for Electric Choke Carburetors",Holley,"Running an electric choke Holley carburetor and having trouble figuring out how to get that new electronic overdrive transmission to work? Holley has a solution for you.","2021-06-19 09:03:14"
84891,"Chevy Flat Top Pro-Billet Distributor",MSD,"Need blower/intake clearance? The Flat top is your answer. Must be used with an MSD 6, 7 or 8-series ignition.","2021-06-19 09:03:18"
C6TZ-96101767-OR,"Scott Drake 1966-77 Bronco Outer Rocker Panel - Weld Thru Primer","Scott Drake","1966-77 Bronco Outer Rocker Panel ? Weld Thru Primer","2021-05-04 14:02:44"
USA-BLUEKIT,"Scott Drake Bluetooth® Interface for Custom Autosound CD Controller Radios","Scott Drake","Bluetooth? Interface for Custom Autosound CD Controller Radios","2021-05-05 09:03:38"
Produces SQl (1 example)
START TRANSACTION;INSERT INTO vehicle_partnumbers (partnumber,name,ShortDescription,lastmodified) VALUES ('84891','Chevy Flat Top Pro-Billet Distributor','Need blower/intake clearance? The Flat top is your answer. Must be used with an MSD 6, 7 or 8-series ignition.','2021-06-19 09:03:18') ON DUPLICATE KEY UPDATE name='Chevy Flat Top Pro-Billet Distributor', ShortDescription='Need blower/intake clearance? The Flat top is your answer. Must be used with an MSD 6, 7 or 8-series ignition.', lastmodified='2021-06-19 09:03:18',partnumber_id=LAST_INSERT_ID(partnumber_id);SET @last_id_in_vehicle_partnumbers = LAST_INSERT_ID();INSERT INTO vehicle_brand (brand,lastmodified) VALUES ('MSD','2021-06-19 09:03:18') ON DUPLICATE KEY UPDATE lastmodified='2021-06-19 09:03:18', brand_id=LAST_INSERT_ID(brand_id);SET @last_id_in_vehicle_brand = LAST_INSERT_ID();INSERT IGNORE INTO partnumber_vehicle_brand_link (partnumber_id,brand_id) VALUES(@last_id_in_vehicle_partnumbers,@last_id_in_vehicle_brand);COMMIT;
Fails in MySql node but works directly in MySql.
Function creating SQL, remember some 37k plus records work, it's just the same 5 that don't
const mysql = global.get("mysql");
//load payload into data object
const data = msg.payload;
msg.topic ='';
//start transaction;
let insert = "START TRANSACTION;";
//insert part number
insert += "INSERT INTO vehicle_partnumbers (partnumber,name,ShortDescription,lastmodified) VALUES (" + mysql.escape(data.partnumber) + "," + mysql.escape(data.name) + ",";
insert += mysql.escape(data.shortdescription) + "," + mysql.escape(data.lastmodified) + ") ";
insert += "ON DUPLICATE KEY UPDATE name=" + mysql.escape(data.name) + ", ShortDescription=" + mysql.escape(data.shortdescription) + ", lastmodified=" + mysql.escape(data.lastmodified) + ",";
insert += "partnumber_id=LAST_INSERT_ID(partnumber_id);";
insert += "SET @last_id_in_vehicle_partnumbers = LAST_INSERT_ID();";
//check if we have brand with a value.
if ( data.hasOwnProperty('brand' ) && data.brand != null ){
//insert Brand
insert += "INSERT INTO vehicle_brand (brand,lastmodified) VALUES (" + mysql.escape(data.brand) + "," + mysql.escape(data.lastmodified) + ") ";
insert += "ON DUPLICATE KEY UPDATE lastmodified=" + mysql.escape(data.lastmodified) + ", brand_id=LAST_INSERT_ID(brand_id);";
insert += "SET @last_id_in_vehicle_brand = LAST_INSERT_ID();";
insert += "INSERT IGNORE INTO partnumber_vehicle_brand_link (partnumber_id,brand_id) VALUES(@last_id_in_vehicle_partnumbers,@last_id_in_vehicle_brand);";
}
//commit transaction
insert += "COMMIT;";
msg.topic = insert;
//node.warn(msg.topic);
return msg;
