re: foreign key not able to be made
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am curious what I am doing wrong. I am using mysql-5.0.13, on Solaris. The error I get, on the first alter table, is: ERROR 1005 (HY000) at line 70: Can't create table './FlashcardProto_production/#sql-151_f5d.frm' (errno: 150) Any help would be appreciated. Thanx. - -- - -- Table structure for table `cardfaces` - -- DROP TABLE IF EXISTS `cardfaces`; CREATE TABLE `cardfaces` ( `id` int NOT NULL auto_increment, `card_id` int NOT NULL, `question` text NOT NULL, `answer` text NOT NULL, INDEX car_ind(`card_id`), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -- - -- Dumping data for table `cardfaces` - -- - -- - -- Table structure for table `cards` - -- DROP TABLE IF EXISTS `cards`; CREATE TABLE `cards` ( `id` int NOT NULL auto_increment, `deck_id` int NOT NULL, `title` tinytext, `description` text, PRIMARY KEY (`id`), INDEX(`deck_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -- - -- Dumping data for table `cards` - -- - -- - -- Table structure for table `decks` - -- DROP TABLE IF EXISTS `decks`; CREATE TABLE `decks` ( `id` int NOT NULL auto_increment, `name` varchar(50) NOT NULL, `numfaces` int NOT NULL, `cardtype_id` int NOT NULL, `description` text, PRIMARY KEY (`id`), INDEX(`cardtype_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `cardtypes`; CREATE TABLE `cardtypes` ( `id` int NOT NULL auto_increment, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `facedefs`; CREATE TABLE `facedefs` ( `id` int NOT NULL auto_increment, `name` varchar(50) NOT NULL, `cardtype_id` int NOT NULL, INDEX(`cardtype_id`), PRIMARY KEY(`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `cardfaces` ADD CONSTRAINT `fk_card_deck` FOREIGN KEY (`card_id`) references card(`id`); ALTER TABLE `cards` ADD CONSTRAINT `fk_card_deck` FOREIGN KEY (`deck_id`) referencesdeck(`id`); ALTER TABLE `decks` ADD CONSTRAINT `fk_deck_cardtype` FOREIGN KEY (`cardtype_id` REFERENCES cardtypes(`id`); ALTER TABLE `facedefs` ADD constraint `fk_facedefs_cardtype` FOREIGN KEY(`cardtype_id`) REFERENCES cardtypes(`id`); - -- Don't worry about people stealing an idea. If it's original, you will have to ram it down their throats. Howard Aiken James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFJVd9ikQgpVn8xrARAnwbAJ4vlp6xyEZas3ikzGX7IxDcVarCbQCfTkk/ vhauWDMFuSsg+SeFDucBIYA= =ft0z -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: would like more info on INSERT ... VALUES
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 In the manual (http://dev.mysql.com/doc/refman/5.0/en/insert.html) I saw this snippet: If you use an INSERT ... VALUES statement with multiple value lists But, I thnk I am missing some information on the syntax of INSERT ... VALUES. This may be what I am looking for, as I want to see if sending 100 inserts at one time is faster than doing 100 inserts, one at a time. I expect it is, but I would like to get some numbers for my query. Thank you for any help. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDmHZTikQgpVn8xrARAnFKAJ9Co+SEny8Xl3q/NaHW528qv+JawwCeOjgj jCma5KTtC6w7f51LnmgECHY= =HIjc -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: no MINUS operator in mysql 5?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am wondering if there is a set-based minus operator. I am assuming there isn't, but I thought I would ask. Here is the query I am trying that fails. Thank you. select n.name from names n, transactions t where n.name=t.name MINUS select nb.name from stats_names sn - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDg4QcikQgpVn8xrARAsFUAJ4h/vh+lcIsSASydKLzR1U4Qs62aQCeOCxk rjzBfzwg5hoHAAOIrnL1qD4= =9YjG -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: how am I missing this key
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Using mysql 5.0.15 my query gets a result of Unknown column 'i.rid' in 'on clause' There is actually a complete query, but this is a snippet, as the selects with subqueries I don't believe will cause the problem. When I changed c.rid=i.rid to c.rid=c.rid, the query executes. It is incorrect, but at least it runs, so, it appears that that the last join is where the problem is. Any suggestions as to what might be the cause? Thanx. FROM items i, nams.netids n INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON a.role=r.code LEFT OUTER JOIN nams.regterms AS t ON (na.badge=t.badge) LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid) WHERE i.rid=12415 AND n.netid='alb' AND i.status='A' AND c.badge IS NULL LIMIT 1; When I use explain on mysql 4.1.08 I get this: - --+ | id | select_type| table | type | possible_keys | key| key_len | ref| rows | Extra | +++---++++-++--+--+ | 1 | PRIMARY| i | const | PRIMARY | PRIMARY| 4 | const |1 | | | 1 | PRIMARY| n | const | PRIMARY,netid_badge_ndx | PRIMARY| 12 | const |1 | | | 1 | PRIMARY| na| const | PRIMARY | PRIMARY| 4 | const |1 | Using index | | 1 | PRIMARY| a | ref| badge_ndx,role_ndx | badge_ndx | 4 | const |1 | | | 1 | PRIMARY| r | eq_ref | PRIMARY | PRIMARY| 2 | nams.a.role|1 | | | 1 | PRIMARY| t | ref| badge_ndx | badge_ndx | 4 | const | 12 | Using where | | 1 | PRIMARY| c | ref| curuse_ndx | curuse_ndx | 4 | const deptid |1 | Using where | | 6 | DEPENDENT SUBQUERY | f | ref| badge_ndx,role_ndx,college_ndx | badge_ndx | 4 | const | 1 | Using where; Using temporary; Using filesort | | 6 | DEPENDENT SUBQUERY | r | eq_ref | PRIMARY | PRIMARY| 2 | nams.f.role|1 | | | 6 | DEPENDENT SUBQUERY | d | eq_ref | PRIMARY | PRIMARY| 2 | nams.f.college |1 | Using where; Using index | | 5 | DEPENDENT SUBQUERY | f | ref| badge_ndx,role_ndx,dept_ndx | badge_ndx | 4 | const |1 | Using where; Using temporary; Using filesort | | 5 | DEPENDENT SUBQUERY | r | eq_ref | PRIMARY | PRIMARY| 2 | nams.f.role|1 | | | 5 | DEPENDENT SUBQUERY | d | eq_ref | PRIMARY,dabbr_ndx | PRIMARY| 4 | 4 | const |1 | | | 2 | DEPENDENT SUBQUERY | names | const | PRIMARY | PRIMARY| 4 | const |1 | | +++---++++-++--+--+ 20 rows in set (0.01 sec) - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDgdwkikQgpVn8xrARAky9AJ4lcBoVIsqkU2nBpca6fAZZsl5dvgCfR3H5 7ed5A7RcOLrcm2XxQh/zSD8= =+Xos -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: impossible WHERE noticed question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am trying to figure out why a query sometimes doesn't work, and so I decided to use explain to help me. We are using myslq 4.1.8 at the moment, btw. The only difference between the two queries is I change skywise to rcooksey. mysql explain select * FROM items i, nams.netids n INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON a.role=r.code LEFT OUTER JOIN nams.regterms AS t ON (na.badge=t.badge) LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid) WHERE ((a.enddate IS NULL AND r.lmsvalid='Y') OR (t.termcode=200508 and t.registered='Y')) AND i.rid=1999 AND n.netid='skywise' AND (i.status='A' OR c.badge IS NULL); ++-+---++-++-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++-++-+--+--+-+ | 1 | SIMPLE | i | const | PRIMARY | PRIMARY | 4 | const|1 | | | 1 | SIMPLE | n | const | PRIMARY,netid_badge_ndx | PRIMARY | 12 | const|1 | | | 1 | SIMPLE | na| const | PRIMARY | PRIMARY | 4 | const|1 | | | 1 | SIMPLE | a | ref| badge_ndx,role_ndx | badge_ndx | 4 | const|1 | | | 1 | SIMPLE | r | eq_ref | PRIMARY | PRIMARY | 2 | nams.a.role |1 | | | 1 | SIMPLE | t | ref| badge_ndx | badge_ndx | 4 | nams.a.badge |3 | Using where | | 1 | SIMPLE | c | ref| curuse_ndx | curuse_ndx | 4 | const|1 | Using where | ++-+---++-++-+--+--+-+ 7 rows in set (0.00 sec) mysql explain select * FROM items i, nams.netids n INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON a.role=r.code LEFT OUTER JOIN nams.regterms AS t ON (na.badge=t.badge) LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid) WHERE ((a.enddate IS NULL AND r.lmsvalid='Y') OR (t.termcode=200508 and t.registered='Y')) AND i.rid=1999 AND n.netid='cooksey' AND (i.status='A' OR c.badge IS NULL); ++-+---+--+---+--+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+--+-+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL| NULL | NULL | Impossible WHERE noticed after reading const tables | ++-+---+--+---+--+-+--+--+-+ 1 row in set (0.01 sec) - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDaRv2ikQgpVn8xrARAswuAJ9+bcmZ0i17hD2AJatCo4pAi1yBggCgjTv8 AWdskslAfIAU8f68LJNYKKc= =d+7U -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: possible bug in mysql 5.0.13
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Due to the complexity of my query I don't know how to get this down to a simple test case to demonstrate the error. This works under mysql 4.1.8 btw, so it is failing due to a change introduced recently. It also worked under mysql 5.0.9, but I haven't tested any of the beta versions between 9 and 13. Any suggestions as to what I may look at to make this simpler? I will be working on it in the meantime, to see what I find. mysql INSERT INTO curuse(rid, start, badge, ip, card_type, fullname, dept, college, campus) SELECT 12612,1129124442, n.badge, 0, (SELECT role FROM nams.names WHERE badge=n.badge), (SELECT IF(p.fullname IS NULL, (SELECT CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ', sna.gen) FROM nams.names AS sna WHERE sna.badge=sne.badge), p.fullname) AS fullname FROM nams.netids AS sne LEFT OUTER JOIN nams.prefs AS p ON(sne.badge=p.badge) WHERE sne.netid='jblack') AS fullnameTable, (SELECT d.dabbr FROM nams.affiliations f, nams.roles r, nams.departments d WHERE f.role=r.code AND f.deptid=d.deptid AND f.badge=n.badge AND d.dabbr !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1), (SELECT f.college FROM nams.affiliations f, nams.roles r, nams.colleges d WHERE f.role=r.code AND f.college=d.code AND f.badge=n.badge AND d.code !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1), (SELECT f.campus FROM nams.affiliations f, nams.roles r, nams.departments d WHERE f.role=r.code AND f.deptid=d.deptid AND f.badge=n.badge AND d.dabbr !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1) FROM items i, nams.netids n INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON a.role=r.code LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid) WHERE r.lmsvalid='Y' AND a.enddate IS NULL AND i.rid=1999 AND n.netid='jblack' AND (i.status='A' OR c.badge IS NULL) LIMIT 1; ERROR 1054 (42S22): Unknown column 'i.rid' in 'on clause' +--+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +--+--+--+-+-++ | rid | int(11) | NO | PRI | NULL | auto_increment | | lid | int(11) | NO | MUL | 0 | | | itemtype | char(4) | NO | MUL | | | | label | char(12) | NO | UNI | | | | status | char(1) | NO | | | | | layoutx | int(11) | YES | | NULL | | | layouty | int(11) | YES | | NULL | | | theta | int(11) | YES | | NULL | | +--+--+--+-+-++ 8 rows in set (0.02 sec) mysql describe curuse; +---+-+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-+---+ | rid | int(11) | NO | PRI | 0 | | | start | int(11) | NO | | 0 | | | badge | int(11) | NO | MUL | 0 | | | card_type | char(2) | NO | | | | | dept | char(3) | NO | | | | | college | char(2) | NO | | | | | campus | char(1) | NO | || | | fullname | varchar(24) | NO | | | | | ip | varchar(40) | NO | PRI | || | alive | int(11) | NO | | 0 | | +---+-+--+-+-+---+ 10 rows in set (0.02 sec) - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDTRZfikQgpVn8xrARAo5LAJ0fkAmKtxtMHHGWMY2kcnSOuXVCqQCbB4sH A4h+LzTgcZns66WLG2xOp9c= =YIgR -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible bug in mysql 5.0.13
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter Brawley wrote: James, You can reproduce that error by writing ... SELECT ... FROM a, b INNER JOIN c ON a.x=c.y The error goes away if you instead write ... SELECT ... FROM b, a INNER JOIN c ON A.x=c.y I will try it. Thank you. Would this be a bug, or just something that should be documented? - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDTUz7ikQgpVn8xrARArGNAJ9MrOEjMay9N4VirSvt9Zv/fZYE0ACfSOYD wqshJve8wnUiZv0vWqVNrCc= =Nhd7 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible bug in mysql 5.0.13
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter Brawley wrote: James, You can reproduce that error by writing ... SELECT ... FROM a, b INNER JOIN c ON a.x=c.y The error goes away if you instead write ... SELECT ... FROM b, a INNER JOIN c ON A.x=c.y so you might try swapping FROM items i , nams.netids n Tried that,now I get: Unknown column 'n.badge'in 'on clause' So, whichever order I put them in, I get one of two errors. It appears that this bug will continue to break for me until it is fixed in the next version, hopefully. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDTVCiikQgpVn8xrARAqGjAJ9y4/ym15QPZj8KVvbyuIkmTIKMKACfTlP9 cd7w92nB8uhOH2Y1+jAe4MU= =FOcN -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible bug in mysql 5.0.13
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: SELECT... FROM items i INNER JOIN nams.netids n INNER JOIN ... ... Does the problem remain? If it goes away, this would be useful information to include in your bug report. Thank you for the suggestion, but it led to an error of: Unknown column r.rid in on clause. Guess I will start working on creating a test db where I can demonstrate this bug, to make it easy to reproduce. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDTWgMikQgpVn8xrARAnaTAKCHTQDzZtL2Iwh09iwFZhKX6kJ3PACgi8u5 mFNisss5Yc4k/WlicBTG5lM= =Worc -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: determing how loaded my mysql server is
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 We have a production server that may be getting overworked, but I don't know. When I look at 'mysqladmin status' it consistently shows 107.# queries/sec on average. That seems a bit high, it should be less, I imagine, since the Id number in the processlist seems to increment by perhaps 5-10/second. Is there a way to monitor the server, without putting too much of a load on the mysql server, to see what is going on? Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDKdNoikQgpVn8xrARAnLrAJ9ZxPNHUskSQVTxn/JRgm6RCkjHlgCdFGj6 d9PUim7Yo713+GbcbZZ30Z0= =AQZl -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: could insert...select have problem with corrupt table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Today mysql 4.1.8 has a segmentation fault (signal 11), and crashed. In the process one table was left corrupted, when it started back up. An application continued to call insert...select on the corrupted table as it wasn't realized this happened. So, in the replication log on the slave we get this error: 050901 10:50:59 [ERROR] Slave: Error 'Duplicate entry '2925542' for key 1' on query. Default database: 'mydb'. Query: 'INSERT INTO target_table(start,badge,finish,lid,itemtype,rid)SELECT c.start, c.badge, 1125586211, i.lid, i.itemtype,12576 FROM corrupt_table c, good_table i WHERE i.rid=12576 AND c.rid=12576', Error_code: 1062 Could this duplicate record have been caused by my doing an insert...select from a corrupted table to a good table? This is on Solaris 9, btw. Thank you for any clarification. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDF1JEikQgpVn8xrARAmaBAJ4jDyW+RdjLBh+tW21PnYR8XRVajwCfbWCV uQcsbwMY9HKRAa7srzk9TpY= =FLGE -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: query hasn't worked properly since Mysql 5.0.6
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a query that uses a subquery that works under mysql 4.1 and 5.0.6, but doesn't work on 5.0.7 or 5.0.9 I am trying to understand why it may be having a problem so I can make a test case to demonstrate it, and am hoping that someone may have an idea. SELECT i.label, i.itemtype, it.name, i.rid, i.status, l.locname, i.lid, (SELECT IF(p.fullname IS NULL, (SELECT CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ', sna.gen) FROM nams.names AS sna WHERE sna.badge=sne.badge), p.fullname) AS fullname FROM nams.netids AS sne LEFT OUTER JOIN nams.prefs AS p ON(sne.badge=p.badge) WHERE sne.badge=c.badge) AS UserFullname, i.layoutx, i.layouty, i.theta FROM items AS i INNER JOIN locations AS l ON(i.lid=l.lid) INNER JOIN itemtypes AS it ON(i.itemtype=it.itemtype) LEFT OUTER JOIN curuse AS c ON(c.rid=i.rid) WHERE l.locname='iclab' ORDER BY i.status, i.label; If I take out the following part then the query works fine. (SELECT IF(p.fullname IS NULL, (SELECT CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ', sna.gen) FROM nams.names AS sna WHERE sna.badge=sne.badge), p.fullname) AS fullname FROM nams.netids AS sne LEFT OUTER JOIN nams.prefs AS p ON(sne.badge=p.badge) WHERE sne.badge=c.badge) AS UserFullname, - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC26O3ikQgpVn8xrARArFEAKCPBVaU85aA4gR0DOjcoN5lNxqvuwCghLAO bGNL/jNf65hw6u8RBOFR2Eg= =N9SC -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: mysql 5.07 results incorrect, mysql 5.0.6 results correct
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 If I run the first query on mysql 5.0.6 the results are correct, I get 142 rows, if I run it on 5.0.7 I get 8 rows back. If I remove the nested select, as shown in the second query, then it works fine on 5.0.7. I am trying to understand what is going on. I may try to create a test case to demonstrate this, but I don't understand why the problem is happening in the first place. This is on Solaris 8 and Solaris 9, btw. Thank you for any suggestions. SELECT i.label, i.itemtype, it.name, i.rid, i.status, l.locname, i.lid,(SELECT IF(p.fullname IS NULL, (SELECT CONCAT(sna.fname, ' ', sna.mname, ' ', sna.lname, ' ', sna.gen) FROM nams.names AS sna WHERE sna.badge=sne.badge), p.fullname) AS fullname FROM nams.netids AS sne LEFT OUTER JOIN nams.prefs AS p ON(sne.badge=p.badge) WHERE sne.badge=c.badge) AS UserFullname, i.layoutx, i.layouty, i.theta FROM items AS i INNER JOIN locations AS l ON(i.lid=l.lid) INNER JOIN itemtypes AS it ON(i.itemtype=it.itemtype) LEFT OUTER JOIN curuse AS c ON(c.rid=i.rid) WHERE l.locname='iclab' ORDER BY i.status, i.label; SELECT i.label, i.itemtype, it.name, i.rid, i.status, l.locname, i.lid,(SELECT IF(p.fullname IS NULL, i.layoutx, i.layouty, i.theta FROM items AS i INNER JOIN locations AS l ON(i.lid=l.lid) INNER JOIN itemtypes AS it ON(i.itemtype=it.itemtype) LEFT OUTER JOIN curuse AS c ON(c.rid=i.rid) WHERE l.locname='iclab' ORDER BY i.status, i.label; - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC1R8dikQgpVn8xrARAjsfAJ4hsAKn81s0/rjP3V9N1MccjUVPIACdHbSV XiUuUvFYubdZgQnrfTGBNpw= =mAcq -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: how to add comments to an existing table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 How would I add a comment to rows in an existing table, if I am not changing the row definition? This is for mysql 4.1 and 5.0. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCuxBsikQgpVn8xrARAvsRAJ0YrVwCPorS2914+jwLA9sWKt7gYQCglFo+ ojh1r5Skifmg83MZAcVonO4= =2WLs -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: is keys a restricted keyword
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a db named keys, and when I tried to do grant select,insert,update,delete on keys.* to [EMAIL PROTECTED] I get an error, about SQL syntax near 'keys.*'. Should this work? I am using mysql 5.0.6 and 4.1.7. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCqKXvikQgpVn8xrARAgLjAJ0aD8nDJu49TbD2xFAQPtoEgyXF3wCfXcRQ krNHXdaEqrieu8efbpTM41I= =srN5 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: file my.cnf is missing for Solaris 8, for mysql 5.0.6
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am getting the following error, when I try to use bin/mysqld_safe, Could not open require defaults file: $MYSQL_HOME/data/my.cnf Fata error in defaults handling. Program aborted Did I d/l a bad .tar.gz file from a mirror? Thank you. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCpFVBikQgpVn8xrARAhJaAJ0YINBpRLhq3VZ+YFkCSHMr1arxUACeKLTe 1ld+80ihBsZC54SCp7FSuJA= =Mmb6 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: solution found for my.cnf problem in mysql 5.0.6
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I followed the steps in http://lists.mysql.com/internals/25364 and fixed my script, so, it would appear that the script is bad for the max version of mysql 5.0.6, for Solaris 8. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCpFlmikQgpVn8xrARAh9qAJ0ekN/cqiuFPRs2urLkU5e2ulbFlQCfQ5+r TJknK26B3tkDnFGa6hrlcXM= =oLsU -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: error 1418 when creating stored procedure using mysql 5.0.6
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am curious what has changed in 5.0.6 that leds to this error: ERROR 1418: This routine is declared to be non-deterministic and to modify data and binary logging is enabled What should I be looking at changing in my CREATE PROCEDURE call to enable it to work now. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCpHrkikQgpVn8xrARAsU9AJwOp7hjiQNliBEze8699S+9VnPYwACglT0N IQJ12hARPu9odCU1jRxxdts= =+DhI -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 1418 when creating stored procedure using mysql 5.0.6
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Here are two of the stored procedures I tried, as I had forgotten to send that with my query. I am hoping that the stored procedure will now work, as it hasn't since mysql 5.0.1. Thanx for any help. CREATE PROCEDURE assignItem ( user CHAR(15), rid int, start int) BEGIN INSERT INTO curuse(rid, start, badge, card_type, dept, college, campus, fullname, ip) SELECT rid, start, n.badge, a.role, a.deptid, a.college, a.campus, concat(na.fname, , na.lname), 0 FROM nams.names na, items i, nams.netids n, nams.affiliations a WHERE i.rid=rid AND n.netid=user AND na.badge=n.badge AND a.badge=na.badge AND i.status='A' AND a.source='B' LIMIT 1; UPDATE items SET status='U' WHERE rid=rid; END; CREATE PROCEDURE deassignItem ( rid int, endtime int) BEGIN INSERT INTO transactions(start,badge,card_type,dept,college,campus,finish,lid,itemtype,rid) SELECT c.start, c.badge, c.card_type, c.dept, c.college, c.campus, endtime, i.lid, i.itemtype, rid FROM curuse c, items i WHERE i.rid=rid AND c.rid=rid; UPDATE items set status='A' where rid=rid; DELETE FROM curuse WHERE rid=rid; END; - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCpJYGikQgpVn8xrARAlslAKCMf8ChA6s+pngbJ82D5WWOjZYLvACeJDPD F4dI37k5oEv1H8SeaLfLB24= =7MBu -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: using OR seems faster than using IN
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a query where I am fetching, in my test 68, rows. Using OR was faster by about 1.5 times, than the same query using IN. Should this be expected, using mysql 5.0.4, on Solaris 8, java 1.5. SELECT name, id FROM table WHERE idx=? OR idx=? OR idx=? ... SELECT name, id FROM table WHERE idx IN(?,?,?) I am also using prepared statements, obviously, and looping through my function 100 times, to get a better idea as to speed, and using junit for the testing. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCig6sikQgpVn8xrARAuBzAJ44tfBLXpYelSd0bTu6GDqjnvJEbgCcDAh4 pOr6+PhSZ7YEXKqCZPtKPzU= =zo9u -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: determing number of Tuesdays within a date range
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a table with session information, with a start and stop time. I can aggregate the information, grouping it by hour or weekday. But, when I group it by hour and weekday, I need to also show the average usage, so I need to figure out how many Tuesdays are contained within 1/1/2004 - 5/1/2004, and I need to come up with a result where I know how many of each day of the week is within that date range. I am hoping someone may have a solution, as, once I know the number of Tues then I can state the average number of sessions on a Tuesday at 4pm. Thanx for any help. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCcPaMikQgpVn8xrARAoSKAJ96AzRIgGXnjbn/Krlxehz7K/HTlgCfSXZe VxslEjt9ERmUBiDJoBti3SE= =bwx8 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: using if in select statement
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My boss and I were playing with using select statements, and we can actually execute subqueries as an option if the result is true or false. Is this expected behavior, or is it something that may be fixed in a revision, before I begin to depend on it being acceptable behavior. Thanx. - -- Corruptisima republica plurimae leges. [The more corrupt a republic, the more laws.] Tacitus from Annals III, 116AD Blogs: http://jamesruminations.blogspot.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (MingW32) iD8DBQFCcX2xJ/zyYkX46joRAgiVAJ9rw9BRPuT164/4wpYlHJbdj+x1agCcCbKG fM7SPPMIo6QSWijniegUM9A= =wK54 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: group by day of week and group by hour in day
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am curious if there is a simple way to do this. I was just asked to give some data that requires me to group by day of week and also group by hour in day (two different pages). Thanx for any help. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCb6NkikQgpVn8xrARAhEpAJwK1Yp9gBn0bnfUJ07dbf2bVHpSiACfd8Wi HycjEYTbpk0NAPEtEgV5BpY= =3+gk -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Mysql 5.0.4 still broken for my stored procedures
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 About 3 wks ago I had sent some files to the list regarding my stored procedures, and they are still broken in 5.0.4. I get an error: Internal error when parsing callable statement metadata and it happens at: com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes, line 6963 I am curious if anyone else is having a failure like this. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCb7HRikQgpVn8xrARApS1AJ9owJYTZBCyQllrHGT96jgmAsRAFgCfYpUE vAaYxIMl7umxQ8VukTKV0j8= =e693 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: getting hours from two datetime columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Is there a way to get all of the hours between two datetime columns? For example, if there is an entry that starts at 2005-4-1 3:00:00 and the end is 2005-4-1 11:00:00 I would like to know that this enry was used in hours 3 - 11, or for 8 hours. I hope this makes sense. I am thinking I am going to be doing this in my application. Thanx for any suggestions. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCb9puikQgpVn8xrARAgTBAJ0eefvxMCqjG5UUWa7vlaFYR5LSMQCfSLiF niYw22pzYxgVTy6bybgJ80Q= =Ysmy -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: mysql 5.0.4 for Solaris, as a tarball
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Is there somewhere where I can d/l the latest beta as a tarball? Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCZnISikQgpVn8xrARAur+AJwMSlfCaP1cWezYzEC5+Py5865pUACfdQ7L xr5b+ANBAuUW8/NFflGlMF4= =LATN -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Error 1130 when on local machine, but can connect remotely
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My mysql server is running on mysql.xxx.edu, for this example. There is a cname so that alias.xxx.edu is mapped to mysql.xxx.edu, so that we can failover to a different server by changing the cname. When I type: mysql -h alias.xxx.edu -pPassword -u username mydatabase I get: ERROR 1130 (0): #HY00Host 'alias' is not allowed to connect to this MySQL server But, when I am on my development machine, and I use the exact same command, it works. Why would it fail when I am on the computer that the server is actually running on? Thanx for any suggestions. We are using Solaris 9, and mysql 4.1.xx - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCY85NikQgpVn8xrARAod6AKCGlDmqIYxHsuyEnwqX50IlW1tIKQCeKkIt fuq5O7az9vUS6vh5hqSKX/s= =yg44 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: just dumping table definitions, not data
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Is there a simple way to just get all the table definitions? I have a bug in mysql connectorj and I want to send my stored procedure with the table definitions, but there are several tables used in it. This is with mysql 5.0.3, btw. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCX9PWikQgpVn8xrARAvynAJ9B4ffkvm8HZ087LPgahWeBdvgRwQCfX5Wc lrboWqDiso5uzOfoFxz+EMA= =3hFf -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: calling a stored procedure from another stored procedure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a stored procedure called assignItem. I would like to call this from another stored procedure. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCVqEoikQgpVn8xrARAhSTAJ9OL4gL+xTK8e6lmuKt4Uook6V2sgCeMiBN EOVEjJ5gm8Qg4TjuWl7GNcU= =5GgG -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: stored procedure error is misleading
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I am using mysql 5.0.3, running under Solaris 8, and I believe the error
I am getting is not possible, but I don't know what I did wrong.
I do:
call assignItem('user', 1999, 97);//
I get:
ERROR 1172 (42000): Result consisted of more than one row
But, there is only one row possible, as the rid is unique.
I am wondering if there is something obvious I missed.
Thanx.
mysql CREATE PROCEDURE assignItem (
- user CHAR(15),
- rid int,
- start int)
- BEGIN
- DECLARE itemtype CHAR(13);
- DECLARE curusecount INT DEFAULT 0;
- SELECT itemtype INTO itemtype FROM items WHERE rid=rid;
- END;//
SELECT itemtype FROM items WHERE rid=1999;//
+--+
| itemtype |
+--+
| PC |
+--+
mysql describe items;//
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| rid | int(11) | NO | PRI | NULL| auto_increment |
| lid | int(11) | NO | MUL | 0 | |
| itemtype | char(4) | NO | MUL | | |
| label| char(12) | NO | UNI | | |
| status | char(1) | NO | | | |
| layoutx | int(11) | YES | | NULL| |
| layouty | int(11) | YES | | NULL| |
| theta| int(11) | YES | | NULL| |
+--+--+--+-+-++
- --
Love is mutual self-giving that ends in self-recovery. Fulton Sheen
James Black[EMAIL PROTECTED]
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCVX3qikQgpVn8xrARAjUHAJ4xvgPiGge494hydhmzCfnLuyasegCcDEqk
94SdQEwxIdqFlktjGDWmySY=
=3ciU
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: any performance increase from using prepared statements
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Are prepared statements cached on the server side? When I create a prepared statement with one connection, and another connection tries to create the same prepared statement, will the cached one be used? I am trying to see when prepared statements would be best used? It seems that it may be an improvement if I am in a tight loop, using the same connection, but may not be as good as dynamically generated queries when using different database connections. Is there any good resources to explain when it is best to use a prepared statement and a dynamically generated statement? Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCU/rLikQgpVn8xrARArf5AJ4xp2ra/kbIGEpOEbumfd5MkOYjDwCeKtfc gG6GJ4zSMU/d/XY5GNatYHY= =n7xB -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: stored procedure has very poor performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 It would appear, based on my testing, so far, that using stored procedures is some between 25% slower and 10x slower, depending on the test. I am using jdk1.5, on Solaris 8, and mysql 5.0.3. I hope that when 5 comes out of beta that the performance is improved. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCVCSEikQgpVn8xrARAkaSAJwOaAyQSfKKZXj0a1VNbiegkInKkwCfaEl1 HF3YdJVxevg7r/f6o2vkSBw= =k8yO -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: more on slow stored procedure performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 For the nams. tables the badge is the primary key, and since the stored procedure and the dynamically generated function generate the same queries, it shouldn't matter as to performance. I am using Solaris 8, JDK1.5, mysql 5.0.3 I haven't tested with prepared statements, but I don't know if I will, as I know there will be a performance hit there, based on past history. It appears that the stored procedure is 4x slower than dynamically generating, to almost 7x slower. Following is the time to do the tests: [junit] Testcase: testDeassignMultiDB took 0.088 sec [junit] Testcase: testDeassignMultiDBStoredProcedures took 0.34 sec [junit] Testcase: testDeassignMultiDBStoredProcedures100Reps took 13.712 sec [junit] Testcase: testDeassignMultiDB100Reps took 2.266 sec The last two tests do the exact same tests 100 times, so I can get a better idea as to numbers. Each test is: deassign assign deassign For the assign functions here are the query that is sent: [junit] INSERT INTO curuse(rid, start, badge, card_type, dept, college, campus, fullname, ip) SELECT 1999,1112812166, n.badge, a.role, a.deptid, a.college, a.campus, concat(na.fname, , na.lname), 0 FROM nams.names na, items i, nams.netids n, nams.affiliations a WHERE i.rid=1999 AND n.netid='jblack' AND na.badge=n.badge AND a.badge=na.badge AND i.status='A' AND a.source='B' LIMIT 1 [junit] UPDATE items SET status='U' WHERE rid=1999 Here is the stored procedure: CREATE PROCEDURE assignItem ( user CHAR(15), rid int, start int) BEGIN INSERT INTO curuse(rid, start, badge, card_type, dept, college, campus, fullname, ip) SELECT rid, start, n.badge, a.role, a.deptid, a.college, a.campus, concat(na.fname, , na.lname), 0 FROM nams.names na, items i, nams.netids n, nams.affiliations a WHERE i.rid=rid AND n.netid=user AND na.badge=n.badge AND a.badge=na.badge AND i.status='A' AND a.source='B' LIMIT 1; UPDATE items SET status='U' WHERE rid=rid; END; For deassign, here is the query: [junit] INSERT INTO transactions(start,badge,card_type,dept,college,campus,finish,lid,itemtype,rid)SELECT c.start, c.badge, c.card_type, c.dept, c.college, c.campus,1112812166, i.lid, i.itemtype,1999 FROM curuse c, items i WHERE i.rid=1999 AND c.rid=1999 [junit] UPDATE items SET status='A' WHERE rid=1999 [junit] DELETE FROM curuse WHERE rid=1999 Here is the stored procedure: CREATE PROCEDURE deassignItem ( rid int, endtime int) BEGIN INSERT INTO transactions(start,badge,card_type,dept,college,campus,finish,lid,itemtype,rid) SELECT c.start, c.badge, c.card_type, c.dept, c.college, c.campus, endtime, i.lid, i.itemtype, rid FROM curuse c, items i WHERE i.rid=rid AND c.rid=rid; UPDATE items set status='A' where rid=rid; DELETE FROM curuse WHERE rid=rid; END; Here are the three main tables that are used: | curuse | CREATE TABLE `curuse` ( `rid` int(11) NOT NULL default '0', `start` int(11) default NULL, `badge` int(11) default NULL, `card_type` char(2) default NULL, `dept` char(3) default NULL, `college` char(2) default NULL, `campus` char(1) default NULL, `fullname` varchar(24) default NULL, `ip` varchar(40) NOT NULL default '', `alive` int(11) default NULL, PRIMARY KEY (`rid`,`ip`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Items that are currently assigned' | | items | CREATE TABLE `items` ( `rid` int(11) NOT NULL auto_increment, `lid` int(11) NOT NULL default '0', `itemtype` char(4) NOT NULL default '', `label` char(12) NOT NULL default '', `status` char(1) NOT NULL default '', `layoutx` int(11) default NULL, `layouty` int(11) default NULL, `theta` int(11) default NULL, PRIMARY KEY (`rid`), UNIQUE KEY `label_ndx` (`label`), KEY `itemtype_ndx` (`itemtype`), KEY `lid_ndx` (`lid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | | transactions | CREATE TABLE `transactions` ( `xid` int(11) NOT NULL auto_increment, `start` int(11) NOT NULL default '0', `finish` int(11) NOT NULL default '0', `lid` int(11) NOT NULL default '0', `itemtype` char(4) NOT NULL default '', `rid` int(11) NOT NULL default '0', `badge` int(11) NOT NULL default '0', `card_type` char(2) NOT NULL default '', `dept` char(3) NOT NULL default '', `college` char(2) NOT NULL default '', `campus` char(1) NOT NULL default '', PRIMARY KEY (`xid`), KEY `start_ndx` (`start`), KEY `rank_ndx` (`card_type`), KEY `dept_ndx` (`dept`), KEY `college_ndx` (`college`), KEY `campus_ndx` (`campus`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCVC08ikQgpVn8xrARApKUAJ4/VMnH3T4cB7gUDYYLf4SZKbe4XwCfQbZ1 5DUJaPRnmNJs170/UpGl3OA= =Iuwr -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http
re: stored procedure calling another database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Is it possible to have a stored procedure query another database? I have two databases where the second (B) uses information from (A) to make decisions. It would be great if the stored procedure on database B could query A, so that it can make decisions. I am using mysql 5.0.3 if it matters. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCTbkVikQgpVn8xrARAk3tAJ9hgTrZZktxWhQc7JsH5jObS9NbcACfZBo0 NmYnKlIeJEzBiqUpaYsdTzg= =eDx8 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: stored procedure slower than not using stored procedure?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am curious if this should be the norm, that the stored procedure took 879 ms when I called it 9 times, with slightly different values, and the non-stored procedure test took 512ms with also slightly different values. I am using jdk1.5 and mysql 5.0.3 on Solaris 8. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCTcI+ikQgpVn8xrARAic6AJ0QiAlSYq/MGpNNLj7sEfHabKUkPQCdEjIO Ccq+YOUiTNeXI/wF0xar+fM= =namZ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: copying a db for mysql 5.0.3
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Until this version, I used to just copy the data/db directory from the old database to the new one. Now, I can't do that, and I think it is because of the data dictionary. I have a database that only exists on this machine, and I want to get it into the new database. When I tried to just copy I had complaints of not being able to find the tables .frm files, even though I see them in the data directory. Is there any way to get this to work, now? Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCSXsqikQgpVn8xrARAiwoAJ9k41oMugqdnYIj20IT2cKrhSrprACZAXFT CgG+rG4rXWl686kKyaoMTtM= =bWvN -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: problem with mysql-max-5.0.3 for Solaris 8 32 bit
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 It would appear that the package is for the 64-bit OS, even though it is listed as being for the 32-bit one. I see directories, that failed to install, of: /usr/local/mysql-max-5.0.3-beta-sun-solaris2.8-sparc-64bit/sql-bench I had gotten my file from http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-max-5.0.3-beta-sun-solaris2.8-sparc.pkg.gz/from/http://mysql.mirrors.pair.com/ - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCSGQkikQgpVn8xrARAqr1AJsFslyPkjRck+5uNTPbU3gxoLx9fwCeJEEn fdly9uy3J0L38pkizClQNzY= =hmti -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: using one query to save data in 4 tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am curious if this would be possible, and which version of mysql would be needed. Basically, I want to store a user in one table, then get the id for the user I just saved, and store three more rows, each in a different database, using the user's id in the insert statements. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCRH8iikQgpVn8xrARAsw5AJ0SxPoMMcy1QUa1GNNEdfg51Q7Q8ACfYHg7 NdFWteuQU4JjSfx7yYS++9k= =JZD5 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: can 5.0.2 be built for Solaris 8
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have tried to compile mysql, and got some linking errors. Any chance a binary for Solaris 8 can be made available? Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCGeAIikQgpVn8xrARAnzUAJ4hAQDnl5uE1O6b2rq6ejRkWS3DgwCePXxV trqHThwInfJg8BuPQC+uk2g= =7eMe -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: getting mysql5.0.2 for solaris 8
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am trying to compile this version for myself, but am having problems, as there is a complaint that sql_functions, get_hash_symbol and symbols are undeclared. Is there any plan to make a binary for solaris 8 for mysql 5.0.2? Thank you. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB78CMikQgpVn8xrARAkFeAJ4vwqNqnzH1gQd2Iz/1bLnUUJ/1LACgiSkn Ajymc+etpCmgNkVrO2s0GoQ= =zDIc -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: can a subquery be used in an insert
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I would like to set one column to point to the auto-incrementing index of the last comment in the database for my application, but I am getting an error when I run my test, and so I wonder if what I am trying to do is possible. I am using mysql 5.0.1, but I may have to switch to 4.1.9 if I can't get ~ mysql 5.0.2 for solaris 8, to do my tests with subqueries. My SQL command is: INSERT INTo comments VALUES(0, 1, NULL, 'testuser', 'this is a test comment', (SELECT max(idx) FROM comments WHERE sourceid=1)) ~ I get an error: You can't specify target table 'comments' for update in FROM clause. But, if I don't have 'From comments', then I get an error also. Thank you for any help. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB78G0ikQgpVn8xrARAhy9AJ9Bl+Yy+RMRtHrbzd0Iv4OONZXt8wCfWTJ2 ishDXVCmM1/+pBZsusy1Kvk= =tdXW -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can a subquery be used in an insert
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Philippe Poelvoorde wrote: | James Black wrote: | | -BEGIN PGP SIGNED MESSAGE- | Hash: SHA1 | | I would like to set one column to point to the auto-incrementing index | of the last comment in the database for my application, but I am getting | an error when I run my test, and so I wonder if what I am trying to do | is possible. | | | My SQL command is: | INSERT INTo comments VALUES(0, 1, NULL, 'testuser', 'this is a test | comment', (SELECT max(idx) FROM comments WHERE sourceid=1)) | | | Try : | | Insert into comments(list of you fields here ) | select 0,1,null,'testuser','testcomment',max(idx) from comments | where sourceid=1; | | but querying max(idx) is a bad idea. you could get the last inserted id | with the following command : | SELECT last_insert_id() from comments LIMIT 1; | | the last_insert_id is available on a per connection basis. | | but why are you inserting back in comments a field from comments ??? | ~ I want to store the parent comment to the comment being added, so I can ensure the comments are printed out in the correct order. ~ I will try putting in my list of fields, and see if that works any better. ~ I am writing a wiki, and I am trying to store the comments for each wiki into my database now. :) ~ Thank you for your response, btw. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB78nPikQgpVn8xrARAsiPAJ4kowIdeqUUgTyzn2oelHhBh/4khACeOaeQ 9IhVMUjrn38PPAnTAzLblKU= =A2Mi -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can a subquery be used in an insert
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Philippe Poelvoorde wrote: | | Insert into comments(list of you fields here ) | select 0,1,null,'testuser','testcomment',max(idx) from comments | where sourceid=1; | | but querying max(idx) is a bad idea. you could get the last inserted id | with the following command : | SELECT last_insert_id() from comments LIMIT 1; | | the last_insert_id is available on a per connection basis. | | but why are you inserting back in comments a field from comments ??? | ~ I looked in the archive for anything about SQL schema, but there were just two messages, and the original message had no thread with it. ~ I took your suggestion, and tried the following, but got the same error message: INSERT INTO comments (idx,sourceid,created,userid,comment,parentid) VALUES(0,1,NULL,'testuser','this is a test',(SELECT max(idx) FROM comments WHERE sourceid=1)) ~ The error is still: You can't specify target table 'comments' for update in FROM clause - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB8AopikQgpVn8xrARAjI2AJ4se/25M8AcUvtO9Q8GAFe62/aIUQCgjVOJ CPYRiuFlXtm+MXhytxo5YKc= =mWUq -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
