Query returns to many results
Greetings All, Please have a look at the following query: SELECT abm.mem_number, abm.first_name, abm.last_name, abm.area_represented, abm.age, abm.sex, abm.cup, ablb.*, ablb.jp + ablb.rc + ablb.fsmgp + ablb.gmc + ablb.saly + ablb.nwgp + ablb.ecgp + ablb.sams + ablb.wcc + ablb.kzngp + ablb.emc + ablb.lmgp + ablb.saff + ablb.gmgp + ablb.safy + ablb.mmw + ablb.lc + ablb.mmc + ablb.nwmc + ablb.ncc + ablb.samp + ablb.gsc + ablb.wcmgp + ablb.sapm + ablb.kznc + ablb.npc + ablb.smc + ablb.ecc + ablb.mgp + ablb.samo + ablb.cofc + ablb.cs + ablb.ncmgp + ablb.fsc + ablb.ggp + ablb.tmc + ablb.gc + ablb.yotm AS total_points FROM ab_leader_board ablb, ab_members abm WHERE abm.sex = 'Female' AND abm.cup = 'kids' ORDER BY total_points DESC Now this query is run over two tables and the ab_members table contains around 302 rows. Around 1/3 of these will be where cup=kids. However, when this query is run it returns 20,700 results :0 Any idea why this is? Also, any help or pointers as to how I can optimize this query will be much appreciated. Thank you! -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query returns to many results
Schalk , You need to specify the unifying column between your ablb and abm tables. ie - in your where, and ablb.id=abm.id Once you get this so it returns expected results, you can run the query, prefaced with explain and it will give you an idea on the way mysql is running the query. This has helped me determine some additional indexes that greatly speed up my queries. -- George - Original Message - From: Schalk [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, February 23, 2006 6:59 AM Subject: Query returns to many results Greetings All, Please have a look at the following query: SELECT abm.mem_number, abm.first_name, abm.last_name, abm.area_represented, abm.age, abm.sex, abm.cup, ablb.*, ablb.jp + ablb.rc + ablb.fsmgp + ablb.gmc + ablb.saly + ablb.nwgp + ablb.ecgp + ablb.sams + ablb.wcc + ablb.kzngp + ablb.emc + ablb.lmgp + ablb.saff + ablb.gmgp + ablb.safy + ablb.mmw + ablb.lc + ablb.mmc + ablb.nwmc + ablb.ncc + ablb.samp + ablb.gsc + ablb.wcmgp + ablb.sapm + ablb.kznc + ablb.npc + ablb.smc + ablb.ecc + ablb.mgp + ablb.samo + ablb.cofc + ablb.cs + ablb.ncmgp + ablb.fsc + ablb.ggp + ablb.tmc + ablb.gc + ablb.yotm AS total_points FROM ab_leader_board ablb, ab_members abm WHERE abm.sex = 'Female' AND abm.cup = 'kids' ORDER BY total_points DESC Now this query is run over two tables and the ab_members table contains around 302 rows. Around 1/3 of these will be where cup=kids. However, when this query is run it returns 20,700 results :0 Any idea why this is? Also, any help or pointers as to how I can optimize this query will be much appreciated. Thank you! -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1016 : cant open ibd file even though it exists
Hi Rithish, I'm all out of ideas with this one, sorry I can't be of more help. Perhaps Mr Tuuri or others with more nouse than myself can help. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Thursday, 23 February 2006 5:02 PM To: Logan, David (SST - Adelaide); MySQL general mailing list Subject: RE: error 1016 : cant open ibd file even though it exists Hello David. Thanks for the prompt response. The permissions were the first thing that I checked when I got the error. In fact, I even tried giving 777 permissions on the .ibd files. No results. Regards, Rithish. -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 10:42 AM To: Rithish Saralaya; MySQL general mailing list Subject: RE: error 1016 : cant open ibd file even though it exists Hi Rithish, Please check your ownership/permissions hambone ~ $ perror 1 OS error code 1: Not owner hambone ~ $ Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Thursday, 23 February 2006 3:43 PM To: MySQL general mailing list Subject: error 1016 : cant open ibd file even though it exists Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 1016 : cant open ibd file even though it exists
Try to help Please give us the ls -l result of this file, also make sure that the owner of mysql process could read this file how to test? Try to su to mysql user, and try to open the file, using cat or other command If you can't, maybe parent directory is not permitted this user to read , then fix it Please also give us lsattr result of this file, maybe file attribute not permitt mysql user to read this file Good luck Logan, David (SST - Adelaide) wrote: Hi Rithish, I'm all out of ideas with this one, sorry I can't be of more help. Perhaps Mr Tuuri or others with more nouse than myself can help. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Thursday, 23 February 2006 5:02 PM To: Logan, David (SST - Adelaide); MySQL general mailing list Subject: RE: error 1016 : cant open ibd file even though it exists Hello David. Thanks for the prompt response. The permissions were the first thing that I checked when I got the error. In fact, I even tried giving 777 permissions on the .ibd files. No results. Regards, Rithish. -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 10:42 AM To: Rithish Saralaya; MySQL general mailing list Subject: RE: error 1016 : cant open ibd file even though it exists Hi Rithish, Please check your ownership/permissions hambone ~ $ perror 1 OS error code 1: Not owner hambone ~ $ Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Thursday, 23 February 2006 3:43 PM To: MySQL general mailing list Subject: error 1016 : cant open ibd file even though it exists Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Encrypted column example
When 5.0 came out, I saw an article or white paper or something with an example of a stored procedure (I think, maybe a trigger) that allowed you to transparently encrypt a column so that even if a database backup was stolen, that column wouldn't be exposed to the thieves. I believe that the example was for a SSN or credit card number. Now that I want it, I can't find it again. I've searched MySQL.com without success. Does anyone else remember it? Can someone point me to it? Thanks. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1016 : cant open ibd file even though it exists
ls -l results for the file. -rwxrwxrwx1 mysqlmysql5863636992 Feb 19 05:20 TBL_FORUM_MSG_BODY.ibd Tried su to mysql user. able to cat the above file. lsattr results for the file. - TBL_FORUM_MSG_BODY.ibd Hope that helps. Regards, Rithish. -Original Message- From: Ady Wicaksono [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 5:50 PM To: Logan, David (SST - Adelaide) Cc: Rithish Saralaya; MySQL general mailing list Subject: Re: error 1016 : cant open ibd file even though it exists Try to help Please give us the ls -l result of this file, also make sure that the owner of mysql process could read this file how to test? Try to su to mysql user, and try to open the file, using cat or other command If you can't, maybe parent directory is not permitted this user to read , then fix it Please also give us lsattr result of this file, maybe file attribute not permitt mysql user to read this file Good luck Logan, David (SST - Adelaide) wrote: Hi Rithish, I'm all out of ideas with this one, sorry I can't be of more help. Perhaps Mr Tuuri or others with more nouse than myself can help. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Thursday, 23 February 2006 5:02 PM To: Logan, David (SST - Adelaide); MySQL general mailing list Subject: RE: error 1016 : cant open ibd file even though it exists Hello David. Thanks for the prompt response. The permissions were the first thing that I checked when I got the error. In fact, I even tried giving 777 permissions on the .ibd files. No results. Regards, Rithish. -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 10:42 AM To: Rithish Saralaya; MySQL general mailing list Subject: RE: error 1016 : cant open ibd file even though it exists Hi Rithish, Please check your ownership/permissions hambone ~ $ perror 1 OS error code 1: Not owner hambone ~ $ Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Thursday, 23 February 2006 3:43 PM To: MySQL general mailing list Subject: error 1016 : cant open ibd file even though it exists Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query returns to many results
George Law wrote: Schalk , You need to specify the unifying column between your ablb and abm tables. ie - in your where, and ablb.id=abm.id Once you get this so it returns expected results, you can run the query, prefaced with explain and it will give you an idea on the way mysql is running the query. This has helped me determine some additional indexes that greatly speed up my queries. -- George - Original Message - From: Schalk [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, February 23, 2006 6:59 AM Subject: Query returns to many results Greetings All, Please have a look at the following query: SELECT abm.mem_number, abm.first_name, abm.last_name, abm.area_represented, abm.age, abm.sex, abm.cup, ablb.*, ablb.jp + ablb.rc + ablb.fsmgp + ablb.gmc + ablb.saly + ablb.nwgp + ablb.ecgp + ablb.sams + ablb.wcc + ablb.kzngp + ablb.emc + ablb.lmgp + ablb.saff + ablb.gmgp + ablb.safy + ablb.mmw + ablb.lc + ablb.mmc + ablb.nwmc + ablb.ncc + ablb.samp + ablb.gsc + ablb.wcmgp + ablb.sapm + ablb.kznc + ablb.npc + ablb.smc + ablb.ecc + ablb.mgp + ablb.samo + ablb.cofc + ablb.cs + ablb.ncmgp + ablb.fsc + ablb.ggp + ablb.tmc + ablb.gc + ablb.yotm AS total_points FROM ab_leader_board ablb, ab_members abm WHERE abm.sex = 'Female' AND abm.cup = 'kids' ORDER BY total_points DESC Now this query is run over two tables and the ab_members table contains around 302 rows. Around 1/3 of these will be where cup=kids. However, when this query is run it returns 20,700 results :0 Any idea why this is? Also, any help or pointers as to how I can optimize this query will be much appreciated. Thank you! Thanks George! It works perfectly. Now to optimize this bugger. -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How stable will mysqld [compiled from source] be on linux 2.4 with glibc 2.3.2 (gcc 3.3.4)?
If I compile mysql from sources with gcc 3.3.4 on linux 2.4 with glibc 2.3.2, how stable will mysqld be with lots (more than 500) of simultaneous connections? Does the issue described in the documentation for glibc 2.2 (big default STACK_SIZE causes mysqld instablity) affect glibc 2.3 as well? Thank you. Denis Solovyov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What binary to choose with glibc 2.3.2 on 2.4 linux kernel?
I'm in doubt. What binary (not RPM) should I choose for linux 2.4 with glibc 2.3.2? Actually, I believe that 'Linux (x86, glibc-2.2, standard is static, gcc)' (mysql-standard-5.0.18-linux-i686.tar.gz)and 'Linux (x86)' (mysql-standard-5.0.18-linux-i686-glibc23.tar.gz) will both work, so my main question is: Is Linux (x86) binary statically linked with glibc 2.3? Or maybe I should choose the one compiled with Intel C/C++ compiler? Is it static? When one should choose it instead of gcc-compiled? Thank you. Denis Solovyov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 1016 : cant open ibd file even though it exists
Rithish, the table definition does not exist in the ibdata file. You have the .frm file and the .ibd file, but that does not help if the table definition is not stored in the ibdata file. How did you end up in this situation? Did you move .frm and .ibd files around? Did you recreate the ibdata1 file? Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . List: mysql Subject:error 1016 : cant open ibd file even though it exists From: Rithish Saralaya rithish.saralaya () tallysolutions ! com Date: 2006-02-22 11:27:44 Message-ID: ANEAKJJGBMNHIAEBLIAIMEPCECAA.rithish.saralaya () tallysolutions ! com [Download message RAW] Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Different length of MyIsam index files.
After some problem on the index of a tables defined therefore: CREATE TABLE `artmain` ( `art_codice` varchar(22) character set latin1 collate latin1_bin NOT NULL default '', `art_descr` varchar(60) NOT NULL default '', `art_suppl` int(8) NOT NULL default '0', `art_origi` char(1) NOT NULL default '', `art_codfam` varchar(6) NOT NULL default '', `art_segnale` varchar(10) NOT NULL default '', `art_vettura` varchar(10) NOT NULL default '', `art_qtaconf` float(8,3) NOT NULL default '0.000', `art_catalogo` varchar(30) NOT NULL default '', `art_peso` float(5,3) NOT NULL default '0.000', `art_catego` varchar(4) NOT NULL default '', `sot_catcodice` varchar(4) NOT NULL default '', `aggiorna` int(1) NOT NULL default '0', PRIMARY KEY (`art_codice`,`art_suppl`), KEY `CATEGORIE` (`art_catego`,`sot_catcodice`,`art_suppl`), KEY `art_descr` (`art_descr`,`art_suppl`), KEY `SUPPLIER` (`art_suppl`,`art_codice`), FULLTEXT KEY `descrizione` (`art_descr`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I made a dump of the table and a successive LOAD DATA to recharge and to reconstruct the table. The situation after LOAD DATA was: seattle:/var/lib/mysql/db # ls -l artmain* -rw-rw-rw- 1 mysql mysql 426799972 Feb 19 10:52 artmain.MYD -rw-rw-rw- 1 mysql mysql 789188608 Feb 19 12:52 artmain.MYI -rw-rw-rw- 1 mysql mysql 9078 Feb 19 10:54 artmain.frm And myisamchk brought back: seattle:/var/lib/mysql/db # myisamchk -i --verbose artmain Checking MyISAM file: artmain Data records: 8204471 Deleted blocks: 0 - check file-size - check record delete-chain No recordlinks - check key delete-chain block_size 1024: - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 65% Packed: 60% Max levels: 5 - check data record references index: 2 Key: 2: Keyblocks used: 87% Packed:0% Max levels: 5 - check data record references index: 3 Key: 3: Keyblocks used: 56% Packed: 84% Max levels: 5 - check data record references index: 4 Key: 4: Keyblocks used: 62% Packed: 72% Max levels: 5 - check data record references index: 5 Key: 5: Keyblocks used: 51% Packed: 96% Max levels: 5 Total:Keyblocks used: 63% Packed: 87% - check record links Records: 8204471M.recordlength: 47 Packed: 69% Recordspace used: 98% Empty space: 1% Blocks/Record: 1.00 Record blocks: 8204471Delete blocks: 0 Record data: 390139470Deleted data: 0 Lost space:5950757Linkdata: 30709745 User time 59.20, System time 57.80 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 812, Physical pagefaults 18, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 318102, Involuntary context switches 2071 I used the database for many hours with massive INSERT, UPDATE and REPLACE operations and the table's situation was: -rw-rw 1 mysql mysql 426899256 Feb 23 01:02 artmain.MYD -rw-rw 1 mysql mysql 490489856 Feb 23 01:58 artmain.MYI --- !!! -rw-rw 1 mysql mysql 9078 Feb 17 20:14 artmain.frm #myisamchk -i --verbose artmain Checking MyISAM file: artmain Data records: 8208200 Deleted blocks: 0 - check file-size - check record delete-chain No recordlinks - check key delete-chain block_size 1024: - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 98% Packed: 60% Max levels: 4 - check data record references index: 2 Key: 2: Keyblocks used: 97% Packed:0% Max levels: 5 - check data record references index: 3 Key: 3: Keyblocks used: 98% Packed: 84% Max levels: 5 - check data record references index: 4 Key: 4: Keyblocks used: 98% Packed: 72% Max levels: 4 - check data record references index: 5 Key: 5: Keyblocks used: 92% Packed: 96% Max levels: 4 Total:Keyblocks used: 96% Packed: 88% - check record links Records: 8208200M.recordlength: 47 Packed: 69% Recordspace used: 98% Empty space: 1% Blocks/Record: 1.00 Record blocks: 8208200Delete blocks: 0 Record data: 390223321Deleted data: 0 Lost space:5952093Linkdata: 30723842 User time 138.63, System time 31.30 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 830, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 2220, Involuntary context switches 9079 The final records number is correct and the procedure using the table, it does not give errors. How it is possibile that the index has become smaller than about 200MB, if the records are increased? - Linux version 2.6.11.4-20a-smp ([EMAIL PROTECTED]) (gcc version 3.3.5 20050117 (prerelease) (SUSE Linux)) #1 SMP Wed Mar 23 21:52:37 UTC 2005 - mysql Ver 14.7 Distrib 4.1.10a, for suse-linux (i686) thanks for the aid -- Pasquale D'Orsi
Re: Help with a join query
Yoed Anis wrote: Hi all, I'm trying to do the following. I have three table: Table a has address information: address_id | City | State | Zip 1Austin TX 78758 2 Dallas TX 77000 3 Galveston TX 77550 Table b has information about the location: address_id | Location_id | Location_name 11The Place 12The Place Before 23A shop Table c has montlhy sales history Locationid | MonthYear | Sales 12005-01-01 299 12005-02-01100 12005-10-01300 22005-01-01 154 32005-10-1099 Not every location has sales information. I am trying to create a query where I can SELECT the Locationname, City, State, Zip, and the SUM(sales) if the place has sales. So far, despite playing around with joins for more hours than one should ever dedicated to the matter, I haven't been able to include SUM(sales) without excluding listings without sales. So far this is my best shot: SELECT locationname, city, state, zip, SUM(sales) as 'Sales' FROM a, b LEFT JOIN c ON (b.locationid = c.locationid) WHERE a.address_id = b.address_id AND monthyear 2005-01-01 GROUP BY c.locationid SELECT locationname, city, state, zip, SUM(sales) as 'Sales' FROM a INNER JOIN b ON a.address_id = b.address_id LEFT JOIN c ON b.locationid = c.locationid and monthyear2005-01-10 GROUP BY c.locationid This however, will return only records with Sales and not those without it. I haven't been able to force adding empty rows from table c... Doing AND c.locationid IS NULL returns no results at all. Any help would GREATLY be appreciated!!! Thank you!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner join with left join
James Harvard [EMAIL PROTECTED] wrote on 02/22/2006
08:53:56 PM:
At 5:08 pm -0800 22/2/06, Scott Haneda wrote:
I think we are close, thanks
ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON
conditions
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM Products p
LEFT JOIN orders as o
ON (p.id = oi.product_id)
Maybe this is where your problem is - you're joining to orders but
referencing order_items in your join condition. Shurely shome mishtake?*
AND o.created BETWEEN 2005-01-01 00:00:00 AND
2006-02-22 23:59:59
AND o.status not IN ('cancelled', 'pending', 'ghost')
LEFT JOIN order_items as oi
ON (o.id = oi.order_id)
GROUP BY p.id
ORDER by qty ASC
* ask a Brit, or consult http://en.wikipedia.
org/wiki/Private_Eye#Examples_of_humour
You're right. It was a dumb cut-and-paste mistake.
LEFT JOIN orders as o
on o.product_id = p.id
If fixing this doesn't give the correct results: What's missing? What's
incorrect? Please help us to help you.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Query returns to many results
Schalk [EMAIL PROTECTED] wrote on 02/23/2006 08:55:01 AM: George Law wrote: Schalk , You need to specify the unifying column between your ablb and abm tables. ie - in your where, and ablb.id=abm.id Once you get this so it returns expected results, you can run the query, prefaced with explain and it will give you an idea on the way mysql is running the query. This has helped me determine some additional indexes that greatly speed up my queries. -- George - Original Message - From: Schalk [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, February 23, 2006 6:59 AM Subject: Query returns to many results Greetings All, Please have a look at the following query: SELECT abm.mem_number, abm.first_name, abm.last_name, abm.area_represented, abm.age, abm.sex, abm.cup, ablb.*, ablb.jp + ablb.rc + ablb.fsmgp + ablb.gmc + ablb.saly + ablb.nwgp + ablb.ecgp + ablb.sams + ablb.wcc + ablb.kzngp + ablb.emc + ablb.lmgp + ablb.saff + ablb.gmgp + ablb.safy + ablb.mmw + ablb.lc + ablb.mmc + ablb.nwmc + ablb.ncc + ablb.samp + ablb.gsc + ablb.wcmgp + ablb.sapm + ablb.kznc + ablb.npc + ablb.smc + ablb.ecc + ablb.mgp + ablb.samo + ablb.cofc + ablb.cs + ablb.ncmgp + ablb.fsc + ablb.ggp + ablb.tmc + ablb.gc + ablb.yotm AS total_points FROM ab_leader_board ablb, ab_members abm WHERE abm.sex = 'Female' AND abm.cup = 'kids' ORDER BY total_points DESC Now this query is run over two tables and the ab_members table contains around 302 rows. Around 1/3 of these will be where cup=kids. However, when this query is run it returns 20,700 results :0 Any idea why this is? Also, any help or pointers as to how I can optimize this query will be much appreciated. Thank you! Thanks George! It works perfectly. Now to optimize this bugger. -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers Schalk, You wouldn't have even run into this as an issue if you had used the explicit JOIN form. Again, I blame the documentation for only demonstrating the lazy form of INNER JOIN declaration almost exclusively. I believe that by only demonstrating the comma-separated join, they have created the impression that it is a preferred method. I strongly discourage the use of that form of declaring table joins for the very reason you posted. If you had used the explicit form: SELECT ...(all of your columns)... FROM ab_leader_board ablb INNER JOIN ab_members abm ON ablb.id=abm.id (or whatever is appropriate) WHERE ... It should have be intuitively obvious that you had left out the ON clause from your original query. As it was, your missing JOIN conditions were just not noticed because of all of the other activity in your whole statement. This is a very frequent problem with the join syntax you used in your original query. Again, I implore all SQL coders to use the explicit JOIN syntax on all platforms that support it (Oracle being a well-known exception). It makes it much easier to catch logical errors just like Schalk ran into in his original post. The explicit form is also the only way to declare outer joins in MySQL so you will have to use it sooner or later. Please, again, I ask the documentation team to modify the SQL examples in the manual (especially in the tutorial section) to use the explicit JOIN forms. Humbly yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Query returns to many results
Again, I implore all SQL coders to use the explicit JOIN syntax on all platforms that support it (Oracle being a well-known exception). It makes Oracle supports the ANSI JOIN syntax from v9 and up. Shawn Green As for the rest, I fully agree. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query help?
I am a novice when it come to queries such as this and was hoping someone could help me write a query that tells me how many records have the same ID and vendor number. |ID | vendor_no | date| |2354 | 578 | 2005-12-23| |2355 | 334 | 2005-12-24| |2356 | 339 | 2005-12-26| |2357 | 339 | 2005-12-26| |2358 | 339 | 2005-12-26| |2359 | 445 | 2005-12-26| |2354 | 522 | 2005-12-27| |2355 | 522 | 2005-12-27| Would I use select count? Any help would be greatly appreciated. A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower.
RE: Number Searches
Probably the problem is in php, or, more probably, in how you store first and then look for the IP address in your query. You should try your query in the mysql console; varchars work almost with anything and I put this example where I look for an IP address with your table, and it finds it correctly. Hope this helps; if you can't find the problem, try little steps with select * from portal_forums_users where ip = '192.168.1.0'; To try and find where you have a problem. You can even try select * from portal_forums_users where ip like '%192.168.1.0%'; The % are wildcards, and that would take care of periods you inadvertenly added/erased. I really think this is not a MySQL problem. mysql describe portal_forums_users; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | user_id | bigint(255) | | PRI | NULL| auto_increment | | ip| varchar(200) | YES | | NULL|| | signup_date | varchar(30) | YES | | NULL|| | city | varchar(200) | YES | MUL | NULL|| | state | varchar(100) | YES | | NULL|| | email_address | varchar(200) | YES | | NULL|| | username | varchar(100) | YES | | NULL|| | password | varchar(100) | YES | | NULL|| | yim | text | YES | | NULL|| | aol | text | YES | | NULL|| | web_url | text | YES | | NULL|| | post_count| varchar(255) | YES | | NULL|| | info | text | YES | | NULL|| | sig | text | YES | | NULL|| | avatar| text | YES | | NULL|| | css_id| int(11) | YES | | NULL|| | mod_f | varchar(20) | YES | | NULL|| | admin | varchar(20) | YES | | NULL|| +---+--+--+-+-++ 18 rows in set (0.00 sec) mysql select * from portal_forums_users; +-+-+-+--+---+---+--+--+--+--+-+ | user_id | ip | signup_date | city | state | email_address | username | password | yim | aol | web_url | post_count +-+-+-+--+---+---+--+--+--+--+-+ | 1 | 192.168.1.0 | x | x| x | x | x | x| x | x| x | x | x| x| x | 0 | x | x | | 2 | 10.100.1.1 | y | y| y | y | y | y| y| y| y | y +-+-+-+--+---+---+--+--+--+--+-+ 2 rows in set (0.00 sec) mysql select * from portal_forums_users where ip = '192.168.1.0'; +-+-+-+--+---+---+--+--+--+--+-+ | user_id | ip | signup_date | city | state | email_address | username | password | yim | aol | web_url | post_count +-+-+-+--+---+---+--+--+--+--+-+ | 1 | 192.168.1.0 | x | x| x | x | x | x| x | x| x | x | x| x| x | 0 | x | x | +-+-+-+--+---+---+--+--+--+--+-+ 1 row in set (0.00 sec) -Mensaje original- De: CodeHeads [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 22 de febrero de 2006 17:35 Para: Ariel Sánchez Mora CC: mysql@lists.mysql.com Asunto: RE: Number Searches On Wed, 2006-02-22 at 16:49 -0600, Ariel Sánchez Mora wrote: So far i've been able to store ip addresses as strings like you would type them in DOS, for ex, '192.168.0.1'. This serves me great since my application uses IP addresses as strings in all cases. I've done queries with the IP column , for example, select office_name from table_1 where ip='10.100.1.1'; and have never had any problems. However, if you plan on sorting based on this column, strings with periods do not behave correctly, and the answers to my previous question on this list do not apply; it makes a good aproximation, though. Hope this helps, but I must admit I am not sure if this answers your question. An example in the mysql console would be great for
Re: query help?
I am a novice when it come to queries such as this and was hoping someone could help me write a query that tells me how many records have the same ID and vendor number. |ID| vendor_no| date| |2354 | 578 | 2005-12-23| |2355 | 334 | 2005-12-24| |2356 | 339 | 2005-12-26| |2357 | 339 | 2005-12-26| |2358 | 339 | 2005-12-26| |2359 | 445 | 2005-12-26| |2354 | 522 | 2005-12-27| |2355 | 522 | 2005-12-27| Would I use select count? Any help would be greatly appreciated. I'd need a clearer spec to offer advise. What results would you want from the example data? 2354 is there twice but with different vendor numbers. And 522 is there twice with different IDs. One interpretation of [records with] the same ID and vendor number is 0 because no record has both the same as any other. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query help?
Richard, If you mean with _both_ the same id _and_ vendor id, try this: Select id, vendor_id, count(*) from tablename group by id, vendor_id; If you just want separate counts for id and vendor_id, use: Select id, count(*) from tablename group by id; Select vendor_id, count(*) from tablename group by vendor_id; Andy -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: 23 February 2006 16:48 To: mysql@lists.mysql.com Subject: query help? I am a novice when it come to queries such as this and was hoping someone could help me write a query that tells me how many records have the same ID and vendor number. |ID | vendor_no | date| |2354 | 578 | 2005-12-23| |2355 | 334 | 2005-12-24| |2356 | 339 | 2005-12-26| |2357 | 339 | 2005-12-26| |2358 | 339 | 2005-12-26| |2359 | 445 | 2005-12-26| |2354 | 522 | 2005-12-27| |2355 | 522 | 2005-12-27| Would I use select count? Any help would be greatly appreciated. A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
linking rows
Let's say I have a query that performs select * from Account one of the columns from Account (city) is actually a key which may be either numeric or character. There is a table called City which contains keys and names of cities. and I want the City.Name value where Account.city matches City.ID. I need the most efficient way to do this: examples, keywords, and URLs to relevant documentation are all welcome. thanks tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.18 Client - FreeBSD Build
Where are the client libraries and such for the FreeBSD 4.x Build of MySQL 4.1.18? I downloaded the MAX binary yesterday, and tried to install it, but none of the libmysqlclient files are present in the distribution. Do I need to download the sources and build it, or are the client files located somewhere else on the web site, etc... I dug for some time and couldn't find anything. Normally the FreeBSD folks make packages for this kind of stuff, but I need the latest build for a 4.x machine and there are only ports for 6.x these days. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Number Searches
On Thu, 2006-02-23 at 10:49 -0600, Ariel Sánchez Mora wrote: Probably the problem is in php, or, more probably, in how you store first and then look for the IP address in your query. You should try your query in the mysql console; varchars work almost with anything and I put this example where I look for an IP address with your table, and it finds it correctly. Hope this helps; if you can't find the problem, try little steps with select * from portal_forums_users where ip = '192.168.1.0'; To try and find where you have a problem. You can even try select * from portal_forums_users where ip like '%192.168.1.0%'; The % are wildcards, and that would take care of periods you inadvertenly added/erased. I really think this is not a MySQL problem. mysql describe portal_forums_users; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | user_id | bigint(255) | | PRI | NULL| auto_increment | | ip| varchar(200) | YES | | NULL|| | signup_date | varchar(30) | YES | | NULL|| | city | varchar(200) | YES | MUL | NULL|| | state | varchar(100) | YES | | NULL|| | email_address | varchar(200) | YES | | NULL|| | username | varchar(100) | YES | | NULL|| | password | varchar(100) | YES | | NULL|| | yim | text | YES | | NULL|| | aol | text | YES | | NULL|| | web_url | text | YES | | NULL|| | post_count| varchar(255) | YES | | NULL|| | info | text | YES | | NULL|| | sig | text | YES | | NULL|| | avatar| text | YES | | NULL|| | css_id| int(11) | YES | | NULL|| | mod_f | varchar(20) | YES | | NULL|| | admin | varchar(20) | YES | | NULL|| +---+--+--+-+-++ 18 rows in set (0.00 sec) mysql select * from portal_forums_users; +-+-+-+--+---+---+--+--+--+--+-+ | user_id | ip | signup_date | city | state | email_address | username | password | yim | aol | web_url | post_count +-+-+-+--+---+---+--+--+--+--+-+ | 1 | 192.168.1.0 | x | x| x | x | x | x| x | x| x | x | x| x| x | 0 | x | x | | 2 | 10.100.1.1 | y | y| y | y | y | y| y| y| y | y +-+-+-+--+---+---+--+--+--+--+-+ 2 rows in set (0.00 sec) mysql select * from portal_forums_users where ip = '192.168.1.0'; +-+-+-+--+---+---+--+--+--+--+-+ | user_id | ip | signup_date | city | state | email_address | username | password | yim | aol | web_url | post_count +-+-+-+--+---+---+--+--+--+--+-+ | 1 | 192.168.1.0 | x | x| x | x | x | x| x | x| x | x | x| x| x | 0 | x | x | +-+-+-+--+---+---+--+--+--+--+-+ 1 row in set (0.00 sec) -Mensaje original- De: CodeHeads [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 22 de febrero de 2006 17:35 Para: Ariel Sánchez Mora CC: mysql@lists.mysql.com Asunto: RE: Number Searches On Wed, 2006-02-22 at 16:49 -0600, Ariel Sánchez Mora wrote: So far i've been able to store ip addresses as strings like you would type them in DOS, for ex, '192.168.0.1'. This serves me great since my application uses IP addresses as strings in all cases. I've done queries with the IP column , for example, select office_name from table_1 where ip='10.100.1.1'; and have never had any problems. However, if you plan on sorting based on this column, strings with periods do not behave correctly, and the answers to my previous question on this list do not apply; it makes a good aproximation,
Re: MySQL 4.1.18 Client - FreeBSD Build
On Thu, 2006-02-23 at 09:28 -0800, Don O'Neil wrote: Where are the client libraries and such for the FreeBSD 4.x Build of MySQL 4.1.18? I downloaded the MAX binary yesterday, and tried to install it, but none of the libmysqlclient files are present in the distribution. Do I need to download the sources and build it, or are the client files located somewhere else on the web site, etc... I dug for some time and couldn't find anything. Normally the FreeBSD folks make packages for this kind of stuff, but I need the latest build for a 4.x machine and there are only ports for 6.x these days. You should still be able to download source code prebuilt packages for older releases on ftp-archive.freebsd.org. - Julian -- Julian C. Dunn Systems Administrator e: [EMAIL PROTECTED] p: 416-363-6316 x292 f: 416-363-6102 Devlin eBusiness Architects 185 Frederick St. Toronto, ON M5A 4L4 http://www.devlin.ca/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1.18 Client - FreeBSD Build
Yeah, I know about that... But the 4.1.18 client/server isn't there.. Only 4.0.7 or some such older version. Thanks! -Original Message- From: Julian C. Dunn [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 9:39 AM To: Don O'Neil Cc: mysql@lists.mysql.com Subject: Re: MySQL 4.1.18 Client - FreeBSD Build On Thu, 2006-02-23 at 09:28 -0800, Don O'Neil wrote: Where are the client libraries and such for the FreeBSD 4.x Build of MySQL 4.1.18? I downloaded the MAX binary yesterday, and tried to install it, but none of the libmysqlclient files are present in the distribution. Do I need to download the sources and build it, or are the client files located somewhere else on the web site, etc... I dug for some time and couldn't find anything. Normally the FreeBSD folks make packages for this kind of stuff, but I need the latest build for a 4.x machine and there are only ports for 6.x these days. You should still be able to download source code prebuilt packages for older releases on ftp-archive.freebsd.org. - Julian -- Julian C. Dunn Systems Administrator e: [EMAIL PROTECTED] p: 416-363-6316 x292 f: 416-363-6102 Devlin eBusiness Architects 185 Frederick St. Toronto, ON M5A 4L4 http://www.devlin.ca/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner join with left join
You're right. It was a dumb cut-and-paste mistake.
LEFT JOIN orders as o
on o.product_id = p.id
If fixing this doesn't give the correct results: What's missing? What's
incorrect? Please help us to help you.
Orders does not have a product_id column.
Let me see if I can explain this again, more better :-)
We have orders and order items, so for every orders, there are 1 or more
order items, pretty basic. This SQL gets me almost what I want:
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY oi.product_id
However, there are
mysql select count(*) from products;
+--+
| count(*) |
+--+
| 109 |
+--+
1 row in set (0.00 sec)
So, 109 products in the products database, the first SQL above, will give me
back a row for every order item that meets those criteria, however, it does
not list products that were not ordered.
If I changed the first SQL to a date 10 years ago, I would get 0 rows, I
want 109 where the sum() is all 0.
Basically, my client is wanting to see what products are selling, and which
ones are not, in a certain date range, and I need to add in the status to
limit it to only certain orders.
Running these three SQL's does what I want, with a temp table, but I find
the solution kinda strange, and know it can be done in one go:
CREATE TEMPORARY TABLE prod_report
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY oi.product_id
INSERT INTO prod_report (SELECT id, prod_name, 0 FROM products)
SELECT * FROM prod_report GROUP BY id ORDER BY prod_name
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: linking rows
Tim Johnson [EMAIL PROTECTED] wrote on 02/23/2006 12:26:35 PM: Let's say I have a query that performs select * from Account one of the columns from Account (city) is actually a key which may be either numeric or character. There is a table called City which contains keys and names of cities. and I want the City.Name value where Account.city matches City.ID. I need the most efficient way to do this: examples, keywords, and URLs to relevant documentation are all welcome. thanks tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com First off, columns can either be numeric or character-based not both. You can store arrangements of the characters 0 through 9 in a character-based field but those are not numbers, they are strings that look like numbers. Your description makes it sound like you have a table that has data in a column called 'city' that looks like: Atlanta Boston 15 10 24 Paris Rome 215 Tokyo or am I mistaken? Back to your direct question: How you link two tables is called joining. There are tons of examples and tutorials of how to join tables. One of my favorites is http://sqlzoo.net/ It takes you through everything you need in order to get your feet wet and it gives you the ability to immediately try out what you are being taught. If you work through their examples you should be able to make some decent headway. I also recommend you read this article about normalization: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html There is a query that can do what you want but I strongly suggest you review your design before moving too much farther into this project. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: query help?
I's so sorry. You are very correct. The sample data is bad. ID should be unique. Here it is corrected. |ID| vendor_no| date| |2354 | 578 | 2005-12-23| |2355 | 334 | 2005-12-24| |2356 | 339 | 2005-12-26| |2357 | 339 | 2005-12-26| |2358 | 339 | 2005-12-26| |2359 | 445 | 2005-12-26| |2360 | 522 | 2005-12-27| |2361 | 522 | 2005-12-27| [EMAIL PROTECTED] wrote: I am a novice when it come to queries such as this and was hoping someone could help me write a query that tells me how many records have the same ID and vendor number. |ID| vendor_no| date| |2354 | 578 | 2005-12-23| |2355 | 334 | 2005-12-24| |2356 | 339 | 2005-12-26| |2357 | 339 | 2005-12-26| |2358 | 339 | 2005-12-26| |2359 | 445 | 2005-12-26| |2354 | 522 | 2005-12-27| |2355 | 522 | 2005-12-27| Would I use select count? Any help would be greatly appreciated. I'd need a clearer spec to offer advise. What results would you want from the example data? 2354 is there twice but with different vendor numbers. And 522 is there twice with different IDs. One interpretation of [records with] the same ID and vendor number is 0 because no record has both the same as any other. A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower.
Re: Inner join with left join
Sorry - I am trying to cut back to just 2 pots of coffee per day and I the
lack of caffeine can make me a little fuzzy :-) Thank you for being
patient with me.
You have a working query, we just need to convert your INNER JOINs to
LEFT JOINs and move your join-specific WHERE conditions into the correct
ON clauses
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
AND o.status NOT IN ('cancelled', 'pending', 'ghost')
AND (o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY p.id, p.prod_name
By placing a restriction in the WHERE clause, you are requiring a value
exist in that column after the JOINs are computed. That is why you have
been throwing out all unsold products before you even got to the GROUP BY
stage. You cannot group on values that aren't going to be there so I
moved the two important columns of your SELECT statement back to the
products table (SELECT p.id, p.prod_name ...) and made sure that those
were the values you were grouping by.
Again, Thanks!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Haneda [EMAIL PROTECTED] wrote on 02/23/2006 12:45:28 PM:
You're right. It was a dumb cut-and-paste mistake.
LEFT JOIN orders as o
on o.product_id = p.id
If fixing this doesn't give the correct results: What's missing?
What's
incorrect? Please help us to help you.
Orders does not have a product_id column.
Let me see if I can explain this again, more better :-)
We have orders and order items, so for every orders, there are 1 or more
order items, pretty basic. This SQL gets me almost what I want:
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY oi.product_id
However, there are
mysql select count(*) from products;
+--+
| count(*) |
+--+
| 109 |
+--+
1 row in set (0.00 sec)
So, 109 products in the products database, the first SQL above, will
give me
back a row for every order item that meets those criteria, however, it
does
not list products that were not ordered.
If I changed the first SQL to a date 10 years ago, I would get 0 rows, I
want 109 where the sum() is all 0.
Basically, my client is wanting to see what products are selling, and
which
ones are not, in a certain date range, and I need to add in the status
to
limit it to only certain orders.
Running these three SQL's does what I want, with a temp table, but I
find
the solution kinda strange, and know it can be done in one go:
CREATE TEMPORARY TABLE prod_report
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY oi.product_id
INSERT INTO prod_report (SELECT id, prod_name, 0 FROM products)
SELECT * FROM prod_report GROUP BY id ORDER BY prod_name
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
Re: query help?
If you are looking just for duplicate (ID,vendort_no) combinations, this will find them: SELECT ID, vendor_no, count(1) as dupes FROM table_name_here GROUP BY ID, vendor_no HAVING dupes 1; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Richard Reina [EMAIL PROTECTED] wrote on 02/23/2006 12:49:28 PM: I's so sorry. You are very correct. The sample data is bad. ID should be unique. Here it is corrected. |ID| vendor_no| date| |2354 | 578 | 2005-12-23| |2355 | 334 | 2005-12-24| |2356 | 339 | 2005-12-26| |2357 | 339 | 2005-12-26| |2358 | 339 | 2005-12-26| |2359 | 445 | 2005-12-26| |2360 | 522 | 2005-12-27| |2361 | 522 | 2005-12-27| [EMAIL PROTECTED] wrote: I am a novice when it come to queries such as this and was hoping someone could help me write a query that tells me how many records have the same ID and vendor number. |ID| vendor_no| date| |2354 | 578 | 2005-12-23| |2355 | 334 | 2005-12-24| |2356 | 339 | 2005-12-26| |2357 | 339 | 2005-12-26| |2358 | 339 | 2005-12-26| |2359 | 445 | 2005-12-26| |2354 | 522 | 2005-12-27| |2355 | 522 | 2005-12-27| Would I use select count? Any help would be greatly appreciated. I'd need a clearer spec to offer advise. What results would you want from the example data? 2354 is there twice but with different vendor numbers. And 522 is there twice with different IDs. One interpretation of [records with] the same ID and vendor number is 0 because no record has both the same as any other. A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower.
Re: Inner join with left join
I hate remembering crap like this AFTER I hit send...
Because we want to limit our sum() to only those rows that match the ORDER
conditionals, we have to change our formula to recognized when to count
and when to not count an order_item.
SELECT p.id, p.prod_name, sum(if(o.id is null,0,oi.quantity)) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
AND o.status NOT IN ('cancelled', 'pending', 'ghost')
AND (o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY p.id, p.prod_name
I think I need a nap! --- SORRY!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
[EMAIL PROTECTED] wrote on 02/23/2006 01:01:17 PM:
Sorry - I am trying to cut back to just 2 pots of coffee per day and I
the
lack of caffeine can make me a little fuzzy :-) Thank you for being
patient with me.
You have a working query, we just need to convert your INNER JOINs to
LEFT JOINs and move your join-specific WHERE conditions into the correct
ON clauses
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
AND o.status NOT IN ('cancelled', 'pending', 'ghost')
AND (o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY p.id, p.prod_name
By placing a restriction in the WHERE clause, you are requiring a value
exist in that column after the JOINs are computed. That is why you have
been throwing out all unsold products before you even got to the GROUP
BY
stage. You cannot group on values that aren't going to be there so I
moved the two important columns of your SELECT statement back to the
products table (SELECT p.id, p.prod_name ...) and made sure that those
were the values you were grouping by.
Again, Thanks!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Haneda [EMAIL PROTECTED] wrote on 02/23/2006 12:45:28 PM:
You're right. It was a dumb cut-and-paste mistake.
LEFT JOIN orders as o
on o.product_id = p.id
If fixing this doesn't give the correct results: What's missing?
What's
incorrect? Please help us to help you.
Orders does not have a product_id column.
Let me see if I can explain this again, more better :-)
We have orders and order items, so for every orders, there are 1 or
more
order items, pretty basic. This SQL gets me almost what I want:
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY oi.product_id
However, there are
mysql select count(*) from products;
+--+
| count(*) |
+--+
| 109 |
+--+
1 row in set (0.00 sec)
So, 109 products in the products database, the first SQL above, will
give me
back a row for every order item that meets those criteria, however, it
does
not list products that were not ordered.
If I changed the first SQL to a date 10 years ago, I would get 0 rows,
I
want 109 where the sum() is all 0.
Basically, my client is wanting to see what products are selling, and
which
ones are not, in a certain date range, and I need to add in the status
to
limit it to only certain orders.
Running these three SQL's does what I want, with a temp table, but I
find
the solution kinda strange, and know it can be done in one go:
CREATE TEMPORARY TABLE prod_report
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2006-02-23 00:00:00 AND 2006-02-23 23:59:59)
GROUP BY oi.product_id
INSERT INTO prod_report (SELECT id, prod_name, 0 FROM products)
SELECT * FROM prod_report GROUP BY id ORDER BY prod_name
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
Re: query help?
Actually I am looking for duplicates (vedor_no, date), but I think I can hopefully adapt the solution you have given me. [EMAIL PROTECTED] wrote: If you are looking just for duplicate (ID,vendort_no) combinations, this will find them: SELECT ID, vendor_no, count(1) as dupes FROM table_name_here GROUP BY ID, vendor_no HAVING dupes 1; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Richard Reina [EMAIL PROTECTED] wrote on 02/23/2006 12:49:28 PM: I's so sorry. You are very correct. The sample data is bad. ID should be unique. Here it is corrected. |ID| vendor_no| date| |2354 | 578 | 2005-12-23| |2355 | 334 | 2005-12-24| |2356 | 339 | 2005-12-26| |2357 | 339 | 2005-12-26| |2358 | 339 | 2005-12-26| |2359 | 445 | 2005-12-26| |2360 | 522 | 2005-12-27| |2361 | 522 | 2005-12-27| [EMAIL PROTECTED] wrote: I am a novice when it come to queries such as this and was hoping someone could help me write a query that tells me how many records have the same ID and vendor number. |ID| vendor_no| date| |2354 | 578 | 2005-12-23| |2355 | 334 | 2005-12-24| |2356 | 339 | 2005-12-26| |2357 | 339 | 2005-12-26| |2358 | 339 | 2005-12-26| |2359 | 445 | 2005-12-26| |2354 | 522 | 2005-12-27| |2355 | 522 | 2005-12-27| Would I use select count? Any help would be greatly appreciated. I'd need a clearer spec to offer advise. What results would you want from the example data? 2354 is there twice but with different vendor numbers. And 522 is there twice with different IDs. One interpretation of [records with] the same ID and vendor number is 0 because no record has both the same as any other. A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower. A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower.
Re: linking rows
* [EMAIL PROTECTED] [EMAIL PROTECTED] [060223 09:09]: Tim Johnson [EMAIL PROTECTED] wrote on 02/23/2006 12:26:35 PM: I need the most efficient way to do this: examples, keywords, and URLs to relevant documentation are all welcome. First off, columns can either be numeric or character-based not both. You can store arrangements of the characters 0 through 9 in a character-based field but those are not numbers, they are strings that look like numbers. Of course. Didn't mean to imply other wish. Your description makes it sound like you have a table that has data in a column called 'city' that looks like: Atlanta Boston 15 10 24 Paris Rome 215 Tokyo or am I mistaken? You got it. Back to your direct question: How you link two tables is called joining. There are tons of examples and tutorials of how to join tables. One of my favorites is Deliberately left out an inquery about join to encourage possibly other suggestions. :-) http://sqlzoo.net/ I also recommend you read this article about normalization: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html Those are the links that I needed. Getting up to speed on 'join is my solution... Thanks (time to do my homework) tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner join with left join
Scott,
If you Left Join to o and oi, and add 'OR oi.product_id IS NULL) to the
WHere clause, I think you have it.
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
LEFT JOIN order_items as oi
ON (p.id = oi.product_id)
LEFT JOIN orders as o
ON (o.id = oi.order_id)
WHERE ( o.status NOT IN ('cancelled', 'pending', 'ghost')
AND o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23
23:59:59" )
OR oi.product_id IS NULL
GROUP BY oi.product_id
PB
-
Scott Haneda wrote:
You're right. It was a dumb cut-and-paste mistake.
LEFT JOIN orders as o
on o.product_id = p.id
If fixing this doesn't give the correct results: What's missing? What's
incorrect? Please help us to help you.
Orders does not have a product_id column.
Let me see if I can explain this again, more better :-)
We have orders and order items, so for every orders, there are 1 or more
order items, pretty basic. This SQL gets me almost what I want:
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59")
GROUP BY oi.product_id
However, there are
mysql select count(*) from products;
+--+
| count(*) |
+--+
| 109 |
+--+
1 row in set (0.00 sec)
So, 109 products in the products database, the first SQL above, will give me
back a row for every order item that meets those criteria, however, it does
not list products that were not ordered.
If I changed the first SQL to a date 10 years ago, I would get 0 rows, I
want 109 where the sum() is all 0.
Basically, my client is wanting to see what products are selling, and which
ones are not, in a certain date range, and I need to add in the status to
limit it to only certain orders.
Running these three SQL's does what I want, with a temp table, but I find
the solution kinda strange, and know it can be done in one go:
CREATE TEMPORARY TABLE prod_report
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59")
GROUP BY oi.product_id
INSERT INTO prod_report (SELECT id, prod_name, 0 FROM products)
SELECT * FROM prod_report GROUP BY id ORDER BY prod_name
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.0.0/267 - Release Date: 2/22/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How stable will mysqld [compiled from source] be on linux 2.4 with glibc 2.3.2 (gcc 3.3.4)?
Denis Solovyov wrote: If I compile mysql from sources with gcc 3.3.4 on linux 2.4 with glibc 2.3.2, how stable will mysqld be with lots (more than 500) of simultaneous connections? Does the issue described in the documentation for glibc 2.2 (big default STACK_SIZE causes mysqld instablity) affect glibc 2.3 as well? Thank you. Denis Solovyov I ran a similar system for quite a while - I didn't have any stability issues, apart from final hardware failure :) I can't tell you specifically about that glibc bug, but if the documentation only mentions 2.2, then you should be fine. Make sure you follow the instructions for compiling ( included in the source distribution ) - specifically the recommended CFLAGS. Some other things to keep in mind: - You would expect the most recent kernel to be the most stable ( most recent 2.4 kernel is fine if you don't want to upgrade to 2.6 ) - The gcc-3.3 series has reached 3.3.6 ... I don't know if there are any bugs fixed between your 3.3.4 the 3.3.6 that would affect you - probably not, but it's possible - MySQL recommends that people use their binaries for maximum performance and stability. If you come across a bug, the first thing they will suggest is that you try one of their binaries -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 1016 : cant open ibd file even though it exists
What are the permissions on the files? Which user runs mysql? If you're not on Windows, is the case the same? When you type mysqld --print-defaults (or whatever your mysql server binary is), what directory shows up under datadir? Is it the same directory? Sincerely, Sheeri On 2/23/06, Rithish Saralaya [EMAIL PROTECTED] wrote: Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need white papers on performace tuning of full text indexing
A simple search on google for mysql fulltext indexing provided many links, including: http://jeremy.zawodny.com/blog/archives/000576.html http://epsilondelta.wordpress.com/2006/02/08/dissecting-mysql-fulltext-indexing/ (overviews of how it works) and http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html (fine tuning full text search) Sincerely, Sheeri On 2/23/06, Anand Sachdev [EMAIL PROTECTED] wrote: anyone know where i can get these, will highly appreciate, this is a feature of mysql 5.0 and my platform is linux. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Permissions
This is probably a simple question. I installed MySQL about a month ago on Mac OS X and I am new to this. During the installation (as root) I created a directory /var/mysql-data. Then, following the installation instructions: #chown mysql:mysql /var/mysql-data #chmod 770 /var/mysql-data The problem is that I am denied access to all of the databases I have created - permission denied except when I connect to the server. When I look in PathFinder, the owner is listed as mysql. NetInfo tells me that I have such a user, that it is the MySQL server. If I gave mysql a password, I do not remember it. I did set up a mysql root with password, and using Navicat I have created another user. Should I not see my databases in /var/mysql-data either in the terminal or the Finder? thanks -walter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Permissions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Walter Johnson wrote: This is probably a simple question. I installed MySQL about a month ago on Mac OS X and I am new to this. During the installation (as root) I created a directory /var/mysql-data. Then, following the installation instructions: #chown mysql:mysql /var/mysql-data #chmod 770 /var/mysql-data The problem is that I am denied access to all of the databases I have created - permission denied except when I connect to the server. When I look in PathFinder, the owner is listed as mysql. NetInfo tells me that I have such a user, that it is the MySQL server. If I gave mysql a password, I do not remember it. I did set up a mysql root with password, and using Navicat I have created another user. Should I not see my databases in /var/mysql-data either in the terminal or the Finder? thanks -walter Hi, Walter - What's the exact error there? We can't tell if you're talking about file permissions, or actual database permissions. Thanks - -dant -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFD/jq2dJpmX+LLzdoRAovMAJoCkwx3E2/yVLrQ3xw7RT4iXkxwHwCeJIbn 8cXh8IEGpNjnXUGLYBepzXw= =MEyX -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Build Backwards Compatible MySQL Client Libs
Hi all... I have some OLD programs I don't have the source for that were built with the MySQL 3.23.55 client libraries. They still work great, even when using those libraries to connect to 4.1.18 Mysql (I have a copy of the old lib in the new lib dir) However, is there some way I can build a 3.X compatible library (the old one is mysqlclient.so.10) with the latest source so I can get all the bug fixes and still be backwards compatible? I tried creating a link to the new lib with the old name, but there is a function call that is missing the old programs complain about when they run. Thanks!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number Searches
CodeHeads a écrit : On Wed, 2006-02-22 at 16:49 -0600, Ariel Sánchez Mora wrote: So far i've been able to store ip addresses as strings like you would type them in DOS, for ex, '192.168.0.1'. This serves me great since my application uses IP addresses as strings in all cases. I've done queries with the IP column , for example, select office_name from table_1 where ip='10.100.1.1'; and have never had any problems. However, if you plan on sorting based on this column, strings with periods do not behave correctly, and the answers to my previous question on this list do not apply; it makes a good aproximation, though. Hope this helps, but I must admit I am not sure if this answers your question. An example in the mysql console would be great for clearing up your objetive. Regards, Ariel OK, I think I did not explain things right the first time. :( I have a table like so: CREATE TABLE `portal_forums_users` ( `user_id` bigint(255) NOT NULL auto_increment, `ip` varchar(200) default NULL, `signup_date` varchar(30) default NULL, `city` varchar(200) default NULL, `state` varchar(100) default NULL, `email_address` varchar(200) default NULL, `username` varchar(100) default NULL, `password` varchar(100) default NULL, `yim` text, `aol` text, `web_url` text, `post_count` varchar(255) default NULL, `info` text, `sig` text, `avatar` text, `css_id` int(11) default NULL, `mod_f` varchar(20) default NULL, `admin` varchar(20) default NULL, PRIMARY KEY (`user_id`), FULLTEXT KEY `full_index` (`city`,`state`,`username`,`email_address`,`ip`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Notice the FULLTEXT; I have the ip listed to be indexed. Using a PHP script I have it will not grab the IP that I am searching for, even though it *is* in the database. The ip's are entered into the database as 192.168.1.10. When I search for a username it works great. Is it because of the . (periods) in the search string?? Hopefully I explained that right this time!! :) LOL Ok I got 2 informations for you: 1) IPv4 address are actually 32 bit integer, easily store in 32 bits fast search etc etc etc (You can google for more on this storage format). Normally you could find a way to goes from the string 192.168.1.1 to the equivalent int. Look for ip2long() function in PHP for example! 2) FULLTEXT indexes are a special type of index in MySQL, their use on numeric field doesn't make sense. To be used on ip string they would require some tweaking as they normally don't remember word under 3 letters if i'm correct. And last but not least they aren't use with a like but with a match You could however use an typical index here, or even better an unique index to ensure the validation! Hope it helps you in you development! See for all information about fulltext index in the manual http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
event notification to client
Hi, there, I am new to Mysql world, please forgive me if the question sounds dumb. I am looking for if it is possible, that upon a record operation at the database table, a event/notification is sent to a client process. The database we are currently using implement this feature by post a event through the corresponding trigger. You help is appreciated. Thanks Jimmy
Re: Permissions
Dan Trainor wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Walter Johnson wrote: This is probably a simple question. I installed MySQL about a month ago on Mac OS X and I am new to this. During the installation (as root) I created a directory /var/mysql-data. Then, following the installation instructions: #chown mysql:mysql /var/mysql-data #chmod 770 /var/mysql-data The problem is that I am denied access to all of the databases I have created - permission denied except when I connect to the server. When I look in PathFinder, the owner is listed as mysql. NetInfo tells me that I have such a user, that it is the MySQL server. If I gave mysql a password, I do not remember it. I did set up a mysql root with password, and using Navicat I have created another user. Should I not see my databases in /var/mysql-data either in the terminal or the Finder? thanks -walter Hi, Walter - What's the exact error there? We can't tell if you're talking about file permissions, or actual database permissions. Thanks - -dant -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFD/jq2dJpmX+LLzdoRAovMAJoCkwx3E2/yVLrQ3xw7RT4iXkxwHwCeJIbn 8cXh8IEGpNjnXUGLYBepzXw= =MEyX -END PGP SIGNATURE- File permissions. thanks -walter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number Searches
On Thu, 2006-02-23 at 22:04 -0500, Mathieu Bruneau wrote: Ok I got 2 informations for you: 1) IPv4 address are actually 32 bit integer, easily store in 32 bits fast search etc etc etc (You can google for more on this storage format). Normally you could find a way to goes from the string 192.168.1.1 to the equivalent int. Look for ip2long() function in PHP for example! 2) FULLTEXT indexes are a special type of index in MySQL, their use on numeric field doesn't make sense. To be used on ip string they would require some tweaking as they normally don't remember word under 3 letters if i'm correct. And last but not least they aren't use with a like but with a match You could however use an typical index here, or even better an unique index to ensure the validation! Hope it helps you in you development! See for all information about fulltext index in the manual http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html Thanks for the reply :) Yes I was figured that was the problem but I wanted to make sure that was it. I did get it working like Ariel suggested. (where ip='192.168.1.1') That worked. I just did a separate search for the IP's. Thanks again for all your help. -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) signature.asc Description: This is a digitally signed message part
Re: Permissions
On Thu, 2006-02-23 at 21:46 -0600, Walter Johnson wrote: #chown mysql:mysql /var/mysql-data #chmod 770 /var/mysql-data Try this, assuming the files are in there for the database. $chown -R mysql:mysql /var/mysql-data/* $chmod -R 770 /var/mysql-data/* Hope that helps. -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) signature.asc Description: This is a digitally signed message part
RE: error 1016 : cant open ibd file even though it exists
I have tried giving 777 permissions on the files. Nothing happens. All mysql processes run as 'mysql' except mysqld-safe, runs as root. I am not on Windows. All the table names are in upper case. I don't have the lower case setting in my.cnf also. The 'datadir' is /var/lib/mysql/ This is where the mysql is located. That's proper. -Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Friday, February 24, 2006 3:22 AM To: Rithish Saralaya Cc: MySQL general mailing list Subject: Re: error 1016 : cant open ibd file even though it exists What are the permissions on the files? Which user runs mysql? If you're not on Windows, is the case the same? When you type mysqld --print-defaults (or whatever your mysql server binary is), what directory shows up under datadir? Is it the same directory? Sincerely, Sheeri On 2/23/06, Rithish Saralaya [EMAIL PROTECTED] wrote: Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to keep account independent in replication
Hi, How can I keep the account of MySQL independent in replication? We have two MySQL 4.1.18 nodes: A and B. B replicate A. We want that the account in A is independent. That is to said, it would not affect the account in B when we add or delete the account in A. We add the following option in B's my.cnf and use the INSERT or DELETE statement in A to deal with the account management now. replicate-ignore-db=mysql As you can see, it is ugly and discommodious. Is there any better solution? Best regards, Leo Huang
RE: error 1016 : cant open ibd file even though it exists
Hello. The tables were working perfectly fine a week back. The database was created from a sql file generated through the mysqldump utility. So there was nothing wrong with the database. This irregularity happened this week onwards. Our system admins tell us that the server was restarted last weekend. When I dug up the mysql error logs, this was what I found saw. == 060219 5:20:25 InnoDB: Starting shutdown... 060219 5:20:30 InnoDB: Shutdown completed; log sequence number 19 1867461149 060219 5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete Memory status: Non-mmapped space allocated from system: 16589028 Number of free chunks: 10 Number of fastbin blocks:0 Number of mmapped regions: 19 Space in mmapped regions:1472028672 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 16479548 Total free space:109480 Top-most, releasable space: 102224 Estimated memory (with thread stack):1488744676 060219 05:20:30 mysqld ended 060219 16:57:48 mysqld started 060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist: InnoDB: a new database to be created! 060219 16:57:49 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 060219 16:57:50 InnoDB: Log file /var/lib/mysql/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:02 InnoDB: Log file /var/lib/mysql/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:14 InnoDB: Log file /var/lib/mysql/ib_logfile2 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile2 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 060219 16:58:28 InnoDB: Started; log sequence number 0 0 /usr/sbin/mysqld: ready for connections. Version: '4.1.11-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) = So... It shows that the ibdata1 file was recreated... But how can that be possible? when it was a regular server shutdown and startup? Regards, Rithish. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 7:52 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, the table definition does not exist in the ibdata file. You have the .frm file and the .ibd file, but that does not help if the table definition is not stored in the ibdata file. How did you end up in this situation? Did you move .frm and .ibd files around? Did you recreate the ibdata1 file? Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . List: mysql Subject:error 1016 : cant open ibd file even though it exists From: Rithish Saralaya rithish.saralaya () tallysolutions ! com Date: 2006-02-22 11:27:44 Message-ID: ANEAKJJGBMNHIAEBLIAIMEPCECAA.rithish.saralaya () tallysolutions ! com [Download message RAW] Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail.
Re: Query returns to many results
Now this query is run over two tables and the ab_members table contains around 302 rows. Around 1/3 of these will be where cup=kids. However, when this query is run it returns 20,700 results That's because your ... FROM ab_leader_board ablb, ab_members abm calls for a cross join--it asks for every logically possible combination of ablb and abm rows. From the rest of your query, it appears you need something like ... FROM ab_leader_board ablb INNER JOIN ab_members abm USING (name_of_joining_column) Also, do you really mean to sum all those ablb column values after having already called for all ablb column values with ablb.* ? PB - Schalk wrote: Greetings All, Please have a look at the following query: SELECT abm.mem_number, abm.first_name, abm.last_name, abm.area_represented, abm.age, abm.sex, abm.cup, ablb.*, ablb.jp + ablb.rc + ablb.fsmgp + ablb.gmc + ablb.saly + ablb.nwgp + ablb.ecgp + ablb.sams + ablb.wcc + ablb.kzngp + ablb.emc + ablb.lmgp + ablb.saff + ablb.gmgp + ablb.safy + ablb.mmw + ablb.lc + ablb.mmc + ablb.nwmc + ablb.ncc + ablb.samp + ablb.gsc + ablb.wcmgp + ablb.sapm + ablb.kznc + ablb.npc + ablb.smc + ablb.ecc + ablb.mgp + ablb.samo + ablb.cofc + ablb.cs + ablb.ncmgp + ablb.fsc + ablb.ggp + ablb.tmc + ablb.gc + ablb.yotm AS total_points FROM ab_leader_board ablb, ab_members abm WHERE abm.sex = 'Female' AND abm.cup = 'kids' ORDER BY total_points DESC Now this query is run over two tables and the ab_members table contains around 302 rows. Around 1/3 of these will be where cup=kids. However, when this query is run it returns 20,700 results :0 Any idea why this is? Also, any help or pointers as to how I can optimize this query will be much appreciated. Thank you! No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.0.0/267 - Release Date: 2/22/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlhotcopy
Hello, I read the manual(http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html) and especially: Back up tables in the given database that match a regular expression: shell mysqlhotcopy db_name./regex/ The regular expression for the table name can be negated by prefixing it with a tilde (‘~’): shell mysqlhotcopy db_name./~regex/ I want to backup all my tables except two -'rtt' and 'expirations'. I use /usr/local/bin/mysqlhotcopy -p mypass --allowold mydb./~expirations/~rtt/ /var/backups/mysqlbackup/$1 However for some reason mysqlhotcopy do NOT skip tables 'rtt' and 'expirations'. Please give me an idea what is wrong. Thanks :-)) Kind regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlhotcopy
solved :) Thanks :-) Peter wrote: Hello, I read the manual(http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html) and especially: Back up tables in the given database that match a regular expression: shell mysqlhotcopy db_name./regex/ The regular expression for the table name can be negated by prefixing it with a tilde (‘~’): shell mysqlhotcopy db_name./~regex/ I want to backup all my tables except two -'rtt' and 'expirations'. I use /usr/local/bin/mysqlhotcopy -p mypass --allowold mydb./~expirations/~rtt/ /var/backups/mysqlbackup/$1 However for some reason mysqlhotcopy do NOT skip tables 'rtt' and 'expirations'. Please give me an idea what is wrong. Thanks :-)) Kind regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1016 : cant open ibd file even though it exists
Sounds like you have any empty database, from the messages below. Try adding a new dummy database, and some test data. See if you can do some selects on that test data. Keith In theory, theory and practice are the same; In practice they are not. On Fri, 24 Feb 2006, Rithish Saralaya wrote: To: MySQL general mailing list mysql@lists.mysql.com From: Rithish Saralaya [EMAIL PROTECTED] Subject: RE: error 1016 : cant open ibd file even though it exists Hello. The tables were working perfectly fine a week back. The database was created from a sql file generated through the mysqldump utility. So there was nothing wrong with the database. This irregularity happened this week onwards. Our system admins tell us that the server was restarted last weekend. When I dug up the mysql error logs, this was what I found saw. == 060219 5:20:25 InnoDB: Starting shutdown... 060219 5:20:30 InnoDB: Shutdown completed; log sequence number 19 1867461149 060219 5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete Memory status: Non-mmapped space allocated from system: 16589028 Number of free chunks: 10 Number of fastbin blocks: 0 Number of mmapped regions: 19 Space in mmapped regions: 1472028672 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 16479548 Total free space: 109480 Top-most, releasable space:102224 Estimated memory (with thread stack):1488744676 060219 05:20:30 mysqld ended 060219 16:57:48 mysqld started 060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist: InnoDB: a new database to be created! 060219 16:57:49 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 060219 16:57:50 InnoDB: Log file /var/lib/mysql/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:02 InnoDB: Log file /var/lib/mysql/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:14 InnoDB: Log file /var/lib/mysql/ib_logfile2 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile2 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 060219 16:58:28 InnoDB: Started; log sequence number 0 0 /usr/sbin/mysqld: ready for connections. Version: '4.1.11-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) = So... It shows that the ibdata1 file was recreated... But how can that be possible? when it was a regular server shutdown and startup? Regards, Rithish. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 7:52 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, the table definition does not exist in the ibdata file. You have the .frm file and the .ibd file, but that does not help if the table definition is not stored in the ibdata file. How did you end up in this situation? Did you move .frm and .ibd files around? Did you recreate the ibdata1 file? Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . List: mysql Subject:error 1016 : cant open ibd file even though it exists From: Rithish Saralaya rithish.saralaya () tallysolutions ! com Date: 2006-02-22 11:27:44 Message-ID: ANEAKJJGBMNHIAEBLIAIMEPCECAA.rithish.saralaya () tallysolutions ! com [Download message RAW] Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot
