Re: Foreign Key with constant?
Hi jesse, You can only set the reference in your constraint: ... ADD FOREIGN KEY (id) references table(id) ... In this case you will get an inconsistent database. Dont reference the two tables Campers and Counselers with the ActivitySelections table, but use two tables in which you put the references: - CampersActivitySelections - CounselorsActivitySelections These tables will have the keys from Campers and Counselors and the ActivitySelections table. The matter is perhaps that you can have the same Activity for both Campers and Counselers, than you would have a problem in the old situation. As a db schema: Campers - CampersActivitySelections - ActivitySelections Counselors - CounselorsActivitySelections - ActivitySelections In your selection you can use the many-to-many tables to create your selection-output. You won't need a Type field in this case and your ActivitySelections holds the data pure for this perpose. Hope this small info will help you on your way :-) Best regards, Danny Jesse wrote: I need to be able to add a foreign key that will allow a constant is possible. I have a table named Campers that has a field named ID. I also have another table called Counselors that has a field named ID as well. There is a detailed table called ActivitySelections that I use for both Campers and Counselors. In ActivitySelections, the field PersonID holds the ID value from either Camper or Counselor, and I've got another field named Type. Type='C' where we're dealing with a Camper, and it holds 'O' where we're dealing with a Counselor. I tried the following, but got an error: ALTER TABLE activityselections DROP FOREIGN KEY FK_Campers, ADD CONSTRAINT FK_Campers FOREIGN KEY FK_Campers (PersonID, Type) REFERENCES campers (ID, 'C') ON DELETE CASCADE ON UPDATE CASCADE; Is what I'm trying to do possible, or do I need to go back to the drawing board, or do this manually? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key with constant?
I thought that might be the only solution to this problem, but didn't know if there was a way to keep it intact. I will split the tables up. Thanks, Jesse - Original Message - From: Danny Stolle [EMAIL PROTECTED] To: Jesse [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, December 27, 2005 6:27 AM Subject: Re: Foreign Key with constant? Hi jesse, You can only set the reference in your constraint: ... ADD FOREIGN KEY (id) references table(id) ... In this case you will get an inconsistent database. Dont reference the two tables Campers and Counselers with the ActivitySelections table, but use two tables in which you put the references: - CampersActivitySelections - CounselorsActivitySelections These tables will have the keys from Campers and Counselors and the ActivitySelections table. The matter is perhaps that you can have the same Activity for both Campers and Counselers, than you would have a problem in the old situation. As a db schema: Campers - CampersActivitySelections - ActivitySelections Counselors - CounselorsActivitySelections - ActivitySelections In your selection you can use the many-to-many tables to create your selection-output. You won't need a Type field in this case and your ActivitySelections holds the data pure for this perpose. Hope this small info will help you on your way :-) Best regards, Danny Jesse wrote: I need to be able to add a foreign key that will allow a constant is possible. I have a table named Campers that has a field named ID. I also have another table called Counselors that has a field named ID as well. There is a detailed table called ActivitySelections that I use for both Campers and Counselors. In ActivitySelections, the field PersonID holds the ID value from either Camper or Counselor, and I've got another field named Type. Type='C' where we're dealing with a Camper, and it holds 'O' where we're dealing with a Counselor. I tried the following, but got an error: ALTER TABLE activityselections DROP FOREIGN KEY FK_Campers, ADD CONSTRAINT FK_Campers FOREIGN KEY FK_Campers (PersonID, Type) REFERENCES campers (ID, 'C') ON DELETE CASCADE ON UPDATE CASCADE; Is what I'm trying to do possible, or do I need to go back to the drawing board, or do this manually? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
field truncate trying to using 'show slave status'
I'm using mysql 5.0.15 on windows 2000. I connect to database using MYDAC component, which seems to work well enough. I got a problem when I tried to load the resultset returned by a query like 'show slave status'. In this case some of the fields are truncated. As example the first field named 'Slave_IO_State' should have a value like 'Waiting for master to send event', while the field i got have only 'Waiting for ma'. If I use MysqlBrowser or yog demo , I get the whole string. Is this a limit of mysql ( I got the 5.0.15 wins revision ) or a MYDAC (rev 3.50) limit or I simply missed something else ? regards, Enzo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what about slave_skip_errors variables
Hi, I got some mysql server connectet with the replica enabled and from time to time it appen that the replicated data are still be inserted in the slave, so the replication server stop working due the duplicate entry error. For my purpose there is no matter to ignore these errors so I try to use the parameters --slave_skip_error=1062 in configuration my.ini file . I restart the server but the slave replication continue to stop for error, what is changed is that now didn't report the cause of the error. There is something wrong in all that and I don't known if it is a some of my mistake in the way I'm using this command or some other problem. I didn't known how to solve the problem, if someone can help, I'm just waiting regards, Ezno ** ** with slave-skip-errors = off ** ** 051227 16:57:43 [ERROR] Slave: Error 'Duplicate entry '7325' for key 1' on query. Default database: 'pmv_manager'. Query: 'INSERT INTO log_st ( IDROW, . , DT_MOD) values ( 7325, ..., current_timestamp )', Error_code: 1062 *** ** with slave-skip-errors = 1062 ** *** 051227 17:07:36 [Note] Slave SQL thread initialized, starting replication in log 'pmv_manager_log_bin.69' at position 743, relay log '.\enzo4-relay-bin.041541' position: 9336 051227 17:07:36 [Note] Slave SQL thread exiting, replication stopped in log 'pmv_manager_log_bin.69' at position 743 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: field truncate trying to using 'show slave status'
Hello. Is this a limit of mysql ( I got the 5.0.15 wins revision ) or a MYDAC (rev 3.50) limit or I simply missed something else ? I'm not a MYDAC (Delphi, C++ Builder) expert, but in my opinion, the problem is not in MySQL, because Query Browser and SQLyog use native MySQL C API and have no problems. MYDAC can work with MySQL in two ways - using MySQL client or native MySQL network protocol. Which of these two options are you using? AESYS S.p.A. [Enzo Arlati] wrote: I'm using mysql 5.0.15 on windows 2000. I connect to database using MYDAC component, which seems to work well enough. I got a problem when I tried to load the resultset returned by a query like 'show slave status'. In this case some of the fields are truncated. As example the first field named 'Slave_IO_State' should have a value like 'Waiting for master to send event', while the field i got have only 'Waiting for ma'. If I use MysqlBrowser or yog demo , I get the whole string. Is this a limit of mysql ( I got the 5.0.15 wins revision ) or a MYDAC (rev 3.50) limit or I simply missed something else ? regards, Enzo -- 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: 5.0.16. Bug in triggers?
Privet! This seems as a bug, especially because with InnoDB tables bulk insert works fine. You may add your comments at: http://bugs.mysql.com/bug.php?id=16021 Juri Shimon wrote: Hello mysql, When trigger on table uses select from same table, then bulk insert into this table cause error. How to repeat: create table t(i int not null, j int not null, n numeric(15,2), primary key(i,j)); create table s(i int not null, n numeric(15,2), primary key(i)); delimiter // create trigger t_ai after insert on t for each row begin declare sn numeric(15,2); select sum(n) into sn from t where i=new.i; replace into s values(new.i, sn); end// delimiter ;// insert into t values (1,1,10.00),(1,2,10.00),(1,3,10.00),(1,4,10.00),(1,5,10.00), (1,6,10.00),(1,7,10.00),(1,8,10.00),(1,9,10.00),(1,10,10.00), (1,11,10.00),(1,12,10.00),(1,13,10.00),(1,14,10.00),(1,15,10.00); ERROR 1030 (HY000) at line 12: Got error 124 from storage engine 1. Without select sum(n) into sn from t all OK. 2. insert into t values (1,1,10.00),(1,2,10.00),(1,3,10.00),(1,4,10.00),(1,5,10.00); - all OK. Is this a bug? -- 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]
Changing types on the fly in select queries?
I have a strange question for you all. I've inherated some code and the way the code works is that I can only mess with the WHERE part of a query. Therefore, I was wondering if something like this would be possible. WHERE where concat(year,period,week) as type int 2007031 Note that I'm trying to change the type of what the concat() is doing. Is this even possible? If so is it possible to do it in the WHERE? The reason why I think I need to do this is that 'period' is a char(2). I have to have the leading zero for every entry into the database so I can run my less than compare to it. Is there a better way of doing this than having the 'period' a char(2) type and trying to make whole concat() a type of int() on the fly? Thanks!
RE: Changing types on the fly in select queries?
Generally MySQL does format conversion for you to match data types. You can also force the CONCAT result to be integer by the following where concat(year,period,week) + 0 2007031 ^^^ -Original Message- From: Jay Paulson (CE CEN) [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 27, 2005 12:49 PM To: mysql@lists.mysql.com Subject: Changing types on the fly in select queries? I have a strange question for you all. I've inherated some code and the way the code works is that I can only mess with the WHERE part of a query. Therefore, I was wondering if something like this would be possible. WHERE where concat(year,period,week) as type int 2007031 Note that I'm trying to change the type of what the concat() is doing. Is this even possible? If so is it possible to do it in the WHERE? The reason why I think I need to do this is that 'period' is a char(2). I have to have the leading zero for every entry into the database so I can run my less than compare to it. Is there a better way of doing this than having the 'period' a char(2) type and trying to make whole concat() a type of int() on the fly? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing types on the fly in select queries?
At 12:48 -0600 12/27/05, Jay Paulson \(CE CEN\) wrote: I have a strange question for you all. I've inherated some code and the way the code works is that I can only mess with the WHERE part of a query. Therefore, I was wondering if something like this would be possible. WHERE where concat(year,period,week) as type int 2007031 Note that I'm trying to change the type of what the concat() is doing. Is this even possible? If so is it possible to do it in the WHERE? The reason why I think I need to do this is that 'period' is a char(2). I have to have the leading zero for every entry into the database so I can run my less than compare to it. Is there a better way of doing this than having the 'period' a char(2) type and trying to make whole concat() a type of int() on the fly? Thanks! You might be able to use the CAST() function. http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html -- 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: Changing types on the fly in select queries?
WHERE where concat(year,period,week) as type int 2007031 Note that I'm trying to change the type of what the concat() is doing. Is this even possible? If so is it possible to do it in the WHERE? You can cast data tyes explicitly: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html The reason why I think I need to do this is that 'period' is a char(2). I have to have the leading zero for every entry into the database so I can run my less than compare to it. Is there a better way of doing this than having the 'period' a char(2) type and trying to make whole concat() a type of int() on the fly? If this table is going to get large then you might find it too slow to use that method. Your query is logically the same as this (assuming integer columns): WHERE (year 2007) OR (year = 2007 AND period 3) OR (year = 2007 AND period = 3 AND week 1) ; Can't remember off the top of my head if that would be able to use an index on (year,period,week) though. Anyone? Good luck, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing types on the fly in select queries?
You can cast data tyes explicitly: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html Now that is cool. Too bad we are using MySQL 3.23.x on our production box. I can't use it. :( If this table is going to get large then you might find it too slow to use that method. Your query is logically the same as this (assuming integer columns): WHERE (year 2007) OR (year = 2007 AND period 3) OR (year = 2007 AND period = 3 AND week 1) ; I'm not quite sure if this will work. For the following reasons. - period is of type char(2) so I can have the leading zero for the concat(). Is it possible to compare a char() and an int() value? example '4' 2? - The values in the database for period are 01 through 13. The values of week are 1 through 4. The values of year is obvious. With the way the values are set I'm not sure if this WHERE you have will work on not. Guess I'll have to do some testing. Can't remember off the top of my head if that would be able to use an index on (year,period,week) though. Anyone? That would be intersting to see if that did work. Thanks for the help!
RE: Changing types on the fly in select queries?
You might be able to use the CAST() function. http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html Unfortunately our producation machine is using MySQL 3.23.x and from what I've found the cast() function isn't available. :( CAST() and CONVERT() are available as of MySQL 4.0.2.
comments on new db architecture
Our current database setup consists of 3 machines, each running MySQL 4; these three databases are more or less independent of each other, are roughly running on P3/1.5GHz boxes with 1GB of RAM each, and they all have RAID. For the most part, the setup works, but we are running into limits. For starters, the production-level work these databases do don't use much of the resources, but if we want to take backups (a dump via mysqladmin) or run manual statistical queries, machines slow down quite a bit. Secondly, we had a power outage a few days ago that corrupted a lot of tables. The RAID is primary suspect in this case. The tables were all successfully repaired, but we'd like to avoid a similar incident in the future. So we've been given the greenlight to upgrade these servers. Here is what we are thinking about doing. 1. Upgrade the machines to MySQL 5 2. For each machine, order a brand new box (with no RAID) that will become a master, and have the current db become its slave, i.e.: [new db0 (master)] --- [old db0 (slave)] [new db1 (master)] --- [old db1 (slave)] [new db2 (master)] --- [old db2 (slave)] Now, we can take backups and run whatever special queries we need on the slaves, and the masters for the most part will be untouched. Not running RAID on the new machines should also take care of table corruption in case of power loss. Given our needs as described above, is this the proper solution? Here is the only thing we are uncomfortable with: disks tend to go fairly frequently. If we don't use RAID on our masters, if a disk goes, and the slave is not synced up to it, we lose some data. How do you address this? (I guess you could use RAID 1...) Secondly, for a MySQL server, what is a decent machine spec? (Decent being mostly undefined, but lets say that it should be capable of handling some medium size website.) thanks, Thomas -- N.J. Thomas [EMAIL PROTECTED] Etiamsi occiderit me, in ipso sperabo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrading to mysql 5
hi, I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's not explained on mysql changes incompatibilities... for example: SELECT yabbse_topics.ID_LAST_MSG, yabbse_topics.ID_TOPIC, yabbse_topics.numReplies, yabbse_topics.locked, yabbse_messages.posterName, yabbse_messages.ID_MEMBER, IFNULL(mem.realName, yabbse_messages.posterName) AS posterDisplayName, yabbse_topics.numViews, yabbse_messages.posterTime, yabbse_messages.modifiedTime, yabbse_topics.ID_FIRST_MSG, yabbse_topics.isSticky, yabbse_topics.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead FROM yabbse_topics, yabbse_messages, yabbse_messages as m2 LEFT JOIN yabbse_members AS mem ON (mem.ID_MEMBER=yabbse_messages.ID_MEMBER) LEFT JOIN yabbse_members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER) LEFT JOIN yabbse_log_topics AS lt ON (lt.ID_TOPIC=yabbse_topics.ID_TOPIC AND lt.ID_MEMBER=2) LEFT JOIN yabbse_log_mark_read AS lmr ON (lmr.ID_BOARD=3 AND lmr.ID_MEMBER=2) WHERE yabbse_topics.ID_TOPIC IN (38562,39516,66603,24732,67085,59867,43884,68331,68330,56897,68090,68144,68174,68202,68329,68280,68258,68294,68299,68326,68325,67977,67286,55125,67897,65748,68316,68317,68152,68291,68187,68000,68240,68237,66796,68271,67486,57293,68027,67332,67990,68089,68072,42517,68315,63612,66121,68275,68032,68261,68308,67919,68305,68302,68300,68296,40983,68256,68293,68295,68270,68212,68198,45444,67246,68215,68264,68208,68133,67017) AND yabbse_messages.ID_MSG=yabbse_topics.ID_LAST_MSG AND m2.ID_MSG=yabbse_topics.ID_FIRST_MSG ORDER BY t.isSticky DESC, yabbse_messages.posterTime DESC ERROR: Unknown column 'yabbse_messages.ID_MEMBER' in 'on clause' show create table yabbse_topics; CREATE TABLE `yabbse_topics` ( `ID_TOPIC` int(11) NOT NULL auto_increment, `ID_BOARD` int(11) NOT NULL default '0', `ID_MEMBER_STARTED` int(11) NOT NULL default '0', `ID_MEMBER_UPDATED` int(11) NOT NULL default '0', `ID_FIRST_MSG` int(11) NOT NULL default '0', `ID_LAST_MSG` int(11) NOT NULL default '0', `ID_POLL` int(11) NOT NULL default '-1', `numReplies` int(11) NOT NULL default '0', `numViews` int(11) NOT NULL default '0', `locked` tinyint(4) NOT NULL default '0', `notifies` text, `isSticky` tinyint(4) NOT NULL default '0', PRIMARY KEY (`ID_TOPIC`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table yabbse_messages; CREATE TABLE `yabbse_messages` ( `ID_MSG` int(11) NOT NULL auto_increment, `ID_TOPIC` int(11) NOT NULL default '0', `ID_MEMBER` int(11) NOT NULL default '0', `subject` tinytext, `posterName` tinytext NOT NULL, `posterEmail` tinytext, `posterTime` bigint(20) default NULL, `posterIP` tinytext NOT NULL, `smiliesEnabled` tinyint(4) NOT NULL default '1', `modifiedTime` bigint(20) default NULL, `modifiedName` tinytext, `body` text, `icon` tinytext, `attachmentSize` mediumint(9) NOT NULL default '0', `attachmentFilename` tinytext, PRIMARY KEY (`ID_MSG`), KEY `ID_TOPIC` (`ID_TOPIC`), KEY `ID_MEMBER` (`ID_MEMBER`), KEY `posterTime` (`posterTime`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what about slave_skip_errors variables
Hello. 'pmv_manager_log_bin.69' at position 743 Find out with mysqlbinlog utility what statement causes the error. Then execute it manually on the slave to get an error code which it produces. What version of MySQL are you using? May be with the fresher one you will get more information from the error log. AESYS S.p.A. [Enzo Arlati] wrote: Hi, I got some mysql server connectet with the replica enabled and from time to time it appen that the replicated data are still be inserted in the slave, so the replication server stop working due the duplicate entry error. For my purpose there is no matter to ignore these errors so I try to use the parameters --slave_skip_error=1062 in configuration my.ini file . I restart the server but the slave replication continue to stop for error, what is changed is that now didn't report the cause of the error. There is something wrong in all that and I don't known if it is a some of my mistake in the way I'm using this command or some other problem. I didn't known how to solve the problem, if someone can help, I'm just waiting regards, Ezno ** ** with slave-skip-errors = off ** ** 051227 16:57:43 [ERROR] Slave: Error 'Duplicate entry '7325' for key 1' on query. Default database: 'pmv_manager'. Query: 'INSERT INTO log_st ( IDROW, . , DT_MOD) values ( 7325, ..., current_timestamp )', Error_code: 1062 *** ** with slave-skip-errors = 1062 ** *** 051227 17:07:36 [Note] Slave SQL thread initialized, starting replication in log 'pmv_manager_log_bin.69' at position 743, relay log '.\enzo4-relay-bin.041541' position: 9336 051227 17:07:36 [Note] Slave SQL thread exiting, replication stopped in log 'pmv_manager_log_bin.69' at position 743 -- 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: upgrading to mysql 5
PaginaDeSpud [EMAIL PROTECTED] wrote on 12/27/2005 03:33:58 PM: hi, I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's not explained on mysql changes incompatibilities... for example: snip FROM yabbse_topics, yabbse_messages, yabbse_messages as m2 LEFT JOIN yabbse_members AS mem ON (mem.ID_MEMBER=yabbse_messages.ID_MEMBER) LEFT JOIN yabbse_members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER) LEFT JOIN yabbse_log_topics AS lt ON (lt.ID_TOPIC=yabbse_topics.ID_TOPIC AND lt.ID_MEMBER=2) LEFT JOIN yabbse_log_mark_read AS lmr ON (lmr.ID_BOARD=3 AND lmr.ID_MEMBER=2) WHERE yabbse_topics.ID_TOPIC IN (38562,39516,66603,24732,67085,59867,43884,68331,68330,56897,68090, 68144,68174,68202,68329,68280,68258,68294,68299,68326,68325,67977, 67286,55125,67897,65748,68316,68317,68152,68291,68187,68000,68240, 68237,66796,68271,67486,57293,68027,67332,67990,68089,68072,42517, 68315,63612,66121,68275,68032,68261,68308,67919,68305,68302,68300, 68296,40983,68256,68293,68295,68270,68212,68198,45444,67246,68215, 68264,68208,68133,67017) AND yabbse_messages.ID_MSG=yabbse_topics.ID_LAST_MSG AND m2.ID_MSG=yabbse_topics.ID_FIRST_MSG ORDER BY t.isSticky DESC, yabbse_messages.posterTime DESC ERROR: Unknown column 'yabbse_messages.ID_MEMBER' in 'on clause' snip I don't know which incompatible change list you looked at but it is definitely on the top of this page: http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html complete with a link to more information: http://dev.mysql.com/doc/refman/5.0/en/join.html Once you read over that, if you can't find the flaw in your query come back to the list and I or someone else will help point it out to you. Your query has always been broken, it's just that some of the bugs in the SQL engine were eliminated with 5.0.12 so that it evaluates SQL statements more according to the specification and your query cannot be evaluated according to the updated rules. It's like having a bad spell-checker in a word processing appliction. You could go for months using one spelling of a word and never get flagged for it. However if you upgraded the spell-checker it may start flagging you on the same word that used to pass muster in the old version. This is a good thing because as your query becomes more ANSI compliant, it becomes more likely to be used cross-platform and not just on MySQL. As an observation: unless you are using the comma-separated form of creating an implicit CROSS JOIN, you don't get caught by the tightening of the rules. Nobody has posted a question of this same nature to the list that only uses explicit JOIN statements. hmmm. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: upgrading to mysql 5
I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's not explained on mysql changes incompatibilities... It is: see the first change item, marked 'incompatible change', at http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html. We can no longer get away with syntactically loose (SQL2003-incompatible) combinations of commas and JOIN clauses. PB PaginaDeSpud wrote: hi, I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's not explained on mysql changes incompatibilities... for example: SELECT yabbse_topics.ID_LAST_MSG, yabbse_topics.ID_TOPIC, yabbse_topics.numReplies, yabbse_topics.locked, yabbse_messages.posterName, yabbse_messages.ID_MEMBER, IFNULL(mem.realName, yabbse_messages.posterName) AS posterDisplayName, yabbse_topics.numViews, yabbse_messages.posterTime, yabbse_messages.modifiedTime, yabbse_topics.ID_FIRST_MSG, yabbse_topics.isSticky, yabbse_topics.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead FROM yabbse_topics, yabbse_messages, yabbse_messages as m2 LEFT JOIN yabbse_members AS mem ON (mem.ID_MEMBER=yabbse_messages.ID_MEMBER) LEFT JOIN yabbse_members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER) LEFT JOIN yabbse_log_topics AS lt ON (lt.ID_TOPIC=yabbse_topics.ID_TOPIC AND lt.ID_MEMBER=2) LEFT JOIN yabbse_log_mark_read AS lmr ON (lmr.ID_BOARD=3 AND lmr.ID_MEMBER=2) WHERE yabbse_topics.ID_TOPIC IN (38562,39516,66603,24732,67085,59867,43884,68331,68330,56897,68090,68144,68174,68202,68329,68280,68258,68294,68299,68326,68325,67977,67286,55125,67897,65748,68316,68317,68152,68291,68187,68000,68240,68237,66796,68271,67486,57293,68027,67332,67990,68089,68072,42517,68315,63612,66121,68275,68032,68261,68308,67919,68305,68302,68300,68296,40983,68256,68293,68295,68270,68212,68198,45444,67246,68215,68264,68208,68133,67017) AND yabbse_messages.ID_MSG=yabbse_topics.ID_LAST_MSG AND m2.ID_MSG=yabbse_topics.ID_FIRST_MSG ORDER BY t.isSticky DESC, yabbse_messages.posterTime DESC ERROR: Unknown column 'yabbse_messages.ID_MEMBER' in 'on clause' show create table yabbse_topics; CREATE TABLE `yabbse_topics` ( `ID_TOPIC` int(11) NOT NULL auto_increment, `ID_BOARD` int(11) NOT NULL default '0', `ID_MEMBER_STARTED` int(11) NOT NULL default '0', `ID_MEMBER_UPDATED` int(11) NOT NULL default '0', `ID_FIRST_MSG` int(11) NOT NULL default '0', `ID_LAST_MSG` int(11) NOT NULL default '0', `ID_POLL` int(11) NOT NULL default '-1', `numReplies` int(11) NOT NULL default '0', `numViews` int(11) NOT NULL default '0', `locked` tinyint(4) NOT NULL default '0', `notifies` text, `isSticky` tinyint(4) NOT NULL default '0', PRIMARY KEY (`ID_TOPIC`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table yabbse_messages; CREATE TABLE `yabbse_messages` ( `ID_MSG` int(11) NOT NULL auto_increment, `ID_TOPIC` int(11) NOT NULL default '0', `ID_MEMBER` int(11) NOT NULL default '0', `subject` tinytext, `posterName` tinytext NOT NULL, `posterEmail` tinytext, `posterTime` bigint(20) default NULL, `posterIP` tinytext NOT NULL, `smiliesEnabled` tinyint(4) NOT NULL default '1', `modifiedTime` bigint(20) default NULL, `modifiedName` tinytext, `body` text, `icon` tinytext, `attachmentSize` mediumint(9) NOT NULL default '0', `attachmentFilename` tinytext, PRIMARY KEY (`ID_MSG`), KEY `ID_TOPIC` (`ID_TOPIC`), KEY `ID_MEMBER` (`ID_MEMBER`), KEY `posterTime` (`posterTime`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.7/214 - Release Date: 12/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need Help Writing a Trigger
I'm trying to write a trigger that will update the age of a camper when ever a record is updated or inserted. I have a table named Campers which contains basic information about the camper as well as their birthday. I have another table named Config which holds various settings, including the date at which camp begins. The Age field in the Campers table needs to be set based on the Config.CampStartDate. So, I have the following query that does what I need: SELECT c.lastname,c.firstname,c.birthday, (Year(co.CampStartDate)-Year(c.Birthday)) - (Right(co.CampStartDate,5)Right(c.Birthday,5)) As Age from campers c, config co (there is always only one record in config). There are actually a few options here: 1. Create a trigger that updates this field when new records are inserted or updated. This will keep the field updated. 2. Everywhere in my code where I need the age, I insert the calculation above to include an age column. 3. I write a view that includes this calculation in it and just use that everywhere. However, I don't know if there are any performance issues with Views or anything. First, what is the best approach here? Also, if I can get the trigger to work, how do I write it? I've gotten this far with it: CREATE TRIGGER insUpdAge BEFORE INSERT ON Campers FOR EACH ROW BEGIN // somehow i need to get the CampStartDate out of the Config table, and store it in a variable (I think). // next, I need to do an set new.age=(calculated age). END Can anyone give me a hand here? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem With FulltText Index and VarChar
I am trying to do a fulltext search with a multi-field index using MySQL 4.1.15. When I create a full text index of my Comments field which is of type TEXT. I can do a fulltext search fine. But when I add another field (like a varchar or even Text) to that index or change the name of the index I get and error like: SELECT * FROM properties WHERE MATCH (CommentsIDX) AGAINST ( item1 OR item2 AND (item3 AND item4 NOT (item5 OR item6)) WITH QUERY EXPANSION ) MySQL said: Documentation #1054 - Unknown column 'CommentsIDX' in 'where clause' My table structure contains:FULLTEXT KEY `CommentsIDX` (`Comments`) Any ideas? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Writing a Trigger
On Tuesday 27 December 2005 2:34 pm, Jesse wrote: I'm trying to write a trigger that will update the age of a camper when ever a record is updated or inserted. I have a table named Campers which contains basic information about the camper as well as their birthday. I have another table named Config which holds various settings, including the date at which camp begins. The Age field in the Campers table needs to be set based on the Config.CampStartDate. So, I have the following query that does what I need: It seems to me that you're storing redundant data. If you know their birthday, than you know their age, just subtract the birthday from today's date and you have it. -- Dr. Joseph Dolan: Isn't there a children's book about an elephant named Babar? Fletch: I don't know. I don't have any. Dr. Joseph Dolan: No children? Fletch: No, elephant books. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best case-insensitive search for Character Set utf8 COLLATE utf8_bin Columns.
What is the best (most optimal) way to perform a case-insensitive search
for a VARCHAR column with COLLATE utf8_bin?
I'm assuming the answer is not:
SELECT *
FROM MyTable
WHERE UPPER(MyColumn) LIKE Upper('%pattern%');
Tia!
R.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading to 5.0.15
Howdy all, I've got a MySQL 4.0.24 database that I'd like to upgrade to 5.0.15. Is it possible to backup the tablespace file in the mysql data directory and then install the new database then move the backed up files into the new installations data directory? Are we stuck running a mysqldump script? That would take hours and we'd like to avoid it if at all possible. If not we'll deal with it but it would be nice to just move the files. Thanks, Tripp __ Yahoo! for Good - Make a difference this year. http://brand.yahoo.com/cybergivingweek2005/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Got error 12 from storage engine on ORDER BY
Hello all, I'm running into an error which seems to be fairly uncommon. Hopefully somebody can give me some insight! In the monthly run for my accounting software, I am doing a fairly heavy query with a few joins, some date arithmetic, a 'having' condition and finally an 'order by'. When mysqld has been running for some time (a day or so), this query produces the following error: ERROR 1030 (HY000): Got error 12 from storage engine Strangely, this problem does not appear when mysqld is restarted and I retry the query. When I wait a day, the error starts to appear again. I've already found that by removing the final order by from the query, the error does not occur. The result is 1107 rows by 24 columns (a few varchars and the rest is all integer). The whole result fits in the querycache, it's not unusually large. The server is running 4.1.10a on FreeBSD 5.4-RELEASE-p6. It has 2GB physical RAM of which 1.2GB is available (free + buffers) to mysqld at almost all times. mysqld's size is 516MB. There's 1GB free on the drive used for filesorts. I've guessed setting max_heap_table_size=256M but this did not help. Because I can only test one variable change per day, I will be quickly running out of options before january 1st :) Any ideas would be appreciated! Kind regards, Walter Hop Transip BV -- Transip BV | http://www.transip.nl/ Hoogwaardige Innovatie | Aangename Zekerheid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading to mysql 5
I only saw this changes: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html I've rebuild this query according to the new sql join sintax and works fine. Thanks a lot because i don't know how many days were spent to solve this without your help :) Ivan Lopez. Logosur. - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: PaginaDeSpud [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, December 27, 2005 10:29 PM Subject: Re: upgrading to mysql 5 /I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's not explained on mysql changes incompatibilities... / It is: see the first change item, marked 'incompatible change', at http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html. We can no longer get away with syntactically loose (SQL2003-incompatible) combinations of commas and JOIN clauses. PB PaginaDeSpud wrote: hi, I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's not explained on mysql changes incompatibilities... for example: SELECT yabbse_topics.ID_LAST_MSG, yabbse_topics.ID_TOPIC, yabbse_topics.numReplies, yabbse_topics.locked, yabbse_messages.posterName, yabbse_messages.ID_MEMBER, IFNULL(mem.realName, yabbse_messages.posterName) AS posterDisplayName, yabbse_topics.numViews, yabbse_messages.posterTime, yabbse_messages.modifiedTime, yabbse_topics.ID_FIRST_MSG, yabbse_topics.isSticky, yabbse_topics.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead FROM yabbse_topics, yabbse_messages, yabbse_messages as m2 LEFT JOIN yabbse_members AS mem ON (mem.ID_MEMBER=yabbse_messages.ID_MEMBER) LEFT JOIN yabbse_members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER) LEFT JOIN yabbse_log_topics AS lt ON (lt.ID_TOPIC=yabbse_topics.ID_TOPIC AND lt.ID_MEMBER=2) LEFT JOIN yabbse_log_mark_read AS lmr ON (lmr.ID_BOARD=3 AND lmr.ID_MEMBER=2) WHERE yabbse_topics.ID_TOPIC IN (38562,39516,66603,24732,67085,59867,43884,68331,68330,56897,68090,68144,68174,68202,68329,68280,68258,68294,68299,68326,68325,67977,67286,55125,67897,65748,68316,68317,68152,68291,68187,68000,68240,68237,66796,68271,67486,57293,68027,67332,67990,68089,68072,42517,68315,63612,66121,68275,68032,68261,68308,67919,68305,68302,68300,68296,40983,68256,68293,68295,68270,68212,68198,45444,67246,68215,68264,68208,68133,67017) AND yabbse_messages.ID_MSG=yabbse_topics.ID_LAST_MSG AND m2.ID_MSG=yabbse_topics.ID_FIRST_MSG ORDER BY t.isSticky DESC, yabbse_messages.posterTime DESC ERROR: Unknown column 'yabbse_messages.ID_MEMBER' in 'on clause' show create table yabbse_topics; CREATE TABLE `yabbse_topics` ( `ID_TOPIC` int(11) NOT NULL auto_increment, `ID_BOARD` int(11) NOT NULL default '0', `ID_MEMBER_STARTED` int(11) NOT NULL default '0', `ID_MEMBER_UPDATED` int(11) NOT NULL default '0', `ID_FIRST_MSG` int(11) NOT NULL default '0', `ID_LAST_MSG` int(11) NOT NULL default '0', `ID_POLL` int(11) NOT NULL default '-1', `numReplies` int(11) NOT NULL default '0', `numViews` int(11) NOT NULL default '0', `locked` tinyint(4) NOT NULL default '0', `notifies` text, `isSticky` tinyint(4) NOT NULL default '0', PRIMARY KEY (`ID_TOPIC`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table yabbse_messages; CREATE TABLE `yabbse_messages` ( `ID_MSG` int(11) NOT NULL auto_increment, `ID_TOPIC` int(11) NOT NULL default '0', `ID_MEMBER` int(11) NOT NULL default '0', `subject` tinytext, `posterName` tinytext NOT NULL, `posterEmail` tinytext, `posterTime` bigint(20) default NULL, `posterIP` tinytext NOT NULL, `smiliesEnabled` tinyint(4) NOT NULL default '1', `modifiedTime` bigint(20) default NULL, `modifiedName` tinytext, `body` text, `icon` tinytext, `attachmentSize` mediumint(9) NOT NULL default '0', `attachmentFilename` tinytext, PRIMARY KEY (`ID_MSG`), KEY `ID_TOPIC` (`ID_TOPIC`), KEY `ID_MEMBER` (`ID_MEMBER`), KEY `posterTime` (`posterTime`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.7/214 - Release Date: 12/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Grand summary
I have two tables, order and order items. An order can have 1 or more order items. Important data in order is the `id` which is the key to link that order to the order_items table. Quantity of items is stored in the order_items table. I need to select the below data, and would like to do it in one select, and not use server side code to add it all up: Total Number of Orders (count order records) Total Products $ (sum of qty and price in order_items) Total Shipping $ (sum of shipping field in order record) Total Gift Card $ (sum of gift card price in order record) Total Tax $ (sum of sales tax in order record) Total Orders: $ (grand summary of total in order record) -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grand summary
on 12/27/05 6:25 PM, Scott Haneda at [EMAIL PROTECTED] wrote: I have two tables, order and order items. An order can have 1 or more order items. Important data in order is the `id` which is the key to link that order to the order_items table. Quantity of items is stored in the order_items table. I need to select the below data, and would like to do it in one select, and not use server side code to add it all up: Total Number of Orders (count order records) Total Products $ (sum of qty and price in order_items) Total Shipping $ (sum of shipping field in order record) Total Gift Card $ (sum of gift card price in order record) Total Tax $ (sum of sales tax in order record) Total Orders: $ (grand summary of total in order record) Sorry for the noise, turns out I did not need this at all, and can just use sum() and no need for the join at all. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help with user variables in where clause of sub query
Hi there i am trying to use usewr variables in a select statement to add to a where clause in a sub query. Ie select @id:=id,@month:=month, (select SUM(totals) from table where [EMAIL PROTECTED] and [EMAIL PROTECTED]) as totals from table its happened on other occasions ie with calculations and sums, whats happened in mysql5 ? It used to work in mysql4 , something i am doing is wrong ? Please let me know thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with user variables in where clause of sub query
Dan Rossi [EMAIL PROTECTED] wrote on 12/27/2005 11:39:57 PM: Hi there i am trying to use usewr variables in a select statement to add to a where clause in a sub query. Ie select @id:=id,@month:=month, (select SUM(totals) from table where [EMAIL PROTECTED] and [EMAIL PROTECTED]) as totals from table its happened on other occasions ie with calculations and sums, whats happened in mysql5 ? It used to work in mysql4 , something i am doing is wrong ? Please let me know thanks. Is there a great reason why you are using a subquery? I could rewrite this to avoid the subquery and probably eliminate your particular problem: SELECT id, month, sum(totals) totals FROM TABLE group by id, month; Unless(!) you oversimplified your original example. In which case, you should post your actual query and I can give you a better response. Technically, the values of the variables should not be determined until AFTER the row is processed which means that you shouldn't be able to use them for your subquery (at least that's how I remember the SQL:2003 spec but it's late and I could very well be wrong in my recollection) Personally, I am not that big a fan of subqueries anyway. There are a few types of queries where they make the SQL to achieve a result rather compact and elegant. However, I have never seen a subquery actually outperform a properly constructed linear query. They sometimes match linear performance but most often perform worse to much worse. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: need help with user variables in where clause of sub query
I have an unfinished query, i am trying to test, basically im required to get the value of the current field in a row and use it for a subquery in that row :| Its not a working query, and im not asking for someone to fix it, however as u can see i need to send the customerID and month to the sub query. What its actually trying to do is tedious to explain, but i have two tables of media usage for a customer, the current month will be in the usage table, so that if the plan changes in that month so does the totals, but for the previous months there is a static month_totals table showing just the totals recorded. So im needing to select two different tables depending on what month is being selected. I hope this helps. SELECT SQL_CACHE CASE WHEN MONTH(mt.month) = MONTH(NOW()) THEN CREATE VIEW current_month AS SELECT @customerID:=c.customerID, @month:=fu.month AS month, DATE_FORMAT(fu.month,'%M') AS month_long, @total_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] AND fu.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3))), @percentage:=((@feed_bandwidth/@total_bandwidth)) AS percentage, @month_totals:=(SELECT month_total FROM month_totals WHERE [EMAIL PROTECTED] AND DATE_FORMAT(month,%m)[EMAIL PROTECTED] GROUP BY month, customerID) AS month_totals, @count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] AND feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3))), ROUND(( IF(c.bandwidth_limit=0, ((c.monthly_price/@count)*f.percentage_paid)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*f.percentage_paid) )),0) AS providers_cut FROM feed_usage fu INNER JOIN customers c ON fu.customerID=c.customerID INNER JOIN feeds f ON fu.feedID=f.feedID WHERE f.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3)) GROUP BY fu.month,fu.customerID ORDER BY fu.month DESC ELSE CREATE VIEW previous_months AS SELECT @customerID:=c.customerID, @month:=MONTH(mt.month) AS month, DATE_FORMAT(mt.month,'%M') AS month_long, @feeds:=feeds, @total_bandwidth:=( SELECT bandwidth FROM month_totals WHERE [EMAIL PROTECTED] AND MONTH(month)[EMAIL PROTECTED]), @feed_bandwidth:=( SELECT bandwidth FROM month_totals INNER JOIN WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] AND fu.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3))), @percentage:=((@feed_bandwidth/@total_bandwidth)) AS percentage, @month_totals:=(SELECT month_total FROM month_totals WHERE [EMAIL PROTECTED] AND DATE_FORMAT(month,%m)[EMAIL PROTECTED] GROUP BY month, customerID) AS month_totals, @count:=(SELECT count(*) FROM feeds WHERE feedID IN (@feeds)), @feed_count:=(SELECT count(*) FROM month_totals WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] AND feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3))), ROUND(( IF(c.bandwidth_limit=0, ((c.monthly_price/@count)*f.percentage_paid)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*f.percentage_paid) )),0) AS providers_cut FROM feed_usage fu INNER JOIN customers c ON fu.customerID=c.customerID INNER JOIN feeds f ON fu.feedID=f.feedID WHERE f.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3)) GROUP BY fu.month,fu.customerID ORDER BY fu.month DESC END On 28/12/2005, at 4:47 PM, [EMAIL PROTECTED] wrote: Dan Rossi [EMAIL PROTECTED] wrote on 12/27/2005 11:39:57 PM: Hi there i am trying to use usewr variables in a select statement to add to a where clause in a sub query. Ie select @id:=id,@month:=month, (select SUM(totals) from table where [EMAIL PROTECTED] and [EMAIL PROTECTED]) as totals from table its happened on other occasions ie with calculations and sums, whats happened in mysql5 ? It used to work in mysql4 , something i am doing is wrong ? Please let me know thanks. Is there a great reason why you are using a subquery? I could rewrite this to avoid the subquery and probably eliminate your particular problem: SELECT id, month, sum(totals) totals FROM TABLE group by id, month; Unless(!) you oversimplified your original example. In which case, you should post your actual query and I can give you a better response. Technically, the values of the variables should not be determined until AFTER the row is processed which means that you shouldn't be able to use them for your subquery (at least that's how I remember the SQL:2003 spec but it's late and I could very well be wrong in my recollection) Personally, I am not that big a fan of subqueries anyway. There are a few types of queries where they make the SQL to achieve a result rather compact and elegant. However, I have never seen a subquery actually outperform a properly constructed linear query. They sometimes match
how to use sql security
User_one create a procdure named proc with option sql security invoker. User_two only have execute privilege in the database that the proc in. But the User_two still call the proc. What effect do the sql security option producing?
Re: how to use sql security
At 15:17 +0800 12/28/05, wangxu wrote: User_one create a procdure named proc with option sql security invoker. User_two only have execute privilege in the database that the proc in. But the User_two still call the proc. What effect do the sql security option producing? proc runs with the invoker's privileges, that is, User_two's privileges. -- 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]
Problem with FullText Indexes
I am trying to do a fulltext search with a multi-field index using MySQL 4.1.15. When I create a full text index of my Comments field which is of type TEXT. I can do a fulltext search fine. But when I add another field (like a varchar or even Text) to that index or change the name of the index I get an error like: SELECT * FROM properties WHERE MATCH (CommentsIDX) AGAINST ( item1 OR item2 AND (item3 AND item4 NOT (item5 OR item6)) WITH QUERY EXPANSION ) MySQL said: Documentation #1054 - Unknown column 'CommentsIDX' in 'where clause' My table structure contains:FULLTEXT KEY `CommentsIDX` (`Comments`) Any ideas? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use sql security
How sql security option restrict privileges? - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: wangxu [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Wednesday, December 28, 2005 3:21 PM Subject:Re: how to use sql security At 15:17 +0800 12/28/05, wangxu wrote: User_one create a procdure named proc with option sql security invoker. User_two only have execute privilege in the database that the proc in. But the User_two still call the proc. What effect do the sql security option producing? proc runs with the invoker's privileges, that is, User_two's privileges. -- 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]
