So I am trying to do the "mysql_collation.php --collation=utf8mb4_unicode_ci" and came across this error on the mdl_user table
mdl_user - Error: Tried to convert mdl_user, but there was a problem. Please check the details of this table and try again.
How do I and what am I checking for? Where do I start?
We are on RHEL 7.2 64-bit, Moodle 3.4, PHP 7.1.8, and MySQL 5.7.21
Thank you.
so i tried this
ALTER TABLE mdl_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
and got this
ERROR 1062 (23000): Duplicate entry '4-000338063' for key 'mdl_user_mneuse_uix'
so what do i do now?
is this a clue?
mysql> show index from mdl_user;
+----------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mdl_user | 0 | PRIMARY | 1 | id | A | 116840 | NULL | NULL | | BTREE | | |
| mdl_user | 0 | mdl_user_mneuse_uix | 1 | mnethostid | A | 1 | NULL | NULL | | BTREE | | |
| mdl_user | 0 | mdl_user_mneuse_uix | 2 | username | A | 116840 | NULL | NULL | | BTREE | | |
| mdl_user | 1 | mdl_user_del_ix | 1 | deleted | A | 1 | NULL | NULL | | BTREE | | |
....
Trust you made an SQL dump of the DB just prior to working with the DB and can restore that dump to a new DB?
https://stackoverflow.com/questions/30051510/how-can-i-rebuild-indexes-and-update-stats-in-mysql-innodb
mysql> use moodle35;
mysql> OPTIMIZE TABLE mdl_user;
With the last, one will see/should see:
Table does not support optimize, doing recreate + analyze instead
but it should also report moodle35.mdl_user | optimize | status | OK
The use moodle35 above you need to adjust for what DB is being used for your Moodle.
'spirit of sharing', Ken
Hello Ken.
Not sure 100% of what you meant unfortunately.
I made a dump from moodle 3.2.x and the db was called moodle32db that was running MySQL 5.5.x and replaced the reference to the "moodle32db" in the file to the new one (see below) to "moodle34db".
I created a new blank database in our newer MySQL 5.7.x called moodle34db.
I then imported the .sql into the moodle34db and upgraded moodle and it all worked.
So I wanted to run this file to see what happens. All was going well until it hit mdl_user.
This what I got when trying to optimize the table
mysql> optimize table mdl_user;
+-----------------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------+----------+----------+-------------------------------------------------------------------+
|moodle34db.mdl_user | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| moodle34db.mdl_user | optimize | status | OK |
+-----------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (7.90 sec)
So what do I do next?
Upgraded the proper way and still getting the same error
- Error: Tried to convert mdl_user, but there was a problem. Please check the details of this table and try again.
What and where do I start checking? No clue.
Try mysql_upgrade --force -u root -p
That might complain about master/slave DB's but you arene;'t running anything setup that way.
It should show:
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Then some info on the moodle DB ... in the clip below my 3.4 DB named moodle34fc
moodle34fc.mdl_analytics_indicator_calc OK
moodle34fc.mdl_analytics_models OK
moodle34fc.mdl_analytics_models_log OK
It says to: Please check the details of this table
So ... first overall ... from command line
mysqlshow -u root -p --count [DATABASEFORMOODLE];
output will show all tables in with the following columns:
| Tables | Columns | Total Rows |
From the mysql> prompt:
mysql> describe mdl_user;
shows what?
Might have imported the .sql prior to the mysql_upgrade command.
Re-acquire an SQL dump from old server.
Transfer that newly acquired SQL dump to new server.
Drop the database for the moodle
Re-create a new database for the moodle (correct character set/collation)
Then import the newly acquired SQL dump into the new DB.
Am about out of ideas/things to try with this.
Maybe a true DB admin type person would 'chime' in here.
'spirit of sharing', Ken
I did do a --force option and desc db did not show anything outstanding...
What's in your my.cnf file? Have correct settings for innodb?
I have been assuming the SQL dump from old server was good. Maybe it's not!
What command (exactly) did you use to export the Moodle DB on old server?
How large is that sql dump?
How did you xfer the .sql file to the new server?'
My next suggestion to you would be do it over again .... all of it.
Erase mysql mysql_server from new server.
Also manually remove the files in /var/lib/mysql/ Those are the actual DB files. If you leave them, the new installation will use them and at this point, you don't want that. We want fresh ... new.
Install the version you want from the mysqlcommunity repo
https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/
Tweak your my.cnf for the new instance.
Then re-acquire an sql dump from old server.
Xfer to new server.
No need to run mysql_upgrade!
Just import the sql dump from old server.
Let's see where that gets you.
'spirit of sharing', Ken
redid everything,, even creating the database from scratch and reinstalled mysql and still the same problem ... working though but that error is a mystery to me
my.cnf is pretty good
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
datadir=/db/prodmysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/opt/rh/rh-mysql57/log/mysql/mysqld.log
pid-file=/var/run/rh-mysql57-mysqld/mysqld.pid
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake
innodb_buffer_pool_size=51G
max_allowed_packet=3G
innodb_log_buffer_size=32M
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instances=8
innodb_thread_concurrency=8
innodb_buffer_pool_dump_pct=75
innodb_adaptive_hash_index_parts=16
innodb_checksum_algorithm=crc32
innodb_page_cleaners=8
innodb_log_file_size=6G
# this is only for embedded server
[embedded]
config.php
$CFG->dboptions = array(
'dbcollation' => 'utf8mb4_unicode_ci',
);
Yes. my.cnf looks ok with the exception of
innodb_buffer_pool_instances=51
https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html
MySQLTuner always recommends if the buffer pool is 51G one instance per G.
There was another user that had a similar problem and inspecting the mdl_user table discovered the 'address' column missing ... so he rebuilt the table by viewing mdl_user from another instance.
mysql> show create table mdl_user;
mysql> use DB;
mysql> SELECT @@character_set_database, @@collation_database;
What if you exported just the mdl_user table from old site and tried to import just the mdl_user table in new?
Ken
I have the "address" field in mdl_user though I doubt that may be the cause.
mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4 | utf8mb4_unicode_ci |
+--------------------------+----------------------+
1 row in set (0.00 sec)
mdl_user | CREATE TABLE `mdl_user` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`auth` varchar(20) NOT NULL DEFAULT 'manual',
`confirmed` tinyint(1) NOT NULL DEFAULT '0',
`policyagreed` tinyint(1) NOT NULL DEFAULT '0',
`deleted` tinyint(1) NOT NULL DEFAULT '0',
`suspended` tinyint(1) NOT NULL DEFAULT '0',
`mnethostid` bigint(10) NOT NULL DEFAULT '0',
`username` varchar(100) NOT NULL DEFAULT '',
`password` varchar(255) NOT NULL DEFAULT '',
`idnumber` varchar(255) NOT NULL DEFAULT '',
`firstname` varchar(100) NOT NULL DEFAULT '',
`lastname` varchar(100) NOT NULL DEFAULT '',
`email` varchar(100) NOT NULL DEFAULT '',
`emailstop` tinyint(1) NOT NULL DEFAULT '0',
`icq` varchar(15) NOT NULL DEFAULT '',
`skype` varchar(50) NOT NULL DEFAULT '',
`yahoo` varchar(50) NOT NULL DEFAULT '',
`aim` varchar(50) NOT NULL DEFAULT '',
`msn` varchar(50) NOT NULL DEFAULT '',
`phone1` varchar(20) NOT NULL DEFAULT '',
`phone2` varchar(20) NOT NULL DEFAULT '',
`institution` varchar(255) NOT NULL DEFAULT '',
`department` varchar(255) NOT NULL DEFAULT '',
`address` varchar(255) NOT NULL DEFAULT '',
`city` varchar(120) NOT NULL DEFAULT '',
`country` varchar(2) NOT NULL DEFAULT '',
`lang` varchar(30) NOT NULL DEFAULT 'en',
`theme` varchar(50) NOT NULL DEFAULT '',
`timezone` varchar(100) NOT NULL DEFAULT '99',
`firstaccess` bigint(10) NOT NULL DEFAULT '0',
`lastaccess` bigint(10) NOT NULL DEFAULT '0',
`lastlogin` bigint(10) NOT NULL DEFAULT '0',
`currentlogin` bigint(10) NOT NULL DEFAULT '0',
`lastip` varchar(45) NOT NULL DEFAULT '',
`secret` varchar(15) NOT NULL DEFAULT '',
`picture` bigint(10) NOT NULL DEFAULT '0',
`url` varchar(255) NOT NULL DEFAULT '',
`description` longtext,
`descriptionformat` tinyint(2) NOT NULL DEFAULT '1',
`mailformat` tinyint(1) NOT NULL DEFAULT '1',
`maildigest` tinyint(1) NOT NULL DEFAULT '0',
`maildisplay` tinyint(2) NOT NULL DEFAULT '2',
`autosubscribe` tinyint(1) NOT NULL DEFAULT '1',
`trackforums` tinyint(1) NOT NULL DEFAULT '0',
`timecreated` bigint(10) NOT NULL DEFAULT '0',
`timemodified` bigint(10) NOT NULL DEFAULT '0',
`trustbitmask` bigint(10) NOT NULL DEFAULT '0',
`imagealt` varchar(255) DEFAULT NULL,
`lastnamephonetic` varchar(255) DEFAULT NULL,
`firstnamephonetic` varchar(255) DEFAULT NULL,
`middlename` varchar(255) DEFAULT NULL,
`alternatename` varchar(255) DEFAULT NULL,
`calendartype` varchar(30) NOT NULL DEFAULT 'gregorian',
PRIMARY KEY (`id`),
UNIQUE KEY `mdl_user_mneuse_uix` (`mnethostid`,`username`),
KEY `mdl_user_del_ix` (`deleted`),
KEY `mdl_user_con_ix` (`confirmed`),
KEY `mdl_user_fir_ix` (`firstname`),
KEY `mdl_user_las_ix` (`lastname`),
KEY `mdl_user_cou_ix` (`country`),
KEY `mdl_user_las2_ix` (`lastaccess`),
KEY `mdl_user_ema_ix` (`email`),
KEY `mdl_user_aut_ix` (`auth`),
KEY `mdl_user_idn_ix` (`idnumber`),
KEY `mdl_user_cit_ix` (`city`),
KEY `mdl_user_las3_ix` (`lastnamephonetic`),
KEY `mdl_user_fir2_ix` (`firstnamephonetic`),
KEY `mdl_user_mid_ix` (`middlename`),
KEY `mdl_user_alt_ix` (`alternatename`)
) ENGINE=InnoDB AUTO_INCREMENT=131212 DEFAULT CHARSET=utf8 COMMENT='One record for each person'
I tried to do an mysqlcheck it all came back okay
.....
.....
moodle34db.mdl_user OK
.....
.....
So you upgraded MySQL from 5.5 to 5.7.
Then attempted to run the cli script mysql_collation.php to convert the DB you had for moodle to utf8mb4_unicode_ci and that's when you ran into the first error that asked you to check the details of the table.
When I upgrade mysql (like 5.5 to 5.7), I run mysql_upgrade.
That runs though all tables of all databases checks tables, etc.
from mysql_upgrade --help:
MySQL utility for upgrading databases to new MySQL versions.
Usage: mysql_upgrade [OPTIONS]
When I do that ... I've have had no issues.
When you ran this:
ALTER TABLE mdl_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
you got duplicate key entry - which would have been fixed with mysql_uprade.
That's when I suggested the optimize table command.
which you eventually ran but only AFTER importing a dump of the DB ... which has to rebuild indexes when creating the new DB.
When one is upgrading Moodle that has no additional DB requirements, one normally doesn't have to do anything with the DB.
Olderversion -> 3.5, however, did require a higher version of MySQL and a change to character set and collation.
So you got there ... not directly ... but ya got there.
Just to be sure .... updates to 3.5 and probably upgrades to the next 3.6 shouldn't
need changes to character set nor collations so you won't have to be doing what you did with this 3.5 upgrade to Moodle.
Do, however, continue the good practice of backing up your database just prior to doing anything from the mysql> prompt and when updating or upgrading Moodle.
'spirit of sharing', Ken
so what do I do next to fix it or is it just notification? I have another moodledatabase and upgraded the platform the same way and did not encounter any errors when I ran the script.
Is the duplicate the problem?
The only way I could answer that is to have full access to your site(s) ... so I could see history of MySQL commands issued as whatever else you might have tried ..... don't think that's gonna happen. And am not asking you to provide that.
From what I can tell, you are 'fixed' ... but that depends upon how good the info shared from you. I sometimes forget, upon upgrading mysql from 5.x to 5.higher, to run the mysql_uprade script and have seen some strange errors ... could be you experience one of those ... no Vulcan Mind Meld possible on this end!
So if you run the same admin/cli/ script does it report any error? If no error, then you're fixed!
'spirit of sharing', Ken
I had asked about how you issued mysqldump on old server to create the .sql file.
There are two indexes related to users in Moodle. One is for mnet users and the other
is for all other methods of authentiation. If I re-call correctly, it was possible
using mNet to have two accounts that were really the same execpt for the mnethost column.
The issue for you was/is a duplicate in mdl_user table.
So am wondering if doing a mysqldump that excludes indexes wouldn't solve the problem.
Offical Moodle docs
https://docs.moodle.org/35/en/Site_backup#Database
-C -Q -e --create-options
The -C is compress
-e extent insert
-Q quote names
MySQL 5.5
https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html
--opt Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
What if you ran your sql dump with --opt option?
Ken
I just did a generic mysqldump as in
mysqldump -u root -p --databases moodle32database > /path/to/moodledb/backup/ourmoodle3x.sql on platform A, MySQL 5.5.x
and imported it into platform's B, MySQL 5.7.x
mysql -u root -p our34database < /path/db/ourmoodle3x.sql
then did a mysql_upgrade on platform B (though not needed but still came out okay)
Are you getting the same errors? If so, then you need to try something different ... same ole thing ... same ole results. :\
*** try the dump with --opt ***
the -opt is a given but I'll try LOL
same results... error.. even with --opt options
Guess Howard might be too busy with work, sooooooo .... even though I said I'd back out of this, it appears am not that cruel. :\
So the command to convert tables to the desired character set and collation kicks out the same error:
ERROR 1062 (23000): Duplicate entry '4-000338063' for key 'mdl_user_mneuse_uix'
PRIMARY KEY (`id`),
UNIQUE KEY `mdl_user_mneuse_uix` (`mnethostid`,`username`),
One of the keys to mdl_user table is related to those accounts in the Moodle coming in from a Moodle Networking host.
Are you running Moodle Networking?
All users in mdl_user table have an 'mnethostid' column. 1 in that column means the server where you are making the query .... in your case the new server.
How many users on your new system have Mnet accounts:
This query will show moodle Networking users that are coming from other networked moodles:
select id,firstname,lastname,email,mnethostid from mdl_user where mnethostid > '1';
If you are not using Moodle Networking any more, remove all users that have accounts in new system whose authentication was/is mnet.
You might not be able to do that from the Moodle Admin UI due to the error.
But you could do it from the mysql client prompt easily enough.
You'll also have to find what courses were setup to use/allow Moodle Networking. That I'll leave up to you.
And, that, my friend, is my final guess!
Comment: Used to run Moodle Networking ... even had a Moodle HUB on a TCEA server at one time. Got away from Moodle Networking (as well as the HUB) when MD posted about Moodle Networking's eventual demise (Moodle Graveyard - like Googles).
Don't think Mnet has gotten any fixes/updates/improvements in a long time .... might be time to put it in your Moodle Graveyard. 2 cent advice of course.
'spirit of sharing', Ken
