InnoDB table which would not unlock
Dear MySql, Using 5.0.41 I had a single innodb table which would not unlock. I wonder if this might be a bug, or an issue that is known to be fixed in later versions? Any DML like this example: UPDATE ws_queue SET stage = 'committed' WHERE stage = 'running' Would result in: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction However there were no visible locks: show open tables like 'ws_queue'; +-+--++-+ | Database| Table| In_use | Name_locked | +-+--++-+ | Web_Members | ws_queue | 0 | 0 | +-+--++-+ This seems to be an error, and was fixed by bouncing the server, something I do not like doing in the middle of a working day. At the time Innodb status showed the following. Can any person help me understand what this is telling me? ---TRANSACTION 0 3683516087, ACTIVE 6 sec, process no 18537, OS thread id 1149135168 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1216 MySQL thread id 2738280, query id 50900786 172.16.16.39 dba Updating UPDATE ws_queue SET stage = 'committed' WHERE stage = 'running' --- TRX HAS BEEN WAITING 40 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 5619722 n bits 240 index `PRIMARY` of table `Web_Members/ws_queue` trx id 0 3683516087 lock_mode X waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 20; compact format; info bits 0 0: len 4; hex 0002b3f8; asc ;; 1: len 6; hex db8363ad; asc c ;; 2: len 7; hex 1a87d72205; asc ;; 3: len 1; hex 80; asc ;; 4: len 1; hex 01; asc ;; 5: len 4; hex 475fbf76; asc G_ v;; 6: len 7; hex 63685f61637473; asc ch_acts;; 7: len 8; hex 3236434539424134; asc 26CE9BA4;; 8: len 8; hex 00107523; asc u#;; 9: len 4; hex 8000; asc ;; 10: len 8; hex 4a414d455349524c; asc JAMESIRL;; 11: len 4; hex 800086bc; asc ;; 12: len 0; hex ; asc ;; 13: len 0; hex ; asc ;; 14: SQL NULL; 15: len 4; hex 8000; asc ;; 16: len 4; hex 8001; asc ;; 17: len 1; hex 80; asc ;; 18: len 4; hex 64626d73; asc dbms;; 19: len 1; hex 00; asc ;; Regards, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Eliminating duplicates from self join results
Hello: I have the following table select * from addressbook +++ | id | email | +++ | 1 | [EMAIL PROTECTED] | | 2 | [EMAIL PROTECTED] | | 3 | [EMAIL PROTECTED] | +++ 3 rows in set (0.00 sec) Now i wanted to find a list of duplicate contacts wherein i can get the 1st contact with same email and merge with the others. so in the above case id 1 has duplicates 2 and 3, 2 has 1 3 and 3 and 1 2. I'm only interested in getting the first set of duplicates i.e. 1 has duplicates 2 3. So i tried the query select t1.id as id1, t2.id as id2, t1.email as email1, t2.email as email2 from addressbook t1, addressbook t2 where t1.email = t2.email and t1.id != t2.id order by t1.id +-+-+++ | id1 | id2 | email1 | email2 | +-+-+++ | 1 | 2 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 1 | 3 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 2 | 1 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 2 | 3 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 3 | 1 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 3 | 2 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | +-+-+++ then i tried the query similar to the one suggested in the MySQL Cookbook Recipe 14.5 select DISTINCT if(t1.id t2.id, t1.id, t2.id) as id1, if(t1.id t2.id, t1.email, t2.email) as email1, if(t1.id t2.id, t2.id, t1.id) as id2, if(t1.id t2.id, t2.email, t1.email) as email2 from addressbook t1, addressbook t2 where t1.email = t2.email and t1.id != t2.id order by t1.id +-++-++ | id1 | email1 | id2 | email2 | +-++-++ | 1 | [EMAIL PROTECTED] | 2 | [EMAIL PROTECTED] | | 1 | [EMAIL PROTECTED] | 3 | [EMAIL PROTECTED] | | 2 | [EMAIL PROTECTED] | 3 | [EMAIL PROTECTED] | +-++-++ I'm stuck trying to get a query that will give me only +-++-++ | id1 | email1 | id2 | email2 | +-++-++ | 1 | [EMAIL PROTECTED] | 2 | [EMAIL PROTECTED] | | 1 | [EMAIL PROTECTED] | 3 | [EMAIL PROTECTED] | +-++-++ Any help, feeback is deeply appreciated. Thanks a bunch in advance. Yashesh Bhatia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Eliminating duplicates from self join results
Hi Yashesh, all ! Yashesh Bhatia wrote: Hello: I have the following table select * from addressbook +++ | id | email | +++ | 1 | [EMAIL PROTECTED] | | 2 | [EMAIL PROTECTED] | | 3 | [EMAIL PROTECTED] | +++ 3 rows in set (0.00 sec) Now i wanted to find a list of duplicate contacts wherein i can get the 1st contact with same email and merge with the others. so in the above case id 1 has duplicates 2 and 3, 2 has 1 3 and 3 and 1 2. I'm only interested in getting the first set of duplicates i.e. 1 has duplicates 2 3. [[...]] select DISTINCT if(t1.id t2.id, t1.id, t2.id) as id1, if(t1.id t2.id, t1.email, t2.email) as email1, if(t1.id t2.id, t2.id, t1.id) as id2, if(t1.id t2.id, t2.email, t1.email) as email2 from addressbook t1, addressbook t2 where t1.email = t2.email and t1.id != t2.id order by t1.id Using and t1.id t2.id would make your life a lot easier, allow you to drop the if and even the distinct (assuming your id values are unique). +-++-++ | id1 | email1 | id2 | email2 | +-++-++ | 1 | [EMAIL PROTECTED] | 2 | [EMAIL PROTECTED] | | 1 | [EMAIL PROTECTED] | 3 | [EMAIL PROTECTED] | | 2 | [EMAIL PROTECTED] | 3 | [EMAIL PROTECTED] | +-++-++ I'm stuck trying to get a query that will give me only +-++-++ | id1 | email1 | id2 | email2 | +-++-++ | 1 | [EMAIL PROTECTED] | 2 | [EMAIL PROTECTED] | | 1 | [EMAIL PROTECTED] | 3 | [EMAIL PROTECTED] | +-++-++ Any help, feeback is deeply appreciated. Thanks a bunch in advance. Tasks related to duplicate values often might be solved by using grouping and groupwise counting: ... GROUP BY ... HAVING COUNT(*) ... If you don't insist on getting it in this tabular form, you might use group_concat(). Try along these lines (untested): SELECT MIN(id), email, GROUP_CONCAT(id) FROM addressbook GROUP BY email HAVING COUNT(*) 1 This would repeat the minimum id (1 in your example data) in the concatenated list. For tabular form, try along these lines (untested): SELECT MIN(id), email, id FROM addressbook WHERE id MIN(id) GROUP BY email HAVING COUNT(*) 1 I wish you success, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Server Version Reference
As some of you might have noticed we've restructured the documentation overview page quite a bit. Hopefully this will make it easier for you to find the information you need. We've amended http://dev.mysql.com/doc/index.html#refman with a section labeled Excerpts from the Reference Manual, where we'll add, well, excerpts from the MySQL Manual. To begin with, we've added a document called MySQL Server Version Reference that should make life easier for everyone who needs cross-version information. It's available for download but you can also browse it online: http://dev.mysql.com/doc/mysqld-version-reference/en/index.html Here's an overview: 1. The mysqld Options/Variables Reference contains all MySQL server options and variables for all MySQL versions. You can easily find out whether or not a particular option is available in a specific MySQL version, when it was introduced or deprecated, and more. For variables, that chapter gives a quick overview of core properties, such as if it's a status or a server system variable, if I can be changed dynamically, or if the scope is global or local. 2. The Reserved Words list contains all words reserved in a particular MySQL (major) version, with annotations about minor versions. The list is created by running a script against all MySQL versions starting from 4.1.0. (Yes, we've installed all MySQL versions on our documentation machine!) 3. The Functions and Operators chapter shows which MySQL functions are available in which version, and when they were introduced or deprecated. 4. The Build (configure) Options chapter should be useful for anyone building MySQL from source. 5. Eventually, the Key changes in MySQL releases chapter list security fixes and incompatible changes for the GA versions of MySQL 4.1 and 5.0 and for the beta and RC versions of MySQL 5.1. The Server Version Reference is not a static document but is being recreated on a regular basis. And it's not the only excerpt from the MySQL Manual you can find in the Excerpts section: We've also added a standalone Connectors book (covering all MySQL connectors and APIs) and guides for each individual MySQL Connector. Kudos go to Martin C. Brown who created all this. We hope it will make your work with MySQL more productive and enjoyable! -- Regards, Stefan Hinz [EMAIL PROTECTED], MySQL AB Documentation Manager Berlin, Germany (UTC +1:00/winter, +2:00/summer) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Assistance with query and joins
Hi all, I have a query that is not quite producing what I expected: select n.nid, n.title, DATE_FORMAT(FROM_UNIXTIME(n.created), '%c/%e/%Y') as created, c.field_product_price_value as price, d.name, t.tid, v.value AS vote_average from node n left join node_revisions r on r.vid = n.vid left join content_type_galleria_product c on c.nid = n.nid left join term_node t on t.nid = n.nid left join term_data d on d.tid = t.tid right join votingapi_cache v on v.content_id = n.nid WHERE n.type = 'galleria_product' AND v.function = 'average' AND t.tid = 22; This query produces one result: nid 1391 title The Omnivore's Dilemma created 12/12/2007 price 21.99 nameBooks, DVDs tid 22 vote_average80 However, I was expecting two rows to be returned. The issue is that the second record does not have an entry in the votingapi_cache table. I used a right join on that table, expecting to get the record back with a NULL in the vote_average column, but that didn't happen. How can I get both records to be found? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Move data from one db to another?
I have similar (not identical) tables in two different db's. I want to merge all the data from one into the other, and will be dropping the original. There will be some duplication of keys so some rows will need to be ignored. Problem is these are on two different servers on different machines. How's the easiest way to handle this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Move data from one db to another?
OK, I have all my data on the new machine in a SQL file with 664,000 insert statements. But when I try to run it like this from the mysql command line: source filename.sql; It starts to work, but after a few seconds the server freezes up with too many connections. How do I avoid this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to count # of occurrences of a char in a string?
I have a Char(50) column and I want to count the number of . in the column using a Select statement. I don't see any MySQL function that can count the number of occurrences of a character in a string. Is there a simple way to do this? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to count # of occurrences of a char in a string?
Hi, On Dec 12, 2007 5:39 PM, mos [EMAIL PROTECTED] wrote: I have a Char(50) column and I want to count the number of . in the column using a Select statement. I don't see any MySQL function that can count the number of occurrences of a character in a string. Is there a simple way to do this? LENGTH(str) - LENGTH(REPLACE(str, '.', '')) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB table which would not unlock
Hi Ben, On Dec 12, 2007 8:14 AM, Ben Clewett [EMAIL PROTECTED] wrote: Dear MySql, Using 5.0.41 I had a single innodb table which would not unlock. I wonder if this might be a bug, or an issue that is known to be fixed in later versions? Any DML like this example: UPDATE ws_queue SET stage = 'committed' WHERE stage = 'running' Would result in: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction However there were no visible locks: show open tables like 'ws_queue'; +-+--++-+ | Database| Table| In_use | Name_locked | +-+--++-+ | Web_Members | ws_queue | 0 | 0 | +-+--++-+ This seems to be an error, and was fixed by bouncing the server, something I do not like doing in the middle of a working day. At the time Innodb status showed the following. Can any person help me understand what this is telling me? ---TRANSACTION 0 3683516087, ACTIVE 6 sec, process no 18537, OS thread id 1149135168 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1216 MySQL thread id 2738280, query id 50900786 172.16.16.39 dba Updating UPDATE ws_queue SET stage = 'committed' WHERE stage = 'running' --- TRX HAS BEEN WAITING 40 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 5619722 n bits 240 index `PRIMARY` of table `Web_Members/ws_queue` trx id 0 3683516087 lock_mode X waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 20; compact format; info bits 0 0: len 4; hex 0002b3f8; asc ;; 1: len 6; hex db8363ad; asc c ;; 2: len 7; hex 1a87d72205; asc ;; 3: len 1; hex 80; asc ;; 4: len 1; hex 01; asc ;; 5: len 4; hex 475fbf76; asc G_ v;; 6: len 7; hex 63685f61637473; asc ch_acts;; 7: len 8; hex 3236434539424134; asc 26CE9BA4;; 8: len 8; hex 00107523; asc u#;; 9: len 4; hex 8000; asc ;; 10: len 8; hex 4a414d455349524c; asc JAMESIRL;; 11: len 4; hex 800086bc; asc ;; 12: len 0; hex ; asc ;; 13: len 0; hex ; asc ;; 14: SQL NULL; 15: len 4; hex 8000; asc ;; 16: len 4; hex 8001; asc ;; 17: len 1; hex 80; asc ;; 18: len 4; hex 64626d73; asc dbms;; 19: len 1; hex 00; asc ;; The SHOW OPEN TABLES statement isn't going to show you anything relevant here, because the lock is on the InnoDB level, not the MySQL server level. The server is unaware of storage-engine locks. Another transaction had the record locked. The transaction needed to commit or rollback to release the locks. You can see locks held (as opposed to locks waited for) by using the InnoDB lock monitor, or via a patch I created. http://www.xaprb.com/blog/2007/09/18/how-to-debug-innodb-lock-waits/ http://bugs.mysql.com/bug.php?id=29126 You may also find innotop (http://innotop.sourceforge.net/) helpful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to count # of occurrences of a char in a string?
At 04:52 PM 12/12/2007, Baron Schwartz wrote: Hi, On Dec 12, 2007 5:39 PM, mos [EMAIL PROTECTED] wrote: I have a Char(50) column and I want to count the number of . in the column using a Select statement. I don't see any MySQL function that can count the number of occurrences of a character in a string. Is there a simple way to do this? LENGTH(str) - LENGTH(REPLACE(str, '.', '')) Baron, Thanks, I gave it a try and it works. :) But why can't MySQL create a function to do that otherwise the SQL harder is much harder to read. Just my 2 cents worth. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ODBC 3.51.22 problem - please help
I've found a glaring problem with the latest ODBC connector. Data types have been changed and data is no longer being read correctly. I'm running MySQL 5.1.16 on Netware. My apps are VB6 and VBA using ADO. The following query produces different data types depending on the version of the ODBC driver. SELECT ConCat(21000,'-','a') In 3.51.19 it is a VarChar and in 3.51.22 it's a VarBinary. Concat is supposed to return a string. In C a byte array may be fine but in VB a string should be a VarChar. Is this a bug or is there a server or OBDC setting that can be changed to make sure that it always returns a VarChar Thanks for the help
Re: Move data from one db to another?
Hi Brian tough to say without looking at the script http://www.php.net/function.mysql-connect suggests allowing the current thread to sleep a bit to wait for the resource to become available M-- - Original Message - Wrom: CLBDXRQBGJSNBOHMKHJYFMYXOEAIJJPHSCRTNHGS To: mysql@lists.mysql.com Sent: Wednesday, December 12, 2007 5:18 PM Subject: Re: Move data from one db to another? OK, I have all my data on the new machine in a SQL file with 664,000 insert statements. But when I try to run it like this from the mysql command line: source filename.sql; It starts to work, but after a few seconds the server freezes up with too many connections. How do I avoid this? -- 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: Eliminating duplicates from self join results
Taking it step by step, this query will give you all the lowest ids, for those records with duplicates. SELECT min(id), email, count(*) AS cnt FROM addressbook GROUP BY email HAVING cnt1 Now think of that query as an already existing table, which you can do, you just need to name the query result, in this case I'm calling it t1. Then you LEFT JOIN it with the addressbook table, but filtering out the ids you already have. SELECT id1, email1, t2.id AS id2, t2.email AS email2 FROM ( SELECT min(id) AS id1, email AS email1, count(*) AS cnt FROM addressbook GROUP BY email HAVING cnt1 ) AS t1 LEFT JOIN addressbook AS t2 ON t1.email1=t2.email AND t1.id1!=t2.id ORDER BY email1 I haven't tested it, but that query should work and give you the output you want. I don't recall if it works in v4.0, but v4.1 and above should work fine. Brent On Dec 12, 2007, at 8:35 AM, Yashesh Bhatia wrote: Hello: I have the following table select * from addressbook +++ | id | email | +++ | 1 | [EMAIL PROTECTED] | | 2 | [EMAIL PROTECTED] | | 3 | [EMAIL PROTECTED] | +++ 3 rows in set (0.00 sec) Now i wanted to find a list of duplicate contacts wherein i can get the 1st contact with same email and merge with the others. so in the above case id 1 has duplicates 2 and 3, 2 has 1 3 and 3 and 1 2. I'm only interested in getting the first set of duplicates i.e. 1 has duplicates 2 3. So i tried the query select t1.id as id1, t2.id as id2, t1.email as email1, t2.email as email2 from addressbook t1, addressbook t2 where t1.email = t2.email and t1.id != t2.id order by t1.id +-+-+++ | id1 | id2 | email1 | email2 | +-+-+++ | 1 | 2 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 1 | 3 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 2 | 1 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 2 | 3 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 3 | 1 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 3 | 2 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | +-+-+++ then i tried the query similar to the one suggested in the MySQL Cookbook Recipe 14.5 select DISTINCT if(t1.id t2.id, t1.id, t2.id) as id1, if(t1.id t2.id, t1.email, t2.email) as email1, if(t1.id t2.id, t2.id, t1.id) as id2, if(t1.id t2.id, t2.email, t1.email) as email2 from addressbook t1, addressbook t2 where t1.email = t2.email and t1.id != t2.id order by t1.id +-++-++ | id1 | email1 | id2 | email2 | +-++-++ | 1 | [EMAIL PROTECTED] | 2 | [EMAIL PROTECTED] | | 1 | [EMAIL PROTECTED] | 3 | [EMAIL PROTECTED] | | 2 | [EMAIL PROTECTED] | 3 | [EMAIL PROTECTED] | +-++-++ I'm stuck trying to get a query that will give me only +-++-++ | id1 | email1 | id2 | email2 | +-++-++ | 1 | [EMAIL PROTECTED] | 2 | [EMAIL PROTECTED] | | 1 | [EMAIL PROTECTED] | 3 | [EMAIL PROTECTED] | +-++-++ Any help, feeback is deeply appreciated. Thanks a bunch in advance. Yashesh Bhatia -- 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]
Re: Problem with a Procedure
Hi Tomas
I'll need more info so I could follow up
Do all the Tables exist in the currently selected DB?
Do all the tables have data?
Does the current user you are using to run the Procedure have the DML
permission to create/query/insert/update or execute?
M--
- Original Message -
From: Tomas Hylander [EMAIL PROTECTED]
To: Martin Gainty [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Monday, November 26, 2007 1:38 AM
Subject: Re: Problem with a Procedure
Hi!
I must say I cant see how this would help me. I know the tabels isnt
empty since when running in query browsern I get a result.
There must be something else thats wrong..
...but thanks anyway!
/Hylsan
On Nov 23, 2007 4:43 PM, Martin Gainty [EMAIL PROTECTED] wrote:
Tomas-
I would effect a quick iterative check on the table(s) to see if they
are
empty e.g.
SELECT count(trans2.nettovikt) from trans2;
(If recordcount0) then
SELECT SUM(trans2.nettovikt)
FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel
(If recordcount0) then
SELECT SUM(trans2.nettovikt) INTO ut_summa
FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel
WHERE trans2.transtid between 'datum1' and 'datum2'
(If recordcount 0) then
SELECT SUM(trans2.nettovikt) INTO ut_summa
FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel
WHERE trans2.transtid between 'datum1' and 'datum2'
and artikel.reservinteger='skatt'
(If recordcount 0) then
SELECT SUM(trans2.nettovikt) INTO ut_summa
FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel
WHERE trans2.transtid between 'datum1' and 'datum2'
and artikel.reservinteger='skatt'
group by artikel.volympris;
Does this help???
Martin Gainty
__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official
business of Sender. This transmission is of a confidential nature and
Sender
does not endorse distribution to any party other than intended
recipient.
Sender does not necessarily endorse content contained within this
transmission.
Date: Fri, 23 Nov 2007 11:10:47 +0100
From: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Problem with a Procedure
Hi!
Hope you can help me with this one.
Im trying to learn this with stored procedures and optimize my
databases.
Can someone point what wrong with this?
--
DELIMITER $$
DROP PROCEDURE IF EXISTS `vagsql`.`sok` $$
CREATE [EMAIL PROTECTED] PROCEDURE `sok`(skatt int,
datum1 DATE,
datum2 DATE,
OUT ut_summa decimal(8,2))
BEGIN
SELECT SUM(trans2.nettovikt) INTO ut_summa
FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel
WHERE trans2.transtid between 'datum1' and 'datum2'
and artikel.reservinteger='skatt'
group by artikel.volympris;
END $$
DELIMITER ;
--
--
call sok('01','2007-01-01 00:00:00', '2007-01-10 23:59:59', @out);
select @out;
All I get is that No data - zero rows fetched, selected or processed
When running this in query brower everything looks ok.
SELECT SUM(trans2.nettovikt) as summa FROM trans2 INNER JOIN artikel
on
trans2.artikel=artikel.artikel WHERE trans2.transtid between
'2007-07-01
00:00:00' and '2007-07-02 23:59:59' and artikel.reservinteger='01'
group
by
artikel.volympris;
Im running mysql 5.1.11.
Thanks in advance!
/Tomas
Share life as it happens with the new Windows Live. Share now!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld startup failure
- Original Message - From: Chris [EMAIL PROTECTED] To: jekillen [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, December 11, 2007 9:12 PM Subject: Re: mysqld startup failure jekillen wrote: Hello: The saga of this machine continues: FreeBSD v6.2 latest mysql version installed from ports: mysql51 client, serve, and scripts. There is no /usr/local/mysql it did not create this dir. No idea where it should put the files, a freebsd list might be able to point you in the right direction. Try /usr/local/var/mysql snip /usr/local/libexec/mysqld: Can't find file: './mysql/general_log.frm' (errno: 13) 071121 15:04:38 [ERROR] /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 071121 15:04:38 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 071121 15:04:38 mysqld ended where is './mysql/ (etc)? the ./ tells me it is relative to /usr/local/libexec I have followed the published instruction steps to configure build and install from source dist and it specifies that data dir should be change to owner mysql but where is the data dir. It is not in /var, unless it is supposed to be in /var/db (where I found the hostname error file). Errno: 13 means access denied as I understand it. So what dir and files do I change privileges for, if that is in fact, the problem? Yep 13 means permission problems (run 'perror 13' at your prompt). The data dir should be owned by the mysql user group. mysqld --print-defaults should tell you everything you need. -- 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: mysqld startup failure
On Dec 12, 2007, at 6:41 PM, Ken Menzel wrote: - Original Message - From: Chris [EMAIL PROTECTED] To: jekillen [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, December 11, 2007 9:12 PM Subject: Re: mysqld startup failure jekillen wrote: Hello: The saga of this machine continues: FreeBSD v6.2 latest mysql version installed from ports: mysql51 client, serve, and scripts. There is no /usr/local/mysql it did not create this dir. No idea where it should put the files, a freebsd list might be able to point you in the right direction. Try /usr/local/var/mysql snip /usr/local/libexec/mysqld: Can't find file: './mysql/general_log.frm' (errno: 13) 071121 15:04:38 [ERROR] /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 071121 15:04:38 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 071121 15:04:38 mysqld ended where is './mysql/ (etc)? the ./ tells me it is relative to /usr/local/libexec I have followed the published instruction steps to configure build and install from source dist and it specifies that data dir should be change to owner mysql but where is the data dir. It is not in /var, unless it is supposed to be in /var/db (where I found the hostname error file). Errno: 13 means access denied as I understand it. So what dir and files do I change privileges for, if that is in fact, the problem? Yep 13 means permission problems (run 'perror 13' at your prompt). The data dir should be owned by the mysql user group. mysqld --print-defaults should tell you everything you need. I totally forgot about this message and even now, I am looking at it with surprise because it looks like a message I may have sent on the same problem on this machine a year or so ago. There has been a happy ending, though, finally, I dumped everything and started over with mysql from ports. It is up and running now. After four major attempts, even re-installing FreeBSD at on time. I have three other machines running FreeBSD and two installed from source, bypassing ports and one from ports with no problems. I do not know why I had so much trouble with this one. One question, though, is it possible to create a mysql user with no login access? I mean what is to stop someone from cracking the MySQL account the way any other account might be cracked? (This is just admission of my novice, or apprentice admin level). Editorial: why not have apprenticeships for Linux/Unix administers with all the software categories and then journeyman, and then master status. like trades, instead of paying many G's of $ for a college education just to start from the bottom in customer service or something? I have a decent, honest, if not straight A high school education and am doing self study for virtually no pay, though I have designed and manage web sites for a few customers including my own. I just turned 59 last October. I make $30,000/year in my regular trade. Thanks for the response Jeff K -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld startup failure
On Dec 12, 2007, at 8:03 PM, jekillen wrote: On Dec 12, 2007, at 6:41 PM, Ken Menzel wrote: - Original Message - From: Chris [EMAIL PROTECTED] To: jekillen [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, December 11, 2007 9:12 PM Subject: Re: mysqld startup failure jekillen wrote: Hello: The saga of this machine continues: FreeBSD v6.2 latest mysql version installed from ports: mysql51 client, serve, and scripts. There is no /usr/local/mysql it did not create this dir. No idea where it should put the files, a freebsd list might be able to point you in the right direction. Try /usr/local/var/mysql snip /usr/local/libexec/mysqld: Can't find file: './mysql/general_log.frm' (errno: 13) 071121 15:04:38 [ERROR] /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 071121 15:04:38 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 071121 15:04:38 mysqld ended where is './mysql/ (etc)? the ./ tells me it is relative to /usr/local/libexec I have followed the published instruction steps to configure build and install from source dist and it specifies that data dir should be change to owner mysql but where is the data dir. It is not in /var, unless it is supposed to be in /var/db (where I found the hostname error file). Errno: 13 means access denied as I understand it. So what dir and files do I change privileges for, if that is in fact, the problem? Yep 13 means permission problems (run 'perror 13' at your prompt). The data dir should be owned by the mysql user group. mysqld --print-defaults should tell you everything you need. I totally forgot about this message and even now, I am looking at it with surprise because it looks like a message I may have sent on the same problem on this machine a year or so ago. There has been a happy ending, though, finally, I dumped everything and started over with mysql from ports. It is up and running now. After four major attempts, even re-installing FreeBSD at on time. I have three other machines running FreeBSD and two installed from source, bypassing ports and one from ports with no problems. I do not know why I had so much trouble with this one. One question, though, is it possible to create a mysql user with no login access? I mean what is to stop someone from cracking the MySQL account the way any other account might be cracked? (This is just admission of my novice, or apprentice admin level). Editorial: why not have apprenticeships for Linux/Unix administers with all the software categories and then journeyman, and then master status. like trades, instead of paying many G's of $ for a college education just to start from the bottom in customer service or something? I have a decent, honest, if not straight A high school education and am doing self study for virtually no pay, though I have designed and manage web sites for a few customers including my own. I just turned 59 last October. I make $30,000/year in my regular trade. Thanks for the response Jeff K I remember now, I did not actually run make install. Dumb mistake, all is well now. Jeff K -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with a Procedure
Hi!
As I wrote before this works fine and I DO get a nice result;
SELECT SUM(trans2.nettovikt) as summa FROM trans2 INNER JOIN artikel on
trans2.artikel=artikel.artikel WHERE trans2.transtid between '2007-07-01
00:00:00' and '2007-07-02 23:59:59' and artikel.reservinteger='01' group by
artikel.volympris;
For a brief moment, it seemed to work but now I only get this no
data...-answer.
All tables exist in the selected DB and the are full with lovely data
I use the same user as in the query browser.
Thanks!
/Tomas
On Dec 13, 2007 3:29 AM, Martin Gainty [EMAIL PROTECTED] wrote:
Hi Tomas
I'll need more info so I could follow up
Do all the Tables exist in the currently selected DB?
Do all the tables have data?
Does the current user you are using to run the Procedure have the DML
permission to create/query/insert/update or execute?
M--
- Original Message -
From: Tomas Hylander [EMAIL PROTECTED]
To: Martin Gainty [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Monday, November 26, 2007 1:38 AM
Subject: Re: Problem with a Procedure
Hi!
I must say I cant see how this would help me. I know the tabels isnt
empty since when running in query browsern I get a result.
There must be something else thats wrong..
...but thanks anyway!
/Hylsan
On Nov 23, 2007 4:43 PM, Martin Gainty [EMAIL PROTECTED] wrote:
Tomas-
I would effect a quick iterative check on the table(s) to see if they
are
empty e.g.
SELECT count(trans2.nettovikt) from trans2;
(If recordcount0) then
SELECT SUM(trans2.nettovikt)
FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel
(If recordcount0) then
SELECT SUM(trans2.nettovikt) INTO ut_summa
FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel
WHERE trans2.transtid between 'datum1' and 'datum2'
(If recordcount 0) then
SELECT SUM(trans2.nettovikt) INTO ut_summa
FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel
WHERE trans2.transtid between 'datum1' and 'datum2'
and artikel.reservinteger='skatt'
(If recordcount 0) then
SELECT SUM(trans2.nettovikt) INTO ut_summa
FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel
WHERE trans2.transtid between 'datum1' and 'datum2'
and artikel.reservinteger='skatt'
group by artikel.volympris;
Does this help???
Martin Gainty
__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official
business of Sender. This transmission is of a confidential nature and
Sender
does not endorse distribution to any party other than intended
recipient.
Sender does not necessarily endorse content contained within this
transmission.
Date: Fri, 23 Nov 2007 11:10:47 +0100
From: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Problem with a Procedure
Hi!
Hope you can help me with this one.
Im trying to learn this with stored procedures and optimize my
databases.
Can someone point what wrong with this?
--
DELIMITER $$
DROP PROCEDURE IF EXISTS `vagsql`.`sok` $$
CREATE [EMAIL PROTECTED] PROCEDURE `sok`(skatt int,
datum1 DATE,
datum2 DATE,
OUT ut_summa decimal(8,2))
BEGIN
SELECT SUM(trans2.nettovikt) INTO ut_summa
FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel
WHERE trans2.transtid between 'datum1' and 'datum2'
and artikel.reservinteger='skatt'
group by artikel.volympris;
END $$
DELIMITER ;
--
--
call sok('01','2007-01-01 00:00:00', '2007-01-10 23:59:59', @out);
select @out;
All I get is that No data - zero rows fetched, selected or
processed
When running this in query brower everything looks ok.
SELECT SUM(trans2.nettovikt) as summa FROM trans2 INNER JOIN artikel
on
trans2.artikel=artikel.artikel WHERE trans2.transtid between
'2007-07-01
00:00:00' and '2007-07-02 23:59:59' and artikel.reservinteger='01'
group
by
artikel.volympris;
Im running mysql 5.1.11.
Thanks in advance!
/Tomas
Share life as it happens with the new Windows Live. Share now!
