We plan to use TXSQL on our online ERP system because it has many features that we can use in real-world applications (query optimization, parallel queries, online column modification). However, after several rounds of testing, we found several problems when we tried to enable these new features.
Issue 1. Data accuracy issue: When parallel queries are enabled, some SQL queries produce different results compared to those without. The SQL statement that reproduces this issue is as follows: When parallel queries are enabled, the customer_id value is missing 1000, and instead, an additional value of 2505 is added.
DROP DATABASE IF EXISTS erp_dev1;
CREATE DATABASE erp_dev1;
USE erp_dev1;
CREATE TABLE campaigns (
campaign_id INT NOT NULL,
campaign_name VARCHAR(64) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
status VARCHAR(16) NOT NULL,
PRIMARY KEY(campaign_id)
) ENGINE=InnoDB;
CREATE TABLE orders (
order_id BIGINT NOT NULL,
customer_id INT NOT NULL,
status VARCHAR(16) NOT NULL,
created_at DATE NOT NULL,
total_amount DECIMAL(12,2) NOT NULL,
PRIMARY KEY(order_id),
KEY idx_customer(customer_id),
KEY idx_created(created_at)
) ENGINE=InnoDB;
INSERT INTO campaigns VALUES
(202601, '2026 New Year Sale', '2026-01-01', '2026-03-31', 'ACTIVE');
SET SESSION cte_max_recursion_depth=10000;
INSERT INTO orders(order_id, customer_id, status, created_at, total_amount)
WITH RECURSIVE seq(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT n AS order_id,
1 + ((n * 97) % 5000) AS customer_id,
CASE n % 5
WHEN 0 THEN 'PAID'
WHEN 1 THEN 'SHIPPED'
WHEN 2 THEN 'PAID'
WHEN 3 THEN 'NEW'
ELSE 'CANCELLED'
END AS status,
DATE_ADD('2026-01-01', INTERVAL (n % 90) DAY) AS created_at,
CAST(20 + ((n * 29) % 9000) / 10.0 AS DECIMAL(12,2)) AS total_amount
FROM seq;
ANALYZE TABLE campaigns, orders;
/* Close PX, reconnect, and then execute the query */
SET GLOBAL txsql_parallel_execution_enabled=OFF;
USE erp_dev1;
SELECT o.customer_id,
c.campaign_name,
COUNT(*) AS order_count,
ROUND(SUM(o.total_amount), 2) AS sales_amount
FROM orders o
JOIN campaigns c
ON c.campaign_id = 202601
AND c.status = 'ACTIVE'
WHERE o.created_at BETWEEN c.start_date AND c.end_date
GROUP BY o.customer_id, c.campaign_name
HAVING o.customer_id BETWEEN 1000 AND 1800
AND SUM(o.total_amount) > 1000
ORDER BY o.customer_id;
/* Enable PX, reconnect, and then execute the same query */
SET GLOBAL txsql_parallel_execution_enabled=ON;
SET GLOBAL txsql_parallel_table_record_threshold=0;
SET GLOBAL txsql_parallel_cost_threshold=0;
SET GLOBAL txsql_parallel_degree=4;
USE erp_dev1;
SELECT o.customer_id,
c.campaign_name,
COUNT(*) AS order_count,
ROUND(SUM(o.total_amount), 2) AS sales_amount
FROM orders o
JOIN campaigns c
ON c.campaign_id = 202601
AND c.status = 'ACTIVE'
WHERE o.created_at BETWEEN c.start_date AND c.end_date
GROUP BY o.customer_id, c.campaign_name
HAVING o.customer_id BETWEEN 1000 AND 1800
AND SUM(o.total_amount) > 1000
ORDER BY o.customer_id;
Issue 2. If field compression is enabled on the table, parallel queries will return ERROR 2013 (HY000): Lost connection to MySQL server during query. Checking the MySQL logs revealed a crash. The reproduced program is as follows:
DROP DATABASE IF EXISTS erp_dev1;
CREATE DATABASE erp_dev1;
USE erp_dev1;
CREATE TABLE order_service_notes (
note_id INT PRIMARY KEY,
order_id BIGINT NOT NULL,
order_note VARCHAR(255) COLUMN_FORMAT COMPRESSED,
attachment_digest VARBINARY(255) COLUMN_FORMAT COMPRESSED
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
INSERT INTO order_service_notes VALUES
(1, 10001, RPAD('refund service note', 255, 'A'), RPAD('x', 255, 'x')),
(2, 10002, RPAD('delivery service note', 255, 'B'), RPAD('y', 255, 'y'));
SET GLOBAL txsql_parallel_execution_enabled=ON;
SET GLOBAL txsql_parallel_table_record_threshold=0;
SET GLOBAL txsql_parallel_cost_threshold=0;
SET GLOBAL txsql_parallel_degree=4;
USE erp_dev1;
SELECT note_id,
order_id,
CHAR_LENGTH(order_note) AS note_length,
LENGTH(attachment_digest) AS digest_length
FROM order_service_notes
ORDER BY note_id;
Issue 3. In certain update scenarios, updates may fail, returning ERROR 2013 (HY000): Lost connection to MySQL server during query, and the MySQL program may crash. This issue occurred in our risk control system. The reproduction is as follows:
DROP DATABASE IF EXISTS erp_dev1;
CREATE DATABASE erp_dev1;
USE erp_dev1;
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
vip_level INT NOT NULL,
city_id INT,
KEY idx_vip(vip_level)
) ENGINE=InnoDB;
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
status VARCHAR(20) NOT NULL,
amount DECIMAL(12,2) NOT NULL,
created_at DATETIME,
KEY idx_customer(customer_id),
KEY idx_amount(amount)
) ENGINE=InnoDB;
INSERT INTO customers VALUES
(1, 1, 10),
(2, 2, 11),
(3, 0, 12),
(4, 3, 13);
INSERT INTO orders VALUES
(1, 1, 'PAID', 100.00, NOW()),
(2, 2, 'PAID', 200.00, NOW()),
(3, 3, 'PAID', 300.00, NOW()),
(4, 4, 'PAID', 400.00, NOW());
UPDATE orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
SET o.status = 'RISK_CHECKED'
WHERE c.vip_level >= 1
ORDER BY o.amount DESC
LIMIT 1;
Issue 4: Online index creation fails when innodb_disable_sort_file_cache=ON, the index is marked as corrupted, and it returns error 1712 - Index idx_sku_amount is corrupted. Reproduction is as follows:
DROP DATABASE IF EXISTS erp_dev1;
CREATE DATABASE erp_dev1;
USE erp_dev1;
SET GLOBAL innodb_disable_sort_file_cache = ON;
SET GLOBAL innodb_txsql_parallel_ddl = OFF;
SET SESSION txsql_parallel_copy_ddl = OFF;
CREATE TABLE n(n INT PRIMARY KEY);
INSERT INTO n VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE order_items (
item_id INT PRIMARY KEY,
customer_id INT NOT NULL,
sku_id INT NOT NULL,
amount INT NOT NULL,
pad VARCHAR(200),
KEY idx_customer(customer_id)
) ENGINE=InnoDB;
INSERT INTO order_items
SELECT a.n * 1000 + b.n * 100 + c.n * 10 + d.n + 1 AS item_id,
(a.n * 1000 + b.n * 100 + c.n * 10 + d.n) % 2000 AS customer_id,
(a.n * 1000 + b.n * 100 + c.n * 10 + d.n) % 500 AS sku_id,
(a.n * 1000 + b.n * 100 + c.n * 10 + d.n) % 10000 AS amount,
RPAD('order-item', 180, 'x') AS pad
FROM n a JOIN n b JOIN n c JOIN n d;
ANALYZE TABLE order_items;
ALTER TABLE order_items
ADD INDEX idx_sku_amount(sku_id, amount),
ALGORITHM=INPLACE;
We plan to use TXSQL on our online ERP system because it has many features that we can use in real-world applications (query optimization, parallel queries, online column modification). However, after several rounds of testing, we found several problems when we tried to enable these new features.
Issue 1. Data accuracy issue: When parallel queries are enabled, some SQL queries produce different results compared to those without. The SQL statement that reproduces this issue is as follows: When parallel queries are enabled, the
customer_idvalue is missing 1000, and instead, an additional value of 2505 is added.DROP DATABASE IF EXISTS erp_dev1;
CREATE DATABASE erp_dev1;
USE erp_dev1;
CREATE TABLE campaigns (
campaign_id INT NOT NULL,
campaign_name VARCHAR(64) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
status VARCHAR(16) NOT NULL,
PRIMARY KEY(campaign_id)
) ENGINE=InnoDB;
CREATE TABLE orders (
order_id BIGINT NOT NULL,
customer_id INT NOT NULL,
status VARCHAR(16) NOT NULL,
created_at DATE NOT NULL,
total_amount DECIMAL(12,2) NOT NULL,
PRIMARY KEY(order_id),
KEY idx_customer(customer_id),
KEY idx_created(created_at)
) ENGINE=InnoDB;
INSERT INTO campaigns VALUES
(202601, '2026 New Year Sale', '2026-01-01', '2026-03-31', 'ACTIVE');
SET SESSION cte_max_recursion_depth=10000;
INSERT INTO orders(order_id, customer_id, status, created_at, total_amount)
WITH RECURSIVE seq(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT n AS order_id,
1 + ((n * 97) % 5000) AS customer_id,
CASE n % 5
WHEN 0 THEN 'PAID'
WHEN 1 THEN 'SHIPPED'
WHEN 2 THEN 'PAID'
WHEN 3 THEN 'NEW'
ELSE 'CANCELLED'
END AS status,
DATE_ADD('2026-01-01', INTERVAL (n % 90) DAY) AS created_at,
CAST(20 + ((n * 29) % 9000) / 10.0 AS DECIMAL(12,2)) AS total_amount
FROM seq;
ANALYZE TABLE campaigns, orders;
/* Close PX, reconnect, and then execute the query */
SET GLOBAL txsql_parallel_execution_enabled=OFF;
USE erp_dev1;
SELECT o.customer_id,
c.campaign_name,
COUNT(*) AS order_count,
ROUND(SUM(o.total_amount), 2) AS sales_amount
FROM orders o
JOIN campaigns c
ON c.campaign_id = 202601
AND c.status = 'ACTIVE'
WHERE o.created_at BETWEEN c.start_date AND c.end_date
GROUP BY o.customer_id, c.campaign_name
HAVING o.customer_id BETWEEN 1000 AND 1800
AND SUM(o.total_amount) > 1000
ORDER BY o.customer_id;
/* Enable PX, reconnect, and then execute the same query */
SET GLOBAL txsql_parallel_execution_enabled=ON;
SET GLOBAL txsql_parallel_table_record_threshold=0;
SET GLOBAL txsql_parallel_cost_threshold=0;
SET GLOBAL txsql_parallel_degree=4;
USE erp_dev1;
SELECT o.customer_id,
c.campaign_name,
COUNT(*) AS order_count,
ROUND(SUM(o.total_amount), 2) AS sales_amount
FROM orders o
JOIN campaigns c
ON c.campaign_id = 202601
AND c.status = 'ACTIVE'
WHERE o.created_at BETWEEN c.start_date AND c.end_date
GROUP BY o.customer_id, c.campaign_name
HAVING o.customer_id BETWEEN 1000 AND 1800
AND SUM(o.total_amount) > 1000
ORDER BY o.customer_id;
Issue 2. If field compression is enabled on the table, parallel queries will return ERROR 2013 (HY000): Lost connection to MySQL server during query. Checking the MySQL logs revealed a crash. The reproduced program is as follows:
DROP DATABASE IF EXISTS erp_dev1;
CREATE DATABASE erp_dev1;
USE erp_dev1;
CREATE TABLE order_service_notes (
note_id INT PRIMARY KEY,
order_id BIGINT NOT NULL,
order_note VARCHAR(255) COLUMN_FORMAT COMPRESSED,
attachment_digest VARBINARY(255) COLUMN_FORMAT COMPRESSED
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
INSERT INTO order_service_notes VALUES
(1, 10001, RPAD('refund service note', 255, 'A'), RPAD('x', 255, 'x')),
(2, 10002, RPAD('delivery service note', 255, 'B'), RPAD('y', 255, 'y'));
SET GLOBAL txsql_parallel_execution_enabled=ON;
SET GLOBAL txsql_parallel_table_record_threshold=0;
SET GLOBAL txsql_parallel_cost_threshold=0;
SET GLOBAL txsql_parallel_degree=4;
USE erp_dev1;
SELECT note_id,
order_id,
CHAR_LENGTH(order_note) AS note_length,
LENGTH(attachment_digest) AS digest_length
FROM order_service_notes
ORDER BY note_id;
Issue 3. In certain update scenarios, updates may fail, returning ERROR 2013 (HY000): Lost connection to MySQL server during query, and the MySQL program may crash. This issue occurred in our risk control system. The reproduction is as follows:
DROP DATABASE IF EXISTS erp_dev1;
CREATE DATABASE erp_dev1;
USE erp_dev1;
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
vip_level INT NOT NULL,
city_id INT,
KEY idx_vip(vip_level)
) ENGINE=InnoDB;
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
status VARCHAR(20) NOT NULL,
amount DECIMAL(12,2) NOT NULL,
created_at DATETIME,
KEY idx_customer(customer_id),
KEY idx_amount(amount)
) ENGINE=InnoDB;
INSERT INTO customers VALUES
(1, 1, 10),
(2, 2, 11),
(3, 0, 12),
(4, 3, 13);
INSERT INTO orders VALUES
(1, 1, 'PAID', 100.00, NOW()),
(2, 2, 'PAID', 200.00, NOW()),
(3, 3, 'PAID', 300.00, NOW()),
(4, 4, 'PAID', 400.00, NOW());
UPDATE orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
SET o.status = 'RISK_CHECKED'
WHERE c.vip_level >= 1
ORDER BY o.amount DESC
LIMIT 1;
Issue 4: Online index creation fails when
innodb_disable_sort_file_cache=ON, the index is marked as corrupted, and it returns error 1712 - Index idx_sku_amount is corrupted. Reproduction is as follows:DROP DATABASE IF EXISTS erp_dev1;
CREATE DATABASE erp_dev1;
USE erp_dev1;
SET GLOBAL innodb_disable_sort_file_cache = ON;
SET GLOBAL innodb_txsql_parallel_ddl = OFF;
SET SESSION txsql_parallel_copy_ddl = OFF;
CREATE TABLE n(n INT PRIMARY KEY);
INSERT INTO n VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE order_items (
item_id INT PRIMARY KEY,
customer_id INT NOT NULL,
sku_id INT NOT NULL,
amount INT NOT NULL,
pad VARCHAR(200),
KEY idx_customer(customer_id)
) ENGINE=InnoDB;
INSERT INTO order_items
SELECT a.n * 1000 + b.n * 100 + c.n * 10 + d.n + 1 AS item_id,
(a.n * 1000 + b.n * 100 + c.n * 10 + d.n) % 2000 AS customer_id,
(a.n * 1000 + b.n * 100 + c.n * 10 + d.n) % 500 AS sku_id,
(a.n * 1000 + b.n * 100 + c.n * 10 + d.n) % 10000 AS amount,
RPAD('order-item', 180, 'x') AS pad
FROM n a JOIN n b JOIN n c JOIN n d;
ANALYZE TABLE order_items;
ALTER TABLE order_items
ADD INDEX idx_sku_amount(sku_id, amount),
ALGORITHM=INPLACE;