Upgrade bug tokudb gone missing or tokudb is buggy?

MySQL & MariaDB Other MySQL® Questions
1

Hi All,
Below was my current.

Percona Server template configuration

[mysqld]

Remove leading # and set to the amount of RAM for the most important data

cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

innodb_buffer_pool_size = 128M

Remove leading # to turn on a very important data integrity option: logging

changes to the binary log between backups.

log_bin

Remove leading # to set options mainly useful for reporting servers.

The server defaults are faster for transactions and fast SELECTs.

Adjust sizes as needed, experiment to find the optimal values.

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#default-storage-engine=tokudb

What I did what yum update and my mysql got updated.

Below is the updates
Updating : Percona-Server-shared-compat-57-5.7.18-14.1.el7.x86_64 1/10
Updating : Percona-Server-shared-57-5.7.18-14.1.el7.x86_64 2/10
Updating : Percona-Server-client-57-5.7.18-14.1.el7.x86_64 3/10
Updating : Percona-Server-server-57-5.7.18-14.1.el7.x86_64 4/10

  • The suggested mysql options and settings are in /etc/percona-server.conf.d/mysqld.cnf
  • If you want to use mysqld.cnf as default configuration file please make backup of /etc/my.cnf
  • Once it is done please execute the following commands:
    rm -rf /etc/my.cnf
    update-alternatives --install /etc/my.cnf my.cnf “/etc/percona-server.cnf” 200

Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
Run the following commands to create these functions:
mysql -e “CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME ‘libfnv1a_udf.so’”
mysql -e “CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME ‘libfnv_udf.so’”
mysql -e “CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME ‘libmurmur_udf.so’”
See [url]http://www.percona.com/doc/percona-server/5.7/management/udf_percona_toolkit.html[/url] for more details
Updating : Percona-Server-tokudb-57-5.7.18-14.1.el7.x86_64 5/10
Cleanup : Percona-Server-tokudb-57-5.7.17-13.1.el7.x86_64 6/10
Cleanup : Percona-Server-server-57-5.7.17-13.1.el7.x86_64 7/10
Cleanup : Percona-Server-client-57-5.7.17-13.1.el7.x86_64 8/10
Cleanup : Percona-Server-shared-57-5.7.17-13.1.el7.x86_64 9/10
Cleanup : Percona-Server-shared-compat-57-5.7.17-13.1.el7.x86_64 10/10
Verifying : Percona-Server-tokudb-57-5.7.18-14.1.el7.x86_64 1/10
Verifying : Percona-Server-shared-57-5.7.18-14.1.el7.x86_64 2/10
Verifying : Percona-Server-server-57-5.7.18-14.1.el7.x86_64 3/10
Verifying : Percona-Server-shared-compat-57-5.7.18-14.1.el7.x86_64 4/10
Verifying : Percona-Server-client-57-5.7.18-14.1.el7.x86_64 5/10
Verifying : Percona-Server-server-57-5.7.17-13.1.el7.x86_64 6/10
Verifying : Percona-Server-shared-compat-57-5.7.17-13.1.el7.x86_64 7/10
Verifying : Percona-Server-tokudb-57-5.7.17-13.1.el7.x86_64 8/10
Verifying : Percona-Server-shared-57-5.7.17-13.1.el7.x86_64 9/10
Verifying : Percona-Server-client-57-5.7.17-13.1.el7.x86_64 10/10

Updated:
Percona-Server-client-57.x86_64 0:5.7.18-14.1.el7
Percona-Server-server-57.x86_64 0:5.7.18-14.1.el7
Percona-Server-shared-57.x86_64 0:5.7.18-14.1.el7
Percona-Server-shared-compat-57.x86_64 0:5.7.18-14.1.el7
Percona-Server-tokudb-57.x86_64 0:5.7.18-14.1.el7

Complete!

After this the tokudb is not being recognised any more my mysql stopped to restart. I had to comment the tokudb then mysql can start now. Below is part of mysql log.

