RE: Does mysql cache strip out /* comments */ first?
I think you will probably find that the code you write isn't what MySQL
executes or stores in the cache.
From: vegiv...@gmail.com [vegiv...@gmail.com] On Behalf Of ext Johan De
Meersman [vegiv...@tuxera.be]
Sent: 18 November 2010 07:48
To: Daevid Vincent
Cc: mysql
Subject: Re: Does mysql cache strip out /* comments */ first?
Given that even spacing is important, it's a safe bet that it takes comments
into consideration, too.
Easily tested, though: grab one of the heaviest queries you have from your
slowlog, and execute with identical and different comments.
On Thu, Nov 18, 2010 at 8:31 AM, Daevid Vincent dae...@daevid.com wrote:
Like most developers, I have a wrapper that all of my SQL queries go
through in PHP.
We have a dedicated NOC screen that shows the mytop status of each
DEV/TEST/PROD master/slave pair.
http://daevid.com/content/examples/snippets.php (Automatic Monitoring of
remote servers)
We sometimes see stuck queries and are always hesitant to kill them off
because we never know WHO is executing that SQL. Is it a customer? Is it a
developer? Is it the boss? Is it rogue from some script gone awry? Mytop
doesn't give the full query due to screen real-estate amongst other
reasons. The downside is they bog down the server until they eventually
time-out or complete.
Anyways, today I implemented a simple, transparent and effective step
towards this puzzle.
I prefix ALL SQL (since it goes through my sql_query() function) with
/* ${SCRIPTNAME} */
Now all sql in the mytop shows up as:
/* foo.php */ SELECT * FROM foo WHERE id = 1;
/* bar.php */ UPDATE bar SET a = b WHERE id = 2;
Etc...
What I'd REALLY like to do is add more information in there. Perhaps add
the FUNCTION/METHOD and the logged-in web USER that is actually executing
that SQL, etc.
My concern is, my gut tells me that the built in mysql cache system is
dumb. And by that I mean, I suspect that mySQL isn't smart enough to
strip out comments from the SQL statement string BEFORE storing it as the
cache hash key (yet I have no facts either way to back it up and hence the
reason for this email).
http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html
Can anyone please tell me I'm wrong and that it is smarter than I give it
credit for, as I think this would be a very useful feature (or bug-fix as
the case may be).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql installation - Innodb not enabled?
Good day all We have downloaded the following MySQL version which untarred to provide a list of RPM's: MySQL-server-advanced-gpl-5.1.50-1.rhel5.x86_64.tar The installations went through fine, however while doing some configurations I found that Innodb is not enabled / installed. When configuring the Innodb paramters in the log files, I get the following errors when I try to start up the database: 101118 11:21:41 [ERROR] Plugin 'InnoDB' init function returned error. 101118 11:21:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 101118 11:23:51 [ERROR] Parsing options for plugin 'InnoDB' failed. 101118 11:23:51 [ERROR] /usr/sbin/mysqld: unknown variable 'innodb_flush_log_at_trx_commit=1' 101118 11:23:51 [ERROR] Aborting Could anyone please assist me with this issue. It would be much appreciated. FYI, the RPMS that came within the tar file are as follows: MySQL-advanced-gpl-debuginfo-5.1.50-1.rhel5.x86_64.rpm MySQL-server-advanced-gpl-5.1.50-1.rhel5.x86_64.rpm MySQL-client-advanced-gpl-5.1.50-1.rhel5.x86_64.rpm MySQL-shared-advanced-gpl-5.1.50-1.rhel5.x86_64.rpm MySQL-devel-advanced-gpl-5.1.50-1.rhel5.x86_64.rpm MySQL-shared-compat-advanced-gpl-5.1.50-1.rhel5.x86_64.rpm MySQL-embedded-advanced-gpl-5.1.50-1.rhel5.x86_64.rpm MySQL-test-advanced-gpl-5.1.50-1.rhel5.x86_64.rpm Regards Machiel
Re: Does mysql cache strip out /* comments */ first?
On Thu, Nov 18, 2010 at 9:00 AM, andrew.2.mo...@nokia.com wrote: I think you will probably find that the code you write isn't what MySQL executes or stores in the cache. it is indeed not quite what it executes, but as I understand it the QC index is *exactly* the string you send (well, hashed presumably), including spaces, capitalisation and whatnot. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Does mysql cache strip out /* comments */ first?
Daevid, snip My concern is, my gut tells me that the built in mysql cache system is dumb. And by that I mean, I suspect that mySQL isn't smart enough to strip out comments from the SQL statement string BEFORE storing it as the cache hash key (yet I have no facts either way to back it up and hence the reason for this email). http://dev.mysql.com/doc/refman/5.0/en/query-cache.html http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html Can anyone please tell me I'm wrong and that it is smarter than I give it credit for, as I think this would be a very useful feature (or bug-fix as the case may be). Your suspicions are correct, the query cache does not strip comments before storing the statement. This can however be done in the Percona build. http://www.percona.com/docs/wiki/percona-server:features:query_cache_enhance#query_cache_strip_comments http://www.percona.com/docs/wiki/percona-server:features:implementation_details:details_query_cache_with_comments Cheers, Ewen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: export db to oracle
Hi, Actually all the helpful tips that I have gotten have caused me to review the requirements! I now realise that that csv or xml files for the storage of an extract would be helpful, for testing and validation. a mysqldump might do that job too, but the output from mysqldump --compatible was rejected by oracle. -Syd - Original Message From: Kevin (Gmail) kfoneil...@gmail.com To: Johan De Meersman vegiv...@tuxera.be; Shawn Green (MySQL) shawn.l.gr...@oracle.com Cc: Sydney Puente sydneypue...@yahoo.com; mysql@lists.mysql.com Sent: Wed, 17 November, 2010 18:17:38 Subject: Re: export db to oracle Hello, It should be possible to connect Oracle to the MySQL (or other) database using a DBlink (using a MySQL ODBC driver) the tables could then be copied using PLSQL. Maybe you could link directly to Oracle and copy the code using MySQL procedures or scripts (I have more experienc of Oracle which works quite well as I described) This way, you can avoid use of external files and CSV etc. It is very likely quicker since you can use bulk loads or 'select into' routines once you have the right table structures and field type in place. This is a technique that I have used for ETL and data integration and it is very manageable. You can trap errors using cursors if the data has anomalies. Kevin O'Neill - Original Message - From: Johan De Meersman vegiv...@tuxera.be To: Shawn Green (MySQL) shawn.l.gr...@oracle.com Cc: Sydney Puente sydneypue...@yahoo.com; mysql@lists.mysql.com Sent: Wednesday, November 17, 2010 8:58 AM Subject: Re: export db to oracle On Wed, Nov 17, 2010 at 1:43 AM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 11/16/2010 15:14, Sydney Puente wrote: Hello, How can I export a mysql 5.0.45 db to Oracle? mysql is going to stau but I need to pass the data to oracle, just so the data can be transfered. I have carried out a mysql dump. This seems fine.create table etc. about 20 MB in total. Any ideas? It is on Redhat if that makes a difference. I suggest you also look at the syntax for SELECT INTO OUTFILE, too. Dumps are usually scripts of SQL statements that Oracle may not read appropriately. I'm not quite sure which formats Oracle reads in, although CSV is probably a good guess. if you disable mysqldump's extended insert syntax, however, I think the actual insert statements should be perfectly fine for most any database. You may need to tweak create statements for datatypes and syntax, though; it may be easier to just recreate the emtpy tables by hand. I think I also have vague memories of an option to use ANSI-SQL standard syntax, although that might just as well have been some third-party tool. And, speaking of third-party tools: tOra can (if well-compiled) be used to manage both MySQL and Oracle; maybe that nice tool can help you. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Granting access rights to a user
using mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu I have root access to the mysql command line. I need to grant access to user 'username' for all databases except for the mysql database for both onsite (cgi script) and remote access. What is the syntax? -- Tim tim at johnsons-web.com or akwebsoft.com http://www.akwebsoft.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Granting access rights to a user
* Tim Johnson t...@johnsons-web.com [101118 11:05]: using mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu I have root access to the mysql command line. I need to grant access to user 'username' for all databases except for the mysql database for both onsite (cgi script) and remote access. What is the syntax? here is what I did to solve this: As root I issued from the mysql DB: SHOW GRANTS FOR username@'%'; Then making note of the stored grant commands issued GRANT ALL PRIVILEGES ON `dbname`.* TO 'username'@'%' WITH GRANT OPTION for which tables I need access for 'username'. -- Tim tim at johnsons-web.com or akwebsoft.com http://www.akwebsoft.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Does mysql cache strip out /* comments */ first?
Ewen thank you! You've opened my eyes to something I didn't even know about and made my special purpose tingle. Have you used Percona personally? What are your opinions/thoughts? If you haven't used it, I'd be curious why not or what turned you away from it? -Original Message- From: Ewen Fortune [mailto:ewen.fort...@gmail.com] Sent: Thursday, November 18, 2010 4:56 AM To: Daevid Vincent Cc: mysql Subject: Re: Does mysql cache strip out /* comments */ first? Daevid, snip My concern is, my gut tells me that the built in mysql cache system is dumb. And by that I mean, I suspect that mySQL isn't smart enough to strip out comments from the SQL statement string BEFORE storing it as the cache hash key (yet I have no facts either way to back it up and hence the reason for this email). http://dev.mysql.com/doc/refman/5.0/en/query-cache.html http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html Can anyone please tell me I'm wrong and that it is smarter than I give it credit for, as I think this would be a very useful feature (or bug-fix as the case may be). Your suspicions are correct, the query cache does not strip comments before storing the statement. This can however be done in the Percona build. http://www.percona.com/docs/wiki/percona-server:features:query _cache_enhance#query_cache_strip_comments http://www.percona.com/docs/wiki/percona-server:features:imple mentation_details:details_query_cache_with_comments Cheers, Ewen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does mysql cache strip out /* comments */ first?
Daevid, On Thu, Nov 18, 2010 at 11:41 PM, Daevid Vincent dae...@daevid.com wrote: Ewen thank you! You've opened my eyes to something I didn't even know about and made my special purpose tingle. Have you used Percona personally? What are your opinions/thoughts? If you haven't used it, I'd be curious why not or what turned you away from it? I work for Percona :o) So I think its best someone else chips in. Ewen -Original Message- From: Ewen Fortune [mailto:ewen.fort...@gmail.com] Sent: Thursday, November 18, 2010 4:56 AM To: Daevid Vincent Cc: mysql Subject: Re: Does mysql cache strip out /* comments */ first? Daevid, snip My concern is, my gut tells me that the built in mysql cache system is dumb. And by that I mean, I suspect that mySQL isn't smart enough to strip out comments from the SQL statement string BEFORE storing it as the cache hash key (yet I have no facts either way to back it up and hence the reason for this email). http://dev.mysql.com/doc/refman/5.0/en/query-cache.html http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html Can anyone please tell me I'm wrong and that it is smarter than I give it credit for, as I think this would be a very useful feature (or bug-fix as the case may be). Your suspicions are correct, the query cache does not strip comments before storing the statement. This can however be done in the Percona build. http://www.percona.com/docs/wiki/percona-server:features:query _cache_enhance#query_cache_strip_comments http://www.percona.com/docs/wiki/percona-server:features:imple mentation_details:details_query_cache_with_comments Cheers, Ewen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Granting access rights to a user
* Tim Johnson t...@johnsons-web.com [101118 12:58]: * Tim Johnson t...@johnsons-web.com [101118 11:05]: using mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu I have root access to the mysql command line. I need to grant access to user 'username' for all databases except for the mysql database for both onsite (cgi script) and remote access. What is the syntax? here is what I did to solve this: As root I issued from the mysql DB: SHOW GRANTS FOR username@'%'; Then making note of the stored grant commands issued GRANT ALL PRIVILEGES ON `dbname`.* TO 'username'@'%' WITH GRANT OPTION for which tables I need access for 'username'. michael dykman points out (OTL) that it is not a good idea to give a user a grant option The syntax for removing the grant option is: REVOKE GRANT OPTION ON databasename.* FROM 'username'@'%'; at least that is what I deduce from docs and `show grants for user;' displays user privileges without the grant option. :) I'm cleaning up after someone else. cheers -- Tim tim at johnsons-web.com or akwebsoft.com http://www.akwebsoft.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does mysql cache strip out /* comments */ first?
Percona's got a great reputation in the community, and I would have no qualms using their builds. On 11/18/10, Ewen Fortune ewen.fort...@gmail.com wrote: Daevid, On Thu, Nov 18, 2010 at 11:41 PM, Daevid Vincent dae...@daevid.com wrote: Ewen thank you! You've opened my eyes to something I didn't even know about and made my special purpose tingle. Have you used Percona personally? What are your opinions/thoughts? If you haven't used it, I'd be curious why not or what turned you away from it? I work for Percona :o) So I think its best someone else chips in. Ewen -Original Message- From: Ewen Fortune [mailto:ewen.fort...@gmail.com] Sent: Thursday, November 18, 2010 4:56 AM To: Daevid Vincent Cc: mysql Subject: Re: Does mysql cache strip out /* comments */ first? Daevid, snip My concern is, my gut tells me that the built in mysql cache system is dumb. And by that I mean, I suspect that mySQL isn't smart enough to strip out comments from the SQL statement string BEFORE storing it as the cache hash key (yet I have no facts either way to back it up and hence the reason for this email). http://dev.mysql.com/doc/refman/5.0/en/query-cache.html http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html Can anyone please tell me I'm wrong and that it is smarter than I give it credit for, as I think this would be a very useful feature (or bug-fix as the case may be). Your suspicions are correct, the query cache does not strip comments before storing the statement. This can however be done in the Percona build. http://www.percona.com/docs/wiki/percona-server:features:query _cache_enhance#query_cache_strip_comments http://www.percona.com/docs/wiki/percona-server:features:imple mentation_details:details_query_cache_with_comments Cheers, Ewen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=waynn...@gmail.com -- Sent from my mobile device __ Waynn Lue 626.429.6412 | waynn...@gmail.com Facebook: www.facebook.com/waynn LinkedIn: www.linkedin.com/in/waynn __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: export db to oracle
On Thu, Nov 18, 2010 at 2:54 PM, Sydney Puente sydneypue...@yahoo.com wrot a mysqldump might do that job too, but the output from mysqldump --compatible was rejected by oracle. Hmm. Interesting, you might want to file an issue about that - now that MySQL is oracle-owned, you'd expect at least that to work, wouldn't you :-p -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
MySQL GA clustering
Good day all I would just like to confirm the following please. I have a client who is running the free downloadable version of MySQL and they would like to go the clustering route for quite a couple of reasons. Does anybody know whether clustering is available with the downloadable version or is this only available with Enterprise? They would also like to know what they need for proper clustering in terms of disks (shared storage or local) memory (i found this on a website though) etc... and resouces they can look at on how to implement. I don't know clustering on MySQL at all so I will really appreciate some help on this. Regards Machiel
