MySQL Forums - Quality Assurance

MySQL in Azure VM: Failure at high IOps (no replies)

Ludo Verstraeten — Tue, 18 Nov 2025 15:47:15 +0000

Hello,

We have an application which consists of multiple web services in IIS on a Windows machine with MySQL 8.4.6 LTS. Customers can run the application on-premises or in a Windows Azure VM. This is the reason we run MySQL in a VM instead of a managed service; to have only one deployment model.
We see now that when the Windows updates are run (which is done automatically on Azure) that the amount of IOps can exceed what the VHD supports; this seems to cause data stalling. This makes everything run slower (to be expected), but we see that MySQL gets the following error:

2025-11-04T22:07:52.621305Z 0 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2025-11-04T22:07:52.824534Z 0 [ERROR] [MY-012639] [InnoDB] Write to file .\#innodb_redo\#ib_redo104 failed at offset 2802688, 512 bytes should have been written, only 0 were written. Operating system error number 0. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2025-11-04T22:07:52.828597Z 0 [ERROR] [MY-012640] [InnoDB] Error number 0 means 'No error'
2025-11-04T22:07:52.875391Z 0 [ERROR] [MY-013864] [InnoDB] [FATAL] Error 100 encountered when writing to the redo log file: .\#innodb_redo\#ib_redo104.
2025-11-04T22:07:52.916697Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: log0write.cc:2118:ib::fatal triggered thread 6516
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.4/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2025-11-04T22:07:52Z UTC - mysqld got exception 0x16 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff7e466fd08 mysqld.exe!?my_print_stacktrace@@YAXPEBEK@Z()
7ff7e382b1d1 mysqld.exe!?my_server_abort@@YAXXZ()
7ff7e382af13 mysqld.exe!?my_server_abort@@YAXXZ()
7ff7e465a57a mysqld.exe!?my_abort@@YAXXZ()
7ff7e4830459 mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff7e4783ab3 mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff7e495315f mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff7e4952ec9 mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff7e495198d mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff7e47b30a4 mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ff7e47b32f9 mysqld.exe!?zstd_mem_res_free@Zstd_dec@compression@event@binlog@mysql@@CAXPEAX0@Z()
7ffe5b58268a ucrtbase.dll!_o_exp()
7ffe5bff7ac4 KERNEL32.DLL!BaseThreadInitThunk()
7ffe5edfa8c1 ntdll.dll!RtlUserThreadStart()

And the mysql service stops and needs manual action to resume.

Our question:
Are there settings that can prevent this from happening? Note that the high IOps moment is only for a certain amount of time, so is there a way to make MySQL more durable during these times? We can accept transient errors, since we have retry-policies, but when the service stops, we need a manual action.

Thanks in advance.

Best Practices for Ensuring MySQL Database Integrity in Quality Assurance Testing (no replies)

James james — Fri, 09 May 2025 06:27:18 +0000

Hello MySQL community,

As MySQL continues to be a cornerstone in many data-driven applications, ensuring database integrity during quality assurance (QA) testing is crucial for maintaining performance, security, and reliability.

I’m looking for insights on the most effective practices for conducting thorough QA testing on MySQL databases, specifically focusing on:

Data Integrity: How do you verify the accuracy and consistency of data during various test phases? What tools or strategies help catch anomalies early in the testing cycle?

Performance Testing: What are the best approaches for testing database performance under heavy loads? How do you simulate real-world traffic to ensure scalability without affecting production systems?

Automated Testing: Have you integrated automated test suites for MySQL? Which frameworks or tools do you find most effective for running continuous integration tests on your database?

Backup and Recovery: How do you test MySQL’s backup and recovery processes to ensure that data can be restored correctly under various failure scenarios?

I’m eager to hear about the challenges you’ve faced in QA testing with MySQL and any lessons learned. What steps do you take to ensure a reliable and efficient database system?

Looking forward to your responses!

Patches for CVE-2024-21090 and CVE-2024-21170 (no replies)

Daniel Leidert — Sun, 29 Sep 2024 17:19:25 +0000

I'm trying to identify the patches for CVE-2024-21090 and CVE-2024-21170. The former was fixed in release 8.4.0 of the Python version of the connector. The latter in version 9.0.0. None of the CVEs gives much indication of what the real problem was and the commits don't mention the CVEs. The patches I spotted that might be related are

https://github.com/mysql/mysql-connector-python/commit/f3e285e78945d53405698c1077251e042121dfe5 (CVE-2024-21090)

https://github.com/mysql/mysql-connector-python/commit/1e799824c308f9ba4a22f0f4a9b340419b6b0f22 (CVE-2024-21170)

I'd appreciate it if anybody could verify that these are indeed the patches for the mentioned vulnerabilities.

MySQL Shutdown unexpectedly (no replies)

Giovanni Marchesi — Thu, 09 May 2024 23:15:49 +0000

Hello to all, i'm completely new to MySQL, and so i kindly ask you if you can help me with a problem. One moment it works fine, but the second time i open it doesn't open! I'm having trouble identifying the problem, so i thought maybe in the forums someone could help me out.

