Unable to run INSERT IGNORE INTO ... ON DUPLICATE KEY UPDATE, and then SHOW WARNINGS

I need to insert/update multiple records at one time (like upsert() allows), while also ignoring error messages (like insertOrIgnore() offers), and I want to be able to retrieve those error messages next, like through the mysql statementSHOW WARNINGS. At first I tried to use the already built-in methods Laravel offers. The upsert() and insertOrIgnore() methods looked promising, but seem to just barely miss the mark.

In the code, I'm trying to run the INSERT IGNORE INTO command and then in a subsequent line I'm trying to run the SHOW WARNINGS query:

$sql ="INSERT IGNORE INTO pestroutesdb.region(region_id,office_id,description,created,deleted,points,type,status) VALUES (NULL, 9999, 'Interesting Description!', '2021-01-11 00:00:00', NULL, NULL, '1', '0') ON DUPLICATE KEY UPDATE region_id=VALUES(region_id), office_id=VALUES(office_id), description=VALUES(description), created=VALUES(created), deleted=VALUES(deleted), points=VALUES(points), type=VALUES(type), status=VALUES(STATUS)";

$stmt = DB::connection('dataWarehouse')->statement($sql);

$warnings = DB::connection('dataWarehouse')->select('SHOW WARNINGS');

dd($stmt, $warnings);

So, the first two values in the VALUES array SHOULD cause two error messages, since null values are not acceptable for the region_id, and there is a foreign key constraint on the office_id field, and no offices have the ID of 9999.

When I execute the following code, I run into this error message:

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. (SQL: SHOW WARNINGS)

I'm not using any fancy PDO connection specifically or any other custom DB configuration, other than what Laravel uses built-in when using the DB facade. I'm not sure why I'm getting that error message, but it's confounded myself and two others on the team for most of the day.

Any help is appreciated. I've been racking my brain with this one for the entire day so far and I'm out of ideas at this point. Thank you.

guybrush_threepwood

5 years ago

Level 33

Hi @wesstep1315

Try:

$stmt = DB::connection('dataWarehouse')->getPdo()->exec($sql);

Wesstep1315

5 years ago

Level 4

Hello, @guybrush_threepwood! Thanks for reaching out.

Here's the code I tested:

$sql = "INSERT IGNORE INTO pestroutesdb.region(region_id,office_id,description,created,deleted,points,type,status) VALUES (NULL, 234, 'Interesting Description Numero dos!', '2021-01-11 00:00:00', NULL, NULL, '1', '0') ON DUPLICATE KEY UPDATE region_id=VALUES(region_id), office_id=VALUES(office_id), description=VALUES(description), created=VALUES(created), deleted=VALUES(deleted), points=VALUES(points), type=VALUES(type), status=VALUES(STATUS)";

$stmt = DB::connection('dataWarehouse')->getPdo()->exec($sql);

$warnings = DB::connection('dataWarehouse')->select('SHOW WARNINGS');

dd($stmt, $warnings);

I tried the recommended change, but still got the exact same error message:

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. (SQL: SHOW WARNINGS)

I also tried changing both the insert statement and the SHOW WARNINGS statement to exec() method calls. I didn't get the above error message, but the results from running the SHOW WARNINGS statement didn't contain the warnings from the invalid data in the insert statement:

0 (e.g. zero rows affected from the insert statement ->this is expected<-)

0 (e.g. incorrect results from SHOW WARNINGS ->this is NOT expected<-)

Wesstep1315

5 years ago

Best Answer

Level 4

I figured it out!

Hey, so just in case anyone else here ever runs into this issue later on, the solution at this time (12 Jan 2021), in one line, is to: Use the raw PDO instance, not the getPdo() method.

Now, in much more than one line, here's what worked for me:

$pdo = new PDO($dsn, $username, $password); // Create the PDO instance without Laravel's helper/wrapper methods; $dsn, $username, and $password are defined prior to instantiating the PDO.

$sql = "INSERT IGNORE INTO pestroutesdb.region(region_id,office_id,description,created,deleted,points,type,status) VALUES (NULL, 234, 'Interesting Description!', '2021-01-11 00:00:00', NULL, NULL, '1', '0') ON DUPLICATE KEY UPDATE region_id=VALUES(region_id), office_id=VALUES(office_id), description=VALUES(description), created=VALUES(created), deleted=VALUES(deleted), points=VALUES(points), type=VALUES(type), status=VALUES(STATUS)";

$pdo->exec($sql); // I used exec() here for testing purposes, I'll be using prepare/execute when making it ready for prod.

$stmt = $pdo->prepare('SHOW WARNINGS');
$stmt->execute();
$results = $stmt->fetchAll();

dd($results);

Hope this helps anyone else struggling with this!

1 like

Please or to participate in this conversation.