- developers - lists.mariadb.org

Re: [Maria-developers] 5154e224a97: MDEV-5215 Granted to PUBLIC
by Sergei Golubchik 25 Aug '22

25 Aug '22

Hi, Oleksandr, > commit 5154e224a97 > Author: Oleksandr Byelkin <sanja(a)mariadb.com> > Date: Mon Dec 13 16:15:21 2021 +0100 > > diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql > index fd2f1c95dda..a1721913b2c 100644 > --- a/scripts/mysql_system_tables.sql > +++ b/scripts/mysql_system_tables.sql > @@ -47,6 +47,10 @@ INSERT IGNORE INTO global_priv SELECT * FROM tmp_user_sys WHERE 0 <> @need_sys_u > DROP TABLE … [View More]tmp_user_sys; > > > +-- This special "role" needed for GRAND ... TO PUBLIC > +INSERT IGNORE INTO global_priv (Host,User,Priv) VALUES ('', 'PUBLIC', concat('{"access":0,"version_id":',regexp_replace(regexp_replace(@@version, '\\b\\d\\b', '0\\0'), '\\D', ''),',"is_role":true}')); 1. why here and not in mysql_system_tables_data.sql ? 2. why this version magic? mysql_system_tables_data.sql doesn't do it for other accounts 3. why do you do it at all? I thought the server can create this role as needed > + > + > CREATE DEFINER='mariadb.sys'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS user AS SELECT > Host, > User, > @@ -95,7 +99,9 @@ CREATE DEFINER='mariadb.sys'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS > ELT(IFNULL(JSON_VALUE(Priv, '$.is_role'), 0) + 1, 'N', 'Y') AS is_role, > IFNULL(JSON_VALUE(Priv, '$.default_role'), '') AS default_role, > CAST(IFNULL(JSON_VALUE(Priv, '$.max_statement_time'), 0.0) AS DECIMAL(12,6)) AS max_statement_time > - FROM global_priv; > + FROM global_priv > +-- Do not show special role for GRANT TO PUBLIC > + WHERE not (Host = "" and User = "PUBLIC"); Why? I don't think it's wrong to show a new PUBLIC role. > > -- Remember for later if user table already existed > set @had_user_table= @@warning_count != 0; > diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc > index 601cae2e945..aa9bd91ec52 100644 > --- a/sql/sql_acl.cc > +++ b/sql/sql_acl.cc > @@ -316,6 +318,13 @@ static bool show_table_and_column_privileges(THD *, const char *, const char *, > static int show_routine_grants(THD *, const char *, const char *, > const Sp_handler *sph, char *, int); > > +static ACL_ROLE *acl_public= NULL; > + > +inline privilege_t public_access() > +{ > + return (acl_public ? acl_public->access : NO_ACL); > +} Why not to have acl_public always not null? e.g. define it as static ACL_ROLE acl_public; so it's not even a pointer. And don't store it in the dynamic array. Or make it like static ACL_ROLE acl_public_dummy, *acl_public= &acl_public_dummy; then you can keep he real role in the dynamic array, but still acl_public will never be null. but in the standard PUBLIC is not a role at all, so may be it'd be simpler not to store it in the dynamic array > + > class Grant_tables; > class User_table; > class Proxies_priv_table; > @@ -3148,7 +3183,7 @@ bool acl_getroot(Security_context *sctx, const char *user, const char *host, > } > else // Role, not User > { > - ACL_ROLE *acl_role= find_acl_role(user); > + ACL_ROLE *acl_role= find_acl_role(user, false); are there tests for that? > if (acl_role) > { > res= 0; > @@ -3161,6 +3196,26 @@ bool acl_getroot(Security_context *sctx, const char *user, const char *host, > } > } > > + /* > + PUBLIC magic: > + > + Note: for usual user privileges of 3 component merged together: > + 1) user privileges > + 2) set role privileges > + 3) public privileges > + But for this routine (used in Security_context::change_security_context) > + only 2 component merged: > + 1) user OR role privileges we are switching to because if we're switching to a user, there can be no set role? I feel this comment is more confusing, then helping, really, I'd suggest to remove it completely > + 2) public privileges > + */ > + if (acl_public) > + { > + if (ACL_DB *acl_db= acl_db_find(db, public_name.str, "", "", FALSE)) > + sctx->db_access|= acl_db->access; > + > + sctx->master_access|= acl_public->access; > + } > + > mysql_mutex_unlock(&acl_cache->lock); > DBUG_RETURN(res); > } > @@ -3320,20 +3375,22 @@ int acl_setrole(THD *thd, const char *rolename, privilege_t access) > /* merge the privileges */ > Security_context *sctx= thd->security_ctx; > sctx->master_access= access; > - if (thd->db.str) > - sctx->db_access= acl_get(sctx->host, sctx->ip, sctx->user, thd->db.str, FALSE); > - > if (!strcasecmp(rolename, "NONE")) > { > thd->security_ctx->priv_role[0]= 0; > } > else > { > - if (thd->db.str) > - sctx->db_access|= acl_get("", "", rolename, thd->db.str, FALSE); > /* mark the current role */ > strmake_buf(thd->security_ctx->priv_role, rolename); > } > + if (thd->db.str) > + sctx->db_access= acl_get_all3(sctx, thd->db.str, FALSE); > + > + // PUBLIC magic > + if (acl_public) > + sctx->master_access|= acl_public->access; why not to do it in check_user_can_set_role()? Like - *access = acl_user->access | role->access; + *access = acl_user->access | role->access | acl_public->access; > + > return 0; > } > > @@ -3347,9 +3404,13 @@ static uchar* check_get_key(ACL_USER *buff, size_t *length, > > static void acl_update_role(const char *rolename, const privilege_t privileges) > { > - ACL_ROLE *role= find_acl_role(rolename); > + ACL_ROLE *role= find_acl_role(rolename, true); > if (role) > + { > role->initial_role_access= role->access= privileges; > + if (strcasecmp(rolename, public_name.str) == 0) > + acl_public= role; why? I mean, in what case can acl_public != role here? > + } > } > > > @@ -3638,6 +3703,23 @@ privilege_t acl_get(const char *host, const char *ip, > DBUG_RETURN(db_access & host_access); > } > > +/* > + Check if there is access for the host/user, role, public on the database > +*/ > + > +privilege_t acl_get_all3(Security_context *sctx, const char *db, > + bool db_is_patern) > +{ > + privilege_t access= acl_get(sctx->host, sctx->ip, > + sctx->priv_user, db, db_is_patern); > + if (sctx->priv_role[0]) > + access|= acl_get("", "", sctx->priv_role, db, db_is_patern); > + if (acl_public) > + access|= acl_get("", "", public_name.str, db, db_is_patern); how can this be optimized, considering that in most cases and for most db names there will be no grants to role and public? A couple of ideas: maintain counters total_number_of_db_grants_to_roles ad total_number_of_db_grants_to_public. This is rather crude. keep grants to users, to roles, and to public in separate caches. or in different dynamic arrays (won't help much in a default setup) keep propery per ACL_DB or shared between all ACL_DB's of one DB? that'd be rather tricky to maintain > + return access; > +} > + > + > /* > Check if there are any possible matching entries for this host > > @@ -3759,7 +3841,7 @@ static bool add_role_user_mapping(const char *uname, const char *hname, > const char *rname) > { > ACL_USER_BASE *grantee= find_acl_user_base(uname, hname); > - ACL_ROLE *role= find_acl_role(rname); > + ACL_ROLE *role= find_acl_role(rname, true); why? > > if (grantee == NULL || role == NULL) > return 1; > @@ -4247,7 +4329,7 @@ bool is_acl_user(const char *host, const char *user) > if (*host) // User > res= find_user_exact(host, user) != NULL; > else // Role > - res= find_acl_role(user) != NULL; > + res= find_acl_role(user, false) != NULL; this disallows DEFINER PUBLIC, right? I couldn't find a test for that. > > mysql_mutex_unlock(&acl_cache->lock); > return res; > @@ -7537,11 +7620,11 @@ bool mysql_grant_role(THD *thd, List <LEX_USER> &list, bool revoke) > if (user->host.str) > hostname= user->host; > else > - if ((role_as_user= find_acl_role(user->user.str))) > + if ((role_as_user= find_acl_role(user->user.str, false))) Why is it false here? It looks like it disallows GRANT role TO PUBLIC > hostname= empty_clex_str; > else > { > - if (is_invalid_role_name(username.str)) > + if (check_role_name(username.str, false) == ROLE_NAME_INVALID) > { > append_user(thd, &wrong_users, &username, &empty_clex_str); > result= 1; > @@ -8280,19 +8355,14 @@ bool check_grant(THD *thd, privilege_t want_access, TABLE_LIST *tables, > if (any_combination_will_do) > continue; > > - t_ref->grant.grant_table_user= grant_table; // Remember for column test > - t_ref->grant.grant_table_role= grant_table_role; > - t_ref->grant.version= grant_version; > - t_ref->grant.privilege|= grant_table ? grant_table->privs : NO_ACL; > - t_ref->grant.privilege|= grant_table_role ? grant_table_role->privs : NO_ACL; > + t_ref->grant.privilege|= t_ref->grant.aggregate_privs(); > t_ref->grant.want_privilege= ((want_access & COL_ACLS) & ~t_ref->grant.privilege); > > if (!(~t_ref->grant.privilege & want_access)) > continue; > > - if ((want_access&= ~((grant_table ? grant_table->cols : NO_ACL) | > - (grant_table_role ? grant_table_role->cols : NO_ACL) | > - t_ref->grant.privilege))) > + if ((want_access&= ~(t_ref->grant.aggregate_cols() | > + t_ref->grant.privilege))) > { > goto err; // impossible > } 1. why is this if() here, if it's impossible? 2. I'm a bit worried that you set t_ref->grant.version early when it's still possible to goto err. Old code was setting the version very late after all checks have succeeded. > @@ -8335,6 +8405,49 @@ static void check_grant_column_int(GRANT_TABLE *grant_table, const char *name, > } > } > > +inline privilege_t GRANT_INFO::aggregate_privs() > +{ > + return (grant_table_user ? grant_table_user->privs : NO_ACL) | > + (grant_table_role ? grant_table_role->privs : NO_ACL) | > + (grant_public ? grant_public->privs : NO_ACL); > +} > + > +inline privilege_t GRANT_INFO::aggregate_cols() > +{ > + return (grant_table_user ? grant_table_user->cols : NO_ACL) | > + (grant_table_role ? grant_table_role->cols : NO_ACL) | > + (grant_public ? grant_public->cols : NO_ACL); > +} > + > +void GRANT_INFO::refresh(const Security_context *sctx, > + const char *db, const char *table) > +{ > + if (version != grant_version) > + read(sctx, db, table); > +} > + > +void GRANT_INFO::read(const Security_context *sctx, > + const char *db, const char *table) > +{ > +#ifdef EMBEDDED_LIBRARY > + grant_table_user= grant_table_role= grant_public= NULL; > +#else > + grant_table_user= > + table_hash_search(sctx->host, sctx->ip, db, > + sctx->priv_user, > + table, FALSE); /* purecov: inspected */ you don't need to preserve purecov comments when moving the code around > + grant_table_role= > + sctx->priv_role[0] ? table_hash_search("", NULL, db, > + sctx->priv_role, > + table, TRUE) : NULL; > + grant_public= > + acl_public ? table_hash_search("", NULL, db, > + public_name.str, > + table, TRUE) : NULL; > +#endif > + version= grant_version; /* purecov: inspected */ > +} > + > /* > Check column rights in given security context > > @@ -9533,6 +9626,13 @@ static bool show_global_privileges(THD *thd, ACL_USER_BASE *acl_entry, > want_access= ((ACL_ROLE *)acl_entry)->initial_role_access; > else > want_access= acl_entry->access; > + > + // suppress "GRANT USAGE ON *.* TO `PUBLIC`" why? I agree that it's pointless, but disabling just it is kind of difficult to explain. > + if (!(want_access & ~GRANT_ACL) && > + acl_entry->user.length == public_name.length && > + strcasecmp(acl_entry->user.str, public_name.str) == 0) > + return FALSE; > + > if (test_all_bits(want_access, (GLOBAL_ACLS & ~ GRANT_ACL))) > global.append(STRING_WITH_LEN("ALL PRIVILEGES")); > else if (!(want_access & ~GRANT_ACL)) > @@ -11015,10 +11116,15 @@ bool mysql_drop_user(THD *thd, List <LEX_USER> &list, bool handle_as_role) > { > int rc; > user_name= get_current_user(thd, tmp_user_name, false); > - if (!user_name) > + if (!user_name || (handle_as_role && > + (strcasecmp(user_name->user.str, > + public_name.str) == 0))) better add this condition to the following if(), not to this one. Like: - if (handle_as_role != user_name->is_role()) + if (handle_as_role != user_name->is_role() || + (handle_as_role && check_role_name(...) == ROLE_NAME_INVALID)) > { > thd->clear_error(); > - append_str(&wrong_users, STRING_WITH_LEN("CURRENT_ROLE")); > + if (!user_name) > + append_str(&wrong_users, STRING_WITH_LEN("CURRENT_ROLE")); > + else > + append_str(&wrong_users, public_name.str, public_name.length); > result= TRUE; > continue; > } > diff --git a/sql/sql_show.cc b/sql/sql_show.cc > index 6290a47ee30..143e647f74c 100644 > --- a/sql/sql_show.cc > +++ b/sql/sql_show.cc > @@ -5304,7 +5300,7 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) > &thd->col_access, NULL, 0, 1) || > (!thd->col_access && check_grant_db(thd, db_name->str))) || > sctx->master_access & (DB_ACLS | SHOW_DB_ACL) || > - acl_get(sctx->host, sctx->ip, sctx->priv_user, db_name->str, 0)) > + acl_get_all3(sctx, db_name->str, 0)) Was it a bug? That one couldn't see db's if privileges were granted via roles? > #endif > { > Dynamic_array<LEX_CSTRING*> table_names(PSI_INSTRUMENT_MEM); > diff --git a/mysql-test/main/public_basic.test b/mysql-test/main/public_basic.test > new file mode 100644 > index 00000000000..3f5993dfe97 > --- /dev/null > +++ b/mysql-test/main/public_basic.test > @@ -0,0 +1,93 @@ > +SHOW GRANTS FOR PUBLIC; > + > +--echo # it is not PUBLIC but an user > +--echo # (this should work as it allowed for roles for example) > +create user PUBLIC; > +create user PUBLIC@localhost; > +GRANT SELECT on test.* to PUBLIC@localhost; > +drop user PUBLIC@localhost; > +drop user PUBLIC; > + > +--echo # preinstalled PUBLIC > +GRANT SELECT on test.* to PUBLIC; > +GRANT SELECT on mysql.db to PUBLIC; > +--replace_regex /"version_id"\:[0-9]+/"version_id":VERSION/ > +select * from mysql.global_priv where user="PUBLIC" ; > + > +SHOW GRANTS FOR PUBLIC; > + > +GRANT UPDATE on test.* to PUBLIC; > +GRANT UPDATE on mysql.db to PUBLIC; > + > +SHOW GRANTS FOR PUBLIC; > + > +REVOKE SELECT on test.* from PUBLIC; > +REVOKE SELECT on mysql.db from PUBLIC; > + > +SHOW GRANTS FOR PUBLIC; > + > +REVOKE UPDATE on test.* from PUBLIC; > +REVOKE UPDATE on mysql.db from PUBLIC; > + > +--error ER_NONEXISTING_GRANT > +REVOKE UPDATE on test.* from PUBLIC; > +--error ER_NONEXISTING_TABLE_GRANT > +REVOKE UPDATE on mysql.db from PUBLIC; > + > +SHOW GRANTS FOR PUBLIC; > + > +--echo # automaticly added PUBLIC > +delete from mysql.global_priv where user="PUBLIC"; > +flush privileges; > +select * from mysql.global_priv where user="PUBLIC" ; > +GRANT SELECT on test.* to PUBLIC; > +GRANT SELECT on mysql.db to PUBLIC; > +--replace_regex /"version_id"\:[0-9]+/"version_id":VERSION/ > +select * from mysql.global_priv where user="PUBLIC" ; > + > +SHOW GRANTS FOR PUBLIC; > + > +GRANT UPDATE on test.* to PUBLIC; > +GRANT UPDATE on mysql.db to PUBLIC; > + > +SHOW GRANTS FOR PUBLIC; > + > +REVOKE SELECT on test.* from PUBLIC; > +REVOKE SELECT on mysql.db from PUBLIC; > + > +SHOW GRANTS FOR PUBLIC; > + > +REVOKE UPDATE on test.* from PUBLIC; > +REVOKE UPDATE on mysql.db from PUBLIC; > + > +SHOW GRANTS FOR PUBLIC; > + > +--error ER_INVALID_ROLE > +GRANT XXXXXX TO CURRENT_USER; > +--echo # following should fail with the same error as above > +--error ER_INVALID_ROLE > +GRANT PUBLIC TO CURRENT_USER; > + > +--error ER_INVALID_ROLE > +REVOKE XXXXXX FROM CURRENT_USER; > +--echo # following should fail with the same error as above > +--error ER_INVALID_ROLE > +REVOKE PUBLIC FROM CURRENT_USER; > +--error ER_CANNOT_USER > + > +drop role XXXXXX; > +--echo # following should fail with the same error as above > +--error ER_CANNOT_USER > +drop role PUBLIC; > + > +--error ER_INVALID_ROLE > +SET ROLE XXXXXX; > +--echo # following should fail with the same error as above > +--error ER_INVALID_ROLE > +SET ROLE PUBLIC; > + > +--error ER_INVALID_ROLE > +SET DEFAULT ROLE XXXXXX; > +--echo # following should fail with the same error as above > +--error ER_INVALID_ROLE > +SET DEFAULT ROLE PUBLIC; This is good, but one can update global_priv table directly and put the default_role=PUBLIC there. Please, add a test for that. and also a couple of tests for `public` (in backticks) > diff --git a/mysql-test/main/public_privileges.test b/mysql-test/main/public_privileges.test > new file mode 100644 > index 00000000000..a542376f05c > --- /dev/null > +++ b/mysql-test/main/public_privileges.test > @@ -0,0 +1,293 @@ > +--echo # > +--echo # Test DB/TABLE/COLUMN privileges in queries > +--echo # > + > +SHOW GRANTS FOR PUBLIC; > + > +create user testuser; > +create database testdb1; > +use testdb1; > +create table t1 (a int, b int); > +insert into t1 values (1,2); > +create database testdb2; > +use testdb2; > +create table t2 (a int, b int); > +insert into t2 values (1,2); > +create table t3 (a int, b int); > +insert into t3 values (1,2); > + > +connect (testuser,localhost,testuser,,); > +connection testuser; "connect testuser" also switches to this connection, the following "connection testuser" is redundant > +--error ER_TABLEACCESS_DENIED_ERROR > +select * from testdb1.t1; > +--error ER_TABLEACCESS_DENIED_ERROR > +select * from testdb2.t2; > +--error ER_TABLEACCESS_DENIED_ERROR > +select b from testdb2.t3; > +--error ER_TABLEACCESS_DENIED_ERROR > +select a from testdb2.t3; > + > +connection default; > + > +GRANT SELECT ON testdb1.* to PUBLIC; > +GRANT SELECT ON testdb2.t2 to PUBLIC; > +GRANT SELECT (b) ON testdb2.t3 to PUBLIC; > + > +disconnect testuser; > +connect (testuser,localhost,testuser,,); > +connection testuser; > +select * from testdb1.t1; > +select * from testdb2.t2; > +select b from testdb2.t3; > +--error ER_COLUMNACCESS_DENIED_ERROR > +select a from testdb2.t3; > + > +connection default; > + > +--replace_column 1 # 3 # 6 # 7 # > +SHOW PROCESSLIST; why? > + > +connection default; > + > +use test; > +disconnect testuser; > +REVOKE SELECT ON testdb1.* from PUBLIC; > +REVOKE SELECT ON testdb2.t2 from PUBLIC; > +REVOKE SELECT (b) ON testdb2.t3 from PUBLIC; may be `revoke all privileges, grant option` ? looks like a good place to have it tested > +drop user testuser; > +drop database testdb1; > +drop database testdb2; > + > +--echo # > +--echo # test global process list privilege and EXECUTE db level > +--echo # > + > +create user testuser; > +create database testdb; > +use testdb; > +create procedure p1 () select 1; > + > +connect (testuser,localhost,testuser,,); > +connection testuser; > + > +--replace_column 1 # 3 # 6 # 7 # > +SHOW PROCESSLIST; > +--error ER_PROCACCESS_DENIED_ERROR > +call testdb.p1(); > + > +connection default; > + > +GRANT PROCESS ON *.* to PUBLIC; > +GRANT EXECUTE ON testdb.* to PUBLIC; > + > +disconnect testuser; > +connect (testuser,localhost,testuser,,); > +connection testuser; > + > +--replace_column 1 # 3 # 6 # 7 # > +SHOW PROCESSLIST; > +call testdb.p1(); > + > +connection default; > + > +--replace_column 1 # 3 # 6 # 7 # > +SHOW PROCESSLIST; > + > +connection default; > + > +use test; > +disconnect testuser; > +REVOKE PROCESS ON *.* from PUBLIC; > +REVOKE EXECUTE ON testdb.* from PUBLIC; > +drop user testuser; > +drop database testdb; > + > +--echo # > +--echo # test DB privilege to allow USE statement > +--echo # > + > +create user testuser; > +create database testdb; > + > +connect (testuser,localhost,testuser,,); > +connection testuser; > + > +--error ER_DBACCESS_DENIED_ERROR > +use testdb; > + > +connection default; > + > +GRANT LOCK TABLES ON testdb.* to PUBLIC; > + > +disconnect testuser; > +connect (testuser,localhost,testuser,,); > +connection testuser; > + > +use testdb; > + > +connection default; > + > +use test; > +disconnect testuser; > +REVOKE LOCK TABLES ON testdb.* from PUBLIC; > +drop user testuser; > +drop database testdb; > + > + > +--echo # > +--echo # test DB privilege to allow USE statement (as above) > +--echo # test current db privileges > +--echo # > + > +create user testuser; > +create database testdb; > +use testdb; > +create table t1 (a int); > +insert into t1 values (1); > +GRANT LOCK TABLES ON testdb.* to PUBLIC; > + > +connect (testuser,localhost,testuser,,); > +connection testuser; > + > +use testdb; > +--error ER_TABLEACCESS_DENIED_ERROR > +update t1 set a=a+1; > + > +connection default; > + > +GRANT UPDATE,SELECT ON testdb.* to PUBLIC; > + > +disconnect testuser; > +connect (testuser,localhost,testuser,,); > +connection testuser; > + > +use testdb; > +update t1 set a=a+1; > + > +connection default; > +select * from testdb.t1; > + > +use test; > +disconnect testuser; > +REVOKE LOCK TABLES ON testdb.* from PUBLIC; > +REVOKE UPDATE,SELECT ON testdb.* from PUBLIC; > +drop user testuser; > +drop database testdb; > + > + > +--echo # > +--echo # test DB privilege to allow USE statement (as above) > +--echo # test table/column privileges in current DB > +--echo # > + > +create user testuser; > +create database testdb; > +use testdb; > +create table t1 (a int); > +insert into t1 values (1); > +create table t2 (a int, b int); > +insert into t2 values (1,2); > +GRANT LOCK TABLES ON testdb.* to PUBLIC; > + > +connect (testuser,localhost,testuser,,); > +connection testuser; > + > +use testdb; > +--error ER_TABLEACCESS_DENIED_ERROR > +delete from t1; > +--error ER_TABLEACCESS_DENIED_ERROR > +select b from t2; > +--error ER_TABLEACCESS_DENIED_ERROR > +select a from t2; > + > +connection default; > + > +GRANT DELETE ON testdb.t1 to PUBLIC; > +GRANT SELECT (a) ON testdb.t2 to PUBLIC; > + > +disconnect testuser; > +connect (testuser,localhost,testuser,,); > +connection testuser; > + > +use testdb; > +delete from t1; > +select a from t2; > +--error ER_COLUMNACCESS_DENIED_ERROR > +select b from t2; > + > +connection default; > +select * from testdb.t1; > + > +use test; > +disconnect testuser; > +REVOKE LOCK TABLES ON testdb.* from PUBLIC; > +REVOKE DELETE ON testdb.t1 from PUBLIC; > +REVOKE SELECT (a) ON testdb.t2 from PUBLIC; > +drop user testuser; > +drop database testdb; > + > +--echo # > +--echo # test function privilege > +--echo # > + > +create user testuser; > +create database testdb; > +use testdb; > +create function f1() returns int return 2; > + > +connect (testuser,localhost,testuser,,); > +connection testuser; > + > +--error ER_PROCACCESS_DENIED_ERROR > +alter function testdb.f1 comment "A stupid function"; > +--error ER_PROCACCESS_DENIED_ERROR > +select testdb.f1(); > + > +connection default; > + > +GRANT ALTER ROUTINE ON testdb.* to PUBLIC; > + > +disconnect testuser; > +connect (testuser,localhost,testuser,,); > +connection testuser; > + > +alter function testdb.f1 comment "A stupid function"; > +--error ER_PROCACCESS_DENIED_ERROR > +select testdb.f1(); > + > +connection default; > + > +use test; > +disconnect testuser; > +REVOKE ALTER ROUTINE ON testdb.* from PUBLIC; > +drop function testdb.f1; > +drop user testuser; > +drop database testdb; > + > +--echo # > +--echo # bug with automatically added PUBLIC role > +--echo # > + > +--echo # automaticly added PUBLIC > +delete from mysql.global_priv where user="PUBLIC"; > +flush privileges; > +GRANT SELECT on test.* to PUBLIC; > + > +REVOKE SELECT on test.* from PUBLIC; > + > +create user testuser; > +create database testdb1; > +use testdb1; > +create table t1 (a int, b int); > +insert into t1 values (1,2); > + > +connect (testuser,localhost,testuser,,); > +connection testuser; > +--error ER_TABLEACCESS_DENIED_ERROR > +select * from testdb1.t1; > + > +connection default; > + > +disconnect testuser; > +drop user testuser; > +drop database testdb1; > diff --git a/mysql-test/suite/roles/none_public.test b/mysql-test/suite/roles/none_public.test > index a0ec2315cfc..d0ba62d226b 100644 > --- a/mysql-test/suite/roles/none_public.test > +++ b/mysql-test/suite/roles/none_public.test > @@ -19,8 +19,9 @@ grant select on *.* to none; > grant public to role1; > --error ER_INVALID_ROLE > grant role1 to public; ^^^ this shouldn't fail > ---error ER_INVALID_ROLE > -grant select on *.* to public; > +# PUBLIC is legal role > +#--error ER_INVALID_ROLE > +#grant select on *.* to public; you can just remove it, commenting out isn't better > --error ER_INVALID_ROLE > grant role1 to current_role; > @@ -40,16 +41,18 @@ revoke select on *.* from public; > > --error ER_INVALID_ROLE > show grants for none; > ---error ER_INVALID_ROLE > -show grants for public; > +# PUBLIC is legal role > +#--error ER_INVALID_ROLE > +#show grants for public; > > --error ER_INVALID_ROLE > create definer=none view test.v1 as select 1; > ---error ER_INVALID_ROLE > -create definer=public view test.v1 as select 1; > +# PUBLIC is legal role > +#--error ER_INVALID_ROLE > +#create definer=public view test.v1 as select 1; eh, no, PUBLIC isn't a valid definer, as far as I understand. > > drop role role1; > > -insert mysql.global_priv values ('', 'none', '{"is_role":true}'), ('', 'public', '{"is_role":true}'); > +insert mysql.global_priv values ('', 'none', '{"is_role":true}'); > flush privileges; > -delete from mysql.global_priv where host=''; > +delete from mysql.global_priv where host='' and user='none'; > diff --git a/mysql-test/main/sp_notembedded.result b/mysql-test/main/sp_notembedded.result > index e03361598a6..239f8acb7f3 100644 > --- a/mysql-test/main/sp_notembedded.result > +++ b/mysql-test/main/sp_notembedded.result > @@ -345,7 +345,6 @@ show grants; > Grants for foo1@localhost > GRANT USAGE ON *.* TO `foo1`@`localhost` IDENTIFIED BY PASSWORD '-F3A2A51A9B0F2BE2468926B4132313728C250DBF' > GRANT CREATE ROUTINE ON `test`.* TO `foo1`@`localhost` > -GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`spfoo` TO `foo1`@`localhost` Why did this line disappear? > connection default; > disconnect foo; > drop procedure spfoo; Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org [View Less]

1 0

Re: [Maria-developers] bcfc424d859: MDEV-5215 prerequisite: remove test and test_* database hacks in the test suite
by Sergei Golubchik 25 Aug '22

25 Aug '22

Hi, Oleksandr, On Aug 25, Oleksandr Byelkin wrote: > revision-id: bcfc424d859 (mariadb-10.6.1-483-gbcfc424d859) > parent(s): 1c3182a99e5 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2022-08-16 11:49:41 +0200 > message: > > MDEV-5215 prerequisite: remove test and test_* database hacks in the test suite let's say, it's ok. Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] 1c3182a99e5: MDEV-5215 prerequisite of prerequisite: if DB is not mentioned in connect ignore errors of switching to it
by Sergei Golubchik 25 Aug '22

25 Aug '22

Hi, Oleksandr, On Aug 25, Oleksandr Byelkin wrote: > revision-id: 1c3182a99e5 (mariadb-10.6.1-482-g1c3182a99e5) > parent(s): 7253cdf8928 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2022-08-16 11:49:41 +0200 > message: > > MDEV-5215 prerequisite of prerequisite: if DB is not mentioned in connect ignore errors of switching to it > > diff --git a/mysql-test/main/init_connect.test b/mysql-test/main/init_connect.test > index e96d02fe0d1.… [View More]