2024-05-09 18:00:32 0 [Note] InnoDB: File 'C:\xampp\mysql\data\ibtmp1' size is now 12 MB.
2024-05-09 18:00:32 0 [Note] InnoDB: Waiting for purge to start
2024-05-09 18:00:32 0 [Note] InnoDB: 10.4.32 started; log sequence number 8697900; transaction id 4526
2024-05-09 18:00:32 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
2024-05-09 18:00:32 0 [Note] InnoDB: Buffer pool(s) load completed at 240509 18:00:32
2024-05-09 18:00:32 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-05-09 18:00:32 0 [Note] Server socket created on IP: '::'.
2024-05-09 23:20:52 0 [Note] Starting MariaDB 10.4.32-MariaDB source revision c4143f909528e3fab0677a28631d10389354c491 as process 18592
Cannot find checkpoint record at LSN (1,0x5556)
2024-05-09 23:20:52 0 [ERROR] mysqld.exe: Aria recovery failed. Please run aria_chk -r on all Aria tables and delete all aria_log.######## files
2024-05-09 23:20:52 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
2024-05-09 23:20:52 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2024-05-09 23:20:52 0 [Note] InnoDB: Uses event mutexes
2024-05-09 23:20:52 0 [Note] InnoDB: Compressed tables use zlib 1.3
2024-05-09 23:20:52 0 [Note] InnoDB: Number of pools: 1
2024-05-09 23:20:52 0 [Note] InnoDB: Using SSE2 crc32 instructions
2024-05-09 23:20:52 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2024-05-09 23:20:52 0 [Note] InnoDB: Completed initialization of buffer pool
2024-05-09 23:20:52 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=13338595
2024-05-09 23:20:53 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2024-05-09 23:20:53 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2024-05-09 23:20:53 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2024-05-09 23:20:53 0 [Note] InnoDB: Setting file 'C:\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-05-09 23:20:53 0 [Note] InnoDB: File 'C:\xampp\mysql\data\ibtmp1' size is now 12 MB.
2024-05-09 23:20:53 0 [Note] InnoDB: Waiting for purge to start
2024-05-09 23:20:53 0 [Note] InnoDB: 10.4.32 started; log sequence number 13338604; transaction id 6870
2024-05-09 23:20:53 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
2024-05-09 23:20:53 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-05-09 23:20:53 0 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
2024-05-09 23:20:53 0 [ERROR] Failed to initialize plugins.
2024-05-09 23:20:53 0 [ERROR] Aborting
2024-05-09 23:21:22 0 [Note] Starting MariaDB 10.4.32-MariaDB source revision c4143f909528e3fab0677a28631d10389354c491 as process 29728
Cannot find checkpoint record at LSN (1,0x5556)
2024-05-09 23:21:22 0 [ERROR] mysqld.exe: Aria recovery failed. Please run aria_chk -r on all Aria tables and delete all aria_log.######## files
2024-05-09 23:21:22 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
2024-05-09 23:21:22 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2024-05-09 23:21:22 0 [Note] InnoDB: Uses event mutexes
2024-05-09 23:21:22 0 [Note] InnoDB: Compressed tables use zlib 1.3
2024-05-09 23:21:22 0 [Note] InnoDB: Number of pools: 1
2024-05-09 23:21:22 0 [Note] InnoDB: Using SSE2 crc32 instructions
2024-05-09 23:21:22 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2024-05-09 23:21:22 0 [Note] InnoDB: Completed initialization of buffer pool
2024-05-09 23:21:22 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2024-05-09 23:21:22 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2024-05-09 23:21:22 0 [Note] InnoDB: Setting file 'C:\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-05-09 23:21:22 0 [Note] InnoDB: File 'C:\xampp\mysql\data\ibtmp1' size is now 12 MB.
2024-05-09 23:21:22 0 [Note] InnoDB: Waiting for purge to start
2024-05-09 23:21:22 0 [Note] InnoDB: 10.4.32 started; log sequence number 13338613; transaction id 6870
2024-05-09 23:21:22 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
2024-05-09 23:21:22 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-05-09 23:21:22 0 [Note] InnoDB: Buffer pool(s) load completed at 240509 23:21:22
2024-05-09 23:21:22 0 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
2024-05-09 23:21:22 0 [ERROR] Failed to initialize plugins.
2024-05-09 23:21:22 0 [ERROR] Aborting
2024-05-09 23:21:28 0 [Note] Starting MariaDB 10.4.32-MariaDB source revision c4143f909528e3fab0677a28631d10389354c491 as process 36036
Cannot find checkpoint record at LSN (1,0x5556)

I will appreciate any help you can provide, thank you all.

Permissions required for ER_NO_REFERENCED_ROW_2. Key violation messages (1 reply)

Brendon Abbott — Fri, 19 Apr 2024 05:43:34 +0000

I am currently porting a project to mysql 8.0 from mysql 5.7. This project takes advantage of extracting the table/column name information in ERR_NO_REFERENCED_ROW_2 - e.g. ERROR 1451 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`joinit`, CONSTRAINT `joinit_ibfk_1` FOREIGN KEY (`g`) REFERENCES `joinit_fk` (`i`) ON DELETE RESTRICT ON UPDATE CASCADE)

WL#8910 changed the behaviour in Mysql8 to tighten up security and require permissions on the relevant tables. This perfectly makes sense so far.

It seems that CREATE/ALTER/DROP + more are required.
On the system I am working on, there are essentially 2 users - which I am guessing is relatively common basic setup. An "admin" user has permission for to issue DDL (and DML) operations, to manage the schema and data. The day to day application accesses with only "DML" operations (INSERT/UPDATE/DELETE) for the schema. There are no specific table or column level permissions for either user. The basic user account, is able to run "SHOW CREATE TABLE..." on both tables to dump out the full DDL with constraints anyway.

I am wondering if someone could explain to me, why the permissions required are so high? I am presuming there is a good reason that I am not grasping. This was already logged as a bug by someone else, https://bugs.mysql.com/bug.php?id=112589 - however was ultimately closed as a documentation problem.

Thanks for any pointers you can give as to the reason.

Inquiry Regarding Initial Seed for MySQL Protocol Fuzzing (no replies)

cherry Pang — Thu, 23 Nov 2023 06:31:59 +0000

I am a novice enthusiast in the field of fuzz testing and have an interest in conducting fuzz testing for the MySQL protocol. I understand that testing the logic bugs or crash bugs in Data Manipulation Language (DML) functionalities differs significantly, and as such, I am unsure about the initial seed format required.

From my understanding, the seed for protocol testing refers to queries or commands sent to the database server. How does this differ from seeds used in regular logic testing? I would greatly appreciate it if you could shed some light on the format of the initial seed for this purpose. If possible, providing a few seed examples would be immensely helpful.

Thank you very much for your time and assistance.

Best regards~

net_write_timeout value adjustment (no replies)

Nikhil Karande — Thu, 03 Aug 2023 08:02:09 +0000

Hi Team,

one of our application recently faced the below exception

** BEGIN NESTED EXCEPTION **
java.io.EOFException
MESSAGE: Can not read response from server. Expected to read 63 bytes, read 51 bytes before connection was unexpectedly lost.
STACKTRACE:
java.io.EOFException: Can not read response from server. Expected to read 63 bytes, read 51 bytes before connection was unexpectedly lost.
at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:67)

After changing net_write_timeout default value from 60 to 180 resolves the issue.

Please let us know whether any other performance related impact will happen or it is safe to use 180.

About Data Export (no replies)

WU SHAN-HUNG — Fri, 05 May 2023 12:34:52 +0000

My Data Export function is not working. The check box below has disappeared. What should I do to handle this?

Cannot Report Bug (no replies)

John G — Fri, 10 Feb 2023 15:21:11 +0000

https://bugs.mysql.com/report.php?

I cannot report a bug through the above webpage. I was trying to submit one for EXCEPT syntax. WB marks it as invalid even though it runs and I cannot save a SP that contains EXCEPT syntax.

