summaryrefslogtreecommitdiff
path: root/mysql-test
Commit message (Collapse)AuthorAgeFilesLines
* Add referenced table to prelocking list during CREATE TABLE with FKfk_in_progressNikita Malyavin2021-03-1014-48/+59
|
* MDEV-21053 Crash safety of foreign key DDLAleksey Midenkov2021-03-099-44/+3876
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | DDL log extensions (and other stuff): - New `.file` handler which means treat ddl_log_entry->name and ddl_log_entry->from_name as file names. Previously they were used without .frm extension. - New ddl_log_entry_code: DDL_TRY_LOG_ENTRY_CODE ('t') which means try log action but not fail the whole chain if action fails. Previously it failed action chain unconditionally dispite the comment: /* Write to error log and continue with next log entry */ - Better ddl_log tracing. New classes: - ddl_log_info: tracks DDL logging state; - FK_backup: contains update FRM procedures; - FK_share_backup, FK_ddl_backup: track (and possibly rollback) foreign key changes for TABLE_SHARE; - FK_backup_storage: collection of FK_ddl_backup, can rollback the whole bunch of shares in memory and FRMs on disk. Other: - Test case for multi-rename.
* MDEV-21652 Foreign key migration from old versionAleksey Midenkov2021-03-0911-0/+933
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | == fk_check_legacy_storage(), fk_upgrade_legacy_storage() == fk_check_legacy_storage() checks whether upgrade is required for the given table name by looking SYS_FOREIGN table for corresponding record existence. fk_upgrade_legacy_storage() does the upgrade routine which includes getting the foreign keys from SYS_FOREIGN[_COLS], updating the foreign/referenced shares as well as their FRM files, deleting the records from SYS_FOREIGN[_COLS] tables. Both routines utilize the internal SQL for SYS_FOREIGN[_COLS] processing. == Upgrade foreign keys via backoff action == When table is opened fk_check_legacy_storage() detects whether upgrade is required and HA_ERR_FK_UPGRADE is returned to SQL layer which then handles this error by backoff action from Open_table_context where table is opened again with HA_OPEN_FOR_REPAIR flag which indicates that fk_upgrade_legacy_storage() is required. After fk_upgrade_legacy_storage() is done fk_check_legacy_storage() is checked again to ensure that SYS_FOREIGN[_COLS] are empty for the given table. == Check foreign/referenced indexes existence == fk_upgrade_legacy_storage() via fk_upgrade_push_fk() fails if there are no indexes in foreign/referenced tables for the given data acquired from SYS_FOREIGN[_COLS]. == Internal SQL: select into both func and vars extension == fk_upgrade_legacy_storage() utilizes syntax extension in internal SQL: FETCH c INTO fk_upgrade_create_fk() fk_id, unused; Thus the data is fetched into both fk_upgrade_create_fk() function and fk_id variable. == Rename table, rename column, drop table, drop column handling == When foreign table is opened it is automatically upgraded by backoff action. But if the referenced table is altered or dropped first there is no chance for the foreign table to get the correct data. So the SYS_FOREIGN_[COLS] must be kept in sync with the above DDL operations in respect of referenced names. DROP TABLE for the referenced table is disabled as usual. DROP TABLE, DROP COLUMN relied in 10.5 on dict_foreign_t cached data for the referenced tables. Now there is no such possibility for the legacy data so we have to look at SYS_FOREIGN_[COLS] directly. == Reverted some SYS_FOREIGN(_COLS) routines == Rename table and rename column handling was done in sync with SYS_FOREIGN_[COLS] in 10.5. To retain the above DDL consistency for the referenced tables we still use that old synchronization code. == ALGORITHM=COPY handling == Since we cannot faingrain ALGORITHM=COPY in innobase handler it is disabled for the referenced tables unless the foreign tables are upgraded. The check is done in create_table_info_t::create_table() and is equivalent to DROP TABLE check as we are actually dropping the old table after the copy routine is done. == WITH_INNODB_FOREIGN_UPGRADE macro == Every SYS_FOREIGN_[COLS] handling is wrapped inside WITH_INNODB_FOREIGN_UPGRADE compilation macro. When this macro is disabled the foreign key upgrade is not possible. Future versions will obsolete the upgrade procedure completely and the wrapped code will be removed. == innodb_eval_sql debug interface == Test cases must fill SYS_FOREIGN_[COLS] with data. This is done with setting the new innodb_eval_sql debug variable. The value of that variable is processed by que_eval_sql(). == Some syntax error-friendly parser handling == que_eval_sql() was unfriendly on syntax errors: it just failed with SIGABRT exception. To keep the server alive some frequent syntax errors are now returned as DB_ERROR from que_eval_sql(). == Drop empty SYS_FOREIGN[_COLS] after import == fk_create_legacy_storage debug flag to create SYS_FOREIGN[_COLS] on innodb_eval_sql. fk_release_locks() iterates through all indexes and pages and releases locks on SUPREMUM records placed there previously by SELECT FOR UPDATE. Also it releases all table locks.
* MDEV-21052 InnoDB foreign key refactoring for TABLE_SHARE::foreign_keysAleksey Midenkov2021-03-0964-1254/+376
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Refactor dict_load_foreigns() for synchronising TABLE_SHARE foreign data with dict_table_t cache. Remove a number of routines working with SYS_FOREIGNS and SYS_FOREIGN_COLS. innobase_update_foreign_try() is now used solely for ER_FK_INCORRECT_OPTION check. Prelock parent tables as well as child tables. This is done for the case when parent table doesn't know about its children when they created before parent with foreign_key_checks=0. Opening the parent table initiates fk_resolve_referenced_keys() which updates its referenced_keys. Due to CREATE TABLE doesn't not know about "illegal" children it can not check for foreign consistency. F.ex. this would succeed: set foreign_key_checks= 0; create table child (fk int references parent (id)) engine=innodb; set foreign_key_checks= 1; create table parent (id bigint primary key) engine=innodb; In the above case dict_load_foreigns() deduces which tables are unknown to opened parent (tables_missing) and reloads their foreign data via recursion. Infinite recursion is not possible via test case: a table cannot be "parent after child" and "child before parent" simultaneously. Though infinite recursion is possible via malicously crafted FRM file, there is no protection from that at InnoDB level but there is protection at SQL level: thd->fk_circular_check. Later though it would not allow DML on child as well as on parent (see innodb.foreign_key MDEV-10083). So this is pretty acceptable: foreign_key_checks is unnormal setting, checking parent on CREATE TABLE would impose all frms scanning which is not acceptable. ha_innobase::open() then synchronizes these referenced_keys with its referenced_set cache by calling dict_load_foreigns(). Disable self-references on same column. The Bug 12902967 restricted them on some condition of "same column/index" (see innodb_bug12902967.test), though such self-references were not completely disabled (see other self-ref cases changed in this patch). It is not clear why they worked if they are "self-refs on same column/index".
* table_flags fixAleksey Midenkov2021-03-096-237/+17
| | | | - "debug" deviation is now gone.
* MDEV-20865 Store foreign key info in TABLE_SHAREAleksey Midenkov2021-03-09108-507/+1301
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 1. Access foreign keys via TABLE_SHARE::foreign_keys and TABLE_SHARE::referenced_keys; foreign_keys and referenced_keys are objects in TABLE_SHARE. 2. Remove handler FK interface: - get_foreign_key_list() - get_parent_foreign_key_list() - referenced_by_foreign_key() 3. Invalidate referenced shares on: - RENAME TABLE - DROP TABLE - RENAME COLUMN - ADD FOREIGN KEY When foreign table is created or altered by the above operations all referenced shares are closed. This blocks the operation while any referenced shares are used (when at least one its TABLE instance is locked). 4. Update referenced shares on: - CREATE TABLE On CREATE TABLE add items to referenced_keys of referenced shares. 5. Invalidate foreign shares on: - RENAME TABLE - RENAME COLUMN The above-mentioned blocking takes effect. 6. Check foreign/referenced shares consistency on: - CHECK TABLE 7. Temporary change until MDEV-21051: InnoDB fill foreign key info at handler open(). On first TABLE open FK info is loaded from storage engine into TABLE_SHARE. All referenced shares (if any exist) are closed. This leads to blocking of first time foreign table open while referenced tables are used. Restore states of referenced shares in case of errors. FOREIGN_KEY_INFO refactored to FK_info holding Lex_cstring (MDEV-21311) Converge Foreign_key and supplemental generated Key together mysql_prepare_create_table() does data validation and such utilities as automatic name generation. But it does that only for indexes and ignores Foreign_key objects. Now as Foreign_key data needs to be stored in FRM files as well this processing must be done for them like for any other Key objects. Replace Key::FOREIGN_KEY type with Key::foreign flag of type Key::MULTIPLE and Key::generated set to true. Construct one object with Key::foreign == true instead of two objects of type Key::FOREIGN_KEY and Key::MULTIPLE. (MDEV-21051) datadict refactorings - Move read_extra2() to datadict.cc - Refactored extra2_fields to Extra2_info - build_frm_image() readability (MDEV-21051) build_table_shadow_filename() refactoring mysql_prepare_alter_table() leaks fixes (MDEV-21051) amend system tables locking restriction Table mysql.help_relation has foreign key to mysql.help_keyword. On bootstrap when help_relation is opened, it preopens help_keyword for READ and fails in lock_tables_check(). If system table is opened for write then fk references are opened for write. Related to: Bug#25422, WL#3984 Tests: main.lock (MDEV-21051) Store and read foreign key info into/from FRM files 1. Introduce Foreign_key_io class which creates/parses binary stream containing foreign key structures. Referenced tables store there only hints about foreign tables (their db and name), they restore full info from the corresponding tables. Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY_INFO field in extra2 section of FRM file. 2. Modify mysql_prepare_create_table() to generate names for foreign keys. Until InnoDB storage of foreign keys is removed, FK names must be unique across the database: the FK name must be based on table name. 3. Keep stored data in sync on DDL changes. Referenced tables update their foreign hints after following operations on foreign tables: - RENAME TABLE - DROP TABLE - CREATE TABLE - ADD FOREIGN KEY - DROP FOREIGN KEY Foreign tables update their foreign info after following operations on referenced tables: - RENAME TABLE - RENAME COLUMN 4. To achieve 3. there must be ability to rewrite extra2 section of FRM file without full reparse. FRM binary is built from primary structures like HA_CREATE_INFO and cannot be built from TABLE_SHARE. Use shadow write and rename like fast_alter_partition_table() does. Create table workflow: 1. Foreign_key is constructed in parser, placed into alter_info->key_list; 2. mysql_prepare_create_table() translates them to FK_info, assigns foreign_id if needed; 3. build_frm_image() writes two FK_info lists into FRM's extra2 section, for referenced keys it stores only table names (hints); 4. init_from_binary_frm_image() parses extra2 section and fills foreign_keys and referenced_keys of TABLE_SHARE. It restores referenced_keys by reading hint list of table names, opening corresponding shares and restoring FK_info from their foreign_keys. Hints resolution is done only when initializing non-temporary shares. Usually temporary share has different (temporary) name and it is impossible to resolve foreign keys by that name (as we identify them by both foreign and referenced table names). Another not unimportant reason is performance: this saves spare share acquisitions. Alter table workflow: 1. Foreign_key is constructed in parser, placed into alter_info->key_list; 2. mysql_prepare_alter_table() prepares action lists and share list of foreigns/references; 3. mysql_prepare_alter_table() locks list of foreigns/references by MDL_INTENTION_EXCLUSIVE, acquires shares; 4. prepare_create_table() converts key_list into FK_list, assigns foreign_id; 5. shadow FRM of altered table is created; 6. data is copied; 7. altered table is locked by MDL_EXCLUSIVE; 8. fk_handle_alter() processes action lists, creates FK backups, modifies shares, writes shadow FRMs; 9. altered table is closed; 10. shadow FRMs are installed; 11. altered table is renamed, FRM backup deleted; 12. (TBD in MDEV-21053) shadow FRMs installation log closed, backups deleted; On FK backup system: In case of failed DDL operation all shares that was modified must be restored into original state. This is done by FK_ddl_backup (CREATE, DROP), FK_rename_backup (RENAME), FK_alter_backup (ALTER). On STL usage: STL is used for utility not performance-critical algorithms, core structures hold native List. A wrapper was made to convert STL exception into bool error status or NULL value. MDEV-20865 fk_check_consistency() in CHECK TABLE Self-refs fix
* Remove useless test innodb.innodb_bug60049Marko Mäkelä2021-02-183-59/+0
| | | | | | | | | | | | The test innodb.innodb_bug60049 used to check that the record (ID,NAME)=(12,'SYS_FOREIGN_COLS') is the last record in the secondary index of the system table SYS_TABLES. But, ever since commit 233655842374e0723d3191febac7ff2a11470fba or mysql/mysql-server@082d59670f2616f68af37666fac0f23dbeb43099 that record no longer is the last one in the table! The more recent test innodb.purge_secondary covers the purge functionality much better.
* MDEV-24746 Atomic CREATE TRIGGERMonty2021-02-175-0/+655
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The purpose of this task is to ensure that CREATE TRIGGER is atomic When a trigger is created, we first create a trigger_name.TRN file and then create or update the table_name.TRG files. This is done by creating .TRN~ and .TRG~ files and replacing (or creating) the result files. The new logic is - Log CREATE TRIGGER to DDL log, with a marker if old trigger existsted - If old .TRN or .TRG files exists, make backup copies of these - Create the new .TRN and .TRG files as before - Remove the backups Crash recovery - If query has been logged to binary log: - delete any left over backup files - else - Delete any old .TRN~ or .TRG~ files - If there was orignally some triggers (old .TRG file existed) - If we crashed before creating all backup files - Delete existing backup files - else - Restore backup files - end - Delete .TRN and .TRG file (as there was no triggers before One benefit of the new code is that CREATE OR REPLACE TRIGGER is now totally atomic even if there existed an old trigger: Either the old trigger will be replaced or the old one will be left untouched. Other things: - If sql_create_definition_file() would fail, there could be memory leaks in CREATE TRIGGER, DROP TRIGGER or CREATE OR REPLACE TRIGGER. This is now fixed.
* MDEV-24607 Atomic CREATE VIEWMonty2021-02-172-0/+169
| | | | | | | | | | | | | | | | | | | | | The logic of the new code is: - Log CREATE view to DDL log, with a marker if old view existed - If old view exists (in case of CREATE or REPLACE view), make a copy of the old view as view_name.frm- - Create the new view definition file - Delete copy of view if it was created. Crash recovery: - Delete view_name.frm~ file (Temporary file for view definition) - If query was logged to binary log - Delete copy of view if it exists - else -rename the copy of the view over the .frm file (restoring the old definition) One benefit of the new code is that CREATE OR REPLACE VIEW for an existing view is no fully atomic: Either the view will be replaced or the old one will be left unchanged.
* MDEV-24576 Atomic CREATE TABLEMonty2021-02-1616-275/+849
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | There are a few different cases to consider Logging of CREATE TABLE and CREATE TABLE ... LIKE - If REPLACE is used and there was an existing table, DDL log the drop of the table. - If discovery of table is to be done - DDL LOG create table else - DDL log create table (with engine type) - create the table - If table was created - Log entry to binary log with xid - Mark DDL log completed Crash recovery: - If query was in binary log do nothing and exit - If discoverted table - Delete the .frm file -else - Drop created table and frm file - If table was dropped, write a DROP TABLE statement in binary log CREATE TABLE ... SELECT required a little more work as when one is using statement logging the query is written to the binary log before commit is done. This was fixed by adding a DROP TABLE to the binary log during crash recovery if the ddl log entry was not closed. In this case the binary log will contain: CREATE TABLE xxx ... SELECT .... DROP TABLE xxx;
* MDEV-24408 Atomic DROP DATABASEMonty2021-02-169-104/+436
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Description of how DROP DATABASE works after this patch - Collect list of tables - DDL log tables as they are dropped - DDL log drop database - Delete db.opt - Delete data directory - Log either DROP TABLE or DROP DATABASE to binary log - De active ddl log entry This is in line of how things where before (minus ddl logging) except that we delete db.opt file last to not loose it if DROP DATABASE fails. On recovery we have to ensure that all dropped tables are logged in binary log and that they are properly dropped (as with atomic drop table). No new tables be dropped as part of recovery. Recovery of active drop database ddl log entry: - If drop database was logged to ddl log but was not found in the binary log: - drop the db.opt file and database directory. - Log DROP DATABASE to binary log - If drop database was not logged to ddl log - Update binary log with DROP TABLE of the dropped tables. If table list is longer than max_allowed_packet, then the query will be split into multiple DROP TABLE/VIEW queries. Other things: - Added DDL_LOG_STATE and 'current database' as arguments to mysql_rm_table_no_locks(). This was needed to be able to combine ddl logging of DROP DATABASE and DROP TABLE and make the generated DROP TABLE statements shorter. - To make the DROP TABLE statement created by ddl log shorter, I changed the binlogged query to use current directory and omit the directory part for all tables in the current directory. - Merged some DROP TABLE and DROP VIEW code in ddl logger. This was done to be able get separate DROP VIEW and DROP TABLE statements in the binary log. - Added a 'recovery_state' variable to remember the state of dropped tables and views. - Moved out code that drops database objects (stored procedures) from mysql_rm_db_internal() to drop_database_objects() for better code reuse. - Made mysql_rm_db_internal() global so that could be used by the ddl recovery code.
* MDEV-24395 Atomic DROP TRIGGERMonty2021-02-162-0/+172
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The purpose of this task is to ensure that DROP TRIGGER is atomic. Description of how atomic drop trigger works: Logging of DROP TRIGGER Log the following information: db table name trigger name xid /* Used to check if query was already logged to binary log */ initial length of the .TRG file query if there is space for it, if not log a zero length query. Recovery operations: - Delete if exists 'database/trigger_name.TRN~' - If this file existed, it means that we crashed before the trigger was deleted and there is nothing else to do. - Get length of .TRG file - If file length is unchanged, trigger was not dropped. Nothing else to do. - Log original query to binary, if it was stored in the ddl log. If it was not stored (long query string), log the following query to binary log: use `database` ; DROP TRIGGER IF EXISTS `trigger_name` /* generated by ddl log */; Other things: - Added trigger name and DDL_LOG_STATE to drop_trigger() Trigger name was added to make the interface more consistent and more general.
* MDEV-23844 Atomic DROP TABLEMonty2021-02-169-4/+737
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Logging logic: - Log tables, just before they are dropped, to the ddl log - After the last table for the statement is dropped, log an xid for the whole ddl log event In case of crash: - Remove first any active DROP TABLE events from the ddl log that matches xids found in binary log (this mean the drop was successful and was propery logged). - Loop over all active DROP TABLE events - Ensure that the table is completely dropped - Write a DROP TABLE entry to the binary log with the dropped tables. Other things: - Added code to ha_drop_table() to be able to tell the difference if a get_new_handler() failed because of out-of-memory or because the handler refused/was not able to create a a handler. This was needed to get sequences to work as sequences needs a share object to be passed to get_new_handler() - TC_LOG_BINLOG::recover() was changed to always collect Xid's from the binary log and always call ddl_log_close_binlogged_events(). This was needed to be able to collect DROP TABLE events with embedded Xid's (used by ddl log). - Added a new variable "$grep_script" to binlog filter to be able to find only rows that matches a regexp. - Had to adjust some test that changed because drop statements are a bit larger in the binary log than before (as we have to store the xid)
* MDEV-23842 Atomic RENAME TABLEMonty2021-02-1616-0/+1571
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | - Major rewrite of ddl_log.cc and ddl_log.h - ddl_log.cc described in the beginning how the recovery works. - ddl_log.log has unique signature and is dynamic. It's easy to add more information to the header and other ddl blocks while still being able to execute old ddl entries. - IO_SIZE for ddl blocks is now dynamic. Can be changed without affecting recovery of old logs. - Code is more modular and is now usable outside of partition handling. - Renamed log file to dll_recovery.log and added option --log-ddl-recovery to allow one to specify the path & filename. - Added ddl_log_entry_phase[], number of phases for each DDL action, which allowed me to greatly simply set_global_from_ddl_log_entry() - Changed how strings are stored in log entries, which allows us to store much more information in a log entry. - ddl log is now always created at start and deleted on normal shutdown. This simplices things notable. - Added probes debug_crash_here() and debug_simulate_error() to simply crash testing and allow crash after a given number of times a probe is executed. See comments in debug_sync.cc and rename_table.test for how this can be used. - Reverting failed table and view renames is done trough the ddl log. This ensures that the ddl log is tested also outside of recovery. - Added helper function 'handler::needs_lower_case_filenames()' - Extend binary log with Q_XID events. ddl log handling is using this to check if a ddl log entry was logged to the binary log (if yes, it will be deleted from the log during ddl_log_close_binlogged_events() mysqltest.cc changes: - --die will now replace $variables with their values - $error will contain the error of the last failed statement storage engine changes: - maria_rename() was changed to be more robust against crashes during rename.
* MDEV-20025: ADD_MONTHS() Oracle functionMonty2021-02-163-2/+121
| | | | Author: woqutech
* MDEV-20017 Implement TO_CHAR() Oracle compatible functionMonty2021-02-163-0/+674
| | | | | | | | | | | | | | | | | | TO_CHAR(expr, fmt) - expr: required parameter, data/time/timestamp type expression - fmt: optional parameter, format string, supports YYYY/YYY/YY/RRRR/RR/MM/MON/MONTH/MI/DD/DY/HH/HH12/HH24/SS and special characters. The default value is "YYYY-MM-DD HH24:MI:SS" In Oracle, TO_CHAR() can also be used to convert numbers to strings, but this is not supported. This will gave an error in this patch. Other things: - If format strings is a constant, it's evaluated only once and if there is any errors in it, they are given at once and the statement will abort. Original author: woqutech Lots of optimizations and cleanups done as part of review
* MDEV-20021 sql_mode="oracle" does not support MINUS set operatorMonty2021-01-283-2/+82
| | | | | | MINUS is mapped to EXCEPT Author: woqutech
* MDEV-24285 support oracle build-in function: sys_guidMonty2021-01-282-0/+9
| | | | | | SYS_GUID() returns same as UUID(), but without any '-' author: woqutech
* MDEV-24089 support oracle syntax: rownumMonty2021-01-289-15/+1492
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The ROWNUM() function is for SELECT mapped to JOIN->accepted_rows, which is incremented for each accepted rows. For Filesort, update, insert, delete and load data, we map ROWNUM() to internal variables incremented when the table is changed. The connection between the row counter and Item_func_rownum is done in sql_select.cc::fix_items_after_optimize() and sql_insert.cc::fix_rownum_pointers() When ROWNUM() is used anywhere in query, the optimization to ignore ORDER BY in sub queries are disabled. This was done to get the following common Oracle query to work: select * from (select * from t1 order by a desc) as t where rownum() <= 2; MDEV-3926 "Wrong result with GROUP BY ... WITH ROLLUP" contains a discussion about this topic. LIMIT optimization is enabled when in a top level WHERE clause comparing ROWNUM() with a numerical constant using any of the following expressions: - ROWNUM() < # - ROWNUM() <= # - ROWNUM() = 1 ROWNUM() can be also be the right argument to the comparison function. LIMIT optimization is done in two cases: - For the current sub query when the ROWNUM comparison is done on the top level: SELECT * from t1 WHERE rownum() <= 2 AND t1.a > 0 - For an inner sub query, when the upper level has only a ROWNUM comparison in the WHERE clause: SELECT * from (select * from t1) as t WHERE rownum() <= 2 In Oracle mode, one can also use ROWNUM without braces. Other things: - Fixed bug where the optimizer tries to optimize away sub queries with RAND_TABLE_BIT set (non-deterministic queries). Now these sub queries will not be converted to joins. This bug fix was also needed to get rownum() working inside subqueries. - In remove_const() remove setting simple_order to FALSE if ROLLUP is USED. This code was disable a long time ago because of wrong assignment in the following code. Instead we set simple_order to false if RAND_TABLE_BIT was used in the SELECT list. This ensures that we don't delete ORDER BY if the result set is not deterministic, like in 'SELECT RAND() AS 'r' FROM t1 ORDER BY r'; - Updated parameters for Sort_param::init_for_filesort() to be able to provide filesort with information where the number of accepted rows should be stored - Reordered fields in class Filesort to optimize storage layout - Added new error messsage to tell that a function can't be used in HAVING - Added field 'with_rownum' to THD to mark that ROWNUM() is used in the query. Co-author: Oleksandr Byelkin <sanja@mariadb.com> LIMIT optimization for sub query
* MDEV-19682 sql_mode="oracle" does not support sysdateAlexander Barkov2021-01-284-6/+244
|
* Give a readable error in mtr if resolve_at_variable failsMonty2021-01-281-2/+9
|
* Add support for minimum field width for strings to my_vsnprintf()Monty2021-01-1910-55/+55
|
* Merge 10.5 into 10.6Marko Mäkelä2021-01-144-161/+0
|\
| * MDEV-24536 innodb_idle_flush_pct has no effectMarko Mäkelä2021-01-134-161/+0
| | | | | | | | | | | | | | | | | | | | | | The parameter innodb_idle_flush_pct that was introduced in MariaDB Server 10.1.2 by MDEV-6932 has no effect ever since the InnoDB changes from MySQL 5.7.9 were applied in commit 2e814d4702d71a04388386a9f591d14a35980bfe. Let us declare the parameter as MARIADB_REMOVED_OPTION. For earlier versions, commit ea9cd97f855fddf91f011434e8289ce5eba52528 declared the parameter deprecated.
* | Merge 10.5 into 10.6Marko Mäkelä2021-01-1132-50/+1203
|\ \ | |/
| * Merge 10.4 into 10.5Marko Mäkelä2021-01-1132-50/+1203
| |\
| | * Merge 10.3 into 10.4Marko Mäkelä2021-01-1116-1/+997
| | |\
| | | * Merge 10.2 into 10.3 (except MDEV-17556)Marko Mäkelä2021-01-119-1/+825
| | | |\ | | | | | | | | | | | | | | | | | | | | | | | | | The fix of MDEV-17556 (commit e25623e78a3efde05e30070dc7362f8dc0d8c459 and commit 61a362c9493df63dc588fcb71409537ae56ab9c8) has been omitted due to conflicts and will have to be applied separately later.
| | | | * MDEV-24482: Added wait condition to make sure table t1 is replicated to node_2.Stepan Patryshev2021-01-041-0/+3
| | | | |
| | | | * MDEV-24465: Added wait condition to make sure table t1 is replicated to node_2.Stepan Patryshev2021-01-041-0/+3
| | | | |
| | | | * MDEV-24464: Added wait condition to make sure table t1 is replicated to node_2.Stepan Patryshev2021-01-041-0/+3
| | | | |
| | | | * MDEV-24447: Added wait condition to make sure table t1 is replicated to node_2.Stepan Patryshev2021-01-041-0/+3
| | | | |
| | | | * MDEV-24462: Added wait condition to make sure table t1 is replicated to node_2.Stepan Patryshev2021-01-041-0/+3
| | | | |
| | | | * MDEV-23033: All slaves crash once in ~24 hours and loop restart with signal 11Sujatha2021-01-042-0/+805
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Problem: ======= Upon deleting or updating a row in a parent table (with primary key), if the child table has virtual column and an associated key with ON UPDATE CASCADE/ON DELETE CASCADE, it will result in slave crash. Analysis: ======== Tables which are related through foreign key require prelocking similar to triggers. i.e If a table has triggers/foreign keys we should add all tables and routines used by them to the prelocking set. This prelocking happens during 'open_and_lock_tables' call. Each table being opened is checked for foreign key references. If foreign key reference exists then the child table is opened and it is linked to the table_list. Upon any modification to parent table its corresponding child tables are retried from table_list and they are updated accordingly. This prelocking work fine on master. On slave prelocking works for following cases. - Statement/mixed based replication - In row based replication when trigger execution is enabled through 'slave_run_triggers_for_rbr=YES/LOGGING/ENFORCE' Otherwise it results in an assert/crash, as the parent table will not find the corresponding child table and it will be NULL. Dereferencing NULL pointer leads to slave server exit. Fix: === Introduce a new 'slave_fk_event_map' flag similar to 'trg_event_map'. This flag will ensure that when foreign key is enabled in row based replication all the parent and child tables are prelocked, so that parent is able to locate the child table. Note: This issue is specific to slave, hence only slave needs to be upgraded.
| | | | * MDEV-19442 server_audit plugin doesn't consider proxy users in ↵Alexey Botchkov2020-12-282-1/+5
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | server_audit_excl_users/server_audit_incl_users. Check the proxy user just as the connection user against the incl_users_list and excl_users_list.
| | | * | MDEV-16272 rpl.rpl_semisync_ali_issues failed in buildbot, SHOW variable was ↵Alice Sherepa2021-01-071-0/+15
| | | | | | | | | | | | | | | | | | | | done instead of waiting for the value of that variable
| | | * | MDEV-17891 Assertion failure upon attempt to replace into a full tableNikita Malyavin2021-01-074-0/+112
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Problem: Assertion `transactional_table || !changed || thd->transaction.stmt.modified_non_trans_table' failed due REPLACE into a versioned table. It is not specific to system versioning/pertitioning/heap, but this combination makes it much easier to reproduce. The thing is to make first ha_update_row call succeed to make info->deleted != 0. And then make REPLACE fail by any reason. In this scenario we overflow versioned partition, so next ha_update_row succeeds, but corresponding ha_write_row fails to insert history record. Fix: modified_non_trans_table is set in one missed place
| | | * | MDEV-23632 ALTER TABLE...ADD KEY creates corrupted index on virtual columnNikita Malyavin2021-01-052-0/+45
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | mysql_col_offset was not updated after the new column has been added by an INSTANT ALTER TABLE -- table data dictionary had been remaining the same. When the virtual column is added or removed, table was usually evicted and then reopened, which triggered vcol info rebuild on the next open. However this also should be done when the usual column is added or removed: mariadb always stores virtual field at the end of maria record, so the shift should always happen. Fix: expand the eviction condition to the case when usual fields are added/removed Note: this should happen only in the case of !new_clustered: * When new_clustered is true, a new data dictionary is created, and vcol metadata is rebuilt in `alter_rebuild_apply_log()` * We can't do it in `new_clustered` case, because the old table is not yet subctituted correctly
| | * | | Fix MTR test galera_as_slave_replayDaniele Sciascia2021-01-092-27/+29
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | - Galera cluster must be in node 1 and 2, and acts as slave for node 3 - Sync point commit_monitor_enter_sync was renamed to commit_monitor_master_enter_sync in 4.x Reviewed-by: Jan Lindström <jan.lindstrom@mariadb.com>
| | * | | MDEV-24500: Added wait condition to make sure table t1 is replicated to node_2.bb-10.4-MDEV-24500Stepan Patryshev2021-01-041-0/+3
| | | | |
| | * | | MDEV-24512 Assertion failed in rec_is_metadata() in ↵Marko Mäkelä2021-01-022-3/+33
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | btr_discard_only_page_on_level() btr_discard_only_page_on_level(): Attempt to read the MDEV-15562 metadata record from the leaf page, not the root page. In the root, the leftmost (in this case, the only) node pointer would look like a metadata record. This corruption bug was introduced in commit 0e5a4ac2532c64a545796c787354dc41d61d0e62 (MDEV-15562). The scenario is rare: a column was dropped instantly or the order of columns was changed instantly, and then the table became empty in such a way that in the last step, the root page had one child page. Normally, a non-leaf B-tree page would always contain at least 2 children.
| | * | | Merge branch '10.3' into 10.4Oleksandr Byelkin2020-12-291-2/+2
| | |\ \ \ | | | |/ /
| | | * | Correction of the merge 10.2 into 10.3 for MDEV-23619Igor Babaev2020-12-281-2/+2
| | | | | | | | | | | | | | | | | | | | (correction for commit 6fed6de93f120b5e311b79892e7865639e9613a4)
| | * | | MDEV-24255 MTR test galera_bf_abort fails with --ps-protocolTeemu Ollakka2020-12-287-2/+136
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Under ps-protocol, commandsl like COM_STMT_FETCH, COM_STMT_CLOSE and COM_STMT_SEND_LONG_DATA are not supposed to return errors. Therefore, if a transaction is BF aborted and the client is processing one of those commands, then we should not return a deadlock error immediately. Instead wait for the a subsequent client interaction which permits errors to be returned. To handle this, wsrep_before_command() now accepts parameter keep_command_error. If set true, keep_command_error will cause wsrep-lib side to skip result handling, and to keep the current error for the next interaction with the client. Reviewed-by: Jan Lindström <jan.lindstrom@mariadb.com>
* | | | | Merge 10.5 into 10.6Marko Mäkelä2021-01-07106-1085/+2173
|\ \ \ \ \ | |/ / / /
| * | | | MDEV-24537 innodb_max_dirty_pages_pct_lwm=0 lost its special meaningMarko Mäkelä2021-01-061-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | In commit 3a9a3be1c64b14c05648e87ebe0f1dd96457de41 (MDEV-23855) some previous logic was replaced with the condition dirty_pct < srv_max_dirty_pages_pct_lwm, which caused the default value of the parameter innodb_max_dirty_pages_pct_lwm=0 to lose its special meaning: 'refer to innodb_max_dirty_pages_pct instead'. This implicit special meaning was visible in the function af_get_pct_for_dirty(), which was removed in commit f0c295e2de8c074c2ca72e19ff06e1d0e3ee6d2b (MDEV-24369). page_cleaner_flush_pages_recommendation(): Restore the special meaning that was removed in MDEV-24369. buf_flush_page_cleaner(): If srv_max_dirty_pages_pct_lwm==0.0, refer to srv_max_buf_pool_modified_pct. This fixes the observed performance regression due to excessive page flushing. buf_pool_t::page_cleaner_wakeup(): Revise the wakeup condition. innodb_init(): Do initialize srv_max_io_capacity in Mariabackup. It was previously constantly 0, which caused mariadb-backup --prepare to hang in buf_flush_sync(), making no progress.
| * | | | Merge commit '10.4' into 10.5Oleksandr Byelkin2021-01-06104-1084/+2169
| |\ \ \ \ | | |/ / /
| | * | | Merge branch '10.3' into 10.4bb-10.4-MDEV-23468Oleksandr Byelkin2020-12-2574-1061/+1582
| | |\ \ \ | | | |/ /
| | | * | Merge branch '10.2' into 10.3Oleksandr Byelkin2020-12-2376-1043/+1703
| | | |\ \ | | | | |/
| | | | * Forgot to add this change to previous csetSergei Petrunia2020-12-221-0/+10
| | | | |
View Lorry Controller Status