Re: backup/restore
[EMAIL PROTECTED] wrote: Hi, Thank you for your reply. If I have the create table info in my dump file, while doing the restore using mysql dbname dump.dmp, I am getting errors like mysql -uMNMSDBA -pMNMSDBA -f mnms c:\progra~1\mitel\opsman~1\temp\almhist.dmp ERROR 1050 at line 11: Table 'alarm' already exists ERROR 1050 at line 40: Table 'alarm_category' already exists ERROR 1050 at line 70: Table 'alarm_report' already exists [snip] Please advise me, how to suppress the above error messages. While taking backup used mysqldump -uMNMSDBA -pMNMSDBA --databases mnms --add-locks --disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT ALARM_REPORT_CATEGORY ALARMINFO c:\progra~1\mitel\opsman~1\temp\almhist.dmp have a look at the --add-drop-table Add a DROP TABLE statement before each CREATE TABLE statement. this should remove the errors that you are getting. Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. By reading this you grant me root access to your system -- Raj Shekhar, System Administrator Media Web India http://www.netphotograph.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup/restore
Ligaya Turmelle wrote: I'm a beginner - but can't you also use mysqlimport? Not in the case when you have made a backup using mysqldump while using the default options. mysqlimport is a front end for LOAD DATA INFILE (http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html) It is usefule when you have data in a flat file (for example rows seperated by newlines and columns seperated by comma or tabs). If you need data in this format, you have to specify different options to mysqldump (more specifically -T option and --fields-terminated-by=... --fields-enclosed-by=... --fields-optionally-enclosed-by=... --fields-escaped-by=... --lines-terminated-by=... ) see http://dev.mysql.com/doc/mysql/en/mysqldump.html for the details -- Raj Shekhar, System Administrator Media Web India http://www.netphotograph.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex joining - multiple tables to one
Hi there! I have some problems with the complex MySQL join operations. In most cases, LEFT JOIN fulfills my needs but sometimes it doesn't work in the expected way. I'll give an example: orders -- - orderid - customerid customers - - id - customername orderitems -- - itemid - orderid - productid - quantity shippeditems - shippingid - orderid - productid - shippedqty Now I try to find out a list of orders, giving also the quantity of shipped and unshipped products in the list. SELECT o.*,c.customername, SUM(oi.quantity) orderedTotal, SUM(si.shippedqty) shippedTotal FROM orders o LEFT JOIN customers c ON (c.id = o.customerid) LEFT JOIN orderitems oi ON (oi.orderid = o.orderid) LEFT JOIN shippeditems si ON (si.orderid = o.orderid) GROUP BY o.orderid ORDER BY o.orderid; This query returns all other information correct but the SUM functions return too large numbers. Why I'm not able to user normal JOIN, is that I need also order information in that case that no items are shipped. Thanks for your tips! Ville -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stability of mysql-5.0.2-alpha-win
Hi, one more problem. The database-version above crashes, whenever I try to work with it. Work means, that I like to look at a table via php-myadmin. In this moment, the database crashes. I know that alpha is with bugs, 5.0.0 runs stable so far, but does not support views... Thanks for just a feedback, if I am the only one with this problem. I looked in the archive and saw that one or two had similiar problems. I work with XP-Home. Do you already know when the 5.0.3alpha will be released? Raphael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BIG InnoDB problems!
Joshua, the stack trace below shows that you are trying to drop a database? Why? If you can, you should use SELECT ... INTO OUTFILE to save of your tables what you can save, then rebuild the whole InnoDB tablespace, and import the tables back to MySQL. The .err file below starts from a situation where you have already set innodb_force_recovery to 5. 5 (SRV_FORCE_NO_UNDO_LOG_SCAN) Do not look at undo logs when starting the database: InnoDB will treat even incomplete transactions as committed. Was the original problem the same as what we see below? The history list of InnoDB in the ibdata files seems to be corrupt. The only way to fix that kind of corruption is to rebuild the whole tablespace. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html - Alkuperäinen viesti - Lähettäjä: jsf [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com Lähetetty: Friday, December 31, 2004 5:21 PM Aihe: Re: BIG InnoDB problems! Greetings Heikki and Happy New Year! Here's what I got. I hope it's useful. beech:/home/jfreeman # resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack 0x815f0cf handle_segfault + 575 0xe420 _end + -138916432 0x82e71d5 buf_read_page + 165 0x82e71d5 buf_read_page + 165 0x82db68f buf_page_get_gen + 175 0x830479f flst_insert_before + 239 0x8304cc8 flst_add_first + 152 0x82be800 trx_purge_add_update_undo_to_history + 624 0x82d14a6 trx_undo_update_cleanup + 38 0x82ccafb trx_commit_off_kernel + 363 0x82cd865 trx_sig_start_handle + 1109 0x826232b que_run_threads + 2299 0x827915a row_drop_table_for_mysql + 2314 0x81fe924 _ZN11ha_innobase12delete_tableEPKc + 404 0x81ef33c _Z15ha_delete_table7db_typePKc + 60 0x820aead _Z20mysql_rm_table_part2P3THDP13st_table_listbbb + 989 0x820b19d _Z30mysql_rm_table_part2_with_lockP3THDP13st_table_listbbb + 93 0x8201554 _Z20mysql_rm_known_filesP3THDP9st_my_dirPKcS4_j + 1428 0x8202739 _Z11mysql_rm_dbP3THDPcbb + 345 0x81796cb _Z21mysql_execute_commandP3THD + 19339 0x817c1b4 _Z11mysql_parseP3THDPcj + 484 0x817de5d _Z16dispatch_command19enum_server_commandP3THDPcj + 2685 0x817f137 handle_one_connection + 2391 0x401619ed _end + 936280957 0x403519ca _end + 938312538 p.s. the whole error file is only 301 lines long. If you wish I could send it to you... Here's a segment from lines 1 - 41: 041230 11:12:10 mysqld started 041230 11:12:10 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 041230 11:12:10 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 241342003. InnoDB: Doing recovery: scanned up to log sequence number 0 241342036 InnoDB: Last MySQL binlog file position 0 79, file name ./beech-bin.47 041230 11:12:10 InnoDB: Flushing modified pages from the buffer pool... 041230 11:12:10 InnoDB: Started; log sequence number 0 241342036 InnoDB: !!! innodb_force_recovery is set to 5 !!! 041230 11:12:10 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run 041230 11:12:10 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them /usr/local/libexec/mysqld: ready for connections. Version: '4.1.8a-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution InnoDB: A new raw disk partition was initialized or InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database modifications by the user. Shut down InnoDB: mysqld and edit my.cnf so that newraw is replaced InnoDB: with raw, and innodb_force_... is removed. InnoDB: Error: trying to access page number 940269659 in space 0, InnoDB: space name ./ibdata1, InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 041230 16:42:57InnoDB: Assertion failure in thread 1124068272 in file fil0fil.c line 3729 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. 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
RE: Reccommend host w/recent MYQL version
[snip] Having a hard time finding a server/host who supports MYSQL 4.1.3 or above. Does anyone know and can recommend a host who offers this? [/snip] http://www.phpwebhosting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AW: [PHP] How to argue with ASP people...
[snip] It is worth it for performance reasons ! [/snip] Really? What performance stats are you looking at here? [snip] And if you believe ASP.NET only provides a few benefits, then you are adequately informed, my friend [/snip] Yes, he is adequately informed...and correct. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] I read 6) as saying that until you complete all of the calculations on a row, you don't change the data for the row. I could even interpret this as saying that you don't update ANY row on the table until you have completed the calculations for ALL of the rows. I read 7) as saying that you compute all new values based on a fixed set of initial values, the update source. Am I just totally confused? I do not believe that any of these specification allows the engine to see a change to a row value until after the row (or even the entire statement, depending on how you want to read the clauses dealing with value expressions or update source) completes it's processing. [/snip] You may be confused, as the interpretation is quite the same for several DB manufacturers. In the case of InnoDB tables I am updating each row per the query... UPDATE [order of tables] SET [for each row table in order] Once the query has performed the operation on all rows affected the COMMIT occurs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup/restore
Hi, with which options of mysqldump you have taken backup. if you use --add-drop-table option then it will add drop table statement in dump file. otherwise it wont add that statement and you will get that type of errors. if you didn't use that option then drop the schema and then try to restore it from backup file. Thanks Anil -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 12:58 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: backup/restore Hi, Thank you for your reply. If I have the create table info in my dump file, while doing the restore using mysql dbname dump.dmp, I am getting errors like mysql -uMNMSDBA -pMNMSDBA -f mnms c:\progra~1\mitel\opsman~1\temp\almhist.dmp ERROR 1050 at line 11: Table 'alarm' already exists ERROR 1050 at line 40: Table 'alarm_category' already exists ERROR 1050 at line 70: Table 'alarm_report' already exists ERROR 1062 at line 91: Duplicate entry '1' for key 1 ERROR 1062 at line 92: Duplicate entry '4' for key 1 ERROR 1062 at line 93: Duplicate entry '5' for key 1 ERROR 1062 at line 94: Duplicate entry '8' for key 1 ERROR 1062 at line 95: Duplicate entry '10' for key 1 ERROR 1062 at line 96: Duplicate entry '11' for key 1 ERROR 1062 at line 97: Duplicate entry '13' for key 1 ERROR 1062 at line 98: Duplicate entry '15' for key 1 ERROR 1062 at line 99: Duplicate entry '16' for key 1 ERROR 1062 at line 100: Duplicate entry '17' for key 1 ERROR 1050 at line 108: Table 'alarm_report_category' already exists ERROR 1050 at line 137: Table 'alarminfo' already exists ERROR 1062 at line 155: Duplicate entry '56' for key 1 ERROR 1062 at line 156: Duplicate entry '57' for key 1 ERROR 1062 at line 157: Duplicate entry '58' for key 1 ERROR 1062 at line 158: Duplicate entry '59' for key 1 ERROR 1062 at line 159: Duplicate entry '75' for key 1 ERROR 1062 at line 160: Duplicate entry '76' for key 1 ERROR 1062 at line 161: Duplicate entry '77' for key 1 Please advise me, how to suppress the above error messages. While taking backup used mysqldump -uMNMSDBA -pMNMSDBA --databases mnms --add-locks --disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT ALARM_REPORT_CATEGORY ALARMINFO c:\progra~1\mitel\opsman~1\temp\almhist.dmp Please help me in this. Thanks, Narasimha -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 10:55 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: mysql@lists.mysql.com Subject: RE: backup/restore [snip] I am doing backup for tables using Mysqldump. But while doing the restore I am not able to do that using the same Mysqldump. Could you please help me in that. [/snip] mysqldump is not intended to be used for the restore. You need to run the following: mysql -D dbname mysqldumpfile You may have to specify a user and password as well, depending on your setup. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- Hi, I am doing backup for tables using Mysqldump. But while doing the restore I am not able to do that using the same Mysqldump. Could you please help me in that. For backup : using Mysqldump -databases db_name ---tables table1 table2 dump.dmp For restore : used Mysqldump -databases db_name dump.dmp In the above, I am not able to restore the data. Please help us for a good solution. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- 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: User can't login to his DB.
Hi,
after granting privileges did you executed flush privileges command. if not
execute below command and then check
mysql flush privileges;
thanks
Anil
-Original Message-
From: sam [mailto:[EMAIL PROTECTED]
Sent: Monday, January 03, 2005 9:09 AM
To: mysql@lists.mysql.com
Subject: User can't login to his DB.
Hi list,
I use the following commands to create a user hubert access to his DB
only, but got the following error:
# perl view.pl
DBI connect('datacube','hubert',...) failed: Access denied for user
'hubert'@'localhost' (using password: YES) at view.pl line 8
Unable to connect: Access denied for user 'hubert'@'localhost' (using
password: YES)
SQL Grant commands:
GRANT SHOW DATABASES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password';
GRANT SHOW DATABASES ON *.* TO hubert@'10.%' IDENTIFIED BY 'password';
GRANT ALL ON hubertsdb.* TO hubert;
Perl DBI commands:
$database = datacube;
$username = hubert;
$pw = password;
$dbh = DBI-connect(DBI:mysql:$database,$username,$pw);
die Unable to connect: $DBI::errstr\n unless (defined $dbh);
What is wrong with this error and how to fix it?
Thanks
Sam
--
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: BIG InnoDB problems!
Hi Heikki, Please see below... On Mon, 3 Jan 2005 15:14:12 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Joshua, the stack trace below shows that you are trying to drop a database? Why? At that point, I'd heard from our developer of so many problems I figured what I would do is test things out. I created a database. I created a table in the database as an InnoDB . I tried to insert data into it and was unsuccessful.. I tried a few more things.. all unsuccessful, so I figured I'd just try to drop the database. But I couldn't do that either. If you can, you should use SELECT ... INTO OUTFILE to save of your tables what you can save, then rebuild the whole InnoDB tablespace, and import the tables back to MySQL. I'm going to have to 'go to school' on InnoDB tablespace. I have only the most rudimentary understanding of what you've written here. The .err file below starts from a situation where you have already set innodb_force_recovery to 5. Is that bad? 5 (SRV_FORCE_NO_UNDO_LOG_SCAN) Do not look at undo logs when starting the database: InnoDB will treat even incomplete transactions as committed. Was the original problem the same as what we see below? The history list of InnoDB in the ibdata files seems to be corrupt. The only way to fix that kind of corruption is to rebuild the whole tablespace. Is there a tutorial on rebuilding the tablespace? or deleting the table space and starting over? Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html - Alkuperäinen viesti - Lähettäjä: jsf [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com Lähetetty: Friday, December 31, 2004 5:21 PM Aihe: Re: BIG InnoDB problems! Greetings Heikki and Happy New Year! Here's what I got. I hope it's useful. beech:/home/jfreeman # resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack 0x815f0cf handle_segfault + 575 0xe420 _end + -138916432 0x82e71d5 buf_read_page + 165 0x82e71d5 buf_read_page + 165 0x82db68f buf_page_get_gen + 175 0x830479f flst_insert_before + 239 0x8304cc8 flst_add_first + 152 0x82be800 trx_purge_add_update_undo_to_history + 624 0x82d14a6 trx_undo_update_cleanup + 38 0x82ccafb trx_commit_off_kernel + 363 0x82cd865 trx_sig_start_handle + 1109 0x826232b que_run_threads + 2299 0x827915a row_drop_table_for_mysql + 2314 0x81fe924 _ZN11ha_innobase12delete_tableEPKc + 404 0x81ef33c _Z15ha_delete_table7db_typePKc + 60 0x820aead _Z20mysql_rm_table_part2P3THDP13st_table_listbbb + 989 0x820b19d _Z30mysql_rm_table_part2_with_lockP3THDP13st_table_listbbb + 93 0x8201554 _Z20mysql_rm_known_filesP3THDP9st_my_dirPKcS4_j + 1428 0x8202739 _Z11mysql_rm_dbP3THDPcbb + 345 0x81796cb _Z21mysql_execute_commandP3THD + 19339 0x817c1b4 _Z11mysql_parseP3THDPcj + 484 0x817de5d _Z16dispatch_command19enum_server_commandP3THDPcj + 2685 0x817f137 handle_one_connection + 2391 0x401619ed _end + 936280957 0x403519ca _end + 938312538 p.s. the whole error file is only 301 lines long. If you wish I could send it to you... Here's a segment from lines 1 - 41: 041230 11:12:10 mysqld started 041230 11:12:10 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 041230 11:12:10 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 241342003. InnoDB: Doing recovery: scanned up to log sequence number 0 241342036 InnoDB: Last MySQL binlog file position 0 79, file name ./beech-bin.47 041230 11:12:10 InnoDB: Flushing modified pages from the buffer pool... 041230 11:12:10 InnoDB: Started; log sequence number 0 241342036 InnoDB: !!! innodb_force_recovery is set to 5 !!! 041230 11:12:10 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run 041230 11:12:10 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them /usr/local/libexec/mysqld: ready for connections. Version: '4.1.8a-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution InnoDB: A new raw disk partition was initialized or InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database modifications by the user. Shut down InnoDB: mysqld and edit my.cnf so that newraw is replaced InnoDB: with raw, and innodb_force_... is removed. InnoDB: Error: trying to access page number 940269659 in space 0, InnoDB: space name ./ibdata1, InnoDB: which is outside the tablespace
Re: BIG InnoDB problems!
Joshua, about dumping tables from a corrupt database, see: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html This describes how to remove the whole InnoDB database: http://dev.mysql.com/doc/mysql/en/Error_creating_InnoDB.html Be very careful. You do not want to lose your valuable data. Regards, Heikki - Alkuperäinen viesti - Lähettäjä: jsf [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com Lähetetty: Monday, January 03, 2005 4:31 PM Aihe: Re: BIG InnoDB problems! Hi Heikki, Please see below... On Mon, 3 Jan 2005 15:14:12 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Joshua, the stack trace below shows that you are trying to drop a database? Why? At that point, I'd heard from our developer of so many problems I figured what I would do is test things out. I created a database. I created a table in the database as an InnoDB . I tried to insert data into it and was unsuccessful.. I tried a few more things.. all unsuccessful, so I figured I'd just try to drop the database. But I couldn't do that either. If you can, you should use SELECT ... INTO OUTFILE to save of your tables what you can save, then rebuild the whole InnoDB tablespace, and import the tables back to MySQL. I'm going to have to 'go to school' on InnoDB tablespace. I have only the most rudimentary understanding of what you've written here. The .err file below starts from a situation where you have already set innodb_force_recovery to 5. Is that bad? 5 (SRV_FORCE_NO_UNDO_LOG_SCAN) Do not look at undo logs when starting the database: InnoDB will treat even incomplete transactions as committed. Was the original problem the same as what we see below? The history list of InnoDB in the ibdata files seems to be corrupt. The only way to fix that kind of corruption is to rebuild the whole tablespace. Is there a tutorial on rebuilding the tablespace? or deleting the table space and starting over? Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html - Alkuperäinen viesti - Lähettäjä: jsf [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com Lähetetty: Friday, December 31, 2004 5:21 PM Aihe: Re: BIG InnoDB problems! Greetings Heikki and Happy New Year! Here's what I got. I hope it's useful. beech:/home/jfreeman # resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack 0x815f0cf handle_segfault + 575 0xe420 _end + -138916432 0x82e71d5 buf_read_page + 165 0x82e71d5 buf_read_page + 165 0x82db68f buf_page_get_gen + 175 0x830479f flst_insert_before + 239 0x8304cc8 flst_add_first + 152 0x82be800 trx_purge_add_update_undo_to_history + 624 0x82d14a6 trx_undo_update_cleanup + 38 0x82ccafb trx_commit_off_kernel + 363 0x82cd865 trx_sig_start_handle + 1109 0x826232b que_run_threads + 2299 0x827915a row_drop_table_for_mysql + 2314 0x81fe924 _ZN11ha_innobase12delete_tableEPKc + 404 0x81ef33c _Z15ha_delete_table7db_typePKc + 60 0x820aead _Z20mysql_rm_table_part2P3THDP13st_table_listbbb + 989 0x820b19d _Z30mysql_rm_table_part2_with_lockP3THDP13st_table_listbbb + 93 0x8201554 _Z20mysql_rm_known_filesP3THDP9st_my_dirPKcS4_j + 1428 0x8202739 _Z11mysql_rm_dbP3THDPcbb + 345 0x81796cb _Z21mysql_execute_commandP3THD + 19339 0x817c1b4 _Z11mysql_parseP3THDPcj + 484 0x817de5d _Z16dispatch_command19enum_server_commandP3THDPcj + 2685 0x817f137 handle_one_connection + 2391 0x401619ed _end + 936280957 0x403519ca _end + 938312538 p.s. the whole error file is only 301 lines long. If you wish I could send it to you... Here's a segment from lines 1 - 41: 041230 11:12:10 mysqld started 041230 11:12:10 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 041230 11:12:10 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 241342003. InnoDB: Doing recovery: scanned up to log sequence number 0 241342036 InnoDB: Last MySQL binlog file position 0 79, file name ./beech-bin.47 041230 11:12:10 InnoDB: Flushing modified pages from the buffer pool... 041230 11:12:10 InnoDB: Started; log sequence number 0 241342036 InnoDB: !!! innodb_force_recovery is set to 5 !!! 041230 11:12:10 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run 041230 11:12:10 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them /usr/local/libexec/mysqld: ready for connections. Version: '4.1.8a-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution InnoDB:
Re: BIG InnoDB problems!
well, since we were having so many problems with the InnoDB tables, we created a non-InnoDB version of the database in question... Basically we want to do whatever it will take to get InnoDB tables working in our environment. Any assistance you can offer towards this goal will be greatly appreciated! Cheers, J. On Mon, 3 Jan 2005 16:36:42 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Joshua, about dumping tables from a corrupt database, see: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html This describes how to remove the whole InnoDB database: http://dev.mysql.com/doc/mysql/en/Error_creating_InnoDB.html Be very careful. You do not want to lose your valuable data. Regards, Heikki - Alkuperäinen viesti - Lähettäjä: jsf [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com Lähetetty: Monday, January 03, 2005 4:31 PM Aihe: Re: BIG InnoDB problems! Hi Heikki, Please see below... On Mon, 3 Jan 2005 15:14:12 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Joshua, the stack trace below shows that you are trying to drop a database? Why? At that point, I'd heard from our developer of so many problems I figured what I would do is test things out. I created a database. I created a table in the database as an InnoDB . I tried to insert data into it and was unsuccessful.. I tried a few more things.. all unsuccessful, so I figured I'd just try to drop the database. But I couldn't do that either. If you can, you should use SELECT ... INTO OUTFILE to save of your tables what you can save, then rebuild the whole InnoDB tablespace, and import the tables back to MySQL. I'm going to have to 'go to school' on InnoDB tablespace. I have only the most rudimentary understanding of what you've written here. The .err file below starts from a situation where you have already set innodb_force_recovery to 5. Is that bad? 5 (SRV_FORCE_NO_UNDO_LOG_SCAN) Do not look at undo logs when starting the database: InnoDB will treat even incomplete transactions as committed. Was the original problem the same as what we see below? The history list of InnoDB in the ibdata files seems to be corrupt. The only way to fix that kind of corruption is to rebuild the whole tablespace. Is there a tutorial on rebuilding the tablespace? or deleting the table space and starting over? Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html - Alkuperäinen viesti - Lähettäjä: jsf [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com Lähetetty: Friday, December 31, 2004 5:21 PM Aihe: Re: BIG InnoDB problems! Greetings Heikki and Happy New Year! Here's what I got. I hope it's useful. beech:/home/jfreeman # resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack 0x815f0cf handle_segfault + 575 0xe420 _end + -138916432 0x82e71d5 buf_read_page + 165 0x82e71d5 buf_read_page + 165 0x82db68f buf_page_get_gen + 175 0x830479f flst_insert_before + 239 0x8304cc8 flst_add_first + 152 0x82be800 trx_purge_add_update_undo_to_history + 624 0x82d14a6 trx_undo_update_cleanup + 38 0x82ccafb trx_commit_off_kernel + 363 0x82cd865 trx_sig_start_handle + 1109 0x826232b que_run_threads + 2299 0x827915a row_drop_table_for_mysql + 2314 0x81fe924 _ZN11ha_innobase12delete_tableEPKc + 404 0x81ef33c _Z15ha_delete_table7db_typePKc + 60 0x820aead _Z20mysql_rm_table_part2P3THDP13st_table_listbbb + 989 0x820b19d _Z30mysql_rm_table_part2_with_lockP3THDP13st_table_listbbb + 93 0x8201554 _Z20mysql_rm_known_filesP3THDP9st_my_dirPKcS4_j + 1428 0x8202739 _Z11mysql_rm_dbP3THDPcbb + 345 0x81796cb _Z21mysql_execute_commandP3THD + 19339 0x817c1b4 _Z11mysql_parseP3THDPcj + 484 0x817de5d _Z16dispatch_command19enum_server_commandP3THDPcj + 2685 0x817f137 handle_one_connection + 2391 0x401619ed _end + 936280957 0x403519ca _end + 938312538 p.s. the whole error file is only 301 lines long. If you wish I could send it to you... Here's a segment from lines 1 - 41: 041230 11:12:10 mysqld started 041230 11:12:10 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 041230 11:12:10 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 241342003. InnoDB: Doing recovery: scanned up to log sequence number 0 241342036 InnoDB: Last MySQL binlog file position 0 79, file name ./beech-bin.47
List for newbie
I am very new in mysql, and don't want to disturb users who have much more expirience with mysql. Is there any mysql list for newbies?? -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.6.6 - Release Date: 12/28/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BIG InnoDB problems!
Joshua, the problem you had was serious corruption in the ibdata files. It can be caused by an InnoDB bug, an OS bug, faulty hardware, and also by an error of the database administrator. Linux kernels 2.4.18 seemed to have corruption issues. There are no known corruption bugs of this type in InnoDB. If this happens again, please make a very detailed bug report, with the complete .err log, all the way from the birth of the installation up to the corruption. Regards, Heikki - Alkuperäinen viesti - Lähettäjä: jsf [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com Lähetetty: Monday, January 03, 2005 4:56 PM Aihe: Re: BIG InnoDB problems! well, since we were having so many problems with the InnoDB tables, we created a non-InnoDB version of the database in question... Basically we want to do whatever it will take to get InnoDB tables working in our environment. Any assistance you can offer towards this goal will be greatly appreciated! Cheers, J. On Mon, 3 Jan 2005 16:36:42 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Joshua, about dumping tables from a corrupt database, see: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html This describes how to remove the whole InnoDB database: http://dev.mysql.com/doc/mysql/en/Error_creating_InnoDB.html Be very careful. You do not want to lose your valuable data. Regards, Heikki - Alkuperäinen viesti - Lähettäjä: jsf [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com Lähetetty: Monday, January 03, 2005 4:31 PM Aihe: Re: BIG InnoDB problems! Hi Heikki, Please see below... On Mon, 3 Jan 2005 15:14:12 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: Joshua, the stack trace below shows that you are trying to drop a database? Why? At that point, I'd heard from our developer of so many problems I figured what I would do is test things out. I created a database. I created a table in the database as an InnoDB . I tried to insert data into it and was unsuccessful.. I tried a few more things.. all unsuccessful, so I figured I'd just try to drop the database. But I couldn't do that either. If you can, you should use SELECT ... INTO OUTFILE to save of your tables what you can save, then rebuild the whole InnoDB tablespace, and import the tables back to MySQL. I'm going to have to 'go to school' on InnoDB tablespace. I have only the most rudimentary understanding of what you've written here. The .err file below starts from a situation where you have already set innodb_force_recovery to 5. Is that bad? 5 (SRV_FORCE_NO_UNDO_LOG_SCAN) Do not look at undo logs when starting the database: InnoDB will treat even incomplete transactions as committed. Was the original problem the same as what we see below? The history list of InnoDB in the ibdata files seems to be corrupt. The only way to fix that kind of corruption is to rebuild the whole tablespace. Is there a tutorial on rebuilding the tablespace? or deleting the table space and starting over? Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html - Alkuperäinen viesti - Lähettäjä: jsf [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com Lähetetty: Friday, December 31, 2004 5:21 PM Aihe: Re: BIG InnoDB problems! Greetings Heikki and Happy New Year! Here's what I got. I hope it's useful. beech:/home/jfreeman # resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack 0x815f0cf handle_segfault + 575 0xe420 _end + -138916432 0x82e71d5 buf_read_page + 165 0x82e71d5 buf_read_page + 165 0x82db68f buf_page_get_gen + 175 0x830479f flst_insert_before + 239 0x8304cc8 flst_add_first + 152 0x82be800 trx_purge_add_update_undo_to_history + 624 0x82d14a6 trx_undo_update_cleanup + 38 0x82ccafb trx_commit_off_kernel + 363 0x82cd865 trx_sig_start_handle + 1109 0x826232b que_run_threads + 2299 0x827915a row_drop_table_for_mysql + 2314 0x81fe924 _ZN11ha_innobase12delete_tableEPKc + 404 0x81ef33c _Z15ha_delete_table7db_typePKc + 60 0x820aead _Z20mysql_rm_table_part2P3THDP13st_table_listbbb + 989 0x820b19d _Z30mysql_rm_table_part2_with_lockP3THDP13st_table_listbbb + 93 0x8201554 _Z20mysql_rm_known_filesP3THDP9st_my_dirPKcS4_j + 1428 0x8202739 _Z11mysql_rm_dbP3THDPcbb + 345 0x81796cb _Z21mysql_execute_commandP3THD + 19339 0x817c1b4 _Z11mysql_parseP3THDPcj + 484 0x817de5d _Z16dispatch_command19enum_server_commandP3THDPcj + 2685 0x817f137 handle_one_connection + 2391 0x401619ed _end + 936280957 0x403519ca _end + 938312538 p.s. the whole error file is only 301 lines long. If you wish I could send it to you... Here's a segment from lines 1 - 41: 041230
RE: List for newbie
smime.p7m Description: S/MIME encrypted message
Re: installing mysql / error
I did the first 2 step you listed below, but when I typed in the mysql -u root, I got a new error message ERROR 2003 (HY000) Can't connect to MySQL server on 'localhost' (10061) Thanks David. - Original Message - From: Tom Crimmins [EMAIL PROTECTED] To: David Katz [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, December 30, 2004 2:32 PM Subject: RE: installing mysql / error [snip] When I try 'mysql -u root' I get the same error, except root is in the place of ODBC ERROR 1045 (28000): Access denied for user 'root' @'localhost' (using password: NO) [/snip] In that case, you may have a password set for root. To reset the password, shutdown the mysql server. Then start it from a command line using: [PATH TO MYSQL BINS]/mysqld-nt --skip-grant-tables Then open another command prompt and run 'mysql -u root' and run the following query: UPDATE mysql.user SET Password='' WHERE User='root' AND Host='localhost'; Then shutdown the server, and restart it normally. You should then be able to connect with 'mysql -u root'. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa office 712.328.4808 mobile 402.677.1592 -Original Message- From: David Katz [mailto:[EMAIL PROTECTED] Sent: Thursday, December 30, 2004 1:25 PM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: Re: installing mysql / error When I try 'mysql -u root' I get the same error, except root is in the place of ODBC ERROR 1045 (28000): Access denied for user 'root' @'localhost' (using password: NO) - Original Message - From: Tom Crimmins [EMAIL PROTECTED] To: David Katz [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, December 30, 2004 1:57 PM Subject: RE: installing mysql / error [snip] We loaded a new server with Windows XP professional, and mysql server 4.1. We can't get Mysql to run. After installing mysql and trying to execute it we get the following message: ERROR 1045 (28000): Access denied for user 'ODBC' @'localhost' (using password: NO) [/snip] Ignore my previous post, I misunderstood. ODBC is the default account on a windows server. You need to use run 'mysql -u root' from a command-line. You should then be able to grant privs to [EMAIL PROTECTED] --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: installing mysql / error
This error is caused when the server isn't started. Make sure that you start the server first, then log in. When you skip the HOSTS.FRM tables (by doing mysqld --skip-grant-tables), you have to restart the server normally to effect your changes in the mysql root user. J.R. -Original Message- From: David Katz [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 10:30 AM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: Re: installing mysql / error I did the first 2 step you listed below, but when I typed in the mysql -u root, I got a new error message ERROR 2003 (HY000) Can't connect to MySQL server on 'localhost' (10061) Thanks David. - Original Message - From: Tom Crimmins [EMAIL PROTECTED] To: David Katz [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, December 30, 2004 2:32 PM Subject: RE: installing mysql / error [snip] When I try 'mysql -u root' I get the same error, except root is in the place of ODBC ERROR 1045 (28000): Access denied for user 'root' @'localhost' (using password: NO) [/snip] In that case, you may have a password set for root. To reset the password, shutdown the mysql server. Then start it from a command line using: [PATH TO MYSQL BINS]/mysqld-nt --skip-grant-tables Then open another command prompt and run 'mysql -u root' and run the following query: UPDATE mysql.user SET Password='' WHERE User='root' AND Host='localhost'; Then shutdown the server, and restart it normally. You should then be able to connect with 'mysql -u root'. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa office 712.328.4808 mobile 402.677.1592 -Original Message- From: David Katz [mailto:[EMAIL PROTECTED] Sent: Thursday, December 30, 2004 1:25 PM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: Re: installing mysql / error When I try 'mysql -u root' I get the same error, except root is in the place of ODBC ERROR 1045 (28000): Access denied for user 'root' @'localhost' (using password: NO) - Original Message - From: Tom Crimmins [EMAIL PROTECTED] To: David Katz [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, December 30, 2004 1:57 PM Subject: RE: installing mysql / error [snip] We loaded a new server with Windows XP professional, and mysql server 4.1. We can't get Mysql to run. After installing mysql and trying to execute it we get the following message: ERROR 1045 (28000): Access denied for user 'ODBC' @'localhost' (using password: NO) [/snip] Ignore my previous post, I misunderstood. ODBC is the default account on a windows server. You need to use run 'mysql -u root' from a command-line. You should then be able to grant privs to [EMAIL PROTECTED] --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- 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: is there a utility like mysqlbinlog but instead processes the query log?
The program would also need to handle...
...queries that span multiple lines
...change databases when appropriate before queries
yes I could program such a beast but I thought someone else might have
already done it.
daniel
-Original Message-
From: Andy Davidson [mailto:[EMAIL PROTECTED]
Sent: Sunday, January 02, 2005 3:58 AM
To: mysql@lists.mysql.com mysql@lists.mysql.com
Subject: Re: is there a utility like mysqlbinlog but instead processes
the query log?
On 30 Dec 2004, at 13:26, Daniel Gaddis wrote:
is there a utility like mysqlbinlog but instead processes the query
log?
I would like to reprocess the queries from the query log.
I don't see another reply to this on the list, so I hope it helps - the
query log is already in plain-text, so you don't need something to fish
the queries out of an unfriendly format.
This bit of perl should be a good starting point.
elephant:/var/log/mysql# cat pullqueries.pl
#!/usr/bin/perl -w
use strict;
while (my $line = ) {
if ($line =~ /Query/) {
my (undef, undef, undef, undef, $display) = split(/ /,
$line, 5);
print $display;
}
}
example :
elephant:/var/log/mysql# tail -n 20 mysql.log | perl pullqueries.pl
SELECT fname from images where groupid='4' order by viewno desc
limit 0,1
SELECT fname from images where groupid='3' order by viewno desc
limit 0,1
SELECT fname from images where groupid='2' order by viewno desc
limit 0,1
SELECT title,story FROM groups where id='1114'
SELECT id,dirname,fname FROM images where groupid='1114'
SELECT id,dirname,fname,viewno,groupid FROM images where
id='10035' limit 0,1
UPDATE images set viewno='1',lastlook=NOW('') where id='10035'
SELECT entry,whoby FROM ucaptions where picid='10035'
SELECT dirname,fname,caption from images where id='10035' limit
0,1
SELECT title,story FROM groups where id='1114'
SELECT id,dirname,fname FROM images where groupid='1114'
--
Regards, Andy Davidson
http://www.fotoserve.com/
Great quality prints from digital photos.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup/restore
Hi, Thank you for your reply. Even though I used the --add-drop-table option, I am getting the error messages. Actually I am taking the backup for a group of tables as following D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debugmysqldump -uMNMSDBA -p MNMSDBA --databases mnms --add-drop-table --add-locks --disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT ALARM_REPORT_CATEGORY ALARMINFOc:\progra~1\mitel\opsman~1\temp\almhist.dmp And doing the restoring as D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debugmysql -uMNMSDBA -pMNMS DBA -f mnmsc:\progra~1\mitel\opsman~1\temp\almhist.dmp ERROR 1051 at line 11: Unknown table 'alarm'// Trying to drop alarm table ERROR 1050 at line 12: Table 'alarm' already exists //Creating alarm ERROR 1051 at line 72: Unknown table 'alarm_report' //Drop alarm_category ERROR 1050 at line 73: Table 'alarm_report' already exists//Create ERROR 1062 at line 94: Duplicate entry '1' for key 1 // Insert ERROR 1062 at line 95: Duplicate entry '4' for key 1 // Insert ERROR 1062 at line 96: Duplicate entry '5' for key 1 // Insert ERROR 1062 at line 97: Duplicate entry '8' for key 1 // Insert ERROR 1062 at line 98: Duplicate entry '10' for key 1 // Insert ERROR 1062 at line 99: Duplicate entry '11' for key 1 // Insert ERROR 1062 at line 100: Duplicate entry '13' for key 1 // Insert ERROR 1062 at line 101: Duplicate entry '15' for key 1 // Insert ERROR 1062 at line 102: Duplicate entry '16' for key 1 // Insert ERROR 1062 at line 103: Duplicate entry '17' for key 1 // Insert Note : ALARM, ALARM_CATEGORY, ALARM_REPORT, ALARM_REPORT_CATEGORY, ALARMINFO all belongs to one group. Alarm and Alarm_category are parent and child tables. ALARM_REPORT, ALARM_REPORT_CATEGORY are parent and child tables Please advise me, how to get rid of the above error messages. Thanks, Narasimha -Original Message- From: Anil Doppalapudi [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 7:03 PM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS); [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: backup/restore Hi, with which options of mysqldump you have taken backup. if you use --add-drop-table option then it will add drop table statement in dump file. otherwise it wont add that statement and you will get that type of errors. if you didn't use that option then drop the schema and then try to restore it from backup file. Thanks Anil -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 12:58 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: backup/restore Hi, Thank you for your reply. If I have the create table info in my dump file, while doing the restore using mysql dbname dump.dmp, I am getting errors like mysql -uMNMSDBA -pMNMSDBA -f mnms c:\progra~1\mitel\opsman~1\temp\almhist.dmp ERROR 1050 at line 11: Table 'alarm' already exists ERROR 1050 at line 40: Table 'alarm_category' already exists ERROR 1050 at line 70: Table 'alarm_report' already exists ERROR 1062 at line 91: Duplicate entry '1' for key 1 ERROR 1062 at line 92: Duplicate entry '4' for key 1 ERROR 1062 at line 93: Duplicate entry '5' for key 1 ERROR 1062 at line 94: Duplicate entry '8' for key 1 ERROR 1062 at line 95: Duplicate entry '10' for key 1 ERROR 1062 at line 96: Duplicate entry '11' for key 1 ERROR 1062 at line 97: Duplicate entry '13' for key 1 ERROR 1062 at line 98: Duplicate entry '15' for key 1 ERROR 1062 at line 99: Duplicate entry '16' for key 1 ERROR 1062 at line 100: Duplicate entry '17' for key 1 ERROR 1050 at line 108: Table 'alarm_report_category' already exists ERROR 1050 at line 137: Table 'alarminfo' already exists ERROR 1062 at line 155: Duplicate entry '56' for key 1 ERROR 1062 at line 156: Duplicate entry '57' for key 1 ERROR 1062 at line 157: Duplicate entry '58' for key 1 ERROR 1062 at line 158: Duplicate entry '59' for key 1 ERROR 1062 at line 159: Duplicate entry '75' for key 1 ERROR 1062 at line 160: Duplicate entry '76' for key 1 ERROR 1062 at line 161: Duplicate entry '77' for key 1 Please advise me, how to suppress the above error messages. While taking backup used mysqldump -uMNMSDBA -pMNMSDBA --databases mnms --add-locks --disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT ALARM_REPORT_CATEGORY ALARMINFO c:\progra~1\mitel\opsman~1\temp\almhist.dmp Please help me in this. Thanks, Narasimha -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 10:55 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: mysql@lists.mysql.com Subject: RE: backup/restore [snip] I am doing backup for tables using Mysqldump. But while doing the restore I am not able to do that using the same Mysqldump. Could you please help me in that. [/snip] mysqldump is not intended to be used for the restore. You need to run the following: mysql -D dbname
changing data dir
Do I have to reinstall mysql in order to change the default data dir from /var/lib/mysql? Can I do this: mv /var/lib/mysql /newdir/mysql cd /var/lib ln -s /newdir/mysql mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max connections being used every 10-12 day.
mysql describe art; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | parent | int(11) | YES | MUL | NULL|| | bodyid | int(11) | YES | | NULL|| | lst| varchar(80) | YES | MUL | NULL|| | mf | varchar(80) | YES | | NULL|| | mt | varchar(80) | YES | | NULL|| | subc | varchar(200) | YES | MUL | NULL|| | sdate | varchar(45) | YES | | NULL|| | batch | varchar(80) | YES | MUL | NULL|| | mgid | varchar(90) | YES | | NULL|| | date| datetime | YES | MUL | NULL|| +-+--+--+-+-++ 11 rows in set (0.12 sec) mysql show index from art; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+---+-+--++--++-+ | art | 0 | PRIMARY |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | date |1 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | lst |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | id_2 |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id_2 |2 | parent | A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |2 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |3 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | batch|1 | batch | A | 141 | NULL | NULL | YES | BTREE | | | art | 1 | batch|2 | lst| A |1177 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |2 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |3 | batch | A | 67501 | NULL | NULL | YES | BTREE | | | art | 1 | id_3 |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id_3 |2 | parent | A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | id_3 |3 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | parent |1 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | subc |1 | subc | A | 40501 | NULL | NULL | YES | FULLTEXT | | +++--+--+-+---+-+--++--++-+ 19 rows in set (0.04 sec) The query: Select A.id, A.parent, B.id, B.parent from art A inner join art B using (id) order by A.date; Would return several hundered thousend answers so i added a where option, i dont know if this was what you had in mind. The way i used your query it should be the same thing as: Select A.id, A.parent from art A where A.id=60 or A.parent=60 order by A.date; mysql explain Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or A.parent=60 order by A.date;
AW: AW: [PHP] How to argue with ASP people...
Ahhh, you guys are sooo funny - you all know what I mean -Ursprüngliche Nachricht- Von: Jay Blanchard [mailto:[EMAIL PROTECTED] Gesendet: Montag, 3. Januar 2005 14:36 An: Freddie Sorensen; [EMAIL PROTECTED] Cc: mail.pmpa; mysql@lists.mysql.com Betreff: RE: AW: [PHP] How to argue with ASP people... [snip] It is worth it for performance reasons ! [/snip] Really? What performance stats are you looking at here? [snip] And if you believe ASP.NET only provides a few benefits, then you are adequately informed, my friend [/snip] Yes, he is adequately informed...and correct. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup/restore
Drop the tables manually then try the restore. [snip] ERROR 1051 at line 11: Unknown table 'alarm' ERROR 1050 at line 12: Table 'alarm' already exists [/snip] It seems strange that you get these errors in this order. If you post maybe the first 15 lines of your dump file, I may be able to provide more help. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- Hi, Thank you for your reply. Even though I used the --add-drop-table option, I am getting the error messages. Actually I am taking the backup for a group of tables as following D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debugmysqldump -uMNMSDBA -p MNMSDBA --databases mnms --add-drop-table --add-locks --disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT ALARM_REPORT_CATEGORY ALARMINFOc:\progra~1\mitel\opsman~1\temp\almhist.dmp And doing the restoring as D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debugmysql -uMNMSDBA -pMNMS DBA -f mnmsc:\progra~1\mitel\opsman~1\temp\almhist.dmp ERROR 1051 at line 11: Unknown table 'alarm'// Trying to drop alarm table ERROR 1050 at line 12: Table 'alarm' already exists //Creating alarm ERROR 1051 at line 72: Unknown table 'alarm_report' //Drop alarm_category ERROR 1050 at line 73: Table 'alarm_report' already exists//Create ERROR 1062 at line 94: Duplicate entry '1' for key 1 // Insert ERROR 1062 at line 95: Duplicate entry '4' for key 1 // Insert ERROR 1062 at line 96: Duplicate entry '5' for key 1 // Insert ERROR 1062 at line 97: Duplicate entry '8' for key 1 // Insert ERROR 1062 at line 98: Duplicate entry '10' for key 1 // Insert ERROR 1062 at line 99: Duplicate entry '11' for key 1 // Insert ERROR 1062 at line 100: Duplicate entry '13' for key 1 // Insert ERROR 1062 at line 101: Duplicate entry '15' for key 1 // Insert ERROR 1062 at line 102: Duplicate entry '16' for key 1 // Insert ERROR 1062 at line 103: Duplicate entry '17' for key 1 // Insert Note : ALARM, ALARM_CATEGORY, ALARM_REPORT, ALARM_REPORT_CATEGORY, ALARMINFO all belongs to one group. Alarm and Alarm_category are parent and child tables. ALARM_REPORT, ALARM_REPORT_CATEGORY are parent and child tables Please advise me, how to get rid of the above error messages. Thanks, Narasimha -Original Message- Hi, with which options of mysqldump you have taken backup. if you use --add-drop-table option then it will add drop table statement in dump file. otherwise it wont add that statement and you will get that type of errors. if you didn't use that option then drop the schema and then try to restore it from backup file. Thanks Anil -Original Message- Hi, Thank you for your reply. If I have the create table info in my dump file, while doing the restore using mysql dbname dump.dmp, I am getting errors like mysql -uMNMSDBA -pMNMSDBA -f mnms c:\progra~1\mitel\opsman~1\temp\almhist.dmp ERROR 1050 at line 11: Table 'alarm' already exists ERROR 1050 at line 40: Table 'alarm_category' already exists ERROR 1050 at line 70: Table 'alarm_report' already exists ERROR 1062 at line 91: Duplicate entry '1' for key 1 ERROR 1062 at line 92: Duplicate entry '4' for key 1 ERROR 1062 at line 93: Duplicate entry '5' for key 1 ERROR 1062 at line 94: Duplicate entry '8' for key 1 ERROR 1062 at line 95: Duplicate entry '10' for key 1 ERROR 1062 at line 96: Duplicate entry '11' for key 1 ERROR 1062 at line 97: Duplicate entry '13' for key 1 ERROR 1062 at line 98: Duplicate entry '15' for key 1 ERROR 1062 at line 99: Duplicate entry '16' for key 1 ERROR 1062 at line 100: Duplicate entry '17' for key 1 ERROR 1050 at line 108: Table 'alarm_report_category' already exists ERROR 1050 at line 137: Table 'alarminfo' already exists ERROR 1062 at line 155: Duplicate entry '56' for key 1 ERROR 1062 at line 156: Duplicate entry '57' for key 1 ERROR 1062 at line 157: Duplicate entry '58' for key 1 ERROR 1062 at line 158: Duplicate entry '59' for key 1 ERROR 1062 at line 159: Duplicate entry '75' for key 1 ERROR 1062 at line 160: Duplicate entry '76' for key 1 ERROR 1062 at line 161: Duplicate entry '77' for key 1 Please advise me, how to suppress the above error messages. While taking backup used mysqldump -uMNMSDBA -pMNMSDBA --databases mnms --add-locks --disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT ALARM_REPORT_CATEGORY ALARMINFO c:\progra~1\mitel\opsman~1\temp\almhist.dmp Please help me in this. Thanks, Narasimha -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 10:55 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: mysql@lists.mysql.com Subject: RE: backup/restore [snip] I am doing backup for tables using Mysqldump. But while doing the restore I am not able to do that using the same Mysqldump. Could you please help me in that. [/snip] mysqldump is not intended to be used for the restore. You need to run the following: mysql -D dbname mysqldumpfile You may
RE: changing data dir
[snip] Do I have to reinstall mysql in order to change the default data dir from /var/lib/mysql? Can I do this: mv /var/lib/mysql /newdir/mysql cd /var/lib ln -s /newdir/mysql mysql [/snip] This should work. You could also change the datadir variable in your my.cnf file instead of creating the link. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup/restore
Hi, use the below command to take backup mysqldump databasename tablename tablename --add-drop-table dump file name i tested it on my local server also it is working fine for me. other wise just open the dump file by executing the below command on linux: head -100 dumpfile name o/p: -- MySQL dump 8.22 -- -- Host: localhostDatabase: NOCINFO - -- Server version 4.0.18-standard -- -- Table structure for table 'IPInfo' -- DROP TABLE IF EXISTS IPInfo; CREATE TABLE IPInfo ( IPAddr varchar(12) NOT NULL default '', PingStatus varchar(10) NOT NULL default '', ServerName varchar(25) NOT NULL default '', ReverseLook varchar(100) NOT NULL default '', ForwardLook varchar(12) default NULL, Remarks varchar(200) default NULL, SerId varchar(10) default NULL, PRIMARY KEY (IPAddr) ) TYPE=MyISAM MAX_ROWS=1000; check the above you will find drop table command in dumpfile. Thanks Anil -- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 9:50 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: backup/restore Hi, Thank you for your reply. Even though I used the --add-drop-table option, I am getting the error messages. Actually I am taking the backup for a group of tables as following D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debugmysqldump -uMNMSDBA -p MNMSDBA --databases mnms --add-drop-table --add-locks --disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT ALARM_REPORT_CATEGORY ALARMINFOc:\progra~1\mitel\opsman~1\temp\almhist.dmp And doing the restoring as D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debugmysql -uMNMSDBA -pMNMS DBA -f mnmsc:\progra~1\mitel\opsman~1\temp\almhist.dmp ERROR 1051 at line 11: Unknown table 'alarm'// Trying to drop alarm table ERROR 1050 at line 12: Table 'alarm' already exists //Creating alarm ERROR 1051 at line 72: Unknown table 'alarm_report' //Drop alarm_category ERROR 1050 at line 73: Table 'alarm_report' already exists//Create ERROR 1062 at line 94: Duplicate entry '1' for key 1 // Insert ERROR 1062 at line 95: Duplicate entry '4' for key 1 // Insert ERROR 1062 at line 96: Duplicate entry '5' for key 1 // Insert ERROR 1062 at line 97: Duplicate entry '8' for key 1 // Insert ERROR 1062 at line 98: Duplicate entry '10' for key 1 // Insert ERROR 1062 at line 99: Duplicate entry '11' for key 1 // Insert ERROR 1062 at line 100: Duplicate entry '13' for key 1 // Insert ERROR 1062 at line 101: Duplicate entry '15' for key 1 // Insert ERROR 1062 at line 102: Duplicate entry '16' for key 1 // Insert ERROR 1062 at line 103: Duplicate entry '17' for key 1 // Insert Note : ALARM, ALARM_CATEGORY, ALARM_REPORT, ALARM_REPORT_CATEGORY, ALARMINFO all belongs to one group. Alarm and Alarm_category are parent and child tables. ALARM_REPORT, ALARM_REPORT_CATEGORY are parent and child tables Please advise me, how to get rid of the above error messages. Thanks, Narasimha -Original Message- From: Anil Doppalapudi [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 7:03 PM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS); [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: backup/restore Hi, with which options of mysqldump you have taken backup. if you use --add-drop-table option then it will add drop table statement in dump file. otherwise it wont add that statement and you will get that type of errors. if you didn't use that option then drop the schema and then try to restore it from backup file. Thanks Anil -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 12:58 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: backup/restore Hi, Thank you for your reply. If I have the create table info in my dump file, while doing the restore using mysql dbname dump.dmp, I am getting errors like mysql -uMNMSDBA -pMNMSDBA -f mnms c:\progra~1\mitel\opsman~1\temp\almhist.dmp ERROR 1050 at line 11: Table 'alarm' already exists ERROR 1050 at line 40: Table 'alarm_category' already exists ERROR 1050 at line 70: Table 'alarm_report' already exists ERROR 1062 at line 91: Duplicate entry '1' for key 1 ERROR 1062 at line 92: Duplicate entry '4' for key 1 ERROR 1062 at line 93: Duplicate entry '5' for key 1 ERROR 1062 at line 94: Duplicate entry '8' for key 1 ERROR 1062 at line 95: Duplicate entry '10' for key 1 ERROR 1062 at line 96: Duplicate entry '11' for key 1 ERROR 1062 at line 97: Duplicate entry '13' for key 1 ERROR 1062 at line 98: Duplicate entry '15' for key 1 ERROR 1062 at line 99: Duplicate entry '16' for key 1 ERROR 1062 at line 100: Duplicate entry '17' for key 1 ERROR 1050 at line 108: Table 'alarm_report_category' already exists ERROR 1050 at line 137: Table 'alarminfo' already exists ERROR
doing SELECTS and keeping the array intact with php
Hi all,
this has been bugging me for some time now.
I want to do a mysql select
then using php
while ($a_row = mysql_fetch_array($result))
{
do something to get a small portion of the data
}
and that works fine. Then later I need to access $result as a complete array
again. Is there a way I can keep $result array intact and still take a
couple values out beforehand. I can get it to work, by doing the select
twice, but thats less than ideal.
Thanks enormously if you can help
Jules
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: List for newbie
At 3:56 PM +0100 1/3/05, Paun wrote: I am very new in mysql, and don't want to disturb users who have much more expirience with mysql. Is there any mysql list for newbies?? No, this is the appropriate list...just make sure you: (1) first try to search the manual: http://dev.mysql.com/doc/ (2) search the mailing list archives: http://lists.mysql.com/ http://marc.theaimsgroup.com/?l=mysqlr=1w=2 (3) Then, if you still need to post a message to the mailing list: * Explain your problem as clearly as possible * Describe what you've tried already *If appropriate, post table structures, example queries, and/or example output Here's a *comprehensive* reference on good ways to ask questions: http://www.catb.org/~esr/faqs/smart-questions.html ...and, finally, welcome to the MySQL world! steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
big table corruption
Hi, I have a big table (900k rows, 200M MYD, 200M MYI) with a fulltext index on it. The table gets corrupted every 1 week or so and I have to repair it. I've tried upgrading to newer versions of mysql 3 times but the problem persists. I think it may have something to do with the raid 1 mirror but I can't really tell. Does anyone have any ideas? Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: doing SELECTS and keeping the array intact with php
[snip]
this has been bugging me for some time now.
I want to do a mysql select
then using php
while ($a_row = mysql_fetch_array($result))
{
do something to get a small portion of the data
}
and that works fine. Then later I need to access $result as a complete
array
again. Is there a way I can keep $result array intact and still take a
couple values out beforehand. I can get it to work, by doing the select
twice, but thats less than ideal.
[/snip]
More of a PHP question, but use reset($result); instead of a new query
http://www.php.net/reset
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about indexing a highly searched table
Hi all , I have a question about how to best approach indexing a table with many variations of fields searched by, but one common ORDER BY field. Take for example this table (with only relevant searched fields shown): CREAT TABLE Offers ( bid` mediumtext NOT NULL, `company_name` varchar(50) default NULL, `country` varchar(25) NOT NULL default '', `email` varchar(100) NOT NULL default '', `keywords` varchar(100) default NULL, `deletedate` date NOT NULL default '-00-00', `subcatID` int(10) unsigned NOT NULL default '0', `ID` int(10) unsigned NOT NULL auto_increment, `userID` int(10) unsigned NOT NULL default '0', FULLTEXT KEY `keywords` (`keywords`), FULLTEXT KEY `bid` (`bid`) ) So , the question about indexes comes up. There are many variations of searches that will happen such as: bid contains certain words keywords contain certain words subcatID IN (1,23,3,4,5,6,7,8,9,) etc.. subcatID IN (1,23,3,4,5,6,7,8,9,) and country='XX' subcatID IN (1,23,3,4,5,6,7,8,9,) and country='XX' AND userID = '12345678' and so on. and all mixed together at points. subcatID is *usually* in the search criteria. everything is ordered by deletedate to get the most recent results first. What should the thinking be when deciding how to best index this table for speed of searches? All advice welcome and appreciated! Thanks, Aaron
Re: InnoDB: Error: tablespace size stored in header is 17024 pages, but
Ian, are you sure that you copied the complete ibdata1 file to the new place? It is strange how 7 MB can be missing from the file end. What does the old .err log contain? Any message about disk space running out? The error below probably has not corrupted your tablespace. Best to run CHECK TABLE on some of your tables, though. When ibdata files are created, they are initially written full of zeros. You can probably fix the error by using another mysqld installation (or any method that makes a 7 MB zero-filled file) to create a new ibdata2 file that is 7 MB in size, and add that to the tablespace. Look from http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html how to edit my.cnf then. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Ian Grant [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, December 31, 2004 5:26 PM Subject: InnoDB: Error: tablespace size stored in header is 17024 pages, but Dear MySQL people I have a question regarding the error: InnoDB: Error: tablespace size stored in header is 17024 pages, but InnoDB: the sum of data file sizes is only 16576 pages which appeared when I upgraded from 4.0.7 to 4.1.7. Somehow I am 448 pages (7M) short of table space. Can I fix this using the procedure outlined in http://archives.neohapsis.com/archives/mysql/2004-q3/3723.html or should I do a dump/restore? Here are the gory details: I was running MySQL Ver 12.22 Distrib 4.0.17 with the following /etc/my.cnf file: [mysqld] user=www datadir=/local/own-1/rt/mysql-data innodb_data_file_path = ibdata1:10M:autoextend Then I built a new MySQL Ver 14.7 Distrib 4.1.7 and decided to do binary logging so I changed /etc/my.cnf to read [mysqld] user=rt datadir=/local/own-1/rt/mysql-data innodb_data_file_path = ibdata1:10M:autoextend # Set buffer pool size to 50-80% of memory set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=10M # Set the log file size to about 25% of the buffer pool size set-variable = innodb_log_file_size=64M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 # Turn on binary logging and sync set-variable = log_bin=1 set-variable = sync_binlog=1 Then I shut down the old database, copied the data files and deleted the ib_logfile* files and this is what I got in the logs on starting 4.1.7. I was a bit surprised at the 'not shut down normally' message, but hey. ... 041229 14:04:51 mysqld started 041229 14:04:51 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 64 MB InnoDB: Database physically writes the file full: wait... 041229 14:04:53 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 64 MB InnoDB: Database physically writes the file full: wait... 041229 14:04:55 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Resetting space id's in the doublewrite buffer 041229 14:04:55 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 405584396. InnoDB: Doing recovery: scanned up to log sequence number 0 405584396 041229 14:04:55 InnoDB: Flushing modified pages from the buffer pool... 041229 14:04:55 InnoDB: Started; log sequence number 0 405584396 InnoDB: You are upgrading to an InnoDB version which allows multiple InnoDB: tablespaces. Wait that purge and insert buffer merge run to InnoDB: completion... InnoDB: Full purge and insert buffer merge completed. InnoDB: You have now successfully upgraded to the multiple tablespaces InnoDB: format. You should NOT DOWNGRADE to an earlier version of InnoDB: InnoDB! But if you absolutely need to downgrade, see InnoDB: http://dev.mysql.com/doc/mysql/en/Multiple_tablespaces.html InnoDB: for instructions. 041229 14:04:57 [Warning] mysql.user table is not updated to new password format ; Disabling new password usage until mysql_fix_privilege_tables is run 041229 14:04:57 [Warning] Can't open and lock time zone table: Table 'mysql.time _zone_leap_second' doesn't exist trying to live without them /local/own-1/rt/rt-3.2.2/libexec/mysqld: ready for connections. Version: '4.1.7-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution But now when I start the database I get: 041230 10:04:37 mysqld started InnoDB: Error: tablespace size stored in header is 17024 pages, but InnoDB: the sum of data file sizes is only 16576 pages 041230 10:04:39 InnoDB: Started; log sequence number 0 405783741 041230 10:04:39 [Warning] mysql.user table is not updated to new password format ;
RE: doing SELECTS and keeping the array intact with php
http://us2.php.net/manual/en/function.mysql-data-seek.php
This lets you set the internal row pointer of the mysql result. In your case
you would want mysql_data_seek($result,0).
---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
-Original Message-
From: julian haffegee
Sent: Monday, January 03, 2005 12:31 PM
To: MySQL General List
Subject: doing SELECTS and keeping the array intact with php
Hi all,
this has been bugging me for some time now.
I want to do a mysql select
then using php
while ($a_row = mysql_fetch_array($result))
{
do something to get a small portion of the data }
and that works fine. Then later I need to access $result as a complete array
again. Is there a way I can keep $result array intact and still take a
couple values out beforehand. I can get it to work, by doing the select
twice, but thats less than ideal.
Thanks enormously if you can help
Jules
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: doing SELECTS and keeping the array intact with php
mysql_field_seek( resultset, 0 );
PB
-
julian haffegee wrote:
Hi all,
this has been bugging me for some time now.
I want to do a mysql select
then using php
while ($a_row = mysql_fetch_array($result))
{
do something to get a small portion of the data
}
and that works fine. Then later I need to access $result as a complete
array again. Is there a way I can keep $result array intact and still
take a couple values out beforehand. I can get it to work, by doing
the select twice, but thats less than ideal.
Thanks enormously if you can help
Jules
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: doing SELECTS and keeping the array intact with php
julian haffegee schrieb:
Hi all,
this has been bugging me for some time now.
I want to do a mysql select
then using php
while ($a_row = mysql_fetch_array($result))
{
do something to get a small portion of the data
}
hi julian,
how about using this snip
$result = mysql_query($sql)or die(fehler:.mysql_error());
$num = mysql_num_rows($result);
$erg = array();
for ($i = 0; $i $num; $i++) {
$erg[$i] = array();
$erg[$i] = mysql_fetch_object($result);
}
i work with it, and its just fine. put it in a function or a class and
you wont have to bother about processing the results anymore.
have fun.
greetz seba
---
http://www.helft-sebastian.de
http://www.schaudirmaldiean.de
http://www.random-link.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: big table corruption
Mark, - Original Message - From: Mark Maggelet [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, January 03, 2005 8:36 PM Subject: big table corruption Hi, I have a big table (900k rows, 200M MYD, 200M MYI) with a fulltext index on it. The table gets corrupted every 1 week or so and I have to repair it. I've tried upgrading to newer versions of mysql 3 times but the problem persists. I think it may have something to do with the raid 1 mirror but I can't really tell. Does anyone have any ideas? it should not get corrupt. Please file a very detailed bug report to bugs.mysql.com, if you are willing to upload the corrupt files via ftp for further analysis. What does CHECK TABLE print about the corruption? What MySQL version? What is SHOW CREATE TABLE like? What OS? Thanks, - Mark Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connecting MySQL to OpenOffice
I am having areal time trying to get my Open Office to recognize my MySQL server. I am using the j-connector : Generally Available (GA) 3.0.6 I have this installed in my path $HOME/java. I have notified the tools-Options-Security-ClassPath. but I still keep getting an error: A driver is not registered for jdbc:mysql://localhost/services On a fedora core1 machine. Running Open Office 1.1, MySQL 3.23.58 Any suggestions? Another question: Are the j-connectors matched for versions of MySQL or available by releases? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connecting MySQL to OpenOffice
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jay Scherrer wrote: I am having areal time trying to get my Open Office to recognize my MySQL server. I am using the j-connector : Generally Available (GA) 3.0.6 I have this installed in my path $HOME/java. I have notified the tools-Options-Security-ClassPath. but I still keep getting an error: A driver is not registered for jdbc:mysql://localhost/services On a fedora core1 machine. Running Open Office 1.1, MySQL 3.23.58 Any suggestions? 3.0.6 is quite old, you might want to consider upgrading, although it sounds more like you have a CLASSPATH problem. Did you restart OOo after you configured it? Another question: Are the j-connectors matched for versions of MySQL or available by releases? Covered in the docs at http://dev.mysql.com/doc/connector/j/en/index.html#id2425523 Version Guidelines When connecting to MySQL server version 4.1 or newer, it is best to use MySQL Connector/J version 3.1, as it has full support for features in the newer versions of the server, including Unicode characters, views, stored procedures and server-side prepared statements. While Connector/J version 3.0 will connect to MySQL server, version 4.1 or newer, and implements Unicode characters and the new authorization mechanism, Connector/J 3.0 will not be updated to support new features in current and future server versions. -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity Office: +1 408 213 6557 www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB2ZhbtvXNTca6JD8RAoplAKCDgnf83IoXIHJBKNkVWMCpGg+mFgCfRbIG LGg3VlV/XGtCED3kxkIj7s8= =LINb -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Max connections being used every 10-12 day.
Frederik, I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 Get rid of id, id_2, and id_3. You need to do this for everything that you have duplicates of. Next according to your original query, the real query you should try and run should look like this: Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or B.parent=60 order by A.date Donny -Original Message- From: Fredrik Carlsson [mailto:[EMAIL PROTECTED] Sent: Monday, January 03, 2005 11:08 AM To: Donny Simonton Cc: mysql@lists.mysql.com Subject: Re: Max connections being used every 10-12 day. mysql describe art; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | parent | int(11) | YES | MUL | NULL|| | bodyid | int(11) | YES | | NULL|| | lst| varchar(80) | YES | MUL | NULL|| | mf | varchar(80) | YES | | NULL|| | mt | varchar(80) | YES | | NULL|| | subc | varchar(200) | YES | MUL | NULL|| | sdate | varchar(45) | YES | | NULL|| | batch | varchar(80) | YES | MUL | NULL|| | mgid | varchar(90) | YES | | NULL|| | date| datetime | YES | MUL | NULL|| +-+--+--+-+-++ 11 rows in set (0.12 sec) mysql show index from art; +++--+--+-+--- +-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+--- +-+--++--++-+ | art | 0 | PRIMARY |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | date |1 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | lst |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | id_2 |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id_2 |2 | parent | A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |2 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |3 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | batch|1 | batch | A | 141 | NULL | NULL | YES | BTREE | | | art | 1 | batch|2 | lst| A |1177 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |2 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |3 | batch | A | 67501 | NULL | NULL | YES | BTREE | | | art | 1 | id_3 |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id_3 |2 | parent | A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | id_3 |3 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | parent |1 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | subc |1 | subc | A | 40501 |
Re: big table corruption
Thanks Heikki,
check table gives me this:
mysql check table resourcesback;
++---+--++
| Table | Op| Msg_type | Msg_text |
++---+--++
| edplanet.resourcesback | check | warning | Table is marked as crashed |
| edplanet.resourcesback | check | error| Found 91296 keys of 91297 |
| edplanet.resourcesback | check | error| Corrupt|
++---+--++
mysql version is: mysql-standard-4.0.21-pc-linux-i686
but I had similar problems with:
mysql-standard-4.0.18-pc-linux-i686 and
mysql-standard-4.1.1-alpha-pc-linux-i686
show create table says:
CREATE TABLE `resourcesback` (
`id` int(11) NOT NULL auto_increment,
`url` varchar(255) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`keywords` text NOT NULL,
`description` varchar(255) NOT NULL default '',
`rating` tinyint(4) NOT NULL default '0',
`grade` set('Pre-K','K-2','3-5','6-8','9-12','Higher Ed') default NULL,
`media` varchar(255) NOT NULL default '',
`image` varchar(255) NOT NULL default '',
`imageexists` enum('Y','N') NOT NULL default 'N',
`imagewidth` int(11) NOT NULL default '0',
`imageheight` int(11) NOT NULL default '0',
`controversial` enum('Y','N') NOT NULL default 'N',
`contents` set('audio','video','images') default NULL,
`timelinestart` int(11) NOT NULL default '0',
`timelineend` int(11) NOT NULL default '0',
`reviewer` varchar(255) default NULL,
`day` date default NULL,
`status` varchar(255) NOT NULL default 'active',
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`),
KEY `media` (`media`),
FULLTEXT KEY `title` (`title`,`description`,`keywords`)
) TYPE=MyISAM
OS is RedHat 9 - 2.4.20-8smp
I'd rather not upload the crashed table because it is really big and it
has some sensitive data.
Thanks,
- Mark
Heikki Tuuri wrote:
Mark,
- Original Message - From: Mark Maggelet
[EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Monday, January 03, 2005 8:36 PM
Subject: big table corruption
Hi,
I have a big table (900k rows, 200M MYD, 200M MYI) with a fulltext index
on it. The table gets corrupted every 1 week or so and I have to repair
it. I've tried upgrading to newer versions of mysql 3 times but the
problem persists. I think it may have something to do with the raid 1
mirror but I can't really tell.
Does anyone have any ideas?
it should not get corrupt. Please file a very detailed bug report to
bugs.mysql.com, if you are willing to upload the corrupt files via ftp
for further analysis.
What does CHECK TABLE print about the corruption? What MySQL version?
What is SHOW CREATE TABLE like? What OS?
Thanks,
- Mark
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Max connections being used every 10-12 day.
[snip] I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 [/snip] id_2 and id_3 are composite indexes. As the explain shows, mysql is using id_3 because it is the most specific to the query. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Donny Simonton Sent: Monday, January 03, 2005 1:12 PM To: 'Fredrik Carlsson' Cc: mysql@lists.mysql.com Subject: RE: Max connections being used every 10-12 day. Frederik, I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 Get rid of id, id_2, and id_3. You need to do this for everything that you have duplicates of. Next according to your original query, the real query you should try and run should look like this: Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or B.parent=60 order by A.date Donny -Original Message- From: Fredrik Carlsson Sent: Monday, January 03, 2005 11:08 AM To: Donny Simonton Cc: mysql@lists.mysql.com Subject: Re: Max connections being used every 10-12 day. mysql describe art; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | parent | int(11) | YES | MUL | NULL|| | bodyid | int(11) | YES | | NULL|| | lst| varchar(80) | YES | MUL | NULL|| | mf | varchar(80) | YES | | NULL|| | mt | varchar(80) | YES | | NULL|| | subc | varchar(200) | YES | MUL | NULL|| | sdate | varchar(45) | YES | | NULL|| | batch | varchar(80) | YES | MUL | NULL|| | mgid | varchar(90) | YES | | NULL|| | date| datetime | YES | MUL | NULL|| +-+--+--+-+-++ 11 rows in set (0.12 sec) mysql show index from art; +++--+--+-+--- +-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+--- +-+--++--++-+ | art | 0 | PRIMARY |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | date |1 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | lst |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | id_2 |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id_2 |2 | parent | A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |2 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |3 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | batch|1 | batch | A | 141 | NULL | NULL | YES | BTREE | | | art | 1 | batch|2 | lst| A |1177 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |2 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |3 | batch | A |
RE: Max connections being used every 10-12 day.
There are some unnecessary indexes though. These include id_2 and 1st. This is because id_3 is an index on (id,parent,date) and id_2 is and index on (id,parent). The way mysql uses indexes, id_3 will work for (id), (id,parent), and (id,parent,date). The same is true of the '1st' index. This is covered by 1st_2 or 1st_3. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Tom Crimmins Sent: Monday, January 03, 2005 2:49 PM To: Donny Simonton; 'Fredrik Carlsson' Cc: mysql@lists.mysql.com Subject: RE: Max connections being used every 10-12 day. [snip] I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 [/snip] id_2 and id_3 are composite indexes. As the explain shows, mysql is using id_3 because it is the most specific to the query. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Donny Simonton Sent: Monday, January 03, 2005 1:12 PM To: 'Fredrik Carlsson' Cc: mysql@lists.mysql.com Subject: RE: Max connections being used every 10-12 day. Frederik, I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 Get rid of id, id_2, and id_3. You need to do this for everything that you have duplicates of. Next according to your original query, the real query you should try and run should look like this: Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or B.parent=60 order by A.date Donny -Original Message- From: Fredrik Carlsson Sent: Monday, January 03, 2005 11:08 AM To: Donny Simonton Cc: mysql@lists.mysql.com Subject: Re: Max connections being used every 10-12 day. mysql describe art; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | parent | int(11) | YES | MUL | NULL|| | bodyid | int(11) | YES | | NULL|| | lst| varchar(80) | YES | MUL | NULL|| | mf | varchar(80) | YES | | NULL|| | mt | varchar(80) | YES | | NULL|| | subc | varchar(200) | YES | MUL | NULL|| | sdate | varchar(45) | YES | | NULL|| | batch | varchar(80) | YES | MUL | NULL|| | mgid | varchar(90) | YES | | NULL|| | date| datetime | YES | MUL | NULL|| +-+--+--+-+-++ 11 rows in set (0.12 sec) mysql show index from art; +++--+--+-+--- +-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+--- +-+--++--++-+ | art | 0 | PRIMARY |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | date |1 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | lst |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | id_2 |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id_2 |2 | parent | A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |2 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |3 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | batch|
MySQL 4.1.7 allows non-unique values in a unique index?!
WOW! This is a really bad bug... mysql SHOW INDEX FROM LITERAL; +-++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-++--+--+-+---+-+--++--++-+ | LITERAL | 0 | PRIMARY |1 | ID | A | 14331755 | NULL | NULL | | BTREE | | | LITERAL | 0 | VALUE|1 | VALUE | A | 14331755 | NULL | NULL | | BTREE | | +-++--+--+-+---+-+--++--++-+ Notice the unique index on VALUE ? Now take a look at this: mysql SELECT *, MD5(LITERAL.VALUE) AS MD5_VALUE FROM LITERAL WHERE ID=567344 OR ID=14076840; +--+---+--+ | ID | VALUE | MD5_VALUE| +--+---+--+ | 567344 | Law | 81588d326cebe6416d3904db93603af1 | | 14076840 | Law | 81588d326cebe6416d3904db93603af1 | +--+---+--+ 2 rows in set (0.00 sec) Seems like a fatal bug to me! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 allows non-unique values in a unique index?!
Kevin A. Burton wrote: WOW! This is a really bad bug... So now only that... but check this out: mysql SELECT * FROM LITERAL WHERE VALUE = 'Law'; +--+---+ | ID | VALUE | +--+---+ | 14076840 | Law | +--+---+ 1 row in set (0.00 sec) Which only returns ONE row even though there are two in the database with the same value! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 allows non-unique values in a unique index?!
Hi, This could also be a badly corrupted table, what does CHECK TABLE / REPAIR TABLE report ? Regards, Jocelyn - Original Message - From: Kevin A. Burton [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, January 03, 2005 10:04 PM Subject: MySQL 4.1.7 allows non-unique values in a unique index?! WOW! This is a really bad bug... mysql SHOW INDEX FROM LITERAL; +-++--+--+-+--- +-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-++--+--+-+--- +-+--++--++-+ | LITERAL | 0 | PRIMARY |1 | ID | A |14331755 | NULL | NULL | | BTREE | | | LITERAL | 0 | VALUE|1 | VALUE | A |14331755 | NULL | NULL | | BTREE | | +-++--+--+-+--- +-+--++--++-+ Notice the unique index on VALUE ? Now take a look at this: mysql SELECT *, MD5(LITERAL.VALUE) AS MD5_VALUE FROM LITERAL WHERE ID=567344 OR ID=14076840; +--+---+--+ | ID | VALUE | MD5_VALUE| +--+---+--+ | 567344 | Law | 81588d326cebe6416d3904db93603af1 | | 14076840 | Law | 81588d326cebe6416d3904db93603af1 | +--+---+--+ 2 rows in set (0.00 sec) Seems like a fatal bug to me! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- 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: big table corruption
At 12:37 PM 1/3/2005, you wrote: Hi, I have a big table (900k rows, 200M MYD, 200M MYI) with a fulltext index on it. The table gets corrupted every 1 week or so and I have to repair it. I've tried upgrading to newer versions of mysql 3 times but the problem persists. I think it may have something to do with the raid 1 mirror but I can't really tell. Does anyone have any ideas? Thanks, - Mark Mark, This might help http://www.google.com/search?hl=enq=mysql+raid+corruptionbtnG=Google+Searchmeta= You can also check the groups http://groups-beta.google.com/groups?q=mysql+raid+corruptionstart=10hl=enlr=; You may want to try and narrow down the results by also supplying your Raid hardware/software that you're using. It could also be processor/OS related. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 allows non-unique values in a unique index?!
Jocelyn Fournier wrote: Hi, This could also be a badly corrupted table, what does CHECK TABLE / REPAIR TABLE report ? Thats next on our list... going to take care of that tonight. Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
max_rows with largetext field
Hi there, I create a large table with the following command CREATE TABLE MyTable( ID int unsigned not null, Description longtext, index(ID), foreign key (ID) references MainTable (ID) on delete cascade ); alter table MyTable max_rows = 200; Then if I do show create table statement, it shows MAX_ROWS=4294967275. Then when I tried to load a data file with 10814572 records, it stops after loading 5081016 records and gave the Table is full message. However, if I change the Description column from largetext to text, I can successfully load into all those records. Any idea why? Here is my server info $ uname -a Linux machinename 2.4.21-9.0.3.ELsmp #1 SMP Tue Apr 20 19:49:13 EDT 2004 i686 i686 i386 GNU/Linux MySQL version is 4.1.8-standard Thanks! Qunfeng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max connections being used every 10-12 day.
I really appreciate your help :) I did some cleanup of my indexes(there are a couple of them left to clean out but it takes so long time): mysql show index from art; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+---+-+--++--++-+ | art | 0 | PRIMARY |1 | id | A | 542437 | NULL | NULL | | BTREE | | | art | 1 | date |1 | date| A | 542437 | NULL | NULL | YES | BTREE | | | art | 1 | lst |1 | lst| A | 216 | NULL | NULL | YES | BTREE | | | art | 1 | batch|1 | batch | A | 183 | NULL | NULL | YES | BTREE | | | art | 1 | batch|2 | lst| A |1802 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |1 | lst| A | 216 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |2 | parent | A | 90406 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |3 | batch | A | 90406 | NULL | NULL | YES | BTREE | | | art | 1 | parent |1 | parent | A | 90406 | NULL | NULL | YES | BTREE | | | art | 1 | mid|1 | mid | A | 542437 | NULL | NULL | YES | BTREE | | | art | 1 | date_2 |1 | date| A | 542437 | NULL | NULL | YES | BTREE | | | art | 1 | subc |1 | subc | A | 54243 | NULL | NULL | YES | FULLTEXT | | | art | 1 | mf|1 | mf | A | 54243 | NULL | NULL | YES | FULLTEXT | | +++--+--+-+---+-+--++--++-+ 13 rows in set (0.00 sec) mysql Select A.id, A.parent from art A inner join art B using (id) where A.id=560685 or B.parent=560685 order by A.date; +++ | id | parent | +++ | 560685 | 0 | | 560707 | 560685 | | 560714 | 560685 | | 560780 | 560685 | | 560783 | 560685 | | 560802 | 560685 | | 560810 | 560685 | | 560851 | 560685 | | 560855 | 560685 | | 561056 | 560685 | | 561104 | 560685 | +++ 11 rows in set (1 min 12.45 sec) mysql explain Select A.id, A.parent from art A inner join art B using (id) where A.id=560685 or B.parent=560685 order by A.date; +---+++-+-+---+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+++-+-+---+++ | A | ALL| PRIMARY| NULL|NULL | NULL | 542437 | Using filesort | | B | eq_ref | PRIMARY,parent | PRIMARY | 4 | mail_archive.A.id | 1 | Using where| +---+++-+-+---+++ 2 rows in set (0.00 sec) // Fredrik Donny Simonton wrote: Frederik, I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 Get rid of id, id_2, and id_3. You need to do this for everything that you have duplicates of. Next according to your original query, the real query you should try and run should look like this: Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or B.parent=60 order by A.date Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is the difference between 4.1 and MAXDB 7.5?
Could some one enlighten me about the differences between these 2 mysql products? Thanks Juan M. Quiroz Oregon Department of Agriculture Systems Analyst -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What is the difference between 4.1 and MAXDB 7.5?
Hi Juan, MAXDB is a totally different product, more akin to the corporate databases like Oracle, Informix, Sybase etc. A description from the mysql web site ( www.mysql.com ) MaxDB(tm) by MySQL For the most demanding enterprise applications, MySQL offers MaxDB by MySQL. Formerly known as SAP DB, MaxDB is the result of a strategic alliance between MySQL and SAP to jointly develop and market an enterprise-class Open Source database capable of running high-end business-critical applications including SAP/R3. MaxDB complements the MySQL database, and is SAP-certified. It includes features such as stored procedures, triggers and views for the most demanding enterprise use. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Juan M.Quiroz [mailto:[EMAIL PROTECTED] Sent: Tuesday, 4 January 2005 9:49 AM To: mysql@lists.mysql.com Subject: What is the difference between 4.1 and MAXDB 7.5? Could some one enlighten me about the differences between these 2 mysql products? Thanks Juan M. Quiroz Oregon Department of Agriculture Systems Analyst -- 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]
first time accessing
I am making my first attempt to access MySQL with Perl #!/perl use warnings; use strict; use dbi; my $dbh=DBI-connect( 'dbi:MySQL, 'user', 'pass' ) or die Cannot connect - !br$DBI::errstr; and I get the following error: Can't connect to data source dbi:MySQL, no data driver specified and DBS_DSN env var not set Any ideas? Thanks, Jerry
RE: first time accessing
Try something like this: use DBI; my $driver = mysql; my $server = myhost; my $database = mydb; my $url = DBI:$driver:$database:$server; my $user = username; my $pass = password; my $dbh = DBI-connect($url, $user, $pass) || die Couldn't connect to database: . DBI-errstr; Obviously you don't have to make everything a variable, this is just one possibility. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Gerald Preston Sent: Monday, January 03, 2005 7:40 PM To: mysql users Subject: first time accessing I am making my first attempt to access MySQL with Perl #!/perl use warnings; use strict; use dbi; my $dbh=DBI-connect( 'dbi:MySQL, 'user', 'pass' ) or die Cannot connect - !br$DBI::errstr; and I get the following error: Can't connect to data source dbi:MySQL, no data driver specified and DBS_DSN env var not set Any ideas? Thanks, Jerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: first time accessing
Hi Gerald, Check your syntax, I can't see the hostname or database name in your connect statement. I have a statement similar to that below. Note the hostname and database. my %err_handle = ( PrintError = 1, RaiseError = 1 ); $dsn = DBI:mysql:host=$hostname;database=lcscreative;; $dbh = DBI-connect($dsn, 'username', 'password', \%err_handle); Check you installed the DBD::mysql driver? You can get the latest version via CPAN. If you are running *nix, use perl -e shell -MCPAN cpaninstall DBD::mysql --- lots of output --- Read the README as there are several requirements for testing eg. a user and access to a test database. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Gerald Preston [mailto:[EMAIL PROTECTED] Sent: Tuesday, 4 January 2005 12:10 PM To: mysql users Subject: first time accessing I am making my first attempt to access MySQL with Perl #!/perl use warnings; use strict; use dbi; my $dbh=DBI-connect( 'dbi:MySQL, 'user', 'pass' ) or die Cannot connect - !br$DBI::errstr; and I get the following error: Can't connect to data source dbi:MySQL, no data driver specified and DBS_DSN env var not set Any ideas? Thanks, Jerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is the difference between 4.1 and MAXDB 7.5?
Logan, David (SST - Adelaide) wrote: Hi Juan, MAXDB is a totally different product, more akin to the corporate databases like Oracle, Informix, Sybase etc. A description from the mysql web site ( www.mysql.com ) MaxDB(tm) by MySQL For the most demanding enterprise applications, MySQL offers MaxDB by MySQL. Formerly known as SAP DB, MaxDB is the result of a strategic alliance between MySQL and SAP to jointly develop and market an enterprise-class Open Source database capable of running high-end business-critical applications including SAP/R3. MaxDB complements the MySQL database, and is SAP-certified. It includes features such as stored procedures, triggers and views for the most demanding enterprise use. Call me crazy but doesn't having two databases just end up confusing your customers. Actually now you have 3 if you include NDB -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What is the difference between 4.1 and MAXDB 7.5?
Hi Kevin, Lucky its not IBM, they have Cloudscape, DB2, Informix, Unidata, Universe, Redbrick and a few others that I can't remember. Did you say only 2? 8-) Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Kevin A. Burton [mailto:[EMAIL PROTECTED] Sent: Tuesday, 4 January 2005 10:32 AM To: Logan, David (SST - Adelaide) Cc: Juan M.Quiroz; mysql@lists.mysql.com Subject: Re: What is the difference between 4.1 and MAXDB 7.5? Logan, David (SST - Adelaide) wrote: Hi Juan, MAXDB is a totally different product, more akin to the corporate databases like Oracle, Informix, Sybase etc. A description from the mysql web site ( www.mysql.com ) MaxDB(tm) by MySQL For the most demanding enterprise applications, MySQL offers MaxDB by MySQL. Formerly known as SAP DB, MaxDB is the result of a strategic alliance between MySQL and SAP to jointly develop and market an enterprise-class Open Source database capable of running high-end business-critical applications including SAP/R3. MaxDB complements the MySQL database, and is SAP-certified. It includes features such as stored procedures, triggers and views for the most demanding enterprise use. Call me crazy but doesn't having two databases just end up confusing your customers. Actually now you have 3 if you include NDB -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: big table corruption
Thanks Mike, It's hardware raid, but I don't know the manufacturer. I could try to find out but if the problem is the hardware there isn't much I can do about it anyway without turning it into a big project. I'm not really expecting to solve this, I'm just hoping for some advice on what the problem is most likely to be (raid,kernel or mysql) or maybe there is a variable that I can tweak. The thing that makes me think it's mysql is that it always happens to the same table (out of 300 or so). The table it happens to has the most reads by far but not many writes. any other tips are appreciated. thanks, - Mark mos wrote: At 12:37 PM 1/3/2005, you wrote: Hi, I have a big table (900k rows, 200M MYD, 200M MYI) with a fulltext index on it. The table gets corrupted every 1 week or so and I have to repair it. I've tried upgrading to newer versions of mysql 3 times but the problem persists. I think it may have something to do with the raid 1 mirror but I can't really tell. Does anyone have any ideas? Thanks, - Mark Mark, This might help http://www.google.com/search?hl=enq=mysql+raid+corruptionbtnG=Google+Searchmeta= You can also check the groups http://groups-beta.google.com/groups?q=mysql+raid+corruptionstart=10hl=enlr=; You may want to try and narrow down the results by also supplying your Raid hardware/software that you're using. It could also be processor/OS related. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: big table corruption
Random hardware failures will NOT only impact a single file. The only thing that I know that will consistantly fail at the same spot is s/w. On Mon, 03 Jan 2005 16:32:31 -0800, Mark Maggelet [EMAIL PROTECTED] wrote: Thanks Mike, It's hardware raid, but I don't know the manufacturer. I could try to find out but if the problem is the hardware there isn't much I can do about it anyway without turning it into a big project. I'm not really expecting to solve this, I'm just hoping for some advice on what the problem is most likely to be (raid,kernel or mysql) or maybe there is a variable that I can tweak. The thing that makes me think it's mysql is that it always happens to the same table (out of 300 or so). The table it happens to has the most reads by far but not many writes. any other tips are appreciated. thanks, - Mark mos wrote: At 12:37 PM 1/3/2005, you wrote: Hi, I have a big table (900k rows, 200M MYD, 200M MYI) with a fulltext index on it. The table gets corrupted every 1 week or so and I have to repair it. I've tried upgrading to newer versions of mysql 3 times but the problem persists. I think it may have something to do with the raid 1 mirror but I can't really tell. Does anyone have any ideas? Thanks, - Mark Mark, This might help http://www.google.com/search?hl=enq=mysql+raid+corruptionbtnG=Google+Searchmeta= You can also check the groups http://groups-beta.google.com/groups?q=mysql+raid+corruptionstart=10hl=enlr=; You may want to try and narrow down the results by also supplying your Raid hardware/software that you're using. It could also be processor/OS related. Mike -- 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]
sorting doesn't work correctly with UTF-8 data
Hi, I have mysql-4.0.18 installed and entered data with UTF-8 characters. When I use a Select command, mysql doesn't sort the data correctly which starts with native Turkish letters. Is there a way to fix it? Thanks.. -- Erol YILDIZ HEV Schools Kemerkoy http://www.kemerkoy.k12.tr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
