Re: Help with query using IN()
On 04/10/2005, Jasper Bryant-Greene wrote: Kishore Jalleda wrote: Could you kindly advice if this query can be made to run faster SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,189 0,1891,1902,1904,2104,624,625,2152,2212,1985,1996,614,1964,2103,2106 ,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); There is an index on templateid also this query takes ~ 0.04xx seconds to run I would like it to be in 0.00xx range explain select gives this id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE template range PRIMARY PRIMARY 4 NULL 40 Using where It's using a primary key and only examining the 40 rows which you asked for, so that's about as optimised as you'll get for that query. You could always make the actual server faster... If your template table contains many columns in addition to templateid, title and template, and title and template are not TEXT columns, you can consider a covering index on templateid, title and template: ALTER TABLE template ADD UNIQUE (templateid, title, template); -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deleting unwanted rows
[EMAIL PROTECTED] wrote: Merlin [EMAIL PROTECTED] wrote on 10/04/2005 10:58:21 AM: [EMAIL PROTECTED] wrote: Merlin [EMAIL PROTECTED] wrote on 10/04/2005 10:21:00 AM: Hi there, I just discovered, that I do have some old rows I do not need anymore. A result from forgeting to empty the table before starting to go into production :-) However, I do try to find the rows which are not asociated with another table in order to delete them. I have 2 tables: table1 and table2 table1 has the key: ID table2 has the subkey table1_id Now I would like to delete all rows in table1 which are not listed in table2 with an id. I tried: SELECT t1.* FROM table1 as t1, table2 as t2 WHERE t1.ID != t2.table1_id But this returns hundreds of thousends of results. I also tryed to group by t1.ID, but it did not help Does anybody have a good idea how to get rid of those rows? Thanx, Merlin PS: Thanx for the answer for the question with full text search! That worked excellent! Use a LEFT JOIN not an INNER JOIN! SELECT t1.* FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.table1_id WHERE t2.table1_id is NULL; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Hi, this workes excellent, but I tried to replace the select * from with delete from, but this did not work. Do I have to use a differnt syntax for deleting in this case? Thanx, Merlin Yes, and it which form you can use depends on your server's version. More details here: http://dev.mysql.com/doc/mysql/en/delete.html DELETE table1 FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.table1_id WHERE t2.table1_id is NULL; Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS - always CC: the list on all responses Thanx that worked excellent! Now I know why this has happened. I have forgot to delete entries in table1 which refer to table 2. Is there a way to delete them with one statement, or do I have to make a select to get the table1_id first and then do 2 delets? I do have at the moment 3 querys!: # get table1_id SELECT table1_id from ... WHERE ID = ... # delete dependent entry DELETE FROM $DB.$T5 WHERE ID = '$data[table1_id]' LIMIT 1 # delete main entry DELETE FROM $DB.$T4 WHERE ID = '$data[id]' LIMIT 1 Is there a better solution as this? Thanx, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cursors in 5.0.13
Having a few problems using cursors in 5.0.13 and I don't know wether it's an 'operator error' :) Should this work? DECLARE cur1 CURSOR FOR SELECT DISTINCT name,addr1,addr2,addr3,addr4,addr5,town,county,postcode,telephone FROM extended WHERE centre_name != ; The DISTINCT in the SELECT statement seems to screw things up merrily? (Full code at bottom of this mail). If I remove the distinct from the cursor I get a count returned of 50511. A simple SELECT COUNT(DISTINCT name,addr1,addr2,addr3,addr4,addr5,town,county,postcode,telephone) FROM extended WHERE centre_name != ; Returns 26813. The stored procedure with the distinct in place only ever returns 1? DELIMITER $$ DROP PROCEDURE IF EXISTS `directory`.`Test1`$$ CREATE PROCEDURE `directory`.`Test1` (OUT counted INT) BEGIN DECLARE countit,done INT DEFAULT 0; DECLARE name,addr1,addr2,addr3,addr4,addr5,town,county,postcode,telephone CHAR(255); DECLARE cur1 CURSOR FOR SELECT DISTINCT name,addr1,addr2,addr3,addr4,addr5,town,county,postcode,telephone FROM extended WHERE centre_name != ; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO name,addr1,addr2,addr3,addr4,addr5,town,county,postcode,telephone; IF NOT done THEN SET countit=countit+1; END IF; UNTIL done END REPEAT; CLOSE cur1; SET counted=countit; END$$ DELIMITER ; (Before anybody states that the above is pointless I know! The above codes the result of trying to track this issue!). -- Best regards, Rob Hall - Red Hat Certified Engineer Technical Team Leader Newsquest Digital Media -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Query Performance
Hi Green, Scrubbing out the data is a great suggestion, I will definitely try that out. I did try out the other option using REGEXP instead of matching individual conditions. It definitely cleaned up the implementation, but did not really improve the performance. -Harini [EMAIL PROTECTED] wrote: Harini Raghavan [EMAIL PROTECTED] wrote on 10/04/2005 11:17:48 AM: Hi, I am using MYSQL 4.1 database in my J2ee application. I am facing performance issues with some queries that are being run on text fields. Since MYISAM storage engine does not support transactions(and my application requires the database tables to support transaction), I have not been able to use FULL TEXT searches. I need to perform token matching against the text fields and so use a lot of LIKE expressions in the query. Below is one such query which is taking almost 2 mins to execute. select count(emp.id) from executive as exec1 , company comp, target_company targetComp, employment as emp where emp.executive_id = exec1.id and emp.company_id = comp.id and comp.id = targetComp.company_id and ((emp.title like 'V.P.' OR emp.title like 'V.P. %' OR emp.title like 'V.P., %' OR emp.title like 'V.P.. %' OR emp.title like '% V.P. %' OR emp.title like '% V.P., %' OR emp.title like '% V.P.. %' OR emp.title like '% V.P.' OR emp.title like '% V.P..') OR (emp.title like 'VP' OR emp.title like 'VP %' OR emp.title like 'VP, %' OR emp.title like 'VP. %' OR emp.title like '% VP %' OR emp.title like '% VP, %' OR emp.title like '% VP. %' OR emp.title like '% VP' OR emp.title like '% VP.') OR (emp.title like 'Vice President' OR emp.title like 'Vice President %' OR emp.title like 'Vice President, %' OR emp.title like 'Vice President. %' OR emp.title like '% Vice President %' OR emp.title like '% Vice President, %' OR emp.title like '% Vice President. %' OR emp.title like '% Vice President' OR emp.title like '% Vice President.') OR (emp.title like 'Vice-President' OR emp.title like 'Vice-President %' OR emp.title like 'Vice-President, %' OR emp.title like 'Vice-President. %' OR emp.title like '% Vice-President %' OR emp.title like '% Vice-President, %' OR emp.title like '% Vice-President. %' OR emp.title like '% Vice-President' OR emp.title like '% Vice-President.')) and emp.active = 1 Does MYSQL provide any other option to perform text based searches? Can someone suggest any tips for performance tuning the database in this scenario? Thanks, Harini It seems to me that you are trying to search on unscrubbed data. I can guess that your are collecting this data from a variety of sources and that those sources don't always use the same abbreviation or punctuation. However, it is better do deal with this kind of issue as the data arrives (before it enters your database) and not to deal with it during retrieval (as you are trying to do). You need to scrub your data and standardize on certain names and abbreviations. Decide that V.P. is going to be your standard for Vice President (and any of it's variants) and update all of your data to match. Until then, you can use RLIKE or REGEXP to minimize how many search terms you are evaluating. http://dev.mysql.com/doc/mysql/en/string-comparison-functions.html http://dev.mysql.com/doc/mysql/en/regexp.html For example, all of these terms: (emp.title like 'V.P.' OR emp.title like 'V.P. %' OR emp.title like 'V.P., %' OR emp.title like 'V.P.. %' OR emp.title like '% V.P. %' OR emp.title like '% V.P., %' OR emp.title like '% V.P.. %' OR emp.title like '% V.P.' OR emp.title like '% V.P..') OR (emp.title like 'VP' OR emp.title like 'VP %' OR emp.title like 'VP, %' OR emp.title like 'VP. %' OR emp.title like '% VP %' OR emp.title like '% VP, %' OR emp.title like '% VP. %' OR emp.title like '% VP' OR emp.title like '% VP.') can be simplified to just: emp.title REGEXP '[:space:]*V.*P[ ,.]*' Would that help? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Query Performance
Hi Brent,
Using REGEXP did not really help with the performance. I need to do
whole word matching sowould prefer not to do LIKE '%Vice President%' as
it may return ome negative results.
I separated out some of the text based columns in to a different table
using MYISAM storage engine. Using FULLTEXT with IN BOOLEAN MODE was a
bit slow, but without the IN BOOLEAN MODE, it seems fast. However I
think the phrase searches are not working properly.
For example the below query returned records where the title was 'Vice
Chairman':
select emp.title from employment_title emp where MATCH(emp.title)
AGAINST('Vice President')
I have verified the syntax of phrase query, and it seems to bve correct.
Any idea why this is happening? Also if I have multiple phrases is the
following query syntax correct?
select emp.title from employment_title emp where MATCH(emp.title)
AGAINST('V.P. VP Vice President Vice-President')
Thanks,
Harini
Brent Baisley wrote:
Egads! That's a lot of OR's. You probably want to use REGEXP instead
of all those OR's. REGEXP can be slow, but you'll be doing far less
comparisons than what you have in your current query so it may be
faster than what you have.
Something like this:
SELECT ...
WHERE ... emp.title REGEXP 'V[.]?P[.]?' OR emp.title REGEXP 'Vice[-]?
President'
I think that will match everything you have. At the very least you
don't have to check for periods, commas, space and everything else
before and after what you are searching on. Searching on emp.title
LIKE '%Vice President%', will find 'Vice President' anywhere in the
text, regardless of what come before or after it.
One thing you should think about trying to do is breaking out the
fields you need to do a full text search on into a separate table
that you can make MYISAM. You'll be adding a JOIN to your queries
that need to do the full text search, but it should be a lot quicker
and your queries much simpler. You'll then have a mix of InnoDB and
MYISAM tables, which is perfectly legal.
On Oct 4, 2005, at 11:17 AM, Harini Raghavan wrote:
Hi,
I am using MYSQL 4.1 database in my J2ee application. I am facing
performance issues with some queries that are being run on text
fields. Since MYISAM storage engine does not support transactions
(and my application requires the database tables to support
transaction), I have not been able to use FULL TEXT searches. I need
to perform token matching against the text fields and so use a lot
of LIKE expressions in the query. Below is one such query which is
taking almost 2 mins to execute.
select count(emp.id) from executive as exec1 , company comp,
target_company targetComp, employment as emp
where emp.executive_id = exec1.id
and emp.company_id = comp.id
and comp.id = targetComp.company_id and ((emp.title like 'V.P.' OR
emp.title like 'V.P. %' OR emp.title like 'V.P., %' OR emp.title
like 'V.P.. %' OR emp.title like '% V.P. %'
OR emp.title like '% V.P., %' OR emp.title like '% V.P.. %' OR
emp.title like '% V.P.' OR emp.title like '% V.P..')
OR (emp.title like 'VP' OR emp.title like 'VP %' OR emp.title like
'VP, %' OR emp.title like 'VP. %' OR emp.title like '% VP %'
OR emp.title like '% VP, %' OR emp.title like '% VP. %' OR emp.title
like '% VP' OR emp.title like '% VP.')
OR (emp.title like 'Vice President' OR emp.title like 'Vice
President %' OR emp.title like 'Vice President, %' OR emp.title like
'Vice President. %'
OR emp.title like '% Vice President %' OR emp.title like '% Vice
President, %' OR emp.title like '% Vice President. %'
OR emp.title like '% Vice President' OR emp.title like '% Vice
President.') OR (emp.title like 'Vice-President' OR emp.title like
'Vice-President %'
OR emp.title like 'Vice-President, %' OR emp.title like 'Vice-
President. %' OR emp.title like '% Vice-President %'
OR emp.title like '% Vice-President, %' OR emp.title like '% Vice-
President. %' OR emp.title like '% Vice-President'
OR emp.title like '% Vice-President.'))
and emp.active = 1
Does MYSQL provide any other option to perform text based searches?
Can someone suggest any tips for performance tuning the database in
this scenario?
Thanks,
Harini
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
variable table names in queries ...
Hi, I am looking for a method to use variable table names. I have various download-tables, all having the same structure. All I want is to run a series of queries on each of these tables. I tried SET @mytable = 'Download200501'; SELECT count(*) FROM @mytable; but this doen't work. Is there any way to work around this ? Thanks, Cor
Re: 2 Bugs
1) that's common problem when doing custom install on PATH containing spaces like C:\Program Files. Solution - try custom location with short(er) path and without spaces like C:\mysql5013\. Probably that will solve problem 2. if not then.. 2. Edit my.ini looking for skip-innodb or related options. Read docs 1st. HTH Philippe HAMEAU wrote: Hi, I noticed 1 bug with 5.0.13rc (win32 msi) : when installing mysql in a custom location (eg c:\program files\easyphp\mysql), 1) the service will not get installed properly 2) mysqld will complain about InnoDB engine not present. Hope this helps. Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: variable table names in queries ...
C.R. Vegelin wrote: Hi, I am looking for a method to use variable table names. I have various download-tables, all having the same structure. All I want is to run a series of queries on each of these tables. I tried SET @mytable = 'Download200501'; SELECT count(*) FROM @mytable; but this doen't work. Is there any way to work around this ? Thanks, Cor Cor, In http://dev.mysql.com/doc/mysql/en/variables.html it states that User variables may be used where expressions are allowed. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE number LINES clause of a LOAD DATA statement. Since the table name in the SELECT syntax cannot be an expression, you can't use variables here. I think this is a job for the programming environment / shell that you use. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 Bugs
...and 3. Are you running another instance of mysql? Then perhaps both compete for same resource(s) like: port 3306 ;-) Remo Tex wrote: 1) that's common problem when doing custom install on PATH containing spaces like C:\Program Files. Solution - try custom location with short(er) path and without spaces like C:\mysql5013\. Probably that will solve problem 2. if not then.. 2. Edit my.ini looking for skip-innodb or related options. Read docs 1st. HTH Philippe HAMEAU wrote: Hi, I noticed 1 bug with 5.0.13rc (win32 msi) : when installing mysql in a custom location (eg c:\program files\easyphp\mysql), 1) the service will not get installed properly 2) mysqld will complain about InnoDB engine not present. Hope this helps. Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 Bugs
Remo Tex wrote: 1) that's common problem when doing custom install on PATH containing spaces like C:\Program Files. Solution - try custom location with short(er) path and without spaces like C:\mysql5013\. Probably that will solve problem 2. if not then.. Remo Tex, That is not a good excuse for me. A path with spaces is a valid path in Windows (at least recent versions), so any Windows program should be able to deal with this. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JBoss queries aren't cached by MySQL
Hi All, I didn't see any follow-up on this thread. Hope I can get some hint :) How to get the query cache to work with Jboss? I've got my app running on the following JBoss 3.2.2. (Unable to upgrade right now) MySQL 4.0.20d mysql-connector-java-3.1.10-bin.jar I have switched on the query_cache as follows: query_cache_limit=2M query_cache_size=32M query_cache_type=1 However, after triggering a few queries from my applications, the query cache is not used at all: SHOW STATUS LIKE 'Qcache%'; Qcache_queries_in_cache,0 Qcache_inserts,0 Qcache_hits,0 Qcache_lowmem_prunes,0 Qcache_not_cached,328 Qcache_free_memory,33545632 Qcache_free_blocks,1 Qcache_total_blocks,1 Note: Running queries from MySQL-Front hits the cache - the above counters increase. Regards, Al -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 12:33 AM To: Rafal Kedziorski Cc: mysql@lists.mysql.com Subject: Re: JBoss queries aren't cached by MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rafal Kedziorski wrote: Hi, we have the problem, that queries generated by JBoss or our code which runns under JBoss will be not cached by MySQL. The same query sendet from an external application or MySQLFront will be cached by the same MySQL. I'm using JBoss 3.2.5 with JDBC 3.0.16 and MySQL 4.0.23a. Any idea why? Regards, Rafal Rafal, Define what you mean by not cached by MySQLDo you mean the query cache? If so, what do your query cache settings look like? -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCbm1JtvXNTca6JD8RAtT9AJ9k8HnIkRh+U9UE3ROf+eff5ZudRgCgxNNI KeS6Iiq5ttoKjZsaDlyXV74= =gCL+ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Send instant messages to your online friends http://asia.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JBoss queries aren't cached by MySQL
Hi again, It seems that the problem is solved with MySQL 4.1 (and later?). See a posting from the Jboss forum: http://www.jboss.org/index.html?module=bbop=viewtopicp=3830750#3830750 Cheers, Al -Original Message- From: Al Caponi [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 8:35 PM To: mysql@lists.mysql.com Subject: RE: JBoss queries aren't cached by MySQL Hi All, I didn't see any follow-up on this thread. Hope I can get some hint :) How to get the query cache to work with Jboss? I've got my app running on the following JBoss 3.2.2. (Unable to upgrade right now) MySQL 4.0.20d mysql-connector-java-3.1.10-bin.jar I have switched on the query_cache as follows: query_cache_limit=2M query_cache_size=32M query_cache_type=1 However, after triggering a few queries from my applications, the query cache is not used at all: SHOW STATUS LIKE 'Qcache%'; Qcache_queries_in_cache,0 Qcache_inserts,0 Qcache_hits,0 Qcache_lowmem_prunes,0 Qcache_not_cached,328 Qcache_free_memory,33545632 Qcache_free_blocks,1 Qcache_total_blocks,1 Note: Running queries from MySQL-Front hits the cache - the above counters increase. Regards, Al -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 12:33 AM To: Rafal Kedziorski Cc: mysql@lists.mysql.com Subject: Re: JBoss queries aren't cached by MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rafal Kedziorski wrote: Hi, we have the problem, that queries generated by JBoss or our code which runns under JBoss will be not cached by MySQL. The same query sendet from an external application or MySQLFront will be cached by the same MySQL. I'm using JBoss 3.2.5 with JDBC 3.0.16 and MySQL 4.0.23a. Any idea why? Regards, Rafal Rafal, Define what you mean by not cached by MySQLDo you mean the query cache? If so, what do your query cache settings look like? -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCbm1JtvXNTca6JD8RAtT9AJ9k8HnIkRh+U9UE3ROf+eff5ZudRgCgxNNI KeS6Iiq5ttoKjZsaDlyXV74= =gCL+ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Send instant messages to your online friends http://asia.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Send instant messages to your online friends http://asia.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 Bugs
Philippe HAMEAU wrote: Hi, I noticed 1 bug with 5.0.13rc (win32 msi) : when installing mysql in a custom location (eg c:\program files\easyphp\mysql), 1) the service will not get installed properly 2) mysqld will complain about InnoDB engine not present. Hope this helps. Phil Have you read: http://dev.mysql.com/doc/mysql/en/windows-troubleshooting.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Find username password on tables
Hello, A while back, I created a database, and performed the following: GRANT ALL ON util_db.* to XXX identified by XXX; Problem is, a year later, I need to find the username and password, so I can write to these tables. Can this be accomplished, I am the root user. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find username password on tables
Scott Purcell wrote: Hello, A while back, I created a database, and performed the following: GRANT ALL ON util_db.* to XXX identified by XXX; Problem is, a year later, I need to find the username and password, so I can write to these tables. Can this be accomplished, I am the root user. Thanks, Scott use the mysql database and look in the user table .. you will be able to see all the users there , as well as the privileges. you will have to reset the password for the user you want to use... use the 'grant' statement to reset the password. hope this helps. -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Find username password on tables
Username you can get it from the user table in Mysql. But I don't think atleast after MySQL 4.1 there is a way to retrieve paswords in MySQL as it uses its own encryption algo to encrypt passwords. You have to reset(make that passwd field to null in the mysql.user table) and add a new password(use grant) to use that. sujay -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 6:39 PM To: mysql@lists.mysql.com Subject: Find username password on tables Hello, A while back, I created a database, and performed the following: GRANT ALL ON util_db.* to XXX identified by XXX; Problem is, a year later, I need to find the username and password, so I can write to these tables. Can this be accomplished, I am the root user. Thanks, Scott -- 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: Find username password on tables
Or if you have any applications that are accessing this database you could look at the config files for it and most likely the password will be there. jay Username you can get it from the user table in Mysql. But I don't think atleast after MySQL 4.1 there is a way to retrieve paswords in MySQL as it uses its own encryption algo to encrypt passwords. You have to reset(make that passwd field to null in the mysql.user table) and add a new password(use grant) to use that. sujay -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 6:39 PM To: mysql@lists.mysql.com Subject: Find username password on tables Hello, A while back, I created a database, and performed the following: GRANT ALL ON util_db.* to XXX identified by XXX; Problem is, a year later, I need to find the username and password, so I can write to these tables. Can this be accomplished, I am the root user. Thanks, Scott -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb locking
Hi, I have a query: UPDATE dbseXyzOrders.tblOrder SET intPoUid = 98 WHERE intOrderUid = 10798 intOrderUid is the primary key There are 25 columns in the table and a further 8 of these have indexes on them. The table is innodb I have just tried to run the above query 3 times and i got the follwing error Invalid Query Lock wait timeout exceeded; try restarting transaction AFAIK innodb locks on row level, so does that mean that something else is locking that row and won't let me update. If so how can i find out what? Here is a copy of what mytop says at the time the query is being run Thanks for any help MySQL on localhost (4.1.8a-Debian_1-log) up 4+08:28:06 [15:07:19] Queries: 6.0M qps: 17 Slow: 120.0 Se/In/Up/De(%): 68/19/01/00 qps now:8 Slow qps: 0.0 Threads:7 ( 3/ 0) 77/00/00/00 Cache Hits: 3.0M Hits/s: 8.5 Hits now: 4.2 Ratio: 74.2% Ratio now: 70.0% Key Efficiency: 97.7% Bps in/out: 6.8k/ 9.8k Now in/out: 3.5k/ 5.0k Id User Host/IP DB TimeCmd Query or State -- --- -- --- -- 554082 root localhost test 0 Query show full processlist 603034 root localhost0 Sleep 603086 root localhost0 Sleep 602989 root localhost1 Sleep 603301 root localhost1 Sleep 604008 root localhost 11 Query UPDATE dbseXyzOrders.tblOrder SET intPoUid = 97 WHERE intOrde 389249 repl clank 133504 Binlog Has sent all binlog to slave; waiting for binlog to be update -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb locking
Is this happening every time you try this, or it happened first time? Yes you right that INNODB uses row level locks, and the only reason for that error should be that someone else is trying to update the same row. As we can see from the o/p of the show procee list, someone else is also trying to update. (can't find from thet if it is the same row or not) Also can you please tell what isolation level are you using now. sujay -Original Message- From: Tony Leake [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 7:27 PM To: mysql@lists.mysql.com Subject: innodb locking Hi, I have a query: UPDATE dbseXyzOrders.tblOrder SET intPoUid = 98 WHERE intOrderUid = 10798 intOrderUid is the primary key There are 25 columns in the table and a further 8 of these have indexes on them. The table is innodb I have just tried to run the above query 3 times and i got the follwing error Invalid Query Lock wait timeout exceeded; try restarting transaction AFAIK innodb locks on row level, so does that mean that something else is locking that row and won't let me update. If so how can i find out what? Here is a copy of what mytop says at the time the query is being run Thanks for any help MySQL on localhost (4.1.8a-Debian_1-log) up 4+08:28:06 [15:07:19] Queries: 6.0M qps: 17 Slow: 120.0 Se/In/Up/De(%): 68/19/01/00 qps now:8 Slow qps: 0.0 Threads:7 ( 3/ 0) 77/00/00/00 Cache Hits: 3.0M Hits/s: 8.5 Hits now: 4.2 Ratio: 74.2% Ratio now: 70.0% Key Efficiency: 97.7% Bps in/out: 6.8k/ 9.8k Now in/out: 3.5k/ 5.0k Id User Host/IP DB TimeCmd Query or State -- --- -- --- -- 554082 root localhost test 0 Query show full processlist 603034 root localhost0 Sleep 603086 root localhost0 Sleep 602989 root localhost1 Sleep 603301 root localhost1 Sleep 604008 root localhost 11 Query UPDATE dbseXyzOrders.tblOrder SET intPoUid = 97 WHERE intOrde 389249 repl clank 133504 Binlog Has sent all binlog to slave; waiting for binlog to be update -- 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: InnoDB error unable to create temporary file
Hi Joerg et all, [snip] You said this is Solaris. AFAIK, Solaris uses the same disk area for /tmp and paging, so the available space for files in /tmp may vary even without files being manipulated. I propose you set TMPDIR to point to some other disk area. We're on Solaris, yes. I suspected that paging might be a cause so I reconfigured our QAS server (which also shows the errors) to use /var/mysql in stead of /tmp. But the problem persists. It is always at the same time: when cron runs a script that obtains table info. Everything else seems to work fine (i.e. nothing breaks), so I think I'll leave it till it's time to upgrade. Kind regards, -- Martijn ASML ITMS Webcenter / Application Support -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
crashes in InnoDB with MySQL 4.1.14
One of our servers which is configured primarily for InnoDB has crashed in the same fashion three times in the past 48 hours. I've been going over the error log, and I would like to know if anyone else has seen similar errors, or can give me some pointers to work out this problem. Here are the server's settings... Dual Xeon 2.8 4G RAM, RAID 5 disks Linux 2.4.20-6smp MySQL-server-4.1.14-0.glibc23 (RPM) my.cnf, edited for relevance skip-locking max_allowed_packet = 1M sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M key_buffer_size = 8M# used only for MyISAM thread_concurrency = 4 # was 8 thread_cache = 8 skip-name-resolve max_allowed_packet = 8M query_cache_size = 32M query_cache_limit = 1M table_cache = 512 innodb_log_buffer_size = 16M innodb_log_file_size = 128M innodb_log_files_in_group = 3 innodb_additional_mem_pool_size = 128M innodb_buffer_pool_size = 1G innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 30 - Within the InnoDB Monitor output, just before the crash, I see roughly 200 transactions in waiting, and exactly 8 that look like this ---TRANSACTION 0 1856439353, ACTIVE 913 sec, process no 25698, OS thread id 2448796736 inserting, thread declared inside InnoDB 410 mysql tables in use 1, locked 1 1 lock struct(s), heap size 320, undo log entries 592 MySQL thread id 1036, query id 368385 192.168.1.37 webserver update INSERT INTO emails_history_30_18 There is also this message in there Main thread process no. 25698, id 2434898496, state: doing insert buffer merge Then, I get this long message in the .err file: -- InnoDB: ## Diagnostic info printed to the standard error stream InnoDB: Error: semaphore wait has lasted 600 seconds InnoDB: We intentionally crash the server, because it appears to be hung. 051005 6:54:17InnoDB: Assertion failure in thread 2426510016 in file srv0srv.c line 1893 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. InnoDB: Thread 2401344576 stopped in file ibuf0ibuf.c line 1335 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. InnoDB: Thread 2434898496 stopped in file ../include/sync0sync.ic line 111 We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388608 read_buffer_size=2093056 max_used_connections=301 max_connections=300 threads_connected=297 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1235789 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x90a1936c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8125d30 0x40049568 (nil) 0x420de1b7 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. --- Any one have a suggestion? I've already tried running CHECK TABLE emails_history_30_18 EXTENDED, that came back saying everything was fine. While I was writing this email, the server has crashed two more times in this same way. Any help would be appreciated! Thanks in advance, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb locking
On Wed, 2005-10-05 at 07:12 -0700, Sujay Koduri wrote: Is this happening every time you try this, or it happened first time? It's not every time but this is not the first. Mostly the query is ok, but I would like to find out why it's happening. Also can you please tell what isolation level are you using now. I am using the default which i beleive is REPEATABLE READ. The particular query that is failing is done using autocommit as it a standalone query. Thanks tony sujay -Original Message- From: Tony Leake [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 7:27 PM To: mysql@lists.mysql.com Subject: innodb locking Hi, I have a query: UPDATE dbseXyzOrders.tblOrder SET intPoUid = 98 WHERE intOrderUid = 10798 intOrderUid is the primary key There are 25 columns in the table and a further 8 of these have indexes on them. The table is innodb I have just tried to run the above query 3 times and i got the follwing error Invalid Query Lock wait timeout exceeded; try restarting transaction AFAIK innodb locks on row level, so does that mean that something else is locking that row and won't let me update. If so how can i find out what? Here is a copy of what mytop says at the time the query is being run Thanks for any help MySQL on localhost (4.1.8a-Debian_1-log) up 4+08:28:06 [15:07:19] Queries: 6.0M qps: 17 Slow: 120.0 Se/In/Up/De(%): 68/19/01/00 qps now:8 Slow qps: 0.0 Threads:7 ( 3/ 0) 77/00/00/00 Cache Hits: 3.0M Hits/s: 8.5 Hits now: 4.2 Ratio: 74.2% Ratio now: 70.0% Key Efficiency: 97.7% Bps in/out: 6.8k/ 9.8k Now in/out: 3.5k/ 5.0k Id User Host/IP DB TimeCmd Query or State -- --- -- --- -- 554082 root localhost test 0 Query show full processlist 603034 root localhost0 Sleep 603086 root localhost0 Sleep 602989 root localhost1 Sleep 603301 root localhost1 Sleep 604008 root localhost 11 Query UPDATE dbseXyzOrders.tblOrder SET intPoUid = 97 WHERE intOrde 389249 repl clank 133504 Binlog Has sent all binlog to slave; waiting for binlog to be update -- 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: innodb locking
I have ever meet this problem, however i never found best solutions. Make sure that there're no other session that update the data with AUTOCOMMIT=0 If there's another session with autocommi=0 and update the data, kill it first so your session will not timeout lock use SHOW INNODB STATUS to see what happen. Tony Leake wrote: Hi, I have a query: UPDATE dbseXyzOrders.tblOrder SET intPoUid = 98 WHERE intOrderUid = 10798 intOrderUid is the primary key There are 25 columns in the table and a further 8 of these have indexes on them. The table is innodb I have just tried to run the above query 3 times and i got the follwing error Invalid Query Lock wait timeout exceeded; try restarting transaction AFAIK innodb locks on row level, so does that mean that something else is locking that row and won't let me update. If so how can i find out what? Here is a copy of what mytop says at the time the query is being run Thanks for any help MySQL on localhost (4.1.8a-Debian_1-log) up 4+08:28:06 [15:07:19] Queries: 6.0M qps: 17 Slow: 120.0 Se/In/Up/De(%): 68/19/01/00 qps now:8 Slow qps: 0.0 Threads:7 ( 3/ 0) 77/00/00/00 Cache Hits: 3.0M Hits/s: 8.5 Hits now: 4.2 Ratio: 74.2% Ratio now: 70.0% Key Efficiency: 97.7% Bps in/out: 6.8k/ 9.8k Now in/out: 3.5k/ 5.0k Id User Host/IP DB TimeCmd Query or State -- --- -- --- -- 554082 root localhost test 0 Query show full processlist 603034 root localhost0 Sleep 603086 root localhost0 Sleep 602989 root localhost1 Sleep 603301 root localhost1 Sleep 604008 root localhost 11 Query UPDATE dbseXyzOrders.tblOrder SET intPoUid = 97 WHERE intOrde 389249 repl clank 133504 Binlog Has sent all binlog to slave; waiting for binlog to be update -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cursors in 5.0.13
On Wednesday 05 October 2005 04:05 am, Rob Hall wrote: Having a few problems using cursors in 5.0.13 and I don't know wether it's an 'operator error' :) Should this work? when loading a procedure, do show warnings after it.. It tells you what problems its running into.. So what error are you getting? Jeff pgpZjgXtyciGn.pgp Description: PGP signature
Re: innodb locking
Tony Leake wrote: Hi, I have a query: UPDATE dbseXyzOrders.tblOrder SET intPoUid = 98 WHERE intOrderUid = 10798 intOrderUid is the primary key There are 25 columns in the table and a further 8 of these have indexes on them. The table is innodb I have just tried to run the above query 3 times and i got the follwing error Invalid Query Lock wait timeout exceeded; try restarting transaction - try using SHOW INNODB STATUS. That will give you more info on table locks. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb locking
Since you are using repeatable read, this should not be a problem. And regarding autocommit=0, I don't think it is playing any role here. From the show processlist, if at all, at the max there were only two processes acting on the row. But still this should not happen as your query should be kept in the wait queue and served back once the lock gets free. Also check that no I/O contention is going on(This may be the cause to wait for longer times for getting the lock), when you get into this problem. Also have a look at your INNODB parameters and find out if any tuning is required. sujay -Original Message- From: Tony Leake [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 7:55 PM To: Sujay Koduri Cc: mysql@lists.mysql.com Subject: RE: innodb locking On Wed, 2005-10-05 at 07:12 -0700, Sujay Koduri wrote: Is this happening every time you try this, or it happened first time? It's not every time but this is not the first. Mostly the query is ok, but I would like to find out why it's happening. Also can you please tell what isolation level are you using now. I am using the default which i beleive is REPEATABLE READ. The particular query that is failing is done using autocommit as it a standalone query. Thanks tony sujay -Original Message- From: Tony Leake [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 7:27 PM To: mysql@lists.mysql.com Subject: innodb locking Hi, I have a query: UPDATE dbseXyzOrders.tblOrder SET intPoUid = 98 WHERE intOrderUid = 10798 intOrderUid is the primary key There are 25 columns in the table and a further 8 of these have indexes on them. The table is innodb I have just tried to run the above query 3 times and i got the follwing error Invalid Query Lock wait timeout exceeded; try restarting transaction AFAIK innodb locks on row level, so does that mean that something else is locking that row and won't let me update. If so how can i find out what? Here is a copy of what mytop says at the time the query is being run Thanks for any help MySQL on localhost (4.1.8a-Debian_1-log) up 4+08:28:06 [15:07:19] Queries: 6.0M qps: 17 Slow: 120.0 Se/In/Up/De(%): 68/19/01/00 qps now:8 Slow qps: 0.0 Threads:7 ( 3/ 0) 77/00/00/00 Cache Hits: 3.0M Hits/s: 8.5 Hits now: 4.2 Ratio: 74.2% Ratio now: 70.0% Key Efficiency: 97.7% Bps in/out: 6.8k/ 9.8k Now in/out: 3.5k/ 5.0k Id User Host/IP DB TimeCmd Query or State -- --- -- --- -- 554082 root localhost test 0 Query show full processlist 603034 root localhost0 Sleep 603086 root localhost0 Sleep 602989 root localhost1 Sleep 603301 root localhost1 Sleep 604008 root localhost 11 Query UPDATE dbseXyzOrders.tblOrder SET intPoUid = 97 WHERE intOrde 389249 repl clank 133504 Binlog Has sent all binlog to slave; waiting for binlog to be update -- 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]
content of the table deleted. can I recover it?
Accidentally I deleted a whole content of the table. Since it happened while I was in a testing area (I didn#8217;t do a back up) #8211; no harm done. But, I would like to know is there any chance to recover lost data? Something like Trash can and you can put it back? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No of records
Hi, I have implemented pagination for the search functionality in my application. Since I have a lot of data, I thought in memory paging would not be a good option and opted for paging at the database layer. To do this I am retrieving the no of records using the following query and then again executing another query which actually limits the no of records to 20. I am facing performance issues with the query. Since I am executing this complex query twice, once to get the records and another time to get the first 20 records, the execution time is double and is very slow. I can probably bring down the execution time by half if I have any other way to get the no. of records. Does anyone have any suggestions? -Harini select count(emp.id) from executive as exec1 , company comp, target_company targetComp, employment as emp where emp.executive_id = exec1.id and emp.company_id = comp.id and comp.id = targetComp.company_id and (emp.title like 'Vice-President' OR emp.title like 'Vice-President %' OR emp.title like 'Vice-President, %' OR emp.title like 'Vice-President. %' OR emp.title like '% Vice-President %' OR emp.title like '% Vice-President, %' OR emp.title like '% Vice-President. %' OR emp.title like '% Vice-President' OR emp.title like '% Vice-President.') and emp.active = 1 select emp.id as id, exec1.firstName as firstName, exec1.lastName as lastName, exec1.id as execId, comp.name as name, comp.ticker as ticker, emp.title as title from executive as exec1 , company comp, target_company targetComp, employment as emp where emp.executive_id = exec1.id and emp.company_id = comp.id and comp.id = targetComp.company_id and ((emp.title like 'V.P.' OR emp.title like 'V.P. %' OR emp.title like 'V.P., %' OR emp.title like 'V.P.. %' OR emp.title like '% V.P. %' OR emp.title like '% V.P., %' OR emp.title like '% V.P.. %' OR emp.title like '% V.P.' OR emp.title like '% V.P..') OR (emp.title like 'Vice-President' OR emp.title like 'Vice-President %' OR emp.title like 'Vice-President, %' OR emp.title like 'Vice-President. %' OR emp.title like '% Vice-President %' OR emp.title like '% Vice-President, %' OR emp.title like '% Vice-President. %' OR emp.title like '% Vice-President' OR emp.title like '% Vice-President.') and emp.active = 1 order by lastName, firstName, id limit 0,20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No of records
In the last episode (Oct 05), Harini Raghavan said: I have implemented pagination for the search functionality in my application. Since I have a lot of data, I thought in memory paging would not be a good option and opted for paging at the database layer. To do this I am retrieving the no of records using the following query and then again executing another query which actually limits the no of records to 20. I am facing performance issues with the query. Since I am executing this complex query twice, once to get the records and another time to get the first 20 records, the execution time is double and is very slow. I can probably bring down the execution time by half if I have any other way to get the no. of records. Does anyone have any suggestions? Since your WHERE clause has LIKE %... comparisons, you're doing a full table scan on emp for both queries. How about in the first query, selecting emp.id instead of count(emp.id), and for the 2nd query, put AND emp.id IN (insert first 20 ids here) in place of any comparison on the emp table? That should make the 2nd query return almost immediately. A better solution would be to normalize emp.title out into its own table so you would just have .. AND (emp.titleid = title.id AND title.desc='Vice President') , which would most likely let mysql use index lookups instead of a full table scan. select count(emp.id) from executive as exec1 , company comp, target_company targetComp, employment as emp where emp.executive_id = exec1.id and emp.company_id = comp.id and comp.id = targetComp.company_id and (emp.title like 'Vice-President' OR emp.title like 'Vice-President %' OR emp.title like 'Vice-President, %' OR emp.title like 'Vice-President. %' OR emp.title like '% Vice-President %' OR emp.title like '% Vice-President, %' OR emp.title like '% Vice-President. %' OR emp.title like '% Vice-President' OR emp.title like '% Vice-President.') and emp.active = 1 select emp.id as id, exec1.firstName as firstName, exec1.lastName as lastName, exec1.id as execId, comp.name as name, comp.ticker as ticker, emp.title as title from executive as exec1 , company comp, target_company targetComp, employment as emp where emp.executive_id = exec1.id and emp.company_id = comp.id and comp.id = targetComp.company_id and ((emp.title like 'V.P.' OR emp.title like 'V.P. %' OR emp.title like 'V.P., %' OR emp.title like 'V.P.. %' OR emp.title like '% V.P. %' OR emp.title like '% V.P., %' OR emp.title like '% V.P.. %' OR emp.title like '% V.P.' OR emp.title like '% V.P..') OR (emp.title like 'Vice-President' OR emp.title like 'Vice-President %' OR emp.title like 'Vice-President, %' OR emp.title like 'Vice-President. %' OR emp.title like '% Vice-President %' OR emp.title like '% Vice-President, %' OR emp.title like '% Vice-President. %' OR emp.title like '% Vice-President' OR emp.title like '% Vice-President.') and emp.active = 1 order by lastName, firstName, id limit 0,20 -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No of records
Harini Raghavan [EMAIL PROTECTED] wrote on 10/05/2005 11:21:26 AM: Hi, I have implemented pagination for the search functionality in my application. Since I have a lot of data, I thought in memory paging would not be a good option and opted for paging at the database layer. To do this I am retrieving the no of records using the following query and then again executing another query which actually limits the no of records to 20. I am facing performance issues with the query. Since I am executing this complex query twice, once to get the records and another time to get the first 20 records, the execution time is double and is very slow. I can probably bring down the execution time by half if I have any other way to get the no. of records. Does anyone have any suggestions? -Harini snipped first query select emp.id as id, exec1.firstName as firstName, exec1.lastName as lastName, exec1.id as execId, comp.name as name, comp.ticker as ticker, emp.title as title from executive as exec1 , company comp, target_company targetComp, employment as emp where emp.executive_id = exec1.id and emp.company_id = comp.id and comp.id = targetComp.company_id and ((emp.title like 'V.P.' OR emp.title like 'V.P. %' OR emp.title like 'V.P., %' OR emp.title like 'V.P.. %' OR emp.title like '% V.P. %' OR emp.title like '% V.P., %' OR emp.title like '% V.P.. %' OR emp.title like '% V.P.' OR emp.title like '% V.P..') OR (emp.title like 'Vice-President' OR emp.title like 'Vice-President %' OR emp.title like 'Vice-President, %' OR emp.title like 'Vice-President. %' OR emp.title like '% Vice-President %' OR emp.title like '% Vice-President, %' OR emp.title like '% Vice-President. %' OR emp.title like '% Vice-President' OR emp.title like '% Vice-President.') and emp.active = 1 order by lastName, firstName, id limit 0,20 Only execute the second query after you add the clause SQL_CALC_FOUND_ROWS to it. After the query comes back, you not only have the first 20 rows, you can also get the number of rows you would have returned if you didn't have the LIMIT clause by calling the function SELECT FOUND_ROWS(). http://dev.mysql.com/doc/mysql/en/select.html http://dev.mysql.com/doc/mysql/en/information-functions.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: How to avoid redundancy between PK and indices ?
It depends.. if this is your create table statement: CREATE TABLE foo ( a smallint NOT NULL, b smallint NOT NULL, c smallint NOT NULL, d smallint NOT NULL, e smallint NOT NULL, f smallint NOT NULL, PRIMARY KEY (a,b,c,d,e,f) ); Then only one unique index is being created on the concatenate key of a+b+c+d+e+f. Queries on any fields other than A will cause a full table scan. On the other hand, if your create table is: CREATE TABLE foo ( a smallint NOT NULL, b smallint NOT NULL, c smallint NOT NULL, d smallint NOT NULL, e smallint NOT NULL, f smallint NOT NULL, PRIMARY KEY (a,b,c,d,e,f), KEY a (a), KEY b (b), KEY c (c), KEY d (d), KEY e (e), KEY f (f) ); This will create the primary key, plus six additional indexes, each of which is queryable. But in this case, the KEY a (a) non-unique index is redundent with the primary key, so to do what you want - a unique index on a+b+c+d+e+f PLUS the ability to independtly search the b c d e and f fields, here is the create table you'll need to use: CREATE TABLE foo ( a smallint NOT NULL, b smallint NOT NULL, c smallint NOT NULL, d smallint NOT NULL, e smallint NOT NULL, f smallint NOT NULL, PRIMARY KEY (a,b,c,d,e,f), KEY b (b), KEY c (c), KEY d (d), KEY e (e), KEY f (f) ); -- -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)
Devananda wrote: One of our servers which is configured primarily for InnoDB has crashed in the same fashion three times in the past 48 hours. I've been going over the error log, and I would like to know if anyone else has seen similar errors, or can give me some pointers to work out this problem. (truncated message) After googling and searching the mysql list archive and forums, and finding other folks who ran into the semaphore wait has lasted 600 seconds error, I saw no consistency between what caused that error for the other people and for my server. I started checking the error log very closely, and came upon something that seems very odd to me; I am hoping someone else can explain this to me (maybe I'm making a silly mistake?). I cut out the very last dump of InnoDB Monitor Output in the server.err file (just to make it easier to read and grep through). I pulled out all the transactions that have a lock struct, and examined each. They are ALL inserts (large ones), but there are two things which seem wrong to me: there are 9 transactions with lock structs listed, even though there are only 8 queries inside InnoDB; two of the locks are on the same table. If the same table was locked by two threads at the same time, that would explain the crash (wouldn't it?), but how is that possible? I thought the express purpose of semaphores was to prevent that. I'm hoping someone else can shed some light on this :) Thanks! Best Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running MySQL tests
Hello, Any inputs or information on running MySQL tests under Valgrind, http://valgrind.org . Thanks -Manish On Tue, 2005-10-04 at 14:25 -0700, Manish Marathe wrote: Hello All, I was building mysql after checking out from the bk repository. In that process, while executing mysql tests I executed them in the environment of Valgrind with the output in xml. After each test case Valgrind thinks output is completed so it completes its xml outputfile, but when the next test case starts, Valgrind gets confused. I have learnt that MySQL is already tested under Valgrind. Does anyone has any idea about this ? Any help would be highly appreciated. Thanks -Manish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Financial return calculations help please
I have researched repeatedly and cannot find an answer to the following. I need to do something like the following (There is probably an easier way). end_date - start_date = diff / start_date = return for period The table contains 401k investment values. Ideas, pointers, etc.? I am using mysql v5.0.12 on Debian Linux. TIA, Jim -- I started using something better than the standard back when IBM advertised OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux. You don't have to accept less than you deserve. Use the Power of the Penguin Registered Linux user #316735 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No of records
Hi Green, I tried using SQL_CALC_FOUND_ROWS as suggested by you. I am executing some other queries following the query that has the clause SQL_CALC_FOUND_ROWS. The documentation says in such a case we can save the row count using *|SET @rows = FOUND_ROWS(); |*How can I retrieve this row count value later? -Harini [EMAIL PROTECTED] wrote: Harini Raghavan [EMAIL PROTECTED] wrote on 10/05/2005 11:21:26 AM: Hi, I have implemented pagination for the search functionality in my application. Since I have a lot of data, I thought in memory paging would not be a good option and opted for paging at the database layer. To do this I am retrieving the no of records using the following query and then again executing another query which actually limits the no of records to 20. I am facing performance issues with the query. Since I am executing this complex query twice, once to get the records and another time to get the first 20 records, the execution time is double and is very slow. I can probably bring down the execution time by half if I have any other way to get the no. of records. Does anyone have any suggestions? -Harini snipped first query select emp.id as id, exec1.firstName as firstName, exec1.lastName as lastName, exec1.id as execId, comp.name as name, comp.ticker as ticker, emp.title as title from executive as exec1 , company comp, target_company targetComp, employment as emp where emp.executive_id = exec1.id and emp.company_id = comp.id and comp.id = targetComp.company_id and ((emp.title like 'V.P.' OR emp.title like 'V.P. %' OR emp.title like 'V.P., %' OR emp.title like 'V.P.. %' OR emp.title like '% V.P. %' OR emp.title like '% V.P., %' OR emp.title like '% V.P.. %' OR emp.title like '% V.P.' OR emp.title like '% V.P..') OR (emp.title like 'Vice-President' OR emp.title like 'Vice-President %' OR emp.title like 'Vice-President, %' OR emp.title like 'Vice-President. %' OR emp.title like '% Vice-President %' OR emp.title like '% Vice-President, %' OR emp.title like '% Vice-President. %' OR emp.title like '% Vice-President' OR emp.title like '% Vice-President.') and emp.active = 1 order by lastName, firstName, id limit 0,20 Only execute the second query after you add the clause SQL_CALC_FOUND_ROWS to it. After the query comes back, you not only have the first 20 rows, you can also get the number of rows you would have returned if you didn't have the LIMIT clause by calling the function SELECT FOUND_ROWS(). http://dev.mysql.com/doc/mysql/en/select.html http://dev.mysql.com/doc/mysql/en/information-functions.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Financial return calculations help please
Jim Seymour wrote: I have researched repeatedly and cannot find an answer to the following. I need to do something like the following (There is probably an easier way). end_date - start_date = diff / start_date = return for period The table contains 401k investment values. Ideas, pointers, etc.? I am using mysql v5.0.12 on Debian Linux. TIA, Jim Depending on how fancy you want to get, you would calculate either an IRR (Internal Rate of Return) that basically says, what interest rate would I need to get on the funds to end up with the same results I actually achived. Note that calculating an interest rate is going to require an iterative approximation. http://www.investopedia.com/offsite.asp?URL=http://invest-faq.com/articles/analy-int-rate-return.html includes a description of the general concepts and a pointer to some programs, including (401-calc) that calculate IRR. http://fox.wikis.com/wc.dll?Wiki~InternalRateOfReturn has some code that uses Newton-Raphson approximation to calculate IRR. Googling for Internal Rate of Return can find you a lot more links. There are also more sophisticated techniques like FMRR that are used when you have minimum investment amounts and a lower rate of return on short term investments of money waiting to accumulate the larger somes needed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to list foreign keys
HI everybody I'm tryng to find a way to know if a field is a foreign key, by example if I run this describe tablename; in the Key colum I got PRI for the primary key field, somebody know a way to get the foreign keys ? Regards Daniel
RE: how to list foreign keys
IF you are on 5.0.x you can use INFORMATION_SCHEMA 21.1.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table http://dev.mysql.com/doc/mysql/en/key-column-usage-table.html -Original Message- From: Operator [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 3:02 PM To: mysql@lists.mysql.com Subject: how to list foreign keys HI everybody I'm tryng to find a way to know if a field is a foreign key, by example if I run this describe tablename; in the Key colum I got PRI for the primary key field, somebody know a way to get the foreign keys ? Regards Daniel
Re: bug report
Hello. Please, could you send a more detailed report. Include information about MySQL and operating system versions. See: http://dev.mysql.com/doc/mysql/en/Bug_reports.html You may want to force a recovery. See: http://dev.mysql.com/doc/mysql/en/forcing-recovery.html Pierre-Henry Perret [EMAIL PROTECTED] wrote: When starting mysqld, I got the err message (in file) 051005 05:26:47 mysqld started 051005 5:26:47 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43902 InnoDB: Doing recovery: scanned up to log sequence number 0 43902 051005 5:26:47 InnoDB: Error: trying to access a stray pointer 88b8fff8 InnoDB: buf pool start is at 8b8, end at 938 InnoDB: Probable reason is database corruption or memory InnoDB: corruption. If this happens in an InnoDB database recovery, InnoDB: you can look from section 6.1 at http://www.innodb.com/ibman.html InnoDB: how to force recovery. 051005 5:26:47 InnoDB: Assertion failure in thread 137490432 in file ../../innobase/include/buf0buf.ic line 261 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=268435456 read_buffer_size=1044480 Fatal signal 11 while backtracing 051005 05:26:47 mysqld ended -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JBoss queries aren't cached by MySQL
Hello. If you can just check your application with the latest release of MySQL (4.1.14 now). Turn on the general log to be sure that MySQL receives unchanged queries and no session variables has been changed. Al Caponi [EMAIL PROTECTED] wrote: Hi All, I didn't see any follow-up on this thread. Hope I can get some hint :) How to get the query cache to work with Jboss? I've got my app running on the following JBoss 3.2.2. (Unable to upgrade right now) MySQL 4.0.20d mysql-connector-java-3.1.10-bin.jar I have switched on the query_cache as follows: query_cache_limit=2M query_cache_size=32M query_cache_type=1 However, after triggering a few queries from my applications, the query cache is not used at all: SHOW STATUS LIKE 'Qcache%'; Qcache_queries_in_cache,0 Qcache_inserts,0 Qcache_hits,0 Qcache_lowmem_prunes,0 Qcache_not_cached,328 Qcache_free_memory,33545632 Qcache_free_blocks,1 Qcache_total_blocks,1 Note: Running queries from MySQL-Front hits the cache - the above counters increase. Regards, Al -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 12:33 AM To: Rafal Kedziorski Cc: mysql@lists.mysql.com Subject: Re: JBoss queries aren't cached by MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rafal Kedziorski wrote: Hi, we have the problem, that queries generated by JBoss or our code which runns under JBoss will be not cached by MySQL. The same query sendet from an external application or MySQLFront will be cached by the same MySQL. I'm using JBoss 3.2.5 with JDBC 3.0.16 and MySQL 4.0.23a. Any idea why? Regards, Rafal Rafal, Define what you mean by not cached by MySQLDo you mean the query cache? If so, what do your query cache settings look like? -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCbm1JtvXNTca6JD8RAtT9AJ9k8HnIkRh+U9UE3ROf+eff5ZudRgCgxNNI KeS6Iiq5ttoKjZsaDlyXV74= =gCL+ -END PGP SIGNATURE- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Query Performance
You're still doing a full table scan with REGEX, so you'll never get
it really fast. I was thinking it would be slightly faster because of
less comparisons. It's the full table scan and no use of indexes that
you want to get away from. Without doing that, the only way to get
things faster is with faster disks and more RAM for caching.
I assume you created a full text index on the title field? Sorry, but
have to ask. You can perform a full text search without full text
index, but it's not going to be fast since no index is used.
Try adding a plus sign in front of the words or phrases you want to
search on when using IN BOOLEAN MODE.
MATCH(emp.title) AGAINST('+Vice President' IN BOOLEAN MODE)
Although I don't know why it would be matching Vice Chairman, that's
just not right. Are you patched to the latest version of MySQL? I
remember getting some really weird results with full text searches
with an older version of 4.1.
Regardless, you will have to adjust the full text settings and
reindex to find words like VP, since that is shorter than the default
4 character word length minimum. I had to do this to search on things
like VB (Visual Basic). MySQL still won't index V.P. since
periods aren't valid characters for words and you wouldn't want MySQL
indexing single letter words. I have a similar problem trying to
search on C, the programming language. I've figured out a work
around for C++, but not C. I think you're stuck with doing at
least a little data scrubbing and do cleanup for new data before saving.
On Oct 5, 2005, at 7:05 AM, Harini Raghavan wrote:
Hi Brent,
Using REGEXP did not really help with the performance. I need to do
whole word matching sowould prefer not to do LIKE '%Vice President
%' as it may return ome negative results.
I separated out some of the text based columns in to a different
table using MYISAM storage engine. Using FULLTEXT with IN BOOLEAN
MODE was a bit slow, but without the IN BOOLEAN MODE, it seems
fast. However I think the phrase searches are not working properly.
For example the below query returned records where the title was
'Vice Chairman':
select emp.title from employment_title emp where MATCH(emp.title)
AGAINST('Vice President')
I have verified the syntax of phrase query, and it seems to bve
correct. Any idea why this is happening? Also if I have multiple
phrases is the following query syntax correct?
select emp.title from employment_title emp where MATCH(emp.title)
AGAINST('V.P. VP Vice President Vice-President')
Thanks,
Harini
Brent Baisley wrote:
Egads! That's a lot of OR's. You probably want to use REGEXP
instead of all those OR's. REGEXP can be slow, but you'll be
doing far less comparisons than what you have in your current
query so it may be faster than what you have.
Something like this:
SELECT ...
WHERE ... emp.title REGEXP 'V[.]?P[.]?' OR emp.title REGEXP 'Vice
[-]? President'
I think that will match everything you have. At the very least
you don't have to check for periods, commas, space and everything
else before and after what you are searching on. Searching on
emp.title LIKE '%Vice President%', will find 'Vice President'
anywhere in the text, regardless of what come before or after it.
One thing you should think about trying to do is breaking out the
fields you need to do a full text search on into a separate table
that you can make MYISAM. You'll be adding a JOIN to your queries
that need to do the full text search, but it should be a lot
quicker and your queries much simpler. You'll then have a mix of
InnoDB and MYISAM tables, which is perfectly legal.
On Oct 4, 2005, at 11:17 AM, Harini Raghavan wrote:
Hi,
I am using MYSQL 4.1 database in my J2ee application. I am
facing performance issues with some queries that are being run
on text fields. Since MYISAM storage engine does not support
transactions (and my application requires the database tables to
support transaction), I have not been able to use FULL TEXT
searches. I need to perform token matching against the text
fields and so use a lot of LIKE expressions in the query. Below
is one such query which is taking almost 2 mins to execute.
select count(emp.id) from executive as exec1 , company comp,
target_company targetComp, employment as emp
where emp.executive_id = exec1.id
and emp.company_id = comp.id
and comp.id = targetComp.company_id and ((emp.title like 'V.P.'
OR emp.title like 'V.P. %' OR emp.title like 'V.P., %' OR
emp.title like 'V.P.. %' OR emp.title like '% V.P. %'
OR emp.title like '% V.P., %' OR emp.title like '% V.P.. %' OR
emp.title like '% V.P.' OR emp.title like '% V.P..')
OR (emp.title like 'VP' OR emp.title like 'VP %' OR emp.title
like 'VP, %' OR emp.title like 'VP. %' OR emp.title like '% VP %'
OR emp.title like '% VP, %' OR emp.title like '% VP. %' OR
emp.title like '% VP' OR emp.title like '% VP.')
OR (emp.title
compare tables from two systems
We have two clusters running the same data independently and saving output to databases on two systems (two hosts) respectively. We need to compare the output each day. Is there an easy way to compare tables from two systems? We are sure the structure of the tables are all the same and we need to compare the data. Any advice is welcome. Thanks. Claire __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Administrator on 10.3 Server -HELP
Hello List, I have installed the mysql database on the osx 10.3 server machine. along with the myodbc connector. I also installed the administrator module, by simply draging it over to the application folder like it says. When I go to launce the application, it does nothing. I tried restarting the machine, and am logging with the administrator account. Any Ideas?
Re: compare tables from two systems
On 10/5/05, Claire Lee [EMAIL PROTECTED] wrote: We have two clusters running the same data independently and saving output to databases on two systems (two hosts) respectively. We need to compare the output each day. Is there an easy way to compare tables from two systems? We are sure the structure of the tables are all the same and we need to compare the data. Any advice is welcome. Thanks. mysqldump -l -B db1 db1.sql mysqldump -l -B db2 db2.sql diff db1.sql db2.sql diff.sql -- Greg Donald Zend Certified Engineer MySQL Core Certification http://destiney.com/
Re: compare tables from two systems
- Original Message - From: Claire Lee [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, October 05, 2005 5:58 PM Subject: compare tables from two systems We have two clusters running the same data independently and saving output to databases on two systems (two hosts) respectively. We need to compare the output each day. Is there an easy way to compare tables from two systems? We are sure the structure of the tables are all the same and we need to compare the data. Any advice is welcome. Thanks. I'm not sure if there are any existing table comparison tools out there; in an ideal world, someone would have written something that works on any relational database that would compare any two tables to see if they are the same. I've never actually heard of such a tool but I've never looked for one either. If there is no such tool out there, you could create one of your own without too much difficulty. One way that should be pretty easy would be to do a mysqldump of each table then do standard file compares of each of the two resulting files. I have a freeware program for Windows called ExamDiff which seems to do that job okay, although I've never tried comparing output files from mysqldump with it. There may be one very difficult problem with this approach: it assumes that the mysqldump will write the INSERT statements for the individual rows in a specific order, ideally primary key sequence, for each table. Unfortunately, I don't see any options for mysqldump that ensure that this will happen and I don't see any statement in the manual that say it will happen automatically. Therefore, it is entirely possible that the mysqldumps of each table will write the INSERTs in some sequence other than primary key order. For example, mysqldump might use the sequence in which the rows were stored, retrieving them from oldest to newest, rather than primary key sequence. In any case where primary key sequence is not used - or where no primary key exists on the table - the two mysqldump files could have completely different sequences even though they have the identical rows. That would almost certainly preclude this approach working. Why not just try doing mysqldumps of each of your two tables and then do a file compare of the two files and see what happens? It shouldn't take long and you'll soon see if this approach will work. By the way, why are you keeping two independent - yet supposedly identical copies - of your tables? Isn't that redundant *and* expensive? Wouldn't it make more sense to backup a single copy of the database regularly so that you can restore when you have problems? Or, if you need the same database in two different locations, why not use replication to ensure that the two copies are automatically synchronised? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.9/116 - Release Date: 30/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table
Incoming from Pooly: 2005/10/3, s. keeling [EMAIL PROTECTED]: I'd like to add a bit of history data to a table (who changed a record last, and when it was last changed). Is this the way to do it? [snip] alter table MEMBERS alter CHG_DATE set default CURRENT_DATE btw, you could do : alter table MEMBERS add CHG_BY varchar(3) not null default sbk, add CHG_DATE date not null default CURRENT_DATE on update current_date (not tested) It doesn't appear to work as I was hoping. According to the manual the target for keyword default has to be a literal. mysql alter table MEMBERS - alter CHANGED_DATE set default CHANGED_DATE = DATE; ERROR 1064: You have an error in your SQL syntax. Check the \ manual that corresponds to your MySQL server version for the \ right syntax to use near 'CHANGED_DATE = DATE' at line 2 mysql alter table MEMBERS - alter CHANGED_DATE set default CHANGED_DATE = CURRENT_DATE; ERROR 1064: You have an error in your SQL syntax. Check the \ manual that corresponds to your MySQL server version for the \ right syntax to use near 'CHANGED_DATE = CURRENT_DATE' at line 2 Drat. -- Any technology distinguishable from magic is insufficiently advanced. (*)http://www.spots.ab.ca/~keeling Please don't Cc: me. - - For the ChiComms: democracy human rights Taiwan Independence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Easy or not so easy GROUP BY
I'm trying to group some sub categories with a concatenated result. I'm trying to get the max sub for each item per month. I think it should be fairly easy but it is kicking my butt. My data looks like this, +++--+ | month | item | sub | +++--+ | 8|5 | NULL | +++--+ | 8|4 | a | +++--+ | 8|6 | NULL | +++--+ | 8|6 | a | +++--+ | 8|5 | a | +++--+ | 8|4 | b | +++--+ | 9|1 | NULL | +++--+ | 9|2 | NULL | +++--+ | 9|1 | a | +++--+ | 9|3 | NULL | +++--+ | 9|2 | a | +++--+ | 9|1 | b | +++--+ | 9|4 | NULL | +++--+ | 9|4 | a | +++--+ | 9|2 | b | +++--+ | 9|1 | c | +++--+ | 10 |1 | NULL | +++--+ | 10 |1 | a | +++--+ | 10 |2 | NULL | +++--+ I'm not having a problem getting a concatenated result but I am having difficulty getting my data grouped correctly. My results should look like this. +---+ | MAX Group | +---+ | 8-4b| +---+ | 8-5a| +---+ | 8-6a| +---+ | 9-1c| +---+ | 9-2b| +---+ | 9-3 | +---+ | 9-4a| +---+ | 10-1a | +---+ | 10-2| +---+ - Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error Running MySQL tests
Hello all, I checked out the MySQL code and was running tests using mysql-test- run.sh. I keep getting this error: ERROR: /tmp/mysql/mysql-test/var/run/master.pid was not created in 400 seconds; Aborting Any insights, suggestions ? Thank You -Manish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Encryption for mySQL 3.23
On Oct 4, 2005, at 4:34 PM, Jeff Pflueger wrote: Where might I find the key so that I can send it along to them? any suggestions on this? The password is what you need to send. The encryption and decryption functions generate a key from the password. Internally, the key that the AES/Rijndal algorithm uses is a 128 bit (or 256 bit) sequence, but the AES_ENCRYPT() function will convert the password (in a deterministic way) probably using a hash function such as SHA-n or MD5 into a 128 bit sequence. If you could describe a bit more fully what it is that you are trying to achieve we might be able to suggest alternative strategies that will better meet your needs than encrypting particular fields this way. -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lock wait timeout exceeded
Thanks for the help. I finally got to the bottom of the problem. It seems that on Windows, by default, if the network cable is momentarily disconnected, Windows shuts down all TCP connection within the box and basically disables the the network interface. However the MySQL server running on another host (or any other server for that matter) is blissfully unaware that this has happened so the MySQL Server end of the connection continues to hold the X lock on the record (remember I'm using innodb tables). When the network cable gets reinserted the interface comes back up. However when you rerun the application it will get 1205 errors when it tries to do the same work as the orphaned MySQL server connection is still holding the X lock. The only way out of this is to kill the orphaned session. The issue with windows is described here: http://www.support.microsoft.com/default.aspx?scid=kb;en-us;239924 However similar problems will occur if a client host crashed (loss of power etc). Any suggestions as to how to mitigate this? Jo -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 04, 2005 1:33 AM To: mysql@lists.mysql.com Subject: Re: Lock wait timeout exceeded Hello. Any ideas where to go with this? How can I find out which session is holding the lock and what lock it is? SHOW INNODB STATUS and, probably, SHOW PROCESSLIST can give you some additional information about what's going on. Use: show variables like 'tx_isolation'; to find out the transaction isolation level. See: http://dev.mysql.com/doc/mysql/en/InnoDB_Monitor.html http://dev.mysql.com/doc/mysql/en/show-processlist.html Jonathan Stockley [EMAIL PROTECTED] wrote: Hi, We're having a problem with lock wait timeout exceeded errors. We are exclusively using innodb tables apart from the mysql database. The problem seems to be with the way we are simulating sequences. There is a table called SEQUENCES defined as follows: CREATE TABLE IF NOT EXISTS Sequences ( tableName VARCHAR(64) NOT NULL PRIMARY KEY, id INTEGER UNSIGNED NOT NULL ) We then generate the next number for a given table as follows: UPDATE Sequences SET id=LAST_INSERT_ID(id+1) WHERE tableName = 'THE_TABLE_NAME' There are several hundred rows in the Sequences table. The general flow is that for each row or set of rows to be inserted we do the following: (AUTOCOMMIT is turned OFF). 1. begin transaction 2. get next sequence number for given target table using above UPDATE statement. 3. insert row into target table 4. if more rows to insert go to step 2 5. commit transaction We are not using LOCK TABLE anywhere and we are using the default transaction isolation level which I believe is READ-COMMITED. Every so often we get the 1205 error lock wait timeout exceeded. Any ideas where to go with this? How can I find out which session is holding the lock and what lock it is? Thanks, J -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to avoid redundancy between PK and indices ?
Hi Hank, You are quite right. I need separate non-unique indices on a, b, c, d, e and f to avoid table scans. And when each combi must be unique I need a Primary Key (a,b,c,d,e,f). And only Key a (a) seems to be redundant with the primary key ... Suppose there would be a PK (a,b,c,d,e,f) defined, without a separate PK index. And let's assume some rows like: columns:a b c d e f row1 has: 1 1 1 1 1 1 row2 has: 1 1 1 1 1 2 row3 has: 1 1 1 1 1 3 etc. Then checking on unique PK could be done by MySQL internally with: Select Count(*) From myTable Where a=1 And b=1 And c=1 And d=1 And e=1 And f=1; to avoid a duplicate primary key for row1, by using / joining the separate index tables. With this Select query, MySQL could / should make use of the 6 existing separate indices. Uniqueness can be fully guaranteed with these 6 non-unique indices in this case. In other words, a separate PK index is fully redundant in this case, right ? In addition, it would save space without the longer concatenate key of a+b+c+d+e+f. Thanks, Cor - Original Message - From: Hank [EMAIL PROTECTED] To: C.R. Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, October 05, 2005 5:57 PM Subject: Re: How to avoid redundancy between PK and indices ? It depends.. if this is your create table statement: CREATE TABLE foo ( a smallint NOT NULL, b smallint NOT NULL, c smallint NOT NULL, d smallint NOT NULL, e smallint NOT NULL, f smallint NOT NULL, PRIMARY KEY (a,b,c,d,e,f) ); Then only one unique index is being created on the concatenate key of a+b+c+d+e+f. Queries on any fields other than A will cause a full table scan. On the other hand, if your create table is: CREATE TABLE foo ( a smallint NOT NULL, b smallint NOT NULL, c smallint NOT NULL, d smallint NOT NULL, e smallint NOT NULL, f smallint NOT NULL, PRIMARY KEY (a,b,c,d,e,f), KEY a (a), KEY b (b), KEY c (c), KEY d (d), KEY e (e), KEY f (f) ); This will create the primary key, plus six additional indexes, each of which is queryable. But in this case, the KEY a (a) non-unique index is redundent with the primary key, so to do what you want - a unique index on a+b+c+d+e+f PLUS the ability to independtly search the b c d e and f fields, here is the create table you'll need to use: CREATE TABLE foo ( a smallint NOT NULL, b smallint NOT NULL, c smallint NOT NULL, d smallint NOT NULL, e smallint NOT NULL, f smallint NOT NULL, PRIMARY KEY (a,b,c,d,e,f), KEY b (b), KEY c (c), KEY d (d), KEY e (e), KEY f (f) ); -- -Hank -- 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: JBoss queries aren't cached by MySQL
Hi Gleb All, Thanks for replying. I saw a posting on Jboss forum that suggested upgrading MySQL to 4.1 and above (See my previous posting - http://lists.mysql.com/mysql/189988) I tried both MySQL 4.1.14 and 5.0.13-rc but it didn't work for me :( Still diggin... :) Thanks, Alain -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 9:07 PM To: mysql@lists.mysql.com Subject: Re: JBoss queries aren't cached by MySQL Hello. If you can just check your application with the latest release of MySQL (4.1.14 now). Turn on the general log to be sure that MySQL receives unchanged queries and no session variables has been changed. Al Caponi [EMAIL PROTECTED] wrote: Hi All, I didn't see any follow-up on this thread. Hope I can get some hint :) How to get the query cache to work with Jboss? I've got my app running on the following JBoss 3.2.2. (Unable to upgrade right now) MySQL 4.0.20d mysql-connector-java-3.1.10-bin.jar I have switched on the query_cache as follows: query_cache_limit=2M query_cache_size=32M query_cache_type=1 However, after triggering a few queries from my applications, the query cache is not used at all: SHOW STATUS LIKE 'Qcache%'; Qcache_queries_in_cache,0 Qcache_inserts,0 Qcache_hits,0 Qcache_lowmem_prunes,0 Qcache_not_cached,328 Qcache_free_memory,33545632 Qcache_free_blocks,1 Qcache_total_blocks,1 Note: Running queries from MySQL-Front hits the cache - the above counters increase. Regards, Al -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 12:33 AM To: Rafal Kedziorski Cc: mysql@lists.mysql.com Subject: Re: JBoss queries aren't cached by MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rafal Kedziorski wrote: Hi, we have the problem, that queries generated by JBoss or our code which runns under JBoss will be not cached by MySQL. The same query sendet from an external application or MySQLFront will be cached by the same MySQL. I'm using JBoss 3.2.5 with JDBC 3.0.16 and MySQL 4.0.23a. Any idea why? Regards, Rafal Rafal, Define what you mean by not cached by MySQLDo you mean the query cache? If so, what do your query cache settings look like? -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCbm1JtvXNTca6JD8RAtT9AJ9k8HnIkRh+U9UE3ROf+eff5ZudRgCgxNNI KeS6Iiq5ttoKjZsaDlyXV74= =gCL+ -END PGP SIGNATURE- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Send instant messages to your online friends http://asia.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Administrator on 10.3 Server -HELP
Did you drag it from a network drive, or copy in some other way? If so, that may be the problem. I haven't used the MySQL Administrator, but am pretty well versed in application troubleshooting. Send me an email privately if you need some direct help. Adam. -- Adam Randall [EMAIL PROTECTED] Senior Web Architect Stork Avenue, Inc. http://www.storkavenue.com/ (800) 861-5437 AIM/iChat: adamstorkave On Oct 5, 2005, at 6:04 PM, Andrew stolarz wrote: Hello List, I have installed the mysql database on the osx 10.3 server machine. along with the myodbc connector. I also installed the administrator module, by simply draging it over to the application folder like it says. When I go to launce the application, it does nothing. I tried restarting the machine, and am logging with the administrator account. Any Ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help !!!!
Please help to compile !!! My system is Solaris 5.9 i`m instal OpenSSl and now i wish compile MySQL with him : ./configure --prefix=/opt/mysql --with-mysqld-user=mysql --with-openssl --with-openssl-includes=/usr/local/ssl/include --with-openssl-libs=/usr/local/ssl/lib --with-extra-charsets=complex make . if gcc -DHAVE_CONFIG_H -I. -I. -I.. -I../include -I/usr/local/ssl/include -O3 -DDBUG_OFF-D_FILE_OFFSET_BITS=64 -DHAVE_RWLOCK_T -MT test-ssl.o -MD -MP -MF .deps/test-ssl.Tpo -c -o test-ssl.o test-ssl.c; \ then mv -f .deps/test-ssl.Tpo .deps/test-ssl.Po; else rm -f .deps/test-ssl.Tpo; exit 1; fi /bin/bash ../libtool --preserve-dup-deps --mode=link gcc -O3 -DDBUG_OFF -D_FILE_OFFSET_BITS=64 -DHAVE_RWLOCK_T-o test-ssl test-ssl.o ../dbug/libdbug.a libvio.a ../mysys/libmysys.a ../strings/libmystrings.a -L/usr/local/ssl/lib -lssl -lcrypto -lpthread -lthread -lposix4 -lcrypt -lgen -lsocket -lnsl -lm -lpthread -lthread mkdir .libs gcc -O3 -DDBUG_OFF -D_FILE_OFFSET_BITS=64 -DHAVE_RWLOCK_T -o test-ssl test-ssl.o ../dbug/libdbug.a libvio.a ../mysys/libmysys.a ../strings/libmystrings.a -L/usr/local/ssl/lib -lssl -lcrypto -lpthread -lthread -lposix4 -lcrypt -lgen -lsocket -lnsl -lm -lpthread -lthread Undefined first referenced symbol in file dlsym /usr/local/ssl/lib/libcrypto.a(dso_dlfcn.o) (symbol belongs to implicit dependency /usr/lib/libdl.so.1) dlopen /usr/local/ssl/lib/libcrypto.a(dso_dlfcn.o) (symbol belongs to implicit dependency /usr/lib/libdl.so.1) dlclose /usr/local/ssl/lib/libcrypto.a(dso_dlfcn.o) (symbol belongs to implicit dependency /usr/lib/libdl.so.1) dlerror /usr/local/ssl/lib/libcrypto.a(dso_dlfcn.o) (symbol belongs to implicit dependency /usr/lib/libdl.so.1) ld: fatal: Symbol referencing errors. No output written to test-ssl collect2: ld returned 1 exit status *** Error code 1 make: Fatal error: Command failed for target `test-ssl' Current working directory /export/home/mor/mysql-4.1.12/vio *** Error code 1 make: Fatal error: Command failed for target `all-recursive' Current working directory /export/home/mor/mysql-4.1.12 *** Error code 1 make: Fatal error: Command failed for target `all' # # crle Configuration file [version 4]: /var/ld/ld.config Default Library Path (ELF): /usr/lib:/usr/local/ssl/lib:/usr/local/lib Trusted Directories (ELF):/usr/lib/secure (system default) Command line: crle -c /var/ld/ld.config -l /usr/lib:/usr/local/ssl/lib:/usr/local/lib System mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)
Deva, please post the complete output except the transaction data. Regards, Heikki - Original Message - From: Devananda [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, October 05, 2005 7:05 PM Subject: Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout) Devananda wrote: One of our servers which is configured primarily for InnoDB has crashed in the same fashion three times in the past 48 hours. I've been going over the error log, and I would like to know if anyone else has seen similar errors, or can give me some pointers to work out this problem. (truncated message) After googling and searching the mysql list archive and forums, and finding other folks who ran into the semaphore wait has lasted 600 seconds error, I saw no consistency between what caused that error for the other people and for my server. I started checking the error log very closely, and came upon something that seems very odd to me; I am hoping someone else can explain this to me (maybe I'm making a silly mistake?). I cut out the very last dump of InnoDB Monitor Output in the server.err file (just to make it easier to read and grep through). I pulled out all the transactions that have a lock struct, and examined each. They are ALL inserts (large ones), but there are two things which seem wrong to me: there are 9 transactions with lock structs listed, even though there are only 8 queries inside InnoDB; two of the locks are on the same table. If the same table was locked by two threads at the same time, that would explain the crash (wouldn't it?), but how is that possible? I thought the express purpose of semaphores was to prevent that. I'm hoping someone else can shed some light on this :) Thanks! Best Regards, Devananda vdv -- 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]
