Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25444

mysql --binary-mode is not able to replay some mysqlbinlog outputs

    XMLWordPrintable

    Details

      Description

      There are still cases when prepared statements (for example, from Java) generate binary logs that can not be replayed without errors like:

      ERROR at line 135: Unknown command '\\'.
      

      while doing point in time recovery with mysqlbinlog ... | mysql --binary-mode=1 ... command lines.

      Consider this example:

      openxs@ao756:~/dbs/maria10.3$ bin/mysql -uroot --socket=/tmp/mariadb.sock test
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 536
      Server version: 10.3.29-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [test]> show create table tb\G
      *************************** 1. row ***************************
             Table: tb
      Create Table: CREATE TABLE `tb` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `cb` longblob DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
      1 row in set (0,000 sec)
       
      MariaDB [test]> reset master;
      Query OK, 0 rows affected (0,166 sec)
       
      MariaDB [test]> truncate table tb;
      Query OK, 0 rows affected (0,291 sec)
       
      MariaDB [test]> exit
      Bye
      openxs@ao756:~/dbs/maria10.3$ cd -
      /home/openxs
      openxs@ao756:~$ for i in `ls ~/Documents`; do java -classpath mariadb-java-client-1.5.4.jar:. blobTest /home/openxs/Documents/$i ; done;
      Loading file /home/openxs/Documents/101.bin
      Loading file /home/openxs/Documents/102.bin
      Loading file /home/openxs/Documents/103.bin
      Loading file /home/openxs/Documents/104.bin
      Loading file /home/openxs/Documents/all.tgz
      Loading file /home/openxs/Documents/gnuplot2.png
      Loading file /home/openxs/Documents/gnuplot2.png.zip
      Loading file /home/openxs/Documents/gnuplot.png
      Loading file /home/openxs/Documents/gnuplot.png.zip
      Loading file /home/openxs/Documents/gnuplot.xcf
      Loading file /home/openxs/Documents/gnuplot.xcf.zip
      Loading file /home/openxs/Documents/psn1.svg
      Loading file /home/openxs/Documents/psn1.svg.zip
      Loading file /home/openxs/Documents/sysbench
      Loading file /home/openxs/Documents/tclsh8.6
      Loading file /home/openxs/Documents/zero1.txt
      Loading file /home/openxs/Documents/zero2.tgz
      openxs@ao756:~$ cd -
      /home/openxs/dbs/maria10.3
      openxs@ao756:~/dbs/maria10.3$ bin/mysql -uroot --socket=/tmp/mariadb.sock test
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 577
      Server version: 10.3.29-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [test]> flush logs;
      Query OK, 0 rows affected (0,139 sec)
       
      MariaDB [test]> show master status;
      +------------------+----------+--------------+------------------+
      | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
      +------------------+----------+--------------+------------------+
      | ao756-bin.000002 |      385 |              |                  |
      +------------------+----------+--------------+------------------+
      1 row in set (0,000 sec)
       
      MariaDB [test]> select id, length(cb) from tb;
      +----+------------+
      | id | length(cb) |
      +----+------------+
      |  1 |        560 |
      |  2 |        560 |
      |  3 |        560 |
      |  4 |        560 |
      |  5 |    2484981 |
      |  6 |      66204 |
      |  7 |      64308 |
      |  8 |      34859 |
      |  9 |      34040 |
      | 10 |      86976 |
      | 11 |      42593 |
      | 12 |      25999 |
      | 13 |       6072 |
      | 14 |    1454472 |
      | 15 |       8824 |
      | 16 |      10000 |
      | 17 |    2484981 |
      +----+------------+
      17 rows in set (0,004 sec)
       
      MariaDB [test]> exit
      Bye
      openxs@ao756:~/dbs/maria10.3$ bin/mysqlbinlog data/ao756-bin.000001 | bin/mysql -uroot --socket=/tmp/mariadb.sock --binary-mode=1 test
      ERROR at line 135: Unknown command '\\'.
      openxs@ao756:~/dbs/maria10.3$
      

      Java code is:

      openxs@ao756:~/dbs/maria10.3$ cd -
      /home/openxs
      openxs@ao756:~$ cat blobTest.java
      import java.sql.DriverManager;
      import java.sql.Connection;
      import java.sql.SQLException;
      import java.sql.Statement;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      import java.sql.ResultSetMetaData;
      import java.io.File;
      import java.io.FileInputStream;
       
      public class blobTest {
          public static void main(String ... args) {
              try {
                  Class.forName("org.mariadb.jdbc.Driver");
              } catch (ClassNotFoundException e) {
                  e.printStackTrace();
                  return;
              }
       
              Connection conn = null;
       
              try {
                  conn = DriverManager.getConnection("jdbc:mariadb://127.0.0.1:3309/test?user=root");
              } catch (SQLException e) {
                  e.printStackTrace();
                  return;
              }
       
              if (conn != null) {
      //          Statement stmt = conn.createStatement();
      //          stmt.execute("create table tb(id int primary key, cb longblob)");   
                try {
                  PreparedStatement ps = conn.prepareStatement("INSERT INTO tb(cb) VALUES(_binary ?)");
                  File file = new File(args[0]);
                  System.out.println("Loading file "+args[0]);
                  ps.setBlob(1, new FileInputStream(file));
                  ps.execute();
                } catch (Exception e) {
                    e.printStackTrace();
                    return;
                }
       
              } else {
                  System.out.println("Failed to make connection!");
              }
          }
      }
      

      It was built this way:

      openxs@ao756:~$ javac -version
      javac 1.8.0_282
      openxs@ao756:~$ javac blobTest.java
      openxs@ao756:~$
      

      The content of my ~/Documents that matters is attached. Nothe *.bin files (they all have the same content, but I can not reproduce easily without other files so far).

        Attachments

          Activity

            People

            Assignee:
            Elkin Andrei Elkin
            Reporter:
            valerii Valerii Kravchuk
            Votes:
            2 Vote for this issue
            Watchers:
            9 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration