InnoDB problems under 5.1.9
Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB problems under 5.1.9
Hi Ben, Try doing SHOW ENGINES; and see what it says. It should say InnoDB is supported, if not then it hasn't compiled in. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: Tuesday, 23 May 2006 4:57 PM To: mysql@lists.mysql.com Subject: InnoDB problems under 5.1.9 Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
String functions FORMAT(X,D)
Hello MySQL Users Is there a way to change the separator in the following example, e.g. from , to '? SELECT FORMAT(12332.123456, 4); - '12,332.1235' is this the easiest way? SELECT REPLACE(FORMAT(12332.123456, 4), ,, '); - '12'332.1235' or can this be set somewhere? Thank you! Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problems under 5.1.9
Logan, David (SST - Adelaide) wrote: Hi Ben, Try doing SHOW ENGINES; Here: (pertinent cols only) ++--+--+-++ | Engine | Support | Transactions | XA | Savepoints | ++--+--+-++ | CSV| YES | NO | NO | NO | | MEMORY | YES | NO | NO | NO | | MRG_MYISAM | YES | NO | NO | NO | | InnoDB | DISABLED | YES | YES | YES| | BLACKHOLE | YES | NO | NO | NO | | MyISAM | DEFAULT | NO | NO | NO | | BerkeleyDB | DISABLED | YES | NO | YES| | ARCHIVE| YES | NO | NO | NO | ++--+--+-++ Ok, now I believe this does mean I have compiled the InnoDB. Just to save me trawling though the manual, can you tell me how I should enable InnoDB? Thanks for the info! Ben and see what it says. It should say InnoDB is supported, if not then it hasn't compiled in. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: Tuesday, 23 May 2006 4:57 PM To: mysql@lists.mysql.com Subject: InnoDB problems under 5.1.9 Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB problems under 5.1.9
Hi Ben,
I thought the InnoDB engine was included without having to set a
./configure option. On my latest build (admittedly a 5.0.18 one) InnoDB
was enabled without setting any ./configure option.
My options were
hambone /usr/dev/src/mysql-5.0.18 $ cat ../../build_scripts/mysql-5.0.18
#!/bin/bash
./configure \
--prefix=/usr/local/mysql-5.0.18 \
--enable-thread-safe-client \
--with-unix-socket-path=/tmp \
--with-openssl \
--with-example-storage-engine \
--with-archive-storage-engine \
--with-csv-storage-engine \
--with-blackhole-storage-engine \
--with-ndbcluster \
--with-ndb-test \
--with-ndb-port=3510 \
--with-ndb-port-base=3710 \
--with-federated-storage-engine
This worked fine. If I get time later, I may give it a punt with your
version. Sorry I can't be of more help.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Ben Clewett [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 23 May 2006 5:50 PM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: Re: InnoDB problems under 5.1.9
Logan, David (SST - Adelaide) wrote:
Hi Ben,
Try doing
SHOW ENGINES;
Here: (pertinent cols only)
++--+--+-++
| Engine | Support | Transactions | XA | Savepoints |
++--+--+-++
| CSV| YES | NO | NO | NO |
| MEMORY | YES | NO | NO | NO |
| MRG_MYISAM | YES | NO | NO | NO |
| InnoDB | DISABLED | YES | YES | YES|
| BLACKHOLE | YES | NO | NO | NO |
| MyISAM | DEFAULT | NO | NO | NO |
| BerkeleyDB | DISABLED | YES | NO | YES|
| ARCHIVE| YES | NO | NO | NO |
++--+--+-++
Ok, now I believe this does mean I have compiled the InnoDB.
Just to save me trawling though the manual, can you tell me how I should
enable InnoDB?
Thanks for the info!
Ben
and see what it says. It should say InnoDB is supported, if not then
it
hasn't compiled in.
Regards
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +618 8408 4273
_/ _/ _/_/_/ Mobile: 0417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Ben Clewett [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 23 May 2006 4:57 PM
To: mysql@lists.mysql.com
Subject: InnoDB problems under 5.1.9
Dear MySQL,
I've installed 5.1.9 from source on a SUSE 10 box. But I can't get
InnoDB tables respected.
I have used the correct compilation flag (--with-innodb).
SHOW VARIABLES; lists all the usual innodb variables.
The innodb table space has been created in ~/var/ibdata1.
But if I enter:
CREATE TABLE a (
a int NOT NULL PRIMARY KEY
) ENGINE=InnoDB;
SHOW CREATE TABLE a;
CREATE TABLE `a` (
`a` int(10) NOT NULL PRIMARY KEY
) ENGINE=MyISAM
As you can see, an InnoDB has become an MyISAM and will be stored in
~/var/test/a.*
I am using the large table .cnf file. Everything else is much as
default.
Can anybody help me?
Regards,
Ben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problems under 5.1.9
Very very odd. I am compiling with the --use-innodb option. I am starting mysqld with the --innodb option. The table space is created for innodb. But SHOW ENGINE shows InnoDB = DISABLED. There are relevant no errors in the .err file. Thanks for sending me your compilation options. I'll give those a go and see what happens... Regards, Ben. Logan, David (SST - Adelaide) wrote: Hi Ben, I thought the InnoDB engine was included without having to set a ./configure option. On my latest build (admittedly a 5.0.18 one) InnoDB was enabled without setting any ./configure option. My options were hambone /usr/dev/src/mysql-5.0.18 $ cat ../../build_scripts/mysql-5.0.18 #!/bin/bash ./configure \ --prefix=/usr/local/mysql-5.0.18 \ --enable-thread-safe-client \ --with-unix-socket-path=/tmp \ --with-openssl \ --with-example-storage-engine \ --with-archive-storage-engine \ --with-csv-storage-engine \ --with-blackhole-storage-engine \ --with-ndbcluster \ --with-ndb-test \ --with-ndb-port=3510 \ --with-ndb-port-base=3710 \ --with-federated-storage-engine This worked fine. If I get time later, I may give it a punt with your version. Sorry I can't be of more help. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: Tuesday, 23 May 2006 5:50 PM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: Re: InnoDB problems under 5.1.9 Logan, David (SST - Adelaide) wrote: Hi Ben, Try doing SHOW ENGINES; Here: (pertinent cols only) ++--+--+-++ | Engine | Support | Transactions | XA | Savepoints | ++--+--+-++ | CSV| YES | NO | NO | NO | | MEMORY | YES | NO | NO | NO | | MRG_MYISAM | YES | NO | NO | NO | | InnoDB | DISABLED | YES | YES | YES| | BLACKHOLE | YES | NO | NO | NO | | MyISAM | DEFAULT | NO | NO | NO | | BerkeleyDB | DISABLED | YES | NO | YES| | ARCHIVE| YES | NO | NO | NO | ++--+--+-++ Ok, now I believe this does mean I have compiled the InnoDB. Just to save me trawling though the manual, can you tell me how I should enable InnoDB? Thanks for the info! Ben and see what it says. It should say InnoDB is supported, if not then it hasn't compiled in. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: Tuesday, 23 May 2006 4:57 PM To: mysql@lists.mysql.com Subject: InnoDB problems under 5.1.9 Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
query help-multiple joins
Hi! I'm stuck with a join query 2 tables, term and relation, the first one with definition of terms the second one with the relations between them. CREATE TABLE `term` ( `term_id` int(11) unsigned NOT NULL auto_increment, `name` varchar(250) default NULL, ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; and CREATE TABLE `relationTerm` ( `relation_id` int(11) unsigned NOT NULL auto_increment, `term_id1` int(11) unsigned NOT NULL default '0', `term_id2` int(11) unsigned NOT NULL default '0', `type_id` int(11) NOT NULL default '0', PRIMARY KEY (`relation_id`), UNIQUE KEY `uniqueRelation` (`term_id1`,`term_id2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; in the second table term_id1 and term_id2 are referencing term_id of the first table. Example of data: INSERT INTO `term` VALUES (1, 'A'); INSERT INTO `term` VALUES (2, 'B'); INSERT INTO `relationTerm` VALUES (1, 1, 2, 1); I want to obtain: A 1 B I managed to obtain A 1 2, but I don't know how to replace the 2 with the actual name of the entry, which would be B. My query is: SELECT term_id, name, type_id, term_id2 FROM term LEFT JOIN relationTerm ON term_id1 = term_id I should be able to somehow join again on term_id2 but just don't see how to do it... Thanks for any help, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I think I need a join
I think the following could be done with some sort of JOIN, but I am now sure how: [country]: id, country, number 1, Germany, 27 2, Japan, 30 3, United States, 18 [days] id, day, countryA, countryB 10, monday, 1, 3 11, tuesday, 2, 3 12, wednesday, 1, 2 [result I want] 10, monday, Germany, 27, United States, 18 11, tuesday, Japan, 30, United States, 18 12, wednesday, Germany, 27, Japan, 30 Hope that makes sence :) I can get it to join on either countryA or countryB but not both :| -- David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help-multiple joins
Hi, I guess you should be able to do something like: SELECT t1.term_id, t1.name, r.type_id, t2.term_id, t2.name FROM term t1 LEFT JOIN relationTerm r ON r.term_id1 = t1.term_id LEFT JOIN term t2 ON r.term_id2 = t2.term_id /Johan mel list_php skrev: Hi! I'm stuck with a join query 2 tables, term and relation, the first one with definition of terms the second one with the relations between them. CREATE TABLE `term` ( `term_id` int(11) unsigned NOT NULL auto_increment, `name` varchar(250) default NULL, ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; and CREATE TABLE `relationTerm` ( `relation_id` int(11) unsigned NOT NULL auto_increment, `term_id1` int(11) unsigned NOT NULL default '0', `term_id2` int(11) unsigned NOT NULL default '0', `type_id` int(11) NOT NULL default '0', PRIMARY KEY (`relation_id`), UNIQUE KEY `uniqueRelation` (`term_id1`,`term_id2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; in the second table term_id1 and term_id2 are referencing term_id of the first table. Example of data: INSERT INTO `term` VALUES (1, 'A'); INSERT INTO `term` VALUES (2, 'B'); INSERT INTO `relationTerm` VALUES (1, 1, 2, 1); I want to obtain: A 1 B I managed to obtain A 1 2, but I don't know how to replace the 2 with the actual name of the entry, which would be B. My query is: SELECT term_id, name, type_id, term_id2 FROM term LEFT JOIN relationTerm ON term_id1 = term_id I should be able to somehow join again on term_id2 but just don't see how to do it... Thanks for any help, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[off-topic] testing mailing list
this is just a test, please ignore -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help-multiple joins
Perfect! I tried aliasing the field names but didn't think about the table, and was just stuck looking at that query without any idea... Thanks a lot for your help. melanie From: Johan Höök [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: query help-multiple joins Date: Tue, 23 May 2006 13:16:33 +0200 Hi, I guess you should be able to do something like: SELECT t1.term_id, t1.name, r.type_id, t2.term_id, t2.name FROM term t1 LEFT JOIN relationTerm r ON r.term_id1 = t1.term_id LEFT JOIN term t2 ON r.term_id2 = t2.term_id /Johan mel list_php skrev: Hi! I'm stuck with a join query 2 tables, term and relation, the first one with definition of terms the second one with the relations between them. CREATE TABLE `term` ( `term_id` int(11) unsigned NOT NULL auto_increment, `name` varchar(250) default NULL, ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; and CREATE TABLE `relationTerm` ( `relation_id` int(11) unsigned NOT NULL auto_increment, `term_id1` int(11) unsigned NOT NULL default '0', `term_id2` int(11) unsigned NOT NULL default '0', `type_id` int(11) NOT NULL default '0', PRIMARY KEY (`relation_id`), UNIQUE KEY `uniqueRelation` (`term_id1`,`term_id2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; in the second table term_id1 and term_id2 are referencing term_id of the first table. Example of data: INSERT INTO `term` VALUES (1, 'A'); INSERT INTO `term` VALUES (2, 'B'); INSERT INTO `relationTerm` VALUES (1, 1, 2, 1); I want to obtain: A 1 B I managed to obtain A 1 2, but I don't know how to replace the 2 with the actual name of the entry, which would be B. My query is: SELECT term_id, name, type_id, term_id2 FROM term LEFT JOIN relationTerm ON term_id1 = term_id I should be able to somehow join again on term_id2 but just don't see how to do it... Thanks for any help, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ The new MSN Search Toolbar now includes Desktop search! http://join.msn.com/toolbar/overview -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Available
Jan, right now I would say performance sounds pretty good for the amount of data you have. 50 million records / 2.5 GB of data is a pretty sizable dataset, so 4 seconds to retrieve a handful of records seems decent. Some suggestions for things you could do to possibly improve performance: 1 - Change to the compressed table format. See http://dev.mysql.com/doc/refman/5.0/en/compressed-format.html and http://techrepublic.com.com/5100-9592-5852557.html This will in theory allow a given number of records to be read off disk more quickly, since they are smaller. 2 - Run an ALTER TABLE command to ORDER BY the field you search against most commonly. This will in theory allow the database engine to read the table in a more orderly fashion without (as much) disk thrashing. 3 - upgrade memory in your server and change MySQL cache settings to match, in an attempt to keep the indexes and data in RAM. With the amount of data you have this should be possible but you'll need to make sure your hardware, OS, and MySQL release support an appropriate amount of RAM. 4 - if you can't do #3, and maybe even if you can, make sure you're using a very fast disk system (I'd guess you already are). I'd look at at a hardware RAID 5 or 1+0 setup on 15K RPM U320 SCSI disks. Fibre is nice if you can get it but tends to get expensive quickly. Of course #3 and #4 will be dependent on budget and importance of this problem, not to mention your MySQL hosting setup/relationship. Hope this helps, Dan Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problems under 5.1.9
Ben Clewett wrote: Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben make sure you don't have skip--innodb in your my.cnf file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to replicate my DB on 5 branches into 1 DB at HO - Urgent
If u need this in urgent, i think my suggestion may help you. try to do replication in this way Branch-A will be update in Branch-B and Branch-B will update in Branch-C . Branch-E(which got total data of A,B,C,D) will update in Main Office.
Re: aha! replication breaking due to duplicate queries
Hi, yes, it is happening when ever some duplicates are loading replication is not moving further. I mean in my experirnce, i stoped my slave from replication for a while, and i forget the exact location where i stoped it. I resetted my binarylog to appropriate file, and position to 0, and started the slave. It is(client) generating a duplicate error message, and io thread is listening but sql thread is stoped. Any other way to solve this problem?
Multiple engines in one DB a problem?
I'm trying to find a weird performance problem in a MySQL database. I use MySQL v5.0 but the db was migrated forward from a v4.1(?) system. Looking at the schema in a recent backup, I was surprised to find different engines used for different tables: ... CREATE TABLE `comment` ( `id` int(10) unsigned NOT NULL default '0', `entered` date default NULL, `author` varchar(20) default NULL, `about` mediumint(8) unsigned NOT NULL default '0', `comment` text, KEY `bySubject` (`about`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `country` ( `abbrev` char(2) NOT NULL default '', `name` varchar(45) default NULL, PRIMARY KEY (`abbrev`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ... Is this OK? If not, what's the solution? Backup, change all the enginges to the same one, and restore? Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple engines in one DB a problem?
MySQL has a pluggable storage engine architecture which means that you can use multiple storage engines within your databases. What you need to do is investigate the different features of the engines and devide which one best fits your system i.e. level of locking required, InnoDB=row level MyISAM = table level. funnily enough, from face value i would of expected your tables to be the other way around if anything, COMMENT to be InnoDB and country to be MyISAM. MyISAM is probably the easier to maintain and understand at first. further reading: http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html Regards Adrian [EMAIL PROTECTED] wrote: I'm trying to find a weird performance problem in a MySQL database. I use MySQL v5.0 but the db was migrated forward from a v4.1(?) system. Looking at the schema in a recent backup, I was surprised to find different engines used for different tables: ... CREATE TABLE `comment` ( `id` int(10) unsigned NOT NULL default '0', `entered` date default NULL, `author` varchar(20) default NULL, `about` mediumint(8) unsigned NOT NULL default '0', `comment` text, KEY `bySubject` (`about`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `country` ( `abbrev` char(2) NOT NULL default '', `name` varchar(45) default NULL, PRIMARY KEY (`abbrev`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ... Is this OK? If not, what's the solution? Backup, change all the enginges to the same one, and restore? Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problems under 5.1.9
Hi Gerald, I am sure I don't have this in my my.cfg. I am using the supplied 'large table' my.cfg. The *only* innodb option I have is the command line parameter to mysqld: --innodb If anybody has any other options about how to get innodb working in 5.1.9, I'd be very interested! Thanks for the advise, Ben gerald_clark wrote: Ben Clewett wrote: Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben make sure you don't have skip--innodb in your my.cnf file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problems under 5.1.9
Hi Gerald, I am sure I don't have this in my my.cfg. I am using the supplied 'large table' my.cfg. The *only* innodb option I have is the command line parameter to mysqld: --innodb If anybody has any other options about how to get innodb working in 5.1.9, I'd be very interested! Thanks for the advise, Ben gerald_clark wrote: Ben Clewett wrote: Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben make sure you don't have skip--innodb in your my.cnf file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I think I need a join
Critters wrote: I can get it to join on either countryA or countryB but not both :| SELECT d.id, d.day, c1.country, c1.id, c2.country, c2.id FROM days d INNER JOIN countries c1 ON d.countryA = c1.id INNER JOIN countries c2 ON d.countryB = c2.id ORDER BY d.id (Getting ready for the World Cup?) PB - I think the following could be done with some sort of JOIN, but I am now sure how: [country]: id, country, number 1, Germany, 27 2, Japan, 30 3, United States, 18 [days] id, day, countryA, countryB 10, monday, 1, 3 11, tuesday, 2, 3 12, wednesday, 1, 2 [result I want] 10, monday, Germany, 27, United States, 18 11, tuesday, Japan, 30, United States, 18 12, wednesday, Germany, 27, Japan, 30 Hope that makes sence :) I can get it to join on either countryA or countryB but not both :| -- David -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.7.0/345 - Release Date: 5/22/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index merge optimization (with OR) and table joins
Thanks for the reply, it's much appreciated. I'll have another look at the tables, although my criteria were such that the query should have only returned a tiny fraction (0.1%) of the rows (which is why I was confused). If I get any closer to a solution I'll try and post with a bit more info. And next time I'll try and remember to send the SHOW CREATE TABLE statements as well:) Kind regards Stuart On 5/4/06, sheeri kritzer [EMAIL PROTECTED] wrote: (again, apologies for the lateness...) MySQL has a cost-based optimizer. If it's deciding that a full-table scan is appropriate, there's a reason. If more than 30% (approx) of the table would be returned in a range query, the optimizer reasons that it's LESS expensive to just do a full table scan. Otherwise, if say you're returning 50% of the rows, you have to find the pointer to the row using the index, then go to the row. Doing a table scan eliminates needing that extra step of the index. Next time full SHOW CREATE TABLE statements would be useful. -Sheeri On 4/11/06, Stuart Brooks [EMAIL PROTECTED] wrote: Hi, I have been having a hassle getting the index_merge to work as expected when I am joining 2 tables on MySQL 5.0.19. The following example should make it clear: Table A key1 (primary key) key2 some_data Table B key1 (indexed) key2 (indexed) more_data SELECT a.key1,a.key2,b.more_data FROM A as a JOIN B as b ON ((a.key1=b.key1) OR (a.key2=b.key2)) WHERE (a.key1=10); This works as expected. An EXPLAIN yields : a | const| PRIMARY b | index_merge | key1,key2 However if I make the WHERE clause a range (or remove it altogether): SELECT a.key1,a.key2,b.more_data FROM A as a JOIN B as b ON ((a.key1=b.key1) OR (a.key2=b.key2)) WHERE (a.key110) #optional I end up with... a | range | PRIMARY b | ALL | none which is a brute force attack on table b. Am I missing something here, I would have expected it to use an index merge on table b in both cases. Is there a way to force it to use the index merge? Regards Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problems under 5.1.9
Ben, what does SHOW ENGINES show you? It should list all known storage engines and indicate whether your MySQL install supports it or not. Here's mine (5.0.21) for comparison; I was able to create a test table as InnoDB and the SHOW CREATE showed it as InnoDB: - show engines; ++-++ | Engine | Support | Comment | ++-++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| NO | Example storage engine | | ARCHIVE| YES | Archive storage engine | | CSV| NO | CSV storage engine | | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables | | FEDERATED | NO | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine | ++-++ 12 rows in set (0.00 sec) Ben Clewett wrote: Hi Gerald, I am sure I don't have this in my my.cfg. I am using the supplied 'large table' my.cfg. The *only* innodb option I have is the command line parameter to mysqld: --innodb If anybody has any other options about how to get innodb working in 5.1.9, I'd be very interested! Thanks for the advise, Ben gerald_clark wrote: Ben Clewett wrote: Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben make sure you don't have skip--innodb in your my.cnf file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
left right join?
I need to write a query that essentially does both a left and right join. I have a list of people and the rooms they occupy. Some rooms have no people. Some people have no room assigned. This gives me a list of rooms and how many people are in each room including any rooms with nobody in them: select r.room_num, count(p.peopid) from rooms r left join people p on r.roomid=p.roomid1 group by r.room_num All I need is one more row in the table with a null for the room num and a count of the number of people with no room assigned. If I change 'left' to 'right' in the query above, I get that row but then I don't get rooms with no people. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: left right join?
John, Union the 2 together. select r.room_num, count(p.peopid) from rooms r left join people p on r.roomid=p.roomid1 group by r.room_num UNION select r.room_num, count(p.peopid) from rooms r right join people p on r.roomid=p.roomid1 group by r.room_num or something like that. Should give you 2 rows with the data you are looking for. HTH, =C= | | Cal Evans | http://blog.calevans.com | | John Heim wrote: I need to write a query that essentially does both a left and right join. I have a list of people and the rooms they occupy. Some rooms have no people. Some people have no room assigned. This gives me a list of rooms and how many people are in each room including any rooms with nobody in them: select r.room_num, count(p.peopid) from rooms r left join people p on r.roomid=p.roomid1 group by r.room_num All I need is one more row in the table with a null for the room num and a count of the number of people with no room assigned. If I change 'left' to 'right' in the query above, I get that row but then I don't get rooms with no people. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Available
Please post your exact table schema using SHOW CREATE TABLE, and your exact query, along with an EXPLAIN SELECT for the query. Thanks! -jay Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problems under 5.1.9
Hi Dan, This is what I have. What does this mean with regards to InnoDB? ++--++--+-++ | Engine | Support | Comment | Transactions | XA | Savepoints | ++--++--+-++ | CSV| YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys | YES | YES | YES| | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | BerkeleyDB | DISABLED | Supports transactions and page-level locking | YES | NO | YES| | ARCHIVE| YES | Archive storage engine | NO | NO | NO | ++--++--+-++ Thanks, Dan Buettner wrote: Ben, what does SHOW ENGINES show you? It should list all known storage engines and indicate whether your MySQL install supports it or not. Here's mine (5.0.21) for comparison; I was able to create a test table as InnoDB and the SHOW CREATE showed it as InnoDB: - show engines; ++-++ | Engine | Support | Comment | ++-++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| NO | Example storage engine | | ARCHIVE| YES | Archive storage engine | | CSV| NO | CSV storage engine | | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables | | FEDERATED | NO | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine | ++-++ 12 rows in set (0.00 sec) Ben Clewett wrote: Hi Gerald, I am sure I don't have this in my my.cfg. I am using the supplied 'large table' my.cfg. The *only* innodb option I have is the command line parameter to mysqld: --innodb If anybody has any other options about how to get innodb working in 5.1.9, I'd be very interested! Thanks for the advise, Ben gerald_clark wrote: Ben Clewett wrote: Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben make sure you don't have skip--innodb in your my.cnf file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problems under 5.1.9
Ben, looks like you've either got it disabled in my.cnf or with a startup flag, or you've not set all the needed options for InnoDB. See http://dev.mysql.com/doc/refman/5.0/en/mysqld-max.html, near the bottom of the page it explains what DISABLED means and refers you to the error log for messages. Dan Ben Clewett wrote: Hi Dan, This is what I have. What does this mean with regards to InnoDB? ++--++--+-++ | Engine | Support | Comment | Transactions | XA | Savepoints | ++--++--+-++ | CSV| YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys | YES | YES | YES| | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | BerkeleyDB | DISABLED | Supports transactions and page-level locking | YES | NO | YES| | ARCHIVE| YES | Archive storage engine | NO | NO | NO | ++--++--+-++ Thanks, Dan Buettner wrote: Ben, what does SHOW ENGINES show you? It should list all known storage engines and indicate whether your MySQL install supports it or not. Here's mine (5.0.21) for comparison; I was able to create a test table as InnoDB and the SHOW CREATE showed it as InnoDB: - show engines; ++-++ | Engine | Support | Comment | ++-++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| NO | Example storage engine | | ARCHIVE| YES | Archive storage engine | | CSV| NO | CSV storage engine | | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables | | FEDERATED | NO | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine | ++-++ 12 rows in set (0.00 sec) Ben Clewett wrote: Hi Gerald, I am sure I don't have this in my my.cfg. I am using the supplied 'large table' my.cfg. The *only* innodb option I have is the command line parameter to mysqld: --innodb If anybody has any other options about how to get innodb working in 5.1.9, I'd be very interested! Thanks for the advise, Ben gerald_clark wrote: Ben Clewett wrote: Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben make sure you don't have skip--innodb in your my.cnf file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA FROM MASTER stops unfinished with Query OK
Greetings, I played around with load data from master (ldfm) and it worked fine in test environment. Now I want to replicate our actual db to a slave. When I issue the ldfm command, it starts the replication. I get Query OK, but only about 5% of the db is replicated. Apparently all tables that are on the slave in the end are exact copies of the master tables, but most MYD files are zero sized Any ideas? Thanks in advance Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problems under 5.1.9
Thanks for the excellent reference, this gives me a lot to go on. My server is in bits at the moment, I'll let you know when it's up again! Ben Dan Buettner wrote: Ben, looks like you've either got it disabled in my.cnf or with a startup flag, or you've not set all the needed options for InnoDB. See http://dev.mysql.com/doc/refman/5.0/en/mysqld-max.html, near the bottom of the page it explains what DISABLED means and refers you to the error log for messages. Dan Ben Clewett wrote: Hi Dan, This is what I have. What does this mean with regards to InnoDB? ++--++--+-++ | Engine | Support | Comment | Transactions | XA | Savepoints | ++--++--+-++ | CSV| YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys | YES | YES | YES| | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | BerkeleyDB | DISABLED | Supports transactions and page-level locking | YES | NO | YES| | ARCHIVE| YES | Archive storage engine | NO | NO | NO | ++--++--+-++ Thanks, Dan Buettner wrote: Ben, what does SHOW ENGINES show you? It should list all known storage engines and indicate whether your MySQL install supports it or not. Here's mine (5.0.21) for comparison; I was able to create a test table as InnoDB and the SHOW CREATE showed it as InnoDB: - show engines; ++-++ | Engine | Support | Comment | ++-++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| NO | Example storage engine | | ARCHIVE| YES | Archive storage engine | | CSV| NO | CSV storage engine | | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables | | FEDERATED | NO | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine | ++-++ 12 rows in set (0.00 sec) Ben Clewett wrote: Hi Gerald, I am sure I don't have this in my my.cfg. I am using the supplied 'large table' my.cfg. The *only* innodb option I have is the command line parameter to mysqld: --innodb If anybody has any other options about how to get innodb working in 5.1.9, I'd be very interested! Thanks for the advise, Ben gerald_clark wrote: Ben Clewett wrote: Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben make sure you don't have skip--innodb in your my.cnf file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Looking for free MySQL Administrator
I'm looking for a MySQL administrator for 4.x/5.x that will allow me to Any suggestions? TIA http://www.webyog.com/ Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Available
Hy Jay, Thanks for you attention. Show you my structure and EXPLAIN: CREATE TABLE `table` ( `id_table1` int(10) unsigned NOT NULL default '0', `id_table2` int(10) unsigned NOT NULL default '0', `field1`smallint(5) unsigned NOT NULL default '0', `field2`mediumint(8) unsigned NOT NULL default '0', `textField` text NOT NULL, PRIMARY KEY (`id_table1`,`id_table2`), KEY `table_idx1` (`id_table2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 +-+---+---++ | select_type | table | type | possible_keys | +-+---+---++ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---++ ++-+--+--+-+ | key | key_len | ref | rows | Extra | +--- +-+--+--+-+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ Please post your exact table schema using SHOW CREATE TABLE, and your exact query, along with an EXPLAIN SELECT for the query. Thanks! -jay Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB problems under 5.1.9
Thanks for the tip. Simple problem, my innodb data file was created with the default my.cnf. When I started it with the large_table version, it used different innodb table space size. Therefore would not start :) Cheers, Ben Dan Buettner wrote: Ben, looks like you've either got it disabled in my.cnf or with a startup flag, or you've not set all the needed options for InnoDB. See http://dev.mysql.com/doc/refman/5.0/en/mysqld-max.html, near the bottom of the page it explains what DISABLED means and refers you to the error log for messages. Dan Ben Clewett wrote: Hi Dan, This is what I have. What does this mean with regards to InnoDB? ++--++--+-++ | Engine | Support | Comment | Transactions | XA | Savepoints | ++--++--+-++ | CSV| YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys | YES | YES | YES| | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | BerkeleyDB | DISABLED | Supports transactions and page-level locking | YES | NO | YES| | ARCHIVE| YES | Archive storage engine | NO | NO | NO | ++--++--+-++ Thanks, Dan Buettner wrote: Ben, what does SHOW ENGINES show you? It should list all known storage engines and indicate whether your MySQL install supports it or not. Here's mine (5.0.21) for comparison; I was able to create a test table as InnoDB and the SHOW CREATE showed it as InnoDB: - show engines; ++-++ | Engine | Support | Comment | ++-++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| NO | Example storage engine | | ARCHIVE| YES | Archive storage engine | | CSV| NO | CSV storage engine | | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables | | FEDERATED | NO | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine | ++-++ 12 rows in set (0.00 sec) Ben Clewett wrote: Hi Gerald, I am sure I don't have this in my my.cfg. I am using the supplied 'large table' my.cfg. The *only* innodb option I have is the command line parameter to mysqld: --innodb If anybody has any other options about how to get innodb working in 5.1.9, I'd be very interested! Thanks for the advise, Ben gerald_clark wrote: Ben Clewett wrote: Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben make sure you don't have skip--innodb in your my.cnf file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finally Working...wow
Hi folks. Me again. I finally got this all up and running under crypt of 'cleartext'. So, even though I am going to be on the same box as the server, how do I set up an MD5 or password entry? MYSQLCrypt password() MYSQLCrypt password MYSQLCrypt MD5() MYSQLCrypt MD5 I'm not sure if the brackets are needed or not. So if I use password or MD5 encryption in the tables, the value in the field is encrypted. How does this change how I structure things? instead of 'passwordalpha' in the password field, I have to enter 'ff08d88bab6edcf9d730a96418c05358'? I am entering users via my own interface, and I can't seem to get MD5 working with either MD5 or MD5(). Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Available
Jan Gomes wrote: Hy Jay, Thanks for you attention. Show you my structure and EXPLAIN: CREATE TABLE `table` ( `id_table1` int(10) unsigned NOT NULL default '0', `id_table2` int(10) unsigned NOT NULL default '0', `field1`smallint(5) unsigned NOT NULL default '0', `field2`mediumint(8) unsigned NOT NULL default '0', `textField` text NOT NULL, PRIMARY KEY (`id_table1`,`id_table2`), KEY `table_idx1` (`id_table2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 You don't show the query you are explaining. It certainly can't be the one in your previous post below, because column names don't match. +-+---+---++ | select_type | table | type | possible_keys | +-+---+---++ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---++ ++-+--+--+-+ | key | key_len | ref | rows | Extra | +--- +-+--+--+-+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ Please post your exact table schema using SHOW CREATE TABLE, and your exact query, along with an EXPLAIN SELECT for the query. Thanks! -jay Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Looking for free MySQL Administrator
Check out http://www.mysql.com/products/tools/ for some good stuff... Raj Mehrotra [EMAIL PROTECTED] -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 23, 2006 1:21 PM To: mysql@lists.mysql.com Subject: RE: Looking for free MySQL Administrator I'm looking for a MySQL administrator for 4.x/5.x that will allow me to Any suggestions? TIA http://www.webyog.com/ Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Looking for free MySQL Administrator
there is also a nice tool for Mac OSX called CocoaMySQL. http://cocoamysql.sourceforge.net/ George Law -Original Message- From: Rajesh Mehrotra [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 23, 2006 3:58 PM To: Rithish Saralaya; mysql@lists.mysql.com Subject: RE: Looking for free MySQL Administrator Check out http://www.mysql.com/products/tools/ for some good stuff... Raj Mehrotra [EMAIL PROTECTED] -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 23, 2006 1:21 PM To: mysql@lists.mysql.com Subject: RE: Looking for free MySQL Administrator I'm looking for a MySQL administrator for 4.x/5.x that will allow me to Any suggestions? TIA http://www.webyog.com/ Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting doubles in a table and setting all but the first
Hi, everyone, This is something I can't seem to solve. Does anyone know how to do this ? I don't know whether I described this right. Anyhow, jumping right in, I have the following query which gives me all the LicenseNumbers that have a different Host-ID, but the same packageID (and with ValidThru 3 which means I don't want to count the ones where validThru is greater than 2).What I want to do next is set another column "Type" for all the specific rows with these LicenseNumbers from this first query, EXCEPT the first row (if ordered in time) The first can be found by a column Created, which is a date.SELECT COUNT( DISTINCT `HostID` ) AS am, licensekeys. *FROM `licensekeys`WHERE (CHAR_LENGTH( `ValidThru` ) 3)GROUP BY PackageID, `LicenseNumber`HAVING am 1In practice: The above query gives me this (2068 results):am LicenseNumber PackageID 8 049-02-00011835 1008 049-02-9949 1008 049-02-00010117 9037 049-02-00015799 1127 049-02-00010117 9057 049-02-00010767 1007 049-02-0382 1006 049-02-9949 112..Checking Lic. Nr 049-02-00011835 with PackageID=100 for example gives this (8 results as given already above):HostID Created803d2ed1 2/9/2004 14:26:30cbac7a9 25/8/2005 9:22:359cb24cdd 8/2/2005 9:06:533453a0b0 2/9/2004 14:51:582cb4d794 15/9/2004 5:08:40c0058a89 30/9/2004 13:21:3994244c2f 6/1/2006 8:43:04ac21b9ad 8/7/2004 8:38:26 What I would want now is that from the last query (just above this sentence) all rows, EXCEPT the earliest one (that would be the one with HostID=ac21b9ad because its Created date is oldest), get Type="replacement". The result would be then:HostID Created Type803d2ed1 2/9/2004 14:26:30 replacementcbac7a9 25/8/2005 9:22:35 replacement9cb24cdd 8/2/2005 9:06:53 replacement3453a0b0 2/9/2004 14:51:58 replacement2cb4d794 15/9/2004 5:08:40replacementc0058a89 30/9/2004 13:21:39 replacement94244c2f 6/1/2006 8:43:04 replacementac21b9ad 8/7/2004 8:38:26How in the name of... can I do this for ALL license Numbers from the first Query automatically ?(Please don't give me the solution for 1 LicenseNumber, but for all the ones form the first query at once or twice, but not in 2068 times)Much appreciated any help!!! Cheers, Ruben Goethals +32-(0)479/316655 [EMAIL PROTECTED]www.e-builds.com
Re: Performance Available
Hy Jay, You don't show the query you are explaining. The query is: SELECT * FROM table where id_table2 in (1652272,1652293,1652316,1652362); +-+---+---+--+ | select_type | table | type | possible_keys | +-+---+---+--+ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---+--+ ++-+--+--+--+ | key | key_len | ref | rows | Extra | +--- +-+--+--+--+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ It certainly can't be the one in your previous post below, because column names don't match. Yes, this is the same, but i hide the real name of table and column for security. PS.: Pardon me for some thing, my english is not very good Jan Gomes wrote: Hy Jay, Thanks for you attention. Show you my structure and EXPLAIN: CREATE TABLE `table` ( `id_table1` int(10) unsigned NOT NULL default '0', `id_table2` int(10) unsigned NOT NULL default '0', `field1`smallint(5) unsigned NOT NULL default '0', `field2`mediumint(8) unsigned NOT NULL default '0', `textField` text NOT NULL, PRIMARY KEY (`id_table1`,`id_table2`), KEY `table_idx1` (`id_table2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 You don't show the query you are explaining. It certainly can't be the one in your previous post below, because column names don't match. +-+---+---++ | select_type | table | type | possible_keys | +-+---+---++ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---++ ++-+--+--+-+ | key | key_len | ref | rows | Extra | +--- +-+--+--+-+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ Please post your exact table schema using SHOW CREATE TABLE, and your exact query, along with an EXPLAIN SELECT for the query. Thanks! -jay Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: I can't connect to mysql server with PHP
Hi! gerald_clark, But when I call mysql_pconnect(localhost:3306,root,root_password),it return the same error. How can I get the permission to open /var/mysql/lib/mysql.sock? Fang fool.ben wrote: Hi everybody! I've install a mysql server on my computer. The operating system is Redhat fedora core 4. The version of the mysql server is 4.1.3 Beta. I wanna connect to the server using the following statement: $db_connection=mysql_pconnect(localhost,root,); The server returned the error 2002: 2002 Can't connect to local MySQL server through socket '/var/mysql/lib/mysql.sock(13)' The user running the php connection does not have permissions to open /var/mysql/lib/mysql.sock. I was suggested that the server may not running, or the sockect is wrong. But when I run the following statement, there was no error occured: localhost#/usr/local/mysql/bin/mysql -uroot -S/var/mysql/lib/mysql.sock Here you are root, so you have permissions. Can anyone help me? Fix your permissions or move your socket to a directory that is world searchable. Fang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Available
Jan Gomes wrote: The query is: SELECT * FROM table where id_table2 in (1652272,1652293,1652316,1652362); +-+---+---+--+ | select_type | table | type | possible_keys | +-+---+---+--+ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---+--+ ++-+--+--+--+ | key | key_len | ref | rows | Extra | +--- +-+--+--+--+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ If this is what the EXPLAIN is, and you are getting 4 second response times, I would guess that something is amiss. Have you tried running an ANALYZE TABLE on your table to rebuild the index? Be aware that this will issue a table lock on the table, do do it off hours. Then, issue the query again and post back time results. Also, post the output of SELECT @@version; Cheers Jay PS.: Pardon me for some thing, my english is not very good It's just fine! Hy Jay, Thanks for you attention. Show you my structure and EXPLAIN: CREATE TABLE `table` ( `id_table1` int(10) unsigned NOT NULL default '0', `id_table2` int(10) unsigned NOT NULL default '0', `field1`smallint(5) unsigned NOT NULL default '0', `field2`mediumint(8) unsigned NOT NULL default '0', `textField` text NOT NULL, PRIMARY KEY (`id_table1`,`id_table2`), KEY `table_idx1` (`id_table2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 You don't show the query you are explaining. It certainly can't be the one in your previous post below, because column names don't match. +-+---+---++ | select_type | table | type | possible_keys | +-+---+---++ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---++ ++-+--+--+-+ | key | key_len | ref | rows | Extra | +--- +-+--+--+-+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ Please post your exact table schema using SHOW CREATE TABLE, and your exact query, along with an EXPLAIN SELECT for the query. Thanks! -jay Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query problem: UNION in subquery
Hello!
I have a problem using UNIONs inside subqueries. I have simplified my
query to make it more readable/understandable.
The question is about the right syntax.
1.
This works fine /UNION/
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))
UNION
(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))
2.
This works fine too /subquery/:
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6')) AS
SubTable1;
3.
But when I run 12 combined I get in troubles. This is a query draft,
can't come up with the right syntax:
SELECT ObjectId FROM
(SELECT f15.Form15SampleTube1RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube1RnaBarcode IN ('01D2V','01DH6'))
UNION
(SELECT f15.Form15SampleTube6RnaBarcode AS ObjectId,
f15.Form15PatientID AS PtId FROM form15 f15
WHERE f15.Form15SampleTube6RnaBarcode IN ('01D2V','01DH6'))
I tried many combinations and got various syntax errors. Any ideas?
Thanks,
Luke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Selecting doubles in a table and setting all but the first
Goethals If I got u properly, Follow these easy steps.. 1. Send the output of your first query to another table say t1 2. Send the output of finding min(Created) date to another table t2 3. Replace the value to TYPE field with for all rows which matches table t1 data 4. in the last replace the field TYPE those matches you t1 and t2 data with '' blank or whatever. Seems it will solve your problem.. there are solution too, but this one is simplest and easy to understand . Cheers :-) Neeraj Black Bits _ From: Ruben Goethals [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 23, 2006 8:54 PM To: mysql@lists.mysql.com Subject: Selecting doubles in a table and setting all but the first Hi, everyone, This is something I can't seem to solve. Does anyone know how to do this ? I don't know whether I described this right. Anyhow, jumping right in, I have the following query which gives me all the LicenseNumbers that have a different Host-ID, but the same packageID (and with ValidThru 3 which means I don't want to count the ones where validThru is greater than 2). What I want to do next is set another column Type for all the specific rows with these LicenseNumbers from this first query, EXCEPT the first row (if ordered in time) The first can be found by a column Created, which is a date. SELECT COUNT( DISTINCT `HostID` ) AS am, licensekeys. * FROM `licensekeys` WHERE ( CHAR_LENGTH( `ValidThru` ) 3 ) GROUP BY PackageID, `LicenseNumber` HAVING am 1 In practice: The above query gives me this (2068 results): am LicenseNumber PackageID 8 049-02-00011835 100 8 049-02-9949 100 8 049-02-00010117 903 7 049-02-00015799 112 7 049-02-00010117 905 7 049-02-00010767 100 7 049-02-0382 100 6 049-02-9949 112 .. Checking Lic. Nr 049-02-00011835 with PackageID=100 for example gives this (8 results as given already above): HostID Created 803d2ed1 2/9/2004 14:26:30 cbac7a9 25/8/2005 9:22:35 9cb24cdd 8/2/2005 9:06:53 3453a0b0 2/9/2004 14:51:58 2cb4d794 15/9/2004 5:08:40 c0058a89 30/9/2004 13:21:39 94244c2f 6/1/2006 8:43:04 ac21b9ad 8/7/2004 8:38:26 What I would want now is that from the last query (just above this sentence) all rows, EXCEPT the earliest one (that would be the one with HostID=ac21b9ad because its Created date is oldest), get Type=replacement. The result would be then: HostID CreatedType 803d2ed1 2/9/2004 14:26:30 replacement cbac7a9 25/8/2005 9:22:35replacement 9cb24cdd 8/2/2005 9:06:53 replacement 3453a0b0 2/9/2004 14:51:58 replacement 2cb4d794 15/9/2004 5:08:40 replacement c0058a89 30/9/2004 13:21:39 replacement 94244c2f 6/1/2006 8:43:04 replacement ac21b9ad 8/7/2004 8:38:26 How in the name of... can I do this for ALL license Numbers from the first Query automatically ? (Please don't give me the solution for 1 LicenseNumber, but for all the ones form the first query at once or twice, but not in 2068 times) Much appreciated any help!!! Cheers, Ruben Goethals +32-(0)479/316655 [EMAIL PROTECTED] www.e-builds.com http://www.e-builds.com/ http://www.e-builds.com/e-builds.vcf Get my vcard http://www.e-builds.com e-builds web development logo
Urgent problem
Best group member, My computer stopped working. And I was able to install Windows on another partition and now I face one problem. I was running Windows with MySQL 4.1. I have all C: working, and can access all files. The first question: Can I recover that data from MySQL? The second question: How can I do that? (All attempts to repair Windows has failed, so that option is gone) I have gone thru the manual but all information there seems to be related to if the database crashes. And some options are there when you do make backups, but I do not have the backups. A desperate soul... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
