Mysql Slave Replication not running on some slaves. - urgent!

Mysql replication with one master and several slaves but replication has suddently stopped working on a few slaves.
Version 4.0.22-standard
The table status on the slaves not working is show this error:
Error 'Can't find file: './%db%/%table_name%.frm' (errno: 24)  

I added %db% and %table% name as placeholders because db and table names not relevant.  This db and table does exist on master and is replicating to other machines.

Slave status also shows:
     Master_Log_File: db-master-bin.420
  Read_Master_Log_Pos: 878231783
       Relay_Log_File: slave2-relay-bin.1118
        Relay_Log_Pos: 21051484
Relay_Master_Log_File: db-master-bin.420
     Slave_IO_Running: Yes
    Slave_SQL_Running: No

How can I restart Slaves correctly?  Why is slave Error saying  it can't find this .fry file???

Please help urgently - will award maximum points.

Many thanks!


duendonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

adrpoCommented:
Hi,

I think you don't have enough file handles available:
http://www.browardphp.com/mysql_manual_en/manual_Problems.html#Not_enough_file_handles

Error code  24:  Too many open files

That is error 24 telling you.
adrpo@...:~> ulimit -n
1024

Try increasing this number:
Login as root and: type:
root@...:~> ulimit -n 2048
See what is happening then.
You might want to set ulimit -n 2048 in your init scripts on your machine.

Cheers,
za-k/
0
adrpoCommented:

More here on how you can prevent this:
http://dev.mysql.com/doc/refman/5.0/en/not-enough-file-handles.html

Cheers,
za-k/
0
duendonAuthor Commented:
Thanks for your quick reply.

I will look into that, thanks - but do you know why that table is now not on the slave? why would it suddenly dissappea causing that table not found error?

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

duendonAuthor Commented:
Ulimit - I have checked that and it returns unlimited - and there is space on the disk.

Also it is affecting several slave machines.
0
adrpoCommented:

Hi,

Even if ulimit -n say unlimited there is a limit in MySQL configuration.
How big is your open_files_limit system variable?
Have a look into your my.cnf and see to what number is:
open_files_limit set to.
You can also check this from mysql client, see below....

More here:
http://dev.mysql.com/doc/refman/4.1/en/option-files.html
http://dev.mysql.com/doc/refman/4.1/en/server-options.html#option_mysqld_open-files-limit

Cheers,
za-k/
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 622   |
+------------------+-------+
1 row in set (0.00 sec)

Open in new window

0
adrpoCommented:

There is a bug in MySQL version less than: 5.1.6
Instead of saying "Too many open files", it says:
Can't find file: './cluster_replication/binlog_index.frm' (errno: 24)
http://bugs.mysql.com/bug.php?id=15020

Which seems exactly like your error.

Cheers,
za-k/
0
duendonAuthor Commented:
It turned out that all I needed do do was START SLAVE and all was ok - thought I needed to solve a big enormous problem before starting sdlave again on each machine but not.

Thanks for your help.
0
adrpoCommented:

For nothing! I'm glad you fixed it!

Cheers,
za-k/
0
duendonAuthor Commented:
So , was the original cause of my error the open files limit?

It affected 6 machines all at the same time.

I want to award you all the points for helping me but I don't want to accept this as a solution if is not solution - eg for other users with my problem.

Please confirm and advise.
Many thanks.
0
adrpoCommented:

Well, I don't know. You don't have to give me the points.
You can request to close this question and get your points refunded.

The error 24 is "Too many open files.", but if you didn't
play since you got the error with your ulimit -n or
with open_files_limit in MySQL settings, then what
I tell you didn't help.

If you only needed to start the slaves again and it worked,
then I don't know what your problem was.
 
Cheers,
za-k/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
duendonAuthor Commented:
Thanks for your help working me through the issue.  Your comments will be useful to future users with similar problems I'm sure.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.