Load in mysql data transfer from one server to another and one more issue

I have two queries due to geo location of my servers

php scripting language

1> I have mysql on one server and php code on other server.
I save the csv file on both the server .
If file is saved on script side and executed the code works fine  .

"LOAD DATA LOCAL INFILE  "/var/www/pathofcsv file of mysql server" INTO TABLE" this code is executed from php server even replaced it with path of php server but result failed.

2 > Other issue is that my client is unaware to do upload part. so the offline software which is running on his windows server would dump the file in one of the  decided folder from where i have to pick up the above file after regular interval
     i was thinking to write a window batch file which would read if some file are dump in the desired location and then upload it on server. Please suggest if there is any other better solution as i tried to such code but failed or please provide the script if readily available
LVL 15
Who is Participating?
MikeOM_DBAConnect With a Mentor Commented:
Why write code?
Just use one of the open source "community version" ETL programs like CloverETL or Pentaho.
There are multiple ways to accomplish this task.

One would be to create a samba share on the Linux server and create a drive mapping on the Windows server to the samba share.

When the csv file is created (presumably on the Windows server), it gets saved under the drive that is mapped to the samba share.

The Linux server would have a cron job running (your php script) which moves the file from that share point to a "processing" directory and the php script then loads it into mysql and then either archives it somewhere or deletes it.
InsoftserviceAuthor Commented:
ok .
But what about my first issue of using load data from one server to other.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Can you post the section of the script that attempts to load the csv file and the complete error message when it fails?
verify that you have local_infile enabled on the server

mysql> show variables like 'local%';
| Variable_name | Value |
| local_infile  | ON    |
1 row in set (0.00 sec)

Open in new window

Is mysql running on the Windows server or the Linux server?
InsoftserviceAuthor Commented:
Its running in linux server. show variables like 'local%' is on .  I have written the sample code in my question its small section as this codes work if executed directly on mysql server.
Some one told me that file should be present on mysql server with mysql permission.
My file right currently is 777 roor:root but if some one could let me know what should be mysql and how to check who should be the owner it would be great.
Is there some other way means to dump this data on php server and later copy the whole table to mysql server no mysqldump as these would again require file
So the php server and mysql are both on the same Linux server?

If that's the case, then why are you using LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE and how does the Windows server factor into the process?

On which server is the csv file located and is it on the same server as mysql and the script that loads into the db?

So far your description of your setup is very confusing.
InsoftserviceAuthor Commented:
There are two servers . one mysql and other php which is linux servers and one is client m/c.
1> Csv is in client m/c which is windows . offline software would dump these csv file in desired location which has to be uploaded by window batch file .
2> These file is uploaded on php server which would execute load() and would dump in mysql server.
Currently i have skipped step1 and have dumped  csv file manually on php server and mysql too. but the load local file is not loading the data on mysql server.
If i execute same on mysql server it works. It even works if i try to dump the data on mysql credential present on php server. so load() further code is working fine as per my knowledge .
Only part remains is of file location and its permission.
I hope its clear now
Do you run the php script as a web app or as a command line program?

What error message do you get?  Do you even have php error reporting enabled?

Try running the sql statement directly from the mysql CLI (from the client) and see what error mysql reports.

Have you looked at the mysql logs to see what the related entries are for the import?

Do you have the general query log enabled?

Do you have sufficient space in the server's temp directory to hold the csv file?


I know I have more questions than answers, but proper answers can't happen without knowing more details and the questions I'm asking should lead you to basic troubleshooting.
InsoftserviceAuthor Commented:
I run this code via web. I am not getting any error have used mysql_error() for the same .
Ya i have too much space to save the log file.
As i mentioned eariler if i run directly on mysql server it executes properly .
I don't know whether mysql logs are enable as this both are shared servers .
I save the Query what i am trying to execute via php using error_log()
Its fine i can understand even i do the same thing while trying to resolve some one else issue :)
If the import fails, then mysql will have a log entry indicating the problem.  In my experience it's not uncommon for php to not report certain types of errors, which is one reason I hate using php.  Since your db is on a shared server, I assume you don't have access to its logs or the ability to enable the general query log.

