- commits - lists.mariadb.org

[Commits] 88cf164a1eb: MDEV-11154: Write_on_release_cache(log_event.cc) function will not write "COMMIT", if use "mysqlbinlog ... | mysql ..."
by sujatha 09 Jul '19

09 Jul '19

revision-id: 88cf164a1eb2dd4ef6b23c3f0c6c978a0372f5dd (mariadb-10.1.39-90-g88cf164a1eb) parent(s): fd08f953222b854428dc259631e8fc707fe34619 author: Sujatha committer: Sujatha timestamp: 2019-07-09 12:01:52 +0530 message: MDEV-11154: Write_on_release_cache(log_event.cc) function will not write "COMMIT", if use "mysqlbinlog ... | mysql ..." Problem: ======= Executing command, "mysqlbinlog --read-from-remote-server --host='xx.xx.xx.xx' --port=3306 --user=xxx --password=xxx --database=mysql --to-last-log mysql-bin.000001 --start-position=1098699 --stop-never |mysql -uxxx -pxxx", we found that last data read from remote couldn't commit. Analysis: ======== The purpose of 'Write_on_release_cache' is that the contents of the Cache will automatically be written to a dedicated result file on destruction. Flush operation on the result file is controlled by a flag 'FLUSH_F'. Events which require force flush upon their destruction will have to enable this 'Write_on_release_cache::FLUSH_F'. At present the 'FLUSH_F' flag is defined as an enum as shown below. enum flag { FLUSH_F }; Since 'FLUSH_F' is the first member without initialization it get the default value '0'. Because of this the following flush condition never succeeds. if (m_flags & FLUSH_F) fflush(m_file); At present the file gets flushed only during my_fclose(result_file) operation. When continuous streaming is enabled through --stop-never option it never gets flushed and hence events are not replicated. Fix: === Initialize the enum value to non zero value. --- .../binlog/r/binlog_mysqlbinlog_stop_never.result | 16 ++++++ .../binlog/t/binlog_mysqlbinlog_stop_never.test | 66 ++++++++++++++++++++++ sql/log_event.cc | 2 +- 3 files changed, 83 insertions(+), 1 deletion(-) diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_stop_never.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_stop_never.result new file mode 100644 index 00000000000..e94f17b9489 --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_stop_never.result @@ -0,0 +1,16 @@ +RESET MASTER; +include/stop_dump_threads.inc +# Step-1: Execute some dummy statements. +CREATE TABLE t1(i int); +INSERT INTO t1 values (1); +# Step-2: Disable binary log temporarily and drop the table 't1'. +set @@SESSION.SQL_LOG_BIN = 0; +DROP TABLE t1; +set @@SESSION.SQL_LOG_BIN = 1; +# Step-3: Execute MYSQL_BINLOG with --stop-never and source it to mysql client. +# Step-4: Wait till dump thread transfer is completed. +# Step-5: Check that the data is there. +# Step-6: Cleanup +# kill the dump thread serving the mysqlbinlog --stop-never process +include/stop_dump_threads.inc +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/t/binlog_mysqlbinlog_stop_never.test b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_stop_never.test new file mode 100644 index 00000000000..d73e453ce96 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_stop_never.test @@ -0,0 +1,66 @@ +# ==== Purpose ==== +# +# Test verifies that continuous streaming of binary log content using the +# "mysqlbinlog --stop-never" option and sourcing it to mysql client works +# fine. +# +# ==== Implementation ==== +# +# Steps: +# 1 - Create a table on a server on which binary log is enabled and insert +# a row. +# 2 - Disable the binary log on the server and drop the table. +# 3 - Capture the binary log output using "mysqlbinlog --stop_never" option +# and source it to mysql client. +# 4 - Query the PROCESSLIST table to ensure that the dump thread which is +# serving "stop_never" option has read entire binlog. +# 5 - Verify that the table is populated on the server. +# 6 - Cleanup. +# +# ==== References ==== +# +# MDEV-11154: Write_on_release_cache(log_event.cc) function will not write +# "COMMIT", if use "mysqlbinlog ... | mysql ..." + +--source include/not_windows.inc + +# Test is not specific to any binlog format. Hence Running only for 'row'. +--source include/have_binlog_format_row.inc + +# binlog file name is needed in the test. To use master-bin.000001, +# RESET MASTER is needed. +RESET MASTER; +# kill the dump threads if there any dump threads (may be from previous test) +--source include/stop_dump_threads.inc + +--echo # Step-1: Execute some dummy statements. +CREATE TABLE t1(i int); +INSERT INTO t1 values (1); + +--echo # Step-2: Disable binary log temporarily and drop the table 't1'. +set @@SESSION.SQL_LOG_BIN = 0; +DROP TABLE t1; +set @@SESSION.SQL_LOG_BIN = 1; + +--echo # Step-3: Execute MYSQL_BINLOG with --stop-never and source it to mysql client. +--write_file $MYSQL_TMP_DIR/mysqlbinlog_stop_never.sh +(`$MYSQL_BINLOG --read-from-remote-server --stop-never --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 | $MYSQL --user=root --protocol=tcp --host=127.0.0.1 --port=$MASTER_MYPORT`) < /dev/null > /dev/null 2>&1 & +EOF +--exec /bin/bash $MYSQL_TMP_DIR/mysqlbinlog_stop_never.sh + +--echo # Step-4: Wait till dump thread transfer is completed. +let $wait_condition= SELECT id from information_schema.processlist where processlist.command like '%Binlog%' and state like '%Master has sent%'; +--source include/wait_condition.inc + +--echo # Step-5: Check that the data is there. +let $count= 1; +let $table= test.t1; +source include/wait_until_rows_count.inc; + +--echo # Step-6: Cleanup +--echo # kill the dump thread serving the mysqlbinlog --stop-never process +--source include/stop_dump_threads.inc + +DROP TABLE t1; +--remove_file $MYSQL_TMP_DIR/mysqlbinlog_stop_never.sh + diff --git a/sql/log_event.cc b/sql/log_event.cc index 61748799a54..65f29441e1a 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -296,7 +296,7 @@ class Write_on_release_cache public: enum flag { - FLUSH_F + FLUSH_F= 1 }; typedef unsigned short flag_set;

1 0

[Commits] cba804d57: MDEV-19986: MyRocks: Range Locking: SeekForUpdate support
by Sergei Petrunia 08 Jul '19

