| Server IP : 172.67.216.182 / Your IP : 172.69.176.33 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/parts/r/ |
Upload File : |
SET default_storage_engine=MyISAM;
#
# CREATE a table with SUBPARTITIONS
#
CREATE TABLE emp (
id INT NOT NULL,
store_name VARCHAR(30),
parts VARCHAR(30),
store_id INT
) engine MyISAM
PARTITION BY RANGE(store_id) SUBPARTITION BY HASH(store_id)
(
PARTITION northeast VALUES LESS THAN (50)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast'
INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast'
(SUBPARTITION ne0, SUBPARTITION ne1),
PARTITION southwest VALUES LESS THAN (100)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest'
INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest'
(SUBPARTITION sw2, SUBPARTITION sw3)
);
Warnings:
Warning 1287 The partition engine, used by table 'test.emp', is deprecated and will be removed in a future release. Please use native partitioning instead.
INSERT INTO emp values(1,'Oracle','NUT',10);
Warnings:
Warning 1287 The partition engine, used by table 'test.emp', is deprecated and will be removed in a future release. Please use native partitioning instead.
INSERT INTO emp values(2,'SAP','BOLT',40);
INSERT INTO emp values(3,'IBM','NAIL',60);
INSERT INTO emp values(4,'SUN','SCREW',90);
SELECT * FROM emp;
id store_name parts store_id
1 Oracle NUT 10
2 SAP BOLT 40
3 IBM NAIL 60
4 SUN SCREW 90
SHOW CREATE TABLE emp;
Table Create Table
emp CREATE TABLE `emp` (
`id` int(11) NOT NULL,
`store_name` varchar(30) DEFAULT NULL,
`parts` varchar(30) DEFAULT NULL,
`store_id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (store_id)
SUBPARTITION BY HASH (store_id)
(PARTITION northeast VALUES LESS THAN (50)
(SUBPARTITION ne0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' ENGINE = MyISAM,
SUBPARTITION ne1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' ENGINE = MyISAM),
PARTITION southwest VALUES LESS THAN (100)
(SUBPARTITION sw2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' ENGINE = MyISAM,
SUBPARTITION sw3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' ENGINE = MyISAM)) */
Warnings:
Warning 1287 The partition engine, used by table 'test.emp', is deprecated and will be removed in a future release. Please use native partitioning instead.
---- MYSQLD_DATADIR/test
emp#P#northeast#SP#ne0.MYD
emp#P#northeast#SP#ne0.MYI
emp#P#northeast#SP#ne1.MYD
emp#P#northeast#SP#ne1.MYI
emp#P#southwest#SP#sw2.MYD
emp#P#southwest#SP#sw2.MYI
emp#P#southwest#SP#sw3.MYD
emp#P#southwest#SP#sw3.MYI
emp.frm
emp.par
---- MYSQL_TMP_DIR/alt_dir_northeast
emp#P#northeast#SP#ne0.MYD
emp#P#northeast#SP#ne0.MYI
emp#P#northeast#SP#ne1.MYD
emp#P#northeast#SP#ne1.MYI
---- MYSQL_TMP_DIR/alt_dir_southwest
emp#P#southwest#SP#sw2.MYD
emp#P#southwest#SP#sw2.MYI
emp#P#southwest#SP#sw3.MYD
emp#P#southwest#SP#sw3.MYI
#
# REORGANIZE the PARTITIONS and SUBPARTITIONS
#
ALTER TABLE emp REORGANIZE PARTITION northeast INTO
(
PARTITION east VALUES LESS THAN (25)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east'
INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east'
(SUBPARTITION e0, SUBPARTITION e1),
PARTITION north VALUES LESS THAN (50)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north'
INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north'
(SUBPARTITION n0, SUBPARTITION n1)
);
Warnings:
Warning 1287 The partition engine, used by table 'test.emp', is deprecated and will be removed in a future release. Please use native partitioning instead.
ALTER TABLE emp REORGANIZE PARTITION southwest INTO
(
PARTITION west VALUES LESS THAN (75)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west'
INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west'
(SUBPARTITION w0, SUBPARTITION w1),
PARTITION south VALUES LESS THAN (100)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south'
INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south'
(SUBPARTITION s0, SUBPARTITION s1)
);
Warnings:
Warning 1287 The partition engine, used by table 'test.emp', is deprecated and will be removed in a future release. Please use native partitioning instead.
SELECT * FROM emp;
id store_name parts store_id
1 Oracle NUT 10
2 SAP BOLT 40
3 IBM NAIL 60
4 SUN SCREW 90
Warnings:
Warning 1287 The partition engine, used by table 'test.emp', is deprecated and will be removed in a future release. Please use native partitioning instead.
SHOW CREATE TABLE emp;
Table Create Table
emp CREATE TABLE `emp` (
`id` int(11) NOT NULL,
`store_name` varchar(30) DEFAULT NULL,
`parts` varchar(30) DEFAULT NULL,
`store_id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (store_id)
SUBPARTITION BY HASH (store_id)
(PARTITION east VALUES LESS THAN (25)
(SUBPARTITION e0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = MyISAM,
SUBPARTITION e1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = MyISAM),
PARTITION north VALUES LESS THAN (50)
(SUBPARTITION n0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = MyISAM,
SUBPARTITION n1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = MyISAM),
PARTITION west VALUES LESS THAN (75)
(SUBPARTITION w0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = MyISAM,
SUBPARTITION w1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = MyISAM),
PARTITION south VALUES LESS THAN (100)
(SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' ENGINE = MyISAM,
SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' ENGINE = MyISAM)) */
Warnings:
Warning 1287 The partition engine, used by table 'test.emp', is deprecated and will be removed in a future release. Please use native partitioning instead.
---- MYSQLD_DATADIR/test
emp#P#east#SP#e0.MYD
emp#P#east#SP#e0.MYI
emp#P#east#SP#e1.MYD
emp#P#east#SP#e1.MYI
emp#P#north#SP#n0.MYD
emp#P#north#SP#n0.MYI
emp#P#north#SP#n1.MYD
emp#P#north#SP#n1.MYI
emp#P#south#SP#s0.MYD
emp#P#south#SP#s0.MYI
emp#P#south#SP#s1.MYD
emp#P#south#SP#s1.MYI
emp#P#west#SP#w0.MYD
emp#P#west#SP#w0.MYI
emp#P#west#SP#w1.MYD
emp#P#west#SP#w1.MYI
emp.frm
emp.par
---- MYSQL_TMP_DIR/alt_dir_northeast
---- MYSQL_TMP_DIR/alt_dir_southwest
---- MYSQL_TMP_DIR/alt_dir_east
emp#P#east#SP#e0.MYD
emp#P#east#SP#e0.MYI
emp#P#east#SP#e1.MYD
emp#P#east#SP#e1.MYI
---- MYSQL_TMP_DIR/alt_dir_north
emp#P#north#SP#n0.MYD
emp#P#north#SP#n0.MYI
emp#P#north#SP#n1.MYD
emp#P#north#SP#n1.MYI
---- MYSQL_TMP_DIR/alt_dir_west
emp#P#west#SP#w0.MYD
emp#P#west#SP#w0.MYI
emp#P#west#SP#w1.MYD
emp#P#west#SP#w1.MYI
---- MYSQL_TMP_DIR/alt_dir_south
emp#P#south#SP#s0.MYD
emp#P#south#SP#s0.MYI
emp#P#south#SP#s1.MYD
emp#P#south#SP#s1.MYI
DROP TABLE emp;
#
# Cleanup
#