I'm using PHP 5.5.9 and MySQL 5.5.44 with mysqlnd 5.0.11-dev on Ubuntu 14.04 LTS. The following statement fails to prepare:
$db->prepare("SELECT nr.x FROM (SELECT ? AS x) AS nr")
This is despite the fact that the following statement prepares successfully:
$db->prepare("SELECT nr.x FROM (SELECT '1337' AS x) AS nr")
What causes this difference? The manual says "Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth." But this is for a data value.
Not PDO's fault
The same thing happens in the stand-alone client:
mysql -uredacted -predacted redacted
-- Type 'help;' or '\h' for help.
SELECT nr.x FROM (SELECT '1337' AS x) AS nr;
-- x
-- 1337
-- 1 row in set (0.00 sec)
PREPARE workingstmt FROM 'SELECT nr.x FROM (SELECT ''1337'' AS x) AS nr';
-- Query OK, 0 rows affected (0.00 sec)
-- Statement prepared
DEALLOCATE PREPARE workingstmt;
-- Query OK, 0 rows affected (0.00 sec)
PREPARE brokenstmt FROM 'SELECT nr.x FROM (SELECT ? AS x) AS nr';
-- ERROR 1054 (42S22): Unknown column 'nr.x' in 'field list'
^D
-- Bye
My motivation
I'm trying to add a row to a table that has an auto-incrementing primary key. In InnoDB's default auto-increment locking mode, which the manual calls "consecutive", InnoDB skips an auto-increment value when a row might be inserted but is not, as is the case with INSERT IGNORE or ON DUPLICATE KEY UPDATE that runs into an existing row whose UNIQUE values match those of the row being inserted. (These are called "mixed-mode inserts" in the manual.)
Every few hours, I import a feed from my supplier. This has about 200,000 rows, and all but on average 200 of these rows have unique values that correspond to values already present in the table. So if I were to use INSERT IGNORE or ON DUPLICATE KEY UPDATE all the time, I'd burn through 199,800 IDs every few hours. So I don't want to use INSERT IGNORE or ON DUPLICATE KEY UPDATE for fear that I might exhaust the 4.2 billion limit of INTEGER UNSIGNED with repeated inserts over time to a table with the same UNIQUE key. I don't want to switch the column to BIGINT type because 32-bit PHP has no type with the same semantics as MySQL BIGINT. The server administrator is unwilling to switch to 64-bit PHP or to change innodb_autoinc_lock_mode for all users of the server.
So instead, I decided to try INSERT INTO ... SELECT, creating a 1-row table with the unique key columns in a subquery and left joining it to the main table to reject unique key values that already exist. (The manual says INSERT INTO ... SELECT is a "bulk insert", which does not burn IDs.) The intent is to do something like this:
INSERT INTO the_table
(uniquecol, othercol1, othercol2)
SELECT nr.uniquecol, :o1 AS othercol1, :o2 AS othercol2
FROM (
SELECT ? AS uniquecol
) AS nr
LEFT JOIN the_table ON nr.settlement_id = the_table.settlement_id
WHERE the_table.row_id IS NULL
This failed, giving the PDO error:
["42S22",1054,"Unknown column 'settlement_id' in 'field list'"]
<?php // MCVE follows
/* Connect to database */
$pdo_dsn = 'mysql:host=127.0.0.1;dbname=redacted';
$pdo_username = 'redacted';
$pdo_password = 'redacted';
$pdo_options = [PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',];
$db = new PDO($pdo_dsn, $pdo_username, $pdo_password, $pdo_options);
$pdo_dsn = $pdo_username = $pdo_password = 'try harder';
// ensure that PDO doesn't convert everything to strings
// per http://stackoverflow.com/a/15592818/2738262
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$db->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
/* Create mock data with which to test the statements */
$prep_stmts = ["
CREATE TEMPORARY TABLE sotemp (
file_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
settlement_id VARCHAR(30) NOT NULL,
num_lines INTEGER UNSIGNED NOT NULL DEFAULT 0,
UNIQUE (settlement_id)
)
","
INSERT INTO sotemp (settlement_id, num_lines) VALUES
('15A1', 150),
('15A2', 273),
('15A3', 201)
"];
foreach ($prep_stmts as $stmt) $db->exec($stmt);
/* Now the tests */
$working_stmt = $db->prepare("
SELECT nr.settlement_id
FROM (
-- change this to either a value in sotemp or one not in sotemp
-- and re-run the test program
SELECT '15A3' AS settlement_id
) AS nr
LEFT JOIN sotemp ON nr.settlement_id = sotemp.settlement_id
WHERE sotemp.file_id IS NULL
");
if ($working_stmt) {
$working_stmt->execute();
$data = $working_stmt->fetchAll(PDO::FETCH_ASSOC);
echo "Working: ".json_encode($data)."\n";
} else {
echo "Working statement failed: ".json_encode($db->errorInfo())."\n";
}
$broken_stmt = $db->prepare("
SELECT nr.settlement_id
FROM (
SELECT ? AS settlement_id
) AS nr
LEFT JOIN sotemp ON nr.settlement_id = sotemp.settlement_id
WHERE sotemp.file_id IS NULL
");
if ($broken_stmt) {
$broken_stmt->execute(['15A4']);
$data = $broken_stmt->fetchAll(PDO::FETCH_ASSOC);
echo "Broken: ".json_encode($data)."\n";
} else {
echo "Broken statement failed: ".json_encode($db->errorInfo())."\n";
}
What is causing this error? And is there a better way to insert a row only if the primary key does not exist without exhausting auto-increment IDs?
