innodb mysql crash
Hello, today I got this in my logs and mysql stopped working. InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html InnoDB: how to resolve the issue. 071107 8:46:26 InnoDB: Flushing modified pages from the buffer pool... 071107 8:46:26 InnoDB: Started; log sequence number 1 1008136481 /var/mysql/mysql-4.1.18/libexec/mysqld: ready for connections. Version: '4.1.18-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution InnoDB: Error: page n:o stored in the page read in is 538976288, should be 1644! 071107 8:46:28 InnoDB: Error: page 538976288 log sequence number 538976288 538976288 InnoDB: is in the future! Current system log sequence number 1 1008136645. InnoDB: Your database may be corrupt. InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 1644. InnoDB: You may have to recover from a backup. 071107 8:46:28 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 2020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020 071107 8:46:29 InnoDB: Page checksum 138226020, prior-to-4.0.14-form checksum 3161919040 InnoDB: stored checksum 538976288, prior-to-4.0.14-form stored checksum 538976288 InnoDB: Page lsn 538976288 538976288, low 4 bytes of lsn at page end 538976288 InnoDB: Page number (if stored to page already) 538976288, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 538976288 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 1644. So great, that mysql doesn't even tell me at which table or database to look. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page. Number of processes running now: 2 mysqld process hanging, pid 19729 - killed mysqld process hanging, pid 19729 - killed 071107 08:46:29 mysqld restarted 071107 8:46:29 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 071107 8:46:29 [ERROR] Do you already have another mysqld server running on port: 3306 ? 071107 8:46:29 [ERROR] Aborting 071107 8:46:29 [Note] /var/mysql/mysql-4.1.18/libexec/mysqld: Shutdown complete 071107 08:46:29 mysqld ended I cannot count any more how often I had problems with innodb. I regret ever compiled it in, but now that a lot of users are using it, I need to recover from the problem. But there is one major problem: All error messages and recovery documentation of innodb sound, as if I would deal with just a few tables. But I have hundrets of databases with thousands of tables! So there is no fast dump and insert of tables. Any ideas (besides from dumping everything and importing at then)? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to migrate from MySQL 3 to MySQL 5 (installed from sources)
Hi. Working on Linux, I've got installed MySQL 3.23.58, but I'm experiencing some problems. For instance, I suffer too many connections error but I cann't set 'max_connections' parameter to a value bigger than 250. I decided to migrate to MySQL 5.0, and I've got some questions: - I installed MySQL from sources on '/usr/local/mysql'. Is it as easy as installing it again on '/usr/local/mysql5'? - what must I back up to migrate data? '/usr/local/mysql/var' directory? - is there any tutorial/webpage to carry out this migration? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to migrate from MySQL 3 to MySQL 5 (installed from sources)
On Wed, 2007-11-07 at 10:22 +0100, thomas Armstrong wrote: Hi. Hello, Working on Linux, I've got installed MySQL 3.23.58, but I'm experiencing some problems. For instance, I suffer too many connections error but I cann't set 'max_connections' parameter to a value bigger than 250. ... and does not compile on 64 bits, and... :) I decided to migrate to MySQL 5.0, and I've got some questions: - I installed MySQL from sources on '/usr/local/mysql'. Is it as easy as installing it again on '/usr/local/mysql5'? Yep. Just change the '--prefix' com './configure' time. - what must I back up to migrate data? '/usr/local/mysql/var' directory? - is there any tutorial/webpage to carry out this migration? I would like to suggest that you do one dump on old database, stop the old and restore on newer database. Just copy the datafiles can work on small upgrades (mysql 3 - 4) but I don't know what is the problem of this, I prefer the basic: dump and restore. -- Tiago Cruz http://everlinux.com Linux User #282636 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to migrate from MySQL 3 to MySQL 5 (installed from sources)
Hi Tiago. Thank you very much for your answer. I decided to migrate to MySQL 5.0, and I've got some questions: - I installed MySQL from sources on '/usr/local/mysql'. Is it as easy as installing it again on '/usr/local/mysql5'? Yep. Just change the '--prefix' com './configure' time. ok - what must I back up to migrate data? '/usr/local/mysql/var' directory? - is there any tutorial/webpage to carry out this migration? I would like to suggest that you do one dump on old database, stop the old and restore on newer database. Just copy the datafiles can work on small upgrades (mysql 3 - 4) but I don't know what is the problem of this, I prefer the basic: dump and restore. So you're suggesting to: - install MySQL 5 from sources on '/usr/local/mysql5' without starting it - dump data from MySQL 3 - stop MySQL 3 - start MySQL 5 - restore data in MySQL 5 - delete MySQL 3 is it right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to migrate from MySQL 3 to MySQL 5 (installed from sources)
Hi All Make sure you use the mysqldump from version 5 and not version 3. Also recheck all your application queries once you have restored the data the list of changes SQL syntax might haunt you. Mark thomas Armstrong wrote: Hi Tiago. Thank you very much for your answer. I decided to migrate to MySQL 5.0, and I've got some questions: - I installed MySQL from sources on '/usr/local/mysql'. Is it as easy as installing it again on '/usr/local/mysql5'? Yep. Just change the '--prefix' com './configure' time. ok - what must I back up to migrate data? '/usr/local/mysql/var' directory? - is there any tutorial/webpage to carry out this migration? I would like to suggest that you do one dump on old database, stop the old and restore on newer database. Just copy the datafiles can work on small upgrades (mysql 3 - 4) but I don't know what is the problem of this, I prefer the basic: dump and restore. So you're suggesting to: - install MySQL 5 from sources on '/usr/local/mysql5' without starting it - dump data from MySQL 3 - stop MySQL 3 - start MySQL 5 - restore data in MySQL 5 - delete MySQL 3 is it right? -- Mark Carson Managing Integrated Product Intelligence CC EMail : [EMAIL PROTECTED]/[EMAIL PROTECTED] snailmail : P.O. Box 36095 Menlo Park 0102, South Africa Cell : +27 83 260 8515 This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION intended only for use of the addressee. If you are not the addressee, or the person responsible for delivering it to the person addressed, you may not copy or deliver this to anyone else. If you received this e-mail by mistake, please do not make use of it, nor disclose it's contents to anyone. Thank you for notifying us immediately by return e-mail or telephone. INFORMATION PROVIDED IN THIS ELECTRONIC MAIL IS PROVIDED AS IS WITHOUT WARRANTY REPRESENTATION OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND THE USE OF THIS DOCUMENT. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to migrate from MySQL 3 to MySQL 5 (installed from sources)
Make sure you use the mysqldump from version 5 and not version 3. Also recheck all your application queries once you have restored the data the list of changes SQL syntax might haunt you. But can I use '/usr/local/mysql5/bin/mysqldump' to dump data of MySQL 3? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to migrate from MySQL 3 to MySQL 5 (installed from sources)
Never tried it with version 3 only 4.1 but it has a --compatible switch with a version 3.23 switch see command line --help so I assume the mysql guys are allowing you to dump 3.23. Try dumping it without the switch as you will be restoring to 5.0. The idea is to make sure the restore is done using a version 5 compatible dump. Keep us posted. thomas Armstrong wrote: Make sure you use the mysqldump from version 5 and not version 3. Also recheck all your application queries once you have restored the data the list of changes SQL syntax might haunt you. But can I use '/usr/local/mysql5/bin/mysqldump' to dump data of MySQL 3? -- Mark Carson Managing Integrated Product Intelligence CC EMail : [EMAIL PROTECTED]/[EMAIL PROTECTED] snailmail : P.O. Box 36095 Menlo Park 0102, South Africa Cell : +27 83 260 8515 This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION intended only for use of the addressee. If you are not the addressee, or the person responsible for delivering it to the person addressed, you may not copy or deliver this to anyone else. If you received this e-mail by mistake, please do not make use of it, nor disclose it's contents to anyone. Thank you for notifying us immediately by return e-mail or telephone. INFORMATION PROVIDED IN THIS ELECTRONIC MAIL IS PROVIDED AS IS WITHOUT WARRANTY REPRESENTATION OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND THE USE OF THIS DOCUMENT. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NFS
Charles Jardine wrote: I am planning to set up a system in which mysql servers access their data via NFS. All the computers involved run Solaris 10. NFS version 4 will be used. I plan to ensure that no NFS share is ever mounted by more than one client computer, and that no client computer ever runs more than one mysql server. There will thus be no attempt to share data between mysql servers using NFS. I am not planning to use ndbd. I have searched the manuals and found nothing that tells me that this will not work. All the warnings are either about performance, or about data sharing. You don't care about performance? Mysql has never performed on NFS for me. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
NFS
I am planning to set up a system in which mysql servers access their data via NFS. All the computers involved run Solaris 10. NFS version 4 will be used. I plan to ensure that no NFS share is ever mounted by more than one client computer, and that no client computer ever runs more than one mysql server. There will thus be no attempt to share data between mysql servers using NFS. I am not planning to use ndbd. I have searched the manuals and found nothing that tells me that this will not work. All the warnings are either about performance, or about data sharing. Are there other reasons why I should not do as I plan? -- Charles Jardine - Computing Service, University of Cambridge [EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger problem
My apologies, try this: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; ;; DELIMITER ; To answer your question: The DELIMITER statement tells MySQL to use a different set of characters to terminate statements. This is necessary when you want to use a ; in your actual statement. In this case, the entire trigger definition is considered one statement, but the ; in the DELETE... line is being interpreted as the termination of it. Yes, it's dumb. On Nov 7, 2007, at 2:53 AM, Lucky Wijaya wrote: No, I didn't set the delimiter. But, it still have an error after I set delimiter in my trigger as your example. By the way, what's delimiter mean ? And what it's for ? Thanks to you Mr. David. David Schneider-Joseph [EMAIL PROTECTED] wrote: Lucky, Did you make sure to set your delimiter before and after the CREATE TRIGGER statement? e.g.: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END;; DELIMITER ; On Nov 6, 2007, at 11:11 PM, Lucky Wijaya wrote: Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with create procedure
Marc
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or
READS SQL DATA in its declaration and binary logging is enabled (you *might*
want to use the less safe log_bin_trust_function_creators variable)
It has to do with whether the fnc is deterministic and how its results might replicate. Since the controls implied by these attributes are not in fact implemented, the simplest solution is
SET GLOBAL log_bin_trust_routine_creators=1;
PB
-
Pau Marc Munoz Torres wrote:
Hi
I'm working with mysql 5.0.24a-log trying to create a procedure as is
indicated at mysql web page
and i get the following error before delimiter ;
mysql delimiter //
mysql CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
- RETURN CONCAT('Hello, ',s,'!');
- //
Query OK, 0 rows affected (0.00 sec)
mysql delimiter ;
and i get the following error before delimiter ;
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or
READS SQL DATA in its declaration and binary logging is enabled (you *might*
want to use the less safe log_bin_trust_function_creators variable)
Can some body tell me what should I do?
thanks
Pau
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.15.24/1115 - Release Date: 11/7/2007 9:21 AM
Re: Corrupting MySQL on purpose for testing
Try putting the data directory on a small partition and let it get filled 100% with a single update that does not get fully committed due to disk full, then turn the power off to the computer so there is no attempt for a nice shutdown. You should also get a corruption in the master binary logs, if binary logging is turned on. If you use VMWare (vmware server is free) to virtualize a test OS with small drives, it will be easier to do this. -RG Richard Edward Horner wrote: Hi, I'm working on a program that will run in the event that tables are crashed or corrupt. Can anyone recommend a good method for crashing my tables or corrupting them so I have some test cases? I'm mostly interested in MyISAM and InnoDB table types. I was thinking I would just start an infinite loop with an UPDATE statement in it and then kill -9 the mysqld process. Any better ideas? Thanks, Rich(ard) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding MySql Service
Your mysql server is either not running, or is not creating the sock file at the location /var/lib/mysql/mysql.sock that calendar.php expects. What do you get when you do this? linux$ ls -la /var/lib/mysql/mysql.sock look in the my.cnf file to find where the MySQL sock is written to, and modify your PHP application to use that sock file. You can also dig through your MySQL error logs to make sure MySQL is not having any problems creating the sock file nor performing a normal startup. -RG piyush joshi wrote: Dear all, Right Now I am having different kind of problem in mysql server . mysqld service automatically goes off . when we open a website and enter something to search from mysql database it gives the following error - *Warning: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/httpd/html/php/mdp/calendar.php on line 97* *Warning: MySQL Connection Failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/httpd/html/php/mdp/calendar.php** on line 97* *Db Error* Kindly tell me where is the problem ..i will be grateful to you ..Thanks in advance .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with create procedure
Hi
I'm working with mysql 5.0.24a-log trying to create a procedure as is
indicated at mysql web page
and i get the following error before delimiter ;
mysql delimiter //
mysql CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
- RETURN CONCAT('Hello, ',s,'!');
- //
Query OK, 0 rows affected (0.00 sec)
mysql delimiter ;
and i get the following error before delimiter ;
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or
READS SQL DATA in its declaration and binary logging is enabled (you *might*
want to use the less safe log_bin_trust_function_creators variable)
Can some body tell me what should I do?
thanks
Pau
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de BarcelonaE-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
Re: NFS
Hi Charles, all ! Charles Jardine wrote: I am planning to set up a system in which mysql servers access their data via NFS. [[...]] If you want to use this for testing or development - fine. If you want to run performance measurements on this - silly. If you want to use it for production work - change your plans. [[...]] I have searched the manuals and found nothing that tells me that this will not work. All the warnings are either about performance, or about data sharing. work may mean different things to different people. I am quite sure that setup will give correct results for some tiny installation tests, but I am also quite sure its performance and its reliability will be limited (see below). I would *not* use it for production purposes. Are there other reasons why I should not do as I plan? Immediately, I know these: - Database performance can be limited by CPU or disk I/O. - If it is disk: Each disk access via NFS has the network roundtrip latency added to the disk response time, and depending on the number of servers you might even run into network bandwidth limits. So I/O performance will suffer, and database performance will do the same. - If it is CPU, this means you have plenty of cache (RAM), slow CPU, and relatively fast disk. In this case, performance *might* remain constant, but I would not rely on that. - Accessing the data via NFS adds complexity and single points of failure to your setup. If either of these machines (or the network connecting them) is down, all your database is down - the NFS setting reduces reliability. - The database server and the disk machine may fail separately, for various reasons, and disk failures may go unnoticed by the database software. Recovery after failures may be difficult (or impossible) in a NFS setting. - Several database algorithms rely on ordered writes for structure changes and/or logging. I strongly doubt NFS will ensure this write order. - Administrative effort will be higher, because you have to deal with several machines for one database and have to do updates synchronized. - I do not follow NFS versions. I know that earlier ones had issues with locking; I cannot tell whether that is still valid. There may be several others. IMNSHO, you should have the server and the data it accesses in the same box - either several (smaller, independent) machines with local disks, or one (larger, multi-CPU and lots of RAM). What is your reason to use NFS for the database files ? HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help creating query statement
Guys, just wanted to thank you again for helping me with the sql statement that I needed. I was able to sorted using php and I was able to display the correct result. Thanks again!!1 Nestor :-) On Nov 6, 2007 7:37 AM, Néstor [EMAIL PROTECTED] wrote: You guys are correct, that is exactly what happened. I must thing of this in the future. At this moment I have a lot of other projects to take care, that it is eaiser for me to read the information into an associative array with the columns and the values and sort the array and then print the top 5 values within each array. Thanks, Nestor :-) On 11/6/07, Enrique Sanchez Vela [EMAIL PROTECTED] wrote: --- [EMAIL PROTECTED] wrote: Néstor wrote: I think you'd best begin by normalising your database. Something along these lines: very true indeed, that would save you major headaches when right after finishing the demo, someone would say, can we add a 18 gallon pledge? what about a 25? as of the sort of columns per row, I believe it is not possible nor in the goals of MySQL to make it possible/easy. best regards, enrique. -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character encoding problem
Hello, We are trying to upgrade from 4.0 to 5 and we are not having any luck on the character sets. We need to use French characters for some things. We have tried setting utf8 for everything we can find but the characters still show all screwed up.. I have read the manual regarding all the variables and have placed all I can in the my.ini etc. Can someone who has it working post the relevant info from their ini file along with any help for other setting we need to change? It would be GREATLY appreciated. --James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
one-liner perfectionist function question
DROP FUNCTION IF EXISTS secs_to_hrs;
DELIMITER |
create function secs_to_hrs (secs varchar(10))
returns varchar(13)
DETERMINISTIC
BEGIN
return CONCAT_WS(' h ',lpad(secs/60 div 60,3,' ') ,
CONCAT(lpad(round(secs/60 mod 60),2,' '),' mins') );
END;
|
DELIMITER ;
What if I wanted to not display mins if they're 0?
In oracle I could possibly stick a decode in there.
I can probably break up the function into a few lines and make it work,
but curious if others had a one-liner addition that I could do.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupting MySQL on purpose for testing
At 05:22 PM 11/6/2007, you wrote: Hi David, Thanks for your reply. This helped greatly. Yeah, I supposed I could just randomly flip bits in the MySQL files and that would do the trick. The program I'm writing does first attempt to repair the tables with MySQL's utilities if they're MyISAM but InnoDB tables don't support repair and this program is actually meant to be a potential stop-gap solution in the event of hardware errors in a non-failover situation which can do really horrible things like corrupt InnoDB inodes thus rendering tables unusable to keep clients seeing SOMETHING (even if it's stale data) until the hardware can be swapped out. Speaking of which, can anyone think of a reason that InnoDB tables would corrupt aside from hardware problems or a bug in the storage engine? David, Bad network cards could corrupt the data or flaky memory. Here are a few links that deal with InnoDb corruption. http://www.mysqlperformanceblog.com/2006/07/30/mysql-crash-recovery/ http://www.mysqlperformanceblog.com/2007/05/22/magic-innodb-recovery-self-healing/ http://www.google.ca/search?hl=enq=innodb+corruption+causesbtnG=Searchmeta= Mike On Nov 6, 2007 6:04 PM, David T. Ashley [EMAIL PROTECTED] wrote: On 11/6/07, mos [EMAIL PROTECTED] wrote: At 02:18 PM 11/6/2007, Richard Edward Horner wrote: Hi, I'm working on a program that will run in the event that tables are crashed or corrupt. Can anyone recommend a good method for crashing my tables or corrupting them so I have some test cases? I'm mostly interested in MyISAM and InnoDB table types. I was thinking I would just start an infinite loop with an UPDATE statement in it and then kill -9 the mysqld process. Any better ideas? Well, there are different types of corruption. Here are a few that come to mind: a)Corruption of a MySQL file, perhaps not repairable (disk hardware problem, OS crash, server power supply failure). b)Corruption in a way that is repairable by a MySQL utility (index file corrupted). c)Application corruption (bad application doesn't handle mutual exclusion correctly, has logical errors, etc.). Hopefully database repair utilities that come with MySQL will map (a) and (b) to be indistinguishable from (c). (But I don't know that--I'm barely a MySQL user.) The most even-handed way to do it would seem to be: a)Write a program that will create a random number of distributed and block corruptions distributed randomly in the MySQL files. (Such a program would take very little time to execute--well under half a second, I'm guessing.) You might want to throw in truncations and blocks added at the end of files, too. b)See how far the MySQL utilities (if there are any) followed by your software will go towards repair. Doesn't seem like an easy thing to test. -- Richard Edward Horner Engineer / Composer / Electric Guitar Virtuoso [EMAIL PROTECTED] http://richhorner.com - updated June 28th -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character encoding problem
Hello, Thanks for the info, For the dump, I would imagine that it is latin1 as mysql 4.x is defaulted to that I beleive. Is there a way to dump it to utf8? --James - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 07, 2007 4:23 PM Subject: Re: Character encoding problem James Sherwood wrote: Hello, We are trying to upgrade from 4.0 to 5 and we are not having any luck on the character sets. We need to use French characters for some things. We have tried setting utf8 for everything we can find but the characters still show all screwed up.. I have read the manual regarding all the variables and have placed all I can in the my.ini etc. Can someone who has it working post the relevant info from their ini file along with any help for other setting we need to change? Are you referring to data that is inserted via the client? If so, make sure to issue the following upon connecting: SET NAMES utf8; SET CHARACTER SET utf8; Was this dumped from the version 4.x DB? Are you sure that it really is UTF-8? If this is a web app, do you see any difference betwen the browser and a terminal? If you're looking at it through a browser, ensure that the correct character set header is sent out. brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ NOD32 2644 (20071107) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character encoding problem
Hello, After I dump the database in and change everything to utf8(or if I try to dump and make them all utf8 on the dump) I get an incorrect string value error when trying to put french characters in. Any ideas?. --James - Original Message - From: James Sherwood [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 07, 2007 6:16 PM Subject: Re: Character encoding problem Hello, Thanks for the info, For the dump, I would imagine that it is latin1 as mysql 4.x is defaulted to that I beleive. Is there a way to dump it to utf8? --James - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 07, 2007 4:23 PM Subject: Re: Character encoding problem James Sherwood wrote: Hello, We are trying to upgrade from 4.0 to 5 and we are not having any luck on the character sets. We need to use French characters for some things. We have tried setting utf8 for everything we can find but the characters still show all screwed up.. I have read the manual regarding all the variables and have placed all I can in the my.ini etc. Can someone who has it working post the relevant info from their ini file along with any help for other setting we need to change? Are you referring to data that is inserted via the client? If so, make sure to issue the following upon connecting: SET NAMES utf8; SET CHARACTER SET utf8; Was this dumped from the version 4.x DB? Are you sure that it really is UTF-8? If this is a web app, do you see any difference betwen the browser and a terminal? If you're looking at it through a browser, ensure that the correct character set header is sent out. brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ NOD32 2644 (20071107) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ NOD32 2644 (20071107) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character encoding problem
Hello, I too strict mode off on advice of a post in a forum. It allowed me to enter the items but they do not go in.. I am using EMS mysql manager sql editor to do it. This is my variables: Variable_name Value character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server utf8 character_set_system utf8 character_sets_dir C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ The SHOW FULL COLUMNS FROM `newsletter`; says the colums are utf8 and the table is utf8 When I do this statement it basically makes the field blank: SET NAMES utf8; SET CHARACTER SET utf8; update `newsletter` set `newsletter`.`story` = é û ê à èé û ê à èé û ê à è where `newsletter`.`news_id` = 35; Can anyone shed some light on what I am doing wrong? --James - Original Message - From: James Sherwood [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 07, 2007 7:37 PM Subject: Re: Character encoding problem Hello, After I dump the database in and change everything to utf8(or if I try to dump and make them all utf8 on the dump) I get an incorrect string value error when trying to put french characters in. Any ideas?. --James - Original Message - From: James Sherwood [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 07, 2007 6:16 PM Subject: Re: Character encoding problem Hello, Thanks for the info, For the dump, I would imagine that it is latin1 as mysql 4.x is defaulted to that I beleive. Is there a way to dump it to utf8? --James - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 07, 2007 4:23 PM Subject: Re: Character encoding problem James Sherwood wrote: Hello, We are trying to upgrade from 4.0 to 5 and we are not having any luck on the character sets. We need to use French characters for some things. We have tried setting utf8 for everything we can find but the characters still show all screwed up.. I have read the manual regarding all the variables and have placed all I can in the my.ini etc. Can someone who has it working post the relevant info from their ini file along with any help for other setting we need to change? Are you referring to data that is inserted via the client? If so, make sure to issue the following upon connecting: SET NAMES utf8; SET CHARACTER SET utf8; Was this dumped from the version 4.x DB? Are you sure that it really is UTF-8? If this is a web app, do you see any difference betwen the browser and a terminal? If you're looking at it through a browser, ensure that the correct character set header is sent out. brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ NOD32 2644 (20071107) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ NOD32 2644 (20071107) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ NOD32 2644 (20071107) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character encoding problem
James Sherwood wrote: Hello, Thanks for the info, For the dump, I would imagine that it is latin1 as mysql 4.x is defaulted to that I beleive. Is there a way to dump it to utf8? You can convert it using iconv: http://climbtothestars.org/archives/2004/07/18/converting-mysql-database-contents-to-utf-8/ There's a Win version available if you don't already have it: http://gnuwin32.sourceforge.net/packages/libiconv.htm Be sure to edit the CHARSET refs in the dumpfile, though: sed -i 's/latin1/utf8/g' dump.sql This article may be useful: http://dev.mysql.com/tech-resources/articles/4.1/unicode.html brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group By and IF statement
I am attempting to get a simple query working: select *,MAX(a.teaching_date) as max, MIN(a.teaching_date) as min from teaching a, topic_cat b where a.teaching_topic = b.topic_id or a.teaching_topic = 999 group by a.teaching_topic order by a.teaching_date DESC This all works, except I don't want to 'group by a.teaching_topic' if a.teaching_topic = 999. Anyone know how to do this? Thank you. -- View this message in context: http://www.nabble.com/Group-By-and-IF-statement-tf4768518.html#a13639835 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