08 Jul '19

revision-id: cba804d57d693e5cf7763d404f29d2483992f96a (v5.8-1043-gcba804d57) parent(s): c91095e08fbdacb69831a93ab403a80487dee435 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-07-08 19:01:56 +0300 message: MDEV-19986: MyRocks: Range Locking: SeekForUpdate support Initial implementation, RocksDB part --- include/rocksdb/utilities/transaction.h | 4 + utilities/transactions/pessimistic_transaction.cc | 172 +++++++++++++++++++++ utilities/transactions/pessimistic_transaction.h | 3 + .../transactions/pessimistic_transaction_db.h | 1 + 4 files changed, 180 insertions(+) diff --git a/include/rocksdb/utilities/transaction.h b/include/rocksdb/utilities/transaction.h index 6b84d4a44..731f76708 100644 --- a/include/rocksdb/utilities/transaction.h +++ b/include/rocksdb/utilities/transaction.h @@ -366,6 +366,10 @@ class Transaction { virtual Iterator* GetIterator(const ReadOptions& read_options, ColumnFamilyHandle* column_family) = 0; + virtual Iterator* GetLockingIterator(const ReadOptions& read_options, + ColumnFamilyHandle* column_family) { + return nullptr; + }; // Put, Merge, Delete, and SingleDelete behave similarly to the corresponding // functions in WriteBatch, but will also do conflict checking on the // keys being written. diff --git a/utilities/transactions/pessimistic_transaction.cc b/utilities/transactions/pessimistic_transaction.cc index 4c0578444..cbac16437 100644 --- a/utilities/transactions/pessimistic_transaction.cc +++ b/utilities/transactions/pessimistic_transaction.cc @@ -56,6 +56,178 @@ PessimisticTransaction::PessimisticTransaction( Initialize(txn_options); } +////////////////////////////////////////////////////////////////////////////// +// Locking iterator +////////////////////////////////////////////////////////////////////////////// + +// +// LockingIterator is an iterator that locks the rows before returning, as well +// as scanned gaps between the rows. +// +// Example: +// lock_iter= trx->GetLockingIterator(); +// lock_iter->Seek('abc'); +// lock_iter->Valid()==true && lock_iter->key() == 'bcd'; +// +// After the above, the returned record 'bcd' is locked by transaction trx. +// Also, the range between ['abc'..'bcd'] is empty and locked by trx. +// +// lock_iter->Next(); +// lock_iter->Valid()==true && lock_iter->key() == 'efg' +// +// Now, the range ['bcd'.. 'efg'] (bounds incluive) is also locked, and there are no +// records between 'bcd' and 'efg'. +// +class LockingIterator : public Iterator { + + ColumnFamilyHandle* cfh_; + PessimisticTransaction *txn_; + Iterator *iter_; + Status status_; + + public: + LockingIterator(Iterator *iter, ColumnFamilyHandle *cfh, + PessimisticTransaction *txn) : + cfh_(cfh), txn_(txn), iter_(iter), status_(Status::InvalidArgument()) {} + + // An iterator is either positioned at a key/value pair, or + // not valid. This method returns true iff the iterator is valid. + // Always returns false if !status().ok(). + virtual bool Valid() const override { return status_.ok(); } + + // Note: MyRocks doesn't ever call these: + virtual void SeekToFirst() override; + virtual void SeekToLast() override { assert(0); } + + virtual void Seek(const Slice& target) override; + + // Position at the last key in the source that at or before target. + // The iterator is Valid() after this call iff the source contains + // an entry that comes at or before target. + virtual void SeekForPrev(const Slice& target) { assert(0); } + + virtual void Next() override; + virtual void Prev() override { + assert(0); // TODO: implement this + } + + virtual Slice key() const override { + assert(Valid()); + return iter_->key(); + } + + virtual Slice value() const override { + assert(Valid()); + return iter_->value(); + } + + virtual Status status() const override { + return status_; + } + +private: + void ScanForward(const Slice& target, bool call_next); +}; + + +Iterator* PessimisticTransaction::GetLockingIterator( + const ReadOptions& read_options, + ColumnFamilyHandle* column_family) { + + if (!txn_db_impl_->UsesRangeLocking()) { + return nullptr; + } + auto iter= GetIterator(read_options, column_family); + + if (iter) + return new LockingIterator(iter, column_family, this); + else + return nullptr; +} + + +void LockingIterator::Seek(const Slice& target) { + iter_->Seek(target); + ScanForward(target, false); +} + +/* + Lock from the current position up to the next key + (This is basically like Seek(right_after(current_position)) +*/ +void LockingIterator::Next() { + assert(Valid()); + // Save the current key value. We need it as the left endpoint + // of the range lock we're going to acquire + std::string current_key = iter_->key().ToString(); + + iter_->Next(); + ScanForward(Slice(current_key), true); +} + +// Note: MyRocks never uses this as call as it has index_nr as prefix for all +// keys. +void LockingIterator::SeekToFirst() { + + iter_->SeekToFirst(); + if (!iter_->Valid()) { + status_ = iter_->status(); + return; + } + + std::string current_key = iter_->key().ToString(); + ScanForward(Slice(current_key), true); +} + +void LockingIterator::ScanForward(const Slice& target, bool call_next) { + + if (!iter_->Valid()) { + status_ = iter_->status(); + return; + } + + while (1) { + /* + TODO: the underlying iterator respects iterator bounds, so we don't need + to check them here + */ + auto end_key = iter_->key(); + status_ = txn_->GetRangeLock(cfh_, Endpoint(target), Endpoint(end_key)); + if (!status_.ok()) { + // Failed to get a lock (most likely lock wait timeout) + return; + } + + //Ok, now we have a lock which is inhibiting modifications in the range + // Somebody might have done external modifications, though: + // - removed the key we've found + // - added a key before that key. + iter_->Seek(target); + if (call_next && iter_->Valid()) + iter_->Next(); + + if (iter_->Valid()) { + if (cfh_->GetComparator()->Compare(iter_->key(), end_key) <= 0) { + // Ok, the key is within the range. + status_ = Status::OK(); + break; + } else { + // We've got a row but it is outside the range we've locked. + // Re-try the lock-and-read step. + continue; + } + } else { + // There's no row (within the iterator bounds perhaps). Exit now. + // (we might already have locked a range in this function but there's + // nothing we can do about it) + status_ = iter_->status(); + break; + } + } +} + +///////////////////////////////////////////////////////////////////////////// + void PessimisticTransaction::Initialize(const TransactionOptions& txn_options) { txn_id_ = GenTxnID(); diff --git a/utilities/transactions/pessimistic_transaction.h b/utilities/transactions/pessimistic_transaction.h index 9fb44c74e..4fb5f63e0 100644 --- a/utilities/transactions/pessimistic_transaction.h +++ b/utilities/transactions/pessimistic_transaction.h @@ -60,6 +60,9 @@ class PessimisticTransaction : public TransactionBaseImpl { Status SetName(const TransactionName& name) override; + virtual Iterator* GetLockingIterator(const ReadOptions& read_options, + ColumnFamilyHandle* column_family) override; + // Generate a new unique transaction identifier static TransactionID GenTxnID(); diff --git a/utilities/transactions/pessimistic_transaction_db.h b/utilities/transactions/pessimistic_transaction_db.h index e88856342..2646228f5 100644 --- a/utilities/transactions/pessimistic_transaction_db.h +++ b/utilities/transactions/pessimistic_transaction_db.h @@ -129,6 +129,7 @@ class PessimisticTransactionDB : public TransactionDB { // Key Tracking should be done only with point lock manager. bool ShouldDoKeyTracking() const { return range_lock_mgr_ == nullptr; } + bool UsesRangeLocking() const { return range_lock_mgr_ != nullptr; } protected: DBImpl* db_impl_; std::shared_ptr<Logger> info_log_;

1 0

[Commits] 29df44a3665: MDEV-17963: Assertion `field_pos < field_count' failed in Protocol_text::store, Assertion `field_handlers == 0 || field_pos < field_count'
by Varun 08 Jul '19

08 Jul '19

revision-id: 29df44a3665c40a464c1a39659ae7566bac5d41c (mariadb-10.1.39-88-g29df44a3665) parent(s): 02a0ebc613bf51fd4a943f4f216f1d5220c947be author: Varun Gupta committer: Varun Gupta timestamp: 2019-07-08 11:37:34 +0530 message: MDEV-17963: Assertion `field_pos < field_count' failed in Protocol_text::store, Assertion `field_handlers == 0 || field_pos < field_count' The problem was that sp_head::MULTI_RESULTS was not set correctly for ANALYZE statement with SELECT ... INTO variable. This is a follow up fix for MDEV-7023 --- mysql-test/r/sp.result | 14 ++++++++++++++ mysql-test/t/sp.test | 20 ++++++++++++++++++++ sql/sp_head.cc | 2 +- 3 files changed, 35 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index e39754159fb..1704e7cd214 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -8319,4 +8319,18 @@ UNION SELECT * FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.PARAMETERS; DROP FUNCTION f; DROP VIEW v; +# +# MDEV-17963: Assertion `field_pos < field_count' failed in Protocol_text::store, +# Assertion `field_handlers == 0 || field_pos < field_count' +# +CREATE TABLE t1 (ct time); +INSERT INTO t1 VALUES ('16:11:28'); +CREATE FUNCTION f1 () RETURNS varchar(100) +BEGIN +DECLARE xxx varchar(100); +ANALYZE SELECT sum(ct) FROM t1 INTO xxx ; +RETURN xxx; +END| +ERROR 0A000: Not allowed to return a result set from a function +drop table t1; #End of 10.1 tests diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 0e95fb5c271..a2a40f87ef0 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -9840,4 +9840,24 @@ SELECT * FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.PARAMETERS; DROP FUNCTION f; DROP VIEW v; +--echo # +--echo # MDEV-17963: Assertion `field_pos < field_count' failed in Protocol_text::store, +--echo # Assertion `field_handlers == 0 || field_pos < field_count' +--echo # + +CREATE TABLE t1 (ct time); +INSERT INTO t1 VALUES ('16:11:28'); + +DELIMITER |; +--error ER_SP_NO_RETSET +CREATE FUNCTION f1 () RETURNS varchar(100) +BEGIN +DECLARE xxx varchar(100); +ANALYZE SELECT sum(ct) FROM t1 INTO xxx ; +RETURN xxx; +END| + +DELIMITER ;| +drop table t1; + --echo #End of 10.1 tests diff --git a/sql/sp_head.cc b/sql/sp_head.cc index c3274e2cecd..5c5688be4a3 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -212,7 +212,7 @@ sp_get_flags_for_command(LEX *lex) switch (lex->sql_command) { case SQLCOM_SELECT: - if (lex->result) + if (lex->result && !lex->analyze_stmt) { flags= 0; /* This is a SELECT with INTO clause */ break;

1 0

[Commits] 0198afed649: MDEV-12867: Full scan despite appropriate index
by Varun 06 Jul '19

06 Jul '19

revision-id: 0198afed649faef8ba1a99b3ccb5b2601dd551c5 (mariadb-10.4.5-123-g0198afed649) parent(s): c6dff51276b4c0a1c14df32c5d96ab65c846baa6 author: Varun Gupta committer: Varun Gupta timestamp: 2019-07-07 03:44:33 +0530 message: MDEV-12867: Full scan despite appropriate index Also consider that select items that participated in conditions in the where clause and are constant can be considered for the MIN/MAX optimzation --- mysql-test/main/func_misc.result | 33 +++++++++++++++++++++++++++++++++ mysql-test/main/func_misc.test | 31 +++++++++++++++++++++++++++++++ sql/opt_sum.cc | 13 +++++++++++-- sql/sql_select.cc | 2 +- sql/sql_select.h | 3 ++- 5 files changed, 78 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/func_misc.result b/mysql-test/main/func_misc.result index 1d284e45545..c2caa6a2fdd 100644 --- a/mysql-test/main/func_misc.result +++ b/mysql-test/main/func_misc.result @@ -1614,5 +1614,38 @@ a y DROP TABLE t1; # +# MDEV-12867: Full scan despite appropriate index +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + B.a*10 from t1 A, t1 B; +CREATE TABLE t3 ( +a int NOT NULL, +b int unsigned NOT NULL, +c int unsigned NOT NULL, +PRIMARY KEY (a,b,c) +); +insert into t3 (a,b,c) values (0,0,1); +insert into t3 (a,b,c) values (1,2,3); +insert into t3 (a,b,c) values (1,3,5); +insert into t3 (a,b,c) values (1,5,3); +insert into t3 (a,b,c) values (0,1,9); +explain +SELECT a, MIN(b) FROM t3 WHERE a = 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT a, MIN(b) FROM t3 WHERE a = 0; +a MIN(b) +0 0 +explain +SELECT MAX(b) FROM t3 WHERE a = 0 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT a, MAX(b) FROM t3 WHERE a = 0; +a MAX(b) +0 1 +drop table t1,t2,t3; +# # End of 10.4 tests # diff --git a/mysql-test/main/func_misc.test b/mysql-test/main/func_misc.test index 331293a9c95..da9bf730690 100644 --- a/mysql-test/main/func_misc.test +++ b/mysql-test/main/func_misc.test @@ -1253,6 +1253,37 @@ $$ DELIMITER ;$$ DROP TABLE t1; +--echo # +--echo # MDEV-12867: Full scan despite appropriate index +--echo # + +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2(a int); +insert into t2 select A.a + B.a*10 from t1 A, t1 B; + +CREATE TABLE t3 ( + a int NOT NULL, + b int unsigned NOT NULL, + c int unsigned NOT NULL, + PRIMARY KEY (a,b,c) +); + +insert into t3 (a,b,c) values (0,0,1); +insert into t3 (a,b,c) values (1,2,3); +insert into t3 (a,b,c) values (1,3,5); +insert into t3 (a,b,c) values (1,5,3); +insert into t3 (a,b,c) values (0,1,9); + +explain +SELECT a, MIN(b) FROM t3 WHERE a = 0; +SELECT a, MIN(b) FROM t3 WHERE a = 0; +explain +SELECT MAX(b) FROM t3 WHERE a = 0 ; +SELECT a, MAX(b) FROM t3 WHERE a = 0; +drop table t1,t2,t3; + --echo # --echo # End of 10.4 tests --echo # diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 0a3c30a176d..5d6169fb81f 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -239,7 +239,8 @@ static int get_index_max_value(TABLE *table, TABLE_REF *ref, uint range_fl) */ int opt_sum_query(THD *thd, - List<TABLE_LIST> &tables, List<Item> &all_fields, COND *conds) + List<TABLE_LIST> &tables, List<Item> &all_fields, COND *conds, + COND_EQUAL *cond_equal) { List_iterator_fast<Item> it(all_fields); List_iterator<TABLE_LIST> ti(tables); @@ -472,9 +473,17 @@ int opt_sum_query(THD *thd, } else if (const_result) { + /* + Added this call here to find if the item belong to a multiple equality and if yes + does that Multiple equality have a constant value for this item. + */ + Item *res= item->propagate_equal_fields(thd, + Value_source:: + Context_identity(), + cond_equal); if (recalc_const_item) item->update_used_tables(); - if (!item->const_item() && item->type() != Item::WINDOW_FUNC_ITEM) + if (!item->const_item() && res == item && item->type() != Item::WINDOW_FUNC_ITEM) const_result= 0; } } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 23827898160..32bc6c8608f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2103,7 +2103,7 @@ JOIN::optimize_inner() If all items were resolved by opt_sum_query, there is no need to open any tables. */ - if ((res=opt_sum_query(thd, select_lex->leaf_tables, all_fields, conds))) + if ((res=opt_sum_query(thd, select_lex->leaf_tables, all_fields, conds, cond_equal))) { DBUG_ASSERT(res >= 0); if (res == HA_ERR_KEY_NOT_FOUND) diff --git a/sql/sql_select.h b/sql/sql_select.h index b7f870bf797..a165f7dcc0f 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1852,7 +1852,8 @@ bool is_indexed_agg_distinct(JOIN *join, List<Item_field> *out_args); /* functions from opt_sum.cc */ bool simple_pred(Item_func *func_item, Item **args, bool *inv_order); int opt_sum_query(THD* thd, - List<TABLE_LIST> &tables, List<Item> &all_fields, COND *conds); + List<TABLE_LIST> &tables, List<Item> &all_fields, COND *conds, + COND_EQUAL *cond_equal); /* from sql_delete.cc, used by opt_range.cc */ extern "C" int refpos_order_cmp(void* arg, const void *a,const void *b);

1 0

[Commits] 3ceaffd: MDEV-17544 No warning when trying to name a primary key constraint.
by holyfoot＠askmonty.org 04 Jul '19

04 Jul '19

revision-id: 3ceaffd68cbfa7a0439f9bf43535d6975fc43e67 (mariadb-10.3.16-23-g3ceaffd) parent(s): 099007c3c92d1405625777fa86d2fba3da1d339c committer: Alexey Botchkov timestamp: 2019-07-04 16:46:44 +0400 message: MDEV-17544 No warning when trying to name a primary key constraint. Warning added when keyname other than PRIMARY specified in CREATE TABLE .. PRIMARY KEY keyname (keyfields) ... --- mysql-test/main/create.result | 4 ++++ mysql-test/main/create.test | 7 +++++++ mysql-test/main/system_mysql_db_fix50117.test | 16 ++++++++-------- scripts/mysql_system_tables.sql | 18 +++++++++--------- sql/share/errmsg-utf8.txt | 2 ++ sql/sql_table.cc | 8 ++++++++ 6 files changed, 38 insertions(+), 17 deletions(-) diff --git a/mysql-test/main/create.result b/mysql-test/main/create.result index fd017c0..e760bbc 100644 --- a/mysql-test/main/create.result +++ b/mysql-test/main/create.result @@ -2093,3 +2093,7 @@ create table t1 (i int, j int, key(i), key(i)) as select 1 as i, 2 as j; Warnings: Note 1831 Duplicate index `i_2`. This is deprecated and will be disallowed in a future release drop table t1; +CREATE TABLE t1 ( id1 INT, id2 INT, CONSTRAINT `foo` PRIMARY KEY (id1), CONSTRAINT `bar` UNIQUE KEY(id2)); +Warnings: +Warning 4145 Name 'foo' ignored for PRIMARY key. +DROP TABLE t1; diff --git a/mysql-test/main/create.test b/mysql-test/main/create.test index af5c427..9cd3d99 100644 --- a/mysql-test/main/create.test +++ b/mysql-test/main/create.test @@ -1941,3 +1941,10 @@ create table t1; # create table t1 (i int, j int, key(i), key(i)) as select 1 as i, 2 as j; drop table t1; + +# +# MDEV-17544 No warning when trying to name a primary key constraint. +# +CREATE TABLE t1 ( id1 INT, id2 INT, CONSTRAINT `foo` PRIMARY KEY (id1), CONSTRAINT `bar` UNIQUE KEY(id2)); +DROP TABLE t1; + diff --git a/mysql-test/main/system_mysql_db_fix50117.test b/mysql-test/main/system_mysql_db_fix50117.test index f8bef3d..d7c8258 100644 --- a/mysql-test/main/system_mysql_db_fix50117.test +++ b/mysql-test/main/system_mysql_db_fix50117.test @@ -27,13 +27,13 @@ use test; # create system tables as in mysql-5.1.17 -CREATE TABLE IF NOT EXISTS db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; +CREATE TABLE IF NOT EXISTS db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; -CREATE TABLE IF NOT EXISTS host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Cr eate_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; +CREATE TABLE IF NOT EXISTS host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Cr eate_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; -CREATE TABLE IF NOT EXISTS user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Referenc es_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N ','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, max_user_connections int(11) unsigned DEFAULT 0 NOT NULL, PRIMARY KEY Host (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; +CREATE TABLE IF NOT EXISTS user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Referenc es_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N ','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, max_user_connections int(11) unsigned DEFAULT 0 NOT NULL, PRIMARY KEY (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; CREATE TABLE IF NOT EXISTS func ( name char(64) binary DEFAULT '' NOT NULL, ret tinyint(1) DEFAULT '0' NOT NULL, dl char(128) DEFAULT '' NOT NULL, type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (name) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='User defined functions'; @@ -62,19 +62,19 @@ CREATE TABLE IF NOT EXISTS help_relation ( help_topic_id int unsigned not null r CREATE TABLE IF NOT EXISTS help_keyword ( help_keyword_id int unsigned not null, name char(64) not null, primary key (help_keyword_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help keywords'; -CREATE TABLE IF NOT EXISTS time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY Name (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; +CREATE TABLE IF NOT EXISTS time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; -CREATE TABLE IF NOT EXISTS time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY TzId (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; +CREATE TABLE IF NOT EXISTS time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; -CREATE TABLE IF NOT EXISTS time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; +CREATE TABLE IF NOT EXISTS time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; -CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; +CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; -CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; +CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; CREATE TABLE IF NOT EXISTS proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns char(64) DEFAULT '' NOT NULL, body longblob NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE' ) DEFAULT '' NOT NULL, comment char(64) collate utf8_bin DEFAULT '' NOT NULL, PRIMARY KEY (db,name,type) ) engine=MyISAM character set utf8 comment='Stored Procedures'; diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql index 8f28976..8126f5f 100644 --- a/scripts/mysql_system_tables.sql +++ b/scripts/mysql_system_tables.sql @@ -28,14 +28,14 @@ set system_versioning_alter_history=keep; set @have_innodb= (select count(engine) from information_schema.engines where engine='INNODB' and support != 'NO'); SET @innodb_or_myisam=IF(@have_innodb <> 0, 'InnoDB', 'MyISAM'); -CREATE TABLE IF NOT EXISTS db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(80) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_history_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; +CREATE TABLE IF NOT EXISTS db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(80) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_history_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY /*Host */(Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges'; -- Remember for later if db table already existed set @had_db_table= @@warning_count != 0; -CREATE TABLE IF NOT EXISTS host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Cr eate_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; +CREATE TABLE IF NOT EXISTS host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Cr eate_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY /*Host*/ (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges'; -CREATE TABLE IF NOT EXISTS user ( Host char(60) binary DEFAULT '' NOT NULL, User char(80) binary DEFAULT '' NOT NULL, Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Referenc es_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N ','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tablespace_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_history_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, max_user_connections int(11) DEFAULT 0 NOT NULL, plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL, authentication_string TEXT NOT NULL, password_expired ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, is_ role enu m('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, default_role char(80) binary DEFAULT '' NOT NULL, max_statement_time decimal(12,6) DEFAULT 0 NOT NULL, PRIMARY KEY Host (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; +CREATE TABLE IF NOT EXISTS user ( Host char(60) binary DEFAULT '' NOT NULL, User char(80) binary DEFAULT '' NOT NULL, Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Referenc es_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N ','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tablespace_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_history_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, max_user_connections int(11) DEFAULT 0 NOT NULL, plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL, authentication_string TEXT NOT NULL, password_expired ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, is_ role enu m('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, default_role char(80) binary DEFAULT '' NOT NULL, max_statement_time decimal(12,6) DEFAULT 0 NOT NULL, PRIMARY KEY /*Host*/ (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'; -- Remember for later if user table already existed set @had_user_table= @@warning_count != 0; @@ -68,19 +68,19 @@ CREATE TABLE IF NOT EXISTS help_relation ( help_topic_id int unsigned not null r CREATE TABLE IF NOT EXISTS help_keyword ( help_keyword_id int unsigned not null, name char(64) not null, primary key (help_keyword_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8 comment='help keywords'; -CREATE TABLE IF NOT EXISTS time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY Name (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; +CREATE TABLE IF NOT EXISTS time_zone_name ( Name char(64) NOT NULL, Time_zone_id int unsigned NOT NULL, PRIMARY KEY /*Name*/ (Name) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone names'; -CREATE TABLE IF NOT EXISTS time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY TzId (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; +CREATE TABLE IF NOT EXISTS time_zone ( Time_zone_id int unsigned NOT NULL auto_increment, Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY /*TzId*/ (Time_zone_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zones'; -CREATE TABLE IF NOT EXISTS time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY TzIdTranTime (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; +CREATE TABLE IF NOT EXISTS time_zone_transition ( Time_zone_id int unsigned NOT NULL, Transition_time bigint signed NOT NULL, Transition_type_id int unsigned NOT NULL, PRIMARY KEY /*TzIdTranTime*/ (Time_zone_id, Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transitions'; -CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY TzIdTrTId (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; +CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, Transition_type_id int unsigned NOT NULL, Offset int signed DEFAULT 0 NOT NULL, Is_DST tinyint unsigned DEFAULT 0 NOT NULL, Abbreviation char(8) DEFAULT '' NOT NULL, PRIMARY KEY /*TzIdTrTId*/ (Time_zone_id, Transition_type_id) ) engine=MyISAM CHARACTER SET utf8 comment='Time zone transition types'; -CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; +CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY /*TranTime*/ (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; CREATE TABLE IF NOT EXISTS proc (db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum( 'CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA') DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns longblob NOT NULL, body longblob NOT NULL, definer char(141) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'O RACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL', 'SIMULTANEOUS_ASSIGNMENT') DEFAULT '' NOT NULL, comment text collate utf8_bin NOT NULL, character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, aggregate enum('NONE', 'GROUP') DEFAULT 'NONE' NOT NULL, PRIMARY KEY (db,name,type)) engine=MyISAM character set utf8 comment='Stored Procedures'; @@ -229,7 +229,7 @@ SET @str=CONCAT(@cmd, ' ENGINE=', @innodb_or_myisam); #EXECUTE stmt; #DROP PREPARE stmt; -CREATE TABLE IF NOT EXISTS proxies_priv (Host char(60) binary DEFAULT '' NOT NULL, User char(80) binary DEFAULT '' NOT NULL, Proxied_host char(60) binary DEFAULT '' NOT NULL, Proxied_user char(80) binary DEFAULT '' NOT NULL, With_grant BOOL DEFAULT 0 NOT NULL, Grantor char(141) DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY Host (Host,User,Proxied_host,Proxied_user), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='User proxy privileges'; +CREATE TABLE IF NOT EXISTS proxies_priv (Host char(60) binary DEFAULT '' NOT NULL, User char(80) binary DEFAULT '' NOT NULL, Proxied_host char(60) binary DEFAULT '' NOT NULL, Proxied_user char(80) binary DEFAULT '' NOT NULL, With_grant BOOL DEFAULT 0 NOT NULL, Grantor char(141) DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY /*Host*/ (Host,User,Proxied_host,Proxied_user), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='User proxy privileges'; -- Remember for later if proxies_priv table already existed set @had_proxies_priv_table= @@warning_count != 0; diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 99af1c6..3ee5a34 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7898,3 +7898,5 @@ ER_KEY_DOESNT_SUPPORT eng "%s index %`s does not support this operation" ER_ALTER_OPERATION_TABLE_OPTIONS_NEED_REBUILD eng "Changing table options requires the table to be rebuilt" +ER_NAME_FOR_PRIMARY_IGNORED 42000 + eng "Name '%-.100s' ignored for PRIMARY key." diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 9719f55..2c878a2 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -3667,6 +3667,14 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info, my_error(ER_WRONG_NAME_FOR_INDEX, MYF(0), key->name.str); DBUG_RETURN(TRUE); } + if (key->type == Key::PRIMARY && key->name.str && + my_strcasecmp(system_charset_info, key->name.str, primary_key_name) != 0) + { + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, + ER_NAME_FOR_PRIMARY_IGNORED, + ER_THD(thd, ER_NAME_FOR_PRIMARY_IGNORED), + key->name.str); + } } tmp=file->max_keys(); if (*key_count > tmp)

1 0

[Commits] a099284: MDEV-19851 server_audit plugin should not allow server_audit_output_type=SYSLOG on Windows.
by holyfoot＠askmonty.org 03 Jul '19

03 Jul '19

revision-id: a099284b716b07af0d196705f44b828d621943d2 (mariadb-10.1.39-80-ga099284) parent(s): bf37b9fce9fc1cfb3fe096bc50c15c1bf53cd629 committer: Alexey Botchkov timestamp: 2019-07-03 16:41:01 +0400 message: MDEV-19851 server_audit plugin should not allow server_audit_output_type=SYSLOG on Windows. 'syslog' output type disabled on Windows. --- plugin/server_audit/server_audit.c | 17 ++++++++++++++++- 1 file changed, 16 insertions(+), 1 deletion(-) diff --git a/plugin/server_audit/server_audit.c b/plugin/server_audit/server_audit.c index 3da5264..16a677e 100644 --- a/plugin/server_audit/server_audit.c +++ b/plugin/server_audit/server_audit.c @@ -26,6 +26,7 @@ #include <fcntl.h> #ifndef _WIN32 +#define DO_SYSLOG #include <syslog.h> #else #define syslog(PRIORITY, FORMAT, INFO, MESSAGE_LEN, MESSAGE) do {}while(0) @@ -384,17 +385,31 @@ static MYSQL_SYSVAR_SET(events, events, PLUGIN_VAR_RQCMDARG, "Specifies the set of events to monitor. Can be CONNECT, QUERY, TABLE," " QUERY_DDL, QUERY_DML, QUERY_DML_NO_SELECT, QUERY_DCL.", NULL, NULL, 0, &events_typelib); +#ifdef DO_SYSLOG #define OUTPUT_SYSLOG 0 #define OUTPUT_FILE 1 +#else +#define OUTPUT_SYSLOG 0xFFFF +#define OUTPUT_FILE 0 +#endif /*DO_SYSLOG*/ + #define OUTPUT_NO 0xFFFF -static const char *output_type_names[]= { "syslog", "file", 0 }; +static const char *output_type_names[]= { +#ifdef DO_SYSLOG + "syslog", +#endif + "file", 0 }; static TYPELIB output_typelib= { array_elements(output_type_names) - 1, "output_typelib", output_type_names, NULL }; static MYSQL_SYSVAR_ENUM(output_type, output_type, PLUGIN_VAR_RQCMDARG, +#ifdef DO_SYSLOG "Desired output type. Possible values - 'syslog', 'file'" +#else + "Desired output type. Possible values - 'file'" +#endif " or 'null' as no output.", 0, update_output_type, OUTPUT_FILE, &output_typelib); static MYSQL_SYSVAR_STR(file_path, file_path, PLUGIN_VAR_RQCMDARG,

1 0

[Commits] 7d580ad1417: MDEV-19936: MyRocks: compile fails on Windows
by Sergei Petrunia 03 Jul '19

03 Jul '19

revision-id: 7d580ad141764e3751922ac9d349ae92ac6dc409 (mariadb-10.3.16-21-g7d580ad1417) parent(s): 1d45b3b055511d58c1e820ad497793f30871586e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-07-03 10:58:40 +0300 message: MDEV-19936: MyRocks: compile fails on Windows Don't compile table/mock_table.cc (pushing this patch to 10.3 first to make sure it fixes the issue will push to 10.2, too) --- storage/rocksdb/build_rocksdb.cmake | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) diff --git a/storage/rocksdb/build_rocksdb.cmake b/storage/rocksdb/build_rocksdb.cmake index 561e6cdefe6..22ec9f324f6 100644 --- a/storage/rocksdb/build_rocksdb.cmake +++ b/storage/rocksdb/build_rocksdb.cmake @@ -171,7 +171,10 @@ set(LIBS ${ROCKSDB_LIBS} ${THIRDPARTY_LIBS} ${SYSTEM_LIBS}) #add_subdirectory(${ROCKSDB_SOURCE_DIR}/tools) # Main library source code - +# Note : table/mock_table.cc must not be in the below list as it causes +# compile warnings-treated-as-errors on Windows, and it is only used in +# RocksDB's tests. +# set(ROCKSDB_SOURCES cache/clock_cache.cc cache/lru_cache.cc @@ -298,7 +301,6 @@ set(ROCKSDB_SOURCES table/iterator.cc table/merging_iterator.cc table/meta_blocks.cc - table/mock_table.cc table/persistent_cache_helper.cc table/plain/plain_table_builder.cc table/plain/plain_table_factory.cc

1 0

[Commits] 7ab258ec112: MDEV-19370: rpl.kill_race_condition failed in buildbot with Wrong value for slave parameter
by sujatha 28 Jun '19

28 Jun '19

revision-id: 7ab258ec112ebfb389e1fd0bb6a8ff44c0fb6eca (mariadb-10.1.39-71-g7ab258ec112) parent(s): 7f2cfa8f47cf05d8dbea65ced3656fd0fc6efbf5 author: Sujatha committer: Sujatha timestamp: 2019-06-28 21:50:56 +0530 message: MDEV-19370: rpl.kill_race_condition failed in buildbot with Wrong value for slave parameter Problem: ======= Executing test with following options will result in test failure. ./mtr rpl.kill_race_condition{,,,,,,,,,,} --repeat=10 --par 12 --mem Fix: ==== Test simulates applier thread kill scenario while applying a row event. But it doesn't wait for applier to catch the error stop. Added :wait_for_slave_sql_error.inc to catch the error. Test uses START SLAVE as a final step and doesn't wait for both threads to start. Added: start_slave.inc --- mysql-test/suite/rpl/r/kill_race_condition.result | 9 ++++++--- mysql-test/suite/rpl/t/kill_race_condition.test | 17 ++++++++++++----- 2 files changed, 18 insertions(+), 8 deletions(-) diff --git a/mysql-test/suite/rpl/r/kill_race_condition.result b/mysql-test/suite/rpl/r/kill_race_condition.result index e4e347dc786..48fd65e3a7c 100644 --- a/mysql-test/suite/rpl/r/kill_race_condition.result +++ b/mysql-test/suite/rpl/r/kill_race_condition.result @@ -1,13 +1,16 @@ include/master-slave.inc [connection master] -set global debug_dbug='d,rows_log_event_before_open_table'; -set debug_sync='now WAIT_FOR before_open_table'; create table t1 (a int); +set global debug_dbug='d,rows_log_event_before_open_table'; insert t1 values (1),(2),(3); +set debug_sync='now WAIT_FOR before_open_table'; kill slave_sql_thread; set debug_sync='now SIGNAL go_ahead_sql'; +include/wait_for_slave_sql_error.inc [errno=1927] +Last_SQL_Error = Error executing row event: 'Connection was killed' set global debug_dbug=''; set debug_sync='RESET'; drop table t1; -start slave; +include/start_slave.inc +Last_SQL_Error = include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/kill_race_condition.test b/mysql-test/suite/rpl/t/kill_race_condition.test index 4268c12cdbf..0f3b44864fe 100644 --- a/mysql-test/suite/rpl/t/kill_race_condition.test +++ b/mysql-test/suite/rpl/t/kill_race_condition.test @@ -2,27 +2,34 @@ source include/have_debug_sync.inc; source include/have_binlog_format_row.inc; source include/master-slave.inc; +connection master; +create table t1 (a int); +--sync_slave_with_master + connection slave; set global debug_dbug='d,rows_log_event_before_open_table'; -send set debug_sync='now WAIT_FOR before_open_table'; connection master; -create table t1 (a int); insert t1 values (1),(2),(3); connection slave; -reap; +set debug_sync='now WAIT_FOR before_open_table'; let $a=`select id from information_schema.processlist where state='debug sync point: now'`; replace_result $a slave_sql_thread; eval kill $a; set debug_sync='now SIGNAL go_ahead_sql'; +--let $slave_sql_errno= 1927 +--source include/wait_for_slave_sql_error.inc +let $error= query_get_value(SHOW SLAVE STATUS, Last_SQL_Error, 1); +--echo Last_SQL_Error = $error set global debug_dbug=''; set debug_sync='RESET'; - connection master; drop table t1; connection slave; -start slave; +--source include/start_slave.inc +let $error= query_get_value(SHOW SLAVE STATUS, Last_SQL_Error, 1); +--echo Last_SQL_Error = $error source include/rpl_end.inc;

1 0

[Commits] c56a8839dbf: MDEV-19834: Selectivity of an equality condition discounted twice
by Sergei Petrunia 28 Jun '19

28 Jun '19

revision-id: c56a8839dbf3bfc1fe0df7903eaf67e06c38329c (mariadb-10.4.5-94-gc56a8839dbf) parent(s): bb702c2e4c24632678b548ee1515c6a5b8173808 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-06-28 11:55:53 +0300 message: MDEV-19834: Selectivity of an equality condition discounted twice When discounting selectivity of ref access, don't discount the selectivity we've already discounted for range access. --- mysql-test/main/rowid_filter_innodb.result | 4 ++-- sql/sql_select.cc | 15 +++++++++------ 2 files changed, 11 insertions(+), 8 deletions(-) diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index d83239eb25f..390c7834a0a 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -2179,7 +2179,7 @@ count(*) 6 explain extended select count(*) from t1 where a in (22,83,11) and b=2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref b,a b 5 const 59 55.93 Using where +1 SIMPLE t1 ref b,a b 5 const 59 3.30 Using where Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11) select * from t1 where a in (22,83,11) and b=2; @@ -2196,7 +2196,7 @@ count(*) 6 explain extended select count(*) from t1 where a in (22,83,11) and b=2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref|filter b,a b|a 5|5 const 59 (3%) 55.93 Using where; Using rowid filter +1 SIMPLE t1 ref|filter b,a b|a 5|5 const 59 (3%) 3.30 Using where; Using rowid filter Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11) select * from t1 where a in (22,83,11) and b=2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 23827898160..e11aa991812 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9028,6 +9028,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, KEYUSE *keyuse= pos->key; KEYUSE *prev_ref_keyuse= keyuse; uint key= keyuse->key; + bool used_range_selectivity= false; /* Check if we have a prefix of key=const that matches a quick select. @@ -9053,6 +9054,7 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, keyparts++; } sel /= (double)table->quick_rows[key] / (double) table->stat_records(); + used_range_selectivity= true; } } @@ -9088,13 +9090,14 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, if (keyparts > keyuse->keypart) { /* Ok this is the keyuse that will be used for ref access */ - uint fldno; - if (is_hash_join_key_no(key)) - fldno= keyuse->keypart; - else - fldno= table->key_info[key].key_part[keyparts-1].fieldnr - 1; - if (keyuse->val->const_item()) + if (!used_range_selectivity && keyuse->val->const_item()) { + uint fldno; + if (is_hash_join_key_no(key)) + fldno= keyuse->keypart; + else + fldno= table->key_info[key].key_part[keyparts-1].fieldnr - 1; + if (table->field[fldno]->cond_selectivity > 0) { sel /= table->field[fldno]->cond_selectivity;

1 0

[Commits] 63e7837b1bd: Fix galera_as_slave_gtid.test galera_var_gtid_domain_id galera.mdev_10518.test
by sachin.setiya＠mariadb.com 27 Jun '19

27 Jun '19

revision-id: 63e7837b1bd1be0dbbba703d38f67cd4b0113ae8 (mariadb-10.1.39-72-g63e7837b1bd) parent(s): 85d23f0bd39dc76c7446bbae0b81379a4da18276 author: Sachin committer: Sachin timestamp: 2019-06-27 14:39:23 +0530 message: Fix galera_as_slave_gtid.test galera_var_gtid_domain_id galera.mdev_10518.test Clean the binlog on nodes after test is complete --- mysql-test/suite/galera/r/galera_as_slave_gtid.result | 4 ++++ mysql-test/suite/galera/r/galera_var_gtid_domain_id.result | 3 +++ mysql-test/suite/galera/r/mdev_10518.result | 3 +++ mysql-test/suite/galera/t/galera_as_slave_gtid.test | 10 ++++++++++ mysql-test/suite/galera/t/galera_var_gtid_domain_id.test | 7 +++++++ mysql-test/suite/galera/t/mdev_10518.test | 7 +++++++ 6 files changed, 34 insertions(+) diff --git a/mysql-test/suite/galera/r/galera_as_slave_gtid.result b/mysql-test/suite/galera/r/galera_as_slave_gtid.result index fbac7b1b6b5..db2bbf9ddaf 100644 --- a/mysql-test/suite/galera/r/galera_as_slave_gtid.result +++ b/mysql-test/suite/galera/r/galera_as_slave_gtid.result @@ -14,3 +14,7 @@ gtid_binlog_state_equal DROP TABLE t1; STOP SLAVE; RESET SLAVE ALL; +#cleanup +reset master; +reset master; +reset master; diff --git a/mysql-test/suite/galera/r/galera_var_gtid_domain_id.result b/mysql-test/suite/galera/r/galera_var_gtid_domain_id.result index 8e84236d5bf..0a9b699a8e1 100644 --- a/mysql-test/suite/galera/r/galera_var_gtid_domain_id.result +++ b/mysql-test/suite/galera/r/galera_var_gtid_domain_id.result @@ -71,4 +71,7 @@ wsrep_gtid_domain_id 9999 wsrep_gtid_mode 1 # On node_1 DROP TABLE t1, t2; +#cleanup +reset master; +reset master; # End of test diff --git a/mysql-test/suite/galera/r/mdev_10518.result b/mysql-test/suite/galera/r/mdev_10518.result index b2a3e0a65ef..f176c224ce9 100644 --- a/mysql-test/suite/galera/r/mdev_10518.result +++ b/mysql-test/suite/galera/r/mdev_10518.result @@ -71,4 +71,7 @@ wsrep_gtid_domain_id 4294967295 wsrep_gtid_mode 1 # On node_1 DROP TABLE t1, t2; +#cleanup +reset master; +reset master; # End of test diff --git a/mysql-test/suite/galera/t/galera_as_slave_gtid.test b/mysql-test/suite/galera/t/galera_as_slave_gtid.test index c2331a2ae05..a3c43540916 100644 --- a/mysql-test/suite/galera/t/galera_as_slave_gtid.test +++ b/mysql-test/suite/galera/t/galera_as_slave_gtid.test @@ -65,3 +65,13 @@ DROP TABLE t1; STOP SLAVE; RESET SLAVE ALL; + +--echo #cleanup +--connection node_1 +reset master; + +--connection node_2 +reset master; + +--connection node_3 +reset master; diff --git a/mysql-test/suite/galera/t/galera_var_gtid_domain_id.test b/mysql-test/suite/galera/t/galera_var_gtid_domain_id.test index c4127b4f655..015f25abf2f 100644 --- a/mysql-test/suite/galera/t/galera_var_gtid_domain_id.test +++ b/mysql-test/suite/galera/t/galera_var_gtid_domain_id.test @@ -48,6 +48,13 @@ source include/print_gtid.inc; # Cleanup DROP TABLE t1, t2; +--echo #cleanup +--connection node_1 +reset master; + +--connection node_2 +reset master; + --source include/galera_end.inc --echo # End of test diff --git a/mysql-test/suite/galera/t/mdev_10518.test b/mysql-test/suite/galera/t/mdev_10518.test index c4127b4f655..015f25abf2f 100644 --- a/mysql-test/suite/galera/t/mdev_10518.test +++ b/mysql-test/suite/galera/t/mdev_10518.test @@ -48,6 +48,13 @@ source include/print_gtid.inc; # Cleanup DROP TABLE t1, t2; +--echo #cleanup +--connection node_1 +reset master; + +--connection node_2 +reset master; + --source include/galera_end.inc --echo # End of test

1 0