MySQL Forums
Forum List  »  Partitioning

MySQL 5.1.6-alpha: Partitioning specific items
Posted by: Edwin DeSouza
Date: February 14, 2006 03:03PM

MySQL 5.1.6-alpha, a new version of the popular Open Source Database
Management System, has been released.

Complete Announcement:
http://forums.mysql.com/read.php?3,69932,69932#msg-69932

Partitioning specific items:

News from the ChangeLog:

Functionality added or changed:

* INCOMPATIBLE CHANGE: Due to a change in the naming scheme for
partitioning and subpartitioning files, it is not possible for
the server to read partitioned tables created in previous MySQL
versions. A suggested workaround is (1) to create a non-
partitioned table with the same table schema using a standard
CREATE TABLE statement (that is, with no partitioning clauses)
and then (2) to issue a SELECT INTO to copy the data into the
non-partitioned table before the upgrade; following the upgrade,
you can partition the new table using ALTER TABLE ... PARTITION
BY .... Alternatively, you can dump the table using mysqldump
prior to upgrading and reload it afterwards with LOAD DATA. In
either case, you should drop the pre-5.1.6 partitioned tables
before upgrading to 5.1.6 or later.
(Bug#13437 (http://bugs.mysql.com/13437))

Important: If any partitioned tables that were created
prior to MySQL 5.1.6 are present following an upgrade to MySQL
5.1.6 or later, it is also not possible to read from the
INFORMATION_SCHEMA.PARTITIONS table, nor will you be able to
drop those tables or the database or databases in which they
are located. In this event, you must: (1) shut down mysqld;
(2) manually delete the table, partition, and (if any)
subpartition files; and then (3) restart the MySQL Server.
(Bug#16695 (http://bugs.mysql.com/16695))

* Partition pruning. A new optimization now ensures searches of
partitioned tables use only those partitions relevant to the
search. The optimization also affects INSERT and UPDATE
operations on partitioned tables, making those operations
faster on partitioned table than in earlier versions of MySQL.

* Replication between MySQL Clusters is now supported. It is now
also possible to replicate between a MySQL Cluster and a non-
cluster database. See Section 16.7, "MySQL Cluster Replication."

* Partition support is not an "engine", but it was included in
the output of SHOW ENGINES. Now it is not.
(Bug#14355 (http://bugs.mysql.com/14355))
The have_partition_engine was renamed to have_partitioning.
(Bug#16718 (http://bugs.mysql.com/16718))

* ANALYZE TABLE is now supported for partitioned tables.
(Bug#13441 (http://bugs.mysql.com/13441))

* Support for OPTIMIZE, CHECK, and REPAIR of partitioned
tables has been added.
* Added the FILES table to INFORMATION_SCHEMA.

* Added the EVENTS table to INFORMATION_SCHEMA.

* Added the PARTITIONS table to INFORMATION_SCHEMA.

* Significant optimizations of partition management has been
performed for non-NDB partitioned tables. This relates to ALTER
TABLE DROP PARTITION, ALTER TABLE ADD PARTITION, ALTER TABLE
REORGANIZE PARTITION.
+ Now adding a new partition to RANGE/LIST partitioned can
be done without touching other partitions and the same
with DROP PARTITION and REORGANIZE will only reorganize
those partitions necessary to reorganize.

* The ALTER TABLE REORGANIZE PARTITION has been changed,
previously the syntax used the keyword REORGANISE.

* A number of new partition commands have been added:
ALTER TABLE OPTIMIZE PARTITION
ALTER TABLE ANALYZE PARTITION
ALTER TABLE CHECK PARTITION
ALTER TABLE REPAIR PARTITION
ALTER TABLE REBUILD PARTITION


Bugs fixed:

* Attempting to insert data into a partitioned table that used
the BLACKHOLE storage engine caused mysqld to crash.
(Bug#14524 (http://bugs.mysql.com/14524))

* Using RANGE partitioning with a CASE statement as the
partitioning function would cause records to be placed in the
wrong partition.
(Bug#15393 (http://bugs.mysql.com/15393))

* ALTER TABLE ... ADD PARTITIONS on a table with one partition
crashed the server. (Bug#15820 (http://bugs.mysql.com/15820))

* Error message for specifying value for which no partition
exists returned wrong values on certain platforms.
(Bug#15910 (http://bugs.mysql.com/15910))

* The DATA DIRECTORY and INDEX DIRECTORY clauses of a CREATE
TABLE statement involving partitions did not work.
(Bug#14354 (http://bugs.mysql.com/14354))

* NDB Cluster (Disk Data): Tablespaces created using parameters
with relatively low values (< 10 MB) produced filesizes much
smaller than expected. (Bug#16742 (http://bugs.mysql.com/16742))

* Trying to add more than one partition in a single ALTER TABLE...
ADD PARTITION statement caused the server to crash.
(Bug#16534 (http://bugs.mysql.com/16534))

* Creating a partitioned table using a storage engine other than
the session default storage engine caused the server to crash.
(Bug#15966 (http://bugs.mysql.com/15966))

* An ALTER TABLE ... PARTITION BY ... statement did not have any
effect. (Bug#15523 (http://bugs.mysql.com/15523))

* Using mysqldump to obtain a dump of a partitioned table
employing the NDB storage engine produced a non-functional table
creation statement. (Bug#13155 (http://bugs.mysql.com/13155))

* SHOW CREATE TABLE did not display the PARTITIONS clause for
tables partitioned by HASH or KEY.
(Bug#14327 (http://bugs.mysql.com/14327))

* Inserting a negative value into an integer column used as the
partitioning key for a table partitioned by HASH could cause
the server to crash. (Bug#15968 (http://bugs.mysql.com/15968))

* With a table partitioned by LIST, inserting a value which was
smaller than any value shown in the partitioning value-lists
could cause the server to crash.
(Bug#14365 (http://bugs.mysql.com/14365))

* ALTER TABLE ... DROP PARTITION would truncate all DATE column
values in the table's remaining partitions to NULL.
(Bug#13644 (http://bugs.mysql.com/13644))

* ALTER TABLE ... ADD PARTITION could crash the server or cause
an Out of memory error in some circumstances.
(Bug#13447 (http://bugs.mysql.com/13447))

* The server would allow foreign keys to be declared in the
definition of a partitioned table despite the fact that
partitioned tables do not support foreign keys (see Section
17.4, "Restrictions and Limitations on Partitioning").
(Bug#13446 (http://bugs.mysql.com/13446))

* A SELECT from a key-partitioned table with a multi-column key
could cause the server to crash.
(Bug#13445 (http://bugs.mysql.com/13445))

* Issuing a TRUNCATE statement twice in succession on the same
partitioned table would cause the server to crash.
(Bug#13442 (http://bugs.mysql.com/13442))

* Using a REPLACE statement on a partitioned table caused the
server to crash. (Bug#13440 (http://bugs.mysql.com/13440))

* Using an identifier rather than a literal integer value in the
LESS THAN clause of a range-partitioned table could cause the
server to crash and corruption of tables.
(Bug#13439 (http://bugs.mysql.com/13439))

* Using ENGINE=... within a PARTITION clause could cause the
server to crash. (Bug#13438 (http://bugs.mysql.com/13438))

* CREATE TABLE ... LIKE did not work if the table whose schema
was to be copied was a partitioned table.
(Bug#13435 (http://bugs.mysql.com/13435))

* SHOW CREATE TABLE did not display the PARTITIONS clause for
tables partitioned by HASH or KEY.
(Bug#14327 (http://bugs.mysql.com/14327))



Edited 1 time(s). Last edit at 07/07/2006 02:19PM by Edwin DeSouza.

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL 5.1.6-alpha: Partitioning specific items
3546
February 14, 2006 03:03PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.