Exception caused by MySQL 5.6 gtid replication

It is working with the separated queries:

        $insert_query = 'CREATE TABLE ' . $display_handler->index_tablename() . '('. $display_handler->batched_execution_state->sandbox['weight_field_alias'] . ' INT(6))';
        db_query($insert_query, $args);

        $q = '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';
        $result = db_query($q, $args);

        $row = $result->fetchAssoc();
        $keys = array_keys($row);

        foreach($keys as $key) {
          if($key != $display_handler->batched_execution_state->sandbox['weight_field_alias']) {
            $q = 'ALTER TABLE ' . $display_handler->index_tablename() . ' ADD ' . $key . ' TEXT';
            db_query($q);
          }
        }

        foreach ($result as $r) {
          $options = array();
          foreach($keys as $key) {
            $options[$key] = $r->$key;
          }

          db_insert($display_handler->index_tablename())->fields($options)->execute();
        }

Although, the issue is that the First Row is always missing.

$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.

Patch #2 works for me. I have tested on Mysql 5.6.

+1 for Patch #2.

We need to make sure that the tests pass.

I'll see if I can work on that more.

Re-rolled the patch against 7.x-3.1

Hi we are facing the same issue with GTID is there any steps to reproduce the issue
Any update on this issue

I 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,

Just 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?

Patch in #9 worked fine for me, except i had to manually add prefixes for tables because db_query function doesn't do it automatically.

$conn = Database::getConnection();
$prefix = $conn->tablePrefix();

Hi!

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!

Hi,

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!

Hi!
Added a test to verify PHP 7.0 compactibility. Looks good in the test

Thanks !

Hello 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.

The patch in #17 solved this issue quite nicely for me. @devaraj-johnson, do you want to add your test to the patch?

Suffered from this error on MariaDB 10.2 - #17 solved it. Thanks.

The 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

Huh.. 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.

The patch from #25 works perfectly (Mysql 5.7.27)

We'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.

The patch from #27 works perfectly for us with MySQL 5.7.24 and GTID.

Hm. 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.