The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

Why does this query hang on Maria but runs fine on MySQL?

I recently migrated a server from CentOS 6 to CentOS 7. With this migration, the hosting company said the default db is now MariaDB (it was MySQL on the old server).

After migrating everything over; all the sites work fine; but, a cron task on 1 of the sites causes the MariaDB server to hang (the query never completes so it hangs one of the server's CPU cores, next time cron runs, same thing.. and it hangs another core, until all the cores are hung up and the server crashes).

I have tried running this query directly on the db server and on the old server's MySQL db server the query completes in less than 0.3s. I have also tried running this query to a copy of the db installed on my local WAMP setup. There i have MySQL 5.7.24 and Maria 10.3.12. Same result there, the query on the MySQL server completes in around 0.3s; the one on Maria never completes.

Curious if anyone has seen this issue before? I'll post the very long query which is causing the problem in the hope that possibly someone sees something they know may be an issue for Maria.

SELECT node.title AS node_title, users_field_data_field_reg_user.name AS users_field_data_field_reg_user_name, users_field_data_field_reg_user.uid AS users_field_data_field_reg_user_uid, users_field_data_field_reg_user.mail AS users_field_data_field_reg_user_mail, node_field_data_field_match_team1.title AS node_field_data_field_match_team1_title, node.nid AS nid, node_field_data_field_match_court.nid AS node_field_data_field_match_court_nid, node_field_data_field_court_venue.nid AS node_field_data_field_court_venue_nid, node_field_data_field_match_team2.title AS node_field_data_field_match_team2_title, 'user' AS field_data_field_profile_fname_user_entity_type, 'node' AS field_data_field_match_court_node_entity_type, 'node' AS field_data_field_court_venue_node_entity_type, 'node' AS field_data_field_venue_address_node_entity_type, 'node' AS field_data_body_node_entity_type FROM node node LEFT JOIN field_data_field_match_team1 field_data_field_match_team1 ON node.nid = field_data_field_match_team1.entity_id AND (field_data_field_match_team1.entity_type = 'node' AND field_data_field_match_team1.deleted = '0') LEFT JOIN node node_field_data_field_match_team1 ON field_data_field_match_team1.field_match_team1_nid = node_field_data_field_match_team1.nid LEFT JOIN field_data_field_match_team2 field_data_field_match_team2 ON node.nid = field_data_field_match_team2.entity_id AND (field_data_field_match_team2.entity_type = 'node' AND field_data_field_match_team2.deleted = '0') LEFT JOIN node node_field_data_field_match_team2 ON field_data_field_match_team2.field_match_team2_nid = node_field_data_field_match_team2.nid LEFT JOIN field_data_field_reg_team node_field_data_field_match_team1field_data_field_reg_team ON node_field_data_field_match_team1.nid = node_field_data_field_match_team1field_data_field_reg_team.field_reg_team_nid LEFT JOIN node field_reg_team_node ON node_field_data_field_match_team1field_data_field_reg_team.entity_id = field_reg_team_node.nid LEFT JOIN field_data_field_reg_team node_field_data_field_match_team2field_data_field_reg_team ON node_field_data_field_match_team2.nid = node_field_data_field_match_team2field_data_field_reg_team.field_reg_team_nid LEFT JOIN node field_reg_team_node_1 ON node_field_data_field_match_team2field_data_field_reg_team.entity_id = field_reg_team_node_1.nid LEFT JOIN field_data_field_reg_user field_reg_team_nodefield_data_field_reg_user ON field_reg_team_node.nid = field_reg_team_nodefield_data_field_reg_user.entity_id AND (field_reg_team_nodefield_data_field_reg_user.entity_type = 'node' AND field_reg_team_nodefield_data_field_reg_user.deleted = '0') LEFT JOIN users users_field_data_field_reg_user ON field_reg_team_nodefield_data_field_reg_user.field_reg_user_uid = users_field_data_field_reg_user.uid LEFT JOIN field_data_field_reg_user field_reg_team_node_1field_data_field_reg_user ON field_reg_team_node_1.nid = field_reg_team_node_1field_data_field_reg_user.entity_id AND (field_reg_team_node_1field_data_field_reg_user.entity_type = 'node' AND field_reg_team_node_1field_data_field_reg_user.deleted = '0') LEFT JOIN users users_field_data_field_reg_user_1 ON field_reg_team_node_1field_data_field_reg_user.field_reg_user_uid = users_field_data_field_reg_user_1.uid LEFT JOIN field_data_field_match_court field_data_field_match_court ON node.nid = field_data_field_match_court.entity_id AND (field_data_field_match_court.entity_type = 'node' AND field_data_field_match_court.deleted = '0') LEFT JOIN node node_field_data_field_match_court ON field_data_field_match_court.field_match_court_nid = node_field_data_field_match_court.nid LEFT JOIN field_data_field_court_venue node_field_data_field_match_courtfield_data_field_court_venue ON node_field_data_field_match_court.nid = node_field_data_field_match_courtfield_data_field_court_venue.entity_id AND (node_field_data_field_match_courtfield_data_field_court_venue.entity_type = 'node' AND node_field_data_field_match_courtfield_data_field_court_venue.deleted = '0') LEFT JOIN node node_field_data_field_court_venue ON node_field_data_field_match_courtfield_data_field_court_venue.field_court_venue_nid = node_field_data_field_court_venue.nid INNER JOIN field_data_taxonomy_vocabulary_8 field_reg_team_nodefield_data_taxonomy_vocabulary_8 ON field_reg_team_node.nid = field_reg_team_nodefield_data_taxonomy_vocabulary_8.entity_id AND (field_reg_team_nodefield_data_taxonomy_vocabulary_8.entity_type = 'node' AND field_reg_team_nodefield_data_taxonomy_vocabulary_8.deleted = '0') INNER JOIN field_data_field_game_reminder users_field_data_field_reg_userfield_data_field_game_reminder ON users_field_data_field_reg_user.uid = users_field_data_field_reg_userfield_data_field_game_reminder.entity_id AND (users_field_data_field_reg_userfield_data_field_game_reminder.entity_type = 'user' AND users_field_data_field_reg_userfield_data_field_game_reminder.deleted = '0') LEFT JOIN field_data_field_match_datetime field_data_field_match_datetime ON node.nid = field_data_field_match_datetime.entity_id AND (field_data_field_match_datetime.entity_type = 'node' AND field_data_field_match_datetime.deleted = '0') WHERE (( (node.status = '1') AND (node.type IN ('match')) AND (field_reg_team_nodefield_data_taxonomy_vocabulary_8.taxonomy_vocabulary_8_tid IN ('28', '30')) AND (users_field_data_field_reg_userfield_data_field_game_reminder.field_game_reminder_value = '1') AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(field_data_field_match_datetime.field_match_datetime_value), SEC_TO_TIME(-14400)), '%Y-%m-%d') = '2020-08-14') ))AND ( EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = '0') AND (na.realm = 'all') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference_author') )OR( (na.gid = '1') AND (na.realm = 'nodeaccess_userreference_all') ))AND (na.grant_view >= '1') AND (node.nid = na.nid) )) AND ( EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = '0') AND (na.realm = 'all') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference_author') )OR( (na.gid = '1') AND (na.realm = 'nodeaccess_userreference_all') ))AND (na.grant_view >= '1') AND (node_field_data_field_match_team1.nid = na.nid) )) AND ( EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = '0') AND (na.realm = 'all') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference_author') )OR( (na.gid = '1') AND (na.realm = 'nodeaccess_userreference_all') ))AND (na.grant_view >= '1') AND (node_field_data_field_match_team2.nid = na.nid) )) AND ( EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = '0') AND (na.realm = 'all') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference_author') )OR( (na.gid = '1') AND (na.realm = 'nodeaccess_userreference_all') ))AND (na.grant_view >= '1') AND (field_reg_team_node.nid = na.nid) )) AND ( EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = '0') AND (na.realm = 'all') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference_author') )OR( (na.gid = '1') AND (na.realm = 'nodeaccess_userreference_all') ))AND (na.grant_view >= '1') AND (field_reg_team_node_1.nid = na.nid) )) AND ( EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = '0') AND (na.realm = 'all') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference_author') )OR( (na.gid = '1') AND (na.realm = 'nodeaccess_userreference_all') ))AND (na.grant_view >= '1') AND (node_field_data_field_match_court.nid = na.nid) )) AND ( EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = '0') AND (na.realm = 'all') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference') )OR( (na.gid = '0') AND (na.realm = 'nodeaccess_userreference_author') )OR( (na.gid = '1') AND (na.realm = 'nodeaccess_userreference_all') ))AND (na.grant_view >= '1') AND (node_field_data_field_court_venue.nid = na.nid) ))

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.