RE: password guessing attacks against mysql
No, it is not true. After repeated failed connection attempts from a host that host will be blocked until a flush hosts command is executed. The number allowed before this blocking is specified by the variable max_connect_errors. See http://dev.mysql.com/doc/mysql/en/Blocked_host.html John From: Dave Dyer [mailto:[EMAIL PROTECTED] Sent: Sat 10/30/2004 2:01 PM To: [EMAIL PROTECTED] Subject: password guessing attacks against mysql While discussing the hazards of having an open mysql port, it occurred to me that I have never seen any mention of defenses against password guessing attacks, such as slow response to failed authentication, or shutting of a particular remote IP that seems to be issuing unsucessful requests. Is it true that there are none? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL functions
Hi, How do I add a new function to MySQL SQL statement list. What I mean is AVG(), MIN() or MAX() is a ready function isnt it? Is there a way to add new functions without compiling all server? Regards... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
password guessing attacks against mysql
While discussing the hazards of having an open mysql port, it occurred to me that I have never seen any mention of defenses against password guessing attacks, such as slow response to failed authentication, or shutting of a particular remote IP that seems to be issuing unsucessful requests. Is it true that there are none? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.22 has been released
Dan Nelson wrote: > In the last episode (Oct 30), Mark said: >>> MySQL 4.0.22, a new version of the popular Open Source/Free >>> Software Database Management System has been released. It is now >>> available in source and binary form for a number of platforms from >>> our download pages at http://dev.mysql.com/downloads/ and mirror >>> sites. >> >> Any reason there is no longer a 4.0.22 'standard' for FreeBSD? There >> is a 4.0.22 'max' for FreeBSD, but not standard. >> >> Also, speaking of FreeBSD, is it not time to start compiling against >> something higher than 4.7? Like 4.10? (or 4.9, perhaps). > > Could be they just haven't built it yet. Could be. But it still is not there. At any rate, I went with the 'max', I disabled InnoDB, and it seems fine too. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: synchronizing mysql database in two diferrent places
Hi. MySQL replication currently does not support any locking protocol between master and slave to guarantee the atomicity of a distributed (cross-server) update. See: http://dev.mysql.com/doc/mysql/en/Replication_FAQ.html >"Eko Budiharto" <[EMAIL PROTECTED]> wrote: > >Hi, >I a facing a situation where I have two database server in two different >places that has exactly the same database. The thing is if the database >is modified in either one of the places, both database has to be updated >lively. >Can someone tell me who done this already? Please help. >I am looking forward to a favorable reply from you. Thank you. -- 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: Help! Question on backup
Hi. Windows filesystem is not case sensitive. See: http://dev.mysql.com/doc/mysql/en/Name_case_sensitivity.html Stuart Felenstein <[EMAIL PROTECTED]> wrote: > I apologize for the missing information. > First the server was on 4.0.20 now on 4.0.22. Apache > 1.3.x not sure of the linux > The local machine is windows xp with mysql v. 4.0.20a > I did the data transfer running Navicat, so that may > not be so clear to anyone, but the only options that > were enabled: > > 1-Create tables > 2-Indclude all table create options > 3-Create records > > They were transfered directly to the server. > The tables are all fine ,outside of when they came to > the windows machine all lowercase on the table names. > I can rename them on the windows machine using upper > case. > > I guess I'm still trying to figure out how the > database got hosed anyway (still awaiting answer from > web host). > One possible guess is that most of my tables are > Innodb. Not sure why , but perhaps its related. > > Stuart > > > --- mysq > -- 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: Help! Question on backup
Hi. How did you backed up the data? Stuart Felenstein <[EMAIL PROTECTED]> wrote: > My database got hosed last night due to some version > upgrade. > Anyway an older copy was restored on the server. I > have a more recent backup on my local machine. > However, when I have done the data transfers I have > found that the table names were convereted to all > lower case. > I'm assuming when I put them back on the linux machine > that is going to be a problem since all my scripts > call to the name with the lower and upper letters . > Would that be correct ? > And why do you think it converts everything to > lowercase. > > Stuart > -- 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: call stored procedure with PHP5 and get SELECT results
Hi. I installed MySQL 5.0.1 (snapshot) and PHP 5.0.2 (compiled --with-mysql=../mysql-debug-5.0.1-alpha-pc-linux-i686 --with-mysqli=../mysql-debug-5.0.1-alpha-pc-linux-i686/bin/mysql_config). Your code really doesn't work when I used $mysqli=mysqli_connect('localhost', 'root','s','test',3807,"/tmp/mysql.sock.gleb.3") to connect to the server. But when I changed that to $mysqli=mysqli_init(); $mysqli->real_connect('localhost', 'root','s','test',3807,"/tmp/mysql.sock.gleb.3"); Everything works fine. It might be a PHP bug. I posted your message with my comments to [EMAIL PROTECTED] My code: real_connect('localhost', 'root','s','test',3807,"/tmp/mysql.sock.gleb.3"); //$mysqli=mysqli_connect('localhost', 'root','s','test',3807,"/tmp/mysql.sock.gleb.3"); $ok = $mysqli->multi_query("call test();"); echo $mysqli->info; //$ok = $mysqli->multi_query("select * from t1;"); //if($ok) { //echo "OK\n"; //echo $mysqli->mysqli_errno()."\n"; //echo $mysqli->mysqli_sqlstate."\n"; do { echo "result\n"; $result = $mysqli->store_result(); if($result) { while ($line = mysqli_fetch_array($result, MYSQL_ASSOC)) { echo "\t\n"; foreach ($line as $col_value) { echo "\t\t$col_value\n"; } echo "\t\n"; } $result->close(); } } while($mysqli->next_result()); //} ?> Michael Kofler <[EMAIL PROTECTED]> wrote: > Hi, > > I am using MySQL 5.0.1 (snapshot) and PHP 5.0.2 with mysqli interface > (latest snapshot) under Linux (SUSE 9.1). > > I would like to call a SP using PHP 5, and I want to get back the SELECT > results. > > This is my SP: > > CREATE PROCEDURE `test`() > BEGIN > SELECT * FROM t1; > END > > From the comments in http://bugs.mysql.com/bug.php?id=2273 I understand I > have to use mysqli_multi_query if I want to get rowsets from a SP. Fine: > > $ok = $mysqli->multi_query("CALL test()"); > if($ok) { > echo "OK\n"; > do { > echo "result\n"; > $result = $mysqli->store_result(); > if($result) { > show_table($result); // shows result details > $result->close(); > } > } while($mysqli->next_result()); > } > > I don't get any results, $ok is FALSE. > > If I instead use the following code, everything works fine, I receive both > rowsets. So, the PHP code above seems to be ok as long as I don't call a SP. > > $ok = $mysqli->multi_query("SELECT * FROM t1; SELECT * FROM t1"); > > Any ideas? Error in MySQL or in PHP? > > Thank you, > > Michael Kofler > > http://www.kofler.cc/ > -- 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: Converting table type
Hi. I think you should upgrade to MySQL 4.0.22. Michael Satterwhite <[EMAIL PROTECTED]> wrote: > [-- text/plain, encoding quoted-printable, charset: us-ascii, 23 lines --] > > I'm running MySQL 4.0.21 > > In order to use Foreign Keys, I'm trying to convert a table Type to INNODB. I > issue a statement of the form > > Alter Table TblName Type=INNODB; > > This statement appears to execute correctly. I would expect this to affect the > Show Create Table output, but when I say > > Show Create Table TblName; > > The output still indicates MyISAM. If I add a Foreign Key with On Delete > Cascade On Update Cascade, it does not show up in the Show Create Table, and > Updating the Foreign key does not cascade to the table with the Foreign Key > Constraint. > > What am I doing wrong? > > [-- application/pgp-signature, encoding 7bit, 8 lines --] > -- 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: Help! Question on backup
Hi. Also check the value of lower_case_table_names. Stuart Felenstein <[EMAIL PROTECTED]> wrote: > My database got hosed last night due to some version > upgrade. > Anyway an older copy was restored on the server. I > have a more recent backup on my local machine. > However, when I have done the data transfers I have > found that the table names were convereted to all > lower case. > I'm assuming when I put them back on the linux machine > that is going to be a problem since all my scripts > call to the name with the lower and upper letters . > Would that be correct ? > And why do you think it converts everything to > lowercase. > > Stuart > -- 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: Over enthusiatic installer of 4.1 makes mistake....
Stephen Moretti (cfmaster) wrote: Ok - feeling a bit embarrassed and sheepish really In my rush to try out 4.1.7 I did the typical techie thing - I didn't bother RTFM before starting installing, other than the bit that says you have to uninstall earlier versions mySQL. So I've got 4.1.7 all up and running on my dev machine - great - but some moron forgot to make backups of all the databases on his machine before uninstalling! I've got all these folders with my databases in, but none of them available via my shiney new installation. Is there anyway to get the databases into the server? Its no great shakes if I can't recover them, being my dev machine, but would be nice if I can get them into the server. Any suggestions? Stop mysql. Copy your old files over to your new data folder. Start mysql. HTH Friedhelm p.s.: you are on win32? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Over enthusiatic installer of 4.1 makes mistake....
Ok - feeling a bit embarrassed and sheepish really In my rush to try out 4.1.7 I did the typical techie thing - I didn't bother RTFM before starting installing, other than the bit that says you have to uninstall earlier versions mySQL. So I've got 4.1.7 all up and running on my dev machine - great - but some moron forgot to make backups of all the databases on his machine before uninstalling! I've got all these folders with my databases in, but none of them available via my shiney new installation. Is there anyway to get the databases into the server? Its no great shakes if I can't recover them, being my dev machine, but would be nice if I can get them into the server. Any suggestions? TIA and regards from a very foolish feeling Stephen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting table type
At 11:06 -0500 10/30/04, Michael Satterwhite wrote: On Saturday 30 October 2004 10:58 am, Paul DuBois wrote: What output does the following statement produce? SHOW VARIABLES LIKE 'have_innodb'; If YES, the ALTER TABLE statement should have worked. If NO, your server doesn't have InnoDB support built in. If DISABLED, your server supports InnoDB but it was disable at startup time with --skip-innodb. That was it. Thanks. I didn't think of Debian disabling the INNODB function. Rude of them. :-) -- 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: Converting table type
On Saturday 30 October 2004 10:58 am, Paul DuBois wrote: > > What output does the following statement produce? > > SHOW VARIABLES LIKE 'have_innodb'; > > If YES, the ALTER TABLE statement should have worked. > If NO, your server doesn't have InnoDB support built in. > If DISABLED, your server supports InnoDB but it was disable at > startup time with --skip-innodb. That was it. Thanks. I didn't think of Debian disabling the INNODB function. pgpR5VPcpF3tw.pgp Description: PGP signature
Re: Converting table type
At 9:24 -0500 10/30/04, Michael Satterwhite wrote: I'm running MySQL 4.0.21 In order to use Foreign Keys, I'm trying to convert a table Type to INNODB. I issue a statement of the form Alter Table TblName Type=INNODB; This statement appears to execute correctly. I would expect this to affect the Show Create Table output, but when I say Show Create Table TblName; The output still indicates MyISAM. If I add a Foreign Key with On Delete Cascade On Update Cascade, it does not show up in the Show Create Table, and Updating the Foreign key does not cascade to the table with the Foreign Key Constraint. What output does the following statement produce? SHOW VARIABLES LIKE 'have_innodb'; If YES, the ALTER TABLE statement should have worked. If NO, your server doesn't have InnoDB support built in. If DISABLED, your server supports InnoDB but it was disable at startup time with --skip-innodb. -- 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: Help! Question on backup
I apologize for the missing information. First the server was on 4.0.20 now on 4.0.22. Apache 1.3.x not sure of the linux The local machine is windows xp with mysql v. 4.0.20a I did the data transfer running Navicat, so that may not be so clear to anyone, but the only options that were enabled: 1-Create tables 2-Indclude all table create options 3-Create records They were transfered directly to the server. The tables are all fine ,outside of when they came to the windows machine all lowercase on the table names. I can rename them on the windows machine using upper case. I guess I'm still trying to figure out how the database got hosed anyway (still awaiting answer from web host). One possible guess is that most of my tables are Innodb. Not sure why , but perhaps its related. Stuart --- mysq -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help! Question on backup
My database got hosed last night due to some version upgrade. Anyway an older copy was restored on the server. I have a more recent backup on my local machine. However, when I have done the data transfers I have found that the table names were convereted to all lower case. I'm assuming when I put them back on the linux machine that is going to be a problem since all my scripts call to the name with the lower and upper letters . Would that be correct ? And why do you think it converts everything to lowercase. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting table type
Michael, All your e-mails come as an attachment in my mailbox instead of plain e-mail. >> Why not simply recreate it with the InnoDB table type? >> >> If there's data in it, rename the old table, recreate the >> table and pump the data. >> >> All done! > >Certainly that *COULD* be done, but it shouldn't be necessary (according to >the documentation). In addition, one of the tables needing this doesn't just >have data in it ... it has A LOT of data in it. It would not be a small, >short running job. Well, I'm not a MySQL pro at all, but if this InnoDB is running in its own table space, the data will have to be transferred into there anyway. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting table type
On Saturday 30 October 2004 09:35 am, Martijn Tonies wrote: > Why not simply recreate it with the InnoDB table type? > > If there's data in it, rename the old table, recreate the > table and pump the data. > > All done! Certainly that *COULD* be done, but it shouldn't be necessary (according to the documentation). In addition, one of the tables needing this doesn't just have data in it ... it has A LOT of data in it. It would not be a small, short running job. pgp6Tww3k1zKJ.pgp Description: PGP signature
Re: Converting table type
Why not simply recreate it with the InnoDB table type? If there's data in it, rename the old table, recreate the table and pump the data. All done! With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bug or feature, <> 'blah' does NOT work with null records
> > Fortunately there is function COALESCE() that will return the first argument > > that is not NULL. In case of NULL values you can use a default value for an > > expression: COALESCE( `col`*2, 14) will produce 14 if `col` is NULL. > > FWIW, IFNULL() does the same thing, with a clearer (to me) name, e.g., > IFNULL(sum(qty), 0). Do note, that COALESCE is the standard name for this :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting table type
I'm running MySQL 4.0.21 In order to use Foreign Keys, I'm trying to convert a table Type to INNODB. I issue a statement of the form Alter Table TblName Type=INNODB; This statement appears to execute correctly. I would expect this to affect the Show Create Table output, but when I say Show Create Table TblName; The output still indicates MyISAM. If I add a Foreign Key with On Delete Cascade On Update Cascade, it does not show up in the Show Create Table, and Updating the Foreign key does not cascade to the table with the Foreign Key Constraint. What am I doing wrong? pgpgDFbx8Rkft.pgp Description: PGP signature
Re: Newbie question on multiple tables
Hi. You may read: http://dev.mysql.com/doc/mysql/en/JOIN.html http://dev.mysql.com/doc/mysql/en/SELECT.html There is a good description. Searching in more than two tables (3 in my example) may be done in such way: select t1.* from t1,t2,t3 where t1.a=t2.a and t1.b=t3.b But using left or inner joins depends on results you are expecting. Steve Grosz <[EMAIL PROTECTED]> wrote: > I am just now getting the hang of searching through 2 tables, and > getting the corresponding information using a pri & foreign key > (ID1=ID1) kinda thing. How is this done with more than 2 tables? > > I had asked on a different forum, but I got some funky select statement > using a left inner join, and lots of other statements that didn't make a > lot of sense to me. > > Is that the way it would need to be done? A inner join? If so why? > > Thanks! > > Steve > -- 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: why aren't my PRIMARY KEYs being used?
On Oct 29, 2004, at 6:26 AM, [EMAIL PROTECTED] wrote: I think it may be because of your mixed left and right joins. There are several bugs listed that show that the optimizer mishandles certain combinations of left and right joins. SELECT activelayers.id,activelayers.lid,activelayers.rsrcc,COUNT(lrsrc.rsrc=nr src.rsrc) as matchcount,activenodes.name,activenodes.rsrcc FROM activelayers LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND lrsrc.lid=activelayers.lid INNER JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc INNER JOIN activenodes ON nrsrc.id=activenodes.id GROUP BY activelayers.id,activelayers.lid,activenodes.id HAVING matchcount=activelayers.rsrcc ORDER BY activelayers.lid DESC; This actually didn't produce the same result. I'm doing a RIGHT JOIN rather than a LEFT or INNER JOIN to catch node resources (nrsrc) which do not match layer resources (lrsrc), or nodes with no layer resources at all. This example makes the difference a little clearer: SELECT activenodes.name,lrsrc.lid,activelayers.rsrcc,lrsrc.rsrc,nrsrc.rsrc FROM activelayers LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND lrsrc.lid=activelayers.lid RIGHT JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc RIGHT JOIN activenodes ON nrsrc.id=activenodes.id ORDER BY activelayers.lid DESC; +---+--+---+--+--+ | name | lid | rsrcc | rsrc | rsrc | +---+--+---+--+--+ | node1 | NULL | 1 | NULL |1 | | node2 | NULL | 1 | NULL |2 | | node3 | NULL | 1 | NULL |1 | | node3 | NULL | 1 | NULL |2 | | node0 | NULL | 1 | NULL | NULL | | node1 |4 | 2 |1 |1 | | node2 |4 | 2 |2 |2 | | node3 |4 | 2 |1 |1 | | node3 |4 | 2 |2 |2 | | node0 | NULL | 2 | NULL | NULL | | node1 |3 | 2 |1 |1 | | node2 |3 | 2 |2 |2 | | node3 |3 | 2 |1 |1 | | node3 |3 | 2 |2 |2 | | node0 | NULL | 2 | NULL | NULL | | node1 | NULL | 1 | NULL |1 | | node2 |2 | 1 |2 |2 | | node3 | NULL | 1 | NULL |1 | | node3 |2 | 1 |2 |2 | | node0 | NULL | 1 | NULL | NULL | | node1 |1 | 1 |1 |1 | | node2 | NULL | 1 | NULL |2 | | node3 |1 | 1 |1 |1 | | node3 | NULL | 1 | NULL |2 | | node0 | NULL | 1 | NULL | NULL | | node1 | NULL | 0 | NULL |1 | | node2 | NULL | 0 | NULL |2 | | node3 | NULL | 0 | NULL |1 | | node3 | NULL | 0 | NULL |2 | | node0 | NULL | 0 | NULL | NULL | +---+--+---+--+--+ SELECT activenodes.name,lrsrc.lid,activelayers.rsrcc,lrsrc.rsrc,nrsrc.rsrc FROM activelayers LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND lrsrc.lid=activelayers.lid INNER JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc INNER JOIN activenodes ON nrsrc.id=activenodes.id ORDER BY activelayers.lid DESC; +---+--+---+--+--+ | name | lid | rsrcc | rsrc | rsrc | +---+--+---+--+--+ | node1 |4 | 2 |1 |1 | | node3 |4 | 2 |1 |1 | | node2 |4 | 2 |2 |2 | | node3 |4 | 2 |2 |2 | | node1 |3 | 2 |1 |1 | | node3 |3 | 2 |1 |1 | | node2 |3 | 2 |2 |2 | | node3 |3 | 2 |2 |2 | | node2 |2 | 1 |2 |2 | | node3 |2 | 1 |2 |2 | | node1 |1 | 1 |1 |1 | | node3 |1 | 1 |1 |1 | +---+--+---+--+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-4.0.22 is released
Hi! InnoDB is the MySQL table type that supports FOREIGN KEY constraints, row-level locking, Oracle-style consistent, non-locking SELECTs, multiple tablespaces (in 4.1), and a non-free online hot backup tool. Release 4.0.22 is mainly a bugfix release of the stable 4.0 series. This release fixes the unfortunate mysqldump --opt assertion failure that slipped into 4.0.21. There are two new my.cnf options: innodb_table_locks : By setting this to 0, you can get the LOCK TABLES behavior that was used prior to 4.0.20. Old applications that use LOCK TABLES in the AUTOCOMMIT=1 mode can easily end up in deadlocks if this is set to 1, which is the default value. innodb_max_purge_lag: Under bursts of a high UPDATE or DELETE load, you can use this to force InnoDB to favor purge at the cost of users' SQL statements. If the purge lags behind, the physical size of the database may grow so that operation becomes disk-bound. To prevent that, it is better to use this parameter to favor purge. Functionality added or changed: * New mysqld option --innodb-table-locks and session variable innodb_table_locks (on by default). You can now disable InnoDB table locks if your application depends on the way MySQL did table locks before 4.0.20. (Bug #3299, Bug #5998) * Added the startup option and settable global variable innodb_max_purge_lag for delaying INSERT, UPDATE, and DELETE operations when the purge operations are lagging. The default value of this parameter is zero, meaning that there will not be any delays. * Change error code to HA_ERR_ROW_IS_REFERENCED if we cannot DROP a parent table because it is referenced by a FOREIGN KEY constraint. Bugs fixed: * Fixed a bug introduced in 4.0.21. An assertion failed if one used mysqldump with the option -l or --opt, or if one used LOCK TABLES ... LOCAL. (Workaround in 4.0.21: use --quick and --single transaction. (Bug #5538) * Make the check for excessive semaphore waits to tolerate glitches in the system clock (do not crash the server if the system time is adjusted while InnoDB is under load.). (Bug #5898) * Fixed a bug in the InnoDB FOREIGN KEY parser that prevented ALTER TABLE of tables containing # in their names. (Bug #5856) * Fixed problem introduced in 4.0.21 where a connection starting a transaction, doing updates, then FLUSH TABLES WITH READ LOCK, then COMMIT, would cause replication slaves to stop complaining about error 1223. The bug surfaced when using the InnoDB innobackup script. (Bug #5949) * If one updated a column so that its size changed, or updated it to an externally stored (TEXT or BLOB) value, then ANOTHER externally stored column would show up as 512 bytes of good data + 20 bytes of garbage in a consistent read that fetched the old version of the row. (Bug #5960) * Release the dictionary latch during a long cascaded FOREIGN KEY operation, so that we do not starve other users doing CREATE TABLE or other DDL operations. This caused a notorious 'Long semaphore wait' message to be printed to the .err log. (Bug #5961) * Let InnoDB remember row locking type (X or S) inside LOCK TABLES, also over plain consistent read SELECTs. * Having a column prefix index in the primary key, and the same column fully in a secondary key could cause an assertion failure in row_build_row_ref(). (Bug #5180) Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]