Description:
In version 5.0.0 of MySQL the following query from Moodle worked fine.
INSERT INTO mdl_choice ( COURSE, NAME, TEXT, FORMAT, RELEASE, DISPLAY, ALLOWUPDATE, SHOWUNANSWERED, LIMITANSWERS, TIMEOPEN, TIMECLOSE, TIMEMODIFIED ) VALUES ( 629, 'Ice Cream', 'What flavor is your favorite? ', 1, 0, 0, 0, 0, 0, 0, 0, 1121535063 );
generates a ERROR 1064 (42000): 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 'RELEASE, DISPLAY, ALLOWUPDATE, SHOWUNANSWERED, LIMITANSWERS, TIMEOPEN, TIMECLOSE, TIMEMODIFIED' at line 1
the problem seems to be with the release column - if it is in single quotes ` it works - if not then it does not work.
In other words:
INSERT INTO mdl_choice ( COURSE, NAME, TEXT, FORMAT, `RELEASE`, DISPLAY, ALLOWUPDATE, SHOWUNANSWERED, LIMITANSWERS, TIMEOPEN, TIMECLOSE, TIMEMODIFIED ) VALUES ( 630, 'Ice Cream', 'What flavor is your favorite? ', 1, 0, 0, 0, 0, 0, 0, 0, 1121535063 );
works, but
INSERT INTO mdl_choice ( COURSE, NAME, TEXT, FORMAT, RELEASE, DISPLAY, ALLOWUPDATE, SHOWUNANSWERED, LIMITANSWERS, TIMEOPEN, TIMECLOSE, TIMEMODIFIED ) VALUES ( 630, 'Ice Cream', 'What flavor is your favorite? ', 1, 0, 0, 0, 0, 0, 0, 0, 1121535063 );
does not work in MySQL versions 5.0.7b and 5.0.9b
The table structure is:
CREATE TABLE `mdl_choice` (
`id` int(10) unsigned NOT NULL auto_increment,
`course` int(10) unsigned NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
`text` text NOT NULL,
`format` tinyint(2) unsigned NOT NULL default '0',
`publish` tinyint(2) unsigned NOT NULL default '0',
`release` tinyint(2) unsigned NOT NULL default '0',
`display` int(4) unsigned NOT NULL default '0',
`allowupdate` tinyint(2) unsigned NOT NULL default '0',
`showunanswered` tinyint(2) unsigned NOT NULL default '0',
`limitanswers` tinyint(3) unsigned NOT NULL default '0',
`timeopen` int(10) unsigned NOT NULL default '0',
`timeclose` int(10) unsigned NOT NULL default '0',
`timemodified` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `course` (`course`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Available choices are stored here.' AUTO_INCREMENT=17 ;
How to repeat:
Using MySQL Version 5.0.9b, create moodle database (MyISAM) and the table mdl_choice using the table structure from above. Attempt to run the INSERT query:
INSERT INTO mdl_choice ( COURSE, NAME, TEXT, FORMAT, RELEASE, DISPLAY, ALLOWUPDATE, SHOWUNANSWERED, LIMITANSWERS, TIMEOPEN, TIMECLOSE, TIMEMODIFIED ) VALUES ( 629, 'Ice Cream', 'What flavor is your favorite? ', 1, 0, 0, 0, 0, 0, 0, 0, 1121535063 );
Now run:
INSERT INTO mdl_choice ( COURSE, NAME, TEXT, FORMAT, `RELEASE`, DISPLAY, ALLOWUPDATE, SHOWUNANSWERED, LIMITANSWERS, TIMEOPEN, TIMECLOSE, TIMEMODIFIED ) VALUES ( 629, 'Ice Cream', 'What flavor is your favorite? ', 1, 0, 0, 0, 0, 0, 0, 0, 1121535063 );
Suggested fix:
I'm not sure what is causing this to parse differently than other inserts.