Did you try running the sql statement from your web server outside of php, i.e., from the mysql CLI, like I suggested?
InsoftserviceAuthor Commented:
ys it runs properly
InsoftserviceAuthor Commented:
I hope i have found the issue. this code runs after few queries .
All queries does not get executed at mysql level. I hope they have restriction while executing code from other servers.
for eg:
1> truncate table_old ;
2> insert from table to table_old
3> truncate table;
4> alter  table add column
5> load
6> alter table add column2 and shift data from column to column2

while executing it shows that all queries has been executed but when checked only 3 queries got executed i had checked when  i ran the script via browser . please note this code is executed on port 80 via curl method from other server .
Is there some method to chk the limitation like such or should i use sleep() so that its executes properly.
Why are you using curl?  That seems to be an odd way to run sql statements.

Using sleep() won't help.

Find out what privileges your user was assigned.
show grants;

Open in new window

As I've previously stated, if one or more statements fail, there will be a mysql error message, but it appears that php is not returning the error.  Without access to the log files, it will boil down to pure guess work.  From the beginning I suspected that the problem would most likely be mysql permissions, and is part of the reason I've tried to find out what error mysql was returning.

The inability to review the log files and not have rights to control mysql is one of the main reasons I will never use a shared server.
Why write code?
Just use one of the open source "community version" ETL programs like CloverETL or Pentaho.

Are you still having this issue, or has it been fixed?

If still a problem, what version of php are you using and are you using the mysql_ or mysqli_ functions in your db calls?  I ask because I just discovered a possible cause, while researching another issue.

Extracted from the v5.5.0 change log:
    Dropped support for LOAD DATA LOCAL INFILE handlers when using libmysql. Known for stability problems
InsoftserviceAuthor Commented:
oh that might be the case.  As other server (mysql) is behaving in strange fashion . Some times it accepts some of my request some time it denies .Creating confusion at my end.
Let me check the version from the vendor.
InsoftserviceAuthor Commented:
Got the issue
"2006: MySQL server has gone away"

I had requested them to on the mysql logs. Through which i got the error. It has been now resolved as i am pasting the
connectivity script in between twice or thrice is there some better way to do so.

where i can check whether connectivity is proper or not and if its not then create the connection again

Found other issue after doing the above process it loads only 2092 records out of 20k records.
And give error "Lost connection to MySQL server during query" is there any solution
InsoftserviceAuthor Commented:
i got my answer thx for ur help.
At least please help me for my point 1
FishMongerConnect With a Mentor Commented:
If the csv file is located on the db server and the php script is on a separate web server, then you should remove the LOCAL keyword from the statement.  This is documented in the official mysql documentation.

The LOCAL keyword would be used if the csv file was on the client side i.e., the web server where the php script is located.

If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

When using LOCAL with LOAD DATA, a copy of the file is created in the server's temporary directory. This is not the directory determined by the value of tmpdir or slave_load_tmpdir, but rather the operating system's temporary directory, and is not configurable in the MySQL Server. (Typically the system temporary directory is /tmp on Linux systems and C:\WINDOWS\TEMP on Windows.) Lack of sufficient space for the copy in this directory can cause the LOAD DATA LOCAL statement to fail.

 If LOCAL is not specified, the file must be located on the server host and is read directly by the server. The server uses the following rules to locate the file:

    If the file name is an absolute path name, the server uses it as given.

    If the file name is a relative path name with one or more leading components, the server searches for the file relative to the server's data directory.

    If a file name with no leading components is given, the server looks for the file in the database directory of the default database.
InsoftserviceAuthor Commented:
thanku @FishMonger but too delay as i had already written script to send the data in part but really great full to all EE comments.
I would try it .
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.