You don't have permission to access "http://bugs.mysql.com/report.php"; on this server.
Reference #18.6e3b2f17.1676042400.1c3f9c36
Workbench is Windows(build 2612062 64bit . Server is on Ubuntu ('8.0.32-0ubuntu0.22.04.2')

EXCEPT Syntax
https://dev.mysql.com/doc/refman/8.0/en/except.html

John

Use JDBC to query the trigger error (1 reply)

jiacheng liang — Sat, 20 May 2023 20:03:41 +0000

1. environment：
mysql5.7.39-log、JDBC8.0.28

2. steps
CREATE TABLE LSP.TEST_TRIGGLE_UPPER (
ID INT PRIMARY KEY AUTO_INCREMENT,
T_NOTE VARCHAR(30)
);

CREATE TABLE LSP.TEST_TRIGGLE_LOG (
ID INT PRIMARY KEY AUTO_INCREMENT,
T_LOG VARCHAR(30)
);
CREATE TRIGGER LSP.TRI_BEFORE_INSERT BEFORE INSERT ON LSP.TEST_TRIGGLE_UPPER
FOR EACH ROW
INSERT INTO TEST_TRIGGLE_LOG(T_LOG) VALUES('BEFORE insert.....');

SELECT TRIGGER_CATALOG,TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_MANIPULATION,EVENT_OBJECT_CATALOG,EVENT_OBJECT_SCHEMA,EVENT_OBJECT_TABLE,ACTION_STATEMENT,ACTION_ORIENTATION,ACTION_TIMING FROM information_schema.triggers WHERE TRIGGER_SCHEMA = 'LSP' and TRIGGER_NAME = 'tri_before_insert';

3. actual results
def,LSP,TRI_BEFORE_INSERT,INSERT,def,LSP,TEST_TRIGGLE_UPPER,INSERT INTO TEST_TRIGGLE_LOG(T_LOG) VALUES('BEFORE insert.....'),ROW,BEFORE
def,LSP,tri_before_insert,INSERT,def,LSP,test_trigger,INSERT INTO test_trigger_log(t_log) VALUES('before insert.....'),ROW,BEFORE

4. expected outcome

def,LSP,tri_before_insert,INSERT,def,LSP,test_trigger,INSERT INTO test_trigger_log(t_log) VALUES('before insert.....'),ROW,BEFORE

trouble with gpg again (2 replies)

megatron from space — Mon, 24 Apr 2023 10:04:41 +0000

Hi all-

I went through all the steps to verify gpg. Nothing worked. Your keyserver doesn't respond to recv--keys. Please let someone know!

gpg --verify mysql-workbench-community_8.0.30-1ubuntu22.04_amd64.deb.asc mysql-workbench-community_8.0.30-1ubuntu22.04_amd64.deb
gpg: Signature made Tue 05 Jul 2022 09:57:25 AM EDT
gpg: using RSA key 859BE8D7C586F538430B19C2467B942D3A79BD29
gpg: Can't check signature: No public key

gpg --import mysql-workbench-community_8.0.30-1ubuntu22.04_amd64.deb.asc
gpg: no valid OpenPGP data found.
gpg: Total number processed: 0

gpg --recv-keys 3A79BD29
gpg: keyserver receive failed: Server indicated a failure

hkp://keys.gnupg.net is not working

THANKS

website signing key question (no replies)

megatron from space — Mon, 28 Mar 2022 00:55:36 +0000

Hi -

the signing keys for mysql 8 download .msi for windows files won't import into kleopatra or msys2. Do you support signing keys anymore or are they obsolete? The md5 signatures are correct. It is for the community version download of mysql.

thanks .

roboloki@yahoo.com

Database can not be shut down properly (1 reply)

Masahito Kubota — Mon, 21 Feb 2022 04:04:31 +0000

Hello.

In our system we are using MariaDB (Mysql) as the database for Zabbix server.
The Zabbix server is in a cluster, with two servers configured as Active/Standby.
The other day, I restarted the Corosync service on the active server using systemctl command to change the Corosync configuration.
When the Corosync service was restarted, each software cluster resource was automatically restarted and the Zabbix service was restored without any problem.
We assumed that when the Corosync service was restarted, the software that serves as each cluster resource would be restarted automatically and the Zabbix service would be restored without any problems.
In the development environment, the result was as expected, but in the production environment, an error occurred in the database and the
The database could not be shut down properly and a crash recovery occurred.
When the failure occurred, the following log was output on the Active server.
The same log was also output on the Standby server.

211122 12:19:27 [Note] /usr/libexec/mysqld: Normal shutdown
211122 12:19:27 [Note] Event Scheduler: Purging the queue. 0 events
211122 12:19:27 InnoDB: Starting shutdown...
211122 12:19:27 InnoDB: Waiting for XXX pages to be flushed
211122 12:20:07 InnoDB: The InnoDB memory heap is disabled
211122 12:20:07 InnoDB: Mutexes and rw_locks use GCC atomic builtins
211122 12:20:07 InnoDB: Compressed tables use zlib 1.2.7
211122 12:20:07 InnoDB: Using Linux native AIO
211122 12:20:07 InnoDB: Initializing buffer pool, size = X.XG
211122 12:20:08 InnoDB: Completed initialization of buffer pool
211122 12:20:08 InnoDB: highest supported file format is Barracuda.
211122 12:20:08 InnoDB: Database was not shut down normally!
211122 12:20:20 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 211122 12:25:00 InnoDB: The InnoDB memory heap is disabled

The versions of each software are as follows
Red Hat Enterprise Linux release 7.2 (Maipo)
mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1
Pacemaker 1.1.17-1.el7
Corosync Cluster Engine, version '2.4.2'.

From the above information, can you figure out what is causing the problem?
Please let me know if there are any other logs or settings I need to check.

the bug of innodb next-key locks (no replies)

da DA — Mon, 14 Feb 2022 07:24:58 +0000

mysql version 8.0.27, isolation level RR

CREATE TABLE `user`
(
`id` int(0) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`age` int(0) NULL DEFAULT NULL,
`value` int(0) NOT NULL,
`uni` bigint(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `value`(`value`) USING BTREE,
UNIQUE INDEX `uni`(`uni`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

records

INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`) VALUES (440, 'Ed Venture', 57, 50, 76);
INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`) VALUES (514, 'Justin Casey Howells', 77, 17, 32);
INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`) VALUES (626, 'Dee Kay', 18, 3, 60);
INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`) VALUES (839, 'Bjorn Free', 75, 61, 80);
INSERT INTO `user`(`id`, `name`, `age`, `value`, `uni`) VALUES (880, 'Barb Dwyer', 70, 42, 52);

sql1:

start transaction;
select * from user where value <= 17 for update;

sql2:

start transaction;
select id from user where value <= 17 for update;

but, sql2 has one more than than sql1 an index record lock.

performance.schema#data_locks
lock_data: 880
lock_mode: X,REC_NOT_GAP
index_name: PRIMARY

FORCE INDEX doesn't take effect when set jdbc param `useServerPrepStmts=true` (no replies)

chen yi — Fri, 21 Jan 2022 04:59:07 +0000

Details:

mysql version: Server version: 8.0.25 MySQL Community Server - GPL
jdbc connector version: mysql-connector-java-8.0.25

reproduce:
step 1: create table:

CREATE DATABASE PRICING;
USE PRICING:
CREATE TABLE `QUOTE` (
`ID` BIGINT NOT NULL,
`GROUP_ID` SMALLINT NOT NULL,
`SYMBOL_ID` SMALLINT NOT NULL,
`SOURCE_ID` BIGINT NULL DEFAULT NULL,
`PRODUCT_ID` INT NOT NULL,
`COMPANY_ID` TINYINT NOT NULL,
`LOW` DECIMAL(15,6) NOT NULL,
`HIGH` DECIMAL(15,6) NOT NULL,
`OPEN` DECIMAL(15,6) NOT NULL,
`SCALE` TINYINT NOT NULL,
`STATUS` TINYINT NOT NULL,
`LADDERED` TINYINT NOT NULL,
`DEVIATION` DECIMAL(15,6) NOT NULL,
`BID_PRICE` DECIMAL(15,6) NOT NULL,
`ASK_PRICE` DECIMAL(15,6) NOT NULL,
`QUOTE_DATE` DATE NOT NULL,
`QUOTE_TIME` BIGINT NOT NULL,
`BID_TRADABLE` TINYINT NOT NULL,
`ASK_TRADABLE` TINYINT NOT NULL,
`BID_ADJUSTMENT` DECIMAL(15,6) NOT NULL,
`ASK_ADJUSTMENT` DECIMAL(15,6) NOT NULL,
`VERSION` INT NOT NULL DEFAULT '1',
`INSERT_DATETIME` TIMESTAMP NULL DEFAULT NULL,
`UPDATE_DATETIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
INDEX `INDEX_01` (`SOURCE_ID`),
INDEX `INDEX_02` (`PRODUCT_ID`),
INDEX `INDEX_03` (`PRODUCT_ID`, `QUOTE_DATE`),
INDEX `INDEX_04` (`PRODUCT_ID`, `QUOTE_TIME`)
)
ENGINE=InnoDB;

step 2: insert 10,000,000 records to above table. `PRODUCT_ID` 's cardinality is 200.

step 3: use following java code to access above table

public static void main(String[] args) throws SQLException {
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/PRICING?useServerPrepStmts=true", "user", "pass")) {
try (PreparedStatement ps = conn.prepareStatement("select * from QUOTE FORCE INDEX(INDEX_02) where PRODUCT_ID = ? order by ID desc limit 512")) {
ps.setInt(1, 10);
try (ResultSet rs = ps.executeQuery()) {
int count = 0;
while (rs.next()) {
count++;
}
System.out.println("count:" + count);
}
}
}
}

we expect above sql to use INDEX_02, key = INDEX_02, Extra = Backward index scan

above code takes very long time to fetch result. if we changed `useServerPrepStmts=true` to `false` or use `Statement` instead of `PreparedStatement` or replace `FORCE INDEX` to `FORCE INDEX FOR JOIN`. it's fast as normal.

mysql execution Plan:

mysql> EXPLAIN select * from QUOTE FORCE INDEX(INDEX_02) where PRODUCT_ID = 1 order by ID desc limit 512;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+---------------------+
| 1 | SIMPLE | QUOTE | NULL | ref | INDEX_02 | INDEX_02 | 4 | const | 1 | 100.00 | Backward index scan |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)

MATCH AGAINST doesn't support combined words (1 reply)

David Allen — Mon, 06 Dec 2021 18:36:39 +0000

My understanding is MATCH is supposed to support phrasing by putting quotes (") around the text, e.g.

MATCH(table.keyword) AGAINST @large_text
OR
MATCH(CONCAT('"',table.keyword,'"') AGAINST @large_text

Where table.keyword may be "Software Engineer".

Regardless of putting quotes are the "words", MATCH will not match the combined words, it always matches the single words separated by spacing.

I believe this is a bug...?

"You do not have access to bug #104771." (1 reply)

Ralf Meyer — Thu, 09 Sep 2021 16:51:05 +0000

Dear MySQL-Community,

I have found bug #104771 via Google (where it still can be found and also seen in Google Cache). We are affected by this exact bug. However, I cannot access it anymore, as I just get the message: "You do not have access to bug #104771.", even when I am logged in.

Can you tell me why the bug is set to private (I suppose)? Is it somehow possible to track the progress on this bug if I cannot see it?

Thank you for your help.

Kind regards

Ralf

5.7.34 broke compatabiliity with mariadb audit plugin (9 replies)

Carl Laserdog — Fri, 03 Jun 2022 14:46:39 +0000

So mariadb's server_audit.so plugin works on MySQL Version 5.7.9 - 5.7.33.

It is now broken on 5.7.34.

Possibly related to either of these Changes:

* Sessions could disable their own auditing. (Bug #31630954)

* Uninstalling a plugin could affect subsequent execution of prepared statements. (Bug #29363867)

I tried opening a bug with mysql, and it was closed as it says they don't support mariadb plugins.

Is it possible to get more information on the above 2x commits (e.g. actual patch diffs?) so I can investigate the issue myself?

Specifically strange to me that "auditing" is even a change that made it into mysql-community given its totally lack of audit logging plugin.

I want to give the devs the benefit of the doubt here, but breaking one of the main compliance plugins that mysql-enterprise just happens to keep behind a paywall seems suspicious.

If 3rd party plugins need to make changes after 5.7.34, that's fine, just need some help finding what changes are needed....

Again, just asking for the commit diffs of the two above changes, as I can't seem to find them myself based on those bug ids anywhere.

If you are interested in details on the mariadb server_audit.so crash, here they are:

-----------

mysqld starts up fine with mariadb server_audit.so loaded.

But all connection attempts (even ones with invalid users/pass) result in:

$ mysql
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 95

And on the server side, a signal 11:

15:38:38 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=1048576
read_buffer_size=262144
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 51365 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f21a0000d40
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f223014be50 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xf3461b]
/usr/sbin/mysqld(handle_fatal_signal+0x486)[0x7e3666]
/lib64/libpthread.so.0(+0x117e0)[0x7f2237ef07e0]
/usr/lib64/mysql/plugin/server_audit.so(get_db_mysql57+0x30)[0x7f21d452e110]
/usr/lib64/mysql/plugin/server_audit.so(+0xb21b)[0x7f21d452e21b]
/usr/sbin/mysqld[0x7e435c]
/usr/sbin/mysqld(_Z18mysql_audit_notifyP3THD30mysql_event_general_subclass_tPKciS3_m+0x25d)[0x7e491d]
/usr/sbin/mysqld(_ZN12Query_logger17general_log_printEP3THD19enum_server_commandPKcz+0xcc)[0xc411bc]
/usr/sbin/mysqld(_Z15acl_log_connectPKcS0_S0_S0_P3THD19enum_server_command+0xaf)[0x7ed45f]
/usr/sbin/mysqld(_Z16acl_authenticateP3THD19enum_server_command+0x6c7)[0x7ee437]
/usr/sbin/mysqld[0xcb994d]
/usr/sbin/mysqld(_Z22thd_prepare_connectionP3THD+0x68)[0xcba9d8]
/usr/sbin/mysqld(handle_connection+0x267)[0xdc1337]
/usr/sbin/mysqld(pfs_spawn_thread+0x1b4)[0x1409ee4]
/lib64/libpthread.so.0(+0x740b)[0x7f2237ee640b]
/lib64/libc.so.6(clone+0x3f)[0x7f22368f909f]

Thanks for reading!

sysbench test show that 8.0.21 is faster than 8.0.23 (no replies)

Liu Jack — Fri, 09 Apr 2021 08:12:06 +0000

The sysbench (1.0.20) test command was

sysbench --test=oltp_read_write --tables=10 --table-size=1000000 --report-interval=10 --time=3600 --threads=50 --db-driver=mysql --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password='xxx' --mysql-db=sbtest run

============= 8.0.21 test result =============

SQL statistics:
queries performed:
read: 15339058
write: 4382588
other: 2191294
total: 21912940
transactions: 1095647 (304.28 per sec.)
queries: 21912940 (6085.57 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 3600.8045s
total number of events: 1095647

Latency (ms):
min: 2.74
avg: 164.30
max: 5033.06
95th percentile: 707.07
sum: 180014353.79

Threads fairness:
events (avg/stddev): 21912.9400/80.08
execution time (avg/stddev): 3600.2871/0.22

============= 8.0.23 test reuslt ================

SQL statistics:
queries performed:
read: 2969064
write: 848304
other: 424152
total: 4241520
transactions: 212076 (58.89 per sec.)
queries: 4241520 (1177.79 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 3601.2587s
total number of events: 212076

Latency (ms):
min: 3.57
avg: 848.89
max: 6098.82
95th percentile: 1973.38
sum: 180029441.14

Threads fairness:
events (avg/stddev): 4241.5200/32.35
execution time (avg/stddev): 3600.5888/0.34

Both tests are performed on the same machine.
Are there any performance issue with 8.0.23?

Only available after end of query execute (no replies)

Wh Ooo — Sun, 28 Mar 2021 23:45:37 +0000

mysqlx::Table table{ schema, "accounts" };

auto tableSelect = table.select("Id");
auto rowResult = tableSelect.execute();

try
{
std::cout << rowResult.getAffectedItemsCount() << std::endl;
}
catch (const mysqlx::Error& error)
{
std::cout << error.what() << std::endl;
}

Returns an error:

CDK Error: Only available after end of query execute

Does it have to be like this?

mysql-test-run: *** ERROR: Could not find "mysqld" in scanned directories (2 replies)

Pavan Sai — Tue, 30 Mar 2021 15:10:36 +0000

While running the mysql-test-run.pl script, I am getting the below error:

mysql-test-run: *** ERROR: Could not find 'mysqld' in scanned directories

There were no spaces in the path to mysql-test folder.
I have connected to my server instance using workbench tool. 'mysqld' is not available in the mysql-debug folder, instead there is mysqld-debug.exe. Am I missing anything here?

GnuCash not opening mysql database on Windows 10 (1 reply)

Christie Wagner — Mon, 18 Jan 2021 01:43:07 +0000

I have a mysql server on my Ubuntu 20.04 server on a local network as the backend for my GnuCash installation. I can connect with no trouble using an SSH tunnel from my Ubuntu 20.04 desktop. However, I have recently acquired a Windows 10 laptop, and I cannot connect. I have been able to verify that the tunnel is operational, as I can pull up the database in MySQL Workbench on my Windows machine. However, GnuCash always reports 'The server at URL tellpizzahut mysql://[db_user]@127.0.0.1:[local_port]/[database_name] experienced an error or encountered bad or corrupt data.' I have tried using 'localhost' instead of 127.0.0.1 with the same result. I also

about function replace (7 replies)

Peter Gu — Mon, 20 Dec 2021 17:56:37 +0000

Mysql version 5.7

select "TEST",replace("TEST"," ", null) from dual;
result:
TEST null

select "TEST",replace("TEST"," ", "OTHER") from dual;
result:
TEST TEST

Is this a bug?

IF NOT EXISTS() THEN (1 reply)

matthew Baynham — Sun, 11 Oct 2020 16:04:58 +0000

Bug report.

I've written a stored procedure and it crashed but it didn't give any error message.

I've copied my stored procedure below.

I've also copied the code to run my stored procedure.

I've found out that if I comment out the if statements that contain "IF NOT EXISTS () THEN" then my stored procedure will run. And if I just remove the word "NOT" then it'll run.

Maybe the use of the word "NOT" was incorrect, however I would expect an error message.

I now know how to rewrite my stored procedure but I am still annoyed that I wasn't given an error message telling me that the use of the word "not" was wrong.

In fact there was no error message at all, the only thing that happened was that the stored procedure didn't give me any data at the end. It just crashed out without getting to the end. And it didn't say it crashed.

Can we have better error reporting please, it saves so much stress.

------------------------------------------------------------------------------------------
My Stored procedure

use db_settings;

DROP PROCEDURE if exists insertProduct;

DELIMITER //

CREATE PROCEDURE insertProduct(OUT p_bIsOk boolean,
OUT p_prd_product_id int,
IN p_language_id int,
IN p_prd_model varchar(64),
IN p_prd_sku varchar(64),
IN p_prd_upc varchar(12),
IN p_prd_ean varchar(14),
IN p_prd_jan varchar(13),
IN p_prd_isbn varchar(17),
IN p_prd_mpn varchar(64),
IN p_prd_location varchar(128),
IN p_prd_quantity int,
IN p_prd_stock_status_id int,
IN p_prd_image varchar(255),
IN p_prd_manufacturer_id int,
IN p_prd_shipping tinyint,
IN p_prd_price decimal(15,4),
IN p_prd_points int,
IN p_prd_tax_class_id int,
IN p_prd_date_available date,
IN p_prd_weight decimal(15,8),
IN p_prd_weight_class_id int,
IN p_prd_length decimal(15,8),
IN p_prd_width decimal(15,8),
IN p_prd_height decimal(15,8),
IN p_prd_length_class_id int,
IN p_prd_subtract tinyint,
IN p_prd_minimum int,
IN p_prd_sort_order int,
IN p_prd_status tinyint,
IN p_desc_name varchar(255),
IN p_desc_description text,
IN p_desc_tag text,
IN p_desc_meta_title varchar(255) ,
IN p_desc_meta_description varchar(255),
IN p_desc_meta_keyword varchar(255),
IN p_Concat_image varchar(4000),
IN p_Concat_image_sort_order varchar(4000),
IN p_flag_new_language boolean,
IN p_flag_overwrite_product boolean,
IN p_flag_overwrite_description boolean,
IN p_flag_remove_old_images boolean
)
BEGIN

declare sDelimiterTab varchar(10) default "-";
declare iCounter int;
declare iPosA int;
declare iPosB int;
declare sTemp varchar(4000) default "";
declare bIsFinished boolean default false;

set p_bIsOk = true;

/************************************
* Drop and recreate temp tables *
************************************/
DROP TABLE IF EXISTS temp_errors;
DROP TABLE IF EXISTS temp_status;
DROP TABLE IF EXISTS temp_product;
DROP TABLE IF EXISTS temp_product_image;
DROP TABLE IF EXISTS temp_images;

CREATE TEMPORARY TABLE temp_errors (
err_ID INT AUTO_INCREMENT PRIMARY KEY,
err_Category varchar(1024) not null default '',
err_Name varchar(1024) not null default '',
err_Long_Description varchar(1024) not null default '',
err_Values varchar(1024) not null default ''
);

CREATE TEMPORARY TABLE temp_status (
tmpSt_ID INT AUTO_INCREMENT PRIMARY KEY,
tmpSt_Category varchar(1024) not null default '',
tmpSt_Name varchar(1024) not null default '',
tmpSt_Long_Description varchar(1024) not null default '',
tmpSt_Values varchar(1024) not null default ''
);

CREATE TEMPORARY TABLE temp_product (
tmpP_id INT AUTO_INCREMENT PRIMARY KEY,
tmpP_product_id int NOT NULL,
tmpP_model varchar(64) NOT NULL,
tmpP_sku varchar(64) NOT NULL,
tmpP_upc varchar(12) NOT NULL,
tmpP_ean varchar(14) NOT NULL,
tmpP_jan varchar(13) NOT NULL,
tmpP_isbn varchar(17) NOT NULL,
tmpP_mpn varchar(64) NOT NULL,
tmpP_location varchar(128) NOT NULL,
tmpP_quantity int NOT NULL DEFAULT '0',
tmpP_stock_status_id int NOT NULL,
tmpP_image varchar(255) DEFAULT NULL,
tmpP_manufacturer_id int NOT NULL,
tmpP_shipping tinyint NOT NULL DEFAULT '1',
tmpP_price decimal(15,4) NOT NULL DEFAULT '0.0000',
tmpP_points int NOT NULL DEFAULT '0',
tmpP_tax_class_id int NOT NULL,
tmpP_date_available date NOT NULL DEFAULT '2020-01-01',
tmpP_weight decimal(15,8) NOT NULL DEFAULT '0.00000000',
tmpP_weight_class_id int NOT NULL DEFAULT '0',
tmpP_length decimal(15,8) NOT NULL DEFAULT '0.00000000',
tmpP_width decimal(15,8) NOT NULL DEFAULT '0.00000000',
tmpP_height decimal(15,8) NOT NULL DEFAULT '0.00000000',
tmpP_length_class_id int NOT NULL DEFAULT '0',
tmpP_subtract tinyint NOT NULL DEFAULT '1',
tmpP_minimum int NOT NULL DEFAULT '1',
tmpP_sort_order int NOT NULL DEFAULT '0',
tmpP_status tinyint NOT NULL DEFAULT '0',
tmpP_viewed int NOT NULL DEFAULT '0',
tmpP_date_added datetime NOT NULL,
tmpP_date_modified datetime NOT NULL
);

CREATE TEMPORARY TABLE temp_product_image (
tmpI_id INT AUTO_INCREMENT PRIMARY KEY,
tmpI_product_image_id int NOT NULL,
tmpI_product_image_id_Orig int NOT NULL,
tmpI_product_id int NOT NULL,
tmpI_image varchar(255) DEFAULT NULL,
tmpI_sort_order int NOT NULL DEFAULT '0'
);

CREATE TEMPORARY TABLE temp_images (
tmpM_id int AUTO_INCREMENT PRIMARY KEY,
tmpM_image varchar(255) DEFAULT NULL
);

CREATE TEMPORARY TABLE temp_images_sort_order (
tmpS_id int AUTO_INCREMENT PRIMARY KEY,
tmpS_sort_order varchar(255) DEFAULT NULL
);

/****************************************************
* put all images in parameter into a temp table *
****************************************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "One", "put all images in parameter into a temp table", "");

set iCounter = 1;
set iPosA = 1;
set iPosB = 1;
set sTemp = p_Concat_image;
set bIsFinished = false;

while iCounter < 10 and bIsFinished = false DO
set iPosA = LOCATE(sDelimiterTab, sTemp, iPosB - 1);
set iPosB = LOCATE(sDelimiterTab, sTemp, iPosB);

if iPosA > 0 and iPosB = 0 then
set sTemp = mid(sTemp, iPosA + 1);
set bIsFinished = true;
else
set sTemp = mid(sTemp, iPosA + 1, iPosB - iPosA - 1);
end if;

if sTemp != "" then
insert into temp_images (tmpM_image)
values (LOWER(TRIM(IFNULL(sTemp, ""))));
end if;

set iCounter = iCounter + 1;
set iPosB = iPosB + 1;
end while;

/***************************************************************
* put all images sort order in parameter into a temp table *
***************************************************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Two", "put all images sort order in parameter into a temp table", "");

set iCounter = 1;
set iPosA = 1;
set iPosB = 1;
set sTemp = p_Concat_image_sort_order;
set bIsFinished = false;

while iCounter < 10 and bIsFinished = false DO
set iPosA = LOCATE(sDelimiterTab, sTemp, iPosB - 1);
set iPosB = LOCATE(sDelimiterTab, sTemp, iPosB);

if iPosA > 0 and iPosB = 0 then
set sTemp = mid(sTemp, iPosA + 1);
set bIsFinished = true;
else
set sTemp = mid(sTemp, iPosA + 1, iPosB - iPosA - 1);
end if;

if sTemp != "" then
insert into temp_images_sort_order (tmpS_sort_order)
values (LOWER(TRIM(IFNULL(sTemp, ""))));
end if;

set iCounter = iCounter + 1;
set iPosB = iPosB + 1;
end while;

/****************************************
* check all sort orders are integer *
****************************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Three", "check all sort orders are integer", "");

if not exists (SELECT *
FROM temp_images_sort_order
WHERE tmpS_sort_order LIKE '[0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9][0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9][0-9][0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9][0-9][0-9][0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') then
insert into temp_errors (err_Category, err_Name, err_Long_Description, err_Values)
SELECT 'image', 'sort order for images is not a integer (8 digits or less) ', concat('Sort order ', tmpS_sort_order), tmpS_sort_order
FROM temp_images_sort_order
WHERE tmpS_sort_order LIKE "[0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9][0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9][0-9][0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9][0-9][0-9][0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9][0-9][0-9][0-9][0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9][0-9][0-9][0-9][0-9][0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]";
set p_bIsOk = false;
end if;

/***********************************
* check sort orders are unique *
***********************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Four", "check sort orders are unique", "");

if not exists (SELECT tmpM_image, count(*) As Count_of_numbers
FROM temp_images
GROUP BY tmpM_image
HAVING count(*) > 1) then
insert into temp_errors (err_Category, err_Name, err_Long_Description, err_Values)
SELECT 'image', 'images are not unique ', concat('Sort order ', T.tmpM_image, " repeats ", CONVERT(T.Count_of_numbers, CHAR(10)), " times."), T.tmpM_image
FROM (SELECT tmpM_image, count(*) As Count_of_numbers
FROM temp_images
GROUP BY tmpM_image
HAVING count(*) > 1) As T;
set p_bIsOk = false;
end if;

/******************************
* check images are unique *
******************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Five", "check images are unique", "");

if not exists (SELECT tmpS_sort_order, count(*) As Count_of_numbers
FROM temp_images_sort_order
GROUP BY tmpS_sort_order
HAVING count(*) > 1) then
insert into temp_errors (err_Category, err_Name, err_Long_Description, err_Values)
SELECT 'image', 'sort order for images is not unique ', concat('Sort order ', T.tmpS_sort_order, " repeats ", CONVERT(T.Count_of_numbers, CHAR(10)), " times."), T.tmpS_sort_order
FROM (SELECT tmpS_sort_order, count(*) As Count_of_numbers
FROM temp_images_sort_order
GROUP BY tmpS_sort_order
HAVING count(*) > 1) As T;
set p_bIsOk = false;
end if;

/***********************************************************
* check number of images matches number of sort orders *
***********************************************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Six", "check number of images matches number of sort orders", "");

if EXISTS (select * FROM temp_images) AND EXISTS (SELECT * FROM temp_images_sort_order) THEN
if (select max(tmpM_id) FROM temp_images) = (SELECT MAX(tmpS_id) FROM temp_images_sort_order) THEN
insert into temp_errors (err_Category, err_Name, err_Long_Description, err_Values)
values ('image', 'number of images does not equal number of sort order for those images', '', '');
set p_bIsOk = false;
END IF;
END IF;

/*********************************************************
* check if item is in db_shoes_product from image *
* also check image isn't in shoes_product_image *
* also check name isn't in shoes_product_description *
*********************************************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Seven", "check if item is in db_shoes_product from image, also check image isnt in shoes_product_image, also check name isnt in shoes_product_description", "");

INSERT INTO temp_product_image (
tmpI_product_image_id_Orig,
tmpI_product_id,
tmpI_image,
tmpI_sort_order)
SELECT product_image_id, product_id, image, sort_order
FROM db_shoes.shoes_product_image
WHERE trim(lower(image)) in (SELECT trim(lower(tmpM_image)) FROM temp_images);

IF EXISTS (SELECT *
FROM db_shoes.shoes_product_description
WHERE language_id = p_language_id
AND product_id in (SELECT tmpI_product_id FROM temp_product_image)
) THEN
INSERT INTO temp_errors (err_Category, err_Name, err_Long_Description, err_Values)
SELECT DISTINCT 'settings', 'Duclicate product description found from picture ', concat('check pictures for product id ', TRIM(CONVERT(tmpI_product_id, CHAR(10)))), TRIM(CONVERT(tmpI_product_id, CHAR(10)))
FROM temp_product_image;
set p_bIsOk = false;
END IF;

/***********************************************************
* If an error has been flagged return error temp table *
* or else return details of language *
***********************************************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Eight", "If an error has been flagged return error temp table or else return details of language", "");

if p_bIsOk then
SELECT
tmpSt_ID,
tmpSt_Category,
tmpSt_Name,
tmpSt_Long_Description,
tmpSt_Values
FROM temp_status
ORDER BY tmpSt_ID;
else
select err_ID, err_Category, err_Name, err_Long_Description, err_Values
from temp_errors;
end if;

END//

DELIMITER ;

------------------------------------------------------------------------------------------
Calling Stored procedure

use db_settings;

set @bIsOk = true;
set @product_id = -1;
set @language_id = 1;
set @prd_model = "model";
set @prd_sku = "sku";
set @prd_upc = "upc";
set @prd_ean = "ean";
set @prd_jan = "jan";
set @prd_isbn = "isbn";
set @prd_mpn = "mpn";
set @prd_location = "location";
set @prd_quantity = 0;
set @prd_stock_status_id = 0;
set @prd_image = "image";
set @prd_manufacturer_id = 0;
set @prd_shipping = 0;
set @prd_price = 0.0;
set @prd_points = 0;
set @prd_tax_class_id = 0;
set @prd_date_available = "2020-01-01";
set @prd_weight = 0.0;
set @prd_weight_class_id = 0;
set @prd_length = 0.0;
set @prd_width = 0.0;
set @prd_height = 0.0;
set @prd_length_class_id = 0;
set @prd_subtract = 0;
set @prd_minimum = 0;
set @prd_sort_order = 0;
set @prd_status = 0;
set @desc_name = "name";
set @desc_description = "description";
set @desc_tag = "teg";
set @desc_meta_title = "meta title";
set @desc_meta_description = "meta description";
set @desc_meta_keyword = "meta keyword";
set @Concat_image = "catalog/demo/ipod_nano_5.jpg1\tcatalog/demo/ipod_nano_4.jpg\tcatalog/demo/ipod_nano_123.jpg";
set @Concat_image_sort_order = "1\t2\t3";
set @flag_new_language = false;
set @flag_overwrite_product = true;
set @flag_overwrite_description = true;
set @flag_remove_old_images = false;

CALL insertProduct(@bIsOk,
@product_id,
@language_id,
@prd_model,
@prd_sku,
@prd_upc,
@prd_ean,
@prd_jan,
@prd_isbn,
@prd_mpn,
@prd_location,
@prd_quantity,
@prd_stock_status_id,
@prd_image,
@prd_manufacturer_id,
@prd_shipping,
@prd_price,
@prd_points,
@prd_tax_class_id,
@prd_date_available,
@prd_weight,
@prd_weight_class_id,
@prd_length,
@prd_width,
@prd_height,
@prd_length_class_id,
@prd_subtract,
@prd_minimum,
@prd_sort_order,
@prd_status,
@desc_name,
@desc_description,
@desc_tag,
@desc_meta_title,
@desc_meta_description,
@desc_meta_keyword,
@Concat_image,
@Concat_image_sort_order,
@flag_new_language,
@flag_overwrite_product,
@flag_overwrite_description,
@flag_remove_old_images
);

SELECT @bIsOk As Is_OK, @product_id As Pruduct_ID;

Regarding Error code : 1406 : Data Truncation : Data too long for column (6 replies)

Vivek Kumar Sharma — Wed, 21 Oct 2020 06:00:48 +0000

Hello,

I have an error saying
Regarding Error code : 1406 : Data Truncation : Data too long for column

scenario:
version: 8.0.19

I am fetching data from federated table (source),

source:

column length is varchar(50).
I have 1 record in this table at id say 517 with length 50.
ENGINE=FEDERATED DEFAULT CHARSET=utf8

Destination:
column length is varchar(50).
ENGINE=InnoDB DEFAULT CHARSET=utf8

Issue:

when i read data from source and insert in destination table in range say rows from 515 to 518, it give error: Error code : 1406 : Data Truncation : Data too long for column and showing error in data at ID=517.

but if I insert data specific for ID=517, it inserted without any error.

I am using strict mode and tried with removing strict mode with
SET @@global.sql_mode= '';
but same error.

what could be the issue and how to solve it.

Win 10 Task Manager shows 2 instances of mysqld process (3 replies)

Judy Whiteside — Tue, 21 Jul 2020 16:00:30 +0000

My Windows 10 shows 2 instances of mysqld processes running.

cmd> tasklist /fi "imagename eq mysqld.exe" shows:

Image Name PID Session Name Session# Mem Usage
================== ============== ========= ==========
mysqld.exe 5656 Services 0 7,620 K
mysqld.exe 7804 Services 0 354,208 K

I do not use more than one mysql instance. When I installed MySQL initially,
it showed up in the Services list and I set it to run Automatically.
I never run anything from the command line involving invocation.

For each of the processes above I ran:
cmd> Get-WmiObject Win32_Process -Filter "name = 'mysqld.exe'" | where {$_.ProcessID -eq 5656 }

__PATH : \\SCOUT\root\cimv2:Win32_Process.Handle="5656"
Caption : mysqld.exe
CommandLine : "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" MySQL80

__PATH : \\SCOUT\root\cimv2:Win32_Process.Handle="7804"
Caption : mysqld.exe
CommandLine : "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" "--defaults-file=C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" MySQL80

One of these instances is a dud -- the pid 5656.
You can see that one of the instances was invoked wrong.
See the odd double-quote differences for the command line information.

My installs are:
Windows 10 version 2004
MySQL 5.8.16
MySQL Workbench 8.0

I've been using MySQL for years. I did the Windows 10 2004 feature update just a few days ago.
I think (??) that before that I would only see one instance of mysqld in the Task Manager.

Here is one real problem, not just an observation:
I cannot stop the mysql service using the Services list. It gives up with failure because the action timed out.
The Services list shows only 1 instance, but the Task Manager shows 2 items.
Perhaps the system is confused on which to close or how to do both --- ?
I can successfully kill both processes from the Task Manager.
I think this may be making my system RESTART action **very slow** since closing down the mysqld processes is troublesome.

If I restart the computer, both instances show up right away.

Even with the detailed Get-Wmi-Object info, there is no indication of who/what starts each instance.

Is this process something started by how mysql is initiated, or does Windows initiate it?
Seems something has a bug in the initiation of mysql. Is it MySQL or Windows or ???

mySQL database table disappears but leaves reference in PHPmyAdmin navigation panel (2 replies)

Neil Dempster — Mon, 13 Jul 2020 20:57:59 +0000

I have been having an ongoing battle with Go Daddy regarding tables disappearing from a database but still leaving the reference to the table in the PHPmyAdmin navigation panel (Note: The table was not deleted by me.). If I click the hyperlink for the table, I receive an error: #1146 – Table ‘cps_db_sa.bdls’ doesn’t exist. This happened two months ago and because I felt the database must've been corrupted in some way I rebuilt a brand-new database from scratch (NOT using a backup restore). It has been running smoothly for two months but this morning the same thing happened although it was a different table that disappeared. Because this is a development database no other user has access; I have not created any 'write' queries as of yet (e.g., INSERT) so the only queries are fetch (e.g., SELECT). We are also on a shared hosting platform.

Go Daddy is telling me it must be something I'm doing but because this database was running smoothly for many weeks and the fact that I was not even in the database yesterday, it seems odd to now have a table just disappear like this. Have any of you experienced this phenomenon? Thank you for any input you can provide.

mysql8.0.18 a killed tread cant released (no replies)

Danie Dong — Mon, 08 Jun 2020 06:32:39 +0000

Hi:

I met a very difficult problem. A threads keep in killed for days.it was doing a simple searching （select count(*)）. as i knew it was a New feature（innodb_parallel_read_threads=4）what made select count() faster
in mysql8.0. it happenned twice already and now it happen in another mysql server.

undo logs cant be truncated by mysql system and Grow to 5G per file· cpu was full buzy.no err in errlog

Call to mysqld_safe in mysql.server script keeps script running. (2 replies)

Levi Dhuyvetter — Sat, 06 Jun 2020 18:54:56 +0000

Hi,

Apologies if this is the wrong thread to post this. I was about to file a bug report but I'm net entirely sure this is a bug.

I'm trying to start MySQL on a remote machine using libssh2. When using the command "mysql.server start" it keeps running and never returns a status code.

Looking at the mysql.server script, the problem seems to be related to this line where mysqld_safe is called:

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &

I can see at the end the output being redirected to /dev/null and the call being run on the background.

However, I think my call to mysql.server from libssh2 keeps running because of some kind of activity on the error output.

Changing it as follows, redirecting stderr to stdout to also be discarded fixes this problem:

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

I'm not too familiar with this kind of stuff so apologies if anything doesn't make sense. I'm just assuming that mysql.server script is only supposed to initiate startup and exit rather than monitoring error output but please tell me if I'm wrong.

How do we escalate a bug report? (4 replies)

Rafael Bracho — Fri, 06 Mar 2020 19:03:34 +0000

There is a bug (#94294) that has been ignored by the development community for way too long. It appeared in v8, a "fix" was introduced in 8.0.13 (not a great fix, but worked), which itself disappeared in 8.0.14, without an alternate fix. The bug affects users of Workbench with MySQL 5.7 or any MariaDB. The "feature" introduced in v8, which originated the bug, is of dubious value, but that is not the topic here.

The bug has been closed! And, prior to that, its severity was lowered. The bug is filed against MacOS, but it really affects all versions of Workbench.

We are stuck in version 8.0.13 of Workbench when it has seen significant improvements up to 8.0.18 (the last one I have checked).

Nobody seems to be listening to comments in that bug report, there are plenty of people requesting that the bug be reopened. It impacts backups of MySQL 5.7 or MariaDB.

So, back to the subject, how do we escalate so someone reads the bug report and hopefully installs again the fix of 8.0.13, perhaps done properly as outlined in the same bug report?

Thanks,
Rafael