Re: Foreign Key with constant?

2005-12-27 Thread Danny Stolle
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

Re: Foreign Key with constant?

2005-12-27 Thread Jesse
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,

field truncate trying to using 'show slave status'

2005-12-27 Thread AESYS S.p.A. [Enzo Arlati]
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

what about slave_skip_errors variables

2005-12-27 Thread AESYS S.p.A. [Enzo Arlati]
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

Re: field truncate trying to using 'show slave status'

2005-12-27 Thread Gleb Paharenko
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

Re: 5.0.16. Bug in triggers?

2005-12-27 Thread Gleb Paharenko
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

Changing types on the fly in select queries?

2005-12-27 Thread Jay Paulson \(CE CEN\)
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

RE: Changing types on the fly in select queries?

2005-12-27 Thread Gordon Bruce
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]

Re: Changing types on the fly in select queries?

2005-12-27 Thread Paul DuBois
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

RE: Changing types on the fly in select queries?

2005-12-27 Thread James Harvard
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

RE: Changing types on the fly in select queries?

2005-12-27 Thread Jay Paulson \(CE CEN\)
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

RE: Changing types on the fly in select queries?

2005-12-27 Thread Jay Paulson \(CE CEN\)
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

2005-12-27 Thread N.J. Thomas
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

upgrading to mysql 5

2005-12-27 Thread PaginaDeSpud
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,

Re: what about slave_skip_errors variables

2005-12-27 Thread Gleb Paharenko
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

Re: upgrading to mysql 5

2005-12-27 Thread SGreen
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

Re: upgrading to mysql 5

2005-12-27 Thread Peter Brawley
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

Need Help Writing a Trigger

2005-12-27 Thread Jesse
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

Problem With FulltText Index and VarChar

2005-12-27 Thread Michael Stearne
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

Re: Need Help Writing a Trigger

2005-12-27 Thread John Meyer
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

Best case-insensitive search for Character Set utf8 COLLATE utf8_bin Columns.

2005-12-27 Thread Robert DiFalco
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

Upgrading to 5.0.15

2005-12-27 Thread Tripp Bishop
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

Got error 12 from storage engine on ORDER BY

2005-12-27 Thread Walter Hop
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'.

Re: upgrading to mysql 5

2005-12-27 Thread PaginaDeSpud
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. -

Grand summary

2005-12-27 Thread Scott Haneda
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

Re: Grand summary

2005-12-27 Thread Scott Haneda
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

need help with user variables in where clause of sub query

2005-12-27 Thread Dan Rossi
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

Re: need help with user variables in where clause of sub query

2005-12-27 Thread SGreen
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

Re: need help with user variables in where clause of sub query

2005-12-27 Thread Dan Rossi
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

how to use sql security

2005-12-27 Thread wangxu
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

2005-12-27 Thread Paul DuBois
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

Problem with FullText Indexes

2005-12-27 Thread Michael Stearne
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

Re: how to use sql security

2005-12-27 Thread wangxu
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