0

I'm trying to let a windows perl script run on linux, but it's not work, I want to know what mistake I made.

Originally, it was running on windows and connect to the local mysql db, but now I want to transfer it to linux. I installed docker on Linux and wanted to connect to the mysql db in container, but the revised script kept reporting an error

On Windows mysql version is 5.1
on Linux mysql version is 8.0

original script on windows

foreach my $file (@files) {
    $command = '-e "LOAD DATA LOCAL INFILE \''.$file.'\' REPLACE INTO TABLE test FIELDS TERMINATED BY \'|\' LINES TERMINATED BY \'\\r\\n\' IGNORE 1 LINES (@v001, @v002, @v003, @v004, @v005) SET `CA` = TRIM(@v001), `CB` = TRIM(@v002),`CD` = TRIM(@v003),`CE` = TRIM(@v004),`CF` = TRIM(@v005);"';
    system('mysql', '-hlocalhost', $user, $password, $command)  == 0 or err("ERROR:Failed to load file to test: $! \n");
    $nfile = "D:\\DONE\\".getmodifydate($file); #file last modify time
    move($file, $nfile);
}

On Linux

sub docker_mysql {
    $docker_mysql = "mysql", "-h$localhost", $database, $user, $password;
}

system(docker_mysql(), '-e', "DELETE FROM test WHERE 1") == 0 or 
    err("ERROR:Failed to delete record from test: $! ");
 
foreach my $file (@files) {
    $command = "LOAD DATA LOCAL INFILE \'$file'\' REPLACE INTO TABLE test FIELDS TERMINATED BY \'|\' LINES TERMINATED BY \'\\n\' IGNORE 1 LINES (\@v001, \@v002, \@v003, \@v004, \@v005) SET `CA` = TRIM(\@v001), `CB` = TRIM(\@v002),`CD` = TRIM(\@v003),`CE` = TRIM(\@v004),`CF` = TRIM(\@v005);";
    system(docker_mysql(), '-e', $command,)  == 0 or 
        err("ERROR:Failed to load file to test: $! \n");
    $nfile = "./DONE/".getmodifydate($file); #file last modify time
    move($file, $nfile);    
}

This is the error code after execution

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near '|' LINES TERMINATED BY '
' IGNORE 1 LINES (@v001, @v002, @v003, @v004, @v005) SE' at line 1
3728eo3
  • 1
  • 2
  • Please share the error message/the complete output you are getting when running the script on Linux. – treuss Oct 26 '22 at 07:10
  • updated error code, thanks for reminding – 3728eo3 Oct 26 '22 at 08:21
  • You have a literal newline inside your SQL statement. That will probably break the command on the command line. Also, why not use the DBI module? – TLP Oct 26 '22 at 14:07
  • Linux does not have C: and D: drives – stark Oct 26 '22 at 14:38
  • I am only responsible for modifying and executing on LINUX, not writing it myself, I do not want to change the overall architecture, and I have changed to LINUX path – 3728eo3 Oct 27 '22 at 01:25
  • Indeed, why not to use the DBI module? It would be much easier to handle queries. Or DBI module is not available/installed and you can not install it (even in your local account)? What you do utilizes shell and is very inefficient in resource utilization. – Polar Bear Oct 27 '22 at 19:20

1 Answers1

0

You need to escape the backslash that is part of \n to send an actual line-feed, not an n.

    $command = "[...] LINES TERMINATED BY \'\n\' [...]";
                                            ^^ This should be \\n
treuss
  • 1,913
  • 1
  • 15