Sync: how get a blob from MySql and save it in another MySql with php

Question

First of all, Im sorry for my bad English, and thank you all for view my cuestion.

I want to sync two Mysql, but I can't get it work with blob types.

The two connections are equal in username and password.

$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS) or die();
mysqli_select_db($conn, DB_NAME) or die();
mysqli_set_charset($conn, 'utf8') or die();

// db connection string variables
$connEco = mysqli_connect("192.168.20.2", DB_USER, DB_PASS) or die();
mysqli_select_db($connEco, DB_NAME) or die();
mysqli_set_charset($connEco, 'utf8') or die();
$sql="DROP TABLE IF EXISTS users_in_fmd";
$rs = mysqli_query($connEco, $sql)
or die(mysqli_error($connEco));

$sql="CREATE TABLE IF NOT EXISTS `users_in_fmd` ( `UsersInFmd` int(10) NOT NULL AUTO_INCREMENT, `SucursalId` int(10) NOT NULL, `UserId` int(10) NOT NULL, `FMD` mediumblob, `FingerIndex` int(2) NOT NULL, PRIMARY KEY (`UsersInFmd`), KEY `UserId` (`UserId`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0";

$rs = mysqli_query($connEco, $sql)
or die("Error 1");

$sql="SELECT UsersInFmd, SucursalId, UserId, FMD, FingerIndex FROM users_in_fmd";
$rs = mysqli_query($conn, $sql)
or die("Error 2");

while($row = mysqli_fetch_array($rs))
{
    //The row['FMD'] is the data.
    $sql3 = "INSERT INTO users_in_fmd (`UsersInFmd`, `SucursalId`, `UserId`, `FMD`, `FingerIndex`) VALUES ('".$row['UsersInFmd']."', '".$row['SucursalId']."', '".$row['UserId']."', '".$row['FMD']."', '".$row['FingerIndex']."')";
    $updateregistro = mysqli_query($connEco,$sql3) or die("Error 3");
}

I have try to get the FMD data in HEX() from mysql, I have try to UNHEX() when is inserted, but all is the same result, I cant import the blob data into the MySql database.

Any Ideas ?

The error is You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ZVE??c??????? \9;???/??"(??!B?{??Fb?c??"??D,⬆?i?k?G*a I try with HEX(FMD), FingerIndex..... but when I upload the data is corrupted. — Javier Limón, Aug 10 '16 at 21:40
It was a bad idea to save it as a blob in the first place. Now if you are geting it out of there, save it as a file. http://stackoverflow.com/a/38829952/267540 — e4c5, Aug 11 '16 at 14:31
Hi, thank you for your Answers, I make a workarround, I solved making a dump file and then uploading to the other server, the option that it work for dumping the Blob was the option --hex-blob (mysqldump -h $SERVER_IP -u"$USER" -p"$DB_PASS" --hex-blob $table > $file) — Javier Limón, Jul 20 '17 at 23:11

rscarson · Answer 1 · 2016-08-10T22:19:53.680

0

Try prepared statements instead of building your query manually like that.

The error you are getting suggests that the blob needs to be escaped properly.

edited Aug 10 '16 at 22:19

answered Aug 10 '16 at 20:15

rscarson

262
3
13

Yes, you know how to do that ? I have been try HEX() for select and UNHEX() for insert, but I get a 0 kb blob in the copy database file. – Javier Limón Aug 10 '16 at 22:28
Hi, thank you for your Answers, I make a workarround, I solved making a dump file and then uploading to the other server, the option that it work for dumping the Blob was the option --hex-blob (mysqldump -h $SERVER_IP -u"$USER" -p"$DB_PASS" --hex-blob $table > $file) – Javier Limón Jul 20 '17 at 23:12

Sync: how get a blob from MySql and save it in another MySql with php

1 Answers1