| Server IP : 172.67.216.182 / Your IP : 172.70.208.146 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/src/mysql-test/suite/innodb/r/ |
Upload File : |
SET DEFAULT_STORAGE_ENGINE=InnoDB; # # If CREATE TABLESPACE fails after the file is created, it should not remain. # SET SESSION DEBUG='+d,innodb_fail_to_update_tablespace_dict'; CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd' ENGINE=InnoDB; ERROR HY000: Failed to create TABLESPACE s_def SET SESSION DEBUG='-d,innodb_fail_to_update_tablespace_dict'; === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path # MYSQLD_DATADIR/ DROP TABLESPACE s_def; # # Assigning general tablespace to myisam,memory is igonred # USE test; CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB; CREATE TABLE t1 (a int) ENGINE=innodb TABLESPACE=s1; CREATE TABLE t2 (a int) ENGINE=memory TABLESPACE=s1; CREATE TABLE t3 (a int) ENGINE=myisam TABLESPACE=s1; INSERT INTO t1 VALUES ( 11); INSERT INTO t2 VALUES ( 21); INSERT INTO t3 VALUES ( 31); === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t1 s1 161 4 Dynamic 0 General ALTER TABLE t2 TABLESPACE=s1; ALTER TABLE t3 TABLESPACE=s1; === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t1 s1 161 4 Dynamic 0 General # MYSQLD_DATADIR/ s1.ibd show tables; Tables_in_test t1 t2 t3 #restart the server # restart USE test; show tables; Tables_in_test t1 t2 t3 select * from t1; a 11 select * from t2; a select * from t3; a 31 DROP TABLE t2,t3; # # A general tablespace and its contents can be recovered. # CREATE TABLE t2 (a int) ENGINE=innodb TABLESPACE=s1; CREATE TABLE t3 (a int) ENGINE=innodb TABLESPACE=innodb_system; CREATE TABLE t4 (a int) ENGINE=innodb TABLESPACE=innodb_file_per_table; INSERT INTO t2 VALUES (21); INSERT INTO t3 VALUES (31); INSERT INTO t4 VALUES (41); BEGIN; INSERT INTO t1 VALUES (12); INSERT INTO t2 VALUES (22); INSERT INTO t3 VALUES (32); INSERT INTO t4 VALUES (42); === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t1 s1 161 4 Dynamic 0 General test/t2 s1 161 4 Dynamic 0 General test/t3 innodb_system 161 4 Dynamic 0 System test/t4 test/t4 33 4 Dynamic 0 Single # Kill and restart === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/t1 s1 161 4 Dynamic 0 General test/t2 s1 161 4 Dynamic 0 General test/t3 innodb_system 161 4 Dynamic 0 System test/t4 test/t4 33 4 Dynamic 0 Single select * from t1; a 11 select * from t2; a 21 select * from t3; a 31 select * from t4; a 41 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( `a` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 BEGIN; INSERT INTO t1 VALUES (13); INSERT INTO t2 VALUES (23); INSERT INTO t3 VALUES (33); INSERT INTO t4 VALUES (43); RENAME TABLE t1 TO tr1; RENAME TABLE t2 TO tr2; RENAME TABLE t3 TO tr3; RENAME TABLE t4 TO tr4; === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/tr1 s1 161 4 Dynamic 0 General test/tr2 s1 161 4 Dynamic 0 General test/tr3 innodb_system 161 4 Dynamic 0 System test/tr4 test/tr4 33 4 Dynamic 0 Single # Kill and restart === information_schema.innodb_sys_tables and innodb_sys_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type test/tr1 s1 161 4 Dynamic 0 General test/tr2 s1 161 4 Dynamic 0 General test/tr3 innodb_system 161 4 Dynamic 0 System test/tr4 test/tr4 33 4 Dynamic 0 Single select * from tr1; a 11 13 select * from tr2; a 21 23 select * from tr3; a 31 33 select * from tr4; a 41 43 SHOW CREATE TABLE tr1; Table Create Table tr1 CREATE TABLE `tr1` ( `a` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE tr2; Table Create Table tr2 CREATE TABLE `tr2` ( `a` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE tr3; Table Create Table tr3 CREATE TABLE `tr3` ( `a` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE tr4; Table Create Table tr4 CREATE TABLE `tr4` ( `a` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE tr1,tr2,tr3,tr4; DROP TABLESPACE s1;