Just following up if there is any update on making “WITHOUT VALIDATION“ option available for exchange partitions.

It will be nice to have that option because we are using partition exchange functionality for deploying large amount of data in the production environment.

Thanks a ton!

Please review bb-11-midenok

Generally everything looks OK, but :

1. I am afraid this tree without full version name do not allow to make tests correctly (at least upgrade tests)
2. Add version end marker after your tests (always should be done if it is absent in the test file and your test is the last one)
3. IMHO it lack of "strange/error" tests, ad at least partitioning with LIST and hash to the tests

The "supplemental feature" (making THAN keyword optional) wasn't a part of the original task, isn't required for the functionality, doesn't seem to be a part of any compatibility effort (as far as I can see, MySQL 8.x does not support it), and in general looks more like a typo than a valid expression:

create table t (a int) partition by range (a) (p0 values less (100));

Right. midenok, please revert this.

We need proper documentation for how an admin should handle a corrupt table produced with the use of WITHOUT VALIDATION clause. The MySQL manual is no help here, it claims that it

can be performed using REPAIR TABLE or ALTER TABLE ... REPAIR PARTITION

however it is not true (not even for MySQL):

mysql> ALTER TABLE t EXCHANGE PARTITION p WITH TABLE t_exchange WITHOUT VALIDATION;

Query OK, 0 rows affected (0.44 sec)

mysql> check table t;

+--------+-------+----------+-----------------------------------------------+

| Table  | Op    | Msg_type | Msg_text                                      |

+--------+-------+----------+-----------------------------------------------+

| test.t | check | error    | Partition p returned error                    |

| test.t | check | error    | Unknown - internal error 160 during operation |

+--------+-------+----------+-----------------------------------------------+

2 rows in set (0.05 sec)

mysql> repair table t;

+--------+--------+----------+-----------------------------------------------+

| Table  | Op     | Msg_type | Msg_text                                      |

+--------+--------+----------+-----------------------------------------------+

| test.t | repair | error    | Partition p returned error                    |

| test.t | repair | error    | Unknown - internal error 160 during operation |

+--------+--------+----------+-----------------------------------------------+

2 rows in set (0.02 sec)

mysql> alter table t repair partition p;

+--------+--------+----------+-----------------------------------------------+

| Table  | Op     | Msg_type | Msg_text                                      |

+--------+--------+----------+-----------------------------------------------+

| test.t | repair | error    | Partition p returned error                    |

| test.t | repair | error    | Unknown - internal error 160 during operation |

+--------+--------+----------+-----------------------------------------------+

2 rows in set (0.02 sec)

mysql> check table t;

+--------+-------+----------+-----------------------------------------------+

| Table  | Op    | Msg_type | Msg_text                                      |

+--------+-------+----------+-----------------------------------------------+

| test.t | check | error    | Partition p returned error                    |

| test.t | check | error    | Unknown - internal error 160 during operation |

+--------+-------+----------+-----------------------------------------------+

2 rows in set (0.04 sec)

Maybe it should be ALTER TABLE .. REBUILD PARTITION, at least it seems to work.

It may be useful to add a status variable Feature_without_validation or something like that counting specifically "without".
The reason is that it may cause a variety of issues, so when we later investigate an obscure problem reported by a user, it may help to know that it could have been used.

I like the idea of logging operations which can make the database inconsistent, also if SET FOREIGN_KEY_CHECKS=0; is used, but we might get too many variables. Adding a warning to the server log could be a first simple solution, and it would not be just a counter but would include a timestamp. I am not sure about the overhead a new log file would have, which would only include such cases. serg, what do you think?

Feature_without_validation is really simple and can likely be done either way. Also we might, perhaps, mark the frm as "tainted", write a special flag there, it's not difficult.

SET FOREIGN_KEY_CHECKS=0 is a different story. We cannot mark it in the frm, as DMLs don't modify frm. A warning in the server log is simple, sure. It won't show, though, what tables were affected. But there's also a question — set foreign_key_checks=0 can easily violate data consistency, should it be a privileged operation? Same for unique_checks and check_constraint_checks. Either way, this discussion doesn't really belong here.

After some discussion with serg I personally would conclude that:

• Feature_without_validation would not help much as it would be reset after server restart, but a partition might still be inconsistent
• A flag in the .frm file could help at least to print a warning when running CHECK TABLE QUICK. But here the question is if a CHECK PARTITION does not anyhow check an inconsistency anyhow

Feature_x counters are not an ultimate weapon, but it doesn't mean they are useless. As happens quite often, if they don't show anything, it doesn't mean much, but if it does show something, it is useful information.

1) Production instances aren't typically restarted without a reason, and issues with invalid tables won't necessarily lead to a crash, so it may well be that a problem under investigation originated from the ongoing server session;
2) Some users/customers do have regular monitoring which records status values, and may be able to provide the snapshot from before the server restarted;
3) Real-life instances tend to perform a typical repeating workload, so if they performed a without validation operation in the current session, there is a good chance they did it before.

That said, I'm not going to insist on adding the counter, I just don't understand why not have it, especially if apparently there isn't any realistic plan to have a more reliable diagnostics. After all, Feature_x is quite a standard thing in MariaDB, not any innovation.

elenst you are definitely right, Feature_x even only as a counter of the used feature still makes sense. And therefor also indicates that partitions should be checked as long as it is >0.

serg, could the flag you mentioned for the .frm file be used in information_schema.partitions to mark a partition which was not validated?

yes

unfortunately, ALTER TABLE ... EXCHANGE PARTITION does not touch the .frm file, so it cannot add a flag there.
I'll print a warning in the log