phpmyadmin blob insert error

[Q695]

I'm trying to use phpMyAdmin to put a blob video into the database (for ease of handling), and for some reason when I put the video into the blob, and submit it, phpMyAdmin bounces back to the home page, and says "no change".  Does anyone know what's going wrong in the file?

 

I've done the same upload with the image preloader just fine, but when I go to do the video it errors.

[jazzman1]

Sounds like the upload file limitation directive is lower than the video file you're uploading.

[Q695]

How do you fix the error then?

 

I'm performing the upload localhost.

[Q695]

When I delete the MySQL column for the video, and just upload the corresponding image it provides the following error on PHP MyAdmin:

 

MySQL said:

#2006 - MySQL server has gone away

 

If someone wants to write it out by hand, this is the table description (for an image/video storage server):

Item - INT, AI

Image - Blob

Video - Long Blob

Edited March 4, 2014 by Q695

[Q695]

I saw PHP myadmin had an update, so I checked the WAMP version of my testing server, and noticed that I'm running WAMP2.1 for testing my code, maybe that will solve the error

 

still says:

MySQL said:

#2006 - MySQL server has gone away

 

Edited March 4, 2014 by Q695

[jazzman1]

 

I've done the same upload with the image preloader just fine, but when I go to do the video it errors.

 

For the application server, try the following code and tell me if you're getting some error or warning:

<?php

error_reporting(-1);

if(!empty($_POST['uploadedFile'])) {
echo 'file count=', count($_FILES),"\n";
echo '<pre>'.print_r($_FILES, true).'</pre>';
echo "\n";
}
?>

<form enctype="multipart/form-data" action="" method="post">
	 Choose a file to upload: <input name="uploadedFile" type="file" /><br />
	 <input type="submit" value="Upload File" />
</form>

For the db server I'm going to provide a different option, because I'm not familiar with phpMyAdmin, WAMP and windows versions in general.

 

Open up the command prompt in your windows machine, and start mysql command line tool. After you've been connected once to the db server and chosen your database try to insert this video file directly to db table.

 

It could be similar like this:

INSERT INTO table VALUES(LOAD_FILE(C:/full/path/to/video.mp4));

If you got the error like shown above " MySQL server has gone away", find where the mysql conf file inside your wamp application is and add next line into it, then restart the db server. 

 

max_allowed_packet = 1024M

[Q695]

I see no error in this, but is it running anything:

 

For the application server, try the following code and tell me if you're getting some error or warning:

<?php

error_reporting(-1);

if(!empty($_POST['uploadedFile'])) {
echo 'file count=', count($_FILES),"\n";
echo '<pre>'.print_r($_FILES, true).'</pre>';
echo "\n";
}
?>

<form enctype="multipart/form-data" action="" method="post">
	 Choose a file to upload: <input name="uploadedFile" type="file" /><br />
	 <input type="submit" value="Upload File" />
</form>

On the old server:
Warning: POST Content-Length of 18640412 bytes exceeds the limit of 8388608 bytes in Unknown on line 0

Edited March 4, 2014 by Q695

[Q695]

I have a longblob storing an image now, but can't seem to insert a 17.7mb video into another longblob.

[jazzman1]

Hey Q695,

 

sorry for the delay but I'm so busy and tired these days. 

 

So find and open up the php conf file named php.ini with your code editor and change next lines:

 

upload_max_filesize = 200M and post_max_size = 200M, then restart your web server and try again!

<?php

error_reporting(-1);

echo 'file count=', count($_FILES),"\n";
echo '<pre>'.print_r($_FILES, true).'</pre>';
echo "\n";

?>

<form enctype="multipart/form-data" action="" method="post">
	 Choose a file to upload: <input name="uploadedFile" type="file" /><br />
	 <input type="submit" value="Upload File" />
</form>


// Result with my binary file 

file count=1

Array
(
    [uploadedFile] => Array
        (
            [name] => xss.mp4
            [type] => video/mp4
            [tmp_name] => /tmp/phpGIb4ZW
            [error] => 0
            [size] => 37174389
        )

)

Do you try to upload the video file directly using insert and LOAD_FILE() function like in my example above?

 

PS: I will try to provide an example with this later on.

Edited March 4, 2014 by jazzman1

[jazzman1]

Here it's a test using my local db server.

 

Create a table named binary_data:

CREATE TABLE `binary_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` longblob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Next, try to insert the video file into data column with current filesize of 36MB. 

[lxc@localhost ~]$ du xss.mp4 
36304	xss.mp4

// insert the binary file
[lxc@localhost ~]$ echo "INSERT INTO binary_data VALUES (NULL,LOAD_FILE ('/home/lxc/xss.mp4'))" | /usr/bin/mysql test -h ::1 -u lxc -ppassword

// select the LENGTH of the data
[lxc@localhost ~]$ echo "SELECT CHARACTER_LENGTH(data) FROM binary_data" | /usr/bin/mysql test -h ::1 -u lxc -ppassword
CHARACTER_LENGTH(data)
37174389

PS: The char_length sql command lists the file in Bites, the du command in kB.

Edited March 4, 2014 by jazzman1

