MySQL Forums - InnoDB clusters

MySQL 8.4.0 InnoDB Cluster - Group Replication Crash (Exception 0xc0000005) (no replies)

Mahesh Mahawar — Wed, 23 Apr 2025 10:41:07 +0000

We are experiencing a critical issue with MySQL 8.4.0 configured as part of an InnoDB Cluster with Group Replication.

On 2025-04-17 at 18:54:47 UTC, the mysqld process crashed with exception code 0xc0000005 (Access Violation). According to the error log, the issue appears to originate from group_replication.dll. Here's a snippet from the backtrace:

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...
7ffe151f14f3 VCRUNTIME140.dll!memcpy()
7ffde936250f group_replication.dll!???
7ffde9365824 group_replication.dll!???
7ffde938f009 group_replication.dll!???
7ffde93904c0 group_replication.dll!???
7ffde947dede group_replication.dll!???
7ffde947d299 group_replication.dll!???
7ffde94449e0 group_replication.dll!???
7ffde946da4a group_replication.dll!???
7ffde946d2ba group_replication.dll!???
7ffde946e08d group_replication.dll!???
7ffde946e1c6 group_replication.dll!???
7ff69997b1f9 mysqld.exe!?init_state_maps@@YA_NPEAVMY_CHARSET_LOADER@@PEAUCHARSET_INFO@@@Z()
7ff6993cc39c mysqld.exe!?my_thread_self_setname@@YAXPEBD@Z()
7ffe1c32268a ucrtbase.dll!_o_exp()
7ffe20077ac4 KERNEL32.DLL!BaseThreadInitThunk()
7ffe201aa8c1 ntdll.dll!RtlUserThreadStart()
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Is there a way to have a node in the InnoDB cluster only as an "arbiter/quorum" without any data? It would only participate in the cluster for voting purposes. (no replies)

Danilo NETO — Thu, 27 Mar 2025 15:00:19 +0000

I need to setup an InnoDB Cluster with 3 nodes, but one node that doesn't store any actual data but still plays a role in the quorum or voting mechanism.

InndoDB Cluster Single-Primary with 2 nodes in R/W mode (no replies)

Adolfo Flores — Wed, 29 Jan 2025 15:15:53 +0000

Hello everyone,

I have the following scenario with a "Single-Primary" InnoDB Cluster running with 3 nodes all of them recently upgraded from MySQL 8.0.27 to 8.0.40

One of the nodes "hostname1" is a Secondary node but I have the following warning on it: "WARNING: Instance is NOT a PRIMARY but super_read_only option is OFF."

Because I had no logs enabled at the time I can't find out when/how/why the cluster became into this state and I was wondering if this has ever happened to anyone else ? If so, any chance you know what happened and how this can be prevented from happening again?
Also, is it possible that this issue has something to do with the recent upgrade of the mysql versions that allow a Single-Primary cluster to have 2 nodes in R/W mode (when one of them in a Secondary)?

Cluster status:

{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "hostname3:",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"hostname1:": {
"address": "hostname1:",
"instanceErrors": [
"WARNING: Instance is NOT a PRIMARY but super_read_only option is OFF."
],
"memberRole": "SECONDARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.40"
},
"hostname2:": {
"address": "hostname2:",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.40"
},
"hostname3:": {
"address": "hostname3:",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.40"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "hostname3:"
}

How to check istance state from command line (no replies)

Tony Soprano — Fri, 06 Dec 2024 09:30:02 +0000

Hi, i'm trying to run this command from shell but return error and i'm lost
mysqlsh --uri root@inno1 -- cluster check-instance-state

return this error:
ERROR: Argument instance: Invalid connection options, expected either a URI or a Connection Options Dictionary

but this command works fine without any additional parameters:
mysqlsh --uri root@inno1 -- cluster status

What i'm missing? thanks

MySQL InnoDB Cluster does not start automatically after MySQL DB is restarted (no replies)

Thiruchendhuran Selladurai — Wed, 02 Oct 2024 16:19:46 +0000

Hi,

I have deployed a 3-Node MySQL 8.4.1 InnoDB Cluster on Linux 9.2.

Everything seems to work fine.

The issue is MySQL InnoDB cluster does not start automatically after I stop and start the MySQL database in each node.

Everytime I have to use JS> dba.rebootFromCompleteOutage().

Please share how to ensure that the MySQL InnoDB cluster starts automatically when MySQL DB is stopped and started.

Regards,

InnoDb Cluster on docker didn't start (no replies)

Thierry LESIRE — Fri, 06 Sep 2024 06:41:13 +0000

Hello,
I try to create a MySql InnoDB Cluster with containers.
I have 2 VM with Ubuntu 22.04, In each I have a container with MySql 8.0.39
I want to create a cluster with the 2 containers. But I failed to create.

dba.createCluster(clusterName, {localAddress:'mysqlClstr01:3306'})
A new InnoDB Cluster will be created on instance 'mysqlClstr01:3306'.

Disabling super_read_only mode on instance 'mysqlClstr01:3306'.
Validating instance configuration at mysqlClstr01:3306...

This instance reports its own address as mysqlClstr01:3306

Instance configuration is suitable.
* Checking connectivity and SSL configuration...

Creating InnoDB Cluster 'mysqlCluster' on 'mysqlClstr01:3306'...

Adding Seed Instance...
ERROR: Unable to start Group Replication for instance 'mysqlClstr01:3306'.
The MySQL error_log contains the following messages:
2024-08-23 14:40:57.147907 [System] [MY-013587] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'
2024-08-23 14:40:57.148434 [System] [MY-011565] Plugin group_replication reported: 'Setting super_read_only=ON.'
2024-08-23 14:40:57.149175 [Error] [MY-011735] Plugin group_replication reported: '[GCS] There is no local IP address matching the one configured for the local node (mysqlClstr01:3306).'
2024-08-23 14:40:57.149375 [Error] [MY-011674] Plugin group_replication reported: 'Unable to initialize the group communication engine'
2024-08-23 14:40:57.149392 [Error] [MY-011637] Plugin group_replication reported: 'Error on group communication engine initialization'
Dba.createCluster: Group Replication failed to start: MySQL Error 3096 (HY000): mysqlClstr01:3306: The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer. (RuntimeError)

I tried to pass an argument in my docker-compose to set the local address group-replication-local-address without success.

My docker compose:
volumes:
mysqlData:
name: mysqlData
external: true
mysqlLog:
name: mysqlLog
external: true
services:
mysql-server-1:
env_file:
- mysql-server.env
image: mysql:8.0
container_name: mysql-server-1
volumes:
- mysqlData:/var/lib/mysql
- mysqlLog:/var/log/mysql
network_mode: host
ports:
- "3301:3306"
- "3306:3306"
- "33060:33060"
- "33061:33061"
- "33062:33062"
command: ["mysqld","--server_id=1","--binlog_checksum=NONE","--gtid_mode=ON","--enforce_gtid_consistency=ON","--log_bin","--log_replica_updates=ON","--user=mysql","--host_cache_size=0", "--authentication_policy=mysql_native_password","--group-replication-local-address='mysqlClstr01:33061'"]
restart: always
mysql-shell:
env_file:
- mysql-shell.env
image: mysql-shell
container_name: mysql-shell-1
volumes:
- ./scripts/:/scripts/
network_mode: host

Could you please help me?

The maximum throughput supported by Group Replication under specific software and hardware conditions (no replies)

Bin Wang — Tue, 03 Sep 2024 14:03:43 +0000

RAMP-UP TIME.(0 sec.)
MEASURING START.
10, trx: 179372, 95%: 7.134, 99%: 9.097, max_rt: 16.857, 178944|16.256, 17941|5.395, 17932|25.313, 17943|14.180
20, trx: 181215, 95%: 7.216, 99%: 9.228, max_rt: 20.587, 180703|433.405, 18123|4.086, 18122|29.192, 18124|4.922
30, trx: 180596, 95%: 7.410, 99%: 9.463, max_rt: 16.930, 180110|16.705, 18061|3.146, 18056|25.658, 18060|7.392
40, trx: 179032, 95%: 7.594, 99%: 10.575, max_rt: 18.047, 178513|16.862, 17906|3.861, 17900|27.482, 17904|5.724
50, trx: 173856, 95%: 8.199, 99%: 12.705, max_rt: 31.777, 173412|30.990, 17388|9.501, 17388|37.472, 17385|5.538
60, trx: 171950, 95%: 8.499, 99%: 13.721, max_rt: 31.584, 171546|31.808, 17200|9.185, 17188|35.098, 17202|13.781
STOPPING THREADS........................................................................................................................................................................................................

[0] sc:481189 lt:584832 rt:0 fl:0 avg_rt: 5.3 (5)
[1] sc:915412 lt:147816 rt:0 fl:0 avg_rt: 4.4 (5)
[2] sc:106614 lt:5 rt:0 fl:0 avg_rt: 0.8 (5)
[3] sc:106586 lt:0 rt:0 fl:0 avg_rt: 11.9 (80)
[4] sc:106619 lt:0 rt:0 fl:0 avg_rt: 2.1 (20)
in 60 sec.

[0] sc:481239 lt:585014 rt:0 fl:0
[1] sc:918008 lt:148245 rt:0 fl:0
[2] sc:106622 lt:5 rt:0 fl:0
[3] sc:106622 lt:0 rt:0 fl:0
[4] sc:106628 lt:0 rt:0 fl:0
(all must be [OK])
[transaction percentage]
Payment: 43.41% (>=43.0%) [OK]
Order-Status: 4.35% (>= 4.0%) [OK]
Delivery: 4.35% (>= 4.0%) [OK]
Stock-Level: 4.35% (>= 4.0%) [OK]
[response time (at least 90% passed)]
New-Order: 45.14% [NG] *
Payment: 86.10% [NG] *
Order-Status: 100.00% [OK]
Delivery: 100.00% [OK]
Stock-Level: 100.00% [OK]

1066021.000 TpmC

User acces rights with MySQL router (2 replies)

Silvio Schloeffel — Wed, 21 Aug 2024 12:20:01 +0000

Hello,

I'm not sure if this is the right forum, could also be an admin issue.

We would like to realise access to our Innodb cluster with several external routers. This means that no adjustments to the software running on the distributed servers are necessary and the connection to the respective read/write systems can be realised more easily.

The aim is that the users of the respective software can only access the router from localhost.

With a ‘normal’ DB server, I would therefore use users of the type ‘user’@‘ip_or_hostname’ to restrict access.

I can't realise this with the router(s).
For testing I created 5 users with different rules -> test_nutzer_xxx

'test_nutzer'@'%';
'test_nutzer_1'@'localhost';
'test_nutzer_2'@'router-0%';
'test_nutzer_3'@'10.234.16.40';
`test_nutzer_4`@`router-01`;

My router server has the ip 10.234.16.40 and is know as router-01.

Test 1 with test_nutzer:
router local:
[xxx@router-01 ~]$mysql -u test_nutzer -p -h 127.0.0.1 -P 6446
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2464332
Server version: 8.0.39 MySQL Community Server - GPL

connect to router remote system:
xxx@fedora:~$ mysql -u test_nutzer -p -h 10.234.16.46 -P 6446
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2464506
Server version: 8.0.39 MySQL Community Server - GPL

-> works as expected

Test 2 test_nutzer_1:
router local:
[xxx@router-01 ~]$ mysql -u test_nutzer_1 -p -h 127.0.0.1 -P 6446
Enter password:
ERROR 1045 (28000): Access denied for user 'test_nutzer_1'@'router-01' (using password: YES)

connect to router remote system:
xxx@fedora:~$ mysql -u test_nutzer_1 -p -h 10.234.16.46 -P 6446
Enter password:
ERROR 1045 (28000): Access denied for user 'test_nutzer_1'@'router-01' (using password: YES)

-> also as expected because we do not connect from localhost of db server

Test 3 test_nutzer_2:
router local:
[xxx@router-01 ~]$ mysql -u test_nutzer_2 -p -h 127.0.0.1 -P 6446
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2465339
Server version: 8.0.39 MySQL Community Server - GPL

connect to router remote system:
xxx@fedora:~$ mysql -u test_nutzer_2 -p -h 10.234.16.46 -P 6446
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2465305
Server version: 8.0.39 MySQL Community Server - GPL

-> first thing I do not understand:
router local is ok the rule allow connects from user@router-0%
BUT
why can I can connect from a external system?
My thinking is that the reason is the router is rewriting the source address.

Test 4 test_nutzer_3:
router local:
[xxx@router-01 ~]$ mysql -u test_nutzer_3 -p -h 127.0.0.1 -P 6446
Enter password:
ERROR 1045 (28000): Access denied for user 'test_nutzer_3'@'router-01' (using password: YES)

connect to router remote system:
xxx@fedora:~$ mysql -u test_nutzer_3 -p -h 10.234.16.46 -P 6446
Enter password:
ERROR 1045 (28000): Access denied for user 'test_nutzer_3'@'router-01' (using password: YES)

-> only hostname based auth possible?

Test 5 test_nutzer_4 -> only to check a single hostname and not % like test 3
router local:
[xxx@router-01 ~]$ mysql -u test_nutzer_4 -p -h 127.0.0.1 -P 6446
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2466379
Server version: 8.0.39 MySQL Community Server - GPL

connect to router remote system:
xxx@fedora:~$ mysql -u test_nutzer_4 -p -h 10.234.16.46 -P 6446
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2466357
Server version: 8.0.39 MySQL Community Server - GPL

-> result is the same as test 3 - connect possible but also from a external system.
The user from the external system is detected as:
MySQL [(none)]> SELECT USER(), CURRENT_USER();
+-------------------------+-------------------------+
| USER() | CURRENT_USER() |
+-------------------------+-------------------------+
| test_nutzer_4@router-01 | test_nutzer_4@router-01 |
+-------------------------+-------------------------+

Questions:
- How to create a user with limited host rights (router localhost)?
- Is the only hostname based auth in combination with a router "normal"?
- Where is the error in my thinking regarding user generation for this use case?

Thanks for your answers and ideas

Best

Silvio

innodb cluster split (no replies)

pavel rekun — Mon, 13 May 2024 10:55:57 +0000

Hi Guys,

Got myself into a strange scenario - we have 4 nodes innodb cluster
db1 R/W Primary
db2 R Secondary
db3 R Secondary
db4 R Secondary

I needed to run an optimize query on a huge 1.2tb (would save 300GB when completed) so i wanted to run it on each node without writing to binary - but i did a mistake in the query and forgot to add the LOCAL switch to skip logging.
so i ran a command on db4 by switching the read_only=0.
only after db4 completed the optimize (about 8 hours) i noticed my mistake - at this point all other servers db1/db2/db3 started to run the query. the problem is that the Primary R/W instance failed to complete the query with this error "...required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again", while all other servers completed susscefully.
not sure what will happen now if i switch the master to a different server - might break the cluster - or will it fail db1 saying it's missing GTID.
the error says to retry again - but how can i do this? not sure... maybe it's possible to skip it and then i'll run it locally only on db1 - again not sure how can it be done.

so currently the status

db1 R/W Primary OPTIMIZE FAILED
db2 R Secondary OPTIMIZE COMPLETED
db3 R Secondary OPTIMIZE COMPLETED
db4 R Secondary OPTIMIZE COMPLETED

Replication currently works and shows no errors but i see the optimize query still being shown in the process list (altogth it failed - my guess is after a restart of mysql on db1 i will enounter replication fail/error due to this.

My best guess would be that once i restart the db1 instance - master will failover to db2 - when db1 comes up again it will retry running the optimize query - can someone confirm this?

any help would be appreciated.

Preferred Consistency setting for Single Primary Mode (no replies)

GP Gp — Tue, 23 Apr 2024 10:47:50 +0000

Hi

I would like to understand the tradeoffs for using 'BEFORE_ON_PRIMARY_FAILOVER' vs 'BEFORE' consistency level in Single primary mode for innodb cluster.

I want the client applications to always have consistent data.
So if the applications only connect on primary, this is solved by using 'BEFORE_ON_PRIMARY_FAILOVER' consistency level
But I have some use cases where applications only need to connect to read-only port. These are heavy read use cases. Before the cluster setup, these applications used to work on a slave. Now, I want to use secondary nodes to serve these use-cases.

To solve this applications can connect to secondary node through mysql-router. This can lead to connection to any of the secondary nodes. The problem occurs if one application connects to secondary1 at one point and to secondary2 at another point and let's say that secondary2 is lagging behind. This would result in consistency issue since now my application is seeing older data than when it was connected to secondary1.

For this we need to put in 'BEFORE' consistency level which solves my problem. But it can either be set on the session level or it can be set at the cluster Setting on application level which requires application changes which we want to avoid.

So my main question is - What are the consequences of setting the consistency level as 'BEFORE' for single primary mode.
Does is lead to degraded performance of the cluster compared to using 'BEFORE_ON_PRIMARY_FAILOVER'?

One node of cluster does not rejoin after reboot (no replies)

Mirko Lübcke — Thu, 18 Apr 2024 14:32:34 +0000

Hi guys,

i have built a cluster and added three members (2*Win2019 1*Win10). I set the option "autoRejoinTries" to 12 and the option "exitStateAction" to "ABORT_SERVER".

The cluster works fine, i can reboot one of the win2019 machines and they reconnect/rejoin the cluster correctly. But if i reboot the win10 machine, it does not rejoin. It stays in state "offline" with the instance error "group replication is stopped".

------------------------

"topology": {
"MEMBER1:3306": {
"address": "MEMBER1:3306",
"instanceErrors": [
"NOTE: group_replication is stopped."
],
"memberRole": "SECONDARY",
"memberState": "OFFLINE",
"mode": "n/a",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)",
"version": "8.0.36"
},
"MEMBER2:3306": {
"address": "MEMBER2:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.36"
},
"MEMBER3:3306": {
"address": "MEMBER3:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.36"
}
},
"topologyMode": "Single-Primary"

----------------------

Does anyone have a clue, what could be the error? I even set the option explicit to the single instances by "setInstanceOption" but it made no difference.

Regards, Mirko

Mysql Router Error Too many open files (1 reply)

Yevhen Pavliuchek — Fri, 28 Feb 2025 13:01:38 +0000

run into issue - Too many open files
centos, docker swarm, innodb cluster 8.0.36 , mysql router 8.0.36 as one service with two task-containers

default settings of innodb cluster with limits innodb_open_files 4000 and open_files_limit 10000

os
ulimit -n
1024

one of container dont have errors in log, the other one have and finish its work after them

is this issue connected to limit of OS or what ?

why '/tmp/mysqlrouter/data/state.json' for writing: Too many open files
this is local file of mysql router container isnt it ? - why it has so many queries ?

or problem with mysql instance
failed to open socket for 0.0.0.0:6446: Too many open files
isn't this problem already not with container router but with mysql db ?
reached limit 1024 on both servers ? couse this containers run on different nodes of cluster swarm

swarm stack:
mysql-router:
hostname: "mysql-router{{.Task.Slot}}"
entrypoint:
- /bin/sh
- -c
- |
exec /run.sh mysqlrouter
environment:
MYSQL_HOST: mysql1
MYSQL_PORT: 3306
MYSQL_USER: inno
MYSQL_PASSWORD: ${DB_ROOT_PASSWORD}
MYSQL_INNODB_CLUSTER_MEMBERS: 2
# MYSQL_CREATE_ROUTER_USER: 0
MYSQL_ROUTER_BOOTSTRAP_EXTRA_OPTIONS: --conf-set-option=DEFAULT.max_connections=2048 --conf-set-option=DEFAULT.max_total_connections=2048
healthcheck:
<<: *common-healthcheck
test: mysqladmin ping -h127.0.0.1 -P 6446 -u$$MYSQL_USER -p$$MYSQL_PASSWORD

log container
loop until stop container

2024-03-09 03:12:23 metadata_cache INFO [7fc2c0213700] Potential changes detected in cluster after metadata refresh (view_id=0)
2024-03-09 03:12:23 metadata_cache INFO [7fc2c0213700] Metadata for cluster 'Cluster' has 3 member(s), single-primary:
2024-03-09 03:12:23 metadata_cache INFO [7fc2c0213700] mysql1:3306 / 33060 - mode=RW
2024-03-09 03:12:23 metadata_cache INFO [7fc2c0213700] mysql2:3306 / 33060 - mode=RO
2024-03-09 03:12:23 metadata_cache INFO [7fc2c0213700] mysql3:3306 / 33060 - mode=RO
2024-03-09 03:12:23 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_x_ro listening on '0.0.0.0:6449'
2024-03-09 03:12:23 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_x_rw listening on '0.0.0.0:6448'
2024-03-09 03:12:23 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_ro listening on '0.0.0.0:6447'
2024-03-09 03:12:23 routing INFO [7fc2c0213700] Disconnecting client 127.0.0.1:59852 from server
2024-03-09 03:12:23 routing INFO [7fc2c0213700] Disconnecting client 10.0.1.155:49014 from server
2024-03-09 03:12:23 routing INFO [7fc2c0213700] Routing routing:bootstrap_rw listening on '0.0.0.0:6446' got request to disconnect 2 invalid connections: metadata change
2024-03-09 03:12:23 routing WARNING [7fc2c0213700] [routing:bootstrap_rw] failed to open socket for 0.0.0.0:6446: Too many open files
2024-03-09 03:12:23 metadata_cache INFO [7fc2c0213700] Failed refreshing metadata: Could not open dynamic state file '/tmp/mysqlrouter/data/state.json' for writing: Too many open files
2024-03-09 03:12:23 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_x_ro listening on 0.0.0.0:6449
2024-03-09 03:12:23 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_x_rw listening on 0.0.0.0:6448
2024-03-09 03:12:23 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_ro listening on 0.0.0.0:6447
2024-03-09 03:12:23 routing ERROR [7fc2c1257700] connecting to backend failed: not set (destinations:1)
2024-03-09 03:12:24 metadata_cache INFO [7fc2c0213700] Potential changes detected in cluster after metadata refresh (view_id=0)
2024-03-09 03:12:24 metadata_cache INFO [7fc2c0213700] Metadata for cluster 'Cluster' has 3 member(s), single-primary:
2024-03-09 03:12:24 metadata_cache INFO [7fc2c0213700] mysql1:3306 / 33060 - mode=RW
2024-03-09 03:12:24 metadata_cache INFO [7fc2c0213700] mysql2:3306 / 33060 - mode=RO
2024-03-09 03:12:24 metadata_cache INFO [7fc2c0213700] mysql3:3306 / 33060 - mode=RO
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_x_ro listening on '0.0.0.0:6449'
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_x_rw listening on '0.0.0.0:6448'
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_ro listening on '0.0.0.0:6447'
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Disconnecting client 10.0.1.155:49014 from server
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Routing routing:bootstrap_rw listening on '0.0.0.0:6446' got request to disconnect 1 invalid connections: metadata change
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_rw listening on '0.0.0.0:6446'
2024-03-09 03:12:24 metadata_cache INFO [7fc2c0213700] Failed refreshing metadata: Could not open dynamic state file '/tmp/mysqlrouter/data/state.json' for writing: Too many open files
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_x_ro listening on 0.0.0.0:6449
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_x_rw listening on 0.0.0.0:6448
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_ro listening on 0.0.0.0:6447
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_rw listening on 0.0.0.0:6446
2024-03-09 03:12:24 metadata_cache INFO [7fc2c0213700] Potential changes detected in cluster after metadata refresh (view_id=0)
2024-03-09 03:12:24 metadata_cache INFO [7fc2c0213700] Metadata for cluster 'Cluster' has 3 member(s), single-primary:
2024-03-09 03:12:24 metadata_cache INFO [7fc2c0213700] mysql1:3306 / 33060 - mode=RW
2024-03-09 03:12:24 metadata_cache INFO [7fc2c0213700] mysql2:3306 / 33060 - mode=RO
2024-03-09 03:12:24 metadata_cache INFO [7fc2c0213700] mysql3:3306 / 33060 - mode=RO
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_x_ro listening on '0.0.0.0:6449'
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_x_rw listening on '0.0.0.0:6448'
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_ro listening on '0.0.0.0:6447'
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Disconnecting client 10.0.1.155:49014 from server
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Routing routing:bootstrap_rw listening on '0.0.0.0:6446' got request to disconnect 1 invalid connections: metadata change
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_rw listening on '0.0.0.0:6446'
2024-03-09 03:12:24 metadata_cache INFO [7fc2c0213700] Failed refreshing metadata: Could not open dynamic state file '/tmp/mysqlrouter/data/state.json' for writing: Too many open files
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_x_ro listening on 0.0.0.0:6449
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_x_rw listening on 0.0.0.0:6448
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_ro listening on 0.0.0.0:6447
2024-03-09 03:12:24 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_rw listening on 0.0.0.0:6446
2024-03-09 03:12:25 metadata_cache INFO [7fc2c0213700] Potential changes detected in cluster after metadata refresh (view_id=0)
2024-03-09 03:12:25 metadata_cache INFO [7fc2c0213700] Metadata for cluster 'Cluster' has 3 member(s), single-primary:
2024-03-09 03:12:25 metadata_cache INFO [7fc2c0213700] mysql1:3306 / 33060 - mode=RW
2024-03-09 03:12:25 metadata_cache INFO [7fc2c0213700] mysql2:3306 / 33060 - mode=RO
2024-03-09 03:12:25 metadata_cache INFO [7fc2c0213700] mysql3:3306 / 33060 - mode=RO
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_x_ro listening on '0.0.0.0:6449'
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_x_rw listening on '0.0.0.0:6448'
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_ro listening on '0.0.0.0:6447'
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Disconnecting client 10.0.1.155:49014 from server
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Routing routing:bootstrap_rw listening on '0.0.0.0:6446' got request to disconnect 1 invalid connections: metadata change
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_rw listening on '0.0.0.0:6446'
2024-03-09 03:12:25 metadata_cache INFO [7fc2c0213700] Failed refreshing metadata: Could not open dynamic state file '/tmp/mysqlrouter/data/state.json' for writing: Too many open files
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_x_ro listening on 0.0.0.0:6449
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_x_rw listening on 0.0.0.0:6448
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_ro listening on 0.0.0.0:6447
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_rw listening on 0.0.0.0:6446
2024-03-09 03:12:25 metadata_cache INFO [7fc2c0213700] Potential changes detected in cluster after metadata refresh (view_id=0)
2024-03-09 03:12:25 metadata_cache INFO [7fc2c0213700] Metadata for cluster 'Cluster' has 3 member(s), single-primary:
2024-03-09 03:12:25 metadata_cache INFO [7fc2c0213700] mysql1:3306 / 33060 - mode=RW
2024-03-09 03:12:25 metadata_cache INFO [7fc2c0213700] mysql2:3306 / 33060 - mode=RO
2024-03-09 03:12:25 metadata_cache INFO [7fc2c0213700] mysql3:3306 / 33060 - mode=RO
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_x_ro listening on '0.0.0.0:6449'
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_x_rw listening on '0.0.0.0:6448'
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_ro listening on '0.0.0.0:6447'
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Disconnecting client 10.0.1.155:49014 from server
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Routing routing:bootstrap_rw listening on '0.0.0.0:6446' got request to disconnect 1 invalid connections: metadata change
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Start accepting connections for routing routing:bootstrap_rw listening on '0.0.0.0:6446'
2024-03-09 03:12:25 metadata_cache INFO [7fc2c0213700] Failed refreshing metadata: Could not open dynamic state file '/tmp/mysqlrouter/data/state.json' for writing: Too many open files
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_x_ro listening on 0.0.0.0:6449
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_x_rw listening on 0.0.0.0:6448
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_ro listening on 0.0.0.0:6447
2024-03-09 03:12:25 routing INFO [7fc2c0213700] Stop accepting connections for routing routing:bootstrap_rw listening on 0.0.0.0:6446
2024-03-09 03:12:25 main SYSTEM [7fc2c2820700] Stopping 'MySQL Router', version: 8.0.36 (MySQL Community - GPL), reason: REQUESTED (Signal Terminated sent by UID: 0 and PID: 0)
2024-03-09 03:12:26 routing INFO [7fc28a7fc700] [routing:bootstrap_x_rw] stopped
2024-03-09 03:12:26 routing INFO [7fc28affd700] [routing:bootstrap_x_ro] stopped
2024-03-09 03:12:26 routing INFO [7fc28bfff700] [routing:bootstrap_ro] stopped
2024-03-09 03:12:26 routing ERROR [7fc2c0a56700] connecting to backend failed: not set (destinations:1)
2024-03-09 03:12:26 routing INFO [7fc28b7fe700] [routing:bootstrap_rw] stopped

MySQL cluster hang: primary has many process "waiting for handler commit" (no replies)

Liang Cheng — Fri, 08 Mar 2024 01:09:32 +0000

Today, we first noticed that the MySQL innodb cluster hangs.
1. Show full processlist, we can see many processes are on "waiting for handler commit" state, following are some of them
530400 user1 db03:43736 test_db02 Query 5230 waiting for handler commit DELETE FROM JGROUPSPING WHERE own_addr='5187608d-3145-5c15-de6f-5c3dfae1b09e' AND cluster_name='7fffffff-8305-e317-8000-0000175d3b33_aaa'
329681185 user1 db03:35106 test_db02 Query 5175 waiting for handler commit DELETE FROM JGROUPSPING WHERE own_addr='4c5392f1-5cfe-fbf3-0d79-ee6c98a44b69' AND cluster_name='7fffffff-8305-e317-8000-0000175d3b33_aaa'
329709428 user1 db03:58476 test_db02 Query 5176 waiting for handler commit DELETE FROM JGROUPSPING WHERE own_addr='edb7cfa3-89e0-96e6-d534-58e7f25db8af' AND cluster_name='7fffffff-8305-e317-8000-0000175d3b33_aaa'
329867929 user1 db03:51294 test_db02 Query 5221 waiting for handler commit commit
332720693 user1 db03:33494 test_db03 Query 4391 waiting for handler commit commit
333021837 user1 db03:36458 test_db02 Query 31 updating UPDATE ....
333021838 user1 db03:36462 test_db02 Query 5216 waiting for handler commit commit

2. On two slaves logs, they are full of message as below. And mysqld.log takes more than 5 Gb within two hours
2024-03-07T01:03:12.440902Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451309Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451333Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451343Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451363Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451370Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451378Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451386Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.
2024-03-07T01:03:12.451393Z 23 [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master '' binary log file, at position 3811062876200.

3. We tried to stop mysql server on slaves, stopping hangs. We have to kill them
4. We tried to stop mysql server on primary db, and stopping hangs as well. We have to kill them
5. We tried to start primary db, it succeed.
6. We tried to start slaves, the group_replication fails.
2024-03-07T03:40:27.230448Z 0 [ERROR] [MY-013467] [Repl] Plugin
group_replication reported: 'No valid or ONLINE members exist to get the
missing data from the group. For cloning check if donors of the same version
and with clone plugin installed exist. For incremental recovery check if you
have donors where the required data was not purged from the binary logs.'

2024-03-07T03:40:27.230542Z 0 [ERROR] [MY-011712] [Repl] Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'
7. Then, we installed clone plugin on primary db and one of the slave dbs. Slaves can join to the cluster. (NOTE: I don't know why one slave has clone plugin installed, but another slave and primary db doesn't)
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

In the past 3 years, we used mysql5.7 innodb cluster, and we haven't encounter such issue before. Last month, we steup this new MySQL8 cluster environment, and meet this issue for the first time.

Not sure if it's one bug. More important, if we meet this issue again, how could we workaround it ASAP.

Using Clone for Cluster.addInstance uses 100% Memory (no replies)

Marvin Warble — Mon, 04 Mar 2024 15:35:05 +0000

When trying to add a new node to a MySQL Innodb cluster using Cluster.addInstance() and selecting the Clone option, within 2 or 3 minutes the donor nodes memory usages hits 100% on a server with 36Gb of memory. At 100% memory usage the server becomes unresponsive and the recipient server moves to another server in the cluster, tries to clone, and the memory usage of the 2nd donor hits 100%. The entire cluster is degraded in under 10 min and unavailable.

This is on a Production server so the consequences here are pretty devastating.

I'm running MySQL 8.0.35-27 on Debian 12 Bookworm.

I have not encountered this problem setting up a test cluster with a small amount of data, which I have done several times to ensure I'm doing everything correctly. However on my Production server with about 125Gb of data, this problem occurs consistently.

Has anyone else experienced this problem or know what the cause might be?

Thank you.

mysqlrouters doesn't listen on port 6446 RW (4 replies)

Tony Soprano — Tue, 07 May 2024 07:13:11 +0000

Hi all, i install on 3 vm ubuntu22 innodbcluster and it works fine here the result of cluster.status()
{
"clusterName": "medhitcluster",
"defaultReplicaSet": {
"name": "default",
"primary": "inno1:3306",
"ssl": "DISABLED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"inno1:3306": {
"address": "inno1:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.3.0"
},
"inno2:3306": {
"address": "inno2:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.3.0"
},
"inno3:3306": {
"address": "inno3:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.3.0"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "inno1:3306"
###############################################################

but after i bootstarp router on a different vm using:
mysqlrouter --ssl-mode=disabled --bootstrap root@inno1 --directory /etc/mysqlrouter --user=root --account=router1

it listen only on RO ports 6447,6449,8443.

here the log of mysqlrouter please help me:

root@inno2:~# tail -80 /etc/mysqlrouter/log/mysqlrouter.log
2024-02-23 14:21:32 main SYSTEM [7fd6775b9d80] Starting 'MySQL Router', version: 8.0.36 ((Ubuntu))
2024-02-23 14:21:32 io INFO [7fd6775b9d80] starting 4 io-threads, using backend 'linux_epoll'
2024-02-23 14:21:32 http_server INFO [7fd6775b9d80] listening on 0.0.0.0:8443
2024-02-23 14:21:32 metadata_cache_plugin INFO [7fd65dffb640] Starting Metadata Cache
2024-02-23 14:21:32 metadata_cache INFO [7fd65dffb640] Connections using ssl_mode 'disabled'
2024-02-23 14:21:32 metadata_cache INFO [7fd674457640] Starting metadata cache refresh thread
2024-02-23 14:21:32 metadata_cache INFO [7fd674457640] Connected with metadata server running on inno1:3306
2024-02-23 14:21:32 routing INFO [7fd64dffb640] [routing:bootstrap_ro] started: routing strategy = round-robin-with-fallback
2024-02-23 14:21:32 routing INFO [7fd64dffb640] Start accepting connections for routing routing:bootstrap_ro listening on '0.0.0.0:6447'
2024-02-23 14:21:32 routing INFO [7fd64d7fa640] [routing:bootstrap_rw] started: routing strategy = first-available
2024-02-23 14:21:32 routing INFO [7fd64d7fa640] Start accepting connections for routing routing:bootstrap_rw listening on '0.0.0.0:6446'
2024-02-23 14:21:32 routing INFO [7fd64cff9640] [routing:bootstrap_x_ro] started: routing strategy = round-robin-with-fallback
2024-02-23 14:21:32 routing INFO [7fd64cff9640] Start accepting connections for routing routing:bootstrap_x_ro listening on '0.0.0.0:6449'
2024-02-23 14:21:32 routing INFO [7fd647fff640] [routing:bootstrap_x_rw] started: routing strategy = first-available
2024-02-23 14:21:32 routing INFO [7fd647fff640] Start accepting connections for routing routing:bootstrap_x_rw listening on '0.0.0.0:6448'
2024-02-23 14:21:32 metadata_cache INFO [7fd674457640] Potential changes detected in cluster after metadata refresh (view_id=0)
2024-02-23 14:21:32 metadata_cache INFO [7fd674457640] Metadata for cluster 'medhitcluster' has 3 member(s), single-primary:
2024-02-23 14:21:32 metadata_cache INFO [7fd674457640] inno1:3306 / 33060 - mode=RO
2024-02-23 14:21:32 metadata_cache INFO [7fd674457640] inno2:3306 / 33060 - mode=RO
2024-02-23 14:21:32 metadata_cache INFO [7fd674457640] inno3:3306 / 33060 - mode=RO
2024-02-23 14:21:32 routing INFO [7fd674457640] Stop accepting connections for routing routing:bootstrap_x_rw listening on 0.0.0.0:6448
2024-02-23 14:21:32 routing INFO [7fd674457640] Stop accepting connections for routing routing:bootstrap_rw listening on 0.0.0.0:6446

Member fencing in group replication (no replies)

Marjus Cako — Tue, 12 Dec 2023 11:54:25 +0000

I was reading this article https://dev.mysql.com/blog-archive/automatic-member-fencing-with-offline_mode-in-group-replication/ about member fencing in MySQL, and while I tried this, I bumped into something unexpected from a first glance.

I have a cluster with 3 instances configured in a group replication InnoDB cluster, with exit action to be ABORT_SERVER. I create a network isolation for one of the nodes. Following I describe what I see in a chronological order:

1. I make an insert from the primary, which hangs and I cancel after a while.
2. After a while, I see from the logs of the primary that the isolated member has been removed from the group.
3. I insert in the primary a new entry in my table.
4. I read from the secondary the same table, and the read operation is executed but my data are stale!
5. After a while the secondary reports that it cannot leave the group gracefully and a SHUTDOWN signal is send to the server.

The exit state action is ABORT_SERVER. I understand that this action is supposed to run after some time, in which the replica has established that it is in the minority partition, and needs to exit from the group.

However if the time it takes for the replica to understand that it should fence is longer that the time for the primary to remove that replica [which is the above case], then this is **not** an actual fencing, and I will have a read-after-right discrepancy in this in-between time.

Is there a way or a set of settings to ensure actual member fencing in the group replication scenario?

I am happy to post more details and logs for my scenario if this helps.

Question about Innodb Cluster Failure condition (1 reply)

Junaid Zeeshan — Tue, 05 Dec 2023 06:44:57 +0000

I am running MySQL innodb cluster with multi master group replication + MySQL router to distribute traffic. I want to know at which condition cluster will stop work and we can face outage. I have 5 nodes cluster in my dev environment and according to documentation if half number of the nodes will be down cluster will not work.
I tried to produce this failed scenario but couldn't succeed. I used multiple ways to down 3 nodes out of 5 but cluster still works for read and write through mysql router. I stopped group replication, killed mysql service or rebooted 3 servers but cluster still works.
Can you please guide, how can we test this failed scenario of cluster when more than half number of nodes are down.

Secondly, for production we have two data centers and we have plan to deploy four nodes in each data center. So if one data center is down, we still have four nodes, but according to documentation if 50% nodes are down, cluster should not work. Please suggest if we should deploy 9th node in AWS to make cluster up if one of the data center is down.

Looking forward your earliest response as have short time to prepare architecture of our production environment. thank you

mysql server slowly eats memories when using InnoDB Cluster (no replies)

takumi koba — Tue, 28 Nov 2023 09:41:39 +0000

We have set up a group replication with three servers using a single-primary mode. Right after the cluster was established, each MySQL Server was utilizing approximately 500MB of memory. However, upon checking one week later, it was observed that all servers were using around 2.6GB of memory each. No significant data inserts were performed during this one-week period. Currently, it seems that the increase in memory usage by the MySQL Server has come to a halt.

We would like to understand whether this phenomenon is normal when using InnoDB Cluster. Additionally, if it is normal, we are interested in knowing how the memory is being allocated.

The performance specifications of the servers in use are as follows:
===========================
OS:Ubuntu 20.04.6 LTS
Memory:4GB
mysql Ver 8.0.35-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
===========================

This is the result of executing systemctl status mysql.
===========================
systemctl status mysql

● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2023-11-20 17:07:35 JST; 1 weeks 1 days ago
Main PID: 220333 (mysqld)
Status: "Server is operational"
Tasks: 57 (limit: 4625)
Memory: 2.6G
CGroup: /system.slice/mysql.service
└─220333 /usr/sbin/mysqld
===========================

Please give us some advice.

MySQL: Transparent read/write splitting (no replies)

Edwin Desouza — Thu, 26 Oct 2023 17:45:54 +0000

MySQL: Transparent read/write splitting
- https://blogs.oracle.com/mysql/post/mysql-82-transparent-readwrite-splitting

Innodb Cluster 8.1 (GTID) (2 replies)

David Martinez — Tue, 31 Oct 2023 04:06:19 +0000

Good morning forum,

we are just creating a brand new mysql 8.1 Innodb cluster ( We know its the innovarion release) withing a single node.

As I can see the innodb cluster is created successfully but we see something strange on the GTID.

Let me share:

These are the variables regarding UUID
+------------------------------------+--------------------------------------+
| Variable_name | Value |
+------------------------------------+--------------------------------------+
| group_replication_view_change_uuid | 473db0e1-5c17-11ee-a13e-4e08e54f71b0 |
| server_uuid | 40c52865-5c17-11ee-866f-4e08e54f71b0 |
+------------------------------------+--------------------------------------+

So nothing special, I can see an UUID for the server itself and another UUID for the cluster itself.

When I check the GTID executed on that node we can see:

| gtid_executed | 40c52865-5c17-11ee-866f-4e08e54f71b0:1-10,
473dadb5-5c17-11ee-a13e-4e08e54f71b0:1-73,
473db0e1-5c17-11ee-a13e-4e08e54f71b0:1 |

In somehow this is explained in the next way:
40c52865-5c17-11ee-866f-4e08e54f71b0:1-10, -- Transactions executed before cluster creation. Create user, keyring udf functions, ...

473dadb5-5c17-11ee-a13e-4e08e54f71b0:1-73, -- Checking the binary logs I can see these transactions are creating the table of innodb cluster like mysql_innodb_cluster_metadata, DROP VIEW IF EXISTS schema_version... so these transactions are cluster wide

but, where is this UID coming from:

473db0e1-5c17-11ee-a13e-4e08e54f71b0:1

Binlog info

/*!*/;
# at 1364
#230926 2:49:14 server id 901 end_log_pos 1450 CRC32 0x1cee0146 GTID last_committed=4 sequence_number=5 rbr_only=no original_committed_timestamp=1695696554711185 immediate_commit_timestamp=1695696554711465 transaction_length=377
# original_commit_timestamp=1695696554711185 (2023-09-26 02:49:14.711185 UTC)
# immediate_commit_timestamp=1695696554711465 (2023-09-26 02:49:14.711465 UTC)
/*!80001 SET @@session.original_commit_timestamp=1695696554711185*//*!*/;
/*!80014 SET @@session.original_server_version=80100*//*!*/;
/*!80014 SET @@session.immediate_server_version=80100*//*!*/;
SET @@SESSION.GTID_NEXT= '473db0e1-5c17-11ee-a13e-4e08e54f71b0:1'/*!*/;
# at 1450
#230926 2:49:14 server id 901 end_log_pos 1521 CRC32 0x5e2a370a Query thread_id=34 exec_time=0 error_code=0
SET TIMESTAMP=1695696554/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=2/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=224,@@session.collation_connection=224,@@session.collation_server=224/*!*/;
BEGIN
/*!*/;
# at 1521
#230926 2:49:14 server id 901 end_log_pos 1664 CRC32 0xb2ec9885 View_change_log_event: view_id=16956965547108570:1
# at 1664
#230926 2:49:14 server id 901 end_log_pos 1741 CRC32 0x5a3b0eae Query thread_id=34 exec_time=0 error_code=0
SET TIMESTAMP=1695696554/*!*/;
COMMIT

*UPDATE*

Sorry for my confusion, looks like this uuid 473db0e1-5c17-11ee-a13e-4e08e54f71b0 its the cluster uuid, then what I dont understand is the transactions from 473dadb5-5c17-11ee-a13e-4e08e54f71b0 because that UID is not the server uuid neither the cluster uuid.

MySQL Replication Observability (no replies)

Edwin Desouza — Thu, 24 Aug 2023 15:56:13 +0000

MySQL Replication Observability
- https://blogs.oracle.com/mysql/post/mysql-8-and-replication-observability

MySQL InnoDB Cluster: Read Replicas (no replies)

Edwin Desouza — Tue, 15 Aug 2023 03:24:23 +0000

MySQL InnoDB Cluster: Read Replicas
- https://blogs.oracle.com/mysql/post/announcing-mysql-innodb-cluster-read-replicas

MySQL InnoDB Cluster - Node lagging behind - election process (no replies)

Carlos Abrantes — Wed, 09 Aug 2023 17:04:47 +0000

Hi All,

After deploying InnoDB Cluster with MySQL version 8.0.33 via MySQL Operator i was trying to understand if a node that is lagging could be elected as Primary.

{
"clusterName": "mysql",
"defaultReplicaSet": {
"name": "default",
"primary": "mysql-2.mysql-instances.mysql.svc.cluster.local:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"mysql-0.mysql-instances.mysql.svc.cluster.local:3306": {
"address": "mysql-0.mysql-instances.mysql.svc.cluster.local:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "00:05:34.192010",
"role": "HA",
"status": "ONLINE",
"version": "8.0.33"
},
"mysql-1.mysql-instances.mysql.svc.cluster.local:3306": {
"address": "mysql-1.mysql-instances.mysql.svc.cluster.local:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.33"
},
"mysql-2.mysql-instances.mysql.svc.cluster.local:3306": {
"address": "mysql-2.mysql-instances.mysql.svc.cluster.local:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.33"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "mysql-2.mysql-instances.mysql.svc.cluster.local:3306"
}

The documentation i found don't state that lagging behind will prevent a node to be used as primary, something that can be critical in many use cases, can you please confirm if my understanding is correct?

For me makes since to at least be able to configure the behaviour of a lagging node if should be consider in Primary election or not, basically a threshold after which the node would not be participating in Primary election.

I was not able to find any reference to such behaviour, can you please clarify if it exists, where can i read about it, or if not consider this a Feature Request?

Version:
community-operator:8.0.33-2.0.10
community-server:8.0.33
community-router:8.0.33

apiVersion: v2
appVersion: 8.0.33
description: MySQL InnoDB Cluster Helm Chart for deploying MySQL InnoDB Cluster in Kubernetes
icon: https://labs.mysql.com/common/themes/sakila/favicon.ico
name: mysql-innodbcluster
type: application
version: 2.0.10

Thanks,

Innodb Cluster.status showing group replication failed (no replies)

Sunil Potluri — Tue, 18 Jul 2023 10:36:46 +0000

Dear Team,

We have Mysql Innodb cluster with group replication of 3 nodes. After a code deployment in applicaiton reaplication stopped and current cluster status is showing below error.

Version is 8.0.32.

cluster.status();
{
"clusterName": "pr_cls",
"clusterRole": "PRIMARY",
"defaultReplicaSet": {
"name": "default",
"primary": "node2p:1543",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE_PARTIAL",
"statusText": "Cluster is NOT tolerant to any failures. 2 members are not active.",
"topology": {
"node1p:1543": {
"address": "node1p:1543",
"instanceErrors": [
"ERROR: applier thread of Group Replication Applier channel stopped with an error: Worker 1 failed executing transaction 'e3a79e26-f954-11ed-b6c5-0050569602ab:6620'; Error 'This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)' on query. Default database: 'emsigner_prd'. Query: 'CREATE DEFINER=`root`@`localhost` FUNCTION `FGetCustomercompanyCIF`(
P_Workflowid varchar(500)
) RETURNS varchar(750) CHARSET utf8mb4
BEGIN
.
.
.
.
.
RETURN ifnull(V_CompanyCIF,null);
END' (1418) at 2023-07-13 16:25:44.995660",
"ERROR: group_replication has stopped with an error."
],
"memberRole": "SECONDARY",
"memberState": "ERROR",
"mode": "n/a",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)",
"version": "8.0.32"
},

Please let me know how I can fix the replication issue and avoid recurring in future.. if at all, application team wants to run DDL commands.

MySQL 8.0: Integrated Certificate-based & Password-less Authentication in InnoDB ClusterSet (no replies)

Edwin Desouza — Fri, 30 Jun 2023 00:56:44 +0000

MySQL 8.0: Integrated Certificate-based & Password-less Authentication in InnoDB ClusterSet
- https://blogs.oracle.com/mysql/post/mysql-innodb-clusterset-certificate-based-password-less-authentication

MySQL Shell 8.0.33: InnoDB ReplicaSet/Cluster/ClusterSet (no replies)

Edwin Desouza — Thu, 27 Apr 2023 02:39:38 +0000

MySQL Shell 8.0.33: InnoDB ReplicaSet/Cluster/ClusterSet

https://blogs.oracle.com/mysql/post/announcing-mysql-shell-8033

Question about Quorum (1 reply)

Kai Koppelmann — Wed, 19 Apr 2023 05:18:18 +0000

Hello,

I am testing some scenarios with our InnoDB Cluster.
Our environment is a 3 node InnoDB Cluster based on version 8.0.32.

All works well - we implemented MySQL Router on application side, connection works like a charm.

I now wanted to test some failure scenarios and did not get the result I excpected. I turned off two out of three machines hosting the InnoDB instances.

As excpected MySQL Router & the Error Log of MySQL reported two failed nodes.
cluster.status() reported status "MISSING".

If 2 out of 3 members are offline/missing I assumed a complete cluster outage due to quorum loss. But... the cluster remains online with a single r/w instance and I am able to place transactions inside of this single instance.

Why the InnoDB cluster is not going down due to quorum loss?

Many regards
Kai

Is there an example of Connection Sharing released in MySQL Router 8.0.32? (no replies)

place rock — Fri, 27 Jan 2023 08:28:28 +0000

The Connection Sharing document states that SQL statements that rely on the previous session state will not work when Connection Sharing is enabled unless it is inside the transaction.

It also does not support user variables, temporary tables, or prepared statements, and even transactions and LOCK TABLES do not support Connection Sharing until they are closed or unlocked.

So, what do they share? I would appreciate it if you could let me know if there is a suitable example.

Thank you

Failed when adding instance to innodb cluster (2 replies)

Jinwoo Jun — Thu, 29 Dec 2022 02:20:20 +0000

I am facing fail when adding instance to innodb cluster.
I can't find a way to solve this problem.

Problem explain:
1. I prepared four VM(virutal machine) :
1 for MySQL Router : server name 'ic_r1'
3 for MySQL InnoDB Cluster : server anem 'ic_s1, s2, s3'

2. Install program ( program version : MySQL Community 8.0.31 )
ic_r1 : installed 'MySQL Shell'
ic_s1, s2, s3 : installed 'MySQL' and 'MySQL Shell'

3. make db user for InnoDB Cluster
create user 'cadmin' on ic_s1, s2, s3

4. Set configuration for ic_s1, s2, s3
dba.configureInstance(...)

5. create InnoDB Cluster on ic_s1
dba.createCluster(...)

6. add instance on created InnoDB Cluster : failed
. failed log
--------------------------------------------------------
MySQL khpesdb01mca01:33060+ ssl JS > cls.addInstance('cadmin@khpesdb03mca01')

NOTE: The target instance 'khpesdb03mca01:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'khpesdb03mca01:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

Please select a recovery method [C]lone/ncremental recovery/[A]bort (default Clone): i
Validating instance configuration at khpesdb03mca01:3306...

This instance reports its own address as khpesdb03mca01:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'khpesdb03mca01:3306'. Use the localAddress option to override.

A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

ERROR: Unable to start Group Replication for instance 'khpesdb03mca01:3306'.
The MySQL error_log contains the following messages:
2022-12-27 08:24:09.369424 [System] [MY-013587] Plugin group_replication reported: 'Plugin 'group_replication' is starting.'
2022-12-27 08:24:09.380888 [System] [MY-010597] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2022-12-27 08:24:39.531922 [Error] [MY-011735] Plugin group_replication reported: '[GCS] Timeout while waiting for the group communication engine to be ready!'
2022-12-27 08:24:39.531981 [Error] [MY-011735] Plugin group_replication reported: '[GCS] The group communication engine is not ready for the member to join. Local port: 3306'
2022-12-27 08:24:39.577422 [Error] [MY-011735] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 3306'
2022-12-27 08:24:45.616098 [Error] [MY-011735] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 3306'
2022-12-27 08:24:45.661331 [Error] [MY-011735] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 3306'
2022-12-27 08:24:51.695701 [Error] [MY-011735] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 3306'
2022-12-27 08:24:51.738338 [Error] [MY-011735] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 3306'
2022-12-27 08:24:57.774530 [Error] [MY-011735] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 3306'
2022-12-27 08:24:57.821827 [Error] [MY-011735] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 3306'
2022-12-27 08:25:03.852152 [Error] [MY-011735] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 3306'
2022-12-27 08:25:03.899604 [Error] [MY-011735] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 3306'
2022-12-27 08:25:09.399855 [Error] [MY-011640] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2022-12-27 08:25:09.399916 [Error] [MY-011735] Plugin group_replication reported: '[GCS] The member is already leaving or joining a group.'
2022-12-27 08:25:09.931778 [Error] [MY-011735] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 3306'
2022-12-27 08:25:09.976294 [Error] [MY-011735] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 3306'

---------------------------------

please give a hit to me.

thanks. jinwoo jun

upgrade mysql5.7 to mysql8 (no replies)

Liang Cheng — Wed, 07 Dec 2022 13:46:34 +0000

Hi,
We have one mysql5.7 innodb cluster (single-primary) environment on EKS. Today, we pick up the mysql8 image, and let mysql upgrade automatically.
The enviornment has one primary and two slaves. The result is that primary and one slave can be upgraded to mysql8 successfully. HOwever, one slave is failed with following error. What does this mean and could be the cause
2022-12-07T11:03:57.231962Z 4 [ERROR] [MY-012119] [InnoDB] Cannot rename './mysql/#sql-ib3584-1767583560.ibd' to './mysql/slave_relay_log_info.ibd' for space ID 2584 because the target file exists. Remove the target file and try again.
2022-12-07T11:03:57.231993Z 4 [ERROR] [MY-013183] [InnoDB] Assertion failure: handler0alter.cc:7009:error == DB_SUCCESS thread 140216232716032
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.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2022-12-07T11:03:57Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=cc730f8d2780f63e4aa3ca1a37aff8fd980c5bed
Thread pointer: 0x560abedc63e0
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 = 7f86a2be1bd0 thread_stack 0x100000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x560ab93dc2fd]
/usr/sbin/mysqld(print_fatal_signal(int)+0x393) [0x560ab82be4a3]
/usr/sbin/mysqld(my_server_abort()+0x7e) [0x560ab82be5ee]
/usr/sbin/mysqld(my_abort()+0xa) [0x560ab93d64fa]
/usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x336) [0x560ab96c1746]
/usr/sbin/mysqld(bool ha_innobase::commit_inplace_alter_table_impl(TABLE*, Alter_inplace_info*, bool, dd::Table*)+0x23d4) [0x560ab950dc94]
/usr/sbin/mysqld(ha_innobase::commit_inplace_alter_table(TABLE*, Alter_inplace_info*, bool, dd::Table const*, dd::Table*)+0x9e) [0x560ab950ea6e]
/usr/sbin/mysqld(+0x105f80d) [0x560ab81f580d]
/usr/sbin/mysqld(mysql_alter_table(THD*, char const*, char const*, HA_CREATE_INFO*, TABLE_LIST*, Alter_info*)+0x57b5) [0x560ab820c845]
/usr/sbin/mysqld(Sql_cmd_alter_table::execute(THD*)+0x4db) [0x560ab861eb6b]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0xafe) [0x560ab81583ae]
/usr/sbin/mysqld(+0xfed587) [0x560ab8183587]
/usr/sbin/mysqld(Prepared_statement::execute_server_runnable(THD*, Server_runnable*)+0x212) [0x560ab8184f22]
/usr/sbin/mysqld(Ed_connection::execute_direct(Server_runnable*)+0xa3) [0x560ab8186623]
/usr/sbin/mysqld(Ed_connection::execute_direct(MYSQL_LEX_STRING)+0x3b) [0x560ab81867fb]
/usr/sbin/mysqld(+0x21f2aaf) [0x560ab9388aaf]
/usr/sbin/mysqld(dd::upgrade::upgrade_system_schemas(THD*)+0xde8) [0x560ab938d6d8]
/usr/sbin/mysqld(+0x11e8736) [0x560ab837e736]
/usr/sbin/mysqld(+0x29d29b5) [0x560ab9b689b5]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7ea7) [0x7f86afa36ea7]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7f86af625a2f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f86002cb228): # Columns added to keep information about REQUIRE_TABLE_PRIMARY_KEY_CHECK replication field ALTER TABLE slave_relay_log_info ADD Require_table_primary_key_check ENUM('STREAM','ON','OFF') NOT NULL DEFAULT 'STREAM' COMMENT 'Indicates what is the channel policy regarding tables having primary keys on create and alter table queries' AFTER Require_row_format
Connection ID (thread ID): 4