2017-05-15T06:03:05.862770Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 3932160 bytes
2017-05-15T06:03:05.946047Z 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2017-05-15T06:03:05.946134Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-05-15T06:03:05.946160Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-05-15T06:03:05.946222Z 0 [Note] InnoDB: Setting file ‘./ibtmp1’ size to 12 MB. Physically writing the file full; Please wait …
2017-05-15T06:03:06.096069Z 0 [Note] InnoDB: File ‘./ibtmp1’ size is now 12 MB.
2017-05-15T06:03:06.096267Z 0 [Note] InnoDB: Doublewrite buffer not found: creating new
2017-05-15T06:03:06.296244Z 0 [Note] InnoDB: Doublewrite buffer created
2017-05-15T06:03:06.310384Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-05-15T06:03:06.310410Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-05-15T06:03:06.310601Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-05-15T06:03:06.354573Z 0 [Note] InnoDB: Foreign key constraint system tables created
2017-05-15T06:03:06.354637Z 0 [Note] InnoDB: Creating tablespace and datafile system tables.
2017-05-15T06:03:06.387884Z 0 [Note] InnoDB: Tablespace and datafile system tables created.
2017-05-15T06:03:06.387937Z 0 [Note] InnoDB: Creating sys_virtual system tables.
2017-05-15T06:03:06.421245Z 0 [Note] InnoDB: sys_virtual table created
2017-05-15T06:03:06.421303Z 0 [Note] InnoDB: Creating zip_dict and zip_dict_cols system tables.
2017-05-15T06:03:06.454581Z 0 [Note] InnoDB: zip_dict and zip_dict_cols system tables created.
2017-05-15T06:03:06.454793Z 0 [Note] InnoDB: Waiting for purge to start
2017-05-15T06:03:06.504972Z 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.18-14 started; log sequence number 0
2017-05-15T06:03:06.505428Z 0 [Note] Plugin ‘FEDERATED’ is disabled.
2017-05-15T06:03:06.510037Z 0 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to [url]http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html[/url] for how to resolve the issue.
mysqld: Table ‘mysql.plugin’ doesn’t exist
2017-05-15T06:03:06.510134Z 0 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
2017-05-15T06:03:06.511046Z 0 [Warning] InnoDB: Cannot open table mysql/gtid_executed from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to [url]http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html[/url] for how to resolve the issue.
mysqld: Table ‘mysql.gtid_executed’ doesn’t exist
2017-05-15T06:03:06.511091Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2017-05-15T06:03:06.514560Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2017-05-15T06:03:06.514581Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2017-05-15T06:03:06.536783Z 0 [Warning] CA certificate ca.pem is self signed.
2017-05-15T06:03:06.536866Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2017-05-15T06:03:06.537595Z 0 [Note] Server hostname (bind-address): ‘*’; port: 3306
2017-05-15T06:03:06.537649Z 0 [Note] IPv6 is available.
2017-05-15T06:03:06.537671Z 0 [Note] - ‘::’ resolves to ‘::’;
2017-05-15T06:03:06.537754Z 0 [Note] Server socket created on IP: ‘::’.
2017-05-15T06:03:06.571570Z 0 [Warning] InnoDB: Cannot open table mysql/server_cost from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to [url]http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html[/url] for how to resolve the issue.
2017-05-15T06:03:06.571611Z 0 [Warning] Failed to open optimizer cost constant tables

2017-05-15T06:03:06.572621Z 0 [Warning] InnoDB: Cannot open table mysql/time_zone_leap_second from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to [url]http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html[/url] for how to resolve the issue.
2017-05-15T06:03:06.572655Z 0 [Warning] Can’t open and lock time zone table: Table ‘mysql.time_zone_leap_second’ doesn’t exist trying to live without them
2017-05-15T06:03:06.573327Z 0 [Warning] InnoDB: Cannot open table mysql/servers from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to [url]http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html[/url] for how to resolve the issue.
2017-05-15T06:03:06.573357Z 0 [ERROR] Can’t open and lock privilege tables: Table ‘mysql.servers’ doesn’t exist
2017-05-15T06:03:06.582207Z 0 [Note] Event Scheduler: Loaded 0 events
2017-05-15T06:03:06.582530Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.7.18-14’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 Percona Server (GPL), Release 14, Revision 2c06f4d
2017-05-15T06:03:06.582552Z 0 [Note] Executing ‘SELECT * FROM INFORMATION_SCHEMA.TABLES;’ to get a list of tables using the deprecated partition engine. You may use the startup option ‘–disable-partition-engine-check’ to skip this check.
2017-05-15T06:03:06.582560Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-05-15T06:03:06.606414Z 0 [Note] End of list of non-natively partitioned tables

I tried running this even this failed. Is tokudb bugging should I move back to innodb ?

