MySQL Forums - InnoDB

Question about REPEATABLE READ isolatin level. (no replies)

Stephan Smith — Sat, 29 Nov 2025 04:29:08 +0000

Hey guys,

From the description of this isolation level here: https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html, it says 'It is not recommended to mix locking statements (UPDATE, INSERT, DELETE, or SELECT ... FOR ...) with non-locking SELECT statements in a single REPEATABLE READ transaction'.

Does it mean that it is preferable that we feed it with READ-ONLY transactions or ones with only DML statements inside? I test it with the following transaction:

start transaction;

insert into t values (1, 1);

select * from t;

commit;

It commits successfully though. Is there something like a performance penalty if I do this in a production environment?

Stephan

InnoDB Table - Instant Add Column taking a long time (no replies)

Roshan Jayalath — Wed, 26 Nov 2025 06:27:56 +0000

I've a MySQL table, which I'm having trouble to add a column using instant algorithm. There is no error when I run the DDL, but it runs forever.

Type: InnoDB
MySQL Version: 8.0.35 (Hosted on OVH MySQL managed services)
Table Size: 95GB (None partitioned)
Full text indexes: None
Schema: Note that I've masked client data with xxxx

CREATE TABLE `xxxx` (
`column1` binary(16) NOT NULL,
`column2` binary(16) NOT NULL,
`column3` binary(16) NOT NULL,
`column1_type` binary(16) DEFAULT NULL,
`column4` binary(16) NOT NULL,
`column5` int NOT NULL,
`column6` int NOT NULL DEFAULT '0',
`column7` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`column8` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`column9` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`column1`),
UNIQUE KEY `xxxxx_column7` (`column3`,`column7`),
KEY `idx_xxxx_xxxx` (`column2`),
KEY `idx_xxxx_xxxxx` (`column4`),
KEY `idx_xxxx_xxxxxx` (`column7`),
KEY `idx_xxxx_xxxxxxx` (`column8`,`column1_type`,`column4`,`column5`),
KEY `idx_xxxx_column1type_column4_column3_column7` (`column1_type`,`column4`,`column3`,`column7`),
KEY `idx_xxxx_column1_type_column7` (`column1_type`,`column7`) USING BTREE,
CONSTRAINT `xxxx xxxxxx` FOREIGN KEY (`column3`) REFERENCES `bbbb` (`column3`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `xxxx xxxxxx` FOREIGN KEY (`column2`) REFERENCES `aaaaa` (`column2`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `xxxxx column1_type` FOREIGN KEY (`column1_type`) REFERENCES `yyyyy_type` (`column1_type`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `xxxx xxxx` FOREIGN KEY (`column4`) REFERENCES `zzzz` (`column4`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Other settings:
"Engine" : "InnoDB",
"Version" : 10,
"Row_format" : "Dynamic",
"Rows" : 159,829,897,
"Avg_row_length" : 165,
"Data_length" : 26444595200,
"Max_data_length" : 0,
"Index_length" : 76576980992,
"Data_free" : 4194304,
"Auto_increment" : null,
"Check_time" : null,
"Collation" : "utf8mb4_unicode_ci",
"Checksum" : null,
"Create_options" : "",
"Comment" : ""

Versions: 0 (So there are more opportunities to run instant DDL)
The whole MySQL instance has a replica (Through OVH high availability)

DDL command trying to run:

ALTER TABLE xxxx ADD COLUMN createdon_utc DATETIME, ALGORITHM=INSTANT;

Outcome:
1. No errors when executing. Tried with LOCK = EXCLUSIVE, LOCK = NONE etc. (Same)
2. SHOW FULL PROCESSLIST; shows Altering table, not waiting for locks, etc
3. Ran while all writes to the table were stopped (same behaviour)
4. Ran for over 10 - 15 minutes, and can see the database size growing (before stopping it)
5. I've tried with adding NULL as default - Same behaviour

I can't tune any OS level settings as it's in OVH hosting.

I cannot afford to run this on Copy algorithm that's trying to follow.

Anything I can do without causing any outage to add this column?

I'm able to add the same column on another copy of a locally installed DB, but with 20 GB-sized table. (Same schema, same settings)

Best Regards,
Roshan

Undo logs Bloat (no replies)

Azar Md — Tue, 11 Nov 2025 09:48:58 +0000

we are experiencing a huge undo log bloat in the mysql version 8.0.40

size of the undo log files grown

580G temp_undo_003.ibu
102G temp_undo_004.ibu
521G undo_001
575G undo_002

History list length 2249562972

mysql> SELECT SPACE, NAME, STATE, FILE_SIZE, ALLOCATED_SIZE FROM information_schema.innodb_tablespaces WHERE SPACE_TYPE='Undo';
+------------+-----------------+----------+--------------+----------------+
| SPACE | NAME | STATE | FILE_SIZE | ALLOCATED_SIZE |
+------------+-----------------+----------+--------------+----------------+
| 4294966771 | innodb_undo_001 | active | 555879497728 | 555879653376 |
| 4294966897 | innodb_undo_002 | inactive | 616831123456 | 616831246336 |
| 4294967277 | temp_undo_003 | active | 618643062784 | 618643353600 |
| 4294967276 | temp_undo_004 | inactive | 109270007808 | 109270110208 |
+------------+-----------------+----------+--------------+----------------+
4 rows in set (0.06 sec)

I could confirm that there is no long running transaction, but it is a heavy write system where more upsert queries will be running.

it was about more than 30 days, it has been made inactive but still not become empty state.

Any best solution to overcome this issue of clearing all the space occupied by the undo logs, within a very short interval of time.

MYSQL table end not ready? (no replies)

Tamas Nedecki — Sun, 07 Sep 2025 08:28:52 +0000

Hi!

I am creating an SQL table in a PHP program. The program runs fine, the data is written to a .cs file as well, there are no error messages, but the end of the SQL table is missing.
Is there a solution? Perhaps buffering?
Thank you.

Regards

Tamas Nedecki.

System: win11, xampp, apache, mysql, fresh installing.

MySQL 8.4.3 – Transactions stuck in waiting for handler commit until restart (async master–master replication, high concurrency on InnoDB) (no replies)

kayukaran Parameswaran — Fri, 15 Aug 2025 05:30:00 +0000

We are experiencing an intermittent but severe commit stall in MySQL 8.4.3 Community Edition (RHEL-compatible OS) running asynchronous master–master replication.

Symptoms:

Many client sessions remain in waiting for handler commit for thousands of seconds.

Affected transactions are short (single-row UPDATE/INSERT/DELETE on InnoDB table radius_auth.re_auth_ctx).

Application connections start aborting with Got an error reading communication packets.

Condition persists until mysqld is restarted. After restart, same workload runs normally.

The same workload on MySQL 5.7.15 does not show this behavior.

Replication thread (SHOW PROCESSLIST) is idle — "Source has sent all binlog to replica; waiting for more updates".

InnoDB redo log status shows no backlog; LSN positions are stable and aligned.

No Group Replication — only classic async replication.

Example stuck sessions:

| 31098 | sdpuser | ... | radius_auth | Query | 7651 | waiting for handler commit | update re_auth_ctx set next_re_auth_id=... |
| 31100 | sdpuser | ... | radius_auth | Query | 7651 | waiting for handler commit | update re_auth_ctx set next_re_auth_id=... |
Table DDL:

CREATE TABLE re_auth_ctx (
correlation_id varchar(50) NOT NULL,
protocol enum('SIM','AKA') NOT NULL,
imsi bigint NOT NULL,
updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
identity varchar(200) DEFAULT NULL,
next_re_auth_id varchar(200) DEFAULT NULL,
counter int NOT NULL DEFAULT '0',
master_key varchar(100) DEFAULT NULL,
k_aut varchar(100) DEFAULT NULL,
k_encr varchar(100) DEFAULT NULL,
nonce_s varchar(100) DEFAULT NULL,
PRIMARY KEY (imsi)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (imsi) PARTITIONS 100 */
Workload:

High QPS from multiple application nodes (RADIUS re-auth).

Each transaction updates or inserts a single PK row.

Async master–master replication between two MySQL 8.4.3 servers; only one server exhibits this issue at a time.

my.cnf highlights:

makefile
Copy
Edit
innodb_flush_log_at_trx_commit=1
innodb_doublewrite=0
innodb_flush_method=O_DIRECT
innodb_log_file_size=2G
innodb_buffer_pool_size=8G
max_connections=500
binlog enabled (async replication)

Additional Notes:

Would appreciate guidance on whether this matches an existing 8.4.3 issue, and if upgrading to ≥8.4.6 is recommended.

Innodb_dict_tables metric - how to access it? (no replies)

Jiri Cieslar — Wed, 09 Jul 2025 13:40:07 +0000

Hi everyone,

I'm currently working on monitoring our Percona XtraDB Cluster using LogicMonitor. Most of the basic MySQL metrics are being collected without any issues. However, I'm having trouble with the Innodb_dict_tables metric — LogicMonitor shows a NaN (Not a Number) value for it.

The tool gives this information:

Innodb_dict_tables: NaN

and the regex processing shows:

(postProcessParam: Innodb_dict_tables,(?:variable_)*value=(\d+), reference=, useValue=output)

I suspect this could be related to how the data is exposed by the Percona server, not necessarily an issue on the LogicMonitor side.

Does anyone know if access to Innodb_dict_tables is restricted somehow, or if it requires a specific server variable, plugin, or configuration to be enabled? I would really appreciate any insights or experience you can share.

Thank you so much in advance!

How recovery data in ibdata1 file? I truncate tables, but i need to recovery (no replies)

Anderson Silva Anderson — Tue, 10 Jun 2025 11:39:16 +0000

Help!!!!
I'm using mysql 5.1 and was executed a truncate table in all tables of database!!!

I made the backup as soon as the command was executed and I can see the data using a text editor in the ibdata file. I tried some programs to recover, but I was not successful.
Can anyone help me recover this data? I beg you!!!!

tks

Anderson

Update of innodb_buffer_pool_instances (1 reply)

Girish Salunke — Sun, 22 Mar 2026 22:13:28 +0000

Dear Team,

We are using MySQL 8.0.37 community version and want to update innodb_buffer_pool_instances and other variable too to increase the performance.

I edited the my.ini to add entry for innodb_buffer_pool_instances=8 and restarted MySQL services. But system is taking dafult value for innodb_buffer_pool_instances=1.

I tried to set the varibale through SET PERSIT and SET GLOBAL command. But couldn't set because of read only variable.

Let me know if you have any clue to set innodb_buffer_pool_instances to 8.

How to debug very slow transactions stuck at "waiting for handler commit" state? (no replies)

Erfan Safari — Fri, 02 May 2025 14:12:35 +0000

- My MySQL Setup:
I have a production MySQL 8.0.33 database.

All tables use the InnoDB storage engine.

The database has been running smoothly for the last 9 months.

Size: ~60GB of data and ~200 million rows.

Primary keys are CHAR(24) BSON ObjectIDs. These are chronologically sortable, similar to UUIDv1. We are aware that the most efficient way to store BSON ObjectIDs is as BINARY(12), but migrating to that would require extensive breaking changes, which are difficult to implement.

innodb_flush_log_at_trx_commit is set to 1, and sync_binlog is also set to 1.

- The Incident:
We have observed sudden MySQL slowdowns over the past few weeks. These incidents were temporary, lasting for several hours, and resolved on their own.

During the incidents, the output of SHOW FULL PROCESSLIST showed that many write transactions were stuck in the "waiting for handler commit" state for several minutes. These were simple INSERT or UPDATE statements—no heavy or bulk writes were involved.

We are currently unable to reproduce the issue.

- My Question:
In the event of a future incident, how can I troubleshoot the issue more deeply?
I'm not sure whether this is caused by disk saturation, an application-level issue (e.g., deadlocks), or possibly a MySQL bug.

[GCP Cloud SQL MySQL Upgrade Error] InnoDB Metadata Inconsistency - Unable to Drop Temporary Table #sql-xxx (no replies)

M Rudi Awalludin — Thu, 24 Apr 2025 09:39:19 +0000

Hi everyone,

We're facing a critical issue during a MySQL upgrade (from version 5.7 to 8.0) on a Google Cloud SQL instance. The upgrade process fails due to metadata inconsistencies in InnoDB, and possibly corrupted or missing .frm files or table data directories.

Here’s a snippet of the error log:

INFO 2025-04-22T14:11:17.034592Z Error: Following tables show signs that either table datadir directory or frm
INFO 2025-04-22T14:11:17.034744Z file was removed/corrupted. Please check server logs, examine datadir to
INFO 2025-04-22T14:11:17.034915Z detect the issue and fix it before upgrade
INFO 2025-04-22T14:11:17.041222Z database.#sql-7_2b1a9e4 - present in INFORMATION_SCHEMA's
INFO 2025-04-22T14:11:17.041413Z INNODB_SYS_TABLES table but missing from TABLES table
INFO 2025-04-22T14:11:17.045358Z 19) Tables recognized by InnoDB that belong to a different engine (engineMixup)

Problem Summary:

Table #sql-7_2b1a9e4 is listed in INFORMATION_SCHEMA.INNODB_SYS_TABLES but not in INFORMATION_SCHEMA.TABLES.

It appears to be a leftover temporary table from a failed/crashed operation.

The logs indicate engineMixup, where InnoDB tables are being detected as belonging to a different storage engine.

This prevents the MySQL upgrade from proceeding.

What We've Tried:

Running:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

Attempting to drop the table with:

DROP TABLE `#mysql50##sql-7_2b1a9e4`;

But this fails, stating the table does not exist.

Additional Constraints:

Dump and restore is not feasible, as the database size exceeds 500GB. This would result in unacceptable downtime for a production environment.

Questions:

Is there a safe and clean way to remove orphan InnoDB table metadata (like #sql-7_2b1a9e4) without requiring a full export/import?

Are there any tools (e.g., from Percona, Oracle, or Google Cloud internal tools) that can repair or clean up InnoDB metadata inconsistencies?

If file-level repairs are an option, how can this be done securely within GCP Cloud SQL without risking data integrity?

Any insights or similar experiences would be greatly appreciated, especially from those who have dealt with this type of upgrade issue on managed MySQL services. Thanks in advance!

MySQL 8.4.2 MY-013935 IO-layer timeout before wait_timeout was reached. (no replies)

Pavol S — Mon, 10 Mar 2025 09:58:59 +0000

Hello,

we used MySQL 5.7.35 + java 17 web application with connector mysql-connector-j-8.2.0.

Application is on different host and running on Windows platform. Database is running on Windows as well.

We have migrated MySQL from 5.7.35 to 8.4.2 recently with connector mysql-connector-j-9.0.0. From that time, we can see a lot of errors in MySQL log:

[ERROR] [MY-013935] [Server] IO-layer timeout before wait_timeout was reached.

The message appears many times during day with no related application logs. Has anyone experience with this? What exactly that error mean?

Thank you. Pavol

Deadlock (no replies)

Nicola Farina — Fri, 27 Sep 2024 09:45:46 +0000

A couple of days ago this deadlock happened on our MySql 5.7 database:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-09-25 11:03:39 0x14b52a06b700
*** (1) TRANSACTION:
TRANSACTION 7342267151, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 23580984, OS thread handle 22769182357248, query id 422061910 10.11.1.43 multidialogo2 updating
UPDATE `multiqueue` SET `postal_orders_generation_status` = 'DONE' WHERE uuid = 'ff9c409d-7b11-55a2-a37f-a278d3b7a75b'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5386 page no 877543 n bits 280 index uuid_uq of table `mdnetb_main`.`multiqueue` trx id 7342267151 lock_mode X locks rec but not gap waiting
Record lock, heap no 214 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 30; hex 66663963343039642d376231312d353561322d613337662d613237386433; asc ff9c409d-7b11-55a2-a37f-a278d3; (total 36 bytes);
1: len 4; hex 0012d0f0; asc ;;

*** (2) TRANSACTION:
TRANSACTION 7342267150, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 23580988, OS thread handle 22768326719232, query id 422061911 10.11.1.123 multidialogo2 updating
UPDATE multiqueue SET `acknowledged_at` = '2024-09-25 13:03:39', status = 'ACKNOWLEDGED' , status_message = 'Queue has ben acknowledged by dispatch service' , debug_last_modifier_class = 'Netbuilder\\Console\\Service\\Storage\\Queue\\QueueReportStorage' WHERE uuid = 'ff9c409d-7b11-55a2-a37f-a278d3b7a75b'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5386 page no 877543 n bits 280 index uuid_uq of table `mdnetb_main`.`multiqueue` trx id 7342267150 lock mode S locks rec but not gap
Record lock, heap no 214 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 30; hex 66663963343039642d376231312d353561322d613337662d613237386433; asc ff9c409d-7b11-55a2-a37f-a278d3; (total 36 bytes);
1: len 4; hex 0012d0f0; asc ;;

The cause of the deadlock seems (to me) this:
there are two PHP services that have issued two different UPDATE statements on the same table line *at the same instant*.

I have two questions, though:

1. the two statements are two UPDATE very similar, the lock mode should be X for both of them. Why the second one has the mode S ?
2. the deadlock is caused by the simultaneity? What could be the best strategy to handle this risk? Adding a timeout the the UPDATE, in order to attempt again after the other one (hopefully) succeeds?

Thanks in advance
Nicola

Enhancing MVCC ReadView Scalability (5 replies)

Bin Wang — Wed, 15 Jan 2025 13:33:30 +0000

The MVCC ReadView uses a vector to store the list of active transactions. In high-concurrency scenarios, this list can become large, leading to a larger working set. In NUMA environments, both querying and replication can become slower, potentially causing a single CPU time slice to miss its deadline and resulting in significant context-switching costs.

MySql 5.7 wrong (apparently) value of character_maximum_length (2 replies)

Nicola Farina — Tue, 13 Aug 2024 13:50:23 +0000

Hi

I am trying to analyze a table structure, to understand why ALTER TABLE operations take so long time.
My attention comes to longtext and mediumtext columns.
One of them, though, has a strange clue in the information_schema.COLUMNS view:
character_maximum_length reports a extremely high value, which does not seem to relate to reality:

this is the table structure:
CREATE TABLE `multiqueue` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`uuid` varchar(36) NOT NULL,
`draft_uuid` varchar(36) DEFAULT NULL COMMENT 'Original draft',
`main_queue_uuid` varchar(36) DEFAULT NULL COMMENT 'Means this is the alternative channels queue of another queue',
`original_request` mediumtext COMMENT 'Original request transmitted from the client',
`action` varchar(191) DEFAULT NULL,
`author_id` bigint(20) NOT NULL,
`idaccount` bigint(20) DEFAULT NULL COMMENT 'this is the owner_id',
`author_uuid` varchar(36) NOT NULL,
`owner_uuid` varchar(36) NOT NULL,
`idaccount_parent` bigint(20) DEFAULT NULL COMMENT 'Deprecated: the information is obtainable from idaccount',
`sendcode` varchar(191) DEFAULT NULL,
`client_name` varchar(32) NOT NULL DEFAULT 'UNKNOWN',
`client_version` varchar(191) DEFAULT NULL,
`client_key` varchar(191) DEFAULT NULL,
`memo` varchar(256) DEFAULT NULL,
`delivery_time` datetime DEFAULT NULL,
`upload_session_uuid` varchar(36) DEFAULT NULL,
`files` mediumtext,
`attachments` mediumtext,
`custom` mediumtext,
`sender` text,
`multicerta_options` text,
`posta_options` text,
`mail_options` text,
`multibox_options` text,
`dateins` datetime DEFAULT NULL,
`deadline_at` datetime DEFAULT NULL COMMENT 'Deadline to read, for channels like MultiCerta',
`deadline_event` enum('RECEIVED-NOTIFIED','RECEIVED-OPENED','RECEIVED-READ') DEFAULT NULL,
`codicelistino` varchar(191) DEFAULT NULL,
`sqs` smallint(1) NOT NULL,
`stato` varchar(4) NOT NULL COMMENT 'Deprecated: see status',
`log` text,
`invoice_tag` varchar(100) DEFAULT NULL,
`postal_account` text,
`notify_email` varchar(191) DEFAULT NULL COMMENT 'Deprecated field, use notification_email_addresses, instead',
`notification_email_addresses` varchar(512) DEFAULT NULL COMMENT 'Comma separated values of valid emails',
`status` enum('WAITING-FOR-COST-CONFIRMATION','COST-CONFIRMED','TRANSMITTED','ACKNOWLEDGED','ABANDONED','SUCCEDED','ARCHIVED','DISPATCH-ERROR','ABORTED-BY-USER') NOT NULL DEFAULT 'WAITING-FOR-COST-CONFIRMATION' COMMENT '\r\n [WAITING-FOR-COST-CONFIRMATION] queue in pending cost confirmation status,\r\n [COST-CONFIRMED] cost have been confirmed by user, or have been automatically confirmed due to his settings,\r\n [TRANSMITTED] transmitted to dispatching service,\r\n [ACKNOWLEDGED] received by dispatching service,\r\n [ABANDONED] pending from too much time,\r\n [SUCCEDED] queue has been delivered successfully to all of the recipients,\r\n [ARCHIVED] succeded queue moved in the archive,\r\n [DISPATCH-ERROR] error while dispatching service,\r\n [ABORTED-BY-USER] refused by user after acknowledgement',
`status_message` varchar(512) NOT NULL,
`created_at` datetime NOT NULL,
`cost_confirmed_at` datetime DEFAULT NULL,
`transmitted_at` datetime DEFAULT NULL,
`acknowledged_at` datetime DEFAULT NULL,
`error_at` datetime DEFAULT NULL,
`abandoned_at` datetime DEFAULT NULL,
`succeded_at` datetime DEFAULT NULL,
`archived_at` datetime DEFAULT NULL COMMENT 'Date the user moved the queue in the archive, cannot be acheived if the queue is not succeded',
`aborted_by_user_at` datetime DEFAULT NULL,
`status_error_message` text,
`recipient_settings_extracted` bit(1) NOT NULL DEFAULT b'0' COMMENT 'indicates that this queue''s recipients data have been extracted and saved in the recipient log table.',
`postal_orders_generation_status` enum('NONE','PENDING','DONE','ERROR') DEFAULT 'NONE' COMMENT 'Status of postal order (files) generation, NONE: no postal order to process, PENDING: processing postal orders, DONE: processed, ERROR: error during processing',
`history_year` int(4) DEFAULT NULL COMMENT 'Year to be used in historicization',
`history_month` int(2) DEFAULT NULL COMMENT 'Year to be used in historicization',
`history_day` int(2) DEFAULT NULL COMMENT 'Year to be used in historicization',
`debug_last_modifier_class` varchar(512) DEFAULT 'UNKNOWN' COMMENT 'last class acceding to storage service',
`save_cost_with_legacy_strategy` enum('YES','NO') DEFAULT 'YES',
`search_uses_email` tinyint(4) NOT NULL COMMENT 'The queue uses email',
`search_uses_pec` tinyint(4) NOT NULL COMMENT 'The queue uses pec',
`search_uses_letter` tinyint(4) NOT NULL COMMENT 'The queue uses letter (ex sendposta)',
`search_uses_multibox` tinyint(4) NOT NULL COMMENT 'The queue uses multibox',
`search_uses_multicerta` tinyint(4) NOT NULL COMMENT 'The queue uses multicerta',
`search_recipients_text` longtext NOT NULL COMMENT 'Used to search recipients associated to queue',
`directory` enum('ARCHIVE') DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uuid_uq` (`uuid`),
KEY `multiqueue_upload_session_fk` (`upload_session_uuid`),
KEY `multiqueue_multiqueue_draft_uuid_fk` (`draft_uuid`),
KEY `multiqueue_sendcode_idx` (`sendcode`),
KEY `multiqueue_idaccount_fk` (`idaccount`),
KEY `multiqueue_idaccount_parent_fk` (`idaccount_parent`),
KEY `multiqueue_owner_uuid_account_uuid_fk` (`owner_uuid`),
KEY `multiqueue_dateins_idx` (`dateins`),
KEY `multiqueue_status_idx` (`status`),
KEY `postal_orders_generation_status_idx` (`postal_orders_generation_status`),
KEY `multiqueue_client_name_idx` (`client_name`),
KEY `multiqueue_history_year_idx` (`history_year`),
KEY `multiqueue_history_month_idx` (`history_month`),
KEY `multiqueue_history_day_idx` (`history_day`),
KEY `multiqueue_multiqueue_main_queue_uuid_fk` (`main_queue_uuid`),
KEY `multiqueue_author_uuid_account_uuid_fk` (`author_uuid`),
KEY `multiqueue_author_id_account_idaccount_fk` (`author_id`),
KEY `multiqueue_directory_idx` (`directory`),
CONSTRAINT `multiqueue_author_id_account_idaccount_fk` FOREIGN KEY (`author_id`) REFERENCES `account` (`IDACCOUNT`),
CONSTRAINT `multiqueue_author_uuid_account_uuid_fk` FOREIGN KEY (`author_uuid`) REFERENCES `account` (`uuid`),
CONSTRAINT `multiqueue_idaccount_fk` FOREIGN KEY (`idaccount`) REFERENCES `account` (`IDACCOUNT`) ON DELETE SET NULL,
CONSTRAINT `multiqueue_idaccount_parent_fk` FOREIGN KEY (`idaccount_parent`) REFERENCES `account` (`IDACCOUNT`) ON DELETE SET NULL,
CONSTRAINT `multiqueue_multiqueue_draft_uuid_fk` FOREIGN KEY (`draft_uuid`) REFERENCES `multiqueue_draft` (`uuid`),
CONSTRAINT `multiqueue_multiqueue_main_queue_uuid_fk` FOREIGN KEY (`main_queue_uuid`) REFERENCES `multiqueue` (`uuid`),
CONSTRAINT `multiqueue_owner_uuid_account_uuid_fk` FOREIGN KEY (`owner_uuid`) REFERENCES `account` (`uuid`),
CONSTRAINT `multiqueue_upload_session_fk` FOREIGN KEY (`upload_session_uuid`) REFERENCES `file_transfer_upload_session` (`uuid`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=1186654 DEFAULT CHARSET=utf8

For the column 'search_recipients_text' the view COLUMNS reports
4294967295 for CHARACTER_MAXIMUM_LENGTH

If I run this query, though:

select length(q.search_recipients_text), q.search_recipients_text, q.*
from multiqueue q
order by length(q.search_recipients_text) desc

the biggest length I get is 114649

Perhaps am I misunderstanding the meaning of the CHARACTER_MAXIMUM_LENGTH field ?

Thanks in advance
Nicola

MySQL execute plan behaves different in Master and Slave (no replies)

Jagadeesh Chinnathambi — Sun, 11 Aug 2024 18:24:35 +0000

I have one MySQL query, and that query is using an index on the master but not using an index on the slave.

The query is as simple as SELECT * FROM table1 JOIN table2 ON table1.primary_id = table2.fk_id with simple where condition.

We checked the structure, indexes, global variables, data, and everything else. Everything is in sync.

I am expecting the query to use the index on the slave.

Delete With JSON Filling /tmp Space (no replies)

Sam Beard — Wed, 07 Aug 2024 22:10:30 +0000

MySQL 8.0.37

statement: DELETE FROM table WHERE datefield<'date';

table has 6 int, date, timestamp, and short (<32) varchar fields and 1 LARGE JSON field. Deleting 2500 records is filling 800M of /tmp space.

Is the select part saving the JSON field? My estimate of the record space is just over the available /tmp space.

If so, why? Is this part of the problem/documentation issue I have seen reported for blobs filling the sort buffers?

innodb_buffer_pool (no replies)

小强小 — Mon, 08 Jul 2024 10:13:52 +0000

Storage pool size parameter: innodb_buffer_pool
1. Understanding the size of the storage pool: When accessing a large amount of data, the time for large amounts of data is not directly related to the size of the storage pool.
2. When the storage pool memory is exhausted, querying data will require a large number of disk IO operations;
3. Causes the database server CPU and IO to become full, eventually causing the service to crash;

question. Is there a MYSQL data indicator that can monitor that the storage pool size is about to be used up, and subsequent disk scans will be performed, requiring memory expansion operations?

mysql buffer pool how monitor (no replies)

c h — Sun, 07 Jul 2024 13:06:51 +0000

The memory size of my server is 128GB with a 4-core processor, and I have configured the MySQL buffer pool to 12GB. Every time when concurrent requests start, I notice that the MySQL service becomes slow, with FREE_BUFFERS approaching 0, server I/O reaching 100%, and CPU usage spiking to 400%. Despite having reasonable indexes for queries. How should I adjust the MySQL buffer pool size to avoid the aforementioned issues, or which MySQL metrics should I monitor to set alerts for the server to prevent crashes?

[ERROR] InnoDB: Database page corruption on disk or a failed file read of page (2 replies)

Tony Karlsson — Mon, 22 Jul 2024 12:56:55 +0000

Hello,

I ran into a problem with my MySQL database on a Nvidia Jetson Nano. All of a sudden the database went corrupt with message:
[ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=0, page number=2078]. You may have to recover from a backup.

According to the IT-guy who restored the database one of the files were missing
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock

The version was:
Server version: 5.7.42-0ubuntu0.18.04.1 (Ubuntu)

The restoration process was:
* Put mysql in recovery mode
* Made a backup with mysqldump
* Deleted the databases
* Cleared transaction logs
* Restored the system database
* Imported the mysql dump
* Removed recovery mode
* Started in normal mode

No data was lost but we had some off time, and we don't want this to happen again. I am grateful for responses so we can prevent this to happen again.

Is there any knowns bugs for this version that could have caused this or any other known problems that can cause this?

$ sudo tail /var/log/mysql/error.log -n 10000

2024-06-17T11:22:36.184533Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.42-0ubuntu0.18.04.1) starting as process 9827 ...

2024-06-17T11:22:36.196894Z 0 [Note] InnoDB: PUNCH HOLE support available

2024-06-17T11:22:36.196969Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2024-06-17T11:22:36.196999Z 0 [Note] InnoDB: Uses event mutexes

2024-06-17T11:22:36.197026Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier

2024-06-17T11:22:36.197053Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.13

2024-06-17T11:22:36.197080Z 0 [Note] InnoDB: Using Linux native AIO

2024-06-17T11:22:36.198518Z 0 [Note] InnoDB: Number of pools: 1

2024-06-17T11:22:36.198950Z 0 [Note] InnoDB: Not using CPU crc32 instructions

2024-06-17T11:22:36.204126Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

2024-06-17T11:22:36.240555Z 0 [Note] InnoDB: Completed initialization of buffer pool

2024-06-17T11:22:36.246257Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

2024-06-17T11:22:36.262871Z 0 [Note] InnoDB: Highest supported file format is Barracuda.

2024-06-17T11:22:36.266851Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 11307628957

2024-06-17T11:22:36.266921Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 11307629499

2024-06-17T11:22:36.267349Z 0 [Note] InnoDB: Database was not shutdown normally!

2024-06-17T11:22:36.267387Z 0 [Note] InnoDB: Starting crash recovery.

2024-06-17T11:22:36.453977Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

2024-06-17T11:22:36.454055Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2024-06-17T11:22:36.454205Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2024-06-17T11:22:37.092688Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2024-06-17T11:22:37.095278Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.

2024-06-17T11:22:37.095333Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.

2024-06-17T11:22:37.096459Z 0 [Note] InnoDB: Waiting for purge to start

2024-06-17T11:22:37.146798Z 0 [Note] InnoDB: 5.7.42 started; log sequence number 11307629499

2024-06-17T11:22:37.147194Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

2024-06-17T11:22:37.147768Z 0 [Note] Plugin 'FEDERATED' is disabled.

2024-06-17T11:22:37.157476Z 0 [Note] InnoDB: Buffer pool(s) load completed at 240617 13:22:37

2024-06-17T11:22:37.170107Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.

2024-06-17T11:22:37.170190Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.

2024-06-17T11:22:37.170215Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.

2024-06-17T11:22:37.170234Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.

2024-06-17T11:22:37.172061Z 0 [Warning] CA certificate ca.pem is self signed.

2024-06-17T11:22:37.172185Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.

2024-06-17T11:22:37.172456Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306

2024-06-17T11:22:37.172497Z 0 [Note] - '127.0.0.1' resolves to '127.0.0.1';

2024-06-17T11:22:37.172566Z 0 [Note] Server socket created on IP: '127.0.0.1'.

2024-06-17T11:22:37.218912Z 0 [Note] Event Scheduler: Loaded 0 events

2024-06-17T11:22:37.219457Z 0 [Note] /usr/sbin/mysqld: ready for connections.

Version: '5.7.42-0ubuntu0.18.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)

2024-06-17T11:22:37.288008Z 0 [ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=0, page number=2078]. You may have to recover from a backup.

2024-06-17T11:22:37.288078Z 0 [Note] InnoDB: Page dump in ascii and hex (16384 bytes):

Troubleshooting innodb mysql-router containers as they are in a unhealthy state (no replies)

Faisal Basha — Fri, 14 Jun 2024 16:30:49 +0000

Hi,

I have been facing an issue with my innodb setup of 3 nodes. Each node runs two containers with image mysql/mysql-server:5.7 and mysql/mysql-router:latest.

These nodes are VM of an esxi which were restarted because of a crash. So when I restarted the containers I am not able to get the router container to a healthy state rather keeps restarting.

so I exec to the vantage-mysql container and mysqlsh to access the shell in order to administer the database. On the master node, Using the JS console I am not able to get the cluster state so here is what I always see:

MySQL 1XX.1XX.XX.XX:3306 ssl JS > var cluster = dba.getCluster('prodCluster_new');
Dba.getCluster: This function is not available through a session to an instance belonging to an unmanaged replication group (RuntimeError)

I tried to reboot the cluster but here is what I got:

MySQL 1XX.1XX.XX.XX:3306 ssl JS > var cluster = dba.rebootClusterFromCompleteOutage();
Dba.rebootClusterFromCompleteOutage: This function is not available through a session to an instance belonging to an unmanaged replication group (RuntimeError)

MySQL 1XX.1XX.XX.XX:3306 ssl SQL > SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | b0d2cc2d-2993-11ef-b92f-00505680c619 | 1XX.1XX.XX.XX | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
1 row in set (0.0004 sec)

So from the above there is only one member which is the master that is online

so from another member I ran the above code and here is what I got:

MySQL 1XX.1XX.XX.XX:3306 ssl SQL > CHANGE MASTER TO MASTER_HOST='1XX.1XX.XX.XX', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='binlog.000442', MASTER_LOG_POS=295030;
Query OK, 0 rows affected, 1 warning (0.0136 sec)
Note (code 1760): Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

MySQL 1XX.1XX.XX.XX:3306 ssl SQL > START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (6.0626 sec)
Warning (code 1681): 'group_replication_allow_local_disjoint_gtids_join' is deprecated and will be removed in a future release.

MySQL 192.168.XX.XX:3306 ssl SQL > SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | 9cc4dc4f-2993-11ef-b5a9-005056806695 | 192.168.40.47 | 3306 | RECOVERING |
| group_replication_applier | a97b2419-2993-11ef-b86e-005056809797 | 192.168.40.46 | 3306 | RECOVERING |
| group_replication_applier | b0d2cc2d-2993-11ef-b92f-00505680c619 | 192.168.40.45 | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+

the same for the other two nodes. So these nodes always say that they are in RECOVERING state whereas the master is always ONLINE

so when I run the command "show slave status" on the master node here is what I get:

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). |

So I check the server_ids which are all unique:

MySQL 1XX.1XX.XX.XX:3306 ssl SQL > SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 45 |
+---------------+-------+
1 row in set (0.0029 sec)

MySQL 1XX.1XX.XX.XX:3306 ssl SQL > SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 46 |
+---------------+-------+
1 row in set (0.0028 sec)

MySQL 1XX.1XX.XX.XX:3306 ssl SQL > SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 47 |
+---------------+-------+
1 row in set (0.0033 sec)

For each node 1XX.1XX.XX.XX are different IP addresses, I didnt want to reveal the ip address here.

Innodb: join of large table to small table is slow if small table has less than 7 rows (no replies)

Wenfeng Hu — Tue, 04 Jun 2024 07:43:13 +0000

I happened to read this post https://forums.mysql.com/read.php?22,620920,620920#msg-620920 of 2014 and I tried and confirmed that it could even be reproduced now in 2024.

I wonder if there's any method to solve this problem or any way to let the optimizer do better?

My MySQL is a RDS instance from one public cloud provider, and the version is :
mysql> show variables like "%innodb_version";
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| innodb_version | 8.0.28 |
+----------------+--------+
1 row in set (0.00 sec)

mysql> show variables like "version";
+---------------+---------------+
| Variable_name | Value |
+---------------+---------------+
| version | 8.0.28-230701 |
+---------------+---------------+
1 row in set (0.00 sec)

Below is how I reproduced the problem(some sql copied from the original post):

#create the large table
CREATE TABLE t1 (id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, t2_id int(11) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#create the small table
CREATE TABLE t2 (id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#insert 1 record in the small table
INSERT INTO t2 (name) VALUES ('small table');

#insert 262144 records in the large table; we do this by inserting 1 record and exponentially duplicating the records 18 times
INSERT INTO t1 (name, t2_id) VALUES ('large table', 1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);

#we now have 262144 identical rows in t1
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 262144 |
+----------+
1 row in set (0.04 sec)

mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.01 sec)

#run the following query; this runs relatively slow; 0.12s
SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+----+-------------+----+-------------+
| id | name | id | name |
+----+-------------+----+-------------+
| 1 | large table | 1 | small table |
| 2 | large table | 1 | small table |
| 3 | large table | 1 | small table |
| 4 | large table | 1 | small table |
| 6 | large table | 1 | small table |
| 7 | large table | 1 | small table |
| 8 | large table | 1 | small table |
| 9 | large table | 1 | small table |
| 13 | large table | 1 | small table |
| 14 | large table | 1 | small table |
+----+-------------+----+-------------+
10 rows in set (0.12 sec)

#run explain; the primary keys are NOT used
EXPLAIN format=tree SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s)
-> Sort: t1.id, limit input to 10 row(s) per chunk
-> Stream results (cost=26302.50 rows=26201)
-> Inner hash join (t1.t2_id = t2.id) (cost=26302.50 rows=26201)
-> Table scan on t1 (cost=2721.35 rows=262010)
-> Hash
-> Table scan on t2 (cost=0.25 rows=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#Now, let's add 6 more records to the small table
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');

#We now have 7 rows in the small table
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 262144 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.01 sec)

#run the query again; now it is fast; 0.00s
SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+----+-------------+----+-------------+
| id | name | id | name |
+----+-------------+----+-------------+
| 1 | large table | 1 | small table |
| 2 | large table | 1 | small table |
| 3 | large table | 1 | small table |
| 4 | large table | 1 | small table |
| 6 | large table | 1 | small table |
| 7 | large table | 1 | small table |
| 8 | large table | 1 | small table |
| 9 | large table | 1 | small table |
| 13 | large table | 1 | small table |
| 14 | large table | 1 | small table |
+----+-------------+----+-------------+
10 rows in set (0.00 sec)

#run explain; the primary keys are used
EXPLAIN format=tree SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s) (cost=39302.51 rows=10)
-> Nested loop inner join (cost=39302.51 rows=10)
-> Index scan on t1 using PRIMARY (cost=0.00 rows=10)
-> Single-row index lookup on t2 using PRIMARY (id=t1.t2_id) (cost=0.15 rows=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

#More info
mysql> show table status;
+------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| t1 | InnoDB | 10 | Dynamic | 262010 | 42 | 11026432 | 0 | 0 | 4194304 | 458731 | 2024-06-04 15:31:25 | 2024-06-04 15:31:47 | NULL | utf8_general_ci | NULL | | |
| t2 | InnoDB | 10 | Dynamic | 7 | 2340 | 16384 | 0 | 0 | 0 | 8 | 2024-06-04 15:31:30 | 2024-06-04 15:34:20 | NULL | utf8_general_ci | NULL | | |
+------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.01 sec)

mysql> show variables like 'innodb%';
+-----------------------------------------------------+-------------------------+
| Variable_name | Value |
+-----------------------------------------------------+-------------------------+
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | OFF |
| innodb_adaptive_hash_index_parts | 8 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 2 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_in_core_file | ON |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 1073741824 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | crc32 |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:128M:autoextend |
| innodb_data_home_dir | |
| innodb_ddl_buffer_size | 1048576 |
| innodb_ddl_threads | 4 |
| innodb_deadlock_detect | ON |
| innodb_dedicated_server | OFF |
| innodb_default_row_format | dynamic |
| innodb_directories | |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_doublewrite_batch_size | 0 |
| innodb_doublewrite_dir | |
| innodb_doublewrite_files | 2 |
| innodb_doublewrite_pages | 120 |
| innodb_extend_and_initialize | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_per_table | ON |
| innodb_fill_factor | 100 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbors | 0 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_fsync_threshold | 0 |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_idle_flush_pct | 100 |
| innodb_io_capacity | 12000 |
| innodb_io_capacity_max | 24000 |
| innodb_lock_wait_timeout | 50 |
| innodb_log_buffer_size | 33554432 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 1048576000 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_spin_cpu_abs_lwm | 80 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_log_write_ahead_size | 8192 |
| innodb_log_writer_threads | ON |
| innodb_lru_scan_depth | 2048 |
| innodb_max_dirty_pages_pct | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_numa_interleave | OFF |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 10240 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_cleaners | 1 |
| innodb_page_size | 16384 |
| innodb_parallel_read_threads | 4 |
| innodb_print_all_deadlocks | OFF |
| innodb_print_ddl_logs | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_purge_threads | 4 |
| innodb_random_read_ahead | OFF |
| innodb_rds_data_file_purge | OFF |
| innodb_rds_data_file_purge_all_at_shutdown | OFF |
| innodb_rds_data_file_purge_dir | |
| innodb_rds_data_file_purge_immediate | OFF |
| innodb_rds_data_file_purge_interval | 100 |
| innodb_rds_data_file_purge_max_size | 32 |
| innodb_rds_data_lock_info_enabled | OFF |
| innodb_rds_fatal_semaphore_timeout_seconds | 600 |
| innodb_rds_print_data_file_purge_process | OFF |
| innodb_rds_warning_long_semaphore_threshold_seconds | 240 |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_redo_log_archive_dirs | |
| innodb_redo_log_encrypt | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_segment_reserve_factor | 12.500000 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_spin_wait_pause_multiplier | 50 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | ON |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | OFF |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| innodb_undo_tablespaces | 2 |
| innodb_use_fdatasync | OFF |
| innodb_use_native_aio | ON |
| innodb_validate_tablespace_paths | ON |
| innodb_version | 8.0.28 |
| innodb_write_io_threads | 4 |
+-----------------------------------------------------+-------------------------+
160 rows in set (0.01 sec)

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t1 | 0 | PRIMARY | 1 | id | A | 262010 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.04 sec)

mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t2 | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Need help to review a serializability implementation for MySQL Cluster (12 replies)

Alex Ou — Fri, 02 Jan 2026 03:04:56 +0000

Hey guys,

I've developed a serializability implementation for MySQL Cluster(NDB Cluster) and you are invited to peer-review it for me. I believe it is the fifth one in commercial database systems after: MySQL InnoDB's
2PL, PostgreSQL's Serializable Snapshot Isolation, Google's Spanner's isolation level(I gave a proof in Appendix D of my article, the google guys
may not have known this), CockroachDB's timestamp-based serializability implementation. The aim is to solve consistent, large(usually implies
a distributed architecture) and performance-boosted database applications, which is daunting for those who care about consistency and
serializability. This solution to the serializability problem is a 2nd-tier one, which means it doesn't require any coding. So as long as you can
manage a MySQL Cluster, you can readily deploy and test your application with it.

This on-going project is hosted @ https://github.com/creamyfish/conflict_serializability
I also set up a discussion site @ https://www.reddit.com/r/Serializability/, besides that of github's

I am posting here since it also tackle the thrashing problem of 2PL with the so called Generalized Serializability Theorems.

Come check it out if you are interested. Your help is highly appreciated!

Reproducing Bug #115113 (no replies)

Gabriel Barcelo — Mon, 27 May 2024 11:09:42 +0000

https://bugs.mysql.com/bug.php?id=115113

Could you pelase try to reproduce the following bug with Linux (amd64)?

I think it is present across 8.0.x-8.4.0 codebase, I think it is reproducible on a variety of platforms/environments.

Best,
Gabriel

Informations and tune for Purge system! (no replies)

Christian Marinelli — Thu, 16 May 2024 13:45:47 +0000

Hi all,
i have some questions about MySQL purge system.
I read from the official documentation the purge system is automatically scheduled so i don't need to enable it in some way, but i didn't understand very weel how often it run; if i'm not wrong, i read it runs every 128 DELETE queryes and i can change this value with the innodb_purge_rseg_truncate_frequency parameter (for example if i want it runs more frequently).
Can you confirm me if i understood correcly the documentation?
Unfortunalty i worked for a long time with PostgreSQL and here there is the VACUUM process, i have a few experience with MySQL, i am learning day by day.
Thanks a lot in advance
Christian

Why is this deadlock happening? (no replies)

Vinicius Faleiro — Sat, 04 May 2024 13:58:32 +0000

I see that one microsservice is causing this deadlock bellow but couldn't find the root cause. I wouldn't expect this to happen sice 2 different threads are executing a replace command for 2 different keys on the table. Any idea?

Table DDL

CREATE TABLE faturamento (
DT_FATURA date NOT NULL COMMENT 'Data da fatura',
COD_GRUPO_FATURA varchar(500) NOT NULL
JSON_CFG_COBRANCA json NOT NULL
VL_BRUTO decimal(32,16) NOT NULL
VL_DESCONTO decimal(32,16) DEFAULT NULL
VL_LIQUIDO decimal(32,16) NOT NULL
JSON_APURACOES json NOT NULL
DESCONTOS_APLICADOS json NOT NULL
PRIMARY KEY (DT_FATURA,COD_GRUPO_FATURA)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Database output (table was empty)

2024-05-03 19:26:03 367417139072
*** (1) TRANSACTION:
TRANSACTION 8020, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 1172, OS thread handle 370219057024, query id 47288 172.19.0.9 root update
replace into faturamento (
DT_FATURA,
COD_GRUPO_FATURA,
JSON_CFG_COBRANCA,
VL_BRUTO,
VL_DESCONTO,
VL_LIQUIDO,
DESCONTOS_APLICADOS,JSON_APURACOES)
values ('2030-02-01', 'B', '{\n "cobrancaItens" : [ {\n "metodo" : "DELEGATE",\n "configuracao" : "{\\"delegateConfig\\":{\\"autoConfirmada\\":true,\\"queue\\":\\"delegate.queue\\"}}"\n } ],\n "configRetentativas" : [ {\n "metodo" : "DELEGATE",\n "totalRetentativas" : 1,\n "contadorRetentativas" : null,\n "frequencia" : "DIARIA",\n "dia" : 0\n } ]\n}', 102.90, 0.00, 102.90, '[ ]', '[ {\n "dtMovimento" : "2030-02-01",\n "idItemApurado" : "c6bf5dff-9c88-4952-98d5-a43df68a20b3",\n "idCicloApuracao" : "2030-01-01-1-2030",\n "frequencia" : "DIARIA",\n "vlPrecoApuracaoRef" : 102.90,\n "tpValorApuracao" : "TARIFA",\n "qtdCumulativaApuracaoCiclo" : 31,\n "vlSaldoAcumulado" : 102.9000000000000000,\n "cfgUltimaApuracao" : {\n "modelo" : "FIXO",\n "frequencia" : "DIARIA",\n "dia" : 1,\n "condicao" : null\n },\n "jsonItemApurado" : "{\\n \\"id\\" : \\"c6bf5dff-9c88-4952-98d5-a43df68a20b3\\",\\n \\"codContratante\\" : 9000001,\\n \\"codContratada\\" : 1000006,\\n \\"dtInicioVigencia\\" : \\"2999-01-01\\",\\n \\"dtFimVigencia\\" : \\"2024-01-01\\",\\n \\"infoAdicionalContratante\\" : \\"{\\\\\\"mcc\\\\\\": \\\\\\"105\\\\\\", \\\\\\"cnae\\\\\\": 3350707, \\\\\\"cnpj\\\\\\": \\\\\\"12.22.184/0001-04\\\\\\"}\\",\\n \\"infoAdicionalContratada\\" : \\"{}\\",\\n \\"cfgPrecificacao\\" : {\\n \\"insumo\\" : null,\\n \\"refContrato\\" : null,\\n \\"faixas\\" : null,\\n \\"fixo\\" : {\\n \\"tipo\\" : \\"TARIFA\\",\\n \\"valor\\" : 102.9\\n }\\n },\\n \\"cfgApuracao\\" : {\\n \\"modelo\\" : \\"FIXO\\",\\n \\"frequencia\\" : \\"DIARIA\\",\\n \\"dia\\" : 1,\\n \\"condicao\\" : null\\n },\\n \\"cfgDesconto\\" : null,\\n \\"cfgFaturamento\\" : {\\n \\"frequencia\\" : \\"MENSAL\\",\\n \\"dia\\" : 1,\\n \\"codAgrupamentoFatura\\" : \\"B\\",\\n \\"diasFaturamento\\" : 0\\n },\\n \\"cfgCobranca\\" : {\\n \\"cobrancaItens\\" : [ {\\n \\"metodo\\" : \\"DELEGATE\\",\\n \\"configuracao\\" : \\"{\\\\\\"delegateConfig\\\\\\":{\\\\\\"autoConfirmada\\\\\\":true,\\\\\\"queue\\\\\\":\\\\\\"delegate.queue\\\\\\"}}\\"\\n } ],\\n \\"configRetentativas\\" : [ {\\n \\"metodo\\" : \\"DELEGATE\\",\\n \\"totalRetentativas\\" : 1,\\n \\"frequencia\\" : \\"DIARIA\\",\\n \\"dia\\" : 0\\n } ]\\n },\\n \\"cdStatus\\" : \\"ATIVO\\"\\n}",\n "dtCriacaoRegistro" : "2024-04-29T19:01:52-03:00",\n "dtUltimaAtualiacao" : "2024-05-03T13:23:52-03:00",\n "tpValorDesconto" : null\n} ]' )

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 18 page no 4 n bits 72 index PRIMARY of table `apurador`.`faturamento` trx id 8020 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 4 n bits 72 index PRIMARY of table `apurador`.`faturamento` trx id 8020 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 8022, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 1193, OS thread handle 372167667584, query id 47290 172.19.0.9 root update
replace into faturamento (
DT_FATURA,
COD_GRUPO_FATURA,
JSON_CFG_COBRANCA,
VL_BRUTO,
VL_DESCONTO,
VL_LIQUIDO,
DESCONTOS_APLICADOS,JSON_APURACOES)
values ('2030-02-01', 'A', '{\n "cobrancaItens" : [ {\n "metodo" : "DELEGATE",\n "configuracao" : "{\\"delegateConfig\\":{\\"autoConfirmada\\":true,\\"queue\\":\\"delegate.queue\\"}}"\n } ],\n "configRetentativas" : [ {\n "metodo" : "DELEGATE",\n "totalRetentativas" : 1,\n "contadorRetentativas" : null,\n "frequencia" : "DIARIA",\n "dia" : 0\n } ]\n}', 89.90, 0.00, 89.90, '[ ]', '[ {\n "dtMovimento" : "2030-02-01",\n "idItemApurado" : "7b3107c0-930e-4e6f-9090-54cf73ac5b05",\n "idCicloApuracao" : "2030-01-01-1-2030",\n "frequencia" : "DIARIA",\n "vlPrecoApuracaoRef" : 89.90,\n "tpValorApuracao" : "TARIFA",\n "qtdCumulativaApuracaoCiclo" : 30,\n "vlSaldoAcumulado" : 89.9000000000000000,\n "cfgUltimaApuracao" : {\n "modelo" : "FIXO",\n "frequencia" : "DIARIA",\n "dia" : 1,\n "condicao" : null\n },\n "jsonItemApurado" : "{\\n \\"id\\" : \\"7b3107c0-930e-4e6f-9090-54cf73ac5b05\\",\\n \\"codContratante\\" : 9000001,\\n \\"codContratada\\" : 7000005,\\n \\"dtInicioVigencia\\" : \\"2999-01-01\\",\\n \\"dtFimVigencia\\" : \\"2024-01-01\\",\\n \\"infoAdicionalContratante\\" : \\"{\\\\\\"mcc\\\\\\": \\\\\\"998\\\\\\", \\\\\\"cnae\\\\\\": 3250709, \\\\\\"cnpj\\\\\\": \\\\\\"11.156.184/0001-08\\\\\\"}\\",\\n \\"infoAdicionalContratada\\" : \\"{}\\",\\n \\"cfgPrecificacao\\" : {\\n \\"insumo\\" : null,\\n \\"refContrato\\" : null,\\n \\"faixas\\" : null,\\n \\"fixo\\" : {\\n \\"tipo\\" : \\"TARIFA\\",\\n \\"valor\\" : 89.9\\n }\\n },\\n \\"cfgApuracao\\" : {\\n \\"modelo\\" : \\"FIXO\\",\\n \\"frequencia\\" : \\"DIARIA\\",\\n \\"dia\\" : 1,\\n \\"condicao\\" : null\\n },\\n \\"cfgDesconto\\" : null,\\n \\"cfgFaturamento\\" : {\\n \\"frequencia\\" : \\"MENSAL\\",\\n \\"dia\\" : 1,\\n \\"codAgrupamentoFatura\\" : \\"A\\",\\n \\"diasFaturamento\\" : 0\\n },\\n \\"cfgCobranca\\" : {\\n \\"cobrancaItens\\" : [ {\\n \\"metodo\\" : \\"DELEGATE\\",\\n \\"configuracao\\" : \\"{\\\\\\"delegateConfig\\\\\\":{\\\\\\"autoConfirmada\\\\\\":true,\\\\\\"queue\\\\\\":\\\\\\"delegate.queue\\\\\\"}}\\"\\n } ],\\n \\"configRetentativas\\" : [ {\\n \\"metodo\\" : \\"DELEGATE\\",\\n \\"totalRetentativas\\" : 1,\\n \\"frequencia\\" : \\"DIARIA\\",\\n \\"dia\\" : 0\\n } ]\\n },\\n \\"cdStatus\\" : \\"ATIVO\\"\\n}",\n "dtCriacaoRegistro" : "2024-04-29T22:22:50-03:00",\n "dtUltimaAtualiacao" : "2024-05-03T13:23:52-03:00",\n "tpValorDesconto" : null\n} ]' )

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 18 page no 4 n bits 72 index PRIMARY of table `apurador`.`faturamento` trx id 8022 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 4 n bits 72 index PRIMARY of table `apurador`.`faturamento` trx id 8022 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)
I tried to analyze de db logs but couldn't understand why a replace command in two different table keys caused a deadlock.

I was able to reproduce the problem using 2 mysql clients. Steps:

execute begin transaction on both clients
execute query on both clients using different values for parameter COD_GRUPO_FATURA: SELECT DT_FATURA, COD_GRUPO_FATURA, JSON_CFG_COBRANCA, VL_BRUTO, VL_DESCONTO, VL_LIQUIDO, DESCONTOS_APLICADOS,JSON_APURACOES from faturamento where DT_FATURA = ? and COD_GRUPO_FATURA = ? FOR UPDATE
execute query on both clients using same parameters from step 2: replace into faturamento ( DT_FATURA, COD_GRUPO_FATURA, JSON_CFG_COBRANCA, VL_BRUTO, VL_DESCONTO, VL_LIQUIDO, DESCONTOS_APLICADOS,JSON_APURACOES)
question is: aren't both commands using row lock? why the deadlock?

MySQL 8.4 LTS – new production-ready defaults for InnoDB (no replies)

Edwin Desouza — Fri, 03 May 2024 18:17:02 +0000

https://lefred.be/content/mysql-8-4-lts-new-production-ready-defaults-for-innodb/

MySQL 8.0.35 crashes during startup (no replies)

Praveen Kumar B A — Thu, 25 Apr 2024 11:05:19 +0000

We are facing problem where MySQL service crashes with below error. ‘binlog_transaction_compression’ is also turned off in our setup. We are able to force recovery. Can someone tell us the reason for crash? The OS has enough disk space.

2024-04-25T05:36:23Z UTC - mysqld got exception 0x16 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x1c1a641a9f0
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...
7ff750dec388 mysqld.exe!?my_print_stacktrace@@YAXPEBEK@Z()
7ff74ff5f461 mysqld.exe!?print_fatal_signal@@YAXH@Z()
7ff74ff5f1a3 mysqld.exe!?my_server_abort@@YAXXZ()
7ff750dd09aa mysqld.exe!?my_abort@@YAXXZ()
7ff750fa1359 mysqld.exe!?do_reset@Zstd_dec@compression@transaction@binary_log@@EEAAXXZ()
7ff750efc9cb mysqld.exe!?do_reset@Zstd_dec@compression@transaction@binary_log@@EEAAXXZ()

Can you please help us in posting this reply in below MySQL Forum link.
https://forums.mysql.com/read.php?22,705028,705028

MySQL 8.0.35 crashes (no replies)

Praveen Kumar B A — Thu, 25 Apr 2024 11:00:49 +0000

Hi All

The application is performing a read query but the database is crashing and not recovering. The force_innodb_recovery flag has to be set to 2 inorder to recover. The operating system has enough disk space. Here is the stack trace for the crash. Is there a way to identify the root cause for this crash?

2024-02-20T15:44:17.070850+01:00 3021 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2024-02-20T15:44:17.072376+01:00 3021 [ERROR] [MY-012639] [InnoDB] Write to file .\#innodb_temp\temp_9.ibt failed at offset 3423600640, 1048576 bytes should have been written, only 0 were written. Operating system error number 33. 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.
2024-02-20T15:44:17.074315+01:00 3021 [ERROR] [MY-012640] [InnoDB] Error number 33 means 'Domain error'
2024-02-20T15:44:17.075637+01:00 3021 [Warning] [MY-012145] [InnoDB] Error while writing 4194304 zeroes to .\#innodb_temp\temp_9.ibt starting at offset 3422552064
2024-02-20T15:44:17.076548+01:00 3021 [ERROR] [MY-013132] [Server] The table 'C:\Windows\SERVIC~1\NETWOR~1\AppData\Local\Temp\#sql100c_bcd_38' is full!
2024-02-20T15:44:17.124983+01:00 3022 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2024-02-20T15:44:17.125847+01:00 3022 [Warning] [MY-012145] [InnoDB] Error while writing 4194304 zeroes to .\#innodb_temp\temp_8.ibt starting at offset 801112064
2024-02-20T15:44:17.126754+01:00 3022 [ERROR] [MY-013132] [Server] The table 'C:\Windows\SERVIC~1\NETWOR~1\AppData\Local\Temp\#sql100c_bce_38' is full!
2024-02-20T15:44:21.229098+01:00 3017 [ERROR] [MY-000035] [Server] Disk is full writing '.\redacted-bin.000021' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-02-20T15:44:51.256533+01:00 3028 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-02-20T15:44:51.585328+01:00 3017 [ERROR] [MY-010907] [Server] Error writing file 'redacted-bin' (errno: 28 - No space left on device)
2024-02-20T15:44:51.586284+01:00 3017 [ERROR] [MY-011072] [Server] Binary logging not possible. Message: An error occurred during flush stage of the commit. 'binlog_error_action' is set to 'ABORT_SERVER'. Server is being stopped..
2024-02-20T14:44:51Z UTC - mysqld got exception 0x16 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x27152f3f0d0
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...
7ff75f53c388 mysqld.exe!?my_print_stacktrace@@YAXPEBEK@Z()
7ff75e6af461 mysqld.exe!?print_fatal_signal@@YAXH@Z()
7ff75e6af1a3 mysqld.exe!?my_server_abort@@YAXXZ()
7ff75f5209aa mysqld.exe!?my_abort@@YAXXZ()
7ff75f2dc95c mysqld.exe!?ending_trans@@YA_NPEAVTHD@@_N@Z()
7ff75f2df285 mysqld.exe!?handle_binlog_flush_or_sync_error@MYSQL_BIN_LOG@@AEAAXPEAVTHD@@_NPEBD@Z()
7ff75f2e522d mysqld.exe!?ordered_commit@MYSQL_BIN_LOG@@AEAAHPEAVTHD@@_N1@Z()
7ff75f2daba1 mysqld.exe!?commit@MYSQL_BIN_LOG@@UEAA?AW4enum_result@TC_LOG@@PEAVTHD@@_N@Z()
7ff75e475092 mysqld.exe!?ha_commit_trans@@YAHPEAVTHD@@_N1@Z()
7ff75e6a4b06 mysqld.exe!?trans_commit@@YA_NPEAVTHD@@_N@Z()
7ff75e66740f mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
7ff75e6629bf mysqld.exe!?dispatch_sql_command@@YAXPEAVTHD@@PEAVParser_state@@@Z()
7ff75e6615ba mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z()
7ff75e662d76 mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
7ff75e469bf8 mysqld.exe!?thread_id@THD@@QEBAIXZ()
7ff75fabf189 mysqld.exe!?my_init_dynamic_array@@YA_NPEAUDYNAMIC_ARRAY@@IIPEAXII@Z()
7ff75f52cb7c mysqld.exe!?my_thread_self_setname@@YAXPEBD@Z()
7ffc77cc6b4c ucrtbase.dll!_recalloc()
7ffc79344cb0 KERNEL32.DLL!BaseThreadInitThunk()
7ffc7a2ce8ab ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (27189bb00f0): COMMIT
Connection ID (thread ID): 3017
Status: KILL_CONNECTION

Incorrect string value (1 reply)

Gina-Marie Rollock — Mon, 29 Apr 2024 19:31:44 +0000

I have a table in an InnoDB database.

The character set is utf8mb4 and the collation is utf8mb4_0900_ai_ci. I have a field in the table defined as longtext, whose collation is also utf8mb4_0900_ai_ci.

When I copy the following content:

Man they are on it this week😊

Into this field, I get the following warnings:

1300 - Invalid utf8mb3 character string: 'F09F98'
1366 - Incorrect string value: '\xF0\x9F\x98\x8A' for column 'Action' at row 1

What do I need to do to fix this?

MySQL restarts - mysqld got signal 11 ; (no replies)

Mark Phillips — Tue, 23 Apr 2024 09:12:10 +0000

We host a web app using MySQL on AWS RDS. We recently upgraded to MySQL8 (8.0.28) and since then we are seeing the server restart itself approximately every 5-7 days with the following error.

========
2024-04-22T14:28:25Z mysqld got signal 11 ; ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x152666551800
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...
2024-04-22T14:28:25.081395Z 47526151 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-04-22T14:28:25.087136Z 47526152 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-04-22T14:28:25.111052Z 47526153 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2024-04-22T14:28:25.122618Z 47526154 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
stack_bottom = 1525b9a4b210 thread_stack 0x40000
2024-04-22T14:28:25.141865Z 47526155 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
/rdsdbbin/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x151bd7e]
/rdsdbbin/mysql/bin/mysqld(print_fatal_signal(int)+0x276) [0xc179e6]
/rdsdbbin/mysql/bin/mysqld(handle_fatal_signal+0x7c) [0xc17c7c]
/lib64/libpthread.so.0(+0x118e0) [0x1533428648e0]
/rdsdbbin/mysql/bin/mysqld(has_external_table(Table_ref*)+0x32) [0xb2a512]
/rdsdbbin/mysql/bin/mysqld(Sql_cmd_dml::execute(THD*)+0x66b) [0xb3246b]
/rdsdbbin/mysql/bin/mysqld(mysql_execute_command(THD*, bool)+0x938) [0xaf39f8]
/rdsdbbin/mysql/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x587) [0xaf74f7]
/rdsdbbin/mysql/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x19df) [0xaf95ef]
/rdsdbbin/mysql/bin/mysqld(do_command(THD*)+0x1c9) [0xafa059]
/rdsdbbin/mysql/bin/mysqld() [0xc18b8f]
/rdsdbbin/mysql/bin/mysqld() [0x18cedbf]
/lib64/libpthread.so.0(+0x744b) [0x15334285a44b]
/lib64/libc.so.6(clone+0x3f) [0x15334203f52f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
====

It is always the same query in the error log(but on different databases) which is causing the error, however, when we run the query manually, it is fine. It feels like it is only in certain circumstances, e.g. hitting some kind of memory limit and causing it to fall over? I have read this could be an OS problem causing the OS to run out of memory and kill MySQL.

Can anyone advise? Should we start by reducing the amount of memory MySQL is trying to use, and if so, how best to do that?