Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
So, as a followup, I ran mysqldump on the actual server (with the output directed over AFP to another machine on the network) -- as opposed to running mysqldump on the destination server and connecting to the databases over TCP/IP (both are running 5.1.39 PPC 64-bit). *That* dump file imported OK. So... It appears there's a bug in mysqldump that occurs intermittently when using TCP/IP connections to the MySQL database server. I haven't seen this bug occur on smaller databases, but it was pretty consistent on my non-damaged database of 9GB+. Anyway, for now I'm going to run automysqlbackup on the actual machine and simply dump to an external drive or another server on the network. On 2009-11-20, at 12:58 PM, René Fournier wrote: > Yes, still exhibits this problem -- although at a different line in the file. > (Seems random.) I should also mention, the backup is running across a local > network. The machine the starts automysqlbackup (and internally, mysqldump) > connects over TCP/IP to the database server. I am going to try running > mysqldump on the database server itself, but it's not something I can do > during normal hours -- plus at 10GB, it's a time-consuming thing to test. > (Would it matter? I guess I need to find out.)
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
Except that, in my case, both machines are running 5.1.39. Mac "OS X" PPC 64-bit. I tried importing another, different dump file of the same database, and this time mysql returned a different error: Sat Nov 21 13:16:20 -- Minas-Tirith :: mysql -u root -p < dump_file.sql Enter password: ERROR 1136 (21S01) at line 6300: Column count doesn't match value count at row 6253 So, a different error. And it appears that each time mysqldump runs, the resulting file has different problem. On 2009-11-21, at 5:05 PM, Claudio Nanni wrote: > There is a bug in mysqldump that prevents mysql from version 5.0.80 from > importing dumps from a old version of mysqldump. > I had this verified by MySQL support. > You can have a look at this bug: http://bugs.mysql.com/bug.php?id=41486 > > Cheers > > Claudio > > > > 2009/11/21 René Fournier > >> It's just odd, because that error was on 5.0. I'm running 5.1.39 -- surely >> enough time for the MySQL devs to fix it right? >> >> Could it be with extended inserts that the max statement length is greater >> than the default 16MB max allowed packet? I'm increasing that value to 256 >> MB and going to try again. >> >> Thanks for the idea though. At least I'm not the only one to have >> encountered that error. >> >> ...Rene >> >> On 2009-11-21, at 5:44 AM, 王浩 wrote: >> >>> i googled the problem,it says that's a mysql bug.This is the source page >> :http://www.shinguz.ch/MySQL/mysql_trouble_shooting.html#backup_restore, >>> And i suggest you ignore the error when import or correct the dump to the >> source database and then import again. >>> >>> Best wishes! >> >> > > > -- > Claudio
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
There is a bug in mysqldump that prevents mysql from version 5.0.80 from importing dumps from a old version of mysqldump. I had this verified by MySQL support. You can have a look at this bug: http://bugs.mysql.com/bug.php?id=41486 Cheers Claudio 2009/11/21 René Fournier > It's just odd, because that error was on 5.0. I'm running 5.1.39 -- surely > enough time for the MySQL devs to fix it right? > > Could it be with extended inserts that the max statement length is greater > than the default 16MB max allowed packet? I'm increasing that value to 256 > MB and going to try again. > > Thanks for the idea though. At least I'm not the only one to have > encountered that error. > > ...Rene > > On 2009-11-21, at 5:44 AM, 王浩 wrote: > > > i googled the problem,it says that's a mysql bug.This is the source page > :http://www.shinguz.ch/MySQL/mysql_trouble_shooting.html#backup_restore, > > And i suggest you ignore the error when import or correct the dump to the > source database and then import again. > > > > Best wishes! > > -- Claudio
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
It's just odd, because that error was on 5.0. I'm running 5.1.39 -- surely enough time for the MySQL devs to fix it right? Could it be with extended inserts that the max statement length is greater than the default 16MB max allowed packet? I'm increasing that value to 256 MB and going to try again. Thanks for the idea though. At least I'm not the only one to have encountered that error. ...Rene On 2009-11-21, at 5:44 AM, 王浩 wrote: > i googled the problem,it says that's a mysql bug.This is the source page > :http://www.shinguz.ch/MySQL/mysql_trouble_shooting.html#backup_restore, > And i suggest you ignore the error when import or correct the dump to the > source database and then import again. > > Best wishes!
Re: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
Yes, still exhibits this problem -- although at a different line in the file. (Seems random.) I should also mention, the backup is running across a local network. The machine the starts automysqlbackup (and internally, mysqldump) connects over TCP/IP to the database server. I am going to try running mysqldump on the database server itself, but it's not something I can do during normal hours -- plus at 10GB, it's a time-consuming thing to test. (Would it matter? I guess I need to find out.) In any case, it seems clear that during the dump, mysqldump is periodically adding a character to the dump file. On 2009-11-20, at 12:41 PM, Gavin Towey wrote: > Have you tried dumping that table manually using mysqldump on the command > line to confirm it's not an issue with automysqlbackup? > > Regards, > Gavin Towey > > -Original Message- > From: René Fournier [mailto:m...@renefournier.com] > Sent: Friday, November 20, 2009 8:31 AM > To: mysql > Subject: Strange problem with mysqldump / automysqlbackup (ERROR 1300) > > I've been using automysqlbackup 2.5 for years on a particular database, and > it's always performed great. Recently, however, I've become encountering > problems when trying to re-import one of its dumped sql files. (Not sure if > it matters, but the database file in question is large and growing -- about > 10GB. The other databases automysqlbackup backs up are fine.) > > Basically on the import, MySQL fails and returns an error indicating a > problem with the dump file: > > mysql -u root -p < dump_file.sql (~10GB) > Enter password: > ERROR 1300 (HY000) at line 426: Invalid utf8 character string: '?03422' > > Sure enough, I look at the line in dump_file.sql, which should contain two > unsigned ints, and two unsigned small ints: > >[...],(32562206,1228?03422,1641,135),[...] > > And yup, there's a question mark in the middle of the second unsigned int, > for some strange reason. Not in any of the other rows in that statement. When > I look at the existing database from which the dump file was made, that row > is fine: > > mysql> SELECT * FROM bandwidth WHERE id = 32562206; > +--++---+---+ > | id | time_sec | device_id | bytes | > +--++---+---+ > | 32562206 | 1228803422 | 1641 | 135 | > +--++---+---+ > 1 row in set (0.00 sec) > > > So... It appears either mysqldump and/or automysqlbackup is having a problem > dumping a true copy of the database. > > Anyone else run into this sort of thing? Any suggestions? Thanks. > > ...Rene > > The information contained in this transmission may contain privileged and > confidential information. It is intended only for the use of the person(s) > named above. If you are not the intended recipient, you are hereby notified > that any review, dissemination, distribution or duplication of this > communication is strictly prohibited. If you are not the intended recipient, > please contact the sender by reply email and destroy all copies of the > original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Strange problem with mysqldump / automysqlbackup (ERROR 1300)
Have you tried dumping that table manually using mysqldump on the command line to confirm it's not an issue with automysqlbackup? Regards, Gavin Towey -Original Message- From: René Fournier [mailto:m...@renefournier.com] Sent: Friday, November 20, 2009 8:31 AM To: mysql Subject: Strange problem with mysqldump / automysqlbackup (ERROR 1300) I've been using automysqlbackup 2.5 for years on a particular database, and it's always performed great. Recently, however, I've become encountering problems when trying to re-import one of its dumped sql files. (Not sure if it matters, but the database file in question is large and growing -- about 10GB. The other databases automysqlbackup backs up are fine.) Basically on the import, MySQL fails and returns an error indicating a problem with the dump file: mysql -u root -p < dump_file.sql (~10GB) Enter password: ERROR 1300 (HY000) at line 426: Invalid utf8 character string: '?03422' Sure enough, I look at the line in dump_file.sql, which should contain two unsigned ints, and two unsigned small ints: [...],(32562206,1228?03422,1641,135),[...] And yup, there's a question mark in the middle of the second unsigned int, for some strange reason. Not in any of the other rows in that statement. When I look at the existing database from which the dump file was made, that row is fine: mysql> SELECT * FROM bandwidth WHERE id = 32562206; +--++---+---+ | id | time_sec | device_id | bytes | +--++---+---+ | 32562206 | 1228803422 | 1641 | 135 | +--++---+---+ 1 row in set (0.00 sec) So... It appears either mysqldump and/or automysqlbackup is having a problem dumping a true copy of the database. Anyone else run into this sort of thing? Any suggestions? Thanks. ...Rene The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Strange problem with mysqldump / automysqlbackup (ERROR 1300)
I've been using automysqlbackup 2.5 for years on a particular database, and it's always performed great. Recently, however, I've become encountering problems when trying to re-import one of its dumped sql files. (Not sure if it matters, but the database file in question is large and growing -- about 10GB. The other databases automysqlbackup backs up are fine.) Basically on the import, MySQL fails and returns an error indicating a problem with the dump file: mysql -u root -p < dump_file.sql (~10GB) Enter password: ERROR 1300 (HY000) at line 426: Invalid utf8 character string: '?03422' Sure enough, I look at the line in dump_file.sql, which should contain two unsigned ints, and two unsigned small ints: [...],(32562206,1228?03422,1641,135),[...] And yup, there's a question mark in the middle of the second unsigned int, for some strange reason. Not in any of the other rows in that statement. When I look at the existing database from which the dump file was made, that row is fine: mysql> SELECT * FROM bandwidth WHERE id = 32562206; +--++---+---+ | id | time_sec | device_id | bytes | +--++---+---+ | 32562206 | 1228803422 | 1641 | 135 | +--++---+---+ 1 row in set (0.00 sec) So... It appears either mysqldump and/or automysqlbackup is having a problem dumping a true copy of the database. Anyone else run into this sort of thing? Any suggestions? Thanks. ...Rene
Re: a strange problem
liaojian_163 schrieb: > thank you Sebastian! > I have re-created the table.there are not any problems in the table. > > if the table is new,need to rebuild de indexes? no -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a strange problem
thank you Sebastian! I have re-created the table.there are not any problems in the table. if the table is new,need to rebuild de indexes? - Original Message - From: "Sebastian Mendel" <[EMAIL PROTECTED]> To: "liaojian_163" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, April 23, 2008 2:11 PM Subject: Re: a strange problem > liaojian_163 schrieb: >> hi,all. >> In my mysql server,I have a strange problem. >> can someone help me? >> Thank you. >> >> mysql> select id,classid,newstime from phome_ecms_zhichang where classid=41 >> and id >2500 order by id desc limit 10; >> +--+-+-+ >> | id | classid | newstime| >> +--+-+-+ >> | 2543 | 41 | 2008-04-22 21:55:22 | >> [...] >> 10 rows in set (0.00 sec) >> >> mysql> select id,classid,newstime from phome_ecms_zhichang where classid=41 >> order by id desc limit 10; >> +--+-+-+ >> | id | classid | newstime| >> +--+-+-+ >> | 2540 | 41 | 2008-04-19 12:29:30 | >> [...] >> >> >> mysql> select id,classid,newstime from phome_ecms_zhichang where classid=41 >> and id >0 order by id desc limit 10; >> Empty set (0.00 sec) > > did you tried to repair the table and/or rebuild the indexes? > > > -- > Sebastian Mendel > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Re: a strange problem
liaojian_163 schrieb: > hi,all. > In my mysql server,I have a strange problem. > can someone help me? > Thank you. > > mysql> select id,classid,newstime from phome_ecms_zhichang where classid=41 > and id >2500 order by id desc limit 10; > +--+-+-+ > | id | classid | newstime| > +--+-+-+ > | 2543 | 41 | 2008-04-22 21:55:22 | > [...] > 10 rows in set (0.00 sec) > > mysql> select id,classid,newstime from phome_ecms_zhichang where classid=41 > order by id desc limit 10; > +--+-+-+ > | id | classid | newstime| > +--+-+-+ > | 2540 | 41 | 2008-04-19 12:29:30 | > [...] > > > mysql> select id,classid,newstime from phome_ecms_zhichang where classid=41 > and id >0 order by id desc limit 10; > Empty set (0.00 sec) did you tried to repair the table and/or rebuild the indexes? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a strange problem
I don't think that the limit cause the stange problem. thank you anyway. - Original Message - From: "Phil" <[EMAIL PROTECTED]> To: "liaojian_163" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, April 23, 2008 3:46 AM Subject: Re: a strange problem > Not knowing your msqyl version, perhaps it's the form of your LIMIT clause. > > try LIMIT 0,10 instead. > > Phil > > 2008/4/22 liaojian_163 <[EMAIL PROTECTED]>: > >> hi,all. >> In my mysql server,I have a strange problem. >> can someone help me? >> Thank you. >> >> mysql> select id,classid,newstime from phome_ecms_zhichang where >> classid=41 and id >2500 order by id desc limit 10; >> +--+-+-+ >> | id | classid | newstime| >> +--+-+-+ >> | 2543 | 41 | 2008-04-22 21:55:22 | >> | 2540 | 41 | 2008-04-19 12:29:30 | >> | 2537 | 41 | 2008-04-18 17:38:50 | >> | 2536 | 41 | 2008-04-18 17:37:56 | >> | 2534 | 41 | 2008-04-18 12:22:24 | >> | 2533 | 41 | 2008-04-18 01:19:49 | >> | 2532 | 41 | 2008-04-18 01:18:42 | >> | 2527 | 41 | 2008-04-16 18:45:34 | >> | 2526 | 41 | 2008-04-16 18:43:03 | >> | 2523 | 41 | 2008-04-16 08:47:16 | >> +--+-+-+ >> 10 rows in set (0.00 sec) >> >> mysql> select id,classid,newstime from phome_ecms_zhichang where >> classid=41 order by id desc limit 10; >> +--+-+-+ >> | id | classid | newstime| >> +--+-+-+ >> | 2540 | 41 | 2008-04-19 12:29:30 | >> | 2537 | 41 | 2008-04-18 17:38:50 | >> | 2536 | 41 | 2008-04-18 17:37:56 | >> | 2534 | 41 | 2008-04-18 12:22:24 | >> | 2533 | 41 | 2008-04-18 01:19:49 | >> | 2532 | 41 | 2008-04-18 01:18:42 | >> | 2527 | 41 | 2008-04-16 18:45:34 | >> | 2526 | 41 | 2008-04-16 18:43:03 | >> | 2523 | 41 | 2008-04-16 08:47:16 | >> | 2522 | 41 | 2008-04-15 15:34:55 | >> +--+-+-+ >> >> >> mysql> select id,classid,newstime from phome_ecms_zhichang where >> classid=41 and id >0 order by id desc limit 10; >> Empty set (0.00 sec) >> >> >> desc phome_ecms_zhichang; >> >> +-+--+--+-+-++ >> | Field | Type | Null | Key | Default | Extra >>| >> >> +-+--+--+-+-++ >> | id | int(11) | NO | PRI | NULL| >> auto_increment | >> | classid | smallint(6) | NO | MUL | 0 | >>| >> | onclick | int(11) | NO | | 0 | >>| >> | newspath| varchar(50) | NO | | | >>| >> | keyboard| varchar(255) | NO | | | >>| >> | keyid | varchar(255) | NO | | | >>| >> | userid | int(11) | NO | | 0 | >>| >> | username| varchar(30) | NO | | | >>| >> | ztid| varchar(255) | NO | | | >>| >> | checked | tinyint(1) | NO | MUL | 0 | >>| >> | istop | tinyint(4) | NO | | 0 | >>| >> | truetime| int(11) | NO | MUL | 0 | >>| >> | ismember| tinyint(1) | NO | | 0 | >>| >> | dokey | tinyint(1) | NO | | 0 | >>| >> | userfen | int(11) | NO | | 0 | >>| >> | isgood | tinyint(1) | NO | | 0 | >>| >> | titlefont | varchar(50) | NO | | | >>| >> | titleurl| varchar(200) | NO | | | >>| >> | filename| varchar(60) | NO | | | >>| >> | filenameqz | varchar(28) | NO | | | >>| >> | fh | tinyint(1) | NO | | 0 | >>| >> | groupid | smallint(6) | NO | | 0 | >>| >> | newstempid | sm
Re: a strange problem
Not knowing your msqyl version, perhaps it's the form of your LIMIT clause. try LIMIT 0,10 instead. Phil 2008/4/22 liaojian_163 <[EMAIL PROTECTED]>: > hi,all. > In my mysql server,I have a strange problem. > can someone help me? > Thank you. > > mysql> select id,classid,newstime from phome_ecms_zhichang where > classid=41 and id >2500 order by id desc limit 10; > +--+-+-+ > | id | classid | newstime| > +--+-+-+ > | 2543 | 41 | 2008-04-22 21:55:22 | > | 2540 | 41 | 2008-04-19 12:29:30 | > | 2537 | 41 | 2008-04-18 17:38:50 | > | 2536 | 41 | 2008-04-18 17:37:56 | > | 2534 | 41 | 2008-04-18 12:22:24 | > | 2533 | 41 | 2008-04-18 01:19:49 | > | 2532 | 41 | 2008-04-18 01:18:42 | > | 2527 | 41 | 2008-04-16 18:45:34 | > | 2526 | 41 | 2008-04-16 18:43:03 | > | 2523 | 41 | 2008-04-16 08:47:16 | > +--+-+-+ > 10 rows in set (0.00 sec) > > mysql> select id,classid,newstime from phome_ecms_zhichang where > classid=41 order by id desc limit 10; > +--+-+-+ > | id | classid | newstime| > +--+-+-+ > | 2540 | 41 | 2008-04-19 12:29:30 | > | 2537 | 41 | 2008-04-18 17:38:50 | > | 2536 | 41 | 2008-04-18 17:37:56 | > | 2534 | 41 | 2008-04-18 12:22:24 | > | 2533 | 41 | 2008-04-18 01:19:49 | > | 2532 | 41 | 2008-04-18 01:18:42 | > | 2527 | 41 | 2008-04-16 18:45:34 | > | 2526 | 41 | 2008-04-16 18:43:03 | > | 2523 | 41 | 2008-04-16 08:47:16 | > | 2522 | 41 | 2008-04-15 15:34:55 | > +--+-+-+ > > > mysql> select id,classid,newstime from phome_ecms_zhichang where > classid=41 and id >0 order by id desc limit 10; > Empty set (0.00 sec) > > > desc phome_ecms_zhichang; > > +-+--+--+-+-++ > | Field | Type | Null | Key | Default | Extra >| > > +-+--+--+-+-++ > | id | int(11) | NO | PRI | NULL| > auto_increment | > | classid | smallint(6) | NO | MUL | 0 | >| > | onclick | int(11) | NO | | 0 | >| > | newspath| varchar(50) | NO | | | >| > | keyboard| varchar(255) | NO | | | >| > | keyid | varchar(255) | NO | | | >| > | userid | int(11) | NO | | 0 | >| > | username| varchar(30) | NO | | | >| > | ztid| varchar(255) | NO | | | >| > | checked | tinyint(1) | NO | MUL | 0 | >| > | istop | tinyint(4) | NO | | 0 | >| > | truetime| int(11) | NO | MUL | 0 | >| > | ismember| tinyint(1) | NO | | 0 | >| > | dokey | tinyint(1) | NO | | 0 | >| > | userfen | int(11) | NO | | 0 | >| > | isgood | tinyint(1) | NO | | 0 | >| > | titlefont | varchar(50) | NO | | | >| > | titleurl| varchar(200) | NO | | | >| > | filename| varchar(60) | NO | | | >| > | filenameqz | varchar(28) | NO | | | >| > | fh | tinyint(1) | NO | | 0 | >| > | groupid | smallint(6) | NO | | 0 | >| > | newstempid | smallint(6) | NO | | 0 | >| > | plnum | int(11) | NO | | 0 | >| > | firsttitle | tinyint(1) | NO | | 0 | >| > | isqf| tinyint(1) | NO | | 0 | >| > | totaldown | int(11) | NO | | 0 | >| > | title | varchar(200) | NO | | | >| > | newstime| datetime | NO | MUL | -00-00 00:00:00 | >| > | titlepic| varchar(200) | NO | | | >| > | closepl | tinyint(1) | NO | | 0 | >
a strange problem
hi,all. In my mysql server,I have a strange problem. can someone help me? Thank you. mysql> select id,classid,newstime from phome_ecms_zhichang where classid=41 and id >2500 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2543 | 41 | 2008-04-22 21:55:22 | | 2540 | 41 | 2008-04-19 12:29:30 | | 2537 | 41 | 2008-04-18 17:38:50 | | 2536 | 41 | 2008-04-18 17:37:56 | | 2534 | 41 | 2008-04-18 12:22:24 | | 2533 | 41 | 2008-04-18 01:19:49 | | 2532 | 41 | 2008-04-18 01:18:42 | | 2527 | 41 | 2008-04-16 18:45:34 | | 2526 | 41 | 2008-04-16 18:43:03 | | 2523 | 41 | 2008-04-16 08:47:16 | +--+-+-+ 10 rows in set (0.00 sec) mysql> select id,classid,newstime from phome_ecms_zhichang where classid=41 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2540 | 41 | 2008-04-19 12:29:30 | | 2537 | 41 | 2008-04-18 17:38:50 | | 2536 | 41 | 2008-04-18 17:37:56 | | 2534 | 41 | 2008-04-18 12:22:24 | | 2533 | 41 | 2008-04-18 01:19:49 | | 2532 | 41 | 2008-04-18 01:18:42 | | 2527 | 41 | 2008-04-16 18:45:34 | | 2526 | 41 | 2008-04-16 18:43:03 | | 2523 | 41 | 2008-04-16 08:47:16 | | 2522 | 41 | 2008-04-15 15:34:55 | +--+-+-+ mysql> select id,classid,newstime from phome_ecms_zhichang where classid=41 and id >0 order by id desc limit 10; Empty set (0.00 sec) desc phome_ecms_zhichang; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | classid | smallint(6) | NO | MUL | 0 | | | onclick | int(11) | NO | | 0 | | | newspath| varchar(50) | NO | | | | | keyboard| varchar(255) | NO | | | | | keyid | varchar(255) | NO | | | | | userid | int(11) | NO | | 0 | | | username| varchar(30) | NO | | | | | ztid| varchar(255) | NO | | | | | checked | tinyint(1) | NO | MUL | 0 | | | istop | tinyint(4) | NO | | 0 | | | truetime| int(11) | NO | MUL | 0 | | | ismember| tinyint(1) | NO | | 0 | | | dokey | tinyint(1) | NO | | 0 | | | userfen | int(11) | NO | | 0 | | | isgood | tinyint(1) | NO | | 0 | | | titlefont | varchar(50) | NO | | | | | titleurl| varchar(200) | NO | | | | | filename| varchar(60) | NO | | | | | filenameqz | varchar(28) | NO | | | | | fh | tinyint(1) | NO | | 0 | | | groupid | smallint(6) | NO | | 0 | | | newstempid | smallint(6) | NO | | 0 | | | plnum | int(11) | NO | | 0 | | | firsttitle | tinyint(1) | NO | | 0 | | | isqf| tinyint(1) | NO | | 0 | | | totaldown | int(11) | NO | | 0 | | | title | varchar(200) | NO | | | | | newstime| datetime | NO | MUL | -00-00 00:00:00 | | | titlepic| varchar(200) | NO | | | | | closepl | tinyint(1) | NO | | 0 | | | havehtml| tinyint(1) | NO | | 0 | | | lastdotime | int(11) | NO | | 0 | | | haveaddfen | tinyint(1) | NO | | 0 | | | infopfen| int(11) | NO | | 0 | | | infopfennum | int(11) | NO | | 0 |
Re: Who know this strange problem
This is correct.Thank you very much. But I also why I wrote is wrong on linux and right on windows. You can tell me answer if you have time.thanks. On Jan 19, 2008 12:47 AM, Baron Schwartz <[EMAIL PROTECTED]> wrote: > Hi, > > On Jan 18, 2008 3:50 AM, Moon's Father <[EMAIL PROTECTED]> wrote: > > My test is as follows: > > mysql> select g_id from t group by g_id order by g_id desc limit 0,1; > > +--+ > > | g_id | > > +--+ > > |6 | > > +--+ > > 1 row in set (0.00 sec) > > > > mysql> select g_id from t group by g_id order by g_id desc limit 0,1 > into > > @tmp_id; > > Query OK, 1 row affected (0.00 sec) > > > > mysql> select @tmp_id; > > +-+ > > | @tmp_id | > > +-+ > > | 5 | > > +-+ > > Try it this way: > > mysql> select @tmp_id := g_id from t group by g_id order by g_id desc > limit 0,1; > +-+ > | @tmp_id := g_id | > +-+ > | 6 | > +-+ > 1 row in set (0.00 sec) > > mysql> select @tmp_id; > +-+ > | @tmp_id | > +-+ > | 6 | > +-+ > > This seems like a bug to me. You should file a bug report at > bugs.mysql.com. > > > 1 row in set (0.00 sec) > > > > mysql> select version(); > > +-+ > > | version() | > > +-+ > > | 5.1.21-beta-log | > > +-+ > > 1 row in set (0.00 sec) > > > > mysql> > > > > > > But the variabe @tmp_id 's value is 5; > > Any help is appreciated. > > > > Here is my test data. > > > > DROP TABLE IF EXISTS `t`; > > > > CREATE TABLE `t` ( > > `id` int(11) NOT NULL auto_increment, > > `g_id` int(11) NOT NULL, > > `t_str` varchar(255) NOT NULL, > > PRIMARY KEY (`id`), > > key (`g_id`) > > ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8; > > > > /*Data for the table `t` */ > > > > insert into `t`(`id`,`g_id`,`t_str`) values > > (1,2,'wo'), > > (2,2,'ni'), > > (3,2,'ta'), > > (4,3,'wo '), > > (5,4,'ni'), > > (6,3,'ni'), > > (7,4,'ta'), > > (8,3,'wang'), > > (9,4,'li'), > > (10,3,'hai'), > > (11,4,'ri'), > > (12,2,'ren'), > > (13,5,'ta'), > > (14,6,'ri'), > > (15,6,'ren'), > > (16,6,'fuck'), > > (17,6,'shit'), > > (18,5,'ls'), > > (19,5,'chmod'), > > (20,5,'chgrp'), > > (21,5,'chown'), > > (22,3,'rm'), > > (23,3,'desc'), > > (24,4,'pwd'), > > (25,5,'cd'); > > > > > > -- > > I'm a mysql DBA in china. > > More about me just visit here: > > http://yueliangdao0608.cublog.cn > > > -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Who know this strange problem
Hi, On Jan 18, 2008 3:50 AM, Moon's Father <[EMAIL PROTECTED]> wrote: > My test is as follows: > mysql> select g_id from t group by g_id order by g_id desc limit 0,1; > +--+ > | g_id | > +--+ > |6 | > +--+ > 1 row in set (0.00 sec) > > mysql> select g_id from t group by g_id order by g_id desc limit 0,1 into > @tmp_id; > Query OK, 1 row affected (0.00 sec) > > mysql> select @tmp_id; > +-+ > | @tmp_id | > +-+ > | 5 | > +-+ Try it this way: mysql> select @tmp_id := g_id from t group by g_id order by g_id desc limit 0,1; +-+ | @tmp_id := g_id | +-+ | 6 | +-+ 1 row in set (0.00 sec) mysql> select @tmp_id; +-+ | @tmp_id | +-+ | 6 | +-+ This seems like a bug to me. You should file a bug report at bugs.mysql.com. > 1 row in set (0.00 sec) > > mysql> select version(); > +-+ > | version() | > +-+ > | 5.1.21-beta-log | > +-+ > 1 row in set (0.00 sec) > > mysql> > > > But the variabe @tmp_id 's value is 5; > Any help is appreciated. > > Here is my test data. > > DROP TABLE IF EXISTS `t`; > > CREATE TABLE `t` ( > `id` int(11) NOT NULL auto_increment, > `g_id` int(11) NOT NULL, > `t_str` varchar(255) NOT NULL, > PRIMARY KEY (`id`), > key (`g_id`) > ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8; > > /*Data for the table `t` */ > > insert into `t`(`id`,`g_id`,`t_str`) values > (1,2,'wo'), > (2,2,'ni'), > (3,2,'ta'), > (4,3,'wo '), > (5,4,'ni'), > (6,3,'ni'), > (7,4,'ta'), > (8,3,'wang'), > (9,4,'li'), > (10,3,'hai'), > (11,4,'ri'), > (12,2,'ren'), > (13,5,'ta'), > (14,6,'ri'), > (15,6,'ren'), > (16,6,'fuck'), > (17,6,'shit'), > (18,5,'ls'), > (19,5,'chmod'), > (20,5,'chgrp'), > (21,5,'chown'), > (22,3,'rm'), > (23,3,'desc'), > (24,4,'pwd'), > (25,5,'cd'); > > > -- > I'm a mysql DBA in china. > More about me just visit here: > http://yueliangdao0608.cublog.cn > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Who know this strange problem
My test is as follows: mysql> select g_id from t group by g_id order by g_id desc limit 0,1; +--+ | g_id | +--+ |6 | +--+ 1 row in set (0.00 sec) mysql> select g_id from t group by g_id order by g_id desc limit 0,1 into @tmp_id; Query OK, 1 row affected (0.00 sec) mysql> select @tmp_id; +-+ | @tmp_id | +-+ | 5 | +-+ 1 row in set (0.00 sec) mysql> select version(); +-+ | version() | +-+ | 5.1.21-beta-log | +-+ 1 row in set (0.00 sec) mysql> But the variabe @tmp_id 's value is 5; Any help is appreciated. Here is my test data. DROP TABLE IF EXISTS `t`; CREATE TABLE `t` ( `id` int(11) NOT NULL auto_increment, `g_id` int(11) NOT NULL, `t_str` varchar(255) NOT NULL, PRIMARY KEY (`id`), key (`g_id`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8; /*Data for the table `t` */ insert into `t`(`id`,`g_id`,`t_str`) values (1,2,'wo'), (2,2,'ni'), (3,2,'ta'), (4,3,'wo '), (5,4,'ni'), (6,3,'ni'), (7,4,'ta'), (8,3,'wang'), (9,4,'li'), (10,3,'hai'), (11,4,'ri'), (12,2,'ren'), (13,5,'ta'), (14,6,'ri'), (15,6,'ren'), (16,6,'fuck'), (17,6,'shit'), (18,5,'ls'), (19,5,'chmod'), (20,5,'chgrp'), (21,5,'chown'), (22,3,'rm'), (23,3,'desc'), (24,4,'pwd'), (25,5,'cd'); -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Strange problem: Increasing Memory / HEAP Table
What does SHOW CREATE TABLE give you? -Sheeri On 3/11/06, Holger Sunke <[EMAIL PROTECTED]> wrote: > Hallo, > > is there a way of maintaining Memory tables or rebuilding indexes? > > I have a "big" memory table with about 300k rows, 12 Attributes, 7 > BTREE-Indexes and a PRIMARY KEY (Hash index). > > Now i'm doing many many Updates (and a little number of inserts) on this > table that also do effect indexed attributes. > (With many updates I mean about 2000 per second...thats why I have chosen > Memory storage engine) > > The strange thing is that my table grows above linear to the happening > inserts, so average row size (=total memory used by table divided by number > of rows) increases too. > > When I now copy this table to another database or another table name by > "CREATE TABLE new ..." and then "INSERT INTO new SELECT * FROM old" > memory used by new table can be about 50% (depends on number of actions done > on the old table before) of memory used by old table although Structure and > Data are equal. > > Could that be a bug? Table size especially of Memory tables should not > increase by updates i think... > > greets! > > -- > 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]
Strange problem: Increasing Memory / HEAP Table
Hallo, is there a way of maintaining Memory tables or rebuilding indexes? I have a "big" memory table with about 300k rows, 12 Attributes, 7 BTREE-Indexes and a PRIMARY KEY (Hash index). Now i'm doing many many Updates (and a little number of inserts) on this table that also do effect indexed attributes. (With many updates I mean about 2000 per second...thats why I have chosen Memory storage engine) The strange thing is that my table grows above linear to the happening inserts, so average row size (=total memory used by table divided by number of rows) increases too. When I now copy this table to another database or another table name by "CREATE TABLE new ..." and then "INSERT INTO new SELECT * FROM old" memory used by new table can be about 50% (depends on number of actions done on the old table before) of memory used by old table although Structure and Data are equal. Could that be a bug? Table size especially of Memory tables should not increase by updates i think... greets! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
strange problem ( for me..)
Hi, i have a little mistake during an interrogation... if i do SELECT Cell_line FROM Mutations Where Cell_line='NCI-H63'; it returns a lot of results , all NCIH63 , and this is correct... but if i do : SELECT DISTINCT Cell_line FROM Mutations Where Cell_line='NCI-H63'; it return empty result, while i espect 1 row with NCI-H63; Is my fault? thanks,Riccardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange problem with index
Grzegorz Paszka <[EMAIL PROTECTED]> wrote: > Hi. > > I use MySQL 4.0.17 from rpm. > When I want insert new row by perl script to one of my table I get such error : > DBD::mysql::st execute failed: Duplicate entry '- Modified the spec file provided by > ...' for key 3 > Table looks : > mysql> desc spak; > +--++--+-+-+---+ > | Field| Type | Null | Key | Default | Extra | > +--++--+-+-+---+ > | id_lacz | int(11)| YES | MUL | NULL| | > | selektor | int(4) | YES | MUL | NULL| | > | data | mediumtext | YES | MUL | NULL| | > +--++--+-+-+---+ > 3 rows in set (0.01 sec) > > mysql> show index from spak; > +---++---+--+-+---+-+--++--++-+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | > Cardinality | Sub_part | Packed | Null | Index_type | Comment | > +---++---+--+-+---+-+--++--++-+ > | spak | 1 | spak_id_lacz |1 | id_lacz | A | > 206999 | NULL | NULL | YES | BTREE | | > | spak | 1 | spak_selektor |1 | selektor| A | > 5 | NULL | NULL | YES | BTREE | | > | spak | 1 | spak_data |1 | data| A | > 344999 | 200 | NULL | YES | BTREE | | > | spak | 1 | spak_fdata|1 | data| A | > 1034998 |1 | NULL | YES | FULLTEXT | | > +---++---+--+-+---+-+--++--++-+ > 4 rows in set (0.04 sec) > > mysql> select count(*) from spak; > +--+ > | count(*) | > +--+ > | 1034998 | > +--+ > 1 row in set (0.00 sec) > > Data length in "data" column is rather big; > > I did myisamchk -r , optimize table, repair table, mysqldump and insert it again, > but problem still exists. > > I read that such error occurs when I insert no unique value to column with unique > property. > But as you can see I haven't UNIQUE key anywhere. > > I need help becouse I don't know what to do. > Please, upload gzipped dump and insert query to the ftp://support.mysql.com/pub/mysql/Incoming and let me know a filename. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange problem with index
Hi. I use MySQL 4.0.17 from rpm. When I want insert new row by perl script to one of my table I get such error : DBD::mysql::st execute failed: Duplicate entry '- Modified the spec file provided by ...' for key 3 Table looks : mysql> desc spak; +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | id_lacz | int(11)| YES | MUL | NULL| | | selektor | int(4) | YES | MUL | NULL| | | data | mediumtext | YES | MUL | NULL| | +--++--+-+-+---+ 3 rows in set (0.01 sec) mysql> show index from spak; +---++---+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++---+--+-+---+-+--++--++-+ | spak | 1 | spak_id_lacz |1 | id_lacz | A | 206999 | NULL | NULL | YES | BTREE | | | spak | 1 | spak_selektor |1 | selektor| A | 5 | NULL | NULL | YES | BTREE | | | spak | 1 | spak_data |1 | data| A | 344999 | 200 | NULL | YES | BTREE | | | spak | 1 | spak_fdata|1 | data| A | 1034998 |1 | NULL | YES | FULLTEXT | | +---++---+--+-+---+-+--++--++-+ 4 rows in set (0.04 sec) mysql> select count(*) from spak; +--+ | count(*) | +--+ | 1034998 | +--+ 1 row in set (0.00 sec) Data length in "data" column is rather big; I did myisamchk -r , optimize table, repair table, mysqldump and insert it again, but problem still exists. I read that such error occurs when I insert no unique value to column with unique property. But as you can see I haven't UNIQUE key anywhere. I need help becouse I don't know what to do. Regards. -- Grzegorz Paszka -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very strange problem
On Fri, Nov 07, 2003 at 09:16:03AM -0800, Eric Anderson wrote: > > A little explaination: [snip] > The threads connecting from the 1U (192.168.0.2) all start to block up > as expected, but when the length query on the e450 stops running, the > threads from the 1U stay blocked, and all NEW threads end up in a > "limbo" state: > > | 726136 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726135 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726134 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726138 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726140 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726142 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726143 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726148 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726149 | nobody | localhost | bm | Sleep | 11 > | | NULL | > | 726150 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726153 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726158 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726161 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726164 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726170 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726172 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726173 | nobody | localhost | bm | Sleep | 4 > | | NULL | > | 726181 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726185 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726191 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > | 726201 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL > | login| NULL | > > No connections from 1U (192.168.0.2) are possible - they just hang, even > the interactive client just hangs. That looks a lot like a DNS problem I've seen on FreeBSD. Do you happen to have skip-name-resovle in the E450's my.cnf? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 55 days, processed 2,062,011,744 queries (433/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very strange problem
> Is anything being logged in the error log? Nope. My 'wait_timeout' was set to 120 seconds, I reduced that down to 60 seconds but I don't think that will help because once it gets "stuck" in this blocked thread thing, it never recovers.. any subsequent connections never actually process, until the max_connections are hit. It's very strange.. :-/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very strange problem
Is anything being logged in the error log? -Original Message- From: Eric Anderson [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 11:16 AM To: [EMAIL PROTECTED] Subject: Very strange problem A little explaination: We've got two servers, e450 (3.23.47) and 1U (4.0.14). These two machines have a private connection between them (192.168.0.1 and 192.168.0.2). The e450 is the master server, and the 1U has a copy (no replication) of some basic data, so it can run server-intensive pages. However, it needs to connect back to the e450 to insert some data. Everything runs fine until the e450 does something that "blocks" other threads (including the threads that are connecting from the 1U) on the table 'vip_t'. Let's say for example that I run a lengthy query on table 'vip_t' which causes all the other threads to be blocked while that query runs. The threads connecting from the 1U (192.168.0.2) all start to block up as expected, but when the length query on the e450 stops running, the threads from the 1U stay blocked, and all NEW threads end up in a "limbo" state: | 726136 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726135 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726134 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726138 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726140 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726142 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726143 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726148 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726149 | nobody | localhost | bm | Sleep | 11 | | NULL | | 726150 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726153 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726158 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726161 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726164 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726170 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726172 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726173 | nobody | localhost | bm | Sleep | 4| | NULL | | 726181 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726185 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726191 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | | 726201 | unauthenticated user | 192.168.0.2 | NULL | Connect | NULL | login| NULL | No connections from 1U (192.168.0.2) are possible - they just hang, even the interactive client just hangs. In order to clear things, I have to shutdown both httpd and mysql on both machines and restart them. And on the e450, mysql will not shut down completely. 'mysqladmin shutdown' just hangs (ostensibly waiting for the above threads to clear). I end up flushing everything and killing both mysql and the starting script. I'm sure the solution is to upgrade the e450's version of mysql first because that seems to be where the problem is, but I hate to do it at gunpoint. I'm wondering if anyone else has heard/seen any of these problems? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing L
Very strange problem
A little explaination: We've got two servers, e450 (3.23.47) and 1U (4.0.14). These two machines have a private connection between them (192.168.0.1 and 192.168.0.2). The e450 is the master server, and the 1U has a copy (no replication) of some basic data, so it can run server-intensive pages. However, it needs to connect back to the e450 to insert some data. Everything runs fine until the e450 does something that "blocks" other threads (including the threads that are connecting from the 1U) on the table 'vip_t'. Let's say for example that I run a lengthy query on table 'vip_t' which causes all the other threads to be blocked while that query runs. The threads connecting from the 1U (192.168.0.2) all start to block up as expected, but when the length query on the e450 stops running, the threads from the 1U stay blocked, and all NEW threads end up in a "limbo" state: | 726136 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726135 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726134 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726138 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726140 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726142 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726143 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726148 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726149 | nobody | localhost | bm | Sleep | 11 | | NULL | | 726150 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726153 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726158 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726161 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726164 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726170 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726172 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726173 | nobody | localhost | bm | Sleep | 4| | NULL | | 726181 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726185 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726191 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726201 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | No connections from 1U (192.168.0.2) are possible - they just hang, even the interactive client just hangs. In order to clear things, I have to shutdown both httpd and mysql on both machines and restart them. And on the e450, mysql will not shut down completely. 'mysqladmin shutdown' just hangs (ostensibly waiting for the above threads to clear). I end up flushing everything and killing both mysql and the starting script. I'm sure the solution is to upgrade the e450's version of mysql first because that seems to be where the problem is, but I hate to do it at gunpoint. I'm wondering if anyone else has heard/seen any of these problems? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange problem on the difference of mysql connection between cgi and shell
It is not strange, it is expected behavior. A browser can only handle so many lines of html before getting all wonky. Running a query that takes over 5 minutes to run in a web application is a bad idea, no one is going to want to sit there and wait for it and if there is no output for an extended period of time newer browsers will be "helpful" and re-request the page, causing lock issues and such. I would suggest trying to speed up the query, you need to make sure you are using indexes. or you might need to use summary tables. You will also want to only display a portion of your result set at a time as 10,000 records is probably going to crash browsers. You will also need to flush output to prevent the browser from re-requesting, you also might want to put something in to prevent the user from hitting reload (such as checking at the beginning to see if the query is already running, most of the time web users do not understand that for every time they hit reload their wait time is going to increase. -Original Message- From: Liu Haifeng [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 18, 2003 7:23 PM To: [EMAIL PROTECTED] Subject: Strange problem on the difference of mysql connection between cgi and shell Hi, I am coding a cgi program to query the records from the mysql tables. When I execute the sql query (which is a join operation on two tables) in the mysql shell, the results are shown correctly. However, if I use cgi script to execute the same query, the web browser just crashed after a while (5 or 6 minutes which is the same as the query execution time ). I am guessing the problem is relevant to the larger result (more than 10,000 records), because if I execute the query against another two tables which have the same structure but contain the less records, both cgi script and shell method can work. So anybody can help me figure out the problem? I have adjusted the configuration of mysqld to be a huge one but still failed. Regards Haifeng Liu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange problem on the difference of mysql connection between cgi and shell
Hi, I am coding a cgi program to query the records from the mysql tables. When I execute the sql query (which is a join operation on two tables) in the mysql shell, the results are shown correctly. However, if I use cgi script to execute the same query, the web browser just crashed after a while (5 or 6 minutes which is the same as the query execution time ). I am guessing the problem is relevant to the larger result (more than 10,000 records), because if I execute the query against another two tables which have the same structure but contain the less records, both cgi script and shell method can work. So anybody can help me figure out the problem? I have adjusted the configuration of mysqld to be a huge one but still failed. Regards Haifeng Liu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
VS: Strange problem with MySql & IIS & ASP
Millions of thanks! Now it's working perfectly!!! Once again MS bugs... Regards Juha -Alkuperäinen viesti- Lähettäjä: Terence [mailto:terencel@;sunway.edu.my] Lähetetty: 23. lokakuuta 2002 4:54 Vastaanottaja: Morsky Juha; [EMAIL PROTECTED] Aihe: Re: Strange problem with MySql & IIS & ASP I had the same problem. It's in your connection, somehow ASP doesnt retrieve the results when doing a select and count, so you need to use a connection like this: dim myConn Set myConn= Server.CreateObject ("ADODB.Connection") myConn.ConnectionString = "Driver={MySQL};SERVER=xxx.xxx.xxx.xxx;DATABASE=mydb;UID=user;PWD=pwd;PORT=3 306;OPTION=16384;" myConn.Open Hopefully this works. Cheers Terence Hi All! I've Select like this: SELECT ExpDepDate, ActArrDate FROM oceandata It works beautifully But When I modify it like this SELECT ExpDepDate, ActArrDate, Count(Ref) as NoOfRefs FROM oceandata I'll get nothing, just a empty screen with a browser! I'm using Ultradev to create this and when I test it in Ultradev it works good, same in MySQL CC Admin. But not on IIS? ASP look like this: <%@LANGUAGE="VBSCRIPT"%> <% set Recordset1 = Server.CreateObject("ADODB.Recordset") Recordset1.ActiveConnection = MM_connMyIFDData_STRING Recordset1.Source = "SELECT ExpDepDate, ActArrDate, Count(Ref) as NoOfRefs FROM oceandata" Recordset1.CursorType = 0 Recordset1.CursorLocation = 2 Recordset1.LockType = 3 Recordset1.Open() Recordset1_numRows = 0 %> <% Dim Repeat1__numRows Repeat1__numRows = 10 Dim Repeat1__index Repeat1__index = 0 Recordset1_numRows = Recordset1_numRows + Repeat1__numRows %> Untitled Document <% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %> <%=(Recordset1.Fields.Item("ExpDepDate").Value)%> <%=(Recordset1.Fields.Item("ActArrDate").Value)%> <%=(Recordset1.Fields.Item("Act_Est").Value)%> <% Repeat1__index=Repeat1__index+1 Repeat1__numRows=Repeat1__numRows-1 Recordset1.MoveNext() Wend %> <% Recordset1.Close() %> Any help will be helpfull Regards Juha Mörsky MySQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Strange problem with MySql & IIS & ASP
I had the same problem. It's in your connection, somehow ASP doesnt retrieve the results when doing a select and count, so you need to use a connection like this: dim myConn Set myConn= Server.CreateObject ("ADODB.Connection") myConn.ConnectionString = "Driver={MySQL};SERVER=xxx.xxx.xxx.xxx;DATABASE=mydb;UID=user;PWD=pwd;PORT=3 306;OPTION=16384;" myConn.Open Hopefully this works. Cheers Terence Hi All! I've Select like this: SELECT ExpDepDate, ActArrDate FROM oceandata It works beautifully But When I modify it like this SELECT ExpDepDate, ActArrDate, Count(Ref) as NoOfRefs FROM oceandata I'll get nothing, just a empty screen with a browser! I'm using Ultradev to create this and when I test it in Ultradev it works good, same in MySQL CC Admin. But not on IIS? ASP look like this: <%@LANGUAGE="VBSCRIPT"%> <% set Recordset1 = Server.CreateObject("ADODB.Recordset") Recordset1.ActiveConnection = MM_connMyIFDData_STRING Recordset1.Source = "SELECT ExpDepDate, ActArrDate, Count(Ref) as NoOfRefs FROM oceandata" Recordset1.CursorType = 0 Recordset1.CursorLocation = 2 Recordset1.LockType = 3 Recordset1.Open() Recordset1_numRows = 0 %> <% Dim Repeat1__numRows Repeat1__numRows = 10 Dim Repeat1__index Repeat1__index = 0 Recordset1_numRows = Recordset1_numRows + Repeat1__numRows %> Untitled Document <% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %> <%=(Recordset1.Fields.Item("ExpDepDate").Value)%> <%=(Recordset1.Fields.Item("ActArrDate").Value)%> <%=(Recordset1.Fields.Item("Act_Est").Value)%> <% Repeat1__index=Repeat1__index+1 Repeat1__numRows=Repeat1__numRows-1 Recordset1.MoveNext() Wend %> <% Recordset1.Close() %> Any help will be helpfull Regards Juha Mörsky MySQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Strange problem with MySql & IIS & ASP
Have you tried using a GROUP BY statement - maybe something like: SELECT ExpDepDate, ActArrDate, Count(Ref) as NoOfRefs FROM oceandata GROUP BY ExpDepDate, ActArrDate, Ref Good Luck! Dennis - Original Message - From: "Morsky Juha" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, October 21, 2002 7:39 AM Subject: Strange problem with MySql & IIS & ASP Hi All! I've Select like this: SELECT ExpDepDate, ActArrDate FROM oceandata It works beautifully But When I modify it like this SELECT ExpDepDate, ActArrDate, Count(Ref) as NoOfRefs FROM oceandata I'll get nothing, just a empty screen with a browser! I'm using Ultradev to create this and when I test it in Ultradev it works good, same in MySQL CC Admin. But not on IIS? ASP look like this: <%@LANGUAGE="VBSCRIPT"%> <% set Recordset1 = Server.CreateObject("ADODB.Recordset") Recordset1.ActiveConnection = MM_connMyIFDData_STRING Recordset1.Source = "SELECT ExpDepDate, ActArrDate, Count(Ref) as NoOfRefs FROM oceandata" Recordset1.CursorType = 0 Recordset1.CursorLocation = 2 Recordset1.LockType = 3 Recordset1.Open() Recordset1_numRows = 0 %> <% Dim Repeat1__numRows Repeat1__numRows = 10 Dim Repeat1__index Repeat1__index = 0 Recordset1_numRows = Recordset1_numRows + Repeat1__numRows %> Untitled Document <% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %> <%=(Recordset1.Fields.Item("ExpDepDate").Value)%> <%=(Recordset1.Fields.Item("ActArrDate").Value)%> <%=(Recordset1.Fields.Item("Act_Est").Value)%> <% Repeat1__index=Repeat1__index+1 Repeat1__numRows=Repeat1__numRows-1 Recordset1.MoveNext() Wend %> <% Recordset1.Close() %> Any help will be helpfull Regards Juha Mörsky MySQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange problem with MySql & IIS & ASP
Hi All! I've Select like this: SELECT ExpDepDate, ActArrDate FROM oceandata It works beautifully But When I modify it like this SELECT ExpDepDate, ActArrDate, Count(Ref) as NoOfRefs FROM oceandata I'll get nothing, just a empty screen with a browser! I'm using Ultradev to create this and when I test it in Ultradev it works good, same in MySQL CC Admin. But not on IIS? ASP look like this: <%@LANGUAGE="VBSCRIPT"%> <% set Recordset1 = Server.CreateObject("ADODB.Recordset") Recordset1.ActiveConnection = MM_connMyIFDData_STRING Recordset1.Source = "SELECT ExpDepDate, ActArrDate, Count(Ref) as NoOfRefs FROM oceandata" Recordset1.CursorType = 0 Recordset1.CursorLocation = 2 Recordset1.LockType = 3 Recordset1.Open() Recordset1_numRows = 0 %> <% Dim Repeat1__numRows Repeat1__numRows = 10 Dim Repeat1__index Repeat1__index = 0 Recordset1_numRows = Recordset1_numRows + Repeat1__numRows %> Untitled Document <% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %> <%=(Recordset1.Fields.Item("ExpDepDate").Value)%> <%=(Recordset1.Fields.Item("ActArrDate").Value)%> <%=(Recordset1.Fields.Item("Act_Est").Value)%> <% Repeat1__index=Repeat1__index+1 Repeat1__numRows=Repeat1__numRows-1 Recordset1.MoveNext() Wend %> <% Recordset1.Close() %> Any help will be helpfull Regards Juha Mörsky MySQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange problem with MySql & IIS & ASP
Hi All! I've Select like this: SELECT ExpDepDate, ActArrDate FROM oceandata It works beautifully But When I modify it like this SELECT ExpDepDate, ActArrDate, (TO_DAYS(ActArrDate)-TO_DAYS(ExpDepDate)) as Act_Est FROM oceandata I'll get nothing, just a empty screen with a browser! I'm using Ultradev to create this and when I test it in Ultradev it works good, same in MySQL CC Admin. But not on IIS? ASP look like this: <%@LANGUAGE="VBSCRIPT"%> <% set Recordset1 = Server.CreateObject("ADODB.Recordset") Recordset1.ActiveConnection = MM_connMyIFDData_STRING Recordset1.Source = "SELECT ExpDepDate, ActArrDate, (TO_DAYS(ActArrDate)-TO_DAYS(ExpDepDate)) as Act_Est FROM oceandata" Recordset1.CursorType = 0 Recordset1.CursorLocation = 2 Recordset1.LockType = 3 Recordset1.Open() Recordset1_numRows = 0 %> <% Dim Repeat1__numRows Repeat1__numRows = 10 Dim Repeat1__index Repeat1__index = 0 Recordset1_numRows = Recordset1_numRows + Repeat1__numRows %> Untitled Document <% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %> <%=(Recordset1.Fields.Item("ExpDepDate").Value)%> <%=(Recordset1.Fields.Item("ActArrDate").Value)%> <%=(Recordset1.Fields.Item("Act_Est").Value)%> <% Repeat1__index=Repeat1__index+1 Repeat1__numRows=Repeat1__numRows-1 Recordset1.MoveNext() Wend %> <% Recordset1.Close() %> Any help will be helpfull Regards Juha Mörsky MySQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange Problem: Need help shutting down Mysql for upgrade
Stephen Sokalski wrote: > > > Stephen Sokalski wrote: > >> I'm running this on a RAQ. >> >> Here is the problem. Something very strange has happened to my older >> version of mysql (3.23.37). >> >> Not only can I not get it to shut down with >> "/usr/share/mysql/mysql.server stop" but even if I do a "ps -ef | >> grep mysql" and do a kill -9 on the pid, if I try to restart it, it >> tells me that a task is using that socket. >> >> On a linux based machine, how can I remove the task so that when I >> reboot it will not start? >> >> I have more questions regarding upgrading but first things are first. >> >> Thanks, >> Steve >> >> > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TCP & TIME_WAIT strange problem ( LINUX & mysql 3.23.49 )
Hi, "TIME_WAIT" said to you that are still packet in the network which are unhandled after socket it's closed. The question is: ... why happend this? IFCONFIG show you information about network interfaces which is mounted in your computer and not about entire network. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "David BORDAS" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: "Gelu Gogancea" <[EMAIL PROTECTED]> Sent: Wednesday, July 03, 2002 10:21 PM Subject: Re: TCP & TIME_WAIT strange problem ( LINUX & mysql 3.23.49 ) > > Hi, > > What is written in err.log ("/var/lib/mysql") ? > nothing :( > something like > mysql started and ready for connection > > > I think you have some problems with network (switch or hub). > Euh network hardware i don't think, perhaps a problem with software but may > be strange 'cause ifconfig says that all is ok : > > > ifconfig show me that me eth0 config is clear : > > > UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 > > > RX packets:17728939 errors:0 dropped:0 overruns:0 frame:0 > > > TX packets:10940614 errors:0 dropped:0 overruns:0 carrier:0 > > > collisions:0 lg file transmission:100 > > > RX bytes:1689574777 (1611.3 Mb) TX bytes:347460048 (331.3 Mb) > > Here's the show status : > +--++ > | Variable_name| Value | > +--++ > | Aborted_clients | 3467 | > | Aborted_connects | 24 | > | Bytes_received | 128250456 | > | Bytes_sent | 2317251152 | > | Com_admin_commands | 0 | > | Com_alter_table | 0 | > | Com_analyze | 0 | > | Com_backup_table | 0 | > | Com_begin| 0 | > | Com_change_db| 1 | > | Com_change_master| 0 | > | Com_check| 0 | > | Com_commit | 0 | > | Com_create_db| 0 | > | Com_create_function | 0 | > | Com_create_index | 0 | > | Com_create_table | 0 | > | Com_delete | 1107 | > | Com_drop_db | 0 | > | Com_drop_function| 0 | > | Com_drop_index | 0 | > | Com_drop_table | 0 | > | Com_flush| 0 | > | Com_grant| 0 | > | Com_insert | 28257 | > | Com_insert_select| 0 | > | Com_kill | 0 | > | Com_load | 0 | > | Com_load_master_table| 0 | > | Com_lock_tables | 0 | > | Com_optimize | 0 | > | Com_purge| 0 | > | Com_rename_table | 0 | > | Com_repair | 0 | > | Com_replace | 0 | > | Com_replace_select | 0 | > | Com_reset| 0 | > | Com_restore_table| 0 | > | Com_revoke | 0 | > | Com_rollback | 0 | > | Com_select | 614872 | > | Com_set_option | 1 | > | Com_show_binlogs | 0 | > | Com_show_create | 1 | > | Com_show_databases | 0 | > | Com_show_fields | 1 | > | Com_show_grants | 0 | > | Com_show_keys| 1 | > | Com_show_logs| 0 | > | Com_show_master_stat | 0 | > | Com_show_open_tables | 0 | > | Com_show_processlist | 11 | > | Com_show_slave_stat | 0 | > | Com_show_status | 6 | > | Com_show_tables | 2 | > | Com_show_variables | 1 | > | Com_slave_start | 0 | > | Com_slave_stop | 0 | > | Com_truncate | 0 | > | Com_unlock_tables| 0 | > | Com_update | 25456 | > | Connections | 425680 | > | Created_tmp_disk_tables | 0 | > | Created_tmp_tables | 1 | > | Created_tmp_files| 32 | > | Delayed_insert_threads | 0 | > | Delayed_writes | 0 | > | Delayed_errors | 0 | > | Flush_commands | 1
Re: Strange problem - could be a bug?
In the last episode (Jul 03), Mike Hall said: > FreeBSD 4.5-STABLE with LinuxThreads 2.2.3. and MySQL 3.23.51. Duel > PIII 1Ghz with 1G of RAM. All from source. > > Compiled okay and came through super-smack and crash-me tests okay. > But a few hours later, for no reason, MySQL decided to look at > /var/tmp instead of /usr/local/mysql/var for the datadir. There is > nothing in the logs to suggest why this happened. AFAIK, you have to > restart mysqld to change the data dir, but the daemon was not > restarted at any point. SHOW VARIABLES revealed that the datadir was > still set to /usr/local/mysql/var, but SHOW DATABASES listed the > contents of /var/tmp (in my case vi.recover). When I shutdown and > restarted the system, it reverted to normal behaviour. > > Anyone have any idea what is going on? Weeird. You don't happen to have written a UDF that changes directories, do you? From a quick scan of the source, mysql chdir()'s to the datadir once on startup and uses relative paths to access tables. This means that any chdir() call made while mysql is running will screw up table access from that point on. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TCP & TIME_WAIT strange problem ( LINUX & mysql 3.23.49 )
w_launch_threads | 0 | | Slow_queries | 1052 | | Sort_merge_passes| 16 | | Sort_range | 33150 | | Sort_rows| 993404 | | Sort_scan| 8522 | | Table_locks_immediate| 556209 | | Table_locks_waited | 113411 | | Threads_cached | 0 | | Threads_created | 65680 | | Threads_connected| 92 | | Threads_running | 87 | | Uptime | 132998 | +--++ 111 rows in set (0.00 sec) Thanks David > - Original Message - > From: "David BORDAS" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, July 03, 2002 7:03 PM > Subject: TCP & TIME_WAIT strange problem ( LINUX & mysql 3.23.49 ) > > > > Hi list, > > > > I've got lots of troubles with one of my mysql dedicated server. > > The problem occurs time to time ( during peac or idle time ). > > In fact mysql seem not to answer all connections. > > Netstat show several connections "TIME_WAIT" and show process_list only > show > > 1 ou 2 queries running ... > > > > But on other way, 80% of the time, mysql works and answers well. > > At those moments netstat show lots of "ETABLISHED"; > > > > All connections are made through TCP/IP on a private LAN ( ip like > > 192.168.x.xx ). > > Server don't have access to internet. > > > > ifconfig show me that me eth0 config is clear : > > UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 > > RX packets:17728939 errors:0 dropped:0 overruns:0 frame:0 > > TX packets:10940614 errors:0 dropped:0 overruns:0 carrier:0 > > collisions:0 lg file transmission:100 > > RX bytes:1689574777 (1611.3 Mb) TX bytes:347460048 (331.3 Mb) > > > > Here is the config : > > > > uname -a > > Linux 2.4.7-10smp #1 SMP Thu Sep 6 17:09:31 EDT 2001 i686 unknown > > Red Hat 7.2 > > 2 * 1Ghz Intel > > 1 Go of ram > > 2 Go of swap > > Raid 5 scsi hdds > > > > Running mysql 3.23.49 from mysql team tar.gz binary. > > I've got only one MYISAM table like this : > > 8922 jun 19 07:43 Forums.frm > > 980150372 jui 3 17:24 Forums.MYD > > 85792768 jui 3 17:24 Forums.MYI > > > > mysql> show table status; > > > > | Name | Type| Row_format | Rows | Avg_row_length | > > Data_length | Max_data_length | Index_length | Data_free | > > > +--+++-++-+- > > +--+---+ > > | Forums | MyISAM | Dynamic | 3881810 |252 | > > 980155640 | 4294967295 | 85792768 | 0 | > > > > > > | Name | Type | Row_format | Rows | Avg_row_length | > > Data_length | Max_data_length | Index_length | Data_free | > > > +--+++-++-+- > > +--+---+ > > | Forums | MyISAM | Dynamic | 3881810 | 252 | > > 980155640 | 4294967295 | 85792768 | 0 | > > > > > > > > my.cnf : > > [mysqld] > > port= 3306 > > #socket = /tmp/mysql.sock > > skip-locking > > skip-name-resolve > > set-variable= key_buffer=128M > > set-variable= back_log=150 > > set-variable= record_buffer=1M > > set-variable= sort_buffer=2M > > set-variable= max_allowed_packet=1M > > set-variable= thread_stack=128K > > set-variable= thread_cache_size=8 > > set-variable= max_connections=700 > > set-variable= max_connect_errors=100 > > set-variable= table_cache=256 > > set-variable= net_read_timeout=180 > > set-variable= net_write_timeout=180 > > set-variable= wait_timeout=3600 > > > > I don't where to search. > > Is that an hardware problem ? > > Why not but the server isn't old ( only 3 mounths ). > > > > Linux ? > > Have a second mysq server with same hardware, same linux and same mysql > and > > this one don't have any problem. > > > > Best rgds > > David > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TCP & TIME_WAIT strange problem ( LINUX & mysql 3.23.49 )
Hi, What is written in err.log ("/var/lib/mysql") ? I think you have some problems with network (switch or hub). Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "David BORDAS" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, July 03, 2002 7:03 PM Subject: TCP & TIME_WAIT strange problem ( LINUX & mysql 3.23.49 ) > Hi list, > > I've got lots of troubles with one of my mysql dedicated server. > The problem occurs time to time ( during peac or idle time ). > In fact mysql seem not to answer all connections. > Netstat show several connections "TIME_WAIT" and show process_list only show > 1 ou 2 queries running ... > > But on other way, 80% of the time, mysql works and answers well. > At those moments netstat show lots of "ETABLISHED"; > > All connections are made through TCP/IP on a private LAN ( ip like > 192.168.x.xx ). > Server don't have access to internet. > > ifconfig show me that me eth0 config is clear : > UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 > RX packets:17728939 errors:0 dropped:0 overruns:0 frame:0 > TX packets:10940614 errors:0 dropped:0 overruns:0 carrier:0 > collisions:0 lg file transmission:100 > RX bytes:1689574777 (1611.3 Mb) TX bytes:347460048 (331.3 Mb) > > Here is the config : > > uname -a > Linux 2.4.7-10smp #1 SMP Thu Sep 6 17:09:31 EDT 2001 i686 unknown > Red Hat 7.2 > 2 * 1Ghz Intel > 1 Go of ram > 2 Go of swap > Raid 5 scsi hdds > > Running mysql 3.23.49 from mysql team tar.gz binary. > I've got only one MYISAM table like this : > 8922 jun 19 07:43 Forums.frm > 980150372 jui 3 17:24 Forums.MYD > 85792768 jui 3 17:24 Forums.MYI > > mysql> show table status; > > | Name | Type| Row_format | Rows | Avg_row_length | > Data_length | Max_data_length | Index_length | Data_free | > +--+++-++-+- > +--+---+ > | Forums | MyISAM | Dynamic | 3881810 |252 | > 980155640 | 4294967295 | 85792768 | 0 | > > > | Name | Type | Row_format | Rows | Avg_row_length | > Data_length | Max_data_length | Index_length | Data_free | > +--+++-++-+- > +--+---+ > | Forums | MyISAM | Dynamic | 3881810 | 252 | > 980155640 | 4294967295 | 85792768 | 0 | > > > > my.cnf : > [mysqld] > port= 3306 > #socket = /tmp/mysql.sock > skip-locking > skip-name-resolve > set-variable= key_buffer=128M > set-variable= back_log=150 > set-variable= record_buffer=1M > set-variable= sort_buffer=2M > set-variable= max_allowed_packet=1M > set-variable= thread_stack=128K > set-variable= thread_cache_size=8 > set-variable= max_connections=700 > set-variable= max_connect_errors=100 > set-variable= table_cache=256 > set-variable= net_read_timeout=180 > set-variable= net_write_timeout=180 > set-variable= wait_timeout=3600 > > I don't where to search. > Is that an hardware problem ? > Why not but the server isn't old ( only 3 mounths ). > > Linux ? > Have a second mysq server with same hardware, same linux and same mysql and > this one don't have any problem. > > Best rgds > David > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
TCP & TIME_WAIT strange problem ( LINUX & mysql 3.23.49 )
Hi list, I've got lots of troubles with one of my mysql dedicated server. The problem occurs time to time ( during peac or idle time ). In fact mysql seem not to answer all connections. Netstat show several connections "TIME_WAIT" and show process_list only show 1 ou 2 queries running ... But on other way, 80% of the time, mysql works and answers well. At those moments netstat show lots of "ETABLISHED"; All connections are made through TCP/IP on a private LAN ( ip like 192.168.x.xx ). Server don't have access to internet. ifconfig show me that me eth0 config is clear : UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:17728939 errors:0 dropped:0 overruns:0 frame:0 TX packets:10940614 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 lg file transmission:100 RX bytes:1689574777 (1611.3 Mb) TX bytes:347460048 (331.3 Mb) Here is the config : uname -a Linux 2.4.7-10smp #1 SMP Thu Sep 6 17:09:31 EDT 2001 i686 unknown Red Hat 7.2 2 * 1Ghz Intel 1 Go of ram 2 Go of swap Raid 5 scsi hdds Running mysql 3.23.49 from mysql team tar.gz binary. I've got only one MYISAM table like this : 8922 jun 19 07:43 Forums.frm 980150372 jui 3 17:24 Forums.MYD 85792768 jui 3 17:24 Forums.MYI mysql> show table status; | Name | Type| Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | +--+++-++-+- +--+---+ | Forums | MyISAM | Dynamic | 3881810 |252 | 980155640 | 4294967295 | 85792768 | 0 | | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | +--+++-++-+- +--+---+ | Forums | MyISAM | Dynamic | 3881810 | 252 | 980155640 | 4294967295 | 85792768 | 0 | my.cnf : [mysqld] port= 3306 #socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=128M set-variable= back_log=150 set-variable= record_buffer=1M set-variable= sort_buffer=2M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= thread_cache_size=8 set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=256 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 I don't where to search. Is that an hardware problem ? Why not but the server isn't old ( only 3 mounths ). Linux ? Have a second mysq server with same hardware, same linux and same mysql and this one don't have any problem. Best rgds David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange problem - could be a bug?
Okay: FreeBSD 4.5-STABLE with LinuxThreads 2.2.3. and MySQL 3.23.51. Duel PIII 1Ghz with 1G of RAM. All from source. Compiled okay and came through super-smack and crash-me tests okay. But a few hours later, for no reason, MySQL decided to look at /var/tmp instead of /usr/local/mysql/var for the datadir. There is nothing in the logs to suggest why this happened. AFAIK, you have to restart mysqld to change the data dir, but the daemon was not restarted at any point. SHOW VARIABLES revealed that the datadir was still set to /usr/local/mysql/var, but SHOW DATABASES listed the contents of /var/tmp (in my case vi.recover). When I shutdown and restarted the system, it reverted to normal behaviour. Anyone have any idea what is going on? --Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange problem with Left join and order request
Hi, i've got a really strange problem with a request using multiple left join and order by : Here is the first sql query : i display two columns of my table CV and one column of table ANNOTATION_CV SELECT ID_CV, TITRECV,ANNOTATION_CV.ANNOTATION_CV FROM CV LEFT JOIN CV_POSE ON CV_POSE.FK_CV=CV.ID_CV AND CV_POSE.FK_BV_RECRUTEUR=20613 LEFT join LISTE_VALEURS AS EXPERIENCE ON EXPERIENCE.ID_LIVA=CV.FK_EXPERIENCE LEFT join ANNOTATION_CV ON ID_CV=ANNOTATION_CV.FK_CV AND ANNOTATION_CV.FK_BV_RECRUTEUR=20613 WHERE FK_ID_ASP=1 AND (EN_RECHERCHE='Actif' OR EN_RECHERCHE='Passif') ORDER BY DATE_DE_DERNIERE_CONNEXION desc,CHERCHE_CDI desc,CHERCHE_CDD desc, CHERCHE_FREELANCE desc,CHERCHE_STAGE desc,CHERCHE_ALTERNANCE desc,CHERCHE_JOB desc, CHERCHE_AUTRE desc,TITRECV Here is the result ++--+---+ | ID_CV | TITRECV | ANNOTATION_CV | ++--+---+ | 138487 | shgfhgfdhfhg | NULL | | 138488 | shgfhgfdhfhg | NULL | | 138489 | shgfhgfdhfhg | NULL | | 138490 | shgfhgfdhfhg | NULL | | 138491 | shgfhgfdhfhg | NULL | | 138492 | shgfhgfdhfhg | NULL | | 138493 | shgfhgfdhfhg | NULL | | 138500 | shgfhgfdhfhg | NULL | | 138501 | shgfhgfdhfhg | NULL | | 138502 | shgfhgfdhfhg | NULL | | 138503 | shgfhgfdhfhg | NULL | | 138504 | shgfhgfdhfhg | NULL | | 138505 | shgfhgfdhfhg | NULL | | 138506 | shgfhgfdhfhg | NULL | | 138494 | shgfhgfdhfhg | NULL | | 138495 | shgfhgfdhfhg | NULL | | 138496 | shgfhgfdhfhg | NULL | | 138497 | shgfhgfdhfhg | NULL | | 138498 | shgfhgfdhfhg | NULL | | 138499 | shgfhgfdhfhg | NULL | | 138507 | TFGYHDFHGDFJ | NULL | | 138508 | TFGYHDFHGDFJ | NULL | | 138509 | TFGYHDFHGDFJ | NULL | ++--+---+ And the explain command : +---++-+---+ -+--+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++-+---+ -+--+--+-+ | CV| ref| INDX_EN_RECHERCHE,FK_ID_ASP | FK_ID_ASP | 3 | const| 5535 | where used; Using temporary; Using filesort | | CV_POSE | ref| CV_FK_BV_POSE,FK_CV | FK_CV | 5 | CV.ID_CV |3 | | | EXPERIENCE| eq_ref | PRIMARY | PRIMARY | 4 | CV.FK_EXPERIENCE |1 | Using index | | ANNOTATION_CV | ALL| FK_BV_RECRUTEUR,FK_CV | NULL |NULL | NULL |8 | | +---++-+---+ -+--+--+-+ Here is the second sql query (the same one, but with only conlums of table CV): i display two columns of my table CV SELECT ID_CV, TITRECV FROM CV LEFT JOIN CV_POSE ON CV_POSE.FK_CV=CV.ID_CV AND CV_POSE.FK_BV_RECRUTEUR=20613 LEFT join LISTE_VALEURS AS EXPERIENCE ON EXPERIENCE.ID_LIVA=CV.FK_EXPERIENCE LEFT join ANNOTATION_CV ON ID_CV=ANNOTATION_CV.FK_CV AND ANNOTATION_CV.FK_BV_RECRUTEUR=20613 WHERE FK_ID_ASP=1 AND (EN_RECHERCHE='Actif' OR EN_RECHERCHE='Passif') ORDER BY DATE_DE_DERNIERE_CONNEXION desc,CHERCHE_CDI desc,CHERCHE_CDD desc, CHERCHE_FREELANCE desc,CHERCHE_STAGE desc,CHERCHE_ALTERNANCE desc,CHERCHE_JOB desc, CHERCHE_AUTRE desc,TITRECV here is the result. ++--+ | ID_CV | TITRECV | ++--+ | 138501 | shgfhgfdhfhg | | 138502 | shgfhgfdhfhg | | 138503 | shgfhgfdhfhg | | 138504 | shgfhgfdhfhg | | 138487 | shgfhgfdhfhg | | 138505 | shgfhgfdhfhg | | 138488 | sh
Re: Very Strange problem.
Hi, I do commit alright. This doesn't happen often. i insert into the tables from a java web app. The db and the data in it is fine when i stop and restart the web server(tomcat). sometimes when i restart the machine, data is all intact. I was using the mysql 4 max before. Same thing happened with that too. i guessed that was because of the alpha version db. so i downgraded to mysql3.23. regards, On Thu, 2 May 2002, Jeremy Zawodny wrote: > On Fri, May 03, 2002 at 11:04:29AM +0530, TP R Murthy wrote: > > > > I have created only one database with 18 tables. Only one of these tables > > is MyIsam table. rest are BDB tables. When the server is up, it works > > fine. but when i reboot the machine, few of the tables loose all the > > records they held before the machine was rebooted. This doesn't happen > > everytime. It happens randomly. > > > > One more thing i noticed is that when i run the 'show table status' it > > says 1000 rows in the table that has lost all the records. > > This will sound dumb, but are you sure you're committing your > transactions? > > Jeremy > -- _ TP R Murthy (Systems Analyst) Cerulean Information Technology Pvt. Ltd. #24, floor#2, 5th Main, 5th Block, Koramangala, Bangalore-560 095. Phone: 299-1886, 299-1897 web:http://www.ceruleaninfotech.com _ Life is a Dream. Dont Wake up. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Very Strange problem.
On Fri, May 03, 2002 at 11:04:29AM +0530, TP R Murthy wrote: > > I have created only one database with 18 tables. Only one of these tables > is MyIsam table. rest are BDB tables. When the server is up, it works > fine. but when i reboot the machine, few of the tables loose all the > records they held before the machine was rebooted. This doesn't happen > everytime. It happens randomly. > > One more thing i noticed is that when i run the 'show table status' it > says 1000 rows in the table that has lost all the records. This will sound dumb, but are you sure you're committing your transactions? Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 84 days, processed 2,193,184,033 queries (299/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Very Strange problem.
Hi, I am new to this list. i could find solution to this problem on the net. i have a RH7.1 512Mb single intel processor. I have installed mysql-3.23.49a-max (binary). I have included the mysql.server script in the init dir and created links to it in rc6, rc5,rc3 and rc1 dir. I have created only one database with 18 tables. Only one of these tables is MyIsam table. rest are BDB tables. When the server is up, it works fine. but when i reboot the machine, few of the tables loose all the records they held before the machine was rebooted. This doesn't happen everytime. It happens randomly. One more thing i noticed is that when i run the 'show table status' it says 1000 rows in the table that has lost all the records. Could someone help me here. help is greatly appreciated. regards, -- _ TP R Murthy (Systems Analyst) Cerulean Information Technology Pvt. Ltd. #24, floor#2, 5th Main, 5th Block, Koramangala, Bangalore-560 095. Phone: 299-1886, 299-1897 web:http://www.ceruleaninfotech.com _ Life is a Dream. Dont Wake up. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange problem
Hi, I work with a rel. 3.23.36 of MYSQL and I have found this problem: I have two tables named t_payments and t_payment_lang; these are their descriptions and data: t_payments (description): +-+---+--+-+-+ + | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+ + | pay_key | int(11) | | PRI | NULL| auto_increment | | pay_enabled | tinyint(1)| YES | | 0 | | | pay_fixed_comm | decimal(18,4) | YES | | 0. | | | pay_perc_comm | decimal(18,4) | YES | | 0. | | | pay_step_height | decimal(18,4) | YES | | 0. | | | pay_step_width | decimal(18,4) | YES | | 0. | | | pay_tax_id | int(11) | YES | | 1 | | | pay_default | tinyint(1)| YES | | NULL| | | pay_en_download | tinyint(1)| YES | | NULL| | +-+---+--+-+-+ + t_payments (data, some fields) +-+-++-+-+ | pay_key | pay_enabled | pay_fixed_comm | pay_default | pay_en_download | +-+-++-+-+ | 1 | 1 | 0. | 1 | 1 | | 2 | 1 | 0. | 0 | 1 | | 3 | 0 | 0. | 0 |NULL | | 4 | 1 | 0. | 1 |NULL | +-+-++-+-+ t_payment_lang (description): +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | pyl_key | int(11) | | PRI | NULL| auto_increment | | pyl_pay_id | int(11) | | | 0 || | pyl_lang_id | int(11) | | | 0 || | pyl_description | varchar(100) | YES | | NULL|| +-+--+--+-+-++ t_payment_lang (data) +-++-+--+ | pyl_key | pyl_pay_id | pyl_lang_id | pyl_description | +-++-+--+ | 1 | 1 | 1 | Cash on delivery | | 17 | 2 | 1 | Credit Card | | 18 | 3 | 1 | Credit Card 2| +-++-+--+ The result of this query: SELECT pay_key, pyl_description, pay_en_download FROMt_payments,t_payment_lang WHERE t_payments.pay_key = t_payment_lang.pyl_pay_id AND t_payment_lang.pyl_lang_id = 1 AND t_payments.pay_enabled <> 0; is: +-+--+-+ | pay_key | pyl_description | pay_en_download | +-+--+-+ | 1 | Cash on delivery |NULL | | 2 | Credit Card |NULL | +-+--+-+ the correct (aspected!) result is: +-+--+-+ | pay_key | pyl_description | pay_en_download | +-+--+-+ | 1 | Cash on delivery | 1 | | 2 | Credit Card | 1 | +-+--+-+ I obtain the correct result if i write the same query in this way: SELECT pay_key, pyl_description, pay_en_download FROMt_payments LEFT JOIN t_payment_lang ON t_payments.pay_key = t_payment_lang.pyl_pay_id WHERE t_payment_lang.pyl_lang_id = 1 AND t_payments.pay_enabled <> 0; or if I drop the column pay_fixed_comm column from table t_payments. I don't understand why ! Thanks for any advice. Luca - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange problem: some data is partially missing (BUG?)
Hi, When i use the mysqlclient to view some data from the table KLASSEN i get some strange partially missing data. like this: select DISTINCTROW * from KLASSEN where KlasseID='Y_FILTER' order by Kenmerk; +--+-+--+ | KlasseID | Kenmerk | Omschrijving | +--+-+--+ |ILTER | 1 | Figuurnummer Alkmaar |ILTER | 2 | Figuurnummer Hoboken |TER | 3 | Figuurnummer ERIKS || 4 | Materiaal huis |Y_FILTER | 5 | Materiaalkwaliteit huis |Y_FILTER | 6 | Materiaal filterelement | 7 | Fabrikant |TER | 8 | Codering fabrikant |ILTER | 9 | Afmeting aansluiting |ER | 10 | Uitw buisdiameter | 11 | Aansluiting 1 | 12 | Aansluiting 2 |R | 13 | Druktrap artikel || 14 | Druktrap flens |ER | 15 | Beschermingswijze |ILTER | 16 | Materiaal afdichting | 17 | Incl aftapper | 18 | Incl element |ER | 19 | Inbouwlengte [mm] | 20 | Maaswijdte +--+-+--+ 20 rows in set (0.00 sec) While it should be like this : select DISTINCTROW * from KLASSEN where KlasseID='Y_FILTER' order by Kenmerk; +--+-+--+ | KlasseID | Kenmerk | Omschrijving | +--+-+--+ | Y_FILTER | 1 | Figuurnummer Alkmaar | Y_FILTER | 2 | Figuurnummer Hoboken | Y_FILTER | 3 | Figuurnummer ERIKS | Y_FILTER | 4 | Materiaal huis | Y_FILTER | 5 | Materiaalkwaliteit huis | Y_FILTER | 6 | Materiaal filterelement | Y_FILTER | 7 | Fabrikant | Y_FILTER | 8 | Codering fabrikant | Y_FILTER | 9 | Afmeting aansluiting | Y_FILTER | 10 | Uitw buisdiameter | Y_FILTER | 11 | Aansluiting 1 | Y_FILTER | 12 | Aansluiting 2 | Y_FILTER | 13 | Druktrap artikel | Y_FILTER | 14 | Druktrap flens | Y_FILTER | 15 | Beschermingswijze | Y_FILTER | 16 | Materiaal afdichting | Y_FILTER | 17 | Incl aftapper | Y_FILTER | 18 | Incl element | Y_FILTER | 19 | Inbouwlengte [mm] | Y_FILTER | 20 | Maaswijdte +--+-+--+ Describe KLASSEN : +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | KlasseID | varchar(64) | | | | | | Kenmerk | int(11) | | MUL | 0 | | | Omschrijving | varchar(64) | | | | | +--+-+--+-+-+---+ 3 rows in set (0.00 sec) When i do the query from a PHP script i get the correct data though !? How can i solve this? and what is the cause? OR what have we done wrong? Kind regards, Gerry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Strange Problem !
On Sun, Sep 02, 2001 at 02:01:36AM +0200, Daniel Urstöger wrote: > Hiya ! > > I am not new with mysql, but I guess my experience is not enough for that > kind of problem ;) > mysql runs fine now the last 3 weeks, but now I am running into problems. > I want to restart mysql with the /etc/rc.d/init.d/mysql stop skript, but it > doesn´t done so .. > Killing mysqld with pid > kill: usage: kill [-s sigspec | -n signum | -sigspec] [pid | job]... or > kill -l [sigspec] > > hmm, I thought ... > Restarted the server. > Still the same ! > Suddenly a tabel of my mysql db is broken .. > I cannot fix it, always when I want to recreate it from new, mysql returns > error 28 ! (0) infidel |root| /root_ perror 28 Error code 28: No space left on device Check your filesystems! df -h -- Any technology distinguishable from magic is insufficiently advanced. [EMAIL PROTECTED] TopQuark Software & Serv. Contract programmer, server bum. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange Problem !
Hiya ! I am not new with mysql, but I guess my experience is not enough for that kind of problem ;) mysql runs fine now the last 3 weeks, but now I am running into problems. I want to restart mysql with the /etc/rc.d/init.d/mysql stop skript, but it doesn´t done so .. Killing mysqld with pid kill: usage: kill [-s sigspec | -n signum | -sigspec] [pid | job]... or kill -l [sigspec] hmm, I thought ... Restarted the server. Still the same ! Suddenly a tabel of my mysql db is broken .. I cannot fix it, always when I want to recreate it from new, mysql returns error 28 ! No matter if my adding a new database, or just a new table :( I am running mysql Ver 11.15 Distrib 3.23.40, for pc-linux-gnu (i686) and this is the first time my having such problems with mysql ;) I hope there is a way to fix that kind of problem ! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange problem
When submitting a form to insert/replace data into my database with values loaded from the previos page I show that everything is entered except for these fields: Why? I am using version mysql-3.23.32-1.7 | Field| Type | Null | Key | Default | Extra | rating | varchar(5) | YES | | NULL || instrumental | char(1) | | | N || favorite | char(2) | YES | | N || Here are some sample values I entered: Rating: *** instrumental: N Favorite: N Here is the REPLACE part: $query = "REPLACE INTO songs VALUES ('$id', '$songname','$instrumental','$beat','$mood','$rating','$favorite','$video', '$album_id', '$movie', '$newfilename', '$bitrate')"; Here is the form part Rating: Favorite: I am using urlencode on the previos page to pass the previos values: $instrumental=urlencode($instrumental); and they click on this link which is where the values get assigned and they appear in the form: When I submit it shows the values of each variable and they are correct a href='updatesongrecord.php?id=$id&songname=$songname&instrumental=$instrumental&beat=$beat&mood=$mood&rating=$rating&favorite=$favorite&video=$video&album_id=$album_id&movie=$movie&newfilename=$newfilename'>"; ?>Update Song Record: - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange problem in installation
Hi, there is somethng strange in installing mysql using my own made charset. Configuration and compilation is going ok. But after that I can't use mysql not as root not as aother user... When I do safe_mysqld --skip-grant & all is Ok (logical) I can change privileges make drop databases, tables. When these changes is made I flushh all and try to start mysqld as normal, but there is the same problen i cant login an do anything with mysql database, it allows all users login without pass (strange) but when do use mysql (as root) permissio denied for root@localhost to database mysql... After more times installation I try to configure and compile with default character set (latin1). All is going OK all grants is working ok. The quiestion is in what measera character set affect mysql databases and tables -- Best regards, dodge mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Strange problem with Count(*)
On Wed, May 30, 2001 at 07:55:28PM +, [EMAIL PROTECTED] wrote: > I am not sure if I am doing something wrong in mySQL, but I have > tested the following in Microsoft SQL, Access and Oracle it works > fine in those environments, but unfortunately, it does not work in > mySQL. > > I have a simple query, such as: > > select name, count(*) from tbl_users group by name; > > In the mySQL client, this query returns the proper results. However, > if I create an ASP page to deliver the results it does not work. It > returns 0 records. However, if I remove count(*) from the query, it > will then return the records just not in the format I want it. Like > I said, this works fine in Microsoft SQL, Access and Oracle. Hm. It's been about 2 years since I cared about ASP, but maybe I can help... > My ASP code is as follows: > > <% > > set oRS = server.createobject("ADODB.Recordset") > set oDB = server.createobject("ADODB.Connection") > > oDB.Open "DSN=" > > sSQL = "SELECT name, count(*) from tbl_users group by name;" How about sSQL = "SELECT name, count(*) as CNT from tbl_users group by name;" > oRS.Open sSQL, oDB > if not oRS.EOF then >while not oRS.EOF > response.write oRS("NAME") and response.write oRS("CNT") ? Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 5 days, processed 32,847,635 queries (72/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange problem with Count(*)
I am not sure if I am doing something wrong in mySQL, but I have tested the following in Microsoft SQL, Access and Oracle it works fine in those environments, but unfortunately, it does not work in mySQL. I have a simple query, such as: select name, count(*) from tbl_users group by name; In the mySQL client, this query returns the proper results. However, if I create an ASP page to deliver the results it does not work. It returns 0 records. However, if I remove count(*) from the query, it will then return the records just not in the format I want it. Like I said, this works fine in Microsoft SQL, Access and Oracle. My ASP code is as follows: <% set oRS = server.createobject("ADODB.Recordset") set oDB = server.createobject("ADODB.Connection") oDB.Open "DSN=" sSQL = "SELECT name, count(*) from tbl_users group by name;" oRS.Open sSQL, oDB if not oRS.EOF then while not oRS.EOF response.write oRS("NAME") oRS.MoveNext wend else response.write "No Records Found" end if oRS.Close oDb.Close set oRS = nothing set oDB = nothing %> Any suggestions or assistance on this would be greatly appreciated. Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange problem.
Hi! > "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes: Peter> Hello , Peter> Few time ago I've started to get the following problems, which several Peter> times allready lead to data lost for me. Peter> Here is the example: Peter> I found the followings in my application error log: Peter> Query - #1017 [opal.local] - Can't find file: 'g04stat_languages.MYD' (errno: 2) Peter> select visitors,loads,id ,hits from layers_st45.g04stat_languages where layer_id=19573 Peter> 05.Apr 16:30:28 : pid:5224 h -- 65972/0 AllInOne rec:0 l:0 tm:1627.20 # Peter> 05.Apr 16:30:28 : pid:5224 h 65972/0 [03.23 17:10] - [01.01 02:59] q:3.73 i:0.00 a:3.26 # Peter> This is not the worst case - in several tables I had .MYD file to be Peter> not existant, but TMD file left - so I just rename it and it works Peter> find. One or two times I didn't find even TMD file so I had to look Peter> at my backups. This indicates that there is some bug when running with --myisam-recover The only way I can see that the above would happen is if MySQL got killed hard between 2 system calls (not likely) or if there is a bug in the function: mysys/my_redel.c The my_redel() functions should delete the .MYD file and then rename the .TMD file as the .MYD file. I can't however see how this could ever go wrong. There could of course be a bug where the rename system call fails We could fix this for system that has the rename call (like Linux) by doing the following change in my_redel.c: } else if (my_delete(org_name,MyFlags)) goto end; if (my_rename(tmp_name,org_name,MyFlags)) goto end; -> #ifndef HAVE_RENAME else if (my_delete(org_name,MyFlags)) goto end; #endif if (my_rename(tmp_name,org_name,MyFlags)) goto end; Peter, could you try the above and check if this helps you solve this problem ? Peter> Looking at the table I found They are usually distributed in one table Peter> group - for example I get many from layers_stXX.gXX* lost there no Peter> over tables lost, so I think this is related to my purge program (this Peter> is still mysql bug anyway :)) ). The thing is after deleting some data Peter> from table the program uses REPAIR table - this is from the times Peter> then optimize table was several times slower. Peter> I'm using mysql 3.23.36, and nothing is changed in system, only mysql, Peter> also I had this problem with mysql 3.23.32 if I'm not mistaken. Peter> I'll try to make an repeatable example but I'm not shure I'll be able Peter> to do so as this does not happens to all tables this program work with Peter> - this may happen only in some specific cases - may be then other Peter> program is working with the table or something. Try using the following test program as a base for this: I have during the previous week run the above program for days without ANY problems in REPAIR; This probably only means that you are doing something different compared to this program. If we can find this out, we will know a lot more! Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange problem.
Hello , Few time ago I've started to get the following problems, which several times allready lead to data lost for me. Here is the example: I found the followings in my application error log: Query - #1017 [opal.local] - Can't find file: 'g04stat_languages.MYD' (errno: 2) select visitors,loads,id ,hits from layers_st45.g04stat_languages where layer_id=19573 05.Apr 16:30:28 : pid:5224 h -- 65972/0 AllInOne rec:0 l:0 tm:1627.20 # 05.Apr 16:30:28 : pid:5224 h 65972/0 [03.23 17:10] - [01.01 02:59] q:3.73 i:0.00 a:3.26 # So If I check mysql it's running quite well and long: opal:/spylog/layers # mysqladmin status Uptime: 255087 Threads: 74 Questions: 28129440 Slow queries: 1453 Opens: 225540 Flush tables: 1 Open tables: 1024 Queries per second avg: 110.274 opal:/spylog/layers # The logfile contains NO errors or snything about this I went to look what is wrong with the file: The problem is index file is gone. And there are MUCH such situations - I get several files lost each day. opal:/spylog/db/layers_st45 # ls -la g04stat_lang* -rw--- 1 mysqldaemon1473241 Mar 26 18:54 g04stat_languages.MYD -rw--- 1 mysqldaemon 8664 Jan 12 19:09 g04stat_languages.frm This is not the worst case - in several tables I had .MYD file to be not existant, but TMD file left - so I just rename it and it works find. One or two times I didn't find even TMD file so I had to look at my backups. Looking at the table I found They are usually distributed in one table group - for example I get many from layers_stXX.gXX* lost there no over tables lost, so I think this is related to my purge program (this is still mysql bug anyway :)) ). The thing is after deleting some data from table the program uses REPAIR table - this is from the times then optimize table was several times slower. I'm using mysql 3.23.36, and nothing is changed in system, only mysql, also I had this problem with mysql 3.23.32 if I'm not mistaken. I'll try to make an repeatable example but I'm not shure I'll be able to do so as this does not happens to all tables this program work with - this may happen only in some specific cases - may be then other program is working with the table or something. -- Best regards, Peter mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php