MariaDB stores varbinary field with different value from the original field using PHP PDO

Question

I'm using PHP - PDO to synchronize a SQL Server Table using RowVersion from our ERP to a MariaDB Database (hosting).

When I save values in a local (office) MariaDB database version 5.5.56, everything goes fine and data is stored correctly. When I do just the same to store data in our hosting with MariaDB version 10.0.37, rowversion field saves a different value.

I've tried, instead from SQL Server, to copy data between office MariaDb and remote MariaDb, using PHP PDO, and I have the same problem. Original rowversion value is different from remote rowversion value.

To store rowversion field I'm using VARBINARY(8).

Example:

ERP SQL SERVER ROW: (id, description, rowversion). Values: 1, AMARILLO, 0x00000000025DB362 ERP ROW
MariaDb local database: stored values 1,AMARILLO,00000000025db362 Local MariaDb row
MariaDb remote database: stored values: 1, AMARILLO, 00000000025d3f62 MariaDb remote row

I don't understand why remote MariaDb saves a different value. Both mariadb tables are identical, but one stores one value and the other a different value. Any ideas? could it be a database version problem?

PHP TEST code, in this case from local MariaDb to remote MariaDb:

    $sql = "SELECT * FROM colors";
    $sth = $this->Db->localdb->query($sql);
    $res = $sth->fetchAll(PDO::FETCH_ASSOC);

    $sql = "TRUNCATE TABLE colors";
    $this->Db->remotedb->exec($sql);

    $sql = "INSERT INTO colors (id,des,rowversion) VALUES (?,?,?)";
    $sthinsert = $this->Db->remotedb->prepare($sql);


    foreach ($res as $line)
    {
            echo "Inserting color {$line['id']}" . PHP_EOL;
            $sthinsert->execute(array(
                $line['id'],$line['des'],$line['rowversion']
            ));         
    }

Table:

CREATE TABLE `colors` (
    id                              int NOT NULL,
    des                             varchar(30),
    rowversion                      varbinary(8),
    date                            timestamp NOT NULL DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (id)
) ENGINE=myisam DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci COMMENT 'Colors';

UPDATED AND SOLVED:

After reading this post PHP/PDO/MySQL: inserting into MEDIUMBLOB stores bad data I've tested to change the SET NAMES in my remote database. That solved the problem.

I added this line to my PHP program:

$this->Db->remotedb->exec("SET NAMES latin1 COLLATE latin1_general_ci");

Now the question is why a database (MariaDB) works in one way and the second in other way.

Sqlserver is working with Modern_Spanish_CI_AS collation.

Local mariadb is working with utf8mb4_unicode_ci, and I set in PDO utf8

Remote mariadb is in utf8mb4_general_ci, and I also was setting PDO for utf8.

With those collations, the data coming from SqlServer was stored different. Setting the new collation solved it. It would be better if PDO could use binary data without any interpretation realted to collation.

SECOND UPDATE

I've found a better way to do this:

I create the table with CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
I change the CHARSET in PDO to:

$this->Db->remotedb->exec("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");

With this, rowversion and languague specific chars are stored right.

Cheers.

Unless I'm missing something those values _are_ identical. There's no such thing as a case-sensitive hexadecimal number. — Sammitch, Dec 29 '18 at 08:34
I read from SqlServer and save to MariaDB using PHP PDO. Data is stored different in one MariaDb than in the other MariaDb. Can any database configuration affect varbinary values? I'm using just the same PDO configuration and php program. — Ramon, Dec 30 '18 at 07:44

score 2 · Answer 1 · answered Dec 29 '18 at 10:58

2

PDO tries to convert the binary value into a string using the specified UTF-8 encoding. B3 is not a valid code point and is thus replaced by ? - when encoding back from string to binary, you then get 3F as value for the replacement character ?.

To prevent PDO from doing binary to string conversion, see this SO post:

save image in mssql database as varbinary(max) without conversion

answered Dec 29 '18 at 10:58

Quagaar

1,257
12
21

That could be the point. After reading the post I can't solve the problem since I'm saving data to MYSQL, not SqlServer. An interesting thing is that my source data is SqlServer. If I store data in my local MariaDb, everything goes fine but if I do the same to my hosting/remote MariaDb, wrong value is stored. My configuration for PDO is just the same for both connections so I think that could be a problem of database configuration/version. PDO seems to do different thing for each database. – Ramon Dec 30 '18 at 07:41
Set the `PDO::SQLSRV_ATTR_ENCODING` attribute when reading from SQL Server since this seems to be where the problem occurs. If this doesn't work, maybe SQL Server has some equivalent to the MySQL function `hex` to convert binary data to a hex string. – Quagaar Dec 30 '18 at 12:11

MariaDB stores varbinary field with different value from the original field using PHP PDO

1 Answers1