summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/system_mysql_db_refs.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/system_mysql_db_refs.test')
-rw-r--r--mysql-test/main/system_mysql_db_refs.test92
1 files changed, 92 insertions, 0 deletions
diff --git a/mysql-test/main/system_mysql_db_refs.test b/mysql-test/main/system_mysql_db_refs.test
new file mode 100644
index 00000000..084d5bbf
--- /dev/null
+++ b/mysql-test/main/system_mysql_db_refs.test
@@ -0,0 +1,92 @@
+#
+# This test must examine integrity of current system database
+#
+
+set @name="This is a very long string, that mustn't find room in a system field like Table_name. Thus it should be cut by the actual size of the field. So we can use this string to find out the actual length of the field and to use it in any compare queries";
+
+#
+# If this part is wrong, most likely you've done wrong modification of system database "mysql"
+#
+
+create table test_db select * from mysql.db;
+delete from test_db;
+--disable_warnings
+insert ignore into test_db (Host,Db,User) values (@name,@name,@name);
+--enable_warnings
+
+create table test_user select * from mysql.user;
+delete from test_user;
+--disable_warnings
+insert ignore into test_user (Host,User) values (@name,@name);
+--enable_warnings
+
+create table test_func select * from mysql.func;
+delete from test_func;
+--disable_warnings
+insert ignore into test_func (name) values (@name);
+--enable_warnings
+
+create table test_tables_priv select * from mysql.tables_priv;
+delete from test_tables_priv;
+--disable_warnings
+insert ignore into test_tables_priv (Host,Db,User,Table_name) values (@name,@name,@name,@name);
+--enable_warnings
+
+create table test_columns_priv select * from mysql.columns_priv;
+delete from test_columns_priv;
+--disable_warnings
+insert ignore into test_columns_priv (Host,Db,User,Table_name,Column_name) values (@name,@name,@name,@name,@name);
+--enable_warnings
+
+# 'Host' field must be the same for all the tables:
+
+select
+ if(isnull(test_db.Host),'WRONG!!!','ok') as test_db_Host,
+ if(isnull(test_user.Host),'WRONG!!!','ok') as test_user_Host,
+ if(isnull(test_tables_priv.Host),'WRONG!!!','ok') as test_tables_priv_Host,
+ if(isnull(test_columns_priv.Host),'WRONG!!!','ok') as test_columns_priv_Host
+
+from test_db
+left join test_user on test_db.Host=test_user.Host
+left join test_tables_priv on test_db.Host=test_tables_priv.Host
+left join test_columns_priv on test_db.Host=test_columns_priv.Host;
+
+# 'Db' field must be the same for all the tables:
+
+select
+ if(isnull(test_db.Db),'WRONG!!!','ok') as test_db_Db,
+ if(isnull(test_tables_priv.Db),'WRONG!!!','ok') as test_tables_priv_Db,
+ if(isnull(test_columns_priv.Db),'WRONG!!!','ok') as est_columns_priv_Db
+
+from test_db
+left join test_tables_priv on test_db.Db=test_tables_priv.Db
+left join test_columns_priv on test_db.Db=test_columns_priv.Db;
+
+# 'User' field must be the same for all the tables:
+
+select
+ if(isnull(test_db.User),'WRONG!!!','ok') as test_db_User,
+ if(isnull(test_user.User),'WRONG!!!','ok') as test_user_User,
+ if(isnull(test_tables_priv.User),'WRONG!!!','ok') as test_tables_priv_User,
+ if(isnull(test_columns_priv.User),'WRONG!!!','ok') as test_columns_priv_User
+
+from test_db
+left join test_user on test_db.User=test_user.User
+left join test_tables_priv on test_db.User=test_tables_priv.User
+left join test_columns_priv on test_db.User=test_columns_priv.User;
+
+# 'Table_name' field must be the same for all the tables:
+
+select
+ if(isnull(test_tables_priv.User),'WRONG!!!','ok') as test_tables_priv_User,
+ if(isnull(test_columns_priv.User),'WRONG!!!','ok') as test_columns_priv_User
+from test_tables_priv
+left join test_columns_priv on test_tables_priv.Table_name=test_columns_priv.Table_name;
+
+drop table test_columns_priv;
+drop table test_tables_priv;
+drop table test_func;
+drop table test_user;
+drop table test_db;
+
+# End of 4.1 tests