ps_tokudb_admin --enable -uroot -p

Installing TokuDB engine…
ERROR: Failed to install TokuDB engine plugin. Please check error log.

What should I do now I can open my tokudb database and their files?

2

Have you tried running mysql_upgrade as suggested in the error log?

3

Hi Hrvojem,
Yes tried nothing happened. I just cannot even start my whole db. Cause it say the mysql folder is missing? I need dire help? What should I do now ?

4

Hi Hrvojem,
I even tried with the --force option still same. Now is even worse the mysql just failed to even start.Below is my full log. I notice the whole of the mysql folder is missing and gone I Just rename it to mysql_old and its gone I did not delete.

2017-05-15T08:16:21.989685Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-05-15T08:16:21.991096Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.18-14) starting as process 17549 …
2017-05-15T08:16:21.994995Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-05-15T08:16:21.995025Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-05-15T08:16:21.995033Z 0 [Note] InnoDB: Uses event mutexes
2017-05-15T08:16:21.995039Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-05-15T08:16:21.995051Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2017-05-15T08:16:21.995058Z 0 [Note] InnoDB: Using Linux native AIO
2017-05-15T08:16:21.995315Z 0 [Note] InnoDB: Number of pools: 1
2017-05-15T08:16:21.995419Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-05-15T08:16:21.996746Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-05-15T08:16:22.000644Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-05-15T08:16:22.002515Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-05-15T08:16:22.014556Z 0 [Note] InnoDB: Crash recovery did not find the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
2017-05-15T08:16:22.015682Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-05-15T08:16:22.128603Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 3932160 bytes
2017-05-15T08:16:22.137007Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-05-15T08:16:22.137124Z 0 [Note] InnoDB: Setting file ‘./ibtmp1’ size to 12 MB. Physically writing the file full; Please wait …
2017-05-15T08:16:22.287003Z 0 [Note] InnoDB: File ‘./ibtmp1’ size is now 12 MB.
2017-05-15T08:16:22.289087Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-05-15T08:16:22.289113Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-05-15T08:16:22.289595Z 0 [Note] InnoDB: Waiting for purge to start
2017-05-15T08:16:22.339830Z 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.18-14 started; log sequence number 1221672
2017-05-15T08:16:22.340195Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-05-15T08:16:22.340261Z 0 [Note] Plugin ‘FEDERATED’ is disabled.
mysqld: Table ‘mysql.plugin’ doesn’t exist
2017-05-15T08:16:22.340458Z 0 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
2017-05-15T08:16:22.340534Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170515 4:16:22
2017-05-15T08:16:22.341311Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2017-05-15T08:16:22.345350Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2017-05-15T08:16:22.345373Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2017-05-15T08:16:22.368654Z 0 [Warning] CA certificate ca.pem is self signed.
2017-05-15T08:16:22.368754Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2017-05-15T08:16:22.369436Z 0 [Note] Server hostname (bind-address): ‘*’; port: 3306
2017-05-15T08:16:22.369489Z 0 [Note] IPv6 is available.
2017-05-15T08:16:22.369511Z 0 [Note] - ‘::’ resolves to ‘::’;
2017-05-15T08:16:22.369574Z 0 [Note] Server socket created on IP: ‘::’.
2017-05-15T08:16:22.403940Z 0 [Warning] Failed to open optimizer cost constant tables

2017-05-15T08:16:22.404125Z 0 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.user’ doesn’t exist
2017-05-15T08:16:22.404215Z 0 [ERROR] Aborting

5

Which folder is empty mysql or mysql_old? Did you update the datadir in your config to point to the new (mysql_old) folder, from the logs it looks like it’s still looking for data/logs in the /var/lib/mysql?

6

Hi,
There is no more folder mysql any more ready. I never change any thing on the datadir? Where to change that ? So what should I change now? I am using currently the safe version of mysqld.conf. But when I run ls -la I cant see the mysql_old folder any more ? There is no more mysql folder at all and neither the mysql_old folder.

7

Hi,

You said

. How did you rename it? Is the renamed folder also empty if you do ls -la?

8

Hi,
I just went to the mysql folder and rename it via my sftp application. There after I see it gone missing no more there. When I run this ls -la I dont see it any more neither via sftp? so what is best way to salvage this now ?

9

You don’t see neither the renamed folder or the (older) mysql folder
as well?

10

Yes I dont see the renamed folder.

11

