Re: Show Master Status
In infinite wisdom David Giragosian [EMAIL PROTECTED] spoke thus: But you've asked the right question without a doubt. I'm sure there'll be a next time... Next time when you keep seeing inserts even after stopping all the writes, turn on the general query log (--log or add log=filename in the mysqld section) and then check the hosts that are sending the queries. -- raj shekhar facts: http://rajshekhar.net opinions: http://rajshekhar.net/blog I've never made anyone's life easier and you know it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: Why dont my query use the index keys?
Hi joerg, that was a excellent explanation. Regards, Pradeep Chandru. Joerg Bruehe wrote: Hi ! Parikh, Dilip Kumar schrieb: Hi , So you are trying to say that 1) when the Table has Low Cardinality, Mysql wont use Index? Is this the logic behind your words? Extreme example: If you are manually looking up one entry from a list of five (say, in a cookbook), would you go through the index or just scan sequentially ? And also do you mean that the select query without index will be faster than that of the select query with Index? I just don't believe it. Then what is the purpose of Index?? Please clarify... Another extreme example: If you were to find a list all male soldiers in a typical army, would you go through an index on sex or just scan the payroll list, skipping the female ones ? For both cases, the logic is: Going through an index causes some overhead over a sequential scan (access the index, for each match follow the pointer to the real data) which you want (the system) to take only if that overhead is less than the overhead of scanning the base data and skipping the non-matches. Typically, both the index and the base data might be arranged sequentially, so scanning to the next entry is cheap, but following a reference from the index to some base record is a random access which is costly. So using the index is efficient only if the cost of (find matches in index) + ((hit rate) * (random data access)) is less than that of (sequential data scan). I have seen a 15 % hit rate used as a rule of thumb: If that optimizer expected a hit rate of more than 15 % (better: a selectivity worse than 15 %), it did not use the index at all but scan the base table. The reasoning was that sequentially scanning 6 - 7 entries (possibly using some read-ahead, disk caches etc) costs less than accessing one data record randomly. Regards, Jörg ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: MicroOLAP Database Designer for MySQL 1.9.7 released
Greetings! We are glad to announce MicroOLAP Database Designer for MySQL 1.9.7 (September 16, 2008) release. This release introduces ability to create partitioned tables for MySQL 5.1+. There are a lot of minor improvements and bugfixes. Changes history: [!] MySQL 5.1+ table partitioning support added [+] Several Test Data Generator bugs fixed, performance improved [+] on update CURRENT_TIMESTAMP clause support for TIMESTAMP columns [+] Ability to limit ENUM/SET items number when displaying column datatype on diagram [+] Columns in SQL Result grid are sortable now [+] Ability to set character set and collation for particular columns added [+] Reverse Engineering from non-standard MySQL builds improved [*] Characterset and collation lists are adopted for latest MySQL versions [-] MEMO-field becomes varchar(0) after MS Access Reverse Engineering bug fixed [-] Select All from context menu doesn't select tables bug fixed [-] Tables on diagram are not redrawn after domains properties change bug fixed [-] Model doesn't refresh itself after page size changing bug fixed [-] Column properties are not updated after changing domain in Column Manager bug fixed [-] Create Object context menu may add object to model with wrong coordinates bug fixed [-] Changing diagram pages has no immediately effect on the display of the Minimap Navigator bug fixed [-] Minor syntax highlighting improvements [-] Minor bugs in SQL Executor You're welcome to download the Database Designer for MySQL 1.9.7 right now at: http://microolap.com/products/database/mysql-designer/download/ Login to your private area on our site at http://microolap.com/my/keys/ to obtain your key if you have a license. Please don't hesitate to ask any questions or report bugs with our Support Ticketing system available at http://www.microolap.com/support/ -- MicroOLAP Technologies Team -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
too many connections
Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED]
Re: too many connections
Restart MySQL server On 9/19/08, Kinney, Gail [EMAIL PROTECTED] wrote: Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: too many connections
On Fri, Sep 19, 2008 at 9:07 PM, Olexandr Melnyk [EMAIL PROTECTED] wrote: Restart MySQL server On 9/19/08, Kinney, Gail [EMAIL PROTECTED] wrote: Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. run the following from shell, mysqladmin flush-hosts and edit my.cnf and add or change the value ( max_connections by default is 100 ) max_connections=500 save my.cnf and restart mysql.
RE: too many connections
in my.cnf configuration file try upping the number of connections max_connections=3072 to max_connections=6144 Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Date: Fri, 19 Sep 2008 09:33:58 -0600 Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] _ Want to do more with Windows Live? Learn “10 hidden secrets” from Jamie. http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008
Re: too many connections
On 9/19/08, Kinney, Gail [EMAIL PROTECTED] wrote: Hello, We have MySQL 4.0.14 and have just gotten an error: Please help. Answer the door, 2004 is calling. -- -jp I hope that someday we will be able to put away our fears and prejudices and just laugh at people. deepthoughtsbyjackhandy.com
RE: too many connections
Gail, I know the list has already recommended allowing more connections but the bigger question is what is sucking them all up. Even with 1000 connections things like apache can only use the number of connections that there are processes (* the number of connections used within each process). As a fast workaround, increase the connections but for a long term solution you really need to find out what the problem is, now how to work around it. Gary From: Kinney, Gail [mailto:[EMAIL PROTECTED] Sent: Fri 9/19/2008 8:33 AM To: 'mysql@lists.mysql.com' Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED]
Re: too many connections
In case you're using PHP, in theory all database connections should be closed when script stops execution. I'm not sure if it's always like that in practice. Persistent connections can be a quick fix to your problem, but as was mentioned in the previous mail, it's better to find out why there's so many of them. On 9/19/08, Gary W. Smith [EMAIL PROTECTED] wrote: Gail, I know the list has already recommended allowing more connections but the bigger question is what is sucking them all up. Even with 1000 connections things like apache can only use the number of connections that there are processes (* the number of connections used within each process). As a fast workaround, increase the connections but for a long term solution you really need to find out what the problem is, now how to work around it. Gary From: Kinney, Gail [mailto:[EMAIL PROTECTED] Sent: Fri 9/19/2008 8:33 AM To: 'mysql@lists.mysql.com' Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: too many connections
Then killing the server process should be safe. Except that server startup may take a while. On Fri, Sep 19, 2008 at 7:25 PM, Kinney, Gail [EMAIL PROTECTED]wrote: We are using the default storage engine - INNODB *From:* Olexandr Melnyk [mailto:[EMAIL PROTECTED] *Sent:* Friday, September 19, 2008 9:44 AM *To:* Kinney, Gail *Subject:* Re: too many connections Are there any UPDATE queries being executed? Which storage engines are you using? On 9/19/08, *Kinney, Gail* [EMAIL PROTECTED] wrote: Ok, we tried that but we are getting and error that it can't be stopped (timed out), although status says that it is stopping. Do we need to reboot the entire machine? *From:* Olexandr Melnyk [mailto:[EMAIL PROTECTED] *Sent:* Friday, September 19, 2008 9:40 AM *To:* Kinney, Gail *Subject:* Re: too many connections Yes, that's what I was referring to On 9/19/08, *Kinney, Gail* [EMAIL PROTECTED] wrote: Thanks you for responding. Our MySQL is on a web server for the entire campus. Can we just restart the service? *From:* Olexandr Melnyk [mailto:[EMAIL PROTECTED] *Sent:* Friday, September 19, 2008 9:38 AM *To:* Kinney, Gail; mysql@lists.mysql.com *Subject:* Re: too many connections Restart MySQL server On 9/19/08, *Kinney, Gail* [EMAIL PROTECTED] wrote: Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: too many connections
PHP provides both msql_connect and mysql_pconnect. The former does indeed create a new connection to process each request and closes it auto-magically upon completion. The latter creates a rather half-assed connection pool; once a connection is allocated by PHP, it is held open and reused for subsequent requests. New connections are created if no persistent connection is available. Unfortunately, connections allocated though mysql_pconnect are never closed. If the rate of requests should spike, PHP will potentially allocate every connection and never release them, even after traffic returns to normal, which counter-indicates using the method for any pratiacl web application. Most developers/admins prefer to take the hit and use mysql_connect, opening and closing a connection for each request rather than risk having all connections consumed. - michael dykman On Fri, Sep 19, 2008 at 12:07 PM, Olexandr Melnyk [EMAIL PROTECTED] wrote: In case you're using PHP, in theory all database connections should be closed when script stops execution. I'm not sure if it's always like that in practice. Persistent connections can be a quick fix to your problem, but as was mentioned in the previous mail, it's better to find out why there's so many of them. On 9/19/08, Gary W. Smith [EMAIL PROTECTED] wrote: Gail, I know the list has already recommended allowing more connections but the bigger question is what is sucking them all up. Even with 1000 connections things like apache can only use the number of connections that there are processes (* the number of connections used within each process). As a fast workaround, increase the connections but for a long term solution you really need to find out what the problem is, now how to work around it. Gary From: Kinney, Gail [mailto:[EMAIL PROTECTED] Sent: Fri 9/19/2008 8:33 AM To: 'mysql@lists.mysql.com' Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlimport failed to import
I created a csv file entitled 'disposed.csv' and placed it in computer_inventory data folder with the following inside: 1087 1046 1086 1161 1049 1178 1029 1030 1224 1044 1106 Now I created the table 'disposed' as following: Create disposed ( Mot_id INT(4) UNIQUE NOT NULL ); Then I issued the command inside computer_inventory data folder as follows: Mysqlimport -u root -p computer_inventory disposed.csv And got the error: Mysqlimport: Error: Data truncated for column 'mot_id' at row 1, when using table: disposed What am I doing wrong? [cid:image001.jpg@01C91A66.5935E360] inline: image001.jpg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many connections
You have to use mysql 64bits on S.O. 64bits --Mensaje original-- De: Martin Gainty Para: Kinney, Gail Para: 'mysql@lists.mysql.com' Enviado: 19 Sep 2008 10:51 Asunto: RE: too many connections in my.cnf configuration file try upping the number of connections max_connections=3072 to max_connections=6144 Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Date: Fri, 19 Sep 2008 09:33:58 -0600 Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] _ Want to do more with Windows Live? Learn “10 hidden secrets” from Jamie. http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008 Enviado desde mi BlackBerry de Claro.
Re: too many connections
One thing a lot of people miss is that web server KeepAliveTimeout setting has an effect on pconnect. Apache will keep the thread handling that client open for the KeepAliveTimeout duration, which will keep the database connection open for reuse. You can lower your KeepAliveTimeout or not use pconnect. Brent Baisley On Fri, Sep 19, 2008 at 3:51 PM, Jaime Fuentes [EMAIL PROTECTED] wrote: You have to use mysql 64bits on S.O. 64bits --Mensaje original-- De: Martin Gainty Para: Kinney, Gail Para: 'mysql@lists.mysql.com' Enviado: 19 Sep 2008 10:51 Asunto: RE: too many connections in my.cnf configuration file try upping the number of connections max_connections=3072 to max_connections=6144 Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Date: Fri, 19 Sep 2008 09:33:58 -0600 Subject: too many connections Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. Gail Kinney Webmaster UC Denver [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] _ Want to do more with Windows Live? Learn 10 hidden secrets from Jamie. http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008 Enviado desde mi BlackBerry de Claro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tables_priv and TABLES_PRIVILEGE
I have run across a problem with differences in the mysql.tables_priv table and the INFORMATION_SCHEMA.TABLE_PRIVILEGES tables. My application is currently written to query the information schema for privileges before allowing users to insert or update. The problem is that for some users and some tables the INFORMATION_SCHEMA does not reflect the true privileges in the mysql database. consider the following 2 querys/results mysql select * from information_schema.table_privileges where grantee like '%user1%' and table_name like '%table%'; ++---+--+++--+ | GRANTEE| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE | ++---+--+++--+ | 'user1'@'localhost'| NULL | lu | table1 | SELECT | NO | | 'user1'@'localhost'| NULL | lu | table1 | INSERT | NO | | 'user1'@'localhost'| NULL | lu | table1 | UPDATE | NO | ++---+--+++--+ 3 rows in set (0.11 sec) mysql select * from mysql.tables_priv where user like '%user1%' and table_name like '%table%'; +---++--+++-+--+-+ | Host | Db | User | Table_name| Grantor | Timestamp | Table_priv| Column_priv | +---++--+++-+--+-+ | localhost | lu | user1 | table1 | [EMAIL PROTECTED] | 2008-09-08 17:30:02 | Select,Insert,Update | | | localhost | lu | user1 | table2| [EMAIL PROTECTED] | 2008-09-08 17:30:02 | Select,Insert,Update | | +---++--+++-+--+-+ 2 rows in set (1.11 sec) as you can see privileges clearly reflected in the mysql database are not reflected in the information schema. what makes the problem even more fun is that not all users are affected for the same table. another user could very well have the problem with table1 instead of table2 and another user may have no problems at all. Any thoughts or suggestions would be greatly appreciated. Thanks Christian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
