Sunday, March 08, 2009

A deceiving error message while setting a replication slave

I was setting up a slave manually for a quick-and-dirty experiment, when I found this one:

mysql %gt; slave start;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Hmm... I did the CHANGE MASTER TO already, but just for the sake of it, let's do it again.

mysql %gt; change master to master_host='127.0.0.1', master_port=22346,
master_user='msandbox', master_password='msandbox',
master_log_file='mysql-bin.000002', master_log_pos=106;
Query OK, 0 rows affected (0.00 sec)

mysql %gt; slave start;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Again! Why is it asking to execute CHANGE MASTER TO, which has been accepted right now?

Then it came to me. Because I did a manual configuration, and I didn't set the server_id.

mysql [localhost] {msandbox} ((none)) %gt; set global server_id=4000;
Query OK, 0 rows affected (0.00 sec)
mysql %gt; slave start;
Query OK, 0 rows affected (0.00 sec)

Yep. That was it. The error message could have been more helpful, though.

4 comments:

arjenAU said...

The added nasty is that if server-id is 0, then it'll show up internally as 1 but it still remembers that it's not really set. So it will look ok but not be. You need to check the my.cnf to figure it out. Annoying.

Consequently, when I set this up for a client, I now never use 1 just so that it's absolutely clear when it's explicitly configured.

gmax said...

@arjenAu,
in my experience, when server_id is set to zero, it shows up as 0.
What do you mean by "internally"?

arjenAU said...

This is from mem, but feel free to test it with a sandbox! If you don't set it at all, it shows up as 1 but really isn't set.
(sorry for confusion in earlier comment about setting to 0)

gmax said...

@arjenAu,
nope. I tested already when I posted my previous one. If you set it to zero, or you don't set it, it shows up as 0.

Giuseppe

Vote on Planet MySQL