MySQL Forums - Partitioning

Possible to partition on client id and year/month from timestamp? (1 reply)

M Z — Thu, 22 May 2025 13:32:36 +0000

Hi! Is it possible to partition on my_client_id and my_table_timestamp? I am not doing it for performance but rather the ability to archive and then delete entire partition per client id per month.

Here's a sample DDL:

CREATE TABLE `my_table` (
`my_client_id` int NOT NULL,
`my_table_id` bigint NOT NULL DEFAULT '0', -- auto increment
`my_client_location_id` int DEFAULT NULL,
`my_table_timestamp` timestamp NOT NULL
);

Many thanks in advance,

Moving table partitions to different tablespace location (no replies)

SUJITH NAIR — Fri, 21 Feb 2025 04:10:35 +0000

I am trying to move partitions to a different location because we have reached the maximum limit at the host level, and it cannot be extended.

I created a new tablespace and tried to move the partitioned tables.

SQL

mysql> ALTER TABLE dummy_table TABLESPACE new_ts3;
ERROR 1478 (HY000): InnoDB : A partitioned table is not allowed in a shared tablespace.
mysql>

I created separate tablespaces for each partition:

SQL

mysql> CREATE TABLESPACE ts2 ADD DATAFILE '/u01/data/datafile2.ibd' ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLESPACE ts3 ADD DATAFILE '/u01/data/datafile3.ibd' ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLESPACE ts4 ADD DATAFILE '/u01/data/datafile4.ibd' ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

However, when I tried to move the partitions, I encountered a syntax error:

SQL

mysql> ALTER TABLE dummy_table
-> PARTITION p20250212 TABLESPACE ts1,
-> PARTITION p20250213 TABLESPACE ts2,
-> PARTITION p20250214 TABLESPACE ts3,
-> PARTITION p20250215 TABLESPACE ts4,
-> PARTITION p20250216 TABLESPACE ts5,
-> PARTITION p20250217 TABLESPACE ts6,
-> PARTITION p20250218 TABLESPACE ts7,
-> PARTITION p20250219 TABLESPACE ts8,
-> PARTITION p20250220 TABLESPACE ts9;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION p20250212 TABLESPACE ts1,
PARTITION p20250213 TABLESPACE ts2,
PARTITION p20250214 TABLESPACE ts3' at line 2
mysql>

Any other way to go forward?

Export/Import with table partitioning (3 replies)

tempman tempman — Thu, 11 Aug 2022 13:00:42 +0000

Hi,

I'm newbie, so I'm sorry. I'm trying migrate a 5.6 mysql database to mariadb (a more new version).

I have a full database backup done (not sure if it's correct with all data). When I do import in target mariadb database all is ok. But I've compared datadir size and it is different. Source is 144MB and target is 84MB, so I've thought that something is bad.

I've compared files with different sizes and I've compared this tables and I've seen that in source DB it has partitions. These partitions are not in target, so I think that it is the problem. What I don't understand is that when I do select count(*) in this tables, it shows same rows in source and target (not sure if in source it is counting partitioned data :-?).

Could someone help me please about how could I export all data and import in mariadb database and all is ok (with partitions)? :-) Thanks beforehand.

Cheers...

Strange values in information_schema.partitions (no replies)

Carlos Simó — Wed, 03 Aug 2022 10:48:19 +0000

We have a Zabbix server that uses MySQL 8.0.28 as DB, installed on an instance of Azure MySQL Flexible Server.
We apply a quite usual workaround for Zabbix installations with a large volume of data, which is to partition certain types of tables (the original Zabbix design does not contemplate it) on a daily basis, using,
in our case, a python script that runs regularly every day. This script checks which partitions are older than a pre-set threshold (in my case 6 days) and deletes them.
It also checks if it needs more partitions, in such a way that always ahead of the data partition that corresponds to the current day, we leave a number of empty partitions established in advance (in my case 10 days).
In normal operation of the script, per day, it deletes one partition (the one that is older than the expected threshold) and creates a future partition.

For a few days now, I am watching some "weird" values in the information_schema.partitions table in the create_date and update_date fields.
For example, for this morning's run, for one of the partitioned tables you see the following:

mysql> select table_schema, table_name, partition_name, table_rows, create_time, update_time from partitions where table_name = 'history';
+--------------+------------+----------------+------------+---------------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | CREATE_TIME | UPDATE_TIME |
+--------------+------------+----------------+------------+---------------------+---------------------+
| zabbix | history | p2022_07_28 | 53687400 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_07_29 | 48749688 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_07_30 | 53768640 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_07_31 | 52771337 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_01 | 50966961 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_02 | 53817777 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_03 | 21373932 | 2022-08-03 03:32:25 | 2022-08-03 08:56:12 |
| zabbix | history | p2022_08_04 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_05 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_06 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_07 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_08 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_09 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_10 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_11 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_12 | 0 | 2022-08-03 03:32:25 | NULL |
| zabbix | history | p2022_08_13 | 0 | 2022-08-03 03:32:25 | NULL |
+--------------+------------+----------------+------------+---------------------+---------------------+
17 rows in set (0.00 sec)