[Q695]

I'll build one on my own, and try to figure out the upload.  I'm shocked that I can't do it VIA phpMyAdmin.

[jazzman1]

I'll build one on my own, and try to figure out the upload.  I'm shocked that I can't do it VIA phpMyAdmin.

 

Did you find where the mysql configuration file in wamp is? Add the following string to the [mysqld] section, restart the db server and try to upload the video data via phpMyAdmin.

 

max_allowed_packet = 1024M  // allow to upload a binary data file with size up to 1GB

 

[Q695]

I moved all the files to the new version of WAMP, but now I can upload 2 images, but not a video in any way/shape/form through var_dump($_FILES);.

Edited March 8, 2014 by Q695

[jazzman1]

Let me see the content of php.ini and my.cnf files.

[Q695]

[jazzman1]

Why don't you try what I suggested you above

 

Find post_max_size = 8M and change the value to 200M.

 

Find upload_max_filesize = 2M and change the value to 200M.

 

Then, restart the server, wamp application or entire windows machine and try my testing code from reply #9.

 

I don't have any idea where the mysql configuration file is, I'm not a windows user. Sorry for that, google it.

 

PS, I found this , try next path -> c:\wamp\bin\mysql\mysqlx.y.z\my.ini

Edited March 9, 2014 by jazzman1

[Q695]

So this doesn't work

<?php
ini_set('upload_max_filesize', '200M');
?>

That stuff worked to get the array output

 

array (size=2)
'image' =>
array (size=5)
'name' => string 'Koala.jpg' (length=9)
'type' => string 'image/jpeg' (length=10)
'tmp_name' => string '__________________' (length=24)
'error' => int 0
'size' => int 780831
'video' =>
array (size=5)
'name' => string '____________' (length=26)
'type' => string 'video/mp4' (length=9)
'tmp_name' => string '______________' (length=24)
'error' => int 0
'size' => int 18640198

Edited March 10, 2014 by Q695

[Q695]

So this doesn't work

<?php
ini_set('upload_max_filesize', '200M');
?>

That stuff worked to get the array output

 

 

array (size=2)
'image' =>
array (size=5)
'name' => string 'Koala.jpg' (length=9)
'type' => string 'image/jpeg' (length=10)
'tmp_name' => string '__________________' (length=24)
'error' => int 0
'size' => int 780831
'video' =>
array (size=5)
'name' => string '____________' (length=26)
'type' => string 'video/mp4' (length=9)
'tmp_name' => string '______________' (length=24)
'error' => int 0
'size' => int 18640198

 

my.ini:

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is C:\mysql\data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password    = your_password
port        = 3306
socket        = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[wampmysqld]
port        = 3306
socket        = /tmp/mysql.sock
key_buffer = 16M
max_allowed_packet = 1M

sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir=c:/wamp2/bin/mysql/mysql5.6.12
log-error=c:/wamp2/logs/mysql.log
datadir=c:/wamp2/bin/mysql/mysql5.6.12/data

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Disable Federated by default
skip-federated

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id    = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
#tmpdir        = /tmp/        
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld]
port=3306

[jazzman1]

 

So this doesn't work!

 

Did you save the file after changes?

# The MySQL server
[wampmysqld]
port        = 3306
socket        = /tmp/mysql.sock
key_buffer = 16M
max_allowed_packet = 1M

To the database sever, change the value of max_allowed_packet = 1M to 100M, save the file and restart mysql server, then try to upload the video file directly via phpMyAdmin.

[Q695]

( ! ) Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 37280537 bytes) in ___________________phpmyadmin4.0.4\tbl_replace.php on line 229 Call Stack # Time Memory Function Location 1 0.0282 357312 {main}( ) ..\tbl_replace.php:0 2 0.9770 100010840 implode ( ) ..\tbl_replace.php:229

 

the test file is: 17.7 MB (18,640,198 bytes)

Edited March 10, 2014 by Q695

[jazzman1]

Well, I am exhausted on what is wrong here. You could try to use a mysql_stmt_send_long_data() function to send a large data to MySQL server in pieces.

[Q695]

I just got the upload to work with a video the size of 452 KB (463,539 bytes), just need to scale it up to a max of 100MB, possibly more later.

[Q695]

Screw my original plan, renaming a file upon upload is looking a lot more comfortable now.

[Q695]

Solved it this way:

<?php
if (isset($_FILES['video']) && $_FILES['video']['error']==0 && $_FILES['video']['size']<=209715200 && $_FILES['video']['type']=='video/mp4'){
    $target_path="video/___/";

    $img_name=$_FILES['video']['name'];
    $img_type=$_FILES['video']['type'];
    $img_size=$_FILES['video']['size'];
    $img_tmp_name=$_FILES['video']['tmp_name'];
    move_uploaded_file($img_tmp_name, $target_path.$___.".mp4") or die ("Video Upload Error, Please Try Again.");
    echo "Video successfully uploaded";
} else if(isset($_FILES['video']) && $_FILES['video']['size']>=1) {
    echo"Please try to upload your video again.";
}
?>

There may be a better way to do this, but I'm happy for now.