Renaming a database field that has become a reserved word

Renaming a database field that has become a reserved word

by Mike Churchward -
Number of replies: 3
Picture of Core developers Picture of Plugin developers Picture of Testers

Hi -

One of my plugins uses a fieldname of 'rank', which as of MySQL 8.0.1 has become a reserved word. I have changed the name of this field throughout the code and added a rename field command to the upgrade script but it appears that people using the version of MySQL that has it as a reserved word cannot execute the upgrade script. I'm guessing because it will not allow  the old name in the "ALTER TABLE" command?

The error that occurs is on this command:

ALTER TABLE mdl_questionnaire_response_rank CHANGE rank rankvalue BIGINT(11) NOT NULL

So, how do I rename the field?

The upgrade script is as follows:

$table = new xmldb_table('questionnaire_response_rank');
$rankfieldname = $DB->get_manager()->generator->getEncQuoted('rank');
$field = new xmldb_field($rankfieldname, XMLDB_TYPE_INTEGER, '11', null, XMLDB_NOTNULL, null, null, null, '0', 'choice_id');
if ($dbman->field_exists($table, $field)) {
    $dbman->rename_field($table, $field, 'rankvalue');
}

Average of ratings: -
In reply to Mike Churchward

Re: Renaming a database field that has become a reserved word

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Hi Mike,
the dirty workaround is wrapping the old field name (CONTRIB-7420) with "`", when using MySQL.

The right solution should be addressed only in Moodle (MDL-63319) since it is a main stream issue: when it will be integrated, the dirty workaround above will break the update stage due to doubling the encoding even in MySQL.

See also a similar issue in https://moodle.org/mod/forum/discuss.php?d=373009 (MDL-60793).

HTH,
Matteo

Average of ratings: Useful (1)
In reply to Matteo Scaramuccia

Re: Renaming a database field that has become a reserved word

by Mike Churchward -
Picture of Core developers Picture of Plugin developers Picture of Testers

The "$DB->get_manager()->generator->getEncQuoted('rank')" should have returned the old field name enclosed in back quotes. But I'm not sure that it works properly within the "field_exists" and "rename_field" functions.

Or are you saying there is no way to fix this in an upgrade script at present? At least until MDL-63319 is implemented?

Okay... Further investigation...

Using:

$rankfieldname = $DB->get_manager()->generator->getEncQuoted('rank');
$field = new xmldb_field($rankfieldname, XMLDB_TYPE_INTEGER, '11', null, XMLDB_NOTNULL, null, null, null, '0', 'choice_id');
does indeed set the name of the field to the quoted version. And the 'rename_field' uses 'getRenameFieldSQL', which for MySQL uses the 'getName' function which indeed returns a string with the proper quotes.
But the main sql_generator version of 'getRenameFieldSQL' passes the 'getName' function through another 'getEncQuoted' call. This might cause problems. It looks like it is possible that the field name would end up with two sets of quotes... sad

Is there any way for me to create an upgrade script that will work without waiting for MDL-63319?
Average of ratings: -
In reply to Mike Churchward

Re: Renaming a database field that has become a reserved word

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Hi MIke,
AFAIK no, unfortunately for you.

I'd code an hacky upgrade path in your plug-in that it will test if it runs on MySQL and under a Moodle version w/o MDL-63319 being landed, and only under that context I'd wrap the rank field name with the required backtick.
Other IMHO valid option: you should add a Wiki entry for this known issue and ask the user to manually add backticks if she/he is running a Moodle version affected by MDL-63319.

The same issue happens in core for MDL-60793, as described in https://moodle.org/mod/forum/discuss.php?d=373009: I put it in the my backlog and when I saw that it affected more than one scenario I started fixing the unbalanced mysql_sql_generator::getRenameFieldSQL().

HTH,
Matteo

Average of ratings: -