It is observed in the create_time field that all the partitions have today's date, when the partitioning script is executed.
Actually, following the logs of the partitioning script, the only partition that was created today was p2022_08_13.
In the update_time field, it looks like today's partition, obviously registering activity, which is logical, but nevertheless
partitions from previous days, which have data and have had previous similar activity, turn out to have a "NULL" value, when it should, I think,
have an adequate value on the day of the partition.
For example, a logical value for p2022_07_28 could be 2022-07-28 23:59:00 or even 2022-07-29 00:03:00 for transactions that had not been completed before the start of the partition the following day.

For a few days now I have been noticing performance issues with the Zabbix application and the partitioning script.
It turns out that the script takes much longer to perform partition management, specifically deleting the partition that is older than 6 days.
Today, for example, it took about three minutes to delete the p2022_07_27 partition from the history table, when it usually does not reach the minute of execution. It is a partition with a lot of data.
Doing a full system check I don't see errors in MySQL, only evidence that there are I/O performance issues and that there are slow queries, coinciding in time with the execution of the partitioning script.
The partitioning script does not log execution errors.
In Zabbix overloads are observed in the internal process history_syncer that precisely setup monitoring data on the history tables.

That's when I've found these "weird" values in the partitions table. This same situation is systematically repeated every day and I don't know if it really is the origin of my problems.

Any suggestion?

need help for partition on tables with foreign keys (7 replies)

Johnson WIlson — Wed, 08 Jun 2022 16:22:07 +0000

We have a few very huge tables in our dbs which need partitioning， with relationship between them.
such as transaction and transaction_log
transaction table records a serial of transactions from our application

CREATE TABLE TRANSACTION
(
TRANS_ID VARCHAR(10) NOT NULL,
CREATION_DATE DATETIME NOT NULL,
......................
PRIMARY KEY (TRANS_ID)
);

transaction_log table records all child events happened on the transaction since the transaction was created, like approval, decline, cancellation of the transaction
It can be joined to transaction table with TRANS_ID

CREATE TABLE TRANSACTION
(
TRANS_EVENT_ID VARCHAR(10) NOT NULL,
TRANS_ID VARCHAR(10) NOT NULL,
LAST_UPDATE_DATE DATETIME NOT NULL,
......................
PRIMARY KEY (TRANS_EVENT_ID )
);

The transaction table has TRANS_ID and creation_timestamp
THe transaction_event table has another ID and last_update of the event, both primary ID are random generated 10 digit string, and transaction_event table can be joined to transaction table using trans_ID

If we put partitioning on those tables based on date_time, it will cause problem when we drop and purge paritioning, for example, if we drop partition for 2020-12 partition on both transaction and event table, since transaction table is based
on creation timestamp, and event table is by last updated timestamp, the drop partitioing will fail because of the foreign key constrain.

We can't put partition based on ID either, because ID is randomly generated.

I cant find the best way for do this, i really appreciate if you could provide some suggestions.

Issue with Partitioning (1 reply)

sreenath kn — Mon, 06 Jun 2022 01:48:07 +0000

I have done some tests to study about partitioning , so that I can implement it to my live database that is having large data.
Step 1:

CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE(year(purchased))
(
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005),
PARTITION p4 VALUES LESS THAN (2010),
PARTITION p5 VALUES LESS THAN (2015),
PARTITION pmax VALUES LESS THAN MAXVALUE
);

Setp 2:

INSERT INTO tr VALUES
(1, 'Store1', '2003-10-15'),
(2, 'Store1', '1997-11-05'),
(3, 'Store1', '2009-03-10'),
(4, 'Store2', '1989-01-10'),
(5, 'Store1', '2014-05-09'),
(6, 'Store1', '1987-06-05'),
(7, 'Store3', '2011-11-22'),
(8, 'Store3', '1992-08-04'),
(9, 'Store4', '2006-09-16'),
(10, 'Store4', '1998-12-25');

Step 3:

EXPLAIN SELECT * FROM tr WHERE purchased BETWEEN '1987-06-05' AND '1989-01-10';

in this step when am retriving data it only using p0 partition which is correct.

Setp 4:

EXPLAIN SELECT * FROM tr WHERE YEAR(purchased) BETWEEN 1987 AND 1989;

