Solved

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

Posted on 2015-01-29
22
48 Views
Last Modified: 2016-05-27
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
0
Comment
Question by:Insoftservice
  • 10
  • 9
  • 2
22 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 250 total points
ID: 40580183
Why write code?
Just use one of the open source "community version" ETL programs like CloverETL or Pentaho.
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 40580515
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.
0
 
LVL 15

Author Comment

by:Insoftservice
ID: 40581429
ok .
But what about my first issue of using load data from one server to other.
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 40581438
Can you post the section of the script that attempts to load the csv file and the complete error message when it fails?
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 40581472
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?
0
 
LVL 15

Author Comment

by:Insoftservice
ID: 40581536
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
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 40581597
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.
0
 
LVL 15

Author Comment

by:Insoftservice
ID: 40581644
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
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 40581712
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.
0
 
LVL 15

Author Comment

by:Insoftservice
ID: 40582548
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 :)
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 28

Expert Comment

by:FishMonger
ID: 40582598
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?
0
 
LVL 15

Author Comment

by:Insoftservice
ID: 40582681
ys it runs properly
0
 
LVL 15

Author Comment

by:Insoftservice
ID: 40582702
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.
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 40582893
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.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40584376
Why write code?
Just use one of the open source "community version" ETL programs like CloverETL or Pentaho.
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 40589492
@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:
mysql:
    Dropped support for LOAD DATA LOCAL INFILE handlers when using libmysql. Known for stability problems
0
 
LVL 15

Author Comment

by:Insoftservice
ID: 40592954
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.
0
 
LVL 15

Author Comment

by:Insoftservice
ID: 40596993
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
0
 
LVL 15

Author Comment

by:Insoftservice
ID: 40616408
i got my answer thx for ur help.
At least please help me for my point 1
0
 
LVL 28

Assisted Solution

by:FishMonger
FishMonger earned 250 total points
ID: 40618988
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.
0
 
LVL 15

Author Comment

by:Insoftservice
ID: 40620723
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 .
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

This article discusses four methods for overlaying images in a container on a web page
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now