Re: Add foreign key

And here are the matching drop and create statements: ALTER TABLE `mytable` DROP FOREIGN KEY `fk_mytable_id`; ALTER TABLE `mytable` ADD CONSTRAINT `fk_mytable_id` FOREIGN KEY ( `id` ) REFERENCES `anothertable` ( `id` ) ON UPDATE CASCADE ON DELETE RESTRICT; I don't know what I was doing wro

Create funtion UDF on MYSQL

Hi! can you hep? when create funtion UDF on linux then it have error ERROR 1126 Can't opened shared library (error: 0 feature disabled) i do'nt want use it ,if you knowm this problem,please help me. i hope to receive your mail thank for read it. _

automated character set conversions for tables

Hello fellow MySQL friends, I recently had the "fun" to convert some 60 tables within a DB f.e. from latin1 to UTF8. I did a DB conversion before that with "ALTER DATABASE db_name CHARACTER SET utf8" That worked wonderfully, except not as expected. ;-) It basically converted only the datab

Re: Enum issue

Hi, Try with, , `consent` enum ('','Y','N','P') , .mysql> desc table; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ . | consent | enum

Re: Add foreign key

Just found this added the name: ALTER TABLE `mytable` ADD FOREIGN KEY `fk_mytable_id` ( `id` ) REFERENCES `othertable` ( `id` ); I thought I had tried that - obviously not. -- View this message in context: http://www.nabble.com/Add-foreign-key-tf2950373.html#a8251427 Sent from the MySQL - Ge

Add foreign key

Still on the top of foreign keys :) I have this statement: ALTER TABLE `mytable` ADD FOREIGN KEY ( `id` ) REFERENCES `othertable` ( `id` ); Is there a way I can give this key a specific name (eg 'FK_mytable_id') and specific the type of reference (eg 'ON UPDATE CASCADE')? -- View this mess

Re: Date v. DateTime index performance

Anders Lundgren wrote: > One potential solution might be to use an extra column that tracks > month_number, and populate it with a trigger on insert or update. > Index that field and then use it in your WHERE clause. One > possibility anyway. Resulting question, what if I have three colums

mysql - connections question

hi shawn... mysql permits you to set/modify the number of simultaneous connections via the my.cnf file my assumption is that this is for all the databases for a mysql instance. is this correct? also, what issues might one run into if you have multiple copies of mysql running on a single server?

Re: Date v. DateTime index performance

> One potential solution might be to use an extra column that tracks > month_number, and populate it with a trigger on insert or update. > Index that field and then use it in your WHERE clause. One > possibility anyway. Resulting question, what if I have three colums named year_number, month_nu

Extracting transactional data from InnoDB log files

Hello, Is it possible to extract transactional data from InnoDB log files? InnoDB kept crashing and trying to insert the same record (replayed from the log after the crash I assume). I'd like to try and extract the record from log to reconstruct the query and try to break it again in case it caus

Re: Choose a random record from a list of duplicates

> zv Green wrote: >> Hello all, >> >> What I want to do is select all the records from the table but where >> there >> are duplicate entries (based on say, the surname and postcode fields) >> pick a random record and then ignore the rest. > > If you want to pick a random record, you can do ORDER

Re: Choose a random record from a list of duplicates

zv Green wrote: Hello all, What I want to do is select all the records from the table but where there are duplicate entries (based on say, the surname and postcode fields) pick a random record and then ignore the rest. If you want to pick a random record, you can do ORDER BY RANDOM LIMIT 1

Choose a random record from a list of duplicates

Hello all, I'm having real problems trying to work this one out. Basically I have a big table full of names, addresses and other such information. What I want to do is select all the records from the table but where there are duplicate entries (based on say, the surname and postcode fields) pick

JOIN condition v.s. WHERE condition

Hi all, If my post is irrelevant to the list, please let me know. I have conditions (REGEXP 'STRING' or ='STRING') as JOIN condition or as WHERE clause condition, i wonder for a 10 table LEFT/INNER JOIN query, which approach is better and why. Thanks, ran i.e. "SELECT

Enum issue

Hi All If I have a column `consent` enum('Y','N','P') default NULL, And I try to insert 'NULL' I get this error: Warning: Data truncated for column 'consent' at row 1 What is the problem there? What I am doing is moving data from one table to another with a python script so I have to assign 'N

Porting MySQL 5.0.x and 5.1.x on Linux IA64: Compiling options and errors

Dear All, I am trying to compile MySQL 5.0.27 and 5.1.14 on a shared memory processors system with 128 processors IA-64 Itanium-2. I have downloaded the binary version of MySQL-max 5.0.27 Linux IA64 and I have installed and configured it with no problems. I need to install also the MySQ

Re: store spatial data in mysql

Hi, LOAD DATA INFILE is capable of loading only the internal representation of the data. Hence load the Well-Known Text (WKT) representation as a text column using LOAD DATA INFILE into a text column and then shift the data geometry column using an UPDATE. Hope this will do.. Thanks ViSol

Re: Slow query

Hello, I pinned down the problem to the order by line. If i leave this away the query is done in 0.05 seconds. - Mike Mike van Hoof schreef: Hello, i have the following query: SELECT DISTINCT ( Waarde ) AS bestemming FROM xml_kenmerk WHERE Omschrijving = 'Bestemming' AND IF

Slow query

Hello, i have the following query: SELECT DISTINCT ( Waarde ) AS bestemming FROM xml_kenmerk WHERE Omschrijving = 'Bestemming' AND IF ( DatumBegin IS NOT NULL AND DatumBegin != '-00-00', DatumBegin <= CURDATE( ) , 1 =1 ) AND IF ( DatumEind IS NOT NULL AN