mysql query is not working in perl

Question

I am beginner for perl developer. i am connecting mysql by perl script . but when i try hit some queries. all queries are running successfully except one. and where i use that query on mysql console it runs successfully.

#!/usr/bin/perl -w

use DBI;

$dbh1 = DBI->connect('dbi:mysql:testing;host=localhost', 'root', '93C0o35A9/692fz') or die "Connection Error: $DBI::errstr\n";

$dbh1->do('stop slave');
$dbh1->do('CHANGE MASTER TO MASTER_HOST=54.254.154.33, MASTER_USER=replica, MASTER_PASSWORD=aims145, MASTER_LOG_FILE=mysql-bin99, MASTER_LOG_POS=107');
$dbh1->do('start slave');

first and third queries are running fine while second one is showing syntax error as follows

 ./newmysql.pl

DBD::mysql::db do failed: 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 '54.183.14.179, MASTER_USER=replica, MASTER_PASSWORD=aims145, MASTER_LOG_FILE=mys' at line 1 at ./newmysql.pl line 8.

enter image description here Please help me here where i am wrong.

ebo · Accepted Answer · 2014-08-04 17:07:14Z

1

The documentation recommends to quote the values of the different variables, try:

$dbh1->do("CHANGE MASTER TO MASTER_HOST='54.254.154.33', MASTER_USER='replica', MASTER_PASSWORD='aims145', MASTER_LOG_FILE='mysql-bin99', MASTER_LOG_POS=107");

edited Aug 4, 2014 at 17:07

answered Aug 4, 2014 at 17:00

ebo

2,7471 gold badge27 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Borodin Over a year ago

It is perverse to use single quotes on a string that has a multitude of single quotes!

Len Jaffe Over a year ago

So replace the outer single-quotes with double-quotes, and then you don't need to escape the inner single-quotes.

Borodin Over a year ago

@LenJaffe: Yes, almost anything else will do: "...", q{...}, qq/.../ but choosing the only special character that appears in the string is a big mistake

ebo Over a year ago

I used the suggestion of @LenJaffe to update the answer.

Borodin Over a year ago

@EricBouwers: I hope that was sarcastic? If not then please realise that you are not answerable to me. If so, then remember that Stack Overflow is all about creating crowd-sourced answers. SO itself says, "Our goal is to have the best answers to every question, so if you see questions or answers that can be improved, you can edit them". I tend to make comments, as I did here, rather than modify solutions; but if I am sure that something is wrong I will amend it eventually. Thanks for your input here

|

Borodin · Accepted Answer · 2014-08-04 17:08:21Z

0

I am not confident that administrative commands like that can work through DBI (and I can't test it at present) nor do I think you should be doing things that way, but I think the problem may be that the parameters to the command aren't quoted properly.

I suggest you change the command to this. At least it will make it more readable

$dbh1->do(<<'__END_SQL__');
CHANGE MASTER TO
  MASTER_HOST     = '54.254.154.33',
  MASTER_USER     = 'replica',
  MASTER_PASSWORD = 'aims145',
  MASTER_LOG_FILE = 'mysql-bin99',
  MASTER_LOG_POS  = 107
__END_SQL__

edited Aug 4, 2014 at 17:08

answered Aug 4, 2014 at 17:02

Borodin

127k9 gold badges72 silver badges146 bronze badges

Comments

user2916639 · Accepted Answer · 2014-08-05 17:57:42Z

0

$dbh1->do("CHANGE MASTER TO MASTER_HOST=\'54.183.14.179\', MASTER_USER=\'replica\', MASTER_PASSWORD=\'aims145\', MASTER_LOG_FILE=\'$mainip[0]\', MASTER_LOG_POS=$mainip[1] ");

answered Aug 5, 2014 at 17:57

user2916639

4281 gold badge6 silver badges20 bronze badges

Collectives™ on Stack Overflow

mysql query is not working in perl

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related