RE: MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject)
Hello, I wanted only to report that I removed and re-added the Index as Martijn suggested and now it's OK. Thanks again for your help Regards, Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject)
Hello! I'm using MySQL 5.0.18-standard Server on a Linux SuSE 10 Server and I've got following problem: I have a table with the followign structure +-+--+--+-+---+- ---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+---+- ---+ | STEP_ID | int(10) unsigned | NO | PRI | NULL | auto_increment | | INVOICE_ID | int(10) unsigned | NO | MUL | 0 | | | STEP_TYPE_ID| smallint(5) unsigned | NO | MUL | 0 | | (some other field) ... +-+--+--+-+---+- ---+ When I execute following statement SELECT STEP_ID, INVOICE_ID, STEP_TYPE_ID FROM step WHERE INVOICE_ID=17081598; I get this result +--++--+ | STEP_ID | INVOICE_ID | STEP_TYPE_ID | +--++--+ | 47870211 | 17081598 |1 | | 47870212 | 17081598 |4 | | 47870214 | 17081599 |1 | !! +--++--+ As you can see there is a record with INVOICE_ID=17081599. Please note that if I ask for mysql SELECT STEP_ID, INVOICE_ID, STEP_TYPE_ID FROM step WHERE INVOICE_ID=17081599; I also receive that record: +--++--+ | STEP_ID | INVOICE_ID | STEP_TYPE_ID | +--++--+ | 47870214 | 17081599 |1 | | 47870215 | 17081599 |4 | | 47870216 | 17081599 |3 | +--++--+ Interesting is also that no record with STEP_ID=47870213 is visible. I wrote visible and not present, because if I try to insert a new Record with this PK I get this error mysql INSERT INTO step (STEP_ID, INVOICE_ID, STEP_TYPE_ID) VALUES (47870213, 17081598, 3); ERROR 1062 (23000): Duplicate entry '47870213' for key 1 However: mysql SELECT * FROM step where STEP_ID=47870213; Empty set (0.00 sec) The problem for me is that I also collect a statistic from this come out wrong, because when I ask for mysql SELECT COUNT(*) FROM step WHERE INVOICE_ID IN (17081598,17081599) AND STEP_TYPE_ID=1; I get, instead of 2: +--+ | COUNT(*) | +--+ |3 | +--+ Can you please tell me what the problem could be and what can I do to solve it? Thanks a lot! Regards, Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject)
Thanks Martijn for you prompt answer! Do you have indices on INVOICE_ID? Yes (KEY `IDX_step1` (`INVOICE_ID`)) If so, try dropping it and recreating it? OK, I will do it (I will have to wait until this week-end to do it (= the table is a bit big (47 mio records) and I cannot stop the service during the week) and tell you the results. Shall also perform a REPAIR TABLE? Regards, Patrick -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, 04 April 2006 10:34 To: Patrick Herber; mysql@lists.mysql.com Subject: Re: MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject) Patrick, I'm using MySQL 5.0.18-standard Server on a Linux SuSE 10 Server and I've got following problem: I have a table with the followign structure +-+--+--+-+--- +- +-+--+--+-+--- + ---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+--- +- +-+--+--+-+--- + ---+ | STEP_ID | int(10) unsigned | NO | PRI | NULL | auto_increment | | INVOICE_ID | int(10) unsigned | NO | MUL | 0 | | | STEP_TYPE_ID| smallint(5) unsigned | NO | MUL | 0 | | (some other field) ... +-+--+--+-+--- +- +-+--+--+-+--- + ---+ When I execute following statement SELECT STEP_ID, INVOICE_ID, STEP_TYPE_ID FROM step WHERE INVOICE_ID=17081598; I get this result +--++--+ | STEP_ID | INVOICE_ID | STEP_TYPE_ID | +--++--+ | 47870211 | 17081598 |1 | | 47870212 | 17081598 |4 | | 47870214 | 17081599 |1 | !! +--++--+ As you can see there is a record with INVOICE_ID=17081599. Please note that if I ask for mysql SELECT STEP_ID, INVOICE_ID, STEP_TYPE_ID FROM step WHERE INVOICE_ID=17081599; I also receive that record: +--++--+ | STEP_ID | INVOICE_ID | STEP_TYPE_ID | +--++--+ | 47870214 | 17081599 |1 | | 47870215 | 17081599 |4 | | 47870216 | 17081599 |3 | +--++--+ Interesting is also that no record with STEP_ID=47870213 is visible. I wrote visible and not present, because if I try to insert a new Record with this PK I get this error mysql INSERT INTO step (STEP_ID, INVOICE_ID, STEP_TYPE_ID) VALUES (47870213, 17081598, 3); ERROR 1062 (23000): Duplicate entry '47870213' for key 1 However: mysql SELECT * FROM step where STEP_ID=47870213; Empty set (0.00 sec) The problem for me is that I also collect a statistic from this come out wrong, because when I ask for mysql SELECT COUNT(*) FROM step WHERE INVOICE_ID IN mysql (17081598,17081599) AND STEP_TYPE_ID=1; I get, instead of 2: +--+ | COUNT(*) | +--+ |3 | +--+ Can you please tell me what the problem could be and what can I do to solve it? Corrupt index? Do you have indices on INVOICE_ID? If so, try dropping it and recreating it? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Group By over many colums
I would suggest a union SELECT name, count(*) FROM (SELECT name1 as name from mytable union select name2 as name from mytable union select name3 as name from table) GROUP BY name but perhaps there's a better way... Regards, Patrick -Original Message- From: Critters [mailto:[EMAIL PROTECTED] Sent: Thursday, 19 January 2006 16:17 To: mysql@lists.mysql.com Subject: Group By over many colums Hi I have a table setup like this: id, name1, name2, name3 Which has data like this: 1, Dave, Bob, Simon 2, Joe, Tim, Dave 3, Dave, Bob, Tom I can run SELECT name, count(id) FROM tablename GROUP BY name1 ORDER BY count(id) DESC Which would give me: Dave, 2 Joe, 1 But how would I go about getting the following result: Dave, 3 Bob, 2 Tom, 2 Joe, 1 Simon, 1 Where it groups by name1, name2 and name3? Is it possible? - David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: doing mulitple quires in one go.
Hi, Do you mean you have such a structure Table A ID_a ID_b ID_c ... Table B ID_b Value_b ... Table C ID_c Value_c ... ? In that case you can SELECT Value_b, Value_c FROM A LEFT JOIN B on A.ID_b=B.ID_b LEFT JOIN C on A.ID_c=C.ID_c WHERE ID_a=xxx Regards, Patrick -Original Message- From: Gregory Machin [mailto:[EMAIL PROTECTED] Sent: Monday, 16 January 2006 09:14 To: mysql@lists.mysql.com Subject: doing mulitple quires in one go. Hi. I'm in need of advise . I have a table that in 2 columns holds id values to data in two other tables. At the moment I do three quieries. One to get the primary data then use the to id valuse to quiery the other two table to get the required valuse.. Is there a way I can get mysql to return all the data in one quiery ? Many thanks -- Gregory Machin [EMAIL PROTECTED] [EMAIL PROTECTED] www.linuxpro.co.za www.exponent.co.za Web Hosting Solutions Scalable Linux Solutions www.iberry.info (support and admin) +27 72 524 8096 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE Date Column
Do you mean something like that? UPDATE tablename SET date2=DATE_ADD(date1, INTERVAL -3 MONTH) Regards, Patrick -Original Message- From: Shaun [mailto:[EMAIL PROTECTED] Sent: Monday, 16 January 2006 15:27 To: mysql@lists.mysql.com Subject: UPDATE Date Column Hi, I have a 2 Date Columns in my table, how can I update the 2nd date column so that the dates are 3 months previous to the orignial date column for every row. Thanks for your advice -- 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]
ERROR 1114 (HY000): The table is full converting a big table from MyISAM to InnoDB on 5.0.18
Hello! I have a database with a big table (Data File 45 GB, Index File 30 GB). Since I have some performance troubles with table-locking in a multi-user environment (when one of them performs a complex query all the other have to wait up to 1 minute, which is not very nice...), I would like to convert this (and other tables) into InnoDB engine. I first tried using the innodb_file_per_table option but when running the statement ALTER TABLE invoice ENGINE=INNODB; ERROR 1114 (HY000): The table '#sql...' is full (this about one our after the start of the command, when the size of the file was bigger than ca. 70GB (I don't know exactly the size)) I tried then without the innodb_file_per_table option, setting my innodb_data_file_path as follows: innodb_data_file_path=ibdata1:500M;ibdata2:500M;ibdata3;500M;ibdata4:500M;ib data5:500M;ibdata6:500M;ibdata7:500M;ibdata8:500M;ibdata9:500M;ibdata10:500M :autoextend Also in this case I got the same error message. What should I do in order to convert this table? Should I set in the innodb_data_file_path for example 50 Files, each big 4GB ? Thanks a lot for your help. Best regards, Patrick PS: I'm running MySQL 5.0.18 on a Linux 2.6.13-15.7-smp server.
RE: ERROR 1114 (HY000): The table is full converting a big table from MyISAM to InnoDB on 5.0.18
Thanks a lot for your answer! However, when I used the option innodb_file_per_table I saw that the temp file (#sql...) was created in my DB directory and on this partition I still have plenty of space (more than 200GB). Do you think I CAN'T use this option for such a big table and I have to use innodb_data_file_path? Thanks a lot and regards, Patrick -Original Message- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED] Sent: Sunday, 15 January 2006 15:09 To: Patrick Herber Cc: mysql@lists.mysql.com Subject: Re: ERROR 1114 (HY000): The table is full converting a big table from MyISAM to InnoDB on 5.0.18 Hi, I think you should change the tmpdir variable value to a directory which have enough room to create your temp big table (by default, it points to /tmp dir). Regards, Jocelyn Patrick Herber a écrit : Hello! I have a database with a big table (Data File 45 GB, Index File 30 GB). Since I have some performance troubles with table-locking in a multi-user environment (when one of them performs a complex query all the other have to wait up to 1 minute, which is not very nice...), I would like to convert this (and other tables) into InnoDB engine. I first tried using the innodb_file_per_table option but when running the statement ALTER TABLE invoice ENGINE=INNODB; ERROR 1114 (HY000): The table '#sql...' is full (this about one our after the start of the command, when the size of the file was bigger than ca. 70GB (I don't know exactly the size)) I tried then without the innodb_file_per_table option, setting my innodb_data_file_path as follows: innodb_data_file_path=ibdata1:500M;ibdata2:500M;ibdata3;500M;ibdata4:5 00M;ib data5:500M;ibdata6:500M;ibdata7:500M;ibdata8:500M;ibdata9:500M;ibdata1 0:500M :autoextend Also in this case I got the same error message. What should I do in order to convert this table? Should I set in the innodb_data_file_path for example 50 Files, each big 4GB ? Thanks a lot for your help. Best regards, Patrick PS: I'm running MySQL 5.0.18 on a Linux 2.6.13-15.7-smp server. -- 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]