1 0

Re: [Maria-developers] b3844107287: MDEV-16546 System versioning setting to allow history modification
by Sergei Golubchik 24 Aug '22

24 Aug '22

Hi, Aleksey! On Jun 08, Aleksey Midenkov wrote: > revision-id: b3844107287 (mariadb-10.5.2-424-gb3844107287) > parent(s): 27d66d644cf > author: Aleksey Midenkov <midenok(a)gmail.com> > committer: Aleksey Midenkov <midenok(a)gmail.com> > timestamp: 2021-03-01 17:43:34 +0300 > message: > > MDEV-16546 System versioning setting to allow history modification > > 1. force_fields_visible session variable makes system-invisible and > user-invisible fields … [View More]visible on next table open. FLUSH TABLES > required for already opened tables. * system_versioning_insert_history * allows pseudocolumns ROW_START and ROW_END be specified in INSERT * doesn't affect invisible columns * FLUSH TABLES requirememnt is rather confusing, but let's have it, at least until I could suggest something better > 2. If secure_timestamp allows to modify timestamp variable then > following DML commands: INSERT, INSERT..SELECT and LOAD DATA can > specify row_start and row_end system field values. when system_versioning_insert_history=1 > 3. Cleaned up select_insert::send_data() from setting vers_write as > this parameter is now set on TABLE initialization. I don't see where it's set. This commit removes two table->vers_write= table->versioned(); and adds one from_field->table->vers_write= false; in Item_field::fix_fields. But where is vers_write set to true? Was it done in some other commit? > diff --git a/mysql-test/suite/versioning/r/insert.result b/mysql-test/suite/versioning/r/insert.result > index 2645d0184e8..00d1bb705c4 100644 > --- a/mysql-test/suite/versioning/r/insert.result > +++ b/mysql-test/suite/versioning/r/insert.result > @@ -112,3 +112,95 @@ x y > 9 9001 > drop table t1; > drop table t2; > +# > +# MDEV-16546 System versioning setting to allow history modification > +# > +# restart: --secure-timestamp=NO > +set @@session.time_zone='+00:00'; > +create table t1(x int) with system versioning; > +insert into t1(x) values (1); > +insert into t1(x, row_start, row_end) values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); > +ERROR 42S22: Unknown column 'row_start' in 'field list' > +set session force_fields_visible= 1; > +flush tables; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `x` int(11) DEFAULT NULL, > + `row_start` timestamp(6) GENERATED ALWAYS AS ROW START, > + `row_end` timestamp(6) GENERATED ALWAYS AS ROW END, shouldn't be visible here, only in insert and load. > + PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`) > +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING > +insert into t1(x, row_start, row_end) values (3, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); please, add tests for insert into t1 values (4) this should work, row_start/row_end must be mentioned explicitly. Also insert into t1 set x=5, row_start=..., row_end=... this should work too. And when only one row_start or row_end is mentioned as well. But update t1 set row_start=... insert into t1 (x, row_start, row_end) values (...) on duplicate key update row_start=... this should fail. While replace load data ... replace should work, but as DELETE+INSERT (I mean that DELETE in versioned tables doesn't actually delete history). If that's too difficult, then an error too. > +update t1 set x= x + 1; > +select x, check_row_ts(row_start, row_end) from t1 for system_time all order by x; ... > diff --git a/mysql-test/suite/versioning/t/insert.opt b/mysql-test/suite/versioning/t/insert.opt > new file mode 100644 > index 00000000000..a74d68957ef > --- /dev/null > +++ b/mysql-test/suite/versioning/t/insert.opt > @@ -0,0 +1 @@ > +--secure-timestamp=yes why is this? > -- source suite/versioning/common_finish.inc > diff --git a/mysql-test/suite/versioning/t/insert2.opt b/mysql-test/suite/versioning/t/insert2.opt > new file mode 100644 > index 00000000000..a74d68957ef > --- /dev/null > +++ b/mysql-test/suite/versioning/t/insert2.opt > @@ -0,0 +1 @@ > +--secure-timestamp=yes and this? > diff --git a/sql/sql_class.cc b/sql/sql_class.cc > index d23b190b2c5..93f8a8434e4 100644 > --- a/sql/sql_class.cc > +++ b/sql/sql_class.cc > @@ -7604,6 +7604,20 @@ void THD::set_last_commit_gtid(rpl_gtid &gtid) > #endif > } > > +bool THD::vers_modify_sys_field() const don't call it vers_modify_sys_field, your previous name vers_modify_history was better. There may be other sys fields that will never be directly writable (like ROWID, for example). > +{ > + if (lex->sql_command != SQLCOM_INSERT && > + lex->sql_command != SQLCOM_INSERT_SELECT && > + lex->sql_command != SQLCOM_LOAD) > + return false; I don't think this is enough to reject insert ... on duplicate key update row_start=xxx > + if (opt_secure_timestamp >= SECTIME_REPL || this is fixed in a later commit, so ok > + (opt_secure_timestamp == SECTIME_SUPER && > + !(security_ctx->master_access & SUPER_ACL))) > + return false; > + return true; > +} > + > + > void > wait_for_commit::reinit() > { > diff --git a/sql/sql_class.h b/sql/sql_class.h > index 2913a4a8350..520ea1d63bf 100644 > --- a/sql/sql_class.h > +++ b/sql/sql_class.h > @@ -820,6 +820,7 @@ typedef struct system_variables > > vers_asof_timestamp_t vers_asof_timestamp; > ulong vers_alter_history; > + my_bool force_fields_visible; system_versioning_insert_history > } SV; > > /** > @@ -5400,6 +5401,7 @@ class THD: public THD_count, /* this must be first */ > Item *sp_prepare_func_item(Item **it_addr, uint cols= 1); > bool sp_eval_expr(Field *result_field, Item **expr_item_ptr); > > + bool vers_modify_sys_field() const; vers_modify_history > }; > > > diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc > index f0b84ab126f..84c2ae4ab36 100644 > --- a/sql/sql_insert.cc > +++ b/sql/sql_insert.cc > @@ -2014,7 +2014,7 @@ int write_record(THD *thd, TABLE *table, COPY_INFO *info, select_result *sink) > !table->file->referenced_by_foreign_key() && > (!table->triggers || !table->triggers->has_delete_triggers())) > { > - if (table->versioned(VERS_TRX_ID)) > + if (table->versioned(VERS_TRX_ID) && table->vers_write) does it even make sense? I mean direct insertion of transaction ids. > { > bitmap_set_bit(table->write_set, table->vers_start_field()->field_index); > table->file->column_bitmaps_signal(); > @@ -2029,7 +2029,7 @@ int write_record(THD *thd, TABLE *table, COPY_INFO *info, select_result *sink) > info->deleted++; > if (!table->file->has_transactions()) > thd->transaction->stmt.modified_non_trans_table= TRUE; > - if (table->versioned(VERS_TIMESTAMP)) > + if (table->versioned(VERS_TIMESTAMP) && table->vers_write) This (and the previous hunk) are in the DUP_REPLACE branch. There should be tests for these changes. > { > store_record(table, record[2]); > error= vers_insert_history_row(table); > diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc > index f817f9086a9..4429308a097 100644 > --- a/sql/sys_vars.cc > +++ b/sql/sys_vars.cc > @@ -6737,3 +6737,7 @@ static Sys_var_ulonglong Sys_max_rowid_filter_size( > SESSION_VAR(max_rowid_filter_size), CMD_LINE(REQUIRED_ARG), > VALID_RANGE(1024, (ulonglong)~(intptr)0), DEFAULT(128*1024), > BLOCK_SIZE(1)); > + > +static Sys_var_mybool Sys_force_fields_visible( > + "force_fields_visible", "Make invisible fields visible on next table open", "system_versioning_insert_history" "Allows direct inserts into ROW_START and ROW_END columns" > + SESSION_VAR(force_fields_visible), CMD_LINE(OPT_ARG), DEFAULT(FALSE)); > diff --git a/sql/table.cc b/sql/table.cc > index f4bdbdeac5a..520f3738c9e 100644 > --- a/sql/table.cc > +++ b/sql/table.cc > @@ -4024,6 +4024,9 @@ enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share, > { > if (!((*field_ptr)= share->field[i]->clone(&outparam->mem_root, outparam))) > goto err; > + if (thd->variables.force_fields_visible && > + (*field_ptr)->invisible <= INVISIBLE_SYSTEM) > + (*field_ptr)->invisible= VISIBLE; Not really, system_versioning_insert_history should not affect visibility of anything besides ROW_START and ROW_END pseudocolumns in the INSERT and LOAD DATA statements. Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org [View Less]

