Re: 2 cursors in a store procedure

[Rilawich Ango]

Thanks for your reply.  Actually, I want to do the following.  But I
have no idea how to do it.

create procedure a()
begin
   declare ...
   declare c1 cursor for select ...
   declare contine handler for not found set x=1;
   declare c2 cursor for select ...
   declare contine handler for not found set y=1;
  create temporary table 
  insert into temporary table 

open c1
  (c1 will join table a and temp table to get the result)
close c1

open c2
  (c2 will join table b and temp table to get the result)
close c2

end


On Feb 11, 2008 11:08 AM, Paul DuBois <[EMAIL PROTECTED]> wrote:
>
> At 10:57 AM +0800 2/11/08, Rilawich Ango wrote:
> >HI all,
> >
> >   How can a store procedure to contain 2 cursors?  Any advise?
> >
> >ango
> >
> >create procedure a()
> >begin
> >   declare ...
> >   declare c1 cursor for select ...
> >   declare contine handler for not found set x=1;
> >   declare c2 cursor for select ...
> >   declare contine handler for not found set y=1;
> >
>
> http://dev.mysql.com/doc/refman/5.0/en/cursors.html:
>
> "Cursors must be declared before declaring handlers. Variables and
> conditions must be declared before declaring either cursors or
> handlers."
>
> You have a handler declared before a cursor.
>
> --
> Paul DuBois, MySQL Documentation Team
> Madison, Wisconsin, USA
> 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]

Re: 2 cursors in a store procedure

[Paul DuBois]

At 10:57 AM +0800 2/11/08, Rilawich Ango wrote:

HI all,

  How can a store procedure to contain 2 cursors?  Any advise?

ango

create procedure a()
begin
  declare ...
  declare c1 cursor for select ...
  declare contine handler for not found set x=1;
  declare c2 cursor for select ...
  declare contine handler for not found set y=1;



http://dev.mysql.com/doc/refman/5.0/en/cursors.html:

"Cursors must be declared before declaring handlers. Variables and 
conditions must be declared before declaring either cursors or 
handlers."


You have a handler declared before a cursor.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
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]

2 cursors in a store procedure

[Rilawich Ango]

HI all,

  How can a store procedure to contain 2 cursors?  Any advise?

ango

create procedure a()
begin
  declare ...
  declare c1 cursor for select ...
  declare contine handler for not found set x=1;
  declare c2 cursor for select ...
  declare contine handler for not found set y=1;


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: If I downgrade MySQL from ver-5 to ver-4.

[Shawn Green]

Brown, Charles wrote:
Hello. Can someone tell me if I will run into any problems? 
1. If I downgrade MySQL from ver-5 to ver-4. 
2. If I run replication between version 4 and version 5 of the software.

Meaning slave is version 4 and the master is version 5.

