Xaprb

Why MySQL says the server is not configured as a slave

with 13 comments

13 Responses to 'Why MySQL says the server is not configured as a slave'

Subscribe to comments with RSS

1. Yeah . The whole server-id thing is amazingly stupid.

   Why can’t a slave compute it’s own server-id and why is it even needed in the first place? Can’t you hashcode the IP address or even turn the IP address into a 32bit int?

   I realize that none of these situations are PERFECT but some more reasonable defaults would be nice!

   

   Kevin Burton

   2 Aug 07 at 2:03 am
2. Hi, found your blog, love it, may find my answer as I go thru it. I posted this on the mysql forum and got not response :( care to take a shot ?

   – THanks, Andrew

   I have a table named JOB that looks like this:

   ————– ————— —— —– ——— —————-
   | Field | Type | Null | Key | Default | Extra |
   ————– ————— —— —– ——— —————-
   | id | int(11) | NO | PRI | NULL | auto_increment |
   | created_at | datetime | YES | | NULL | |
   | processed_at | datetime | YES | | NULL | |
   ————– ————— —— —– ——— —————-

   I have been processing this from a single thread by just finding the oldest non-completed job and processing it…

   - SELECT id FROM job WHERE processed_at IS NULL ORDER BY created_at;
   - UPDATE job SET processed_at = CURDATE() where id =
   - process the job

   Now I need to speed things up a bit, and need to process these jobs from multiple threads. The code above does not work, as the same job could get processed twice.

   What’s the best practice way to do something like this ?

   

   andrew

   2 Aug 07 at 12:52 pm
3. dead link to an article

   this post
   http://www.xaprb.com/blog/2007/01/11/how-to-implement-a-queue-in-sql/

   reference this post
   http://www.xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql/“

   which seems to be missing ?

   

   andrew

   2 Aug 07 at 12:55 pm
4. Tsk, tsk, off topic :-)

   I’d update the job and set a ‘processor’ column to CONNECTION_ID(), like this:

   update job
   set processor = CONNECTION_ID()
   where processor is null
      and processed_at is null
   limit 1;
   
   select * from job where processor = CONNECTION_ID() ....

   You need to handle the case where your processing application claims a job but never finishes it, in which case nothing else would ever claim it. I’d have a periodic job to just reset processor to NULL after the job has been claimed but not processed for a while.

   

   Xaprb

   2 Aug 07 at 9:17 pm
5. Andrew, thanks for the note. I was missing a quote in a link. Fixed now.

   

   Xaprb

   2 Aug 07 at 9:19 pm
6. I find it useful to run a RESET SLAVE command before running the CHANGE MASTER command. This helps keep weird things from happening.

   

   Keith Murphy

   14 Aug 07 at 4:47 pm
7. If this is on the master

   character-set-server=utf8
   collation-server=utf8_unicode_ci

   default-character-set=utf8
   default-collation=utf8_unicode_ci

   Make sure it is on the slave too (my.cnf)

   

   Anonymous

   7 Dec 07 at 9:14 pm
8. My problem hadn’t been solved until I restared mysqld. If you are in the same situation, try it!

   

   Zahra

   21 Oct 08 at 5:57 am
9. Hoo boy, I ran into a doozy today. Related to Zahra’s posting, be sure you restart the actual mysqld service itself, and check out the log that results. I use Plesk and in the details I found that it was ignoring the my.cnf changes because the file was world-writable (I had to make it that way temporarily so I could get to it via my FTP tool).

   Be sure your my.cnf isn’t world-writable or else MySQL will silently ignore it.

   

   Matt

   28 Oct 08 at 5:29 pm
10. If your slave server does not accept your my.cnf
    you can set the server-id like this in your mysql client:

    SET GLOBAL server_id = 2;

    (but you should try to fix the problem with your my.cnf :] )

    

    Eddie

    31 May 10 at 5:34 am
11. Mssr. Burton: sometimes you have >1 mysqld on one IP address, at which point, you have to figure out how to fit >32bit of information into 32bit of server-id.

    Hopefully your entire network is 10.* in which case you can use the last three octets + port to generate a server-id. But I presided over a cluster that was mixed 192.168.* and 10.*. In that case you must encode the 192.168-ness, the 10-ness, and the 172.16-ness into a byte, hope you can use the last two octets, then encode the port into the other byte.

    My cluster of course mixed and matched the 2nd octet in the 10 network, so I had to come up with the most convoluted server-id generating algorithm ever, the details of which I’ve thankfully forgotten.

    Doing a hash is another option, but hash collisions are in the realm of possibility with only 32 bits, so I would shy away from it. What DO you do when there’s a hash collision?

    

    PhiloVivero

    13 Jan 11 at 4:05 pm
12. Hey, Thanks! I have no idea how my server id got changed but that is why i couldn’t get my replication fixed. I wonder if it was working with both server_id’s set to 1 before or what

    

    scotty

    14 Jul 11 at 12:13 pm
13. You’ve saved me again! This was a really tricky problem to debug.

    Our fix was to explicitly set ‘server-id’ in my.cnf , even though `SHOW VARIABLES LIKE ‘server_id’` shows this is a `1`. I have a more detailed description of my problem at http://dba.stackexchange.com/questions/4400 .

    Baron, I love your stuff.

    If I run into a particularly difficult problem with MySQL replication, and I google around enough, I eventually come across one of your posts somewhere.

    I had no idea that this was your blog until some other guy said “Thanks Baron”. Ironically, I’ve been debugging this for a couple hours with the “High Performance MySQL” book sitting open in front of me.

    

    Stefan Lasiewski

    5 Aug 11 at 5:01 pm

Leave a Reply