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
Os:linux
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
Os:linux
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok .
But what about my first issue of using load data from one server to other.
But what about my first issue of using load data from one server to other.
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%';
Is mysql running on the Windows server or the Linux server?
mysql> show variables like 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.00 sec)Is mysql running on the Windows server or the Linux server?
ASKER
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
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.
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.
ASKER
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
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.
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.
ASKER
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 :)
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?
Did you try running the sql statement from your web server outside of php, i.e., from the mysql CLI, like I suggested?
ASKER
ys it runs properly
ASKER
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.
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.
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.
Using sleep() won't help.
Find out what privileges your user was assigned.
show grants;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.
@insoftservice,
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:
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:
mysql:
Dropped support for LOAD DATA LOCAL INFILE handlers when using libmysql. Known for stability problems
ASKER
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.
Let me check the version from the vendor.
ASKER
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
"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
ASKER
i got my answer thx for ur help.
At least please help me for my point 1
At least please help me for my point 1
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 .
I would try it .
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.