File upload problem

[nas123]

Hello,

I have an upload controller which uploads file into a mysql database and
it works fine on my local machine where I am using webrick but when I
use it on a live apache server then it gives me the following error:

ActiveRecord::StatementInvalid in <controller not set>#<action not set>

Mysql::Error: You have an error in your SQL syntax near ''4749463' at
line 1: INSERT INTO pictures (`content_type`, `name`, `photo`,
`user_id`) VALUES('image/gif', 'arrow.gif',
x'4749463839610a3c68d0208080003', 1)

I guess this insert query is ok as it is working on the webrick so the issue must be something else.

I would be really thankful if someone could help me how to resolve this.

Thanks

[rob]

Are you using the file_column plugin? If so, make sure the public/ folder, especially the folder that matches the name of your model that will have the file, has write permissions.

[nas123]

Hi rob,

No, I am not using file_column plugin but I just don't know why it is giving this error on apache while it is working fine on webrick. Any other idea?

Thanks in advance

[motobass]

Quick test - can you successfully run that sql statement in mysql on the OCS server? That is, log in via ssh and run it straight against the db.

[nas123]

thanks motoboss,

I tried to run:

insert into pictures (user_id, name, photo) values (3,'phot1.jpg', x'2323');

on my local machine and it worked fine but when I tried the same on server then it gave mysql error:

ERROR 1064: You have an error in your SQL syntax near ''2323')' at line 1

So why is it working on one machine and not on another ???

Thanks in advance!

[rob]

On your local machine, run:

mysql -V

and do the same on your server. Paste both the versions.

[nas123]

Hi rob,

On the local machine = 5.0


On the server = 3.23.58

I just found out that it is probably due to the x'hextring' format in which the data is going into the blob type column and mysql versions before 4.01 support 0x format. Hence it is working on local machine but giving the syntax error on the server.

Any idea on how to change this to 0x format before loading it into the mysql. In my rails model, I am using:
self.picture = picture_field.read

to receive data for the mysql

Thanks in advance.

[rob]

I know that's not our server running 3.x ;)

I used to store files in the db too until file_column (http://www.kanthak.net/opensource/file_column/) came along. Take a look, it will make things so much easier on you.

If you want to use the db however, I would recommend if possible to upgrade the server (or have your admin upgrade it) to at least a 4.x series. The 3.x series of MySQL server had so many issues :(

[nas123]

The hosting company is not ready to upgrade the mysql as they have the control panel and other stuff tied up with the current database. So instead they suggested to install another instance of higher version of mysql.

So I will try that and see how it goes.

Anyway thanks for your help.

[rob]

No problem. Good luck!

[nas123]

I spent almost 24 hours to install another instance of mysql and I am really frustrated now.

I installed it in a different directory than the default mysql, gave a different pid file name, different location for datadir and sock but it always gives error.

If I run using this command

/usr/local/mysql2/bin/mysqld_safe --user=mysql --datadir=/usr/local/mysql2/data

then i get this

Starting mysqld daemon with databases from /usr/local/mysql2/data
STOPPING server from pid file /var/run/mysqld/mysqld.pid
060505 11:59:53 mysqld ended

or with command

/usr/local/mysql2/bin/mysqld_safe --user=mysql --pid-file=/usr/local/mysqld/mysqld.pid --datadir=/usr/local/mysql2/data

I get this

Starting mysqld daemon with databases from /usr/local/mysql2/data
STOPPING server from pid file /usr/local/mysqld/mysqld.pid
060505 11:39:40 mysqld ended


I dont have a clue why its happening, It just never starts..Any ideas??

[rob]

Can you post the last bits of the log file?

It should be in the /usr/local/mysql2/log directory or similar.

[nas123]

Nothing in any log. I am leaving it for now and will try it later when I have time.

Thanks a lot rob for replying to all my queries.

[rob]

No problem. Glad to help.