Mysqld stalls
I've been trying to fine tune my mysqld settings in my.cnf on a very busy server. It seems to be doing fine, as in the server loading. BUT every so often I see the number of processes spike and then it sesms mysqld is unresponsive through httpd and I end up having to restart mysqld to get it going again. What settings should I be tweaking ? Mark Susol -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Key file
On one database, I keep seeing similar errors related to the key file for the different tables. 1034 Incorrect key file for table: Can't read key from filepos: 672768 error 5 when fixing table Can't copy datafile-header to tempfile, error 9 How do I diagnose if the database file structure is corrupt? What do these errors in key file point to as a root cause? MySQL 4.0.17 Mark Susol -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Key file
On one database, I keep seeing similar errors related to the key file for the different tables. 1034 Incorrect key file for table: Can't read key from filepos: 672768 error 5 when fixing table Can't copy datafile-header to tempfile, error 9 How do I diagnose if the database file structure is corrupt? What do these errors in key file point to as a root cause? MySQL 4.0.17 Mark Susol -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL4 and phpBB
I have recently setup a new server running EnsimPro 3.7 (Fedora) and upgraded the MySQL to 4.0.17-max per their tech notes. Now, I'm having troubles with tables related to my most busiest site becoming corrupt. The site in question uses phpBB. We've checked the hard drive out and don't think we have an issue with it. We did map out any bad blocks on install. The corruption is only with this one site. My sys admin believes this may be some I/O problem at the kernel level with this version of MySQL. The phpBB has been very stable for years even on a RaQ4 running RH6.2 and MySQL 4.0.15..albeit that server could barely handle the load. But since moving to this new install I'm struggling with problems. Is anyone experiencing problems with MySQL 4.0.17-max (other versions) and a lot of I/O type errors on large databases or especially running phpBB? Yes I'm asking on Ensim phpBB support sites for help as well, but this may also be a compile/module issue with MySQl and the server. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking tables
The general answer to your question, if you're willing to cooperate with the server, is to lock the tables from within the server so that no other clients can modify them, and use FLUSH TABLES to flush any changes to disk. While the lock remains in place, copy the table files. Then unlock the tables. Remember that the client that locks the table *must remain connected* while you copy the table files, because any locks are released automatically when the client connection ends. Have a look at the mysqlhotcopy source for any idea of how it uses this approach. Essentially, what it does is open a connection to the server, tells the server to lock the tables, and then while the tables are locked, goes behind the server's back to directly copy table files. (This is why mysqlhotcopy must be run on the server host. It's also (I believe) why it doesn't work on WIndows: Windows file locking semantics do not allow you to copy a file while the server has it locked.) By the way, it's difficult to see how automysqlbackup could corrupt any tables. A quick look through it seems to indicate that it only uses mysqldump to perform backups. Yes the script is using mysqldump with --opt , so these seems like what to expect for mysqlhotcopy. When you read the MYSQL docs, mysqlhotcopy apears to be the tool for choice for live servers. Maybe I'm not interpeting the documentation correctly, maybe it refers to having to be on the SAME server to run. I want the automysqlbackup script to run from cron at 4am..one site in question is very busy still during that time. What does the user experience who may in the middle of a write action to the database as the server starts the mysqldump --opt process? How can I do myisamchk with tables locked to make sure the tables are not corrupt before I try this shell script again? I want to make sure I start clean before I try it so that if it repeats the problem I can relate the problem to the action just performed. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking tables
I've found a nice shell script to use to backup my server's MySQL databases. https://sourceforge.net/projects/automysqlbackup/ However, when I tried this earlier today it resulted in a corrupt table. Now the table in question is one I've had issues with for other reasons, but it has over 2 mil records in it when only partially built. What I need to do is put a lock on the database/tables before the script is run. The script allows for a pre post shell script to run. So what do I need to do before running any backup scripts to ensure the tables will not be corrupted during any kind of shell operation? Do I have to stop services like mysqld or httpd before hand? Does anyone use MySQL 4.0.18 and the hot copy instead? I'm using 4.0.17-max Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking tables
Did I really ask that tough a question? Anyone? On 4/15/04 7:38 PM, Mark Susol | Ultimate Creative Media [EMAIL PROTECTED] wrote: I've found a nice shell script to use to backup my server's MySQL databases. https://sourceforge.net/projects/automysqlbackup/ However, when I tried this earlier today it resulted in a corrupt table. Now the table in question is one I've had issues with for other reasons, but it has over 2 mil records in it when only partially built. What I need to do is put a lock on the database/tables before the script is run. The script allows for a pre post shell script to run. So what do I need to do before running any backup scripts to ensure the tables will not be corrupted during any kind of shell operation? Do I have to stop services like mysqld or httpd before hand? Does anyone use MySQL 4.0.18 and the hot copy instead? I'm using 4.0.17-max Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP + MySQL Not Playing Nice Any More
On 4/15/04 11:46 PM, Joseph A. Nagy, Jr. [EMAIL PROTECTED] wrote: So I'm using PHP and MySQL to serve up a game and all is going well until today. The first problem came when for some reason the game was sending apache as the username to access the db (which is not what I have in the dbconnect file) and output some errors. I checked the page it was complaining about and all was good so I use phpmyadmin to login and admin my db but now even phpmyadmin won't let me in and I know I haven't touched the config file since I first set it up. Does MySQL do this very often or is it a PHP error and not a MySQL one and if this is a PHP error where do I look to fix it? This seems more like a change was made in your hosting environment, unrelated to php or mysql. I've seen this happen when sites were moved into safe moded environments. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slightly OT: MySQL + PHP and escaping data to output to a web page
On 4/15/04 11:49 PM, Daniel Kasak [EMAIL PROTECTED] wrote: I'm currently using: replace(replace(NoteText, '', '#060'), '', '#062') as NoteText to replace the offending characters with HTML escape codes. Is there a cleaner way of doing this? You can try the php command: htmlspecialchars($NoteText, ENT_NOQUOTES); ref: http://us3.php.net/htmlspecialchars Best place for php questions is www.sitepoint.com Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dumping mass inserts?
I'm trying only to update one table from a backup. I have the sql file stripped down to just the INSERT commands. I have emptied the table from phpMyadmin to start. How do I load this sql file so it runs all of the inserts in this file through ssh (70MB)? Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping mass inserts?
On Friday 09 April 2004 09:52 am, Mark Susol | Ultimate Creative Media wrote: I'm trying only to update one table from a backup. I have the sql file stripped down to just the INSERT commands. I have emptied the table from phpMyadmin to start. How do I load this sql file so it runs all of the inserts in this file through ssh (70MB)? FTP the sql file to the machine/account that you are ssh'ing into. Then do mysql -u username -p databasename sql_file_to_load James I'm doing that, but its echoing inserts that are not in that sql file. I don't have any other sql file by the name I'm trying to load. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping mass inserts?
Ah..I see now. I was still using mysqldump..instead of mysql. Yes I'm trying to import the data from backup, into an empty table since the data was corrupt. Looks like you are on the right track now! :) James Ok now I want to dump tables with names starting with phpads so I can move them to another db. How is the mysqldump command run then? And when I go to move them to the new db, I do use the mysqldump command since I am dumping to the new db? Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Corruption and my.cnf
I've experienced more corruption lately on my main site since I moved to my own server running 4.0.17max. The site is very busy (60GB a month) and the tables are large. I didn't have this level of problems on the rental server. What are the variables to look into regarding why my tables are getting corrupt? Is this a my.cnf issue? Is this a memory issue? Is this a hard drive issue? Is this a too many connections issue? Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using PHP to copy tables
Is there an easy way to create an HTML page that will copy selected tables to backup copies on the same server? I want to create an administration page for my client to be able to backup their database whenever they see fit. But, I can't give them direct access to the MySQL server and don't want them backing up to their hard drive. I prefer to simply copy the tables to backup versions on the server so that if problems arise, I can log into the server and simply copy the backups to the originals. Here is what I use... Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using PHP to copy tables
Is there an easy way to create an HTML page that will copy selected tables to backup copies on the same server? I want to create an administration page for my client to be able to backup their database whenever they see fit. But, I can't give them direct access to the MySQL server and don't want them backing up to their hard drive. I prefer to simply copy the tables to backup versions on the server so that if problems arise, I can log into the server and simply copy the backups to the originals. Here is what I have used before...but this might be for you to run as a cron task and be transparent to them. http://www.silisoftware.com/scripts/index.php?scriptname=backupDB Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corruption and my.cnf
I've experienced more corruption lately on my main site since I moved to my
own server running 4.0.17max. The site is very busy (60GB a month) and the
tables are large. I didn't have this level of problems on the rental server.
What are the variables to look into regarding why my tables are getting
corrupt? Is this a my.cnf issue? Is this a memory issue? Is this a hard
drive issue? Is this a too many connections issue?
Looking at my dmesg output..
end_request: I/O error, dev 03:42 (hdb), sector 52228450
hdb: read_intr: status=0x59 { DriveReady SeekComplete DataRequest Error }
hdb: read_intr: error=0x40 { UncorrectableError }, LBAsect=56276830, high=3,
low=5945182, sector=52228450
Is it possible this is related to my MySQL table corruption issues? There
are more of these in the file.
Mark Súsol
---
u l t i m a t e CreativeMedia
Web | Print | CD Media | eCommerce
www.ultimatecreativemedia.com
Ph: 301-668-0588
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too Many Connections
What is the best way to diagnose the root cause of this error? What scripts are doing the connecting and totalling them up? Warning: mysql_connect(): User ultimated has already more than 'max_user_connections' active connections I have a very active phpBB but I'm on a new server and its not pulling a server loading over 0.5. I ran some data before (crontab php script gathered the info for me every 5 minutes for several weeks) and the problem happened before related to server loading..not necessarily how many users I had on that site posting. That was an older Cobalt RaQ4. I seemed to be having a lot of search bots accessing the site then. [mysqld] set-variable = max_connections=512 set-variable = max_user_connections=200 set-variable = key_buffer=64M set-variable = table_cache=256 set-variable = sort_buffer=4M set-variable = wait_timeout=300 I've only had this problem this week, its run 3 weeks fine. I do have a corrupted MYI file according to myisamck. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql and phpBB
I'm working with a script that is made for phpBB to rebuild the search engine. The script does work. I've been able to use it with limited success. The problem is I haven't been able to use it with complete success. The problem I'm having is that when it attempts to run its query, which selects X post texts from a table $sql = SELECT post_id, post_subject, post_text FROM . POSTS_TEXT_TABLE . LIMIT $start, . $HTTP_GET_VARS['post_limit']; $result = $db-sql_query ($sql); I will eventually get Warning: mysql_query(): Unable to save result set Which comes from the connection script line: $this-query_result = mysql_query($query, $this-db_connect_id); Someone suggested I use mysql_unbuffered_query() on another site. I've never had this kind of error before with this site. Its a new server I've deployed. Are there my.cnf settings I need have in order to run a script that will work on 300,000 records doing 10 at a time even? Even if I try this with 1 or 2 posts text I get this error. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and phpBB
You may have a corrupt table. I would recommend printing the $query just before mysql_query() is ran. This will give you the query that is failing and the table that it is attempting to write to. May want to try a check table on this table. SELECT post_id, post_subject, post_text FROM phpbb_posts_text LIMIT 30 , 10 MySQL said: #1030 - Got error 127 from table handler This is really weird. I'm showing a gap in posts_ID from 240915 to 4089168. This script has run before from 300,000 to the end before. Now if I check this table I get: Checking MyISAM file: phpbb_posts_text.MYI Data records: 324989 Deleted blocks: 0 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check record links myisamchk: Error reading file 'phpbb_posts_text.MYD' (Errcode: 5) myisamchk: error: got error: 5 when reading datafile at record: 3627 MyISAM-table 'phpbb_posts_text.MYI' is corrupted record: 3627 is where my script give me the error. Warning: mysql_query(): Unable to save result set I've run a check on this table before and it was ok. I don't know how this datafile would have been corrupted. Now, how can I remove that datafile specifically? I fear trying to rebuild that table for fear of it failing. Mark Susol -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