Some back ground:
We have replication implemented. Currently, both are running version
5.0.29. My management wants one of these servers to run version 4.
(Don't ask me why). Will there be any repercussions. Please help me. I
need to provide an answer before 8am Monday. Thanks



My management suggests we down grade to RHEl 4 of mySQl 



You can downgrade your OS without downgrading your MySQL server. The two 
are not locked together.


Yes, you could experience many problems. There have been many many bugs 
fixed in 5.0.29 that will still exist in your version 4 downgrade. To 
identify the differences between the two versions, please refer to the 
change logs in the online manual.  More than one of those fixes changed 
something about how replication failed to handle one kind of problem or 
another. This means that there will be elements in the 5.0.x replication 
stream that your 4.x server will be unable to recognize or handle properly.


For the most recent changes to the Community server, please read:
http://dev.mysql.com/doc/refman/5.0/en/releasenotes-cs-5-0-51a.html

--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /
  / /|_/ / // /\ \/ /_/ / /__
 /_/  /_/\_, /___/\___\_\___/
<___/
 Join the Quality Contribution Program Today!
 http://dev.mysql.com/qualitycontribution.html


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Large Database Performance - Reference Sites?

[Jay Pipes]

You will likely need to be a lot more specific about what you are asking 
for here, David.  What is a "large select"?  What constitutes a "large 
update"?  What number of joined tables composes a "multi join" in your 
specific case?  What is "text functionality"?


-jay

David Stoller wrote:

Can Someone with Large Databases (100million records 20K-row avg  )X5
 
contact me for some questions, regarding performance on:

1. Text functionality
2. Performance
  large selects
  multi joins
  large updates
  bulk inserts
 
 
Best Regards,
[EMAIL PROTECTED]  
R & D DBA

Malha Technology Park
Jerusalem 91481, Israel
972-2-6499241
 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Server Crash

[samk]

See Thread at: http://www.techienuggets.com/Detail?tx=24273 Posted on behalf of 
a User

Help! I'm still facing this problem. I can run a check error successfully but 
when I try to dump the database using MySQL Administrator halfway through one 
table the server crashes. See message at the end. I restart the server run a 
check error and everything checks out but it crashes again when I try and do a 
backup. I'm using Innodb and my problems appear to have started when I upgraded 
from 5.0.45 to 5.0.51a. I'm not the normal Mysql admin, who's currently not 
available. Please any help would be great.

InnoDB: stored checksum 4292411360, prior-to-4.0.14-form stored checksum 
544367987
InnoDB: Page lsn 6553600 4293656593, low 4 bytes of lsn at page end 1869967971
InnoDB: Page number (if stored to page already) 1067590,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 19200
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 13375.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also InnoDB: 
http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.

In Response To: 

Hi I'm running 5.0.51a on Windows machine. The server crashes with:

080210 15:56:38  InnoDB: Page checksum 2545965801, prior-to-4.0.14-form 
checksum 1667729874
InnoDB: stored checksum 1195984440, prior-to-4.0.14-form stored checksum 
1416128883
InnoDB: Page lsn 4294918911 4294967295, low 4 bytes of lsn at page end 541878627
InnoDB: Page number (if stored to page already) 962664704,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 4294967295
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 13178.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also InnoDB: 
http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.

When I do a error check. I have full backups but when I restore the backup to 
the original schema name I get the same problem but when I restore to a new 
schema name the server works fine. I have change my programs to use the new 
schema name but I don't understand what's causing this problem? I can even 
delete the original schema name and restore the backup but it still fails.

Thanks

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Inefficient query processing?

[Peter Brawley]

Yves,

> My problem is that the sub-select in line 7
> ("SELECT 1") takes a rather long time

It might be possible to simplify. Do I have the schema right?

message (messageID)
keylist (keylistID)
tag ( tagID, readaccesskeylist references keylist(keylistID) )
message_revision_tag ( ???, messageID references message(messageID), 
tagID references tag(tagID))


Or is there a message_revisions table missing from your description? On 
what I have, if the condition tag.readaccesskeylist IS NOT NULL defines 
a 'banned' condition, perhaps the query can be written without 
referencing the keylist table, since the requirement simply translates 
to finding all messages with no tag having a non-null readaccesskeylist, 
i.e. ...

(i} find the messages which have a non-null tag.readaccesskeylist,
(ii) find the messages which are not in [i].

(i) Finding messages which have a deny-access tag looks like a simple join:

SELECT DISTINCT messageID
FROM message_revision_tag AS mrt
JOIN tag AS t ON mrt.tagID=t.tagID
WHERE t.readaccesskeylist IS NOT NULL;

(ii) We get the messages not in the above result with a simple exclusion 
join:


SELECT messageID
FROM message m
LEFT JOIN (
 SELECT DISTINCT messageID
 FROM message_revision_tag AS mrt
 JOIN tag AS t ON mrt.tagID=t.tagID
 WHERE t.readaccesskeylist IS NOT NULL
) AS banned USING (messageID)
WHERE banned.messageID IS NULL,

Or did I miss something?

PB
http://www.artfulsoftware.com

-

Yves Goergen wrote:

Hi,

I have a performance problem with one of my SQL queries. It's a rather 
complex one so I'll spare you the details. This is the situation:


In my system, there's messages, tags and keylists. Each message has 
message_revisions, each message_revision can be assigned tags (stored 
in message_revision_tag). Each tag points to a keylist that contains 
all keys that grant access to messages with that tag. If a tag has no 
keylist assigned (ReadAccessKeylist IS NULL), then everybody may 
access the messages.


This query finds all messages that don't have a tag assigned that 
would deny access to it. (Assume every message has only a single 
revision with the number 1, for now. The actual user comparison with 
another sub-select is hidden in .)


SELECT m."MessageId"
FROM "message" m
WHERE
  NOT EXISTS
(SELECT
  EXISTS
(SELECT 1
FROM "keylist" tk
WHERE tk."KeylistId" = t."ReadAccessKeylist" AND )
  AS "Allowed"
FROM "message_revision_tag" mrt
  JOIN "tag" t USING ("TagId")
WHERE mrt."MessageId" = m."MessageId" AND
  mrt."RevisionNumber" = 1 AND
  t."ReadAccessKeylist" IS NOT NULL
HAVING NOT "Allowed")

My problem is that the sub-select in line 7 ("SELECT 1") takes a 
rather long time. (When I remove it, it's much faster.) I'm not sure 
why, because there's not a single keylist in that table, however. 
Another issue is that this query should actually never be regarded. 
The condition in the second-last line is always false. A simple test 
confirms that:


SELECT COUNT(*) FROM "tag" WHERE "ReadAccessKeylist" IS NOT NULL
-> 0

So there should never be a reason why the FROM in line 11 would result 
in a row (filtering out with the conditions, of course). But it still 
gets executed. When I make sure that the condition is always false, by 
adding a "AND 0" just before the HAVING clause, the whole thing runs 
much faster.


I have no separate timings, but it's in the magnitude of 1 vs. 5-10 
milliseconds for the query. Run a hundred times makes a noticeable delay.


My understanding of it all was that first the FROM clause is regarded 
to see what rows there are. Then WHERE filters them, then SELECT will 
pick some columns (and thereby execute my sub-select expression) and 
finally HAVING filters again. Since in my theory there is no single 
row, SELECT has nothing to do. But obviously it has.


Some suggestion what's going on?



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Server Crash

[samk]

See Thread at: http://www.techienuggets.com/Detail?tx=24273 Posted on behalf of 
a User

Hi I'm running 5.0.51a on Windows machine. The server crashes with:

080210 15:56:38  InnoDB: Page checksum 2545965801, prior-to-4.0.14-form 
checksum 1667729874
InnoDB: stored checksum 1195984440, prior-to-4.0.14-form stored checksum 
1416128883
InnoDB: Page lsn 4294918911 4294967295, low 4 bytes of lsn at page end 541878627
InnoDB: Page number (if stored to page already) 962664704,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 4294967295
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 13178.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also InnoDB: 
http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.

When I do a error check. I have full backups but when I restore the backup to 
the original schema name I get the same problem but when I restore to a new 
schema name the server works fine. I have change my programs to use the new 
schema name but I don't understand what's causing this problem? I can even 
delete the original schema name and restore the backup but it still fails.

Thanks



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Inefficient query processing?

[Yves Goergen]

Hi,

I have a performance problem with one of my SQL queries. It's a rather 
complex one so I'll spare you the details. This is the situation:


In my system, there's messages, tags and keylists. Each message has 
message_revisions, each message_revision can be assigned tags (stored in 
message_revision_tag). Each tag points to a keylist that contains all 
keys that grant access to messages with that tag. If a tag has no 
keylist assigned (ReadAccessKeylist IS NULL), then everybody may access 
the messages.


This query finds all messages that don't have a tag assigned that would 
deny access to it. (Assume every message has only a single revision with 
the number 1, for now. The actual user comparison with another 
sub-select is hidden in .)


SELECT m."MessageId"
FROM "message" m
WHERE
  NOT EXISTS
(SELECT
  EXISTS
(SELECT 1
FROM "keylist" tk
WHERE tk."KeylistId" = t."ReadAccessKeylist" AND )
  AS "Allowed"
FROM "message_revision_tag" mrt
  JOIN "tag" t USING ("TagId")
WHERE mrt."MessageId" = m."MessageId" AND
  mrt."RevisionNumber" = 1 AND
  t."ReadAccessKeylist" IS NOT NULL
HAVING NOT "Allowed")

My problem is that the sub-select in line 7 ("SELECT 1") takes a rather 
long time. (When I remove it, it's much faster.) I'm not sure why, 
because there's not a single keylist in that table, however. Another 
issue is that this query should actually never be regarded. The 
condition in the second-last line is always false. A simple test 
confirms that:


SELECT COUNT(*) FROM "tag" WHERE "ReadAccessKeylist" IS NOT NULL
-> 0

So there should never be a reason why the FROM in line 11 would result 
in a row (filtering out with the conditions, of course). But it still 
gets executed. When I make sure that the condition is always false, by 
adding a "AND 0" just before the HAVING clause, the whole thing runs 
much faster.


I have no separate timings, but it's in the magnitude of 1 vs. 5-10 
milliseconds for the query. Run a hundred times makes a noticeable delay.


My understanding of it all was that first the FROM clause is regarded to 
see what rows there are. Then WHERE filters them, then SELECT will pick 
some columns (and thereby execute my sub-select expression) and finally 
HAVING filters again. Since in my theory there is no single row, SELECT 
has nothing to do. But obviously it has.


Some suggestion what's going on?

--
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MYSQL db no-no: deleted root permissions (smile)

[Dave Sampson]

Hey folks,

As with many lessons in linux world it takes a big oop for a big lesson.

system:
gentoo OS
webmin/virtualmin frontend


I was trying to apply permissions to usetrs so they can only access
certain DB's. This involved removing the associated user permissions
from the permissions list and creating seprate DB permissions.  This
worked great untill I messed with root and my non-root universal access
users.

I tried using other higher level permissions users but was always
blocked from the mysql DB. I guess this is where permissions info is
stored?

I "unmerged" mysql and re-"merged" mysql (package uninstall/install on
gentoo), then it asked my to initiate the new install.  It screamed at
the existance of a prevous mysql DB. So I thought it would be great to
keep all DB's and restart the MYSQL DB. so i deleted and re-initiated
mysql DB

still no glorry.

So how can I get  root user permission back into MYSQL?

Cheers


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Large Database Performance - Reference Sites?

[David Stoller]

Can Someone with Large Databases (100million records 20K-row avg  )X5
 
contact me for some questions, regarding performance on:
1. Text functionality
2. Performance
  large selects
  multi joins
  large updates
  bulk inserts
 
 
Best Regards,
[EMAIL PROTECTED]  
R & D DBA
Malha Technology Park
Jerusalem 91481, Israel
972-2-6499241
 

Server Getting crashed, Any Help Comments?

[VeeJay]

Hello

I am running a Freebsd server:

1. Software:
Apache 1.3
mysql 5.0.27
php 4.4


2. Hardware:
2 intel procerssors
4 gb ram
RAID 10 with hard drives 15K rpm

I am having this problem quite often now. Apache stops responding due to
mysql (my guess). And I cannot connect to mysql server:

/usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Out of memory; check if mysqld or some other process uses all
available memory; if not, you may have to use 'ulimit' to allow mysqld to
use more memory or you can add more swap space'


There is following configuration for mysql:

# The following options will be passed to all MySQL clients
[client]
port= 3306
socket  = /tmp/mysql.sock
default-character-set=utf8

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port= 3306
socket  = /tmp/mysql.sock
skip-locking
skip-networking
skip-name-resolve

server-id=1

max_connections=1000
key_buffer = 1024M
max_allowed_packet = 16M
table_cache = 512K
sort_buffer_size = 4M
net_buffer_length = 64K
read_buffer_size = 4M
read_rnd_buffer_size = 4M
join_buffer_size=4M

myisam_sort_buffer_size = 128M

# increase until threads_created doesnt grow anymore
thread_cache=512

query_cache_type=1
query_cache_limit=2M
query_cache_size=64M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4


set-variable=local-infile=0
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_unicode_ci
ft_min_word_len=2
ft_max_word_len=15

log-bin=mysql-bin

server-id   = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
default-character-set=utf8

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

ANY COMMENTS WILL BE WELCOMED, thanks!!!


-- 
Thanks!

BR / vj