2 5

Re: [Maria-developers] Review request for MDEV-12325 Unexpected data type and truncation when using CTE
by Alexander Barkov 06 Aug '22

06 Aug '22

Hello Monty. The patch looks OK for me. I have just one question below: On 8/5/22 7:15 PM, Michael Widenius wrote: > Hi! > > Just to make things easy, here is the patch: > commit a556c4d972eaa09bb8ad586b9bda6cfb2a7b574a > Author: Monty <monty(a)mariadb.org> > Date: Fri Aug 5 17:57:27 2022 +0300 > > MDEV-12325 Unexpected data type and truncation when using CTE > > When creating a recursive CTE, the column types are taken from the > … [View More]non recursive part of the CTE (this is according to the SQL standard). > > This patch adds code to abort the CTE if the calculated values in the > recursive part does not fit in the fields in the created temporary table. > > The new code only affects recursive CTE, so it should not cause any notable > problems for old applications. > <cut> > diff --git a/sql/sql_union.cc b/sql/sql_union.cc > index 48d8c16db68..d271a919a4e 100644 > --- a/sql/sql_union.cc > +++ b/sql/sql_union.cc > @@ -111,6 +111,8 @@ int select_unit::send_data(List<Item> &values) > { > int rc; > int not_reported_error= 0; > + enum_check_fields save_count_cuted_fields= thd->count_cuted_fields; > + > if (unit->offset_limit_cnt) > { // using limit offset,count > unit->offset_limit_cnt--; > @@ -118,8 +120,10 @@ int select_unit::send_data(List<Item> &values) > } > if (thd->killed == ABORT_QUERY) > return 0; > + > if (table->no_rows_with_nulls) > table->null_catch_flags= CHECK_ROW_FOR_NULLS_TO_REJECT; > + > if (intersect_mark) > { > fill_record(thd, table, table->field + 1, values, TRUE, FALSE); > @@ -127,6 +131,8 @@ int select_unit::send_data(List<Item> &values) > } > else > fill_record(thd, table, table->field, values, TRUE, FALSE); > + thd->count_cuted_fields= save_count_cuted_fields; > + > if (unlikely(thd->is_error())) > { > rc= 1; > @@ -297,7 +303,13 @@ bool select_unit::send_eof() > Why do you need above changes in select_unit::send_data()? Shouldn't it be enough just to have below changes in select_union_recursive::send_data()? > int select_union_recursive::send_data(List<Item> &values) > { > + Abort_on_warning_instant_set handle_abort(thd, 1); > + enum_check_fields save_count_cuted_fields= thd->count_cuted_fields; > + > + /* For recursive CTE's give warnings for wrong field info */ > + thd->count_cuted_fields= CHECK_FIELD_WARN; > int rc= select_unit::send_data(values); > + thd->count_cuted_fields= save_count_cuted_fields; > > if (rc == 0 && > write_err != HA_ERR_FOUND_DUPP_KEY && <cut> [View Less]

1 1

Re: [Maria-developers] a507f126b03: MDEV-29159 Patch for MDEV-28918 introduces more inconsistency than it solves, breaks usability
by Sergei Golubchik 05 Aug '22

05 Aug '22

Hi, Alexander, a thought about the error message text and a couple of questions about changes in results, please, see below: On Aug 05, Alexander Barkov wrote: > revision-id: a507f126b03 (mariadb-10.7.4-39-ga507f126b03) > parent(s): 97d16c7544c > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2022-08-05 09:42:46 +0400 > message: > > MDEV-29159 Patch for MDEV-28918 introduces more inconsistency than it solves, breaks usability > > 1. Store … [View More]assignment failures on incompatible data types now cause errors if: > - STRICT_ALL_TABLES or STRICT_TRANS_TABLES sql_mode is used, and > - IGNORE is not used > > Otherwise, only a warning is raised and the statement continues. > > 2. Changing the error/warning test as follows: > > -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' > +ERROR HY000: Unknown CAST(int AS inet6) in assignment of 'target' Better not to write 'CAST(int AS inet6)' if there's no explicit CAST() function, I think it might be confusing. I'd say Cannot cast int as inet6 in assignment of 'target' > so in case of a big table it's easier to see which column has the problem. > The new error text is aslo applied to SP variables. s/aslo/also/ > diff --git a/mysql-test/main/sp-vars.result b/mysql-test/main/sp-vars.result > index 127a0993e2b..feef67ccb1d 100644 > --- a/mysql-test/main/sp-vars.result > +++ b/mysql-test/main/sp-vars.result > @@ -1026,11 +1026,11 @@ BEGIN > SELECT arg; > END| > CALL p1((1, 2)); > -ERROR HY000: Illegal parameter data types tinyint and row for operation 'SET' > +ERROR 21000: Operand should contain 1 column(s) Why not "Unknown CAST" like elsewhere? > diff --git a/mysql-test/main/type_geometry_mix_int.result b/mysql-test/main/type_geometry_mix_int.result > index e5d00c63727..0d0a21a8d29 100644 > --- a/mysql-test/main/type_geometry_mix_int.result > +++ b/mysql-test/main/type_geometry_mix_int.result > @@ -6,6 +6,7 @@ > # > CREATE TABLE t1 (target GEOMETRY DEFAULT POINT(1,1), source INT DEFAULT 0); > # Start of type_store_assignment_incompatible.inc > +SET @sql_mode_save= (SELECT @@sql_mode); strange, why not `SET @sql_mode_save= @@sql_mode` ? > SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS > WHERE COLUMN_NAME='source' > AND TABLE_NAME='t1' > @@ -47,6 +52,7 @@ INSERT INTO t3 VALUES > (2, > (SELECT source FROM t2 ORDER BY id LIMIT 1), > (SELECT source FROM t2 ORDER BY id LIMIT 1)); > +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field What's that? It wasn't an error, and now it is? > SELECT * FROM v3; > id target source > 1 POINT(1 1) 0 Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org [View Less]

2 2

Re: [Maria-developers] f3fc58149dd: MDEV-29159 Patch for MDEV-28918 introduces more inconsistency than it solves, breaks usability
by Sergei Golubchik 05 Aug '22

05 Aug '22

Hi, Alexander, A couple of comments: On Aug 03, Alexander Barkov wrote: > revision-id: f3fc58149dd (mariadb-10.7.4-39-gf3fc58149dd) > parent(s): 97d16c7544c > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2022-08-03 11:28:31 +0400 > message: > > MDEV-29159 Patch for MDEV-28918 introduces more inconsistency than it solves, breaks usability > > Store assignment failures on incompatible data types now cause errors if: > - STRICT_ALL_TABLES … [View More]

2 1

[Maria-developers] Review: MDEV-29075 Changing explicit_defaults_for_timestamp within stored procedure works inconsistently
by Alexander Barkov 01 Aug '22

01 Aug '22

Hello Sergei, I have a couple of suggestions: > commit 7b8304045272111a6f4d44196d6b37cbfef06f37 > Author: Sergei Golubchik <serg(a)mariadb.org> > Date: Wed Jul 20 17:31:48 2022 +0200 > > MDEV-29075 Changing explicit_defaults_for_timestamp within stored procedure works inconsistently > > move the OPTION_EXPLICIT_DEF_TIMESTAMP check from the parsing step > to the execution > <cut> > > diff --git a/sql/field.cc b/sql/field.cc &… [View More]gt; index 2e0c70d3d13..23ebb07b7f7 100644 > --- a/sql/field.cc > +++ b/sql/field.cc > @@ -10824,6 +10824,7 @@ Column_definition::Column_definition(THD *thd, Field *old_field, > comment= old_field->comment; > vcol_info= old_field->vcol_info; > option_list= old_field->option_list; > + explicitly_nullable= !(old_field->flags & NOT_NULL_FLAG); This introduces asymmetry in how NULL and NOT NULL attributes are handled. I suggest considering these ways: 1. Change "bool explicitly_nullable" to "bool explicit_nullability", which will be: - false if nothing was specified - true if NULL or NOT NULL was specified. 2. Or don't store NOT_NULL_FLAG in Column_definition::flags at all. Add a new Column_definition enum member with three values for: - not specified - explicit NULL - explicit NOT NULL As discussed on slack, the latter will need some changes. But I think not that much, as only Column_definition needs changes, while Field does not seem to need at this point. > compression_method_ptr= 0; > versioning= VERSIONING_NOT_SET; > invisible= old_field->invisible; > diff --git a/sql/field.h b/sql/field.h > index 5d5be5204cd..87a30bd9f95 100644 > --- a/sql/field.h > +++ b/sql/field.h > @@ -5281,7 +5281,7 @@ class Column_definition: public Sql_alloc, > uint flags, pack_length; > List<String> interval_list; > engine_option_value *option_list; > - > + bool explicitly_nullable; > > /* > This is additinal data provided for any computed(virtual) field. > @@ -5303,7 +5303,7 @@ class Column_definition: public Sql_alloc, > comment(null_clex_str), > on_update(NULL), invisible(VISIBLE), char_length(0), > flags(0), pack_length(0), > - option_list(NULL), > + option_list(NULL), explicitly_nullable(false), > vcol_info(0), default_value(0), check_constraint(0), > versioning(VERSIONING_NOT_SET), period(NULL) > { > diff --git a/sql/sql_table.cc b/sql/sql_table.cc > index 3eeabe20c71..8c31f988a05 100644 > --- a/sql/sql_table.cc > +++ b/sql/sql_table.cc > @@ -2316,6 +2316,8 @@ void promote_first_timestamp_column(List<Create_field> *column_definitions) > if (column_definition.is_timestamp_type() || // TIMESTAMP > column_definition.unireg_check == Field::TIMESTAMP_OLD_FIELD) // Legacy > { > + if (!column_definition.explicitly_nullable) > + column_definition.flags|= NOT_NULL_FLAG; I think this does not work well with PS. On the very first execution the field becomes NOT NULL forever, if the current OPTION_EXPLICIT_DEF_TIMESTAMP says so. > DBUG_PRINT("info", ("field-ptr:%p", column_definition.field)); > if (first && > (column_definition.flags & NOT_NULL_FLAG) != 0 && // NOT NULL, > diff --git a/sql/sql_type.cc b/sql/sql_type.cc > index 44a51428280..dc0c0b1e0cf 100644 > --- a/sql/sql_type.cc > +++ b/sql/sql_type.cc > @@ -4258,18 +4258,6 @@ void Type_handler_temporal_with_date::Item_update_null_value(Item *item) const > (void) item->get_date(thd, &ltime, Datetime::Options(thd)); > } > > -bool > -Type_handler_timestamp_common:: > -Column_definition_set_attributes(THD *thd, > - Column_definition *def, > - const Lex_field_type_st &attr, > - column_definition_type_t type) const > -{ > - Type_handler::Column_definition_set_attributes(thd, def, attr, type); > - if (!(thd->variables.option_bits & OPTION_EXPLICIT_DEF_TIMESTAMP)) > - def->flags|= NOT_NULL_FLAG; > - return false; > - > > void Type_handler_string_result::Item_update_null_value(Item *item) const > { > diff --git a/sql/sql_type.h b/sql/sql_type.h > index 7ff4bc64679..e5f54535ece 100644 > --- a/sql/sql_type.h > +++ b/sql/sql_type.h > @@ -6655,11 +6655,6 @@ class Type_handler_timestamp_common: public Type_handler_temporal_with_date > bool Item_func_min_max_get_date(THD *thd, Item_func_min_max*, > MYSQL_TIME *, date_mode_t fuzzydate) > const override; > - bool Column_definition_set_attributes(THD *thd, > - Column_definition *def, > - const Lex_field_type_st &attr, > - column_definition_type_t type) > - const override; > }; > > > diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy > index 5416cec49b5..f326d8c0cc5 100644 > --- a/sql/sql_yacc.yy > +++ b/sql/sql_yacc.yy > @@ -5831,7 +5831,6 @@ field_def: > | opt_generated_always AS virtual_column_func > { > Lex->last_field->vcol_info= $3; > - Lex->last_field->flags&= ~NOT_NULL_FLAG; // undo automatic NOT NULL for timestamps > } > vcol_opt_specifier vcol_opt_attribute > { > @@ -6313,7 +6312,12 @@ attribute_list: > ; > > attribute: > - NULL_SYM { Lex->last_field->flags&= ~ NOT_NULL_FLAG; $$.init(); } > + NULL_SYM > + { > + Lex->last_field->flags&= ~NOT_NULL_FLAG; > + Lex->last_field->explicitly_nullable= true; > + $$.init(); > + } > | DEFAULT column_default_expr { Lex->last_field->default_value= $2; $$.init(); } > | ON UPDATE_SYM NOW_SYM opt_default_time_precision > { [View Less]

3 3

[Maria-developers] review for (MDEV-28632) bugfix: DEFAULT NULL was allowed for NOT NULL columns
by Alexander Barkov 29 Jul '22

29 Jul '22

Hello Sergei, > commit 5a362d486b30fdaf3c7a360737331767154b4ee8 > Author: Sergei Golubchik <serg(a)mariadb.org> > Date: Mon Jul 18 22:53:27 2022 +0200 > > bugfix: DEFAULT NULL was allowed for NOT NULL columns > > this was detected during parsing (so NOT NULL DEFAULT NULL was an error), > but if a column was made NOT NULL later (e.g. as a part of primary key, > in mysql_prepare_create_table()), DEFAULT NULL was accepted and ignored. > … [View More] > to fix this the NOT NULL DEFAULT NULL was moved into > mysql_prepare_create_table(). I'm not sure that in case of auto_increment we should really disallow DEFAULT NULL under terms of this TIMESTAMP task. Look at this script: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY); INSERT INTO t1 VALUES (NULL); Query OK, 1 row affected (0.012 sec) NULL can be inserted without errors. Yes, it's inserted with a side effect - it's replaced to the next auto increment value. But why this side effect makes NULL an invalid default? Note, I'm not against this change. Stricter behavior is good :) But I'm worried that: - It can break compatibility on one hand. - It goes without it's own MDEV on the other hand. This change deserves it's own MDEV (with motivation and consequences explained) and should be properly documented and mentioned in release notes. <cut> > index 712b8629c09..d8471894db9 100644 > --- a/mysql-test/main/view.result > +++ b/mysql-test/main/view.result > @@ -4082,12 +4082,12 @@ DROP TABLE t1,t2; > # MDEV-6251: SIGSEGV in query optimizer (in set_check_materialized > # with MERGE view) > # > -CREATE TABLE t1 (a1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); > -CREATE TABLE t2 (b1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); > -CREATE TABLE t3 (c1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); > -CREATE TABLE t4 (d1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); > -CREATE TABLE t5 (e1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); > -CREATE TABLE t6 (f1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); > +CREATE TABLE t1 (a1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY); > +CREATE TABLE t2 (b1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY); > +CREATE TABLE t3 (c1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY); > +CREATE TABLE t4 (d1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY); > +CREATE TABLE t5 (e1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY); > +CREATE TABLE t6 (f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY); It seems you removed all cases with the old syntax. So now it's not recorded in MTR what happens on this statement: CREATE TABLE t1 (a INT NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); Please cover this scenario in some relevant *.test file. <cut> > diff --git a/sql/sql_table.cc b/sql/sql_table.cc > index cf0bbfb1130..3eeabe20c71 100644 > --- a/sql/sql_table.cc > +++ b/sql/sql_table.cc <cut> > @@ -3647,6 +3643,15 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info, > { > Field::utype type= (Field::utype) MTYP_TYPENR(sql_field->unireg_check); > > + if (sql_field->default_value && > + sql_field->default_value->expr->type() == Item::NULL_ITEM && > + sql_field->flags & NOT_NULL_FLAG && > + !(sql_field->flags & AUTO_INCREMENT_FLAG)) > + { > + my_error(ER_INVALID_DEFAULT, MYF(0), sql_field->field_name.str); > + DBUG_RETURN(TRUE); > + } ^^^ now obvious errors are caught only during EXECUTE, although it's already clear at the PREPARE stage that the DEFAULT is wrong: MariaDB [test]> PREPARE stmt FROM 'CREATE TABLE T1 (a INT NOT NULL DEFAULT NULL)'; Query OK, 0 rows affected (0.000 sec) Statement prepared MariaDB [test]> EXECUTE stmt; ERROR 1067 (42000): Invalid default value for 'a' I suggest we still catch non-unambiguous cases (when nothing depends on system variables) during PREPARE. Inside some existing or a new Type_handler method. Also, please add PS tests: - for unambiguous scenarios like here - for ambiguous scenarios, e.g. with TIMESTAMP columns changing OPTION_EXPLICIT_DEF_TIMESTAMP between PREPARE and EXECUTE. It seems this code needs to be changed: > /* > Set NO_DEFAULT_VALUE_FLAG if this field doesn't have a default value and > it is NOT NULL, not an AUTO_INCREMENT field, not a TIMESTAMP and not > updated trough a NOW() function. > */ > if (!sql_field->default_value && > !sql_field->has_default_function() && > (sql_field->flags & NOT_NULL_FLAG) && > (!sql_field->is_timestamp_type() || > (thd->variables.option_bits & OPTION_EXPLICIT_DEF_TIMESTAMP))&& > !sql_field->vers_sys_field()) > { > sql_field->flags|= NO_DEFAULT_VALUE_FLAG; > sql_field->pack_flag|= FIELDFLAG_NO_DEFAULT; > } It can only add NO DEFAULT related flags. Shouldn't it also remove NO DEFAULT flags if the current PS EXECUTE time option OPTION_EXPLICIT_DEF_TIMESTAMP is unset? <cut> > diff --git a/storage/spider/mysql-test/spider/bg/t/spider_fixes.test b/storage/spider/mysql-test/spider/bg/t/spider_fixes.test > index b222f494ba1..41f87e61416 100644 > --- a/storage/spider/mysql-test/spider/bg/t/spider_fixes.test > +++ b/storage/spider/mysql-test/spider/bg/t/spider_fixes.test > @@ -294,14 +294,14 @@ if ($USE_CHILD_GROUP2) > --disable_query_log > echo CREATE TABLE ta_l ( > a int(11) NOT NULL DEFAULT '0', > - b char(1) DEFAULT NULL, > - c datetime DEFAULT NULL, > + b char(1), > + c datetime, > PRIMARY KEY (a, b, c) > ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT5_2_1; > eval CREATE TABLE ta_l ( > a int(11) NOT NULL DEFAULT '0', > - b char(1) DEFAULT NULL, > - c datetime DEFAULT NULL, > + b char(1), > + c datetime, > PRIMARY KEY (a, b, c) > ) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT5_2_1; > --enable_query_log Why this change ^^^ ? > diff --git a/storage/spider/mysql-test/spider/t/spider_fixes.test b/storage/spider/mysql-test/spider/t/spider_fixes.test > index 47bc225d614..75f108f1164 100644 > --- a/storage/spider/mysql-test/spider/t/spider_fixes.test > +++ b/storage/spider/mysql-test/spider/t/spider_fixes.test > @@ -294,14 +294,14 @@ if ($USE_CHILD_GROUP2) > --disable_query_log > echo CREATE TABLE ta_l ( > a int(11) NOT NULL DEFAULT '0', > - b char(1) DEFAULT NULL, > - c datetime DEFAULT NULL, > + b char(1), > + c datetime, > PRIMARY KEY (a, b, c) > ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT5_2_1; > eval CREATE TABLE ta_l ( > a int(11) NOT NULL DEFAULT '0', > - b char(1) DEFAULT NULL, > - c datetime DEFAULT NULL, > + b char(1), > + c datetime, > PRIMARY KEY (a, b, c) > ) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT5_2_1; > --enable_query_log Same question. [View Less]

2 2

Re: [Maria-developers] Move on with Gtid strict mode
by Kristian Nielsen 13 Jul '22

13 Jul '22

Andrei Elkin <andrei.elkin(a)mariadb.com> writes: > Kristian, salve! Hi Andrei, thanks for interesting comments and discussion! >> 3. To solve (2), we recover the old master with >> --tc-heuristic-recover=rollback. This makes it rollback (=discard) any >> transaction that was not fully committed to disk (does it also roll back the >> binary log for such transactions? I don't recall this detail). >> >> 4. Because of (3), there might be a … [View More]transaction that originated on the old >> master (has it's server_id), but which is now missing from that server (now >> a slave) as it was deliberately discarded. >> >> 5. To solve (4), we will use circular replication > > Well, the circularity is not necessary for (4). The ex-master just needs > to be configured as slave. Right, I see now. > Also let me call in the following this stage of receiving/accepting own > events as 'slave recovery'. But thinking more, there's another point that I don't understand, around the use of --tc-heuristic-recover=rollback. From memory, and also from reading docs on https://mariadb.com/kb/en/heuristic-recovery-with-the-transaction-coordinat…, this will forcibly roll back any prepared-but-not-committed InnoDB transaction, but _not_ roll back any corresponding binlog entry. Doesn't this leave the binlog inconsistent with the storage engine? Of the last few transactions in the binlog, some may be in InnoDB, some may not. And if so, doesn't this make the binlog useless for deciding which of our own transactions to ignore? When the old-master-now-slave gets one of its own events that's also in the binlog, we don't know if it is in the engine. If we apply it, we may end up with it double. If we do not apply it, we may be missing it. Is there some mechanims used in this feature/MDEV that handles this correctly that I'm missing? >> So isn't there a better way to achieve the underlying use case? For example, >> why not just use the new MASTER_DEMOTE_TO_SLAVE option to set the correct >> slave position from which to replicate from in the old master (now >> slave)? > > That's how it should normally do, yet it's an optimization (to save on > traffic) part of the solution. Interesting. Can you elaborate on this optimization - how does it save on traffic? > In the worst case `gtid_slave_pos` can be even empty for the > ex-master's domain, so the ex-master slave might keep receiving a bulk > of own events, only to ignore the most of them. > And that's the patch's base code logics as well. I guess there's something here that I'm not understanding. If MASTER_DEMOTE_TO_SLAVE is used to copy the gtid_binlog_pos into the gtid_slave_pos, how can the gtid_slave_pos be empty for the ex-master's domain? > ... , `gtid_ignore_duplicate = OFF` would have to go in my perception. > And if it goes, we'd remain with the only "choice" of > `gtid_ignore_duplicate = ON` :-) so the option would be gone. Hm. So are you suggesting that the current behaviour when --gtid-ignore-duplicates=ON becomes the default behaviour - in fact the only behaviour? As I remember it, the --gtid-ignore-duplicates=ON option is a very dangerous option unless the servers are managed very strictly and correctly. In fact I was expecting to find a dire warning written by myself about this, but didn't find anything in a quick web search ;-) I think the main problem is if an incorrect transaction done on a slave somewhere introduces an out-of-order GTID sequence, Then the replication will silently ignore potentially a large number of transactions that have their sequence number smaller than the incorrect out-of-order transaction. Out-of-order will normally be an error. But mistakes happen, and it is bad if this causes replication to become silently corrupt, as opposed to flagging the error (assuming --gtid-strict-mode=ON). What do you think? Again, it's cool that things are being worked on in MariaDB GTID, and I hope my comments don't come out as purely negative. While MariaDB/MySQL replication is in some ways overly complex, this is also what gives it the enormous flexibility for advanced users to do crazy^H^H^H^H^Hfantastic stuff! So there's some leeway for allowing complexity. But there are also some very delicate pitfalls that the original design tried hard to avoid, with decisions that may not always be obvious. So anything I can do there to help with history and design principles, I'm happy to assist with... - Kristian. [View Less]

1 0