- Add IS_DEPRECATED and DEPRECATION_REPLACEMENT columns to the
SYSTEM_VARIABLES information schema table
- Add a mysql-test case to verify the new columns output correctly
removed:
* DES encryption
* --secure-auth
* --old
* spider table options: bfz, btt, cmd, ctp, cwg, isa, ilm, ios, smd, stc, stl
extended under old-mode:
* YEAR(2), still available when old-mode=2_DIGIT_YEAR
un-deprecated:
* keep_files_on_create, originally (MDEV-23570) the idea was to make it
TRUE and deprecate. It cannot be removed when FALSE, but TRUE breaks
mariabackup.aria_backup where a table is altered from Aria to InnoDB
during a backup, so both t.MAD/t.MAI and t.ibd gets into a backup.
Definition
==========
Implement the SET PATH statement.
```
SET PATH 'schema1, schema2, schema3';
```
Function
--------
In MariaDB, the SQL-path will also be used to lookup packages in
package routine invocations, so the following script:
SET PATH 'sys';
SELECT UTL_ENCODE.BASE64_DECODE('data');
invokes SYS.UTL_ENCODE.BASE64_DECODE('data'), i.e. the function
BASE64_DECODE() in the packge UTL_ENCODE in the database SYS
(if such package and package routine exist).
MariaDB implementation details
------------------------------
The ticket's requirement stated the grammar should support delimited
identifiers with backticks or double quotes within the string quote,
ie: the single tick. A simple parser was implemented to handle this
requirement in Sql_path::from_text.
Only 16 schemas are supported per session - we do not want too many
schemas in the path anyway since it will impact performance. This also
means that the path cache is a simple static array for easier memory
management.
According to SQL standard, rows from `INFORMATION_SCHEMA.TRIGGERS` table
should be visible to users with non-SELECT privileges on the columns.
`ACTION_CONDITION`, `ACTION_STATEMENT` and `DEFINER` columns should be
visible only if the user is the owner of the schema.
MariaDB uses `TRIGGER` privilege instead of owner, which controls the
visibilty of all columns, including those which only need non-SELECT
privileges.
This fix
- Allows users with non-SELECT privileges- INSERT, DELETE or UPDATE,
to see rows in `INFORMATION_SCHEMA.TRIGGERS` table.
- Ensure `ACTION_CONDITION`, `ACTION_STATEMENT` and `DEFINER` columns
are `NULL` unless the user is the owner of the schema or has `TRIGGER`
privilege.
number-to-time conversion was too eagerly capping the value.
A string "9000090" was invalid time, because of 90 seconds.
But number-to-time was capping first, validating later,
to 9000090->time worked. Let's fix it.
also, let's make invalid time values in a string field include
the field name in the warning message, just like invalid time
values in a numeric field do.
This patch removes MASTER_SSL_VERIFY_SERVER_CERT from the list
reserved_keyword_udt_not_param_type and adds it to
keyword_func_sp_var_and_label in the parser. All other MASTER_SSL_* keywords
are in this list.
This allows MASTER_SSL_VERIFY_SERVER_CERT to be used unquoted when directly
accessed via INFORMATION_SCHEMA.slave_status, as well as opens it up to be
used as names in other places, e.g. stored procedures.
Reviewed-by: Brandon Nesterenko <brandon.nesterenko@mariadb.com>
MariaDB 11.8 and above now supports `DEFAULT` values for parameters in
stored procedures and functions. This commit introduces the
`INFORMATION_SCHEMA.PARAMETERS.PARAMETER_DEFAULT` column to display the
default value of parameters.
The value is only shown if the current user or any of it's enabled roles
is the owner of parameter's `SPECIFIC_SCHEMA`, otherwise `NULL`.
MariaDB 11.8 and above allows to specify list of columns in the update
trigger and such trigger is fired only when one of the specified columns
is updated. The `INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS` table
lists all such columns for a trigger.
A column is shown only if the user has any non-SELECT privileges on the
column specified in the trigger.
* automatically disable ps2 and cursor protocol when the
select statement returns no result set
* remove manual {disable|enable}_{ps2|cursor}_protocol from around
`select ... into` in tests
* other misc collateral test cleanups
Cherry-pick from 11.8
let's always disconnect a user connection before dropping the said user.
MariaDB is traditionally very tolerant to active connections
of the dropped user, which isn't the case for most other databases.
Let's avoid unintentionally spreading incompatible behavior
and disconnect before drop.
Except in cases when the test specifically tests such a behavior.
Remove one of the major sources of race condiitons in mariadb-test.
Normally, mariadb_close() sends COM_QUIT to the server and immediately
disconnects. In mariadb-test it means the test can switch to another
connection and sends queries to the server before the server even
started parsing the COM_QUIT packet and these queries can see the
connection as fully active, as it didn't reach dispatch_command yet.
This is a major source of instability in tests and many - but not all,
still less than a half - tests employ workarounds. The correct one
is a pair count_sessions.inc/wait_until_count_sessions.inc.
Also very popular was wait_until_disconnected.inc, which was completely
useless, because it verifies that the connection is closed, and after
disconnect it always is, it didn't verify whether the server processed
COM_QUIT. Sadly the placebo was as widely used as the real thing.
Let's fix this by making mariadb-test `disconnect` command _to wait_ for
the server to confirm. This makes almost all workarounds redundant.
In some cases count_sessions.inc/wait_until_count_sessions.inc is still
needed, though, as only `disconnect` command is changed:
* after external tools, like `exec $MYSQL`
* after failed `connect` command
* replication, after `STOP SLAVE`
* Federated/CONNECT/SPIDER/etc after `DROP TABLE`
and also in some XA tests, because an XA transaction is dissociated from
the THD very late, after the server has closed the client connection.
Collateral cleanups: fix comments, remove some redundant statements:
* DROP IF EXISTS if nothing is known to exist
* DROP table/view before DROP DATABASE
* REVOKE privileges before DROP USER
etc
Before MySQL 4.0.18, user-specified constraint names were ignored.
Starting with MySQL 4.0.18, the specified constraint name was
prepended with the schema name and '/'. Now we are transforming
into a format where the constraint name is prepended with the
dict_table_t::name and the impossible UTF-8 sequence 0xff.
Generated constraint names will be ASCII decimal numbers.
On upgrade, old FOREIGN KEY constraint names will be displayed
without any schema name prefix. They will be updated to the new
format on DDL operations.
dict_foreign_t::sql_id(): Return the SQL constraint name
without any schemaname/tablename\377 or schemaname/ prefix.
row_rename_table_for_mysql(), dict_table_rename_in_cache():
Simplify the logic: Just rename constraints to the new format.
dict_table_get_foreign_id(): Replaces dict_table_get_highest_foreign_id().
innobase_get_foreign_key_info(): Let my_error() refer to erroneous
anonymous constraints as "(null)".
row_delete_constraint(): Try to drop all 3 constraint name variants.
Reviewed by: Thirunarayanan Balathandayuthapani
Tested by: Matthias Leich
Adding a new column in INFORMATION_SCHEMA.COLLATIONS:
PAD_ATTRIBUTE ENUM('PAD SPACE','NO PAD')
and a new column Pad_attribute into SHOW COLLATION.
The new column has been added after SORTLEN but before COMMENT.
This order is compatible with MySQL-8.0 order,
with the exception that MariaDB has an extra last column COMMENT:
MariaDB [test]> desc information_schema.collations;
+--------------------+----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+----------------------------+------+-----+---------+-------+
| COLLATION_NAME | varchar(64) | NO | | NULL | |
| CHARACTER_SET_NAME | varchar(32) | YES | | NULL | |
| ID | bigint(11) | YES | | NULL | |
| IS_DEFAULT | varchar(3) | YES | | NULL | |
| IS_COMPILED | varchar(3) | NO | | NULL | |
| SORTLEN | bigint(3) | NO | | NULL | |
| PAD_ATTRIBUTE | enum('PAD SPACE','NO PAD') | NO | | NULL | |
| COMMENT | varchar(80) | NO | | NULL | |
+--------------------+----------------------------+------+-----+---------+-------+
The new Pad_attribute in SHOW COLLATION has been added as the last column.
This is also compatible with MySQL:
MariaDB [test]> show collation like 'utf8mb4_bin';
+-------------+---------+------+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+-------------+---------+------+---------+----------+---------+---------------+
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE |
+-------------+---------+------+---------+----------+---------+---------------+
when a definer for SP/view is wrong - it shold be ER_MALFORMED_DEFINER,
not ER_NO_SUCH_USER
when one uses current_role as a definer or grantee but there's no
current role - it should be ER_INVALID_ROLE not ER_MALFORMED_DEFINER
when a non-existent user is specified - it should be ER_NO_SUCH_USER,
which should say "The user does not exist", not "Definer does not exist"
clarify ER_CANT_CHANGE_TX_CHARACTERISTICS to say what cannot be changed
Added capability to create a trigger associated with several trigger
events. For this goal, the syntax of the CREATE TRIGGER statement
was extended to support the syntax structure { event [ OR ... ] }
for the `trigger_event` clause. Since one trigger will be able to
handle several events it should be provided a way to determine what
kind of event is handled on execution of a trigger. For this goal
support of the clauses INSERTING, UPDATING , DELETING was added by
this patch. These clauses can be used inside a trigger body to detect
what kind of trigger action is currently processed using the following
boilerplate:
IF INSERTING THEN ...
ELSIF UPDATING THEN ...
ELSIF DELETING THEN ...
In case one of the clauses INSERTING, UPDATING, DELETING specified in
a trigger's body not matched with a trigger event type, the error
ER_INCOMPATIBLE_EVENT_FLAG is emitted.
After this patch be pushed, one Trigger object will be associated with
several trigger events. It means that the array
Table_triggers_list::triggers
can contain several pointers to the same Trigger object in array members
corresponding to different events. Moreover, support of several trigger
events for the same trigger requires that the data members `next` and
`action_order` of the Trigger class be converted to arrays to store
relating information per trigger event base.
Ability to specify the same trigger for different event types results in
necessity to handle invalid cases on execution of the multi-event
trigger, when the OLD or NEW qualifiers doesn't match a current event
type against that the trigger is run. The clause OLD should produces
the NULL value for INSERT event, whereas the clause NEW should produce
the NULL value for DELETE event.
When the IO thread (re)connect to a primary,
no updates are available besides unique errors that cause the failure.
These new `Master_info` numbers supplement SHOW SLAVE STATUS’s (most-
recent) ‘Connecting’ state with statistics on (re)connect attempts:
* `Connects_Tried`: how many retries have been attempted so far
This was previously a local variable that only counted re-attempts;
it’s now meaningful even after the “Connecting” state concludes.
* `Master_Retry_Count` (from MDEV-25674): out of how many configured
Side-note: Some of the tests updated by this commit dump the entire
SHOW SLAVE STATUS, which might include non-deterministic entries.
Reviewed-by: Kristian Nielsen <knielsen@knielsen-hq.org>
Reviewed-by: Brandon Nesterenko <brandon.nesterenko@mariadb.com>
* automatically disable ps2 and cursor protocol when the
select statement returns no result set
* remove manual {disable|enable}_{ps2|cursor}_protocol from around
`select ... into` in tests
* other misc collateral test cleanups
* rpl.rpl_system_versioning_partitions updated for MDEV-32188
* innodb.row_size_error_log_warnings_3 changed error for MDEV-33658
(checks are done in a different order)
Update all integer columns of SHOW REPLICA STATUS (technically
INFORMATION_SCHEMA.SLAVE_STATUS) to unsigned because, well, they are (:.
Some `uint32` ones were accidentally using the `Field::store(double nr)`
overload because they forgot the `, true` for
`Field::store(longlong nr, bool unsigned_val)`.
The mistake’s harmless, fortunately, as `double` supports over 15
significant decimal digits, well over `uint32`’s 9-and-a-half.
Resize the types and widths of SHOW REPLICA STATUS
(technically `INFORMATION_SCHEMA.SLAVE_STATUS`)
columns to better match their possible values
In case of intentionally but absurdly long lists,
text columns that list an uncapped number of elements
have expanded to accept as many bytes as we could support.
Particularly, the first-gen `Replicate_` filters were
incorrectly typed as singlular `Name()`s during MDEV-33526.
Under `Name`s’ 64-char limit, they could overflow
(read: truncate) even before their lengths got absurd.
In response to `‘MAX_SLAVE_ERRMSG’ was not declared in this scope` in
Embedded builds, a new `#ifdef HAVE_REPLICATION` guard wraps
`slave_status_info` to skip this unused data in Replication-less builds.
For testing, this commit forward-ports a modified cherry-pick of #3795
(the latter targets our oldest maintained LTS as part of MDEV-35948).
> Assert that 1st-gen `replicate_*` filter variables display
> their input – including long but reasonable lists –
> correctly (without truncation) in
> * direct SELECT
> * [semi-new] INFORMATION_SCHEMA.GLOBAL_VARIABLES.VARIABLE_VALUE
> * [new] SHOW REPLICA STATUS
Reviewed-by: Brandon Nesterenko <brandon.nesterenko@mariadb.com>
normalize_cond() translated `WHERE col` into `WHERE col<>0`
But the opetator "not equal to 0" does not necessarily exists
for all data types.
For example, the query:
SELECT * FROM t1 WHERE inet6col;
was translated to:
SELECT * FROM t1 WHERE inet6col<>0;
which further failed with this error:
ERROR : Illegal parameter data types inet6 and bigint for operation '<>'
This patch changes the translation from `col<>0` to `col IS TRUE`.
So now
SELECT * FROM t1 WHERE inet6col;
gets translated to:
SELECT * FROM t1 WHERE inet6col IS TRUE;
Details:
1. Implementing methods:
- Field_longstr::val_bool()
- Field_string::val_bool()
- Item::val_int_from_val_str()
If the input contains bad data,
these methods raise a better error message:
Truncated incorrect BOOLEAN value
Before the change, the error was:
Truncated incorrect DOUBLE value
2. Fixing normalize_cond() to generate Item_func_istrue/Item_func_isfalse
instances instead of Item_func_ne/Item_func_eq
3. Making Item_func_truth sargable, so it uses the range optimizer.
Implementing the following methods:
- get_mm_tree(), get_mm_leaf(), add_key_fields() in Item_func_truth.
- get_func_mm_tree(), for all Item_func_truth descendants.
4. Implementing the method negated_item() for all Item_func_truth
descendants, so the negated item has a chance to be sargable:
For example,
WHERE NOT col IS NOT FALSE -- this notation is not sargable
is now translated to:
WHERE col IS FALSE -- this notation is sargable
This is done by mapping most of the existing MySQL unicode 0900 collations
to MariadB 1400 unicode collations. The assumption is that 1400 is a super
set of 0900 for all practical purposes.
I also added a new function 'compare_collations()' and changed most code
to use this instead of comparing character sets directly.
This enables one to seamlessly mix-and-match the corresponding 0900 and
1400 sets. Field comparision and alter table treats the character sets
as identical.
All MySQL 8.0 0900 collations are supported except:
- utf8mb4_ja_0900_as_cs
- utf8mb4_ja_0900_as_cs_ks
- utf8mb4_ru_0900_as_cs
- utf8mb4_zh_0900_as_cs
These do not have corresponding entries in the MariadB 01400 collations.
Other things:
- Added COMMENT colum to information_schema.collations. For utf8mb4_0900
colletions it contains the corresponding alias collation.
The limit of socket length on unix according to libc is 108, see
sockaddr_un::sun_path, but in the table it is a string of max length
64, which results in truncation of socket and failure to connect by
plugins using servers such as spider.
