This log just indicates that you didn't shut down correctly, so upon startup, InnoDB had to do a "crash recovery". It's not the crash recovery that would have lost data -- it's your operating system or hardware. Crash recovery is designed actually to avoid losing data due to crashes (that's its entire purpose).
The log message indicates a severe problem:
The log sequence numbers 5988825 and 5988825 in ibdata files do not match the log sequence number 6057379 in the ib_logfiles!
This likely means that data is not getting to disk effectively either because of a misconfiguration in MySQL, or a misconfiguration of your operating system or hardware.
If you are running with innodb_flush_log_at_trx_commit
set to anything other than 1
, this is your fault as this is a dangerous setting. If you are running it with 1
, this would imply that InnoDB is requesting data to be written to disk and confirmed, but the system is lying to InnoDB about writing it, causing corruption later.
There is no way to get the data back. If you need data that was lost, you must restore it from backup.
MyISAM is definitely not safer, and in fact MyISAM would more than likely have suffered extreme corruption under the same circumstances.
I had the same problem with an Ubuntu installation of MySQL 5.6.23.
I had to edit /etc/mysql/my.conf and add these entries to these sections:
[client]
default-character-set=utf8mb4
[mysqld]
character-set-server = utf8mb4
[mysql]
default-character-set=utf8mb4
Then as root execute: service mysql restart
Both my webserver connections and my local (shell) mysql connections worked with utf8mb4.
INSERT INTO some_table_name
(some_index_id,value_to_be_utf8mb4)
VALUES (55, x'F09F9A8A');
select * from some_table_name;
| some_index_id | value_to_be_utf8mb4 |
| 55 | ? |
1 rows in set (0.00 sec)
The following are from a fresh command line instantiation of mysql:
mysql> show variables like "%coll%";
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+----------------------+--------------------+
mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
As a final note, the "character_set_system" is always "utf8" according to the mysql 5.x specifications, so that is normal.
Best Answer
If you manually update any mysql.X table regarding privileges will require a FLUSH PRIVILEGES to effect the change.
Using GRANT is the better way of of ensuring privs exists (and doesn't require
FLUSH PRIVILEGES
).