Re: missing ibd files on upgrade of mysql from 4.1.9 to 4.1.12a
Hello. I think that deleting .ibd files is a weird behavior for installer. Did you use innodb_file_per_table? If not, then a new version couldn't understand an old format of InnoDB data files. Probably, you could install a binary copy of MySQL 4.1.9, specify your data directory as it's datadir and make a dump with mysqldump. Then just import it to the new instance of MySQL. Jason Pyeron [EMAIL PROTECTED] wrote: No the user is still the same, there are just no innodb files. ?anymore? On Mon, 4 Jul 2005, Gleb Paharenko wrote: Hello. [EMAIL PROTECTED] gleb]$ perror 1 OS error code 1: Operation not permitted Do you run new MySQL service under different user account? Jason Pyeron [EMAIL PROTECTED] wrote: I get errors like: ERROR 1016 (HY000): Can't open file: 'files.ibd' (errno: 1) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Post-Installation Procedures for Slackware
Hello. For binaries downloaded from mysql.com you should run mysql_install_db. Not sure for others. Thanks. I have read that document. But I wasn't clear as to whether or not I should run mysql_install_db. Looks like I should, 'cuz slack doesn't use RPM. Cheers tim 4.0.20 is rather old, use the latest release (4.1.12). Tim Johnson [EMAIL PROTECTED] wrote: Hello All: I am most familiar with mysql thru RH 9.0 and win XP. I have set up a slackware partition (ver 10.0) with mysql ver 4.0.20. Are there any special instructions for Post-Installation Procedures? thanks tim -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(More Data) Re: Innodb crash on failed read disk
Seems that i have a bad block :( # badblocks -sv /dev/sda3 Checking for bad blocks in read-only mode From block 0 to 10241437 Checking for bad blocks (read-only test): 102414360/ 10241437 done Pass completed, 1 bad blocks found. Ady Wicaksono wrote: Dear All I use RedHat 9 with 2,5 Gbyte RAM, Intel(R) Xeon(TM) CPU 2.80GHz (Hyperthread), filesystem ext3 standar linux journaling filesystem. Today my DB is crash :(, here is the log. I try to : 1. shutdown MySQL, unmount harddisk partition used by MySQL innodb data file and doing fsck.ext3 on it and found that partition is clean |# fsck.ext3 -v -f /dev/sda3 e2fsck 1.32 (09-Nov-2002) Pass 1: Checking inodes, blocks, and sizes Pass 2: Checking directory structure Pass 3: Checking directory connectivity /lost+found not found. Createy? yes Pass 4: Checking reference counts Pass 5: Checking group summary information /data1: * FILE SYSTEM WAS MODIFIED * 20 inodes used (0%) 1 non-contiguous inodes (5.0%) # of inodes with ind/dind/tind blocks: 8/8/0 2103570 blocks used (82%) 0 bad blocks 0 large files 8 regular files 2 directories 0 character device files 0 block device files 0 fifos 0 links 0 symbolic links (0 fast symbolic links) 0 sockets 10 files | Any explanation ? |050705 11:19:18 InnoDB: Started; log sequence number 0 4129451638 /usr/sbin/mysqld-max: ready for connections. Version: '4.1.9-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 Official MySQL RPM InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 18467. InnoDB: You may have to recover from a backup. hexdump ... 050705 11:19:20 InnoDB: Page checksum 1075917609, prior-to-4.0.14-form checksum 3652064195 InnoDB: stored checksum 2099841729, prior-to-4.0.14-form stored checksum 3652064195 InnoDB: Page lsn 0 3887279414, low 4 bytes of lsn at page end 3887279414 InnoDB: Page number (if stored to page already) 18467, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 InnoDB: Page may be an index page where index id is 0 310 InnoDB: (index PRIMARY of table sms_9388_telkomsel/t_outgoing_sms) InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 18467. 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: 0 050705 11:19:20 mysqld restarted | ( -- Regards, Ady Wicaksono HP: +628562208680 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: missing ibd files on upgrade of mysql from 4.1.9 to 4.1.12a
That is what I attempted, but to no success. I will try again, I think the installer blew away my old my.ini. On Mon, 4 Jul 2005, Gleb Paharenko wrote: Hello. I think that deleting .ibd files is a weird behavior for installer. Did you use innodb_file_per_table? If not, then a new version couldn't understand an old format of InnoDB data files. Probably, you could install a binary copy of MySQL 4.1.9, specify your data directory as it's datadir and make a dump with mysqldump. Then just import it to the new instance of MySQL. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating virtual rows question
Greetings Please have a look at the following code and let me know if this is the correct way to create a virtual row. Also, after having run this MySQL returns the error that the row total_points, does not exist. Where am I going wrong? Thank you in advance. SELECT mem_number, first_name, last_name, joining_points + emc + sapmc + starmc + ecmclassic + sams + kznmgp + mmgp + gsmc + mmw + saloty + safoty + kznmc + ecmgp + wcmc + fsmc + nwmgp + npmc + samo + csf + coc + ncmc + gmgp + nlmc + samp + tmc + gmc + yotmf = total_points FROM modelcup.ab_leader_board WHERE sex = 'Female' AND cup = 'kids' AND current_pos 0 ORDER BY total_points ASC -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Landlines Tel: +27125468436 Fax: +27125468436 Web email:[EMAIL PROTECTED] Global: www.volume4.com Messenger Yahoo!: v_olume4 AOL: v0lume4 MSN: [EMAIL PROTECTED] We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: missing ibd files on upgrade of mysql from 4.1.9 to 4.1.12a
On Mon, 4 Jul 2005, Gleb Paharenko wrote: Hello. I think that deleting .ibd files is a weird behavior for installer. Did you use innodb_file_per_table? If not, then a new version couldn't it is doing something with the files, but still it looks for the .ibd? 07/05/2005 07:04 10,485,760 ibdata1 07/05/2005 07:04 18,874,368 ib_logfile0 07/05/2005 06:54 18,874,368 ib_logfile1 any ideas? -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating virtual rows question
SELECT mem_number, first_name, last_name, joining_points + emc + sapmc + starmc + ecmclassic + sams + kznmgp + mmgp + gsmc + mmw + saloty + safoty + kznmc + ecmgp + wcmc + fsmc + nwmgp + npmc + samo + csf + coc + ncmc + gmgp + nlmc + samp + tmc + gmc + yotmf = total_points ncmc + gmgp + nlmc + samp + tmc + gmc + yotmf AS total_points Use the AS keyword, not the equal/assignment operator FROM modelcup.ab_leader_board WHERE sex = 'Female' AND cup = 'kids' AND current_pos 0 ORDER BY total_points ASC -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating virtual rows question
Jacques Thank you, I will give it a try. Jacques Marneweck wrote: Schalk Neethling wrote: Greetings Please have a look at the following code and let me know if this is the correct way to create a virtual row. Also, after having run this MySQL returns the error that the row total_points, does not exist. Where am I going wrong? Thank you in advance. SELECT mem_number, first_name, last_name, joining_points + emc + sapmc + starmc + ecmclassic + sams + kznmgp + mmgp + gsmc + mmw + saloty + safoty + kznmc + ecmgp + wcmc + fsmc + nwmgp + npmc + samo + csf + coc + ncmc + gmgp + nlmc + samp + tmc + gmc + yotmf = total_points FROM modelcup.ab_leader_board WHERE sex = 'Female' AND cup = 'kids' AND current_pos 0 ORDER BY total_points ASC Hi Schalk, Try: SELECT mem_number, first_name, last_name, joining_points + emc + sapmc + starmc + ecmclassic + sams + kznmgp + mmgp + gsmc + mmw + saloty + safoty + kznmc + ecmgp + wcmc + fsmc + nwmgp + npmc + samo + csf + coc + ncmc + gmgp + nlmc + samp + tmc + gmc + yotmf AS total_points FROM modelcup.ab_leader_board WHERE sex = 'Female' AND cup = 'kids' AND current_pos 0 ORDER BY total_points ASC Regards --jm -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Landlines Tel: +27125468436 Fax: +27125468436 Web email:[EMAIL PROTECTED] Global: www.volume4.com Messenger Yahoo!: v_olume4 AOL: v0lume4 MSN: [EMAIL PROTECTED] We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating virtual rows question
Thanks to all! The AS keyword did the trick. Philippe Poelvoorde wrote: SELECT mem_number, first_name, last_name, joining_points + emc + sapmc + starmc + ecmclassic + sams + kznmgp + mmgp + gsmc + mmw + saloty + safoty + kznmc + ecmgp + wcmc + fsmc + nwmgp + npmc + samo + csf + coc + ncmc + gmgp + nlmc + samp + tmc + gmc + yotmf = total_points ncmc + gmgp + nlmc + samp + tmc + gmc + yotmf AS total_points Use the AS keyword, not the equal/assignment operator FROM modelcup.ab_leader_board WHERE sex = 'Female' AND cup = 'kids' AND current_pos 0 ORDER BY total_points ASC -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Landlines Tel: +27125468436 Fax: +27125468436 Web email:[EMAIL PROTECTED] Global: www.volume4.com Messenger Yahoo!: v_olume4 AOL: v0lume4 MSN: [EMAIL PROTECTED] We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
do frm files have the schema for the table?
I am willing to give up on my data recovery efforts if I can just get the schema from the dead tables, any suggestions? Sincerely, Jason Pyeron -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do frm files have the schema for the table?
Hello. As of MySQL 4.0.2 after REPAIR ... USE_FRM you should be able to get information about table with SHOW CREATE TABLE, however some information could be lost. See: http://dev.mysql.com/doc/mysql/en/repair-table.html Jason Pyeron [EMAIL PROTECTED] wrote: I am willing to give up on my data recovery efforts if I can just get the schema from the dead tables, any suggestions? Sincerely, Jason Pyeron -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do frm files have the schema for the table?
On Tue, 5 Jul 2005, Gleb Paharenko wrote: Hello. As of MySQL 4.0.2 after REPAIR ... USE_FRM you should be able to get information about table with SHOW CREATE TABLE, however some information could be lost. See: http://dev.mysql.com/doc/mysql/en/repair-table.html but doesn't that only work with MyISAM tables? from the web page: REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as myisamchk --recover tbl_name. REPAIR TABLE works only on MyISAM tables. mysql repair table files USE_FRM; +-++--+-+ | Table | Op | Msg_type | Msg_text | +-++--+-+ | crisfield.files | repair | error| Can't open file: 'files.ibd' (errno: 1) | +-++--+-+ files.ibd does not exist on the system. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-medium.cnf file not created
Hello. Usually you could use template files from another version. If my-xxx.cnf files are not shipped with windows distribution (and they are not!), take them from corresponding Linux packages. But be aware of: http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html [EMAIL PROTECTED] wrote: Dear Sir or Madam: After installing mysql, the template files, my-small.cnf, my-medium.cnf or my-large.cnf, were not created. I uninstalled and then reinstalled mysql, but they were still not created. I am using mysql-4.0.20a-win on Windows ME. Could you please help with this problem. Thanks Nick -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do frm files have the schema for the table?
Hello. Yes, it works only for MyISAM. You've began a new thread, and haven't specified that this one is related to the previous. So I've decided they were independent. Possibly you could obtain schema information using different levels of innodb_force_recovery. See: http://dev.mysql.com/doc/mysql/en/forcing-recovery.html Jason Pyeron [EMAIL PROTECTED] wrote: On Tue, 5 Jul 2005, Gleb Paharenko wrote: Hello. As of MySQL 4.0.2 after REPAIR ... USE_FRM you should be able to get information about table with SHOW CREATE TABLE, however some information could be lost. See: http://dev.mysql.com/doc/mysql/en/repair-table.html but doesn't that only work with MyISAM tables? from the web page: REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as myisamchk --recover tbl_name. REPAIR TABLE works only on MyISAM tables. mysql repair table files USE_FRM; +-++--+-+ | Table | Op | Msg_type | Msg_text | +-++--+-+ | crisfield.files | repair | error| Can't open file: 'files.ibd' (errno: 1) | +-++--+-+ files.ibd does not exist on the system. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-medium.cnf file not created
search for my-medium.ini [EMAIL PROTECTED] wrote: Dear Sir or Madam: After installing mysql, the template files, my-small.cnf, my-medium.cnf or my-large.cnf, were not created. I uninstalled and then reinstalled mysql, but they were still not created. I am using mysql-4.0.20a-win on Windows ME. Could you please help with this problem. Thanks Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Network drive
Hello everyone, I would like to make a short, quick and simple question. Is it possible to have the following line: innodb_data_home_dir=X:/data/ in a my.ini config file? I'm using windows XP, mysql 4.1.12, X: is a mapped network drive to a Linux folder using samba, all the permissions are set and tested. I have seen this posted many times but without many replies. I want to use a folder in a mapped drive to save my InnoDB data. Is this possible? Thank you very much Rúben Carvalho ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Network drive
Hi Ruben, I would like to make a short, quick and simple question. Is it possible to have the following line: innodb_data_home_dir=X:/data/ in a my.ini config file? I'm using windows XP, mysql 4.1.12, X: is a mapped network drive to a Linux folder using samba, all the permissions are set and tested. I have seen this posted many times but without many replies. I want to use a folder in a mapped drive to save my InnoDB data. Is this possible? I don't know this particular answer for MySQL, but I do have 1 question: If the database engine doesn't have control over the files and/or drive, what should it do in the case of a network failure? Let alone the latency of a networked file... With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.25 has been released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 4.0.25, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the recent production version. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * Added --with-big-tables compilation option to configure. (Previously it was necessary to pass -DBIG_TABLES to the compiler manually in order to enable large table support.) Section 2.8.2, Typical configure Options for details. Bugs fixed: * A problem with the my_global.h file caused compilation of MySQL to fail on single-processor Linux systems running 2.6 kernels. (Bug #10364 (http://bugs.mysql.com/10364)) * Fixed a portability problem testing for crypt() support that caused compilation problems when using OpenSSL/yaSSL on HP-UX and Mac OS X. (Bug #10675(http://bugs.mysql.com/10675),Bug#11150 (http://bugs.mysql.com/11150)) * MyISAM table corruption could occur with ANALYZE TABLE if a write lock was acquired with LOCK TABLES and then an INSERT or DELETE was done prior to analyzing the table. (Bug #10901 (http://bugs.mysql.com/10901)) * Fixed a server crash resulting from CREATE TABLE ... SELECT that selected from a table being altered by ALTER TABLE. (Bug #10224 (http://bugs.mysql.com/10224)) * InnoDB: In DROP DATABASE, check for all referencing tables from other databasesbeforedroppinganytables.(Bug #10335 (http://bugs.mysql.com/10335)) * Fixed a problem with incorrect constant propagation resulting in incorrectevaluationofAND/OR queries. (Bug #10095 (http://bugs.mysql.com/10095)) * Fixed wrong buffer usage for auto-increment key with blob part that caused CHECK TABLE to report that the table was wrong. (Bug #10045 (http://bugs.mysql.com/10045)) * No error was raised for BOOLEAN full-text searches for storage engines that do not support full-text. (Bug #7709 (http://bugs.mysql.com/7709)) * The test in configure to see whether CXX specified gcc failed if gcc was specified as a full pathname. (Bug #9690 (http://bugs.mysql.com/9690)) * In the mysql_real_escape_string() C API function, when a multi-byte character is encountered that is illegal in the current character set, escape only the first byte, not each byte. This avoids creating a valid character from an invalid one. (Bug #9864 (http://bugs.mysql.com/9864); this is a backport of Bug #8378 (http://bugs.mysql.com/8378) from MySQL 4.1.11 to 4.0.25) * Fixed a deadlock resulting from use of FLUSH TABLES WITH READ LOCK while an INSERT DELAYED statement is in progress. (Bug #7823 (http://bugs.mysql.com/7823)) * Fixed a segmentation fault in mysqlcheck that occurred when the last table checked in --auto-repair mode returned an error (such as the table being a MERGE table). (Bug #9492 (http://bugs.mysql.com/9492)) * Fixed faulty display of TIMESTAMP columns retrieved as col_name+0 while thenew system variable is set to 1. (Bug #8894 (http://bugs.mysql.com/8894)) * Queries containing CURRENT_USER() incorrectly were registered in the query cache. (Bug #9796 (http://bugs.mysql.com/9796)) * Fixed problems with static variables to allow building on Fedora Core 3. (Bug #6554 (http://bugs.mysql.com/6554)) * An UPDATE that updated only some of the columns in a multiple-column index could result in a loop. (Bug #8942 (http://bugs.mysql.com/8942)) * REPAIR TABLE did not invalidate query results in the query cache that were generated from the table. (Bug #8480 (http://bugs.mysql.com/8480)) * Fixed a bug that caused concurrent inserts to be allowed into the tables in the SELECT ... UNION ... part of INSERT ... SELECT ... UNION This could result in the incorrect order of queries in the binary log. (Bug #9922 (http://bugs.mysql.com/9922)) * Fixed a bug that under certain circumstances could allow a privilege escalation via database wildcards in GRANT. (Bug #3924 (http://bugs.mysql.com/3924),CAN-2004-0957 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2004-0957)) * = was not properly comparing NULL values in the WHERE clause of outer joins. (Bug #8711 (http://bugs.mysql.com/8711)) * InnoDB: Fixed a bug: MySQL-4.0.23 and 4.0.24 could complain that an InnoDB table created with MySQL-3.23.49 or earlier was in the new compact InnoDB table format of 5.0.3 or
innodb crashes during heavy usage with exceeded memory error
Hi, We are running mysql version 4.1.7 with innodb on i686 running 2.4.26 linux kernal with 8G memory. Mysql crashes consistently during heavy usage with fatal innodb error. We are running a high volume front end application which inserts 50M data to a row in innodb table often. I don't know whether these inserts causing the memory overflow. Anyway I have included our my.cnf file and error logs from last crash below. Any help would be greatly appreciated. Thanks in advance. Kasthuri -- /etc/my.cnf key_buffer = 8M max_allowed_packet = 128M read_buffer_size = 512K sort_buffer_size = 512K myisam_sort_buffer_size = 5M thread_cache = 1024 table_cache = 1024 query_cache_size = 32M max_connections=1000 wait_timeout=300 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /var/mysql/data innodb_data_file_path = ibdata1:10M;ibdata2:1G;ibdata3:1G:autoextend # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DSYNC innodb_lock_wait_timeout = 10 - Mysql Error from error logs: 050704 18:19:21 InnoDB: Error: cannot allocate 101892621 bytes of memory for InnoDB: a BLOB with malloc! Total allocated memory InnoDB: by InnoDB 1989384815 bytes. Operating system errno: 12 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. 050704 18:19:21 InnoDB: Warning: could not allocate 100892621 + 100 bytes to retrieve InnoDB: a big column. Table name `sessions/horde_sessionhandler` 050704 18:37:16 InnoDB: ERROR: the age of the last checkpoint is 483189148, InnoDB: which exceeds the log group capacity 483180135. InnoDB: If you are using big BLOB or TEXT rows, you must set the InnoDB: combined size of log files at least 10 times bigger than the InnoDB: largest such row. 050704 18:38:23 InnoDB: Fatal error: cannot allocate 100892688 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 1787599407 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388608 read_buffer_size=524288 max_used_connections=608 max_connections=700 threads_connected=33 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size) *max_connections = 724986 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x893b6018 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfb3e0b8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x81767d9 0x401698e5 0x83c1657 0x83c1690 0x83bef0c 0x83bef9f 0x832d1c1 0x832d33c 0x82ff5f3 0x83026c5 0x8202b0b 0x8202d63 0x81f021a 0x81bb4fc 0x81bb01d 0x81b1432 0x81b1b20 0x81aea76 0x8189f10 0x818ef68 0x8188a2e 0x81885ee 0x8187e34 0x40163d03 0x402f0267 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8ca47e80 is invalid pointer thd-thread_id=25038128 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Memory status: Non-mmapped space allocated from system: 31660016 Number of free chunks: 236 Number of fastbin blocks:5 Number of mmapped regions: 22 Space in mmapped regions:1275957248 Maximum total allocated space: 0 Space available in freed fastbin blocks: 168 Total allocated space: 25461120 Total free space:
Returned mail: see transcript for details
The original message was received at Tue, 5 Jul 2005 19:45:52 +0200 from lists.debian.org [46.122.169.149] - The following addresses had permanent fatal errors - mysql@lists.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb crashes during heavy usage with exceeded memory error
You definitely should increase memory sizes in your my.cnf file. The settings that you have are for a very smal setup. You also need to allocate more space for innodb table extender. So instead of 10m have something like 50m or try and see which settings is better. The reason is under heavy load if innodb has to constantly extend the table space it will not have opportunity to anything else. So having 100m for each extend will reduce the number of times it has to increase table space. Are you doing any deletes/updates at the same time? You said 50m inserts to a table. Is that figure per day ? How large (in bytes) is an average row? Lastly look into the my.cnf for a large setup that ships with mysql. I think it is called my-large.cnf that will give you some help on settings. On 7/5/05, Kasthuri Ilankamban [EMAIL PROTECTED] wrote: Hi, We are running mysql version 4.1.7 with innodb on i686 running 2.4.26 linux kernal with 8G memory. Mysql crashes consistently during heavy usage with fatal innodb error. We are running a high volume front end application which inserts 50M data to a row in innodb table often. I don't know whether these inserts causing the memory overflow. Anyway I have included our my.cnf file and error logs from last crash below. Any help would be greatly appreciated. Thanks in advance. Kasthuri -- /etc/my.cnf key_buffer = 8M max_allowed_packet = 128M read_buffer_size = 512K sort_buffer_size = 512K myisam_sort_buffer_size = 5M thread_cache = 1024 table_cache = 1024 query_cache_size = 32M max_connections=1000 wait_timeout=300 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /var/mysql/data innodb_data_file_path = ibdata1:10M;ibdata2:1G;ibdata3:1G:autoextend # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DSYNC innodb_lock_wait_timeout = 10 - Mysql Error from error logs: 050704 18:19:21 InnoDB: Error: cannot allocate 101892621 bytes of memory for InnoDB: a BLOB with malloc! Total allocated memory InnoDB: by InnoDB 1989384815 bytes. Operating system errno: 12 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. 050704 18:19:21 InnoDB: Warning: could not allocate 100892621 + 100 bytes to retrieve InnoDB: a big column. Table name `sessions/horde_sessionhandler` 050704 18:37:16 InnoDB: ERROR: the age of the last checkpoint is 483189148, InnoDB: which exceeds the log group capacity 483180135. InnoDB: If you are using big BLOB or TEXT rows, you must set the InnoDB: combined size of log files at least 10 times bigger than the InnoDB: largest such row. 050704 18:38:23 InnoDB: Fatal error: cannot allocate 100892688 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 1787599407 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388608 read_buffer_size=524288 max_used_connections=608 max_connections=700 threads_connected=33 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size) *max_connections = 724986 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x893b6018 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfb3e0b8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x81767d9 0x401698e5 0x83c1657 0x83c1690 0x83bef0c 0x83bef9f 0x832d1c1 0x832d33c 0x82ff5f3 0x83026c5 0x8202b0b 0x8202d63 0x81f021a 0x81bb4fc 0x81bb01d 0x81b1432 0x81b1b20 0x81aea76 0x8189f10 0x818ef68 0x8188a2e 0x81885ee 0x8187e34 0x40163d03 0x402f0267 New value of fp=(nil) failed sanity check, terminating stack trace!
MAX on UNSIGNED INT Column
I have a column of type UNSIGNED INT which holds a 32bit counter. When the value of the field exceeds 2147483647 (signed max) the value of MAX on the column returns a negative number. If I convert the column to BIGINT the correct MAX is returned. Is this expected behavior, am I doing something wrong, is this a bug? I would rather not store a 32bit counter as a 64bit value, but I need the MAX function to work so in the mean time I will convert all these fields to BIGINT. Thanks, Jake -- Jacob S. Barrett Chief Technology Officer PogoZone LLC email: [EMAIL PROTECTED] web: www.pogozone.com voice: 360-676-8772 fax: 360-733-3941 address: 114 W. Magnolia Street Suite 417 Bellingham, Washington 98225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT and mysql.user table
At 16:57 -0700 7/4/05, l'[EMAIL PROTECTED] wrote: I tried as root having GRANT ALL PRIVILEGES with GRANT OPTION to change the privilege of a user: GRANT ALL ON mydb.* TO myUser; The result of this statement is that the query is OK and 0 rows are affected. WHen I look at the mysql table holding the grants: user, the myUser row is unchanged. You assigned database-level privileges, which are recorded in mysql.db, not mysql.user. What could possibly be preventing the system from changing the GRANT of myUser? Is the mysql.db table part of the granting? Yes. Recommended reading: http://dev.mysql.com/doc/mysql/en/privilege-system.html http://dev.mysql.com/doc/mysql/en/user-account-management.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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: MAX on UNSIGNED INT Column
At 12:17 -0700 7/5/05, Jacob S. Barrett wrote: I have a column of type UNSIGNED INT which holds a 32bit counter. When the value of the field exceeds 2147483647 (signed max) the value of MAX on the column returns a negative number. If I convert the column to BIGINT the correct MAX is returned. Is this expected behavior, am I doing something wrong, is this a bug? I would rather not store a 32bit counter as a 64bit value, but I need the MAX function to work so in the mean time I will convert all these fields to BIGINT. Please report a repeatable test case to http://bugs.mysql.com. Thanks. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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]
null data in table question
Hello, I have created a web-based simple application, and used mysql for data storage. All has worked well. But I do have a simple question I would like to ask the group. I have some web-based forms that match table structure. Most of the important fields have validation, and I ensure good data into the table. But I have a few columns in the table such as middleInitial where I do not validate the data. And in the database is shows a null when I do a select * from. Is a null acceptable in the database, or is there something I should do on columns that the user may not put in data? Currently my tables are simple like so. CREATE TABLE `user` ( `id` int(11) NOT NULL auto_increment, `modified_date` timestamp(14) NOT NULL, `created_date` timestamp(14) NOT NULL, `username` varchar(50) NOT NULL default '', `firstname` varchar(25) default NULL, `initial` char(1) default NULL, `lastname` varchar(25) default NULL, `company` varchar(50) default NULL, `address1` varchar(50) default NULL, `address2` varchar(50) default NULL, `city` varchar(50) default NULL, `state` char(2) default NULL, `zip` varchar(5) default NULL, `phone` varchar(12) default NULL, `eveningPhone` varchar(12) default NULL, `email` varchar(50) default NULL, `password` varchar(50) default NULL, `admin` char(1) NOT NULL default 'F', `hintchoice` char(1) default NULL, `hintvalue` varchar(50) default NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; Thanks for any input, as I am learning. Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update not returning any warning on failure
I have a problem working with the stored procs.
The stored proc looks like this
create procedure unit_swap()
BEGIN
DECLARE b INT DEFAULT 7;
DECLARE c INT;
DECLARE CONTINUE HANDLER FOR 1176 SET b = ;
DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION, SQLWARNING SET b =
;
UPDATE EMP SET STYLE='' WHERE ID='999';
IF b = THEN
INSERT INTO EMP (ID) VALUES ('999');
END IF;
COMMIT;
END;
The problem here is that even if update fails, meaning even if ID '999'
doesn't exist, MySQL doesn't report any error or warning (NOT FOUND error
handler is not catching this). So I have no way to declare error handler for
this statement and then taking the action accordingly. (INSETTING OR ANY
OTHER THING)
Any help on this is extremely useful.
Thank you
sujay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why are these two queries deadlocking?
SHOW INNODB STATUS indicates these two queries are deadlocking: (1) REPLACE INTO TMP_pixel_xfer SELECT * FROM user_question q INNER JOIN user_session s USING(user_session_id) WHERE user_question_id BETWEEN '27853011' AND '27891923' ORDER BY s.user_id (2) DELETE t from TMP_user_client_report t LEFT JOIN user_question u USING(user_session_id,question_id) WHERE u.user_id IS NULL I execute 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED' before executing each respective query. Note that the two queries both join to user_question, but neither query changes any data in user_question. Only data in their respective TMP_ tables is modified. But apparently there are row-locks set on user_question anyway. Why is this? And shouldn't each query be using it's own fresh copy of user_question since the isolation level is set to READ COMMITTED beforehand? Is there another strategy I can use to avoid the deadlock? Any insight is greatly appreciated. One last note that may or may not be relevant. I began to experience this deadlock only after I upgraded from mysql 4.0 to 4.1. Thanks, Brady -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: null data in table question
Scott Purcell [EMAIL PROTECTED] wrote on 07/05/2005 04:21:06 PM: Hello, I have created a web-based simple application, and used mysql for data storage. All has worked well. But I do have a simple question I would like to ask the group. I have some web-based forms that match table structure. Most of the important fields have validation, and I ensure good data into the table. But I have a few columns in the table such as middleInitial where I do not validate the data. And in the database is shows a null when I do a select * from. Is a null acceptable in the database, or is there something I should do on columns that the user may not put in data? Currently my tables are simple like so. CREATE TABLE `user` ( `id` int(11) NOT NULL auto_increment, `modified_date` timestamp(14) NOT NULL, `created_date` timestamp(14) NOT NULL, `username` varchar(50) NOT NULL default '', `firstname` varchar(25) default NULL, `initial` char(1) default NULL, `lastname` varchar(25) default NULL, `company` varchar(50) default NULL, `address1` varchar(50) default NULL, `address2` varchar(50) default NULL, `city` varchar(50) default NULL, `state` char(2) default NULL, `zip` varchar(5) default NULL, `phone` varchar(12) default NULL, `eveningPhone` varchar(12) default NULL, `email` varchar(50) default NULL, `password` varchar(50) default NULL, `admin` char(1) NOT NULL default 'F', `hintchoice` char(1) default NULL, `hintvalue` varchar(50) default NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; Thanks for any input, as I am learning. Scott There are two basic philosophies when it comes to NULL values in a database. The first group says No! Never! How dare you ask such a thing you lazy sot!. The other group recognizes that real data cannot always be complete and that NULLs are a convenient way to represent such an occurrence. I happen to fall into the second group. What you have to remember about NULL information is that it represents nothingness, a state of being, NOT nothing the value. Your missing middle initial is an excellent example of why NULLs are acceptable. If someone has a middle initial, you can easily store it (some people have more than one but I will let you figure that one out on your own... you did say you were learning, right?). If you know that someone doesn't have a middle initial you can also store that fact as the empty string ''. The fact that you just do not know if a person has a middle initial or not would be represented by the value NULL. Notice that NULL is not ''. One is a value (a string with no letters in it) the other is a condition representing the lack of information (NULL). How you deal with missing data is entirely up to you. In my humble opinion any field for which you absolutely do not need a value is potentially a NULL-able field. Does that mean I will always make them null? No. What gets stored in a database is sometimes determined by the programming languages and the needs of the user interface or other users of the data. In your case, I think you have a good first design and you shouldn't worry about the NULL values unless you really need to for other reasons. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: null data in table question
Hi Scott, I have created a web-based simple application, and used mysql for data storage. All has worked well. But I do have a simple question I would like to ask the group. I have some web-based forms that match table structure. Most of the important fields have validation, and I ensure good data into the table. But I have a few columns in the table such as middleInitial where I do not validate the data. And in the database is shows a null when I do a select * from. Is a null acceptable in the database, or is there something I should do on columns that the user may not put in data? Given that NULL means unknown and there's no value/state for non applicable, NULLs don't belong in places where you actually want to fill in nothing or empty. An empty string is an empty string. Why not insert that instead? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql repl
Dear mysql@lists.mysql.com: Our db is the innodb eng. We have no hot backup tools to dump the data. If lock the table by the following cmd before we dump the data FLUSH TABLES WITH READ LOCK; It seems ok,it can lock the innodb too. But the replication would be crushed ,even it start up smoothly,due to the duplicated entry. Do any one try to lock innodb tables to make the snap shot? Since shutdown the master db would take great effect,we don't hope so. Best regards. MaFai [EMAIL PROTECTED] 2005-07-06 34955929 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
