My exports are failing with the following error:
Exception: SQLSTATE[HY000]: General error: 1786 CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1.
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mydb.views_data_export_index_19' doesn't exist: SELECT COUNT(*) FROM {views_data_export_index_19};
The reason is that MySQL 5.6 gtid replication does not allow create table ... select statements. http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-restrictions.html
CREATE TABLE ... SELECT statements. CREATE TABLE ... SELECT is not safe for statement-based replication. When using row-based replication, this statement is actually logged as two separate events—one for the creation of the table, and another for the insertion of rows from the source table into the new table just created. When this statement is executed within a transaction, it is possible in some cases for these two events to receive the same transaction identifier, which means that the transaction containing the inserts is skipped by the slave. Therefore, CREATE TABLE ... SELECT is not supported when using GTID-based replication.
The query that causes this:
$insert_query = 'CREATE TABLE {' . $display_handler->index_tablename() . '} SELECT @row := @row + 1 AS ' . $display_handler->batched_execution_state->sandbox['weight_field_alias'] . ', ' . implode(', ', $select_aliases) . ' FROM (' . (string)$query . ') AS cl, (SELECT @row := 0) AS r';
db_query($insert_query, $args);
After reading the docs and consulting with colleagues, I believe the solution here is to break this query into 2 - first create the table, then select rows. Has anyone else run into this?
Comment | File | Size | Author |
---|---|---|---|
#27 | interdiff_25_27.txt | 829 bytes | dagomar |
#27 | views_data_export-gtid_replicate-2209853-27.patch | 2.43 KB | dagomar |
| |||
#17 | views_data_export-gtid_replicate-2209853-17.patch | 2.1 KB | omarlopesino |
Comments
Comment #1
astimac CreditAttribution: astimac commentedIt is working with the separated queries:
Although, the issue is that the First Row is always missing.
Comment #2
drasgardian CreditAttribution: drasgardian at Eighty Options commented$result->fetchAssoc() was moving the pointer on the resultset to the next row, causing the first record to be missing.
patch attached should address this.
Comment #3
drasgardian CreditAttribution: drasgardian at Eighty Options commentedComment #6
q2_faith CreditAttribution: q2_faith commentedPatch #2 works for me. I have tested on Mysql 5.6.
Comment #7
Anonymous (not verified) CreditAttribution: Anonymous commentedPatch #2 also works for me.
Comment #8
SpartyDan CreditAttribution: SpartyDan commented+1 for Patch #2.
We need to make sure that the tests pass.
I'll see if I can work on that more.
Comment #9
SpartyDan CreditAttribution: SpartyDan commentedRe-rolled the patch against 7.x-3.1
Comment #12
Devaraj johnson CreditAttribution: Devaraj johnson as a volunteer and commentedHi we are facing the same issue with GTID is there any steps to reproduce the issue
Any update on this issue
Comment #13
TTNT CreditAttribution: TTNT commentedI presume we run into the same issue following an upgrade to MySQL5.6.
Error:
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table '.views_data_export_index_775' doesn't exist: SELECT * FROM {views_data_export_index_775} ORDER BY vde_weight ASC LIMIT 0, 100; Array ( ) in views_data_export_plugin_display_export->query() (line 525 of /sites/all/modules/views_data_export/plugins/views_data_export_plugin_display_export.inc).
Patch in #9 doesn't work for 3.1.
Edit: #9 is for 3.2. I'll test later.
Sincerely,
Comment #14
TTNT CreditAttribution: TTNT commentedJust confirming #9 works for 3.2 on MySQL 5.6
Perhaps it should be committed? Or does this method cause issues when working with other MySQL versions?
Comment #15
Devaraj johnson CreditAttribution: Devaraj johnson as a volunteer and commentedComment #16
djecka CreditAttribution: djecka commentedPatch in #9 worked fine for me, except i had to manually add prefixes for tables because db_query function doesn't do it automatically.
Comment #17
omarlopesinoHi!
Patch #9 worked for us, thanks!
Tests are not working because table prefixes are not added to the querys (as @decka commented in #16). This is a security error and cause the patch doesn't work in some scenarios
Uploaded new patch adding brackets to que query, which drupal uses to add table prefix. This solves the problem commented in #16.
Please review, thanks!
Comment #18
Devaraj johnson CreditAttribution: Devaraj johnson as a volunteer and commentedHi,
Verified the patch work perfect marking it for RTBC. We will wait for the contributor review and expecting it soon in a release so that the patch can be removed soon.
Thanks!
Comment #19
Devaraj johnson CreditAttribution: Devaraj johnson as a volunteer and commentedHi!
Added a test to verify PHP 7.0 compactibility. Looks good in the test
Thanks !
Comment #20
omarlopesinoComment #21
jyraya CreditAttribution: jyraya at European Commission and European Union Institutions, Agencies and Bodies commentedHello All,
My site runs with MySQL 5.6.38 and I do not meet this issue.
With which version of 5.6, the problem occurs?
Regards.
Comment #22
PapaGrandeThe patch in #17 solved this issue quite nicely for me. @devaraj-johnson, do you want to add your test to the patch?
Comment #23
skylord CreditAttribution: skylord commentedSuffered from this error on MariaDB 10.2 - #17 solved it. Thanks.
Comment #24
sosyuki CreditAttribution: sosyuki commentedThe patch in #17 work me.
Db is aliyun RDS mysql5.7.25.
https://www.aliyun.com/product/rds/mysql?spm=5176.8142029.388261.124.4f756d3eOuQkRc
Comment #25
bucefal91 CreditAttribution: bucefal91 at Ocelot commentedHuh.. Sorry guys to reset the 'reviewed by community' status, but we did hit one additional problem. We've been using the patch #17 pretty successfully... But our MySQL's db default charset is latin1 (do not ask me why... let's just assume it is) while all the Drupal tables in that DB are utf8mb4.
So because the patch #17 is doing direct SQL queries, the short-living table gets created with latin1 charset. This apparently breaks things pretty nasty if you are exporting anything that has a 4-byte symbol.
So I have just rewritten the #17 to use drupal API to work with database where it forces the Drupal charset independently of DB default charset.
Comment #26
Alezu CreditAttribution: Alezu at Initlab commentedThe patch from #25 works perfectly (Mysql 5.7.27)
Comment #27
dagomar CreditAttribution: dagomar at SWIS commentedWe've had an additional issue. We're using a percona cluster with the pxc_strict_mode setting to master, which forces all tables to have a primary key. So we had to update the patch to include a primary key.
Patch and interdiff included.
Comment #28
mvcThe patch from #27 works perfectly for us with MySQL 5.7.24 and GTID.
Comment #29
skylord CreditAttribution: skylord commentedHm. After upgrade to php 8.1 and MariaDB 10.5 #17 starts to give WSOD. Have no time to investigate, so just reverted it. Keep it in mind.