The only option then if you don’t see neither is to restore the folder from the last backup.

12

Hi,
So are you saying completely yum remove mysql and reinstall and reinstall it?The problem now is that I cannot start my mysql. I did nnot do any back up cause I was just testing for tokudb. Why after upgrade this issue happened? Is there anything I should be careful with tokudb or its not stable yet. How can I restore from my .frm files the table schem?

13

Hi,
In future when I back up must i also back up the mysql folder? What is your opinion about tokudb is it safe to use or should I revert to innodb I am worried if I continue to use it and then I end up again another problem with the next upgrade? I really need you expert opinion on this to continue or keep working with innodb ? Please help on this.

14

I’m not sure what happened, I’ve tried testing this locally and I can’t reproduce it. The upgrade process should look like this (even without the TokuDB):

  1. run yum update
  2. run mysql_upgrade
  3. restart the mysql service

You shouldn’t see any errors in the error_log. In case you’ve done this and you’re unable to start the service or there are errors in the log please file a bug here ([url]https://bugs.launchpad.net/percona-server/+filebug[/url]).

To restore the table schema from .frm files you can use the mysqlfrm from the mysql-utilities (available here: [url]MySQL :: Download MySQL Utilities (Archived Versions))

To restore it you can run a command like this:

[root@centos7 ~]# mysqlfrm --diagnostic /var/lib/mysql/world/City.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for /var/lib/mysql/world/City.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `world`.`City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL,
`CountryCode` char(3) NOT NULL,
`District` char(20) NOT NULL,
`Population` int(11) NOT NULL,
PRIMARY KEY `PRIMARY` (`ID`),
KEY `CountryCode` (`CountryCode`)
) ENGINE=TokuDB;

#...done.
15

Hi,
I am also puzzle what happened till all failed. Ok in future what you suggest run yum update. There after run mysql_upgrade is it? I notice this mysql folder is very crucial is it a need for it to be backed up too? So now the only option is yum remove the whole mysql right or is there any other solution to this? Then after I install the brand new mysql. I still have my tokudb files etc can I restore them in my new installation? What is best way to move forward? How to restore from my current tokudb files is that possible rather ?

16

Hi,

Depends on the type of backups you’re running, but you should be able to restore it from both logical (taken with mysqldump) or physical (taken with XtraBackup or TokuBackup) if you backed up all the databases.

I think you should first figure out where the renamed folder went.

You can restore the files from the last backup, if you’ve used mysqldump to take the backup you can insert/restore them back the same way you’d do it for InnoDB files.

17

Hi,
I normally only took mysqldump backups. I never tried before xtrabackup or tokubackup. Actually this is my first time trying percona mysql previously I have been using oracle mysql which comes with centos. Unfortunately I did not do any backup neither mysqldump too. So with the existing folder of my db with the tokudb files is there anyway I can restore my db or it not possible?

For the renamed folder I have tried all method its just not there. Where else you think it would have gone or any commands I can run to find out cause I tried ls -la nothing is showing via it.

How mature and reliable is tokudb is there still many bug or is it safe to use for production?

18

My typical advice is that unless you have a very specific reason to use TokuDB, meaning that you have tested and understand the performance tradeoffs, the backup/restore limitations, etc…, then you should stick to InnoDB as you will not likely be very happy with your results. TokuDB is quite mature and used in production with multiple PB of data, but it is NOT intended to be a blind drop in for InnoDB to be ‘better’ in all ways. It solves a specific set of storage capacity problems (compression) and performance problems (write heavy workloads) for huge data sets which comes with trade-offs for read performance and less community feature support than InnoDB.

19

Hi Georgelorch,
I am expecting data to reach on monthly basis to be few hundred millions of lines in near future as weekly I am reaching almost 50 million lines. So far with oracle mysql things are working but off course if not so fast. I agree I am not going to do a blind swap to Tokudb I have been reading but I agree there is not much help and find quite a number of issue with it only thing is that was looking around the insert speed but you highlighted that there will be issue with read performance so is kind of tradeoff then. So what is your suggestion based on my scenario say monthly I will have data reaching around close to 300 million will innodb be good for it?

20

It depends on your row size too, if you assume a small-ish row of say 128 bytes, then 300 million records is only ~38.5 GB. Within in the grand scheme of database things, this is fairly small. Properly tuned InnoDB can handle this easily, it is handling TB of data elsewhere in production.