I spend refactoring of a code of a site for PHP . The code was migrated from CentOS 6 , PHP 5.3 , mysql 5.1.73 to Ubuntu 16.04 , php 7.0 , mysql 5.7.17 . Accordingly, due to the transition to PHP 7.0 I had to change the code. And more specifically - the methods of working with the database - mysql on PDO . Everything worked out and everything works well.

Twice a week, it is necessary to exchange data between the site database and the manager database ( Microsoft SQL Server 2005 ).

In short, the CLI script simply clears the table in the mysql database, and then copies the data from the mssql database into it.

Here is an example:

 <?php try { $host = "localhost"; $site_db = "site_db"; $user = "root"; $password = '12345'; $mysql = new PDO ("mysql:host=$host;dbname=$site_db","$user","$password" ); $mysql->exec("SET NAMES utf8"); $mysql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { print_r( $e->getMessage() ); exit; } try { $host = "192.168.0.34"; $port = 1433; $manager_db = "manager_db"; $user = "admin"; $password = '12345'; $mssql = new PDO ("dblib:host=$host:$port;dbname=$manager_db","$user","$password" ); $mssql->exec("SET NAMES utf8"); $mssql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { print_r( $e->getMessage() ); exit; } $mysql -> query( 'TRUNCATE firms' ); $mssql_query = $mssql -> prepare(" SELECT firm_id, firm_name, address, image FROM firms "); $mssql_query->execute(); while( $row = $mssql_query -> fetch( PDO::FETCH_ASSOC ) ){ foreach ( $row as $key => $value ) { if ( $key !== 'image' ) { $row[$key] = iconv('Windows-1251', 'UTF-8//IGNORE', $row[$key]); } } $mysql_query = $mysql -> prepare(" INSERT INTO `firms` ( firm_id, firm_name, address, image ) VALUES ( ?, ?, ?, ? ) "); $mysql_query -> execute( array( $row['firm_id'], $row['firm_name'], $row['address'], $row['image'] ) ); if ( !$mysql_query ) { print_r( $mysql_query->error ); break; } }

But one problem crept in. After copying, the size of binary data in the image field in the mysql database does not exceed 64 kilobytes. It happens and less, but the threshold of 64 kilobytes is not exceeded. When you try to download an image from the database, the image is downloaded and even opened by phpMyAdmin means, but it is cropped.

For example: - Left - the image is copied from the mssql database into the mysql database. The size is 64 512 bytes. - Right - the original image in the mssql database. The size is 87 047 bytes.

enter image description here

Before refactoring, everything worked fine.

Type field image в mssql - image . Type field image в mysql - MEDIUMBLOB

That is, the problem is not in the tables but most likely in the method of working with the database - PDO .

Then I found out about bindColumn , bindParam and PDO::PARAM_LOB , but after applying the result is the same. I also found out that the images come from the mssql database already cropped. That is, images are cropped not on this record in mysql , but at the stage of sampling from mssql . I've been picking up the Internet for a week and find nothing sensible. Comrades, I need your help!

  • cut in the sense of broken? PS: for this procedure in general php must be used, i.e. Is this not an extra link? I'm generally talking about Linked Servers or integration services - teran

1 answer 1

I googled a bit and found such a discussion: https://bugs.php.net/bug.php?id=38805

Several options are offered, someone writes that everything is working, someone still suffers.

Try

 $pdo->query('SET TEXTSIZE 300000');

or

 SELECT CONVERT(TEXT,field) as field_name from table

When retrieving data. Or both options - suddenly help)

  • Thank! $ pdo-> query ('SET TEXTSIZE 300000'); helped I did not think it was some kind of bug. - Alexander Mirovov
  • Check the correct answer then :) - Ilya Kovalyov