DDL SQL execution

DDL SQL execution

by Marijan Milovec -
Number of replies: 2

Hi, on Moodle 3.3 I want to convert existing database to utf8mb4 and I have followed this documentation: https://docs.moodle.org/33/en/MySQL_full_unicode_support

And I got an error:


mdl_wiki_pages                           - Default exception handler:DDL sql execution

Debug: Duplicate entry '2465-Win7-139522' for key 'mdl_wikipage_subtituse_uix'

ALTER TABLE mdl_wiki_pages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
Error code: ddlexecuteerror
* line 492 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown
* line 1041 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 319 of /admin/cli/mysql_collation.php: call to mysqli_native_moodle_database->change_database_structure()
* line 153 of /admin/cli/mysql_collation.php: call to mysql_set_row_format()



has anyone problem like that? What should i do how to fix this?


thank you.

Average of ratings: -
In reply to Marijan Milovec

Re: DDL SQL execution

by Ken Task -
Picture of Particularly helpful Moodlers

First, make a backup of the database ... an sql dump.   Even if it's a little putzed, having something that's a backup is better than no backup at all.

You must be attempting this in prep to upgrade to 3.5 of Moodle (that's a guess).   Is that right?

See it's on Win-7?   What is mysql version?

Did you recently upgrade the MySQL server to a higher version in order to convert character set/collation of the DB for Moodle?

Someone else had similar issue/error dealing with indexes.

Yours:

Duplicate entry '2465-Win7-139522' for key 'mdl_wikipage_subtituse_uix

Do you have a recent backup of the database?  a mysqldump?   If so, one could simply create a new database for the moodle with character set/collation set on creation of the db.

mysql> create database moodlenew character set utf8mb4 collate utf8mb4_unicode_ci;

Quit the mysql client. \q

then import the mysqldump (might be named moodlebackup.sql - whatever you named it).

mysql -u root -p moodlenew < moodlebackup.sql

That will import to the new database and regenerate those indexes on the fly.

Then, edit config.php file and point to the newer database.

OR ... if you recently upgraded mysql, then run:

mysql_upgrade -u root -p

That will check all databases/indexes and fix them on the fly ... upgrading.

'spirit of sharing', Ken


Average of ratings: -