in this step I just changed the where query to check the data based on year only not a full date.Here in this case the query searching in whole partition.
I don't understand why it is working like this.Please explain if there is any mistake so that i can update my steps and complete the work.

Wrong index used in SELECT , ROW_NUMBER() OVER (PARTITION BY (3 replies)

M Z — Sat, 28 Aug 2021 05:58:37 +0000

Hi! It appears MySQL 8.0.23 optimizer uses wrong index in queries using SELECT * , ROW_NUMBER() OVER (PARTITION BY ....
How do you index the table for my query? Here's the modified DDL and DML

-- DDL:
----------------------------
CREATE TABLE `test_table` (
`client_id` int DEFAULT NULL,
`division_id` int DEFAULT NULL,
`some_val` float DEFAULT NULL,
`end_date` timestamp NULL DEFAULT NULL,
`department_id` int DEFAULT NULL,
`some_data` varchar(45) DEFAULT NULL,
KEY `idx_client_enddate_division` (`client_id`,`end_date`,`division_id`),
KEY `idx_some_val` (`some_val` DESC),
KEY `idx_client_division` (`client_id`,`division_id`),
KEY `idx_client_department` (`client_id`,`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`client_id`)
(PARTITION part_null VALUES IN (NULL) ENGINE = InnoDB,
PARTITION part_neg_one VALUES IN (-1) ENGINE = InnoDB,
PARTITION a100 VALUES IN (100) ENGINE = InnoDB,
PARTITION a101 VALUES IN (101) ENGINE = InnoDB,
PARTITION a102 VALUES IN (102) ENGINE = InnoDB,
PARTITION a103 VALUES IN (103) ENGINE = InnoDB) */;

-- DATA
---------------------------------
INSERT INTO test_table(client_id, division_id, some_val, end_date, department_id, some_data) VALUES
(100, 1, 35.78, '2021-11-13 12:25:04', NULL, NULL),
(100, 1, 0.956245, '2022-04-09 05:24:48', NULL, NULL),
(100, 1, 0.861436, '2022-02-12 01:04:55', NULL, NULL),
(100, 1, 0.761067, '2023-07-29 05:49:36', NULL, NULL),
(100, 1, 0.707362, '2022-08-30 14:57:18', NULL, NULL),
(100, 1, 0.590262, '2022-03-24 01:41:34', NULL, NULL),
(100, 1, 0.848734, '2022-02-14 03:33:34', NULL, NULL),
(100, 1, 0.813579, '2022-01-30 14:22:59', NULL, NULL),
(100, 1, 0.819393, '2022-04-05 11:13:52', NULL, NULL),
(100, 1, 0.246256, '2021-12-29 21:55:56', NULL, NULL),
(100, 1, 0.304336, '2023-05-15 13:37:22', NULL, NULL),
(100, 1, 0.566081, '2021-11-11 23:25:52', NULL, NULL),
(100, 1, 0.0165217, '2022-11-19 20:34:28', NULL, NULL),
(100, 1, 0.21918, '2021-11-05 22:01:14', NULL, NULL),
(100, 1, 0.0157273, '2022-12-05 03:12:42', NULL, NULL),
(100, 1, 0.326197, '2022-10-16 16:49:26', NULL, NULL),
(100, 1, 0.056206, '2022-07-01 12:44:24', NULL, NULL),
(100, 2, 0.132138, '2022-02-20 04:37:58', NULL, NULL),
(100, 2, 0.00476808, '2021-12-11 07:20:57', NULL, NULL),
(100, 2, 0.901352, '2023-06-29 11:55:47', NULL, NULL),
(100, 2, 0.0827709, '2022-08-30 02:30:30', NULL, NULL),
(100, 2, 0.460482, '2022-12-06 22:16:54', NULL, NULL),
(100, 2, 0.00422428, '2023-07-23 02:45:55', NULL, NULL),
(100, 2, 0.935921, '2022-12-31 05:59:29', NULL, NULL),
(100, 2, 0.74449, '2022-10-05 02:56:12', NULL, NULL),
(100, 2, 0.722022, '2023-04-02 11:51:48', NULL, NULL),
(100, 3, 0.0180021, '2022-09-26 16:57:38', NULL, NULL),
(100, 3, 0.843845, '2022-07-16 09:28:08', NULL, NULL),
(100, 3, 0.871028, '2023-05-28 13:15:08', NULL, NULL),
(100, 3, 0.95494, '2021-08-24 08:33:43', NULL, NULL),
(100, 3, 0.304606, '2022-06-03 05:23:45', NULL, NULL),
(100, 3, 0.19306, '2022-12-24 11:27:13', NULL, NULL),
(100, 3, 0.926688, '2022-08-21 11:02:35', NULL, NULL),
(100, 3, 0.869524, '2023-02-03 20:43:32', NULL, NULL),
(100, 3, 0.180765, '2022-11-02 16:23:13', NULL, NULL),
(100, 3, 0.608817, '2021-11-19 14:48:45', NULL, NULL),
(100, 3, 0.940649, '2022-01-23 23:56:26', NULL, NULL),
(100, 3, 0.392966, '2022-01-18 19:32:53', NULL, NULL),
(100, 3, 0.00051388, '2022-03-02 21:56:07', NULL, NULL),
(100, 3, 0.473072, '2022-07-16 10:49:32', NULL, NULL),
(100, 3, 0.983735, '2022-07-13 18:15:20', NULL, NULL),
(100, 3, 0.433318, '2023-01-18 19:24:53', NULL, NULL),
(101, 7, 0.867828, '2023-02-22 21:51:02', NULL, NULL),
(101, 7, 0.31631, '2022-01-14 06:44:16', NULL, NULL),
(101, 7, 0.199429, '2022-03-12 13:21:03', NULL, NULL),
(101, 7, 0.942369, '2023-02-25 11:00:54', NULL, NULL),
(101, 7, 0.110144, '2021-12-15 03:06:48', NULL, NULL),
(101, 7, 0.611413, '2022-07-24 18:00:36', NULL, NULL),
(101, 7, 0.625555, '2022-11-18 12:09:26', NULL, NULL),
(101, 7, 0.382829, '2023-06-30 06:53:03', NULL, NULL),
(101, 7, 0.64375, '2022-04-07 07:22:21', NULL, NULL),
(101, 7, 0.785784, '2023-05-17 16:06:40', NULL, NULL),
(101, 7, 0.136147, '2023-07-19 09:44:43', NULL, NULL),
(101, 7, 0.514747, '2022-10-26 02:08:49', NULL, NULL),
(101, 7, 0.554862, '2023-05-29 11:04:18', NULL, NULL),
(101, 7, 0.909665, '2023-03-09 09:01:45', NULL, NULL),
(101, 11, 0.289879, '2021-08-27 09:53:48', NULL, NULL),
(101, 11, 0.320718, '2022-07-22 20:45:40', NULL, NULL),
(101, 11, 0.484727, '2023-06-28 03:22:58', NULL, NULL),
(101, 11, 0.323356, '2023-01-29 07:24:45', NULL, NULL),
(101, 11, 0.781222, '2022-11-21 00:22:52', NULL, NULL),
(101, 11, 0.933015, '2022-12-22 22:31:01', NULL, NULL),
(101, 11, 0.696283, '2022-05-07 02:22:56', NULL, NULL),
(101, 11, 0.83224, '2021-08-05 20:35:50', NULL, NULL),
(101, 11, 0.546006, '2022-12-26 19:53:45', NULL, NULL),
(101, 11, 0.883958, '2022-03-11 10:38:08', NULL, NULL),
(101, 11, 0.881157, '2022-07-20 08:59:56', NULL, NULL),
(101, 11, 0.786355, '2022-07-11 13:14:47', NULL, NULL),
(101, 11, 0.0103294, '2022-11-02 12:40:47', NULL, NULL),
(101, 11, 0.119066, '2022-12-27 11:41:08', NULL, NULL),
(101, 11, 0.169285, '2023-01-14 21:53:42', NULL, NULL),
(102, 3, 0.379421, '2022-12-15 22:34:06', NULL, NULL),
(102, 3, 0.309135, '2022-07-14 00:03:25', NULL, NULL),
(102, 3, 0.458774, '2023-04-20 23:57:12', NULL, NULL),
(102, 3, 0.934485, '2021-10-01 08:35:20', NULL, NULL),
(102, 3, 0.626253, '2023-04-29 05:35:52', NULL, NULL),
(102, 3, 0.488451, '2023-03-22 15:02:46', NULL, NULL),
(102, 3, 0.644282, '2023-02-02 05:29:07', NULL, NULL),
(102, 3, 0.845293, '2023-07-01 02:47:37', NULL, NULL),
(102, 3, 0.262038, '2022-06-10 13:41:56', NULL, NULL),
(102, 3, 0.371081, '2022-09-13 05:07:40', NULL, NULL),
(102, 3, 0.692191, '2023-02-18 08:07:28', NULL, NULL),
(102, 3, 0.811907, '2023-01-11 22:12:11', NULL, NULL),
(102, 3, 0.196504, '2023-03-08 22:43:33', NULL, NULL),
(102, 3, 0.426422, '2023-01-09 01:57:13', NULL, NULL),
(102, 3, 0.33348, '2022-07-30 10:34:25', NULL, NULL),
(102, 5, 0.498327, '2023-07-25 01:18:24', NULL, NULL),
(102, 5, 0.466895, '2022-04-17 11:39:14', NULL, NULL),
(102, 5, 0.386062, '2023-04-18 03:39:04', NULL, NULL),
(102, 5, 0.133126, '2021-10-04 22:28:22', NULL, NULL),
(102, 5, 0.0548398, '2021-07-31 20:06:42', NULL, NULL),
(102, 5, 0.84382, '2022-01-02 21:51:26', NULL, NULL),
(102, 5, 0.539021, '2021-09-07 04:16:06', NULL, NULL),
(102, 5, 0.647028, '2021-09-24 14:22:20', NULL, NULL),
(102, 5, 0.442331, '2023-07-17 04:38:53', NULL, NULL),
(102, 5, 0.581043, '2023-07-01 16:57:05', NULL, NULL),
(102, 5, 0.0588302, '2022-05-28 02:14:01', NULL, NULL),
(102, 5, 0.88839, '2021-12-25 19:51:54', NULL, NULL),
(102, 5, 0.349947, '2021-11-10 07:19:22', NULL, NULL),
(102, 5, 0.653479, '2023-04-08 18:20:14', NULL, NULL),
(102, 5, 0.266575, '2023-03-04 04:29:29', NULL, NULL),
(102, 5, 0.18361, '2022-08-20 02:17:14', NULL, NULL),
(102, 5, 0.0894101, '2023-04-21 13:01:31', NULL, NULL),
(102, 5, 0.0463059, '2022-11-11 20:53:06', NULL, NULL),
(102, 5, 0.0751218, '2022-06-22 01:34:27', NULL, NULL),
(102, 5, 0.0105604, '2022-12-31 19:55:54', NULL, NULL),
(102, 5, 0.524552, '2022-07-22 14:31:27', NULL, NULL),
(102, 5, 0.871445, '2023-05-11 10:33:45', NULL, NULL),
(102, 5, 0.836485, '2022-08-08 07:57:38', NULL, NULL),
(102, 5, 0.0502111, '2023-01-03 18:10:27', NULL, NULL),
(102, 5, 0.425646, '2023-07-17 19:39:04', NULL, NULL),
(102, 5, 0.635379, '2022-01-14 03:47:27', NULL, NULL),
(102, 5, 0.241381, '2022-08-13 01:09:25', NULL, NULL);

QUERY:
------------------------------------

explain SELECT m.*, ROW_NUMBER() OVER (PARTITION BY airline_id, end_date, fleet_id ORDER BY aqi DESC) AS rn
FROM pollutant_aggregated_data m
WHERE airline_id = 115
AND end_date <= '2022-07-30 14:53:27'
AND end_date >= '2020-07-30 14:53:27'
AND fleet_id is not null ;

So the optimizer picks idx_client_department instead of idx_client_enddate_division.

What am I doing wrong? How would you index this table for the aforementioned query?

Infinite thanks in advance.

Need help in setting up partition (year and month - key partition) to MySQL Document store collection (no replies)

Vasundhara Ranga — Tue, 05 Jan 2021 13:26:05 +0000

Hi Team,

I am new to MySQL document store. I have created a collection (my_notes) in MySQL 8.x document store, inserted sample json documents into it, which is working fine. Now i need to apply partition to this collection. Please let me know how can create key partition for the collection.

Thanks,
vasundhara.

MySQL Document store (no replies)

Vasundhara Ranga — Tue, 05 Jan 2021 12:25:48 +0000

Hi Team,

I am new to MySQL document store. I have created a collection (my_notes) in MySQL document store, inserted sample json documents into it, which is working fine. Now i need to apply partition to this collection. Please let me know how can create key partition for the collection.

Thanks,
vasundhara.

Mysql 8.0.22 - Indexes on virtual columns do not update (1 reply)

Vasilis Kioustelidis — Mon, 07 Dec 2020 21:24:42 +0000

I have an original replication setup consisting of two OLTP mysql instances (5.7.31 on Centos 7) and two OLAP (5.7.30 on Solaris). The schemas on the OLAP servers differ from OLTP in two significant ways:
1. several tables have computed (i.e. columns) columns that are build by applying:
* a cast to date on a VARCHAR timestamp
* a hashing function on VARCHAR columns
2. the tables that have the above enhancements have been provided with indexes on the computed columns
The original replication setup has been working perfectly for several months withou issue.
I decided to add three new OLAP devices in an effort to remove the solaris nodes AND upgrade to the newer version of mysql.

The three new instances use mysql 8.0.14 (on Centos 7) and get replication from one the OLAP nodes. This new setup was also working as expected and everything was being updated as normal.

The next step was to decouple the three new OLAP nodes from the old OLAP nodes and have them receive replication directly from the OLTP nodes. I first upgraded the new nodes to 8.0.22 without issue and I switched their master configuration to the OLTP servers. The row updates started coming through as expected, the computed columns were complete however the indexes on the computed columns stopped updating.

I can retrieve data using the indexes up to the point when I made the change but since then the indexed receive NO updates. I also tried to create copies of the indexes (different name) and again I noticed that the new indexes were complete up to the creation point-in-time but they received no updates after that.

Need help to create auto increment partition based on date (no replies)

Tanmoy Moulik — Wed, 28 Oct 2020 10:30:50 +0000

Dear Team,

I am new to mysql.I have been exploring possibility to create partition based on date.So did came across key partition case be used with date field ,however in such case number of partition is fixed.I have shared the sample sql.

create table test_date_partition
(name varchar(100),
dept varchar(100),
salary integer,
join_date date not null)
partition by key(join_date)
partitions 6;

However, I want partition in which partition should auto increment and need basis partition with old date can be dropped.Is there any method or solution to solve this issue?

Thanks in Advance!!!

Regards,
Tanmoy Moulik

Locking (no replies)

Alex Brown — Wed, 14 Oct 2020 07:32:49 +0000

Hi,

I am trying to find more information on what locks would be applies when using the command:

ALTER TABLE ... DROP PARTITION ...

I am particularly interested in how this compares to TRUNCATE PARTITION

Whilst I can find documentation on truncate I cant seem to find anything conclusive on drop.

I have a hunch that DROP will lock the whole table whereas truncate will prune lock, only locking the data to be deleted.

Hoping someone here can provide some clarification, and or point me to the correct docs.

Here are some of the places I have already looked:
https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-locking.html
https://dev.mysql.com/doc/refman/5.6/en/lock-tables.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html

Would like to know what happens in V5.6 / 5.7

MySQL: automatic partitions surely would be nice (no replies)

Edwin Desouza — Fri, 25 Sep 2020 16:19:44 +0000

MySQL: automatic partitions surely would be nice
— https://blog.koehntopp.info/2020/09/25/mysql-dynamic-partitions-suck.html

Add new LIST partition based on passed id in stored proc? (3 replies)

M Z — Thu, 10 Sep 2020 01:26:54 +0000

Hi! Thank you for taking a look.
I have a table with LIST partitioning. We need to have each client in its own partition:

CREATE TABLE `mydb`.`test` (
`client_id` INT NOT NULL,
`client_name` VARCHAR(45) NULL,
PRIMARY KEY (`client_id`));

ALTER TABLE test PARTITION BY LIST (client_id)
(partition p100 values in (100),
partition p101 values in (101));

I am trying to create a stored procedure to be called when a new client is created:

DELIMITER $$
USE `mydb`$$
CREATE PROCEDURE `util_partioning_partition_new`(IN p_client_id INT)
BEGIN
DECLARE partition_name varchar(25);
select CONCAT('p', p_client_id) into partition_name;
ALTER TABLE test ADD PARTITION (PARTITION partition_name values in (p_client_id));
END$$

DELIMITER ;
;

and getting this error:
Apply changes to util_partioning_partition_new Error 1064: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near 'p_client_id));

PLEASE HELP! How do I pass client ID to the values in()?

Many thanks in advance,

Help to alter a non partitioned table to partitioned with subpartitions (no replies)

O C — Fri, 28 Aug 2020 11:04:04 +0000

Hi,

I have a table in my MySQL 5.7 database:

create table CUSTOMERS
(
CUSTOMER_ID bigint not null,
SERVICE_ID bigint not null,
POLICY_ID bigint,
CUSTOMER_NAME varchar(255),
CREATED_ON datetime(6) not null,
primary key (CUSTOMER_ID),
unique key A_U1 (SERVICE_ID, CUSTOMER_NAME)
);

create index C_2 on CUSTOMERS
(
POLICY_ID
);

I'm looking for help to create the statements on how to change this table to be a partitioned table by list SERVICE_ID first and subpartitioned by CREATED_ON

SERVICE_ID can be one of the following numbers: 01, 02, 03, 04, 05, 06, 07, 08, 09
CREATED_DTM is a timestamp and I want the subpartitions to be daily based.

Please also let me know if I need to upgrade to MySQL 8.0 to get access to better features to do this.

Thanks in advance!

Automate MySQL Partitioning using events and procedures (no replies)

Edwin Desouza — Wed, 20 May 2020 15:10:05 +0000

Automate MySQL Partitioning using events and procedures
- http://mysql-nordic.blogspot.com/2020/05/automate-mysql-partitioning-using.html

Error 'Unknown error 1507' on query. Query: 'ALTER TABLE x DROP PARTITION x' (no replies)

Kevin Crous — Thu, 19 Mar 2020 10:01:51 +0000

I am having a problem on a newly setup slave database every day I get replication error Unknown error 1507' on query. Query: 'ALTER TABLE x DROP PARTITION x'.

They all relate to partitions and does not seem to exist. So far I have been skipping the error however I am not certain if this is the correct way as the error keeps coming back. I believe there is a stored procedure or something on the master that prunes the partitions this is likely why it occurs daily.

Having said that this is a slave db from a slave db. The middle slave that is slaving directly from the master does not have this behaviour. Any suggestions, recommendations would be appreciated.

MySQL Partitioning based on ID and Week (no replies)

M Z — Mon, 16 Mar 2020 17:14:22 +0000

Hi! I have a table

CREATE TABLE `acme`.`partitioned_table` (
`id` INT NULL,
`client_id` INT NOT NULL,
`create_datetime` INT NOT NULL,
`some_val` VARCHAR(45) NULL);

I'd like to partition this table in such a way that each client’s data is stored in its own partition based on the client_id AND each partition can only contain data for 1 week based on the create_datetime. This is done so we can drop weekly one week’s worth of data based each client’s own retention policy.

For example, some clients would like to have 3 months of data while others may have longer data retention policies.

I am having a hard time being new to MySQL to come up with a proper partitioning strategy. How can I partition by Week based on the INT column? To throw a curve ball this might be hosted on AWS RDS later.

Many thanks in advance,

M

Help with partition strategy (2 replies)

Nuno Donato — Tue, 28 Jan 2020 16:12:06 +0000

Hi everyone!

I'm trying to find out what would be the best partition strategy for a table that has millions of rows. The relevant columns are

* date
* userid
* key
* value

Ke-values are received every X min from sensors, and there are N key-value pairs per user. (meaning, every time data is received, it gets more than 1 key-value pair per user).

The table grows big very fast, but most users only need to access the last month(or year) of data (to generate charts and stats). However, I need to keep all data available for people who want to see historic data.

My first thought was to partition based on userid first and date second (using a range rule to split the partitions).

My other option was to actually have a scheduled event to move older rows (>2years) to an "archive" table. Both the current and archive table would be partitioned based on userid only.

What would be the preferred strategy here? Any other valuable tips? Is it worth to actually simulate both scenarios and see which performs better?

thanks in advance

Adding partition taking time (1 reply)

Ragesh Kandathil — Fri, 10 Jan 2020 13:25:50 +0000

in MySQL version 5.7 community server, Adding partition is taking nearly 30 mins.

what could be the issue. how to trace or find the root cause

Why Partition a table on a SAN (no replies)

doug valdez — Mon, 16 Dec 2019 23:08:52 +0000

How does the engine benefit if I partition a table on a SAN.

Assuming i currently have 1 drive letter on a LUN across multiple drives.

I then ask my SAN admin for 3 more drives with an effort to break up my table into 4 drives.

He proceeds to add 3 more luns/Disk across the same set of disks how does it benefit the table?

It seems of the End user adds a where clause on the date it would be the same as not breaking up the table? what am I missing.

Thanks

MySQL 5.5 - can't discard tablespace for partitioned tables? (no replies)

Jan Andersen — Fri, 25 Oct 2019 08:34:28 +0000

I have to restore a partitioned table from an old MySQL 5.5 backup, created with Percona's innobackupex. I have tried to import it into a newer version, but it fails due to incompatibilities around temporal data, so I have to use MySQL 5.5. The strategy involves:

- create the table from information in the .frm file
- discard the tablespace
- move the backup tablespace to the data directory
- import tablespace

However, when I try to discard the tablespace, I get an error:

ERROR 1031 (HY000) at line 1: Table storage engine for 'my_partitioned_table' doesn't have this option

I am sure I have done this before, but quite possibly in a more up-to-date version; is this not possible in MySQL 5.5?

Maintaining indexes with exchange partition (no replies)

Andrew Edwards — Tue, 25 Jun 2019 11:04:39 +0000

We have a requirement to have a table partitioned by month to allow us to use exchange partition to swap in updated partitions on a regular basis. I want to understand how indexes are maintained on other columns in this scenario. When exchange partition is ran it will swap in the new data. I believe this is a simple file system operation of moving the new data in. How and when is the indexes updated? Or does it just retain the index data from the source table?

error Code 1441:datetime function: datetime field overflow (no replies)

MEL Support — Fri, 17 May 2019 08:20:30 +0000

I am using version 5.6.44 with Tripwire Log centre. The TLC stopped sending data to the DB, the logs show an error stating that a partition could not be found. I was advised to use the CALL ADDPARTITION(737548) in a the MySQL command line, I did but saw no error. When I used MySQL WorkBench and looked at the partitions it was not there. I then ran the same command in WorkBench and I see the output error Code 1441:datetime function: datetime field overflow

Does anyone have a clue?

Adding Subpartitions, help needed (no replies)

Pandit P — Tue, 19 Mar 2019 07:21:33 +0000

Hello,

I have a tables called sales and I have created 12 (LIST) partititions for each of the business unit (id).

Now I want to add subpartitions on each of those partitions based on the year by using YEAR(report_date).

Below is the syntax I am trying

ALTER TABLE sales
MODIFY PARTITION sales_bu_p1
SUBPARTITION BY LIST (YEAR(report_date))
SUBPARTITIONS 4 (
PARTITION p2017 VALUES IN (2017),
PARTITION p2018 VALUES IN (2018),
PARTITION p2019 VALUES IN (2019),
PARTITION p2020 VALUES IN (2020)
);

But it is giving error (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use)

Please help me in correting the syntax if anything is wrong.

Thanks in advance.

Regards
Pandit.P

Partition Plugin Not Found (2 replies)

Luke Lang — Fri, 15 Feb 2019 14:32:21 +0000

I am running MySQL Community 8.0.15. When I run the command:

Select * From Information_schema.plugins;

...the plugin name "partition" does not appear on the list.

I'm running MySQL Community 5.6 on another server. It appears on that server.

How do I make it appear on my 8.0.15 instance? Is it possible? What do I not know?

Thank you in advance!

Partitioned table maintenance (no replies)

Pandit P — Mon, 01 Oct 2018 12:09:36 +0000

Hi,

I have these two maintenance scripts scheduled to run daily / weekly.

 





--Daily (except Sunday) 


mysqlcheck --user=root --password='*******' --fast --auto-repair --optimize --force --silent --skip-write-binlog --databases testdatabase > /root/DBSCRIPTS/Maintenance_Daily.log 





-- Weekly (only on Sunday) 


mysqlcheck --user=root --password='*******' --quick --auto-repair --optimize --force --silent --skip-write-binlog --databases testdatabase > /root/DBSCRIPTS/Maintenance_Weekly.log

Recently I have created partitions of two tables which has 5 millions of data in each table and it is expected to grow by 1 million records every quarter.

So far there is no plan to archive the old data.

I have few questions.

1. Is the above maintenance mysqlcheck script able to take care of optimizing the partitions/

2. If yes, will it be able to optimize the table even when the table size grows with billions of records?

3. If I need to separate the maintenance script only for the partitioned tables, how can I do it with mysqlcheck command?

4. If I skip "--auto-repair --optimize" and add only the optimize partitition(partition_name), it is not going to rebuild the indexes or defragment the data for the whole table.

Please help.

Thanks
Praveen

partitioning and compression efficiency (no replies)

George Develekos — Thu, 20 Sep 2018 13:22:20 +0000

Hello,

When I compress a table that is partitioned by linear hash, I get a substantial benefit, size ratio is 3.63. When I alter the table to partition it by key (which is what we prefer in this case) the benefit drops to a negligible ratio 1.17.

Is there an explanation for this?

Thanks,
George

using window functions with "partition by" can give incorrect results (no replies)

Witek Oleksiewicz — Tue, 03 Jul 2018 20:42:59 +0000

I have a problem with window functions in MySQL8 - they give incorrect results when applied to large tables (either: large number of rows or large number of columns).

Example: Table: bureau.csv (1.7 mln rows) from https://www.kaggle.com/c/home-credit-default-risk/data

I run 3 simple queries changing only the number of rows of the table to be used and number of columns to be outputed. You can clearly see that the combination of large amount of rows and many outputed columns gives incorrect results of "count(*) over()" - the last column.

More: https://stackoverflow.com/questions/51161778/mysql-8-window-function-wrong-results

how we can delete old data from hash partition ?? (no replies)

sabbirali kadiwala — Tue, 19 Jun 2018 10:07:55 +0000

I want to keep 2 years old data into below table, older data I want to move to archival tables...

I have one table with autoincrement id and daily 1 lakh records would be added into it.
but its huge table so it will take more time with help of delete query.

I have created a HASH partition on it with 24 partitions.

now daily or monthly I need to remove older than 2 years data from it, but its huge table so it will take more time with help of delete query.

can we delete partition like range or list so we can delete old partition and old data will be deleted from the table?

Please suggest how we can delete data ??