Laravel/PHP with charset or collation type breaks cluster

MikeBerry · January 23, 2023, 7:35pm

Hi, long shot here, but…

We are using Laravel 5.8 / PHP 7.2 with Percona XtraDB Cluster 8.

The Dev team are running migrations constantly with create table inside the migration, for tables that already exist. They are starting to add ‘If table not exist then do create table’ to new migrations but there is a lot of code already done, and there wasn’t a problem with a single mysql server, so they are loathe to update all the code.

When they run a create table for a table that already exists then if they specify a different charset or collation type to the mysql default then the node drops out. This only happens using laravel/php. Using workbench or anything else returns an error as it should and the cluster keeps working.

This code in Laravel/PHP makes the node drop out of the cluster?
create table brokers (name varchar(255) not null) default character set utf8 collate ‘utf8_unicode_ci’;

But this one doesn’t…
create table brokers (name varchar(255) not null)');

Both times the error code SQLSTATE (42S01) Base table already exists is returned which is correct.

Thanks
Mike

This is the mysql err log when the node drops out of the cluster;

============================
2023-01-23T15:05:38.696342Z 0 [Note] [MY-000000] [Galera] Member 2(dev-pxc-cluster-node-1) initiates vote on 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051688,96c8de7a7cc64f40:  Table 'cluster_test' already exis
ts, Error_code: 1050;
2023-01-23T15:05:38.696438Z 0 [Note] [MY-000000] [Galera] Votes over 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051688:
   96c8de7a7cc64f40:   1/3
Waiting for more votes.
2023-01-23T15:05:38.698678Z 0 [Note] [MY-000000] [Galera] Member 0(dev-pxc-cluster-node-2) initiates vote on 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051688,9aa9a058451d27cc:  'utf8' is currently an alias for
the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous., Error_code: 3719; 'utf8mb3_unicode_ci' is a collation of the depre
cated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead., Error_code: 3778; Table 'cluster_test' already exists, Error_code: 1050;
2023-01-23T15:05:38.698742Z 0 [Note] [MY-000000] [Galera] Votes over 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051688:
   96c8de7a7cc64f40:   1/3
   9aa9a058451d27cc:   1/3
Waiting for more votes.
2023-01-23T15:05:38.698777Z 0 [Note] [MY-000000] [Galera] Member 1(dev-pxc-cluster-node-3) initiates vote on 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051688,9aa9a058451d27cc:  'utf8' is currently an alias for
the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous., Error_code: 3719; 'utf8mb3_unicode_ci' is a collation of the depre
cated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead., Error_code: 3778; Table 'cluster_test' already exists, Error_code: 1050;
2023-01-23T15:05:38.698834Z 0 [Note] [MY-000000] [Galera] Votes over 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051688:
   96c8de7a7cc64f40:   1/3
   9aa9a058451d27cc:   2/3
Winner: 9aa9a058451d27cc
2023-01-23T15:05:38.699152Z 367 [ERROR] [MY-000000] [Galera] Inconsistency detected: Inconsistent by consensus on 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051688
         at galera/src/replicator_smm.cpp:process_apply_error():1440
2023-01-23T15:05:38.700675Z 367 [Note] [MY-000000] [Galera] Closing send monitor...
2023-01-23T15:05:38.700749Z 367 [Note] [MY-000000] [Galera] Closed send monitor.
2023-01-23T15:05:38.700782Z 367 [Note] [MY-000000] [Galera] gcomm: terminating thread
2023-01-23T15:05:38.700889Z 367 [Note] [MY-000000] [Galera] gcomm: joining thread
2023-01-23T15:05:38.701265Z 367 [Note] [MY-000000] [Galera] gcomm: closing backend
2023-01-23T15:05:38.716635Z 367 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node
view (view_id(NON_PRIM,11569cdc-971a,21)
memb {
        d564d9bd-99eb,0
        }
joined {
        }
left {
        }
partitioned {
        11569cdc-971a,0
        4df0c6dd-a314,0
        }
)
2023-01-23T15:05:38.716699Z 367 [Note] [MY-000000] [Galera] PC protocol downgrade 1 -> 0
2023-01-23T15:05:38.716717Z 367 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node
view ((empty))
2023-01-23T15:05:38.717337Z 367 [Note] [MY-000000] [Galera] gcomm: closed
2023-01-23T15:05:38.717402Z 0 [Note] [MY-000000] [Galera] New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
2023-01-23T15:05:38.717550Z 0 [Note] [MY-000000] [Galera] Flow-control interval: [100, 100]
2023-01-23T15:05:38.717594Z 0 [Note] [MY-000000] [Galera] Received NON-PRIMARY.
2023-01-23T15:05:38.717618Z 0 [Note] [MY-000000] [Galera] Shifting SYNCED -> OPEN (TO: 1051688)
2023-01-23T15:05:38.717667Z 0 [Note] [MY-000000] [Galera] New SELF-LEAVE.
2023-01-23T15:05:38.717733Z 16 [Note] [MY-000000] [Galera] ================================================
View:
  id: 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051688
  status: non-primary
  protocol_version: 4
  capabilities: MULTI-MASTER, CERTIFICATION, PARALLEL_APPLYING, REPLAY, ISOLATION, PAUSE, CAUSAL_READ, INCREMENTAL_WS, UNORDERED, PREORDERED, STREAMING, NBO
  final: no
  own_index: 0
  members(1):
        0: d564d9bd-9b2b-11ed-99eb-1a2381e29aad, dev-pxc-cluster-node-1
