By bsenftner on
I'm staring at this, and it looks okay to me:
INSERT INTO flixor_actor (nid, vid, aid, gender, eyeCode, status, desc, timestamp) VALUES (70, 70, 'Client4adf813ba7cde', 0, 8, 0, 'third times a charm', 1256161596)
The sql is from a backtrace, where the above appears to be throwing an error near the usage of 'desc'. Here's the error message:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc, timestamp) VALUES (70, 70, 'Client4adf813ba7cde', 0, 8, 0, 'third times a ' at line 1 query: INSERT INTO flixor_actor (nid, vid, aid, gender, eyeCode, status, desc, timestamp) VALUES (70, 70, 'Client4adf813ba7cde', 0, 8, 0, 'third times a charm', 1256161596)
The above sql line worked fine earlier today, but I made a cosmetic change to the schema: the field 'desc' used to be called 'label'. I uninstalled the module I'm making (forcing my old table to be deleted), changed that field in the schema from 'label' to 'desc', and re-enabled the module (forcing a re-install and new table creation.) That appeared to work fine, and viewing the empty table in phpMyAdmin, it appears to be correct.
Here's my schema:
<?php
function flixor_schema() {
$schema['flixor_actor'] = array(
'description' => t('Maintain information for Flixor Digital Actors.'),
'fields' => array(
'nid' => array(
'description' => t('node id associated with this digital actor'),
'type' => 'int',
'unsigned' => TRUE,
'not null' => TRUE,
'default' => 0
),
'vid' => array(
'description' => t('version id associated with the node of this digital actor'),
'type' => 'int',
'unsigned' => TRUE,
'not null' => TRUE,
'default' => 0
),
'actorNum' => array(
'description' => t('increments with every new digital actor, keeping them orderly'),
'type' => 'serial',
'unsigned' => TRUE,
'not null' => TRUE,
'size' => 'big',
),
'aid' => array(
'description' => t('actor id string associated with this digital actor'),
'type' => 'varchar',
'length' => 64,
'not null' => TRUE,
'default' => ''
),
'gender' => array(
'description' => t('if zero then male, if 1 then female, else is an error'),
'type' => 'int',
'unsigned' => TRUE,
'not null' => TRUE,
'default' => 0
),
'eyeCode' => array(
'description' => t('the eye color code, specific values are media dependent'),
'type' => 'int',
'unsigned' => TRUE,
'not null' => TRUE,
'default' => 0
),
'status' => array(
'description' => t('internal use, tracks digital actor progress of use'),
'type' => 'int',
'unsigned' => TRUE,
'not null' => TRUE,
'default' => 0
),
'desc' => array(
'description' => t('user supplied description of this digital actor'),
'type' => 'varchar',
'length' => 128,
'not null' => TRUE,
'default' => ''
),
'timestamp' => array(
'description' => 'The Unix timestamp when this digital actor was created.',
'type' => 'int',
'not null' => TRUE,
'default' => 0
),
),
'indexes' => array(
'aid' => array('aid'), // most often lookups will be by actorId or status
'status' => array('status'),
),
'primary key' => array('actorNum'), // apparently, our auto-incrementing field must be the primary key
);
return $schema;
}
?>
Any ideas why I'm getting this sql syntax error?
Comments
If you look at the actual
If you look at the actual table, what fields does it have?
looking at the table in
looking at the table in phpMyAdmin, I see what I'm expecting:
nid - int(10), unsigned, Null=No, Default=0
vid - int(10), unsigned, Null=No, Default=0
actorNum - bigint(20), unsigned, Null=No, auto_increment
aid - varchar(64), utf8_general_ci, Null=no
gender - int(10), unsigned, Null=No, Default=0
eyeCode - int(10), unsigned, Null=No, Default=0
status - int(10), unsigned, Null=No, Default=0
desc - varchar(128), utf8_general_ci, Null=no
timestamp - int(11), Null=No, Default=0
I tried plugging that raw sql into phpMyAdmin, and it too reports an error near the usage of 'desc'... I'm not seeing it...
comparing my sql with that in other modules that also store strings, the syntax looks the same to me... Here's my insert code:
All the variables have been tested to have valid values as well...
-Blake
www.BlakeSenftner.com www.3D-Avatar-Store.com
I just tried drupal_write_record()
I just tried drupal_write_record() as a replacement for db_query() and an INSERT statement. However, I'm getting the same error...?!
Here's the drupal_write_record() version:
$returnVal is false, and the error generated is the same as above. The exact same sql line is being generated by drupal_write_record() as was before with db_query()...
-Blake
www.BlakeSenftner.com www.3D-Avatar-Store.com
Doesn't desc need to be
Doesn't desc need to be quoted thus: `desc`
In the drupal_write_record()
In the drupal_write_record() version, I'm not doing the quoting, Drupal is...
-Blake
www.BlakeSenftner.com www.3D-Avatar-Store.com
I believe the cause is the use of 'desc' as a column name!
Apparently, Drupal is not handling the quoting of column names correctly, and there's a bit of resistance to fix the issue:
http://drupal.org/node/266610
At first, I was thinking that perhaps having a column named 'status' could be a problem, so I changed it to 'stat' right about the same time that I found the above linked issue. Additionally, I looked up the list of mysql reserved words, and right there is 'desc' as a reserved word.
Yep! That was it. I just tried changing the field 'desc' to 'description' and everything worked. Kinda a hidden gotcha... that ought to be fixed.
-Blake
www.BlakeSenftner.com www.3D-Avatar-Store.com
Keyword?
Isn't DESC a keyword, as in ORDER BY ... DESC (descending)?
Oops, never mind, strike that, you said that.