We don't have direct connection to TokuDB gurus from our JIRA anymore, so we need to re-report TokuDB issues (non-specific to MariaDB) to their JIRA: https://tokutek.atlassian.net .
I can do it on behalf, but I thought you might want to do it yourself as it will be easier for you to track and follow.
Back to the question, I can report it to TokuDB on your behalf, but they will probably request more information from you, will you be ready to provide it?
Hello, sorry for the delay...
Yes, I will be ready from the next week because now I'm on holiday 
Thank you!
Hello, I'm ready to provide all the information needed.
Thank you.
----Messaggio originale----
Da: Elena Stepanova (JIRA) jira@mariadb.atlassian.net
Inviato: lunedì 24 agosto 2015 23:20
A: Risato Stefano (IR SCR)
Oggetto: [JIRA] (MDEV-8541) TokuDB file map table doesn't reflect the real situation of the files on the disk
Elena Stepanova commented on MDEV-8541:
---------------------------------------
Back to the question, I can report it to TokuDB on your behalf, but they will probably request more information from you, will you be ready to provide it?
–
This message was sent by Atlassian JIRA
(v7.0.0-OD-02-247#70102)
I have refiled it as https://tokutek.atlassian.net/browse/DB-872, so you can track it there. But I suppose for efficient communication you will need to have an account in TokuDB JIRA.
Sadly, the upstream report does not show any activity... We will still have to wait for Percona to work on it, I don't think we have manpower now to start digging deep into TokuDB.
Thank you. I started with a fresh install with version 10.1.7, and recreated the databases (about 300GB of compresseded TokuDB files) and in two weeks I did't have any problem. I hope for the future...
Bye
----Messaggio originale----
Da: Elena Stepanova (JIRA) jira@mariadb.atlassian.net
Inviato: lunedì 28 settembre 2015 23:41
A: Risato Stefano (IR SCR)
Oggetto: [JIRA] (MDEV-8541) TokuDB file map table doesn't reflect the real situation of the files on the disk
Elena Stepanova updated MDEV-8541:
----------------------------------
Labels: tokudb upstream (was: need_feedback tokudb upstream)
Sadly, the upstream report does not show any activity... We will still have to wait for Percona to work on it, I don't think we have manpower now to start digging deep into TokuDB.
–
This message was sent by Atlassian JIRA
(v7.0.0-OD-07-011#70107)
steris, how did it go with the fresh install?
Please comment to re-open (or comment directly on the upstream report) if you encounter the problem again.
The problem is still present (version 10.1.8). I have executed the following steps:
-create a new db
-create a new table
-populate the table with enough data (1GB in my case; apparently if you put a few records only, the files on disk are not touched and you can access the data even after removing the files)
-delete the .tokdb files in the data directory
-drop the table
-eventually restart the service
-try to re-create the same table:
ERROR 1005 (HY000): Can't create table `bbb`.`preventivi` (errno: 17 "File exists")
With the last two versions I did't have table corruption after a fresh new install, so the problem wasn't relevant for me (but I had serious problem during the upgrade from 10.1.7 to 10.1.8 on one on the two very similar machines; something were wrong and I needed to fresh install again losing all the data).
steris,
Actually, I closed it with a wrong comment. I don't find it surprising that the data goes inconsistent after dropping per-table TokuDB files – in fact, it will be the same if you drop a per-table tablespace (*.ibd) and *.frm for an InnoDB table – since the system tablespace still has information about that table, it won't allow to create a new one, and won't allow to "drop" the old one because the files don't exist.
I was rather hoping for upstream (TokuDB) to suggest a workaround for the problem, how to get the data back to the consistent state in a best possible way. Since we are not getting any response, I would suggest to try this dirty trick:
Lets suppose you had create table test.t1 (pk int primary key, c char(255)) engine=TokuDB, populated with some data, and TokuDB files for this table got corrupted;
- remove <datadir>/test_t1* files (there are two, "main" and "status"); store the names of the files you have just removed, e.g.
$ rm data/_test_t1_*rm: remove regular file `data/_test_t1_main_6_1_1b_B_0.tokudb'? yrm: remove regular file `data/_test_t1_status_3_1_1b.tokudb'? y - start server with TokuDB enabled;
- run create table test.t2 (pk int primary key, c char(255)) engine=TokuDB where the table has the exact same definition as t1, but a unique name (create it in the same schema where t1 belonged to);
- shutdown the server gracefully;
- copy TokuDB files for t2 into corresponding names from t1 (note – not only the table name differs, some other symbols will differ too, use the exact names that you removed previously), e.g.
$ cp data/_test_t2_main_16_2_1b.tokudb data/_test_t1_main_6_1_1b_B_0.tokudb$ cp data/_test_t2_status_16_1_1b.tokudb data/_test_t1_status_3_1_1b.tokudb - check if t1.frm still exists in the schema's subfolder; if it doesn't (e.g. if you tried to drop the table on the way), also copy t2.frm to t1.frm;
- start the server;
- both t1 and t2 should be accessible now, you can drop t1 and re-create it.
Please let me know if it works for you.
Ok, I tried the solution and it worked, only I had to add the -a switch to the cp commands to preserve the file permissions (copied as root) elsewhere mariadb can't delete the files and the trick doesn't work.
I hope to don't have to do this when I'll go in production with the linux server (actually I'm using windows with most Aria tables), since my tables usually has a lot of index and a lot of partitions, and therefore hundreds of files to be copied and renamed, impossible to do without a script.
From my point of view the priority should be:
-let the server start anyway to reduce the downtime, and then give an error when accessing the inconsistent table;
-allow "simple" table delete/recreate/restore from backup;
-allow to recover most of the data also if they are not updated to last transaction (ignoring the log).
Maybe this is obvious
Thank you again!
PS: I had similar headache when I upgraded from 10.1.7 to 10.1.8 also with some InnoDB tables, but the situation was too complex to report, and finally I've reinstalled MariaDB and restored the DBs cloning the data hard disk from the second test PC, where the upgrade was ok.
----Messaggio originale----
Da: Elena Stepanova (JIRA) jira@mariadb.atlassian.net
Inviato: venerdì 27 novembre 2015 18:24
A: Risato Stefano (IR SCR)
Oggetto: [JIRA] (MDEV-8541) TokuDB file map table doesn't reflect the real situation of the files on the disk
Elena Stepanova commented on MDEV-8541:
---------------------------------------
steris,
Actually, I closed it with a wrong comment. I don't find it surprising that the data goes inconsistent after dropping per-table TokuDB files – in fact, it will be the same if you drop a per-table tablespace (*.ibd) and *.frm for an InnoDB table – since the system tablespace still has information about that table, it won't allow to create a new one, and won't allow to "drop" the old one because the files don't exist.
I was rather hoping for upstream (TokuDB) to suggest a workaround for the problem, how to get the data back to the consistent state in a best possible way. Since we are not getting any response, I would suggest to try this dirty trick:
Lets suppose you had create table test.t1 (pk int primary key, c char(255)) engine=TokuDB, populated with some data, and TokuDB files for this table got corrupted;
- remove <datadir>/test_t1* files (there are two, "main" and "status"); store the names of the files you have just removed, e.g.
$ rm data/_test_t1_*rm: remove regular file `data/_test_t1_main_6_1_1b_B_0.tokudb'? yrm: remove regular file `data/_test_t1_status_3_1_1b.tokudb'? y - start server with TokuDB enabled;
- run create table test.t2 (pk int primary key, c char(255)) engine=TokuDB where the table has the exact same definition as t1, but a unique name (create it in the same schema where t1 belonged to);
- shutdown the server gracefully;
- copy TokuDB files for t2 into corresponding names from t1 (note – not only the table name differs, some other symbols will differ too, use the exact names that you removed previously), e.g.
$ cp data/_test_t2_main_16_2_1b.tokudb data/_test_t1_main_6_1_1b_B_0.tokudb$ cp data/_test_t2_status_16_1_1b.tokudb data/_test_t1_status_3_1_1b.tokudb - check if t1.frm still exists in the schema's subfolder; if it doesn't (e.g. if you tried to drop the table on the way), also copy t2.frm to t1.frm;
- start the server;
- both t1 and t2 should be accessible now, you can drop t1 and re-create it.
Please let me know if it works for you.
–
This message was sent by Atlassian JIRA
(v7.1.0-OD-01-053#71000)
This continues to be a problem, particularly in cases where files have been lost for whatever reason.
Apparently, this is resolved in an upcoming version. Fixed in https://tokutek.atlassian.net/browse/FT-733
For whatever reason, the solution for this bug is being removed from the upstream TokuDB code – see https://github.com/MariaDB/server/commit/dfeff40706
If MariaDB maintainers are going to neuter the upstream solution, they need to provide their own fix.
We might merge the upstream solution later, when it gets more mature. It's currently marked Experimental, and TokuDB storage engine in MariaDB is marked stable.
That commit message is pretty misleading, but your explanation makes more sense. It's definitely a mandatory tool when dealing with certain recovery cases - or, as in my case, when dealing with a table that was corrupted by an earlier bug in TokuDB that has since been fixed.
Having a similar situation, where I can't drop and recreate a table with a corrupted partition, with error "File exists", I report my workaround to resolve the problem (MariaDB 10.2.21):
-create an identical table in another database (tmp), with the same partitions as the original table;
-here the trick, copy the new files over the old files (if exists), looking at their names in the table information_schema.TokuDB_file_map, but using old file names. To do this, adapt this query to reflect your paths, db and table names, and run it:
SELECT CONCAT('cp /media/data/data/', MID(new.internal_file_name,3), ' /media/data/data/four/', MID(old.internal_file_name,10)) command
FROM information_schema.TokuDB_file_map old
JOIN information_schema.TokuDB_file_map new ON old.table_name=new.table_name AND old.table_dictionary_name=new.table_dictionary_name
WHERE old.table_schema='dbname' AND new.table_schema='tmp' AND old.table_name LIKE 'tablename#P%'
-rows generated are similar to this one:
cp /media/data/data/tmp/four_richiesta_P_p235_key_COD_FISCALE_CL_1f6c1337_6_1d.tokudb /media/data/data/four/four_richiesta_P_p235_key_COD_FISCALE_CL_18e4b633_6_1d.tokudb
-export rows to a text editor and save as script
-run the script (with sudo)
-if necessary change permissions to the created files (sudo chown mysql:mysql /media/data/data/dbname/tablename_P*)
-drop the table in sql (should't return errors this time)
-recreate the table with partitions in original database