=================================================
2023-01-23T15:05:38.717759Z 16 [Note] [MY-000000] [Galera] Non-primary view
2023-01-23T15:05:38.717755Z 0 [Note] [MY-000000] [Galera] Flow-control interval: [0, 0]
2023-01-23T15:05:38.717770Z 16 [Note] [MY-000000] [WSREP] Server status change synced -> connected
2023-01-23T15:05:38.717789Z 16 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.
2023-01-23T15:05:38.717857Z 16 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.
2023-01-23T15:05:38.717788Z 0 [Note] [MY-000000] [Galera] Received SELF-LEAVE. Closing connection.
2023-01-23T15:05:38.717926Z 0 [Note] [MY-000000] [Galera] Shifting OPEN -> CLOSED (TO: 1051688)
2023-01-23T15:05:38.717973Z 0 [Note] [MY-000000] [Galera] RECV thread exiting 0: Success
2023-01-23T15:05:38.718129Z 367 [Note] [MY-000000] [Galera] recv_thread() joined.
2023-01-23T15:05:38.718146Z 367 [Note] [MY-000000] [Galera] Closing replication queue.
2023-01-23T15:05:38.718154Z 367 [Note] [MY-000000] [Galera] Closing slave action queue.
2023-01-23T15:05:38.718318Z 16 [Note] [MY-000000] [Galera] ================================================
View:
  id: 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051688
  status: non-primary
  protocol_version: 4
  capabilities: MULTI-MASTER, CERTIFICATION, PARALLEL_APPLYING, REPLAY, ISOLATION, PAUSE, CAUSAL_READ, INCREMENTAL_WS, UNORDERED, PREORDERED, STREAMING, NBO
  final: yes
  own_index: -1
  members(0):
=================================================
2023-01-23T15:05:38.718331Z 16 [Note] [MY-000000] [Galera] Non-primary view
2023-01-23T15:05:38.718341Z 16 [Note] [MY-000000] [WSREP] Server status change connected -> disconnected
2023-01-23T15:05:38.718349Z 16 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.
2023-01-23T15:05:38.718361Z 16 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.
2023-01-23T15:05:38.728001Z 12 [Note] [MY-000000] [WSREP] Applier thread exiting ret: 6 thd: 12
2023-01-23T15:05:38.728043Z 15 [Note] [MY-000000] [WSREP] Applier thread exiting ret: 6 thd: 15
2023-01-23T15:05:38.728106Z 2 [Note] [MY-000000] [WSREP] Applier thread exiting ret: 6 thd: 2
2023-01-23T15:05:38.728363Z 10 [Note] [MY-000000] [WSREP] Applier thread exiting ret: 6 thd: 10
2023-01-23T15:05:38.728377Z 14 [Note] [MY-000000] [WSREP] Applier thread exiting ret: 6 thd: 14
2023-01-23T15:05:38.728055Z 13 [Note] [MY-000000] [WSREP] Applier thread exiting ret: 6 thd: 13
2023-01-23T15:05:38.728726Z 11 [Note] [MY-000000] [WSREP] Applier thread exiting ret: 6 thd: 11
2023-01-23T15:05:38.729896Z 0 [Note] [MY-000000] [Galera] Service thread queue flushed.
2023-01-23T15:05:38.730042Z 16 [Note] [MY-000000] [Galera] ####### Assign initial position for certification: 00000000-0000-0000-0000-000000000000:-1, protocol version: 5
2023-01-23T15:05:38.730096Z 16 [Note] [MY-000000] [WSREP] Applier thread exiting ret: 0 thd: 16

matthewb · January 23, 2023, 10:45pm

Hello Mike,
Are you 100% certain that all 3 nodes are the same version? Also, are you 100% certain that the data and schema is the same on all 3 nodes? This error message leads me to believe no:

2023-01-23T15:05:38.699152Z 367 [ERROR] [MY-000000] [Galera] Inconsistency detected: Inconsistent by consensus on 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051688

This is an internal “vote” by all the nodes. Basically, 1 of the node says “I don’t match you two” and it drops itself from the cluster. It could be that this consistency check is verifying the char set and collation, so if they are not matching, that’ll be an issue.

are running migrations constantly

Well, to be honest, that’s a bad way to operate a database backend. Migrations should only occur when there is specific reason, like a new code push.

for tables that already exist.

If they already exist, then why are they running excessive code?

It is very important they understand how DDLs work in a clustered environment as it is not the same as standalone MySQL.

Pep_Pla · January 24, 2023, 11:13am

Hi @Mike,

It isn’t easy to debug if you do not provide a test case that can be easily reproduced. In any case, it looks like there are two options here:

You were hit by a bug (in this case, I recommend you fill a bug report as this is beyond the purpose of this forum)
The application does something that generates this situation, for example, setting a different wsrep_OSU_method for the session.

Just two remarks:

The application creates a table without a primary key. This is not recommended for PXC and can lead to undesired effects (it could even be the root cause of this situation).
Did you test using create table if not exist?

Thanks

Pep

MikeBerry · January 24, 2023, 12:40pm

Hi Matthew

The 3 nodes are in sync until the one node drops out. Data, schema, charset, collation, all the same on all 3 nodes. I have put 3 copy/pastes down below.

I am used to having DDL and code in separate places but with Laravel, all of the DDL, create table, alter table, etc are all within Laravel code which is great for the Dev’s but a nightmare for me. They are constantly making changes to the code, then they just run the migration for that piece of code again and again, which sometimes has the create table statement in it. When they didn’t have a cluster, the error came back, table already exists, and everything was fine. They were also using old charset/collate aswell, so when I copied the data to the cluster I changed it all to the new mysql default. They upgraded Laravel charset/collate aswell but some of the older code still specifies charset/collate in it (different to new mysql default). This is what makes the node drop out. If they don’t specify the charset/collate then the table already exists error is returned and the node doesn’t drop out. They should have had “if table doesnt exist then create table” everywhere but because it worked with the error code return they just carried on. This has been going on for years before I arrived. Yes, its very bad coding and I am trying to teach them

We have tried a table with the same charset/collation as the mysql default and as a different one as shown below;

Node 1 default charset/collation
utf8mb4, utf8mb4_0900_ai_ci

Node 1 table definition
cluster_test, CREATE TABLE cluster_test (
name varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

Node 2 default charset/collation
utf8mb4, utf8mb4_0900_ai_ci

Node 2 table definition
cluster_test, CREATE TABLE cluster_test (
name varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

Node 3 default charset/collation
utf8mb4, utf8mb4_0900_ai_ci

Node 3 table definition
cluster_test, CREATE TABLE cluster_test (
name varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

This is what happens if they don’t specify the charset/collate. The error code table exists is returned and the node stays up.
2023-01-23T15:27:55.166249Z 0 [Note] [MY-000000] [Galera] Member 0(dev-pxc-cluster-node-2) initiates vote on 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051694,be8c6cdf3f05ce5c: Table ‘cluster_test’ already ex
ists, Error_code: 1050;
2023-01-23T15:27:55.166370Z 0 [Note] [MY-000000] [Galera] Votes over 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051694:
be8c6cdf3f05ce5c: 1/3
Waiting for more votes.
2023-01-23T15:27:55.167413Z 0 [Note] [MY-000000] [Galera] Member 1(dev-pxc-cluster-node-3) initiates vote on 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051694,be8c6cdf3f05ce5c: Table ‘cluster_test’ already ex
ists, Error_code: 1050;
2023-01-23T15:27:55.167501Z 0 [Note] [MY-000000] [Galera] Votes over 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051694:
be8c6cdf3f05ce5c: 2/3
Winner: be8c6cdf3f05ce5c
2023-01-23T15:27:55.167546Z 0 [Note] [MY-000000] [Galera] Recovering vote result from history: 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051694,be8c6cdf3f05ce5c
2023-01-23T15:27:55.168232Z 10 [Note] [MY-000000] [Galera] Got vote request for seqno 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051694
2023-01-23T15:27:55.168347Z 10 [Note] [MY-000000] [Galera] 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226:1051694 already voted on. Continue.

This the the wsrep ±---------------------- | Variable_name ±---------------------- | wsrep_local_state_uuid | wsrep_protocol_version | wsrep_last_applied | wsrep_last_committed | wsrep_monitor_status (L/A/C) | wsrep_replicated | wsrep_replicated_bytes | wsrep_repl_keys | wsrep_repl_keys_bytes | wsrep_repl_data_bytes | wsrep_repl_other_bytes | wsrep_received | wsrep_received_bytes | wsrep_local_commits | wsrep_local_cert_failures | wsrep_local_replays | wsrep_local_send_queue | wsrep_local_send_queue_max | wsrep_local_send_queue_min | wsrep_local_send_queue_avg | wsrep_local_recv_queue | wsrep_local_recv_queue_max | wsrep_local_recv_queue_min | wsrep_local_recv_queue_avg | wsrep_local_cached_downto | wsrep_flow_control_paused_ns | wsrep_flow_control_paused | wsrep_flow_control_sent | wsrep_flow_control_recv | wsrep_flow_control_active | wsrep_flow_control_requested | wsrep_flow_control_interval | wsrep_flow_control_interval_low | wsrep_flow_control_interval_high | wsrep_flow_control_status | wsrep_cert_deps_distance | wsrep_apply_oooe | wsrep_apply_oool | wsrep_apply_window | wsrep_apply_waits | wsrep_commit_oooe | wsrep_commit_oool | wsrep_commit_window | wsrep_local_state | wsrep_local_state_comment | wsrep_cert_index_size | wsrep_cert_bucket_count | wsrep_gcache_pool_size | wsrep_causal_reads | wsrep_cert_interval | wsrep_open_transactions | wsrep_open_connections | wsrep_ist_receive_status | wsrep_ist_receive_seqno_start | wsrep_ist_receive_seqno_current | wsrep_ist_receive_seqno_end | wsrep_incoming_addresses | wsrep_cluster_weight | wsrep_desync_count | wsrep_evs_delayed | wsrep_evs_evict_list | wsrep_evs_repl_latency | wsrep_evs_state | wsrep_gcomm_uuid | wsrep_gmcast_segment | wsrep_cluster_capabilities | wsrep_cluster_conf_id | wsrep_cluster_size | wsrep_cluster_state_uuid | wsrep_cluster_status | wsrep_connected | wsrep_local_bf_aborts | wsrep_local_index | wsrep_provider_capabilities | wsrep_provider_name | wsrep_provider_vendor | wsrep_provider_version | wsrep_ready | wsrep_thread_count ±---------------------- before the node drops out…
-----------±-----------------------------------------------------------------------------------------------------------------------------------------------+
| Value |
-----------±-----------------------------------------------------------------------------------------------------------------------------------------------+
| 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226 |
| 10 |
| 1052136 |
| 1052136 |
| [ (453, 453), (1052136, 1052136), (1052136, 1052136) ] |
| 443 |
| 216184 |
| 2139 |
| 27744 |
| 158642 |
| 0 |
| 9 |
| 432 |
| 434 |
| 0 |
| 0 |
| 0 |
| 1 |
| 0 |
| 0 |
| 0 |
| 1 |
| 0 |
| 0 |
| 1051663 |
| 0 |
| 0 |
| 0 |
| 0 |
| false |
| false |
| [ 173, 173 ] |
| 173 |
| 173 |
| OFF |
| 1 |
| 0 |
| 0 |
| 1 |
| 0 |
| 0 |
| 0 |
| 1 |
| 4 |
| Synced |
| 169 |
| 823 |
| 784629200 |
| 0 |
| 29981.9 |
| 0 |
| 0 |
| |
| 0 |
| 0 |
| 0 |
| 10.10.10.96:3306,10.168.164.97:3306,10.10.10.95:3306 |
| 3 |
| 0 |
| |
| |
| 0/0/0/0/0 |
| OPERATIONAL |
| 9c400143-9b31-11ed-b5f6-9b9b5d11012a |
| 0 |
| |
| 25 |
| 3 |
| 4ab2da3b-3cc2-11ed-b34d-7aa6ac77c226 |
| Primary |
| ON |
| 0 |
| 2 |
| :MULTI_MASTER:CERTIFICATION:PARALLEL_APPLYING:TRX_REPLAY:ISOLATION:PAUSE:CAUSAL_READS:INCREMENTAL_WRITESET:UNORDERED:PREORDERED:STREAMING:NBO: |
| Galera |
| Codership Oy info@codership.com (modified by Percona https://percona.com/) |
| 4.12(e167906) |
| ON |
| 9 |
-----------±-----------------------------------------------------------------------------------------------------------------------------------------------+

MikeBerry · January 25, 2023, 1:11pm

I am on something else at the moment but as soon as I get chance I will setup up a proper test that hopefully you can replicate.

Topic		Replies	Views
Percona xtraDB cluster 5.7 and 8.0.19 collation Percona XtraDB Cluster 8.x community , mysql , percona	3	847	August 11, 2020
Cluster crash and not restart Percona XtraDB Cluster 5.x	2	1529	March 4, 2015
Replication from percona 8.0.34 to Mariadb 10.232 & Mariadb 10.6 Percona XtraBackup percona	9	46	July 29, 2024
XtraDB Cluster seems to completely ignore SQL_MODE Percona XtraDB Cluster 5.x	0	1041	September 13, 2019
Errors are reported when using pt-table-checksum to check MySQL8.0.25 and MySQL5.7.34 Percona Toolkit	6	1400	July 28, 2021

Laravel/PHP with charset or collation type breaks cluster

Related topics