Hi, We have an old instance of MariaDB 10.3.9/10.3.39, with historical data. Every month I copy an old MyISAM partition (binary files) from the same version of the engine (but a different instance) to a big volume and replace an empty partition in the database with symlinks to the newly copied two files (MYD, MYI - when the engine is stopped). I have been doing it for a year or two, checking each time the data are accessible afterwards. This month it happened, that the data are not accessible anymore, and the database throwing an exception: *Got error 140: "Wrong create options" from storage engine MyISAM.* I tried doing it in a new instance, each time I replace a partition with a link to file elsewhere, the error appeared again. Does anyone have any ideas, why it's showing the error and how to avoid it? I cannot recall changing anything in the database configuration, upgrading any database, or doing any other changes. Thanks for any help you can provide, Jan
Hi, Jan, error 140 on *open* (not on create) can basically happen only for two reasons: * you have a symlinked table and realpath() failed * you have a symlinked table and symlink points into a datadir make sure all your symlinks are valid and point outside of datadir. On Feb 20, Jan Křístek via discuss wrote:
Hi,
We have an old instance of MariaDB 10.3.9/10.3.39, with historical data. Every month I copy an old MyISAM partition (binary files) from the same version of the engine (but a different instance) to a big volume and replace an empty partition in the database with symlinks to the newly copied two files (MYD, MYI - when the engine is stopped). I have been doing it for a year or two, checking each time the data are accessible afterwards.
This month it happened, that the data are not accessible anymore, and the database throwing an exception:
*Got error 140: "Wrong create options" from storage engine MyISAM.*
I tried doing it in a new instance, each time I replace a partition with a link to file elsewhere, the error appeared again.
Does anyone have any ideas, why it's showing the error and how to avoid it? I cannot recall changing anything in the database configuration, upgrading any database, or doing any other changes.
Thanks for any help you can provide, Jan
Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
Hi Sergei, Thanks for quick reply. You were right. In the new instance, I had the original target partitions within the datadir. After moving it out and checking the file access permissions it started working. In the original setup, it still does not work. Could it be a problem, that the target partitions are on the NFS volume on the Synology NAS? I have checked and rechecked the permissions, and they seems to be correct. Jan On Tue, 20 Feb 2024 at 10:46, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Jan,
error 140 on *open* (not on create) can basically happen only for two reasons:
* you have a symlinked table and realpath() failed * you have a symlinked table and symlink points into a datadir
make sure all your symlinks are valid and point outside of datadir.
On Feb 20, Jan Křístek via discuss wrote:
Hi,
We have an old instance of MariaDB 10.3.9/10.3.39, with historical data. Every month I copy an old MyISAM partition (binary files) from the same version of the engine (but a different instance) to a big volume and replace an empty partition in the database with symlinks to the newly copied two files (MYD, MYI - when the engine is stopped). I have been doing it for a year or two, checking each time the data are accessible afterwards.
This month it happened, that the data are not accessible anymore, and the database throwing an exception:
*Got error 140: "Wrong create options" from storage engine MyISAM.*
I tried doing it in a new instance, each time I replace a partition with a link to file elsewhere, the error appeared again.
Does anyone have any ideas, why it's showing the error and how to avoid it? I cannot recall changing anything in the database configuration, upgrading any database, or doing any other changes.
Thanks for any help you can provide, Jan
Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
Hello, I'd like to have some advice about partitioning. I have a database with several tables like this one:CREATE TABLE `my_timeranges_table` ( `id` INT(11), `time_range_beg` INT UNSIGNED, `time_range_end` INT UNSIGNED, <plus some irrelevant data fields>, PRIMARY KEY (`id`,`time_range_beg`), KEY `time_range_end` (`time_range_end`) ) ENGINE=MyISAM PARTITION BY RANGE (`time_range_beg`) (<some partitioning scheme>); Each row on these tables are expected to be unique for both `id`+`time_range_beg` fields, so I setup the primary key on this, but this could be `id`+`time_range_end` as well, knowing that the defined time ranges never overlap. Some of these tables are very big (currently 150,000,000 or even 500,000,000 rows), and they are constantly growing by the end. As they are not very efficient to query, I am trying several kinds of partitioning in order to find the most effective one. The problem is that, according to partition scheme, if I change the “PARTITION BY RANGE” field, I can get the following message: “A PRIMARY KEY must include all columns in the table's partitioning function”. I order to fix this, I need to change the primary key, for setting one including the field I want to use. But if I do, for example: ALTER TABLE `my_timeranges_table` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`time_range_end`); I get the same error message because the partitioning is already set on the primary key that I want to drop before adding the new one. So what I actually have to do is the following: ALTER TABLE`my_timeranges_table` REMOVE PARTITIONING; ALTER TABLE `my_timeranges_table` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`time_range_end`); ALTER TABLE`my_timeranges_table` PARTITION BY RANGE (`time_range_end`) (<new partitioning scheme>); But if I do this, as my tables are very big, this implies copying all of their contents on each statement, which may need about *several hours* for *each* (on a not very performant computer, I admit). In order to do it more quickly, I tried: ALTER TABLE`my_timeranges_table` REMOVE PARTITIONING, DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`time_range_end`), PARTITION BY RANGE (`time_range_end`) (<new partitioning scheme>); But unfortunately, this doesn't work: MariaDB doesn't seem to accept changing partitions and index keys in the same statement (despite the fact that the ALTER statement definition <https://mariadb.com/kb/en/alter-table/> syntax would theoretically allow it). Thus I get another error message. So I have some questions: 1. Is it really impossible to combine all these changes into a single statement, in order to copy the table only once, and save a lot of hours? Or is this planned to some future version? 2. It is said that all columns in a table's partitioning function have to be included in a primary key. Is this really necessary? Couldn't it just have to be included in any existing index key, not necessarily the primary one? Because of course, I cannot have several primary keys. But if I could have more than one kind of index keys for sustaining partitioning, at least I could add the needed one along with the primary key, and then reduce the needed statements to execute at the number of two, thus copying the table only twice. I'm not sure if this message was a question or a feature request. Regards, Gingko
Hi Gingko, On Mon, Aug 5, 2024 at 4:38 PM Gingko via discuss <discuss@lists.mariadb.org> wrote:
So what I actually have to do is the following:
ALTER TABLE `my_timeranges_table` REMOVE PARTITIONING; ALTER TABLE `my_timeranges_table` DROP PRIMARY KEY, ADD PRIMARY KEY … ALTER TABLE `my_timeranges_table` PARTITION BY RANGE …
There is also a possibility to invoke ALTER TABLE … EXCHANGE PARTITION in order to convert each partition into a normal table and then to alter the table. That would seem to remove two full-copy steps while removing and adding partitioning. After converting each table (former partition), you'd create an empty partitioned table corresponding to the new PRIMARY KEY definition and EXCHANGE PARTITION from the individual tables again. I am not deeply familiar with the partitioning code, and I did not test the above suggestion. From the storage engine point of view, each partition or subpartition is just a funnily named table, and ALTER TABLE…EXCHANGE PARTITION is just a RENAME TABLE.
Is it really impossible to combine all these changes into a single statement, in order to copy the table only once, and save a lot of hours?
I think yes, it is impossible. The grammar of the SQL parser does not allow partition management operations such as REMOVE PARTITIONING to be combined with index management operations such as ADD INDEX.
Or is this planned to some future version? It is said that all columns in a table's partitioning function have to be included in a primary key. Is this really necessary? Couldn't it just have to be included in any existing index key, not necessarily the primary one? Because of course, I cannot have several primary keys. But if I could have more than one kind of index keys for sustaining partitioning, at least I could add the needed one along with the primary key, and then reduce the needed statements to execute at the number of two, thus copying the table only twice.
I can’t answer these questions, but I would assume that unless and until support for global indexes across all partitions is implemented, the primary key values must be disjoint between partitions. Best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc
Hi Marko, Thank you for the ALTER TABLE … EXCHANGE PARTITION idea, but I don't think it has any chance to work. Even if I keep the same timestamps as partition limits, several “near to the boundary” rows in my new partition scheme are expected to not fall in the same partition as before. Regards, Gingko ------------------------------------------------------------------------ *De :* Marko Mäkelä [mailto:marko.makela@mariadb.com] *Envoyé :* lundi 5 août 2024 à 4:09 PM *Pour :* Gingko *Cc :* MariaDB discuss *Objet :* [MariaDB discuss] About partitioning change
[…] There is also a possibility to invoke ALTER TABLE … EXCHANGE PARTITION in order to convert each partition into a normal table and then to alter the table. That would seem to remove two full-copy steps while removing and adding partitioning. After converting each table (former partition), you'd create an empty partitioned table corresponding to the new PRIMARY KEY definition and EXCHANGE PARTITION from the individual tables again.
I am not deeply familiar with the partitioning code, and I did not test the above suggestion. From the storage engine point of view, each partition or subpartition is just a funnily named table, and ALTER TABLE…EXCHANGE PARTITION is just a RENAME TABLE.
[…] Best regards,
Marko
Hi Marko, Thinking again about that … You said that : ALTER TABLE `my_timeranges_table` REMOVE PARTITIONING; ALTER TABLE `my_timeranges_table` DROP PRIMARY KEY, ADD PRIMARY KEY … ALTER TABLE `my_timeranges_table` PARTITION BY RANGE … cannot be combined to: ALTER TABLE `my_timeranges_table` REMOVE PARTITIONING, DROP PRIMARY KEY, ADD PRIMARY KEY …, PARTITION BY RANGE … I understand that it may be not possible to do this by copying the table only once. But does this really prevent allowing the combined statement? At least by doing it the same way as the separated one (but using up to three temporary tables). It seems to me that it could be nevertheless be somewhat optimized (because the distinct statements also imply rebuilding the index three times whereas this index will not have to be used in the middle), by doing it that way : 1. Copy the table to temporary table #1 for partition removal _without actually rebuilding the indexes_. 2. Copy temporary table #1 to temporary table #2 for executing the index management operations, _still without actually rebuilding the indexes_. Temporary table #1 can be deleted after the copy. 3. Copy temporary table #2 to temporary table #3 for building the new partitioning scheme, _effectively rebuilding the index only at the end of that part_. Temporary table #2 can be deleted after the copy and before the index rebuild. This would certainly save a lot of time because the indexation part is very important in the time needed for ALTER tables operations. Also, in step 2, you change only the index file, keeping the data unchanged, so _for the data part_, temporary table #1 could be simple renamed (instead of copied) to temporary table #2, also saving some time in the process. Last but not least, the whole operation being expected to be atomic, if something fails in the middle, you just have to delete all temporary files, and nothing will be changed at all. Regards, Gingko
*De :* Marko Mäkelä [mailto:marko.makela@mariadb.com] *Envoyé :* lundi 5 août 2024 à 4:09 PM *Pour :* Gingko *Cc :* MariaDB discuss *Objet :* [MariaDB discuss] About partitioning change Hi Gingko,
[…]
Is it really impossible to combine all these changes into a single statement, in order to copy the table only once, and save a lot of hours?
I think yes, it is impossible. The grammar of the SQL parser does not allow partition management operations such as REMOVE PARTITIONING to be combined with index management operations such as ADD INDEX.
[…]
Best regards,
Marko
Hi Gingko, On Wed, Aug 7, 2024 at 3:28 PM Gingko <from_mariadb@gingko.ovh> wrote:
You said that :
ALTER TABLE `my_timeranges_table` REMOVE PARTITIONING; ALTER TABLE `my_timeranges_table` DROP PRIMARY KEY, ADD PRIMARY KEY … ALTER TABLE `my_timeranges_table` PARTITION BY RANGE …
cannot be combined to:
ALTER TABLE `my_timeranges_table` REMOVE PARTITIONING, DROP PRIMARY KEY, ADD PRIMARY KEY …, PARTITION BY RANGE …
Yes, this is foremost a limitation of the SQL parser, as well as a limitation of how partition and operations around them have been implemented above the storage engine layer.
I understand that it may be not possible to do this by copying the table only once.
Have you considered a CREATE TABLE of a differently partitioned table followed by INSERT INTO…SELECT from the old-format partitioned table? That would involve copying the data only once. Possibly, to make this more efficient, you should initially create the new table without any secondary indexes, and execute ALTER TABLE…ADD INDEX…ADD INDEX at the end.
It seems to me that it could be nevertheless be somewhat optimized (because the distinct statements also imply rebuilding the index three times whereas this index will not have to be used in the middle), by doing it that way :
Copy the table to temporary table #1 for partition removal without actually rebuilding the indexes. Copy temporary table #1 to temporary table #2 for executing the index management operations, still without actually rebuilding the indexes. Temporary table #1 can be deleted after the copy. Copy temporary table #2 to temporary table #3 for building the new partitioning scheme, effectively rebuilding the index only at the end of that part. Temporary table #2 can be deleted after the copy and before the index rebuild.
This would certainly save a lot of time because the indexation part is very important in the time needed for ALTER tables operations.
I am mostly involved with the InnoDB storage engine. All InnoDB tables are index-organized, that is, the data is stored along with the primary key, and secondary indexes contain (secondary_key,primary_key) tuples that point to the primary key index. What you are suggesting could already be possible when using heap-organized tables with a storage engine that supports it. I am not sure if ENGINE=Aria supports DISABLE KEYS and ENABLE KEYS, which originated in ENGINE=MyISAM.
Last but not least, the whole operation being expected to be atomic, if something fails in the middle, you just have to delete all temporary files, and nothing will be changed at all.
That is a challenge. To my understanding, many operations on partitioned tables are not really atomic internally. RENAME TABLE of a partitioned table might be, starting with MariaDB Server 10.6. Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc
participants (4)
-
Gingko -
Jan Křístek -
Marko Mäkelä -
Sergei Golubchik