Re: Is it possible to make this more efficient?
On Wed, Oct 19, 2011 at 05:09:13PM +1100, Wayne W wrote: Hi, I asked this question over on stackoverflow - basically I have a query and when using EXPLAIN I see that around 2400 rows are still being scanned. I'd added various indexes but it cannot make it perform any better. I would appreciate if anyone has any further ideas? http://stackoverflow.com/questions/7793393/what-can-i-do-to-make-this-sql-more-effecient-table-has-850k-rows Create index on (importance, company_id) (or (company_id,importance), should not matter). When that index is present, the query should use ref access on it using both key parts (if by some crazy reason it doesn't, use FORCE INDEX to make it to). This way, you will be scanning as few rows as possible. Also, check if the EXPLAIN has Using index. If it doesn't, add `id` to the index, i.e. use INDEX (importance, company_id, id) That way, the query will be only using index, which will give the best possible speed. BR Sergey -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is it possible to make this more efficient?
- Original Message - From: Sergei Petrunia pser...@askmonty.org Create index on (importance, company_id) (or (company_id,importance), should not matter). When that index is present, the query should use ref access on it using both key parts (if by some crazy reason it doesn't, use FORCE INDEX to make it to). This way, you will be scanning as few rows as possible. Umm... It might just be a matter of cardinality? :-) While the optimizer can be wrong at times (and it's certainly worth checking that) it does usually pick the best choices. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Very interesting. Waiting for update. On Jun 15, 2011 4:51 AM, Hank hes...@gmail.com wrote: The slave is receiving null as the statement based insert, not an out of range number from the master. I've been doing more research all day on this bug and have a bit more information as to what's causing it. I plan to write it up tomorrow and post it. Basically, everything works perfectly, until I add a replication-ignore-table=xxx statement in my.cnf where xxx is a different table with a unique id INT auto-increment as the single primary key And then the values being inserted into the test table (above, not ignored) represent the last-insert-id of the replication *ignored* table on the slave Yeah, pretty strange, I know. But totally repeatable. -Hank 2011/6/14 Halász Sándor h...@tbbs.net 2011/06/13 22:38 -0400, Hank But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. If the master were sending random big numbers, and replication on the slave in the usual way handled out-of-bound numbers when not allowed to fail, then 65535 would be an expected value for a signless 16-bit number. Of course, if this were true, the slave would be getting not that statement but insert into test values (1,469422). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=hes...@gmail.com
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
You should also have a look at the slave relay log. But in any case sounds like a bug. Claudio On Jun 14, 2011 5:18 AM, Hank hes...@gmail.com wrote: Both my master and slave bin logs look OK (I think).. master bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; slave bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; -Hank On Mon, Jun 13, 2011 at 10:38 PM, Hank hes...@gmail.com wrote: Yes, it's basic out-of-the box mysql replication. This appears to be an instance of this bug: http://bugs.mysql.com/bug.php?id=45670 But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. http://bugs.mysql.com/bug.php?id=45670 -Hank On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.com wrote: Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 469422 |
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
That is the slave relay log dump I posted (and mis-labeled). Thanks. -Hank On Tue, Jun 14, 2011 at 2:34 AM, Claudio Nanni claudio.na...@gmail.comwrote: You should also have a look at the slave relay log. But in any case sounds like a bug. Claudio On Jun 14, 2011 5:18 AM, Hank hes...@gmail.com wrote: Both my master and slave bin logs look OK (I think).. master bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; slave relay log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; -Hank On Mon, Jun 13, 2011 at 10:38 PM, Hank hes...@gmail.com wrote: Yes, it's basic out-of-the box mysql replication. This appears to be an instance of this bug: http://bugs.mysql.com/bug.php?id=45670 But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. http://bugs.mysql.com/bug.php?id=45670 -Hank On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.comwrote: Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
2011/06/13 22:38 -0400, Hank But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. If the master were sending random big numbers, and replication on the slave in the usual way handled out-of-bound numbers when not allowed to fail, then 65535 would be an expected value for a signless 16-bit number. Of course, if this were true, the slave would be getting not that statement but insert into test values (1,469422). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
The slave is receiving null as the statement based insert, not an out of range number from the master. I've been doing more research all day on this bug and have a bit more information as to what's causing it. I plan to write it up tomorrow and post it. Basically, everything works perfectly, until I add a replication-ignore-table=xxx statement in my.cnf where xxx is a different table with a unique id INT auto-increment as the single primary key And then the values being inserted into the test table (above, not ignored) represent the last-insert-id of the replication *ignored* table on the slave Yeah, pretty strange, I know. But totally repeatable. -Hank 2011/6/14 Halász Sándor h...@tbbs.net 2011/06/13 22:38 -0400, Hank But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. If the master were sending random big numbers, and replication on the slave in the usual way handled out-of-bound numbers when not allowed to fail, then 65535 would be an expected value for a signless 16-bit number. Of course, if this were true, the slave would be getting not that statement but insert into test values (1,469422). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=hes...@gmail.com
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 | 1 | 470673 | should be 2 +++ Now if I repeat the entire scenario using smallint for the 'cnt' field, here are the results: master CREATE TABLE test (id int NOT NULL, cnt smallint unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master desc test; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | smallint(5) unsigned | NO | PRI | NULL | auto_increment | +---+--+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 65535 | should be 1 +++ but this is different: master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! | 1 | 2 | correct! ++-+ slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, missing second record, too ++---+ slave show slave status; Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test values (1,null)' .. at which point I have to restart the slave due to the error: slave SET GLOBAL sql_slave_skip_counter=1; slave start; slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, still missing second record, too (of course) ++---+ Now if I manually replicate the statements just on the slave - it works perfectly: slave: truncate table test; slave: insert into test values (1,null); slave: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | ++-+ slave: insert into test values (1,null); slave: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | ++-+ So something in the replication code is munging the 'null' into some random value and trying to insert it. Seems strange that direct statements would work, but replicated statements do not. Nothing really changed on my system, but for some reason, this all started happening about a week or so ago. I've been running this 5.5.8/5.5.11 configuration for months now (since 5.5.8 was released). The PHP code that does this hasn't changed one bit, and this is a simplified version of the database and code that is running in production. Additional note: If I drop the
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Yes, it's basic out-of-the box mysql replication. This appears to be an instance of this bug: http://bugs.mysql.com/bug.php?id=45670 But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. http://bugs.mysql.com/bug.php?id=45670 -Hank On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.comwrote: Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 | 1 | 470673 | should be 2 +++ Now if I repeat the entire scenario using smallint for the 'cnt' field, here are the results: master CREATE TABLE test (id int NOT NULL, cnt smallint unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master desc test; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | smallint(5) unsigned | NO | PRI | NULL | auto_increment | +---+--+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 65535 | should be 1 +++ but this is different: master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! | 1 | 2 | correct! ++-+ slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, missing second record, too ++---+ slave show slave status; Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test values (1,null)' .. at which point I have to restart the slave due to the error: slave SET GLOBAL sql_slave_skip_counter=1; slave start; slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, still missing second record, too (of course) ++---+ Now if I manually replicate the statements just on the slave - it works perfectly: slave: truncate table test; slave: insert into test values (1,null);
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Both my master and slave bin logs look OK (I think).. master bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; slave bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; -Hank On Mon, Jun 13, 2011 at 10:38 PM, Hank hes...@gmail.com wrote: Yes, it's basic out-of-the box mysql replication. This appears to be an instance of this bug: http://bugs.mysql.com/bug.php?id=45670 But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. http://bugs.mysql.com/bug.php?id=45670 -Hank On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.comwrote: Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 | 1 | 470673 | should be 2 +++ Now if I repeat the entire scenario using smallint for the 'cnt' field, here are the results: master CREATE TABLE test (id int
Re: Is is possible to update a column based on a REGEXP on another column?
If the bracketed stuff really can be anything, you're better off doing it externally, I guess. If you can be reasonably sure that there'll not be any square brackets in there, you can fluff about with instr() and substr(). On Sat, Jan 22, 2011 at 6:18 PM, Eric Bergen eric.ber...@gmail.com wrote: There isn't a built in way but you can try http://www.mysqludf.org/lib_mysqludf_preg/ I would go with the php/perl script if this is a one time thing. -Eric On Jan 21, 2011, at 11:31 AM, Phil pchap...@nc.rr.com wrote: I have a table which contains a username column which may be constructed something like somename[A] or [DDD]someothername The A or DDD can be anything at all. I've added a new column to the table to which I'd like to populate with the value within the square brackets. I could write something in perl or php to run through each and update them but was wondering if there is a way to do this within mysql itself? The regexp only returns a boolean so I can't see how to use that. Regards Phil -- Distributed Computing stats http://stats.free-dc.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Is is possible to update a column based on a REGEXP on another column?
There isn't a built in way but you can try http://www.mysqludf.org/lib_mysqludf_preg/ I would go with the php/perl script if this is a one time thing. -Eric On Jan 21, 2011, at 11:31 AM, Phil pchap...@nc.rr.com wrote: I have a table which contains a username column which may be constructed something like somename[A] or [DDD]someothername The A or DDD can be anything at all. I've added a new column to the table to which I'd like to populate with the value within the square brackets. I could write something in perl or php to run through each and update them but was wondering if there is a way to do this within mysql itself? The regexp only returns a boolean so I can't see how to use that. Regards Phil -- Distributed Computing stats http://stats.free-dc.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Nested inserts possible?
Keith, You will need to reference the mysql_insert_id() function after adding the row to Table_One. Then use the value returned for the rest of the tables. http://dev.mysql.com/doc/refman/5.1/en/getting-unique-id.html Mike At 08:48 AM 3/12/2010, you wrote: I have two tables and I have data to insert into both at the same time, but the second table depends on an ID that is auto created in the first table. I'm not sure exactly how to accomplish this. Table_One Table_One_Index_ID Data_One Date_Two Table_Two Table_Two_Index_ID Table_One_Index_ID Data_Three Data_Four -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Nested inserts possible?
Johan, That seems to be the ticket. Thanks! Keith On Fri, 2010-03-12 at 16:54 +0100, Johan De Meersman wrote: Have a look at last_insert_id(). On Fri, Mar 12, 2010 at 3:48 PM, Keith Clark keithcl...@k-wbookworm.com wrote: I have two tables and I have data to insert into both at the same time, but the second table depends on an ID that is auto created in the first table. I'm not sure exactly how to accomplish this. Table_One Table_One_Index_ID Data_One Date_Two Table_Two Table_Two_Index_ID Table_One_Index_ID Data_Three Data_Four -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Nested inserts possible?
If you're inserting multiple rows in the dependent table for the same id in the parent table, you'll need to save the value of LAST_INSERT_ID(), otherwise subsequent inserts will change it! INSERT INTO parent VALUES (...); SET @id:=LAST_INSERT_ID(); INSERT INTO child1 VALUES (@id, ... ); INSERT INTO child2 VALUES (@id, ... ); -Original Message- From: Keith Clark [mailto:keithcl...@k-wbookworm.com] Sent: Friday, March 12, 2010 7:57 AM To: mysql@lists.mysql.com Subject: Re: Nested inserts possible? Johan, That seems to be the ticket. Thanks! Keith On Fri, 2010-03-12 at 16:54 +0100, Johan De Meersman wrote: Have a look at last_insert_id(). On Fri, Mar 12, 2010 at 3:48 PM, Keith Clark keithcl...@k-wbookworm.com wrote: I have two tables and I have data to insert into both at the same time, but the second table depends on an ID that is auto created in the first table. I'm not sure exactly how to accomplish this. Table_One Table_One_Index_ID Data_One Date_Two Table_Two Table_Two_Index_ID Table_One_Index_ID Data_Three Data_Four -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
Re: Nested inserts possible?
Have a look at last_insert_id()http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id . On Fri, Mar 12, 2010 at 3:48 PM, Keith Clark keithcl...@k-wbookworm.comwrote: I have two tables and I have data to insert into both at the same time, but the second table depends on an ID that is auto created in the first table. I'm not sure exactly how to accomplish this. Table_One Table_One_Index_ID Data_One Date_Two Table_Two Table_Two_Index_ID Table_One_Index_ID Data_Three Data_Four -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Is It Possible to create a Join with Multiple Tables
c...@hosting4days.com wrote: :Newbie: Short Question: Is it possible to create an inner join (or another join) with multiple tables? something like $query_list1 = SELECT table1.id,table1.status, table2.id, table3.id (more here - etc.) FROM table1 INNER JOIN table2 ON table1.id = table2.id INNER JOIN table3 ON table2.id = table3.id WHERE table1.status = 'open'; Q: Is something like this possible? The easiest way to find out if something might work is to try it. Did you actually try the statement you posted above? Assuming your typing was correct, it should have worked just fine. For future reference, here is the JOIN page in the MySQL manual: http://dev.mysql.com/doc/refman/5.1/en/join.html Suggestion -- If you have a problem: document what you did, what you did it to, and the error message you received. Include any alternatives you have tried and enough explanation that we can figure out what you are trying to accomplish. Normally this will get you correct and accurate responses very quickly. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is It Possible to create a Join with Multiple Tables
I don't see why not, but another choice is to create a view that encompasses all these tables and Select * From my_view. Arthur On Fri, Sep 25, 2009 at 11:28 PM, c...@hosting4days.com c...@hosting4days.com wrote: :Newbie: Short Question: Is it possible to create an inner join (or another join) with multiple tables? something like $query_list1 = SELECT table1.id,table1.status, table2.id, table3.id (more here - etc.) FROM table1 INNER JOIN table2 ON table1.id = table2.id INNER JOIN table3 ON table2.id = table3.id WHERE table1.status = 'open'; Q: Is something like this possible? Thanks, c...@hosting4days.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com
Re: Is it possible to convert column data into rows
I have a column which contains a list of IDs separated by commas. How can I take the content of this column, and insert it into a temporary table so that each ID is on a separate row? Can this be done using SQL? Can be done in a stored procedure, or (more easily) in an application language. PB - Khai Doan wrote: I have a column which contains a list of IDs separated by commas. How can I take the content of this column, and insert it into a temporary table so that each ID is on a separate row? Can this be done using SQL? Thanks! Khai No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.37/2130 - Release Date: 05/23/09 07:00:00
Re: Single query possible?
Hi Frank,
Frank Rust wrote:
Hi there, I'm just trying to solve a little problem:
There are two tables (simplified):
CREATE TABLE article (ID int, Title varchar, ...other_data );
CREATE TABLE author (ID int,name varchar,article_ID int,...other_data );
Now I have a lot of articles. They have one or more autors, e.g.
INSERT INTO article (ID,Title)
VALUES (123,'test article'),
(124,'another article'),
(125,'third article');
INSERT INTO author (ID,name,article_ID)
VALUES (1,'peter', 123),(2,'paul', 123),(3,'mary',123);
INSERT INTO author (ID,name,article_ID)
VALUES (4,'peter', 124),(5,'paul',124);
INSERT INTO author (ID,name,article_ID)
VALUES (6,'peter', 125),(7,'mary',125),(8,'tom',125);
I try to build a query to do the following: find all articles where
'peter' and 'mary' are among the authors;
or find all articles where 'peter' is an author but 'tom' not;
etc.
If there would be no possibility to do that, how could I redesign my
tables?
It is definitely possible to write these as single queries and there are
many ways to do it. For most queries involving an AND relationship
between two rows, you will end up using an INNER JOIN. For those where
you want value exists here but not there queries then you will be
using one of the OUTER JOIN operators.
Here is one way to rewrite your first problem (find articles with both
'peter' and 'mary' as authors):
SELECT DISTINCT a1.article_id
FROM author a1
INNER JOIN author a2
ON a1.id != a2.id
AND a1.article_id = a2.article_id
WHERE a1.name in ('Peter','Mary');
And one way to encode your second problem (articles with 'peter' that
did not also include 'tom')
SELECT DISTINCT a1.article_id
FROM author a1
LEFT JOIN author a2
ON a2.name = 'Tom'
AND a1.article_id = a2.article_id
WHERE a1.name = 'Peter'
AND a2.id is NULL;
I hope this gives you some ideas of the patterns you can use to solve
these types of problems.
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to either update or insert in a single query?
Have you look at replace ? http://dev.mysql.com/doc/refman/5.0/en/replace.html Carlos Douglas Pearson wrote: Apologies if this is a dumb question, but is it possible to write a single query that either updates certain columns in a row, or adds an entirely new row if there is none already? I seem to be running into this a lot, and so far I've solved it by: 1) run UPDATE table SET x,y WHERE some row 2) if rowsChanged == 0 then run the INSERT It just feels like there must be a way to do this more efficiently. Thanks, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to either update or insert in a single query?
This part of the manual might be of use to you:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
REPLACE works exactly like INSERT, except that if an old row in the table
has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the
old row is deleted before the new row is inserted. See Section 13.2.4,
“INSERT Syntax”.
REPLACE is a MySQL extension to the SQL standard. It either inserts, or
deletes and inserts. For another MySQL extension to standard SQL — that
either inserts or updates — see Section 13.2.4.3, “INSERT ... ON DUPLICATE
KEY UPDATE Syntax”.
Note that unless the table has a PRIMARY KEY or UNIQUE index, using a
REPLACE statement makes no sense. It becomes equivalent to INSERT, because
there is no index to be used to determine whether a new row duplicates
another.
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
On Fri, April 13, 2007 08:14, Douglas Pearson wrote:
Apologies if this is a dumb question, but is it possible to write a single
query that either updates certain columns in a row, or adds an entirely
new
row if there is none already?
I seem to be running into this a lot, and so far I've solved it by:
1) run UPDATE table SET x,y WHERE some row
2) if rowsChanged == 0 then run the INSERT
It just feels like there must be a way to do this more efficiently.
Thanks,
Doug
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to either update or insert in a single query?
REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL — that either inserts or updates — see Section 13.2.4.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”. Take note!! If you're using triggers, foreign key constraints or defaults on columns, it is better to use ON DUPLICATE cause it will update instead of deleting the row first. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is it possible to either update or insert in a single query?
You could try a stored procedure that either inserts a new row or updates an existing row: CREATE PROCEUDRE InsertOrUpdateRecord(IN NewID INT, ... other params ... ) BEGIN IF NOT EXISTS (SELECT ID FROM myTable WHERE ID = NewID) THEN BEGIN INSERT INTO myTable () END; ELSE BEGIN UPDATE myTable SET . END; END IF; END; Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology -Original Message- From: Douglas Pearson [mailto:[EMAIL PROTECTED] Sent: Friday, April 13, 2007 2:14 AM To: [EMAIL PROTECTED] Subject: Is it possible to either update or insert in a single query? Apologies if this is a dumb question, but is it possible to write a single query that either updates certain columns in a row, or adds an entirely new row if there is none already? I seem to be running into this a lot, and so far I've solved it by: 1) run UPDATE table SET x,y WHERE some row 2) if rowsChanged == 0 then run the INSERT It just feels like there must be a way to do this more efficiently. Thanks, Doug -- 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: Is it possible to either update or insert in a single query?
Thanks Mogens. I was aware of REPLACE but it was the non-standard ON
DUPLICATE KEY UPDATE that I was looking for.
Thanks,
Doug
-Original Message-
From: Mogens Melander [mailto:[EMAIL PROTECTED]
Sent: Friday, April 13, 2007 2:28 AM
To: Douglas Pearson
Cc: [EMAIL PROTECTED]
Subject: Re: Is it possible to either update or insert in a single query?
This part of the manual might be of use to you:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
REPLACE works exactly like INSERT, except that if an old row in the table
has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old
row is deleted before the new row is inserted. See Section 13.2.4, INSERT
Syntax.
REPLACE is a MySQL extension to the SQL standard. It either inserts, or
deletes and inserts. For another MySQL extension to standard SQL - that
either inserts or updates - see Section 13.2.4.3, INSERT ... ON DUPLICATE
KEY UPDATE Syntax.
Note that unless the table has a PRIMARY KEY or UNIQUE index, using a
REPLACE statement makes no sense. It becomes equivalent to INSERT, because
there is no index to be used to determine whether a new row duplicates
another.
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
On Fri, April 13, 2007 08:14, Douglas Pearson wrote:
Apologies if this is a dumb question, but is it possible to write a
single query that either updates certain columns in a row, or adds an
entirely new row if there is none already?
I seem to be running into this a lot, and so far I've solved it by:
1) run UPDATE table SET x,y WHERE some row
2) if rowsChanged == 0 then run the INSERT
It just feels like there must be a way to do this more efficiently.
Thanks,
Doug
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
This message has been scanned for viruses and dangerous content by
OpenProtect(http://www.openprotect.com), and is believed to be clean.
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow seach - Possible better query
Albert, it seems like the first query could be simplified, like so: SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) ORDER BY updated DESC LIMIT 50 This might help it hit the index you've created on the UPDATED column. I know there have been bugs here and there with the optimizer and IN subqueries not hitting indices, especially in earlier 4.1.x releases. If you have a large number of rows with recent values for the UPDATED column, MySQL may be doing a table scan. This is an instance where test/development scenarios don't always work quite as well as real data. However, 6-12 seconds for a 5000 row table does seem slow ... Can you post the output of EXPLAIN query ? That will help us see how MySQL is planning to run your query, and may also reveal a little bit about your data. Thanks, Dan On 10/31/06, Albert Padley [EMAIL PROTECTED] wrote: I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query that works: SELECT id, subject, updated FROM mrldisc WHERE (id IN (SELECT thread_id FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR))) ORDER BY updated DESC LIMIT 50 Here is the table schema: CREATE TABLE `mrldisc` ( `id` int(14) NOT NULL auto_increment, `thread_id` int(14) default NULL, `author` varchar(100) NOT NULL default '', `state` varchar(25) NOT NULL default '', `subject` varchar(100) NOT NULL default '', `message` longtext NOT NULL, `date` varchar(50) NOT NULL default '', `jdate` varchar(50) default '-00-00 00:00:00', `statespecific` varchar(25) NOT NULL default '', `mainthread` char(1) NOT NULL default '', `email` varchar(100) NOT NULL default '', `threadcount` int(11) NOT NULL default '0', `updated` timestamp NOT NULL default CURRENT_TIMESTAMP, `ip_address` varchar(15) NOT NULL default '', PRIMARY KEY (`id`), KEY `thread_id` (`thread_id`), KEY `statespecific` (`statespecific`), KEY `state` (`state`), KEY `updated` (`updated`), KEY `email` (`email`), KEY `mainthread` (`mainthread`), KEY `jdate` (`jdate`), FULLTEXT KEY `author` (`author`), FULLTEXT KEY `message` (`message`), FULLTEXT KEY `subject` (`subject`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have tried other queries like: SELECT DISTINCT * FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) GROUP BY thread_id ORDER BY updated DESC LIMIT 50 This one was close, but returned the oldest row in the group rather than the most recent row. I've also thought about doing the search with a temporary table, but haven't gotten very far with that. Any pointers would be greatly appreciated. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow seach - Possible better query
Dan, On Oct 31, 2006, at 3:41 PM, Dan Buettner wrote: Albert, it seems like the first query could be simplified, like so: SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) ORDER BY updated DESC LIMIT 50 This query won't work. The table contains threaded messages. The thread_id refers back to the id of the first message in the thread. The above query would return a list of all messages in the last 48 hours when all I want is to return the original message in the thread. This might help it hit the index you've created on the UPDATED column. I know there have been bugs here and there with the optimizer and IN subqueries not hitting indices, especially in earlier 4.1.x releases. If you have a large number of rows with recent values for the UPDATED column, MySQL may be doing a table scan. This is an instance where test/development scenarios don't always work quite as well as real data. However, 6-12 seconds for a 5000 row table does seem slow ... In our test database it's probably less than 50 rows that have been updated in the last 48 hours. Can you post the output of EXPLAIN query ? That will help us see how MySQL is planning to run your query, and may also reveal a little bit about your data. 1 PRIMARY mrldisc index updated 4 4888Using where 2 DEPENDENT SUBQUERY mrldisc index_subquery thread_id,updated thread_id 5 func 8 Using index; Using where Thanks. Al Thanks, Dan On 10/31/06, Albert Padley [EMAIL PROTECTED] wrote: I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query that works: SELECT id, subject, updated FROM mrldisc WHERE (id IN (SELECT thread_id FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR))) ORDER BY updated DESC LIMIT 50 Here is the table schema: CREATE TABLE `mrldisc` ( `id` int(14) NOT NULL auto_increment, `thread_id` int(14) default NULL, `author` varchar(100) NOT NULL default '', `state` varchar(25) NOT NULL default '', `subject` varchar(100) NOT NULL default '', `message` longtext NOT NULL, `date` varchar(50) NOT NULL default '', `jdate` varchar(50) default '-00-00 00:00:00', `statespecific` varchar(25) NOT NULL default '', `mainthread` char(1) NOT NULL default '', `email` varchar(100) NOT NULL default '', `threadcount` int(11) NOT NULL default '0', `updated` timestamp NOT NULL default CURRENT_TIMESTAMP, `ip_address` varchar(15) NOT NULL default '', PRIMARY KEY (`id`), KEY `thread_id` (`thread_id`), KEY `statespecific` (`statespecific`), KEY `state` (`state`), KEY `updated` (`updated`), KEY `email` (`email`), KEY `mainthread` (`mainthread`), KEY `jdate` (`jdate`), FULLTEXT KEY `author` (`author`), FULLTEXT KEY `message` (`message`), FULLTEXT KEY `subject` (`subject`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have tried other queries like: SELECT DISTINCT * FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) GROUP BY thread_id ORDER BY updated DESC LIMIT 50 This one was close, but returned the oldest row in the group rather than the most recent row. I've also thought about doing the search with a temporary table, but haven't gotten very far with that. Any pointers would be greatly appreciated. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow seach - Possible better query PROBLEM SOLVED
Dan, Actually you were on the right track. I changed your suggested query to the following and it seems to work and is a lot quicker. SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW (), INTERVAL 48 HOUR) AND mainthread = 'T' ORDER BY updated DESC LIMIT 50 Thanks. Al On Oct 31, 2006, at 4:01 PM, Albert Padley wrote: Dan, On Oct 31, 2006, at 3:41 PM, Dan Buettner wrote: Albert, it seems like the first query could be simplified, like so: SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) ORDER BY updated DESC LIMIT 50 This query won't work. The table contains threaded messages. The thread_id refers back to the id of the first message in the thread. The above query would return a list of all messages in the last 48 hours when all I want is to return the original message in the thread. This might help it hit the index you've created on the UPDATED column. I know there have been bugs here and there with the optimizer and IN subqueries not hitting indices, especially in earlier 4.1.x releases. If you have a large number of rows with recent values for the UPDATED column, MySQL may be doing a table scan. This is an instance where test/development scenarios don't always work quite as well as real data. However, 6-12 seconds for a 5000 row table does seem slow ... In our test database it's probably less than 50 rows that have been updated in the last 48 hours. Can you post the output of EXPLAIN query ? That will help us see how MySQL is planning to run your query, and may also reveal a little bit about your data. 1 PRIMARY mrldisc index updated 4 4888Using where 2 DEPENDENT SUBQUERY mrldisc index_subquery thread_id,updated thread_id 5 func 8 Using index; Using where Thanks. Al Thanks, Dan On 10/31/06, Albert Padley [EMAIL PROTECTED] wrote: I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query that works: SELECT id, subject, updated FROM mrldisc WHERE (id IN (SELECT thread_id FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR))) ORDER BY updated DESC LIMIT 50 Here is the table schema: CREATE TABLE `mrldisc` ( `id` int(14) NOT NULL auto_increment, `thread_id` int(14) default NULL, `author` varchar(100) NOT NULL default '', `state` varchar(25) NOT NULL default '', `subject` varchar(100) NOT NULL default '', `message` longtext NOT NULL, `date` varchar(50) NOT NULL default '', `jdate` varchar(50) default '-00-00 00:00:00', `statespecific` varchar(25) NOT NULL default '', `mainthread` char(1) NOT NULL default '', `email` varchar(100) NOT NULL default '', `threadcount` int(11) NOT NULL default '0', `updated` timestamp NOT NULL default CURRENT_TIMESTAMP, `ip_address` varchar(15) NOT NULL default '', PRIMARY KEY (`id`), KEY `thread_id` (`thread_id`), KEY `statespecific` (`statespecific`), KEY `state` (`state`), KEY `updated` (`updated`), KEY `email` (`email`), KEY `mainthread` (`mainthread`), KEY `jdate` (`jdate`), FULLTEXT KEY `author` (`author`), FULLTEXT KEY `message` (`message`), FULLTEXT KEY `subject` (`subject`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have tried other queries like: SELECT DISTINCT * FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) GROUP BY thread_id ORDER BY updated DESC LIMIT 50 This one was close, but returned the oldest row in the group rather than the most recent row. I've also thought about doing the search with a temporary table, but haven't gotten very far with that. Any pointers would be greatly appreciated. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to combine mysql with nis?
I'm far from an expert on the subject, but judging by a quick Google search for 'NIS mysql backend', people have been able to do this via OpenLDAP. I just thought I'd send you a starting point. On 7/19/06, Cheng-Lin Yang [EMAIL PROTECTED] wrote: Hi all, I'd like to know if it's possbile to make MySQL auth the account through system account via NIS? I want the user in my system who can automatically have their own mysql account by using the same password on the system. Please kindly help me on this problem. Thank you in advanced! :) Regards, Cheng-Lin Yang -- 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: Is it possible to combine mysql with nis?
In the last episode (Jul 19), Cheng-Lin Yang said: I'd like to know if it's possbile to make MySQL auth the account through system account via NIS? I want the user in my system who can automatically have their own mysql account by using the same password on the system. Please kindly help me on this problem. Thank you in advanced! :) Mysql can only authenticate to its own internal user table. See also http://bugs.mysql.com/bug.php?id=4703 -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: It is possible to copy the database of mysql-3.22.32 on version 4.0.20? There is possibility that they work?
Hello. You may use mysqlhotcopy. May be it is better to use select ... into outfile and load data infile. See: http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html http://dev.mysql.com/doc/mysql/en/SELECT.html But The Right Way(tm) is using a mysqldump utility. It is possible to copy the database of mysql-3.22.32 (/usr/local/mysql/var) on version 4.0.20 (/usr/local/mysql_new/var)? There is possibility that they work? My O.S. is Solaris 8 Thank's Alessio P.S. In that case I must use the function mysqlhotcopy? I don't have the possibility to use mysqldump --all-databases all_databases.sql[EMAIL PROTECTED] wrote: -- 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: Is it possible to have Undeletable Records?
zzapper [EMAIL PROTECTED] wrote: Is it possible to have undeletable/unmodifiable records in a table of otherwise modifiable deleteable createable records? At present I do it at the Update/Delete level where I have clauses which prevent certain records being changed. I suppose I could also have an extra boolean field Record Read only, but is there anyway to specify at the data level? No, no such feature. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Is it possible to have Undeletable Records?
I don't think so. I think your current method of either storing it in the query or with a boolean field is your best bet. What I've seen done in the past is that records get marked with a user level and there is either one or a group of users who are allowed to delete or modify those otherwise permanent records. Wes On Fri, 03 Sep 2004 11:38:35 +0100, zzapper [EMAIL PROTECTED] wrote: Hi Y'All Is it possible to have undeletable/unmodifiable records in a table of otherwise modifiable deleteable createable records? At present I do it at the Update/Delete level where I have clauses which prevent certain records being changed. I suppose I could also have an extra boolean field Record Read only, but is there anyway to specify at the data level? zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to have Undeletable Records?
Wesley Furgiuele wrote: I don't think so. I think your current method of either storing it in the query or with a boolean field is your best bet. What I've seen done in the past is that records get marked with a user level and there is either one or a group of users who are allowed to delete or modify those otherwise permanent records. Wes On Fri, 03 Sep 2004 11:38:35 +0100, zzapper [EMAIL PROTECTED] wrote: Hi Y'All Is it possible to have undeletable/unmodifiable records in a table of otherwise modifiable deleteable createable records? At present I do it at the Update/Delete level where I have clauses which prevent certain records being changed. I suppose I could also have an extra boolean field Record Read only, but is there anyway to specify at the data level? zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips Here is an off the wall idea. Create a MERGE table for the data. Put the undeletable data into one table then pack it. Put the other data into a normal myisam file. I have no idea if this will work. But I think it is worth a try. Good Luck -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL TEXT - Possible Corruption
Hi again, Sorry for the double post. I created a new database, recreated all my tables, repopulated them, and it seems to work now. Any ideas as to what was causing this? Paul From: Paul Higgins [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: MySQL TEXT - Possible Corruption Date: Sat, 04 Sep 2004 23:42:51 -0400 Hi, I've been having a problem using the TEXT column type. I'm trying to insert a serialized PHP object into this field. However, when I execute the insert command (via a php script), I cannot view the data until I restart the service. I query the database using the command prompt, but the data that was just entered does not appear. PHP can access the updated data. I had this problem before with another table, but somehow that one is now fixed. Any ideas? Thanks, Paul _ On the road to retirement? Check out MSN Life Events for advice on how to get there! http://lifeevents.msn.com/category.aspx?cid=Retirement -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Again: Is possible set variable 'read_only' within my.ini?
Renato Cramer wrote: Hello All, I'm using MySQL 4.1.0, InnoDB tables and Win2K. I need set read_only=1 and want do via option file, it's possible? According to http://dev.mysql.com/doc/mysql/en/Server_system_variables.html: * read_only ... This variable was added in MySQL 4.0.14. * I already tried put of both modes: read_only=1 and set-variable = read_only=1 But neither functioned. Anybody will can help me? Thanks in advance, Renato Cramer. 4.0.14 was released after 4.1.0, so 4.1.0 doesn't have this feature. Both 4.1.1 and 4.1.2 were released after 4.0.14, so they should both be able to do it. You can see the release dates at: http://dev.mysql.com/doc/mysql/en/News.html Regards, Harrison -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RES: Is possible Autocommit within my.ini?
At 15:14 -0300 5/21/04, Renato Cramer wrote: I'm using MySQL 4.1.0 and InnoDB tables. I need set autocommit=0 and want do via option file. I know what SET AUTOCOMMIT=0 works fine, however I prefer by the option file. Is possible? In 4.1.0, no. You'll be able to do this in 4.1.2. See the description for the init_connect variable here: http://dev.mysql.com/doc/mysql/en/Server_system_variables.html Thanks, Renato. -Mensagem original- De: Paul DuBois [mailto:[EMAIL PROTECTED] Enviada em: sexta-feira, 21 de maio de 2004 15:03 Para: Renato Cramer; Lista MySQL (E-mail) Assunto: Re: Is possible Autocommit within my.ini? At 14:34 -0300 5/21/04, Renato Cramer wrote: Hi All, I already looking for Autocommit within my.ini at documentation and archives of the list, and not found. Anybody can help me? What is it that you want to accomplish? -- 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: Is it possible to get a timestamp from the server ?
Yea do a search on the mysql.com web site for date functions (or just click here http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html ) its loaded with many examples and descriptions of useful functions. Built in date calculations are one thing I really enjoy with mysql. Best, Yoed -Original Message- From: Greg Hosler [mailto:[EMAIL PROTECTED] Sent: Friday, April 23, 2004 7:08 PM To: [EMAIL PROTECTED] Subject: Is it possible to get a timestamp from the server ? Hi, I have a database application that I am porting to MySQL. one of the things that the application needs to do is to get a timestamp from the server, so that all instances of the application across a network can timestamp cewrtain records using a common source for the timestamp. e.g. the sql server. In the past, under Informix, we did the following sql: SELECT distinct current year to second FROM systables I've looked around, and I'm not seeing anything equivalent under MySQL. Have I hopefully missed something ? thx for any suggestions / hints / pointers. best rgds, -Greg Hosler +-+ You can release software that's good, software that's inexpensive, or software that's available on time. You can usually release software that has 2 of these 3 attributes -- but not all 3. | Greg Hosler [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]
RE: Is it possible to get a timestamp from the server ?
To get the current year, use: SELECT DATE_FORMAT(NOW(), '%Y'); Basically you can use the DATE_FORMAT to show the present time as desired. --bmansell -Original Message- From: Greg Hosler [mailto:[EMAIL PROTECTED] Sent: Friday, April 23, 2004 5:08 PM To: [EMAIL PROTECTED] Subject: Is it possible to get a timestamp from the server ? Hi, I have a database application that I am porting to MySQL. one of the things that the application needs to do is to get a timestamp from the server, so that all instances of the application across a network can timestamp cewrtain records using a common source for the timestamp. e.g. the sql server. In the past, under Informix, we did the following sql: SELECT distinct current year to second FROM systables I've looked around, and I'm not seeing anything equivalent under MySQL. Have I hopefully missed something ? thx for any suggestions / hints / pointers. best rgds, -Greg Hosler +-+ You can release software that's good, software that's inexpensive, or software that's available on time. You can usually release software that has 2 of these 3 attributes -- but not all 3. | Greg Hosler [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]
Re: Is this possible?
On Wed, 2004-03-31 at 16:08, Chris Boget wrote: I have 2 tables (looks best in fixed width font): test_items +---+--+ | name | rec_num | +---+--+ | Book | 1| | Game | 2| +---+--+ test_attributes +---++ | item_rec_num | attribute | +---++ | 1 | Thick | | 1 | Tall | | 1 | Green | | 2 | Narrow | | 2 | Yellow | +---++ How can I query the above tables so that if I select name and attribute, the result set comes out looking like this: +---+---+ | name | attribute | +---+---+ | Book | Thick/Tall/Green | | Game | Narrow/Yellow | +---+---+ Because every join query I've been able to think of always returns the result set as follows: +--+---+ | name | attribute | +--+---+ | Book | Thick | | Book | Tall | | Book | Green | | Game | Narrow| | Game | Yellow| +--+---+ So my question, is it even possible to do what I'd like to do? thnx, Chris Take a look at qiktable here www.qiktable.com If you are putting that data into a html table qiktable can generate the table on the fly from the data coming back from your query. It is highly configurable, and I believe it will do what you are trying to accomplish. Sean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is this possible?
GROUP_CONCAT() is in 4.1. :-) Oh, so close yet so far. We're running 4.0.14 and my company has no plans on upgrading in the near future. *sigh* Thanks for the info, however!! Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is this possible?
You would need an aggregate concat() function I think its in 5.0 m -Original Message- From: Chris Boget [mailto:[EMAIL PROTECTED] Sent: Thursday, April 01, 2004 7:08 AM To: MySQL Subject: Is this possible? I have 2 tables (looks best in fixed width font): test_items +---+--+ | name | rec_num | +---+--+ | Book | 1| | Game | 2| +---+--+ test_attributes +---++ | item_rec_num | attribute | +---++ | 1 | Thick | | 1 | Tall | | 1 | Green | | 2 | Narrow | | 2 | Yellow | +---++ How can I query the above tables so that if I select name and attribute, the result set comes out looking like this: +---+---+ | name | attribute | +---+---+ | Book | Thick/Tall/Green | | Game | Narrow/Yellow | +---+---+ Because every join query I've been able to think of always returns the result set as follows: +--+---+ | name | attribute | +--+---+ | Book | Thick | | Book | Tall | | Book | Green | | Game | Narrow| | Game | Yellow| +--+---+ So my question, is it even possible to do what I'd like to do? thnx, Chris -- 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: Is this possible?
Hi, GROUP_CONCAT() is in 4.1. :-) Matt - Original Message - From: m.pheasant Sent: Wednesday, March 31, 2004 5:26 PM Subject: RE: Is this possible? You would need an aggregate concat() function I think its in 5.0 m -Original Message- From: Chris Boget [mailto:[EMAIL PROTECTED] Sent: Thursday, April 01, 2004 7:08 AM To: MySQL Subject: Is this possible? I have 2 tables (looks best in fixed width font): test_items +---+--+ | name | rec_num | +---+--+ | Book | 1| | Game | 2| +---+--+ test_attributes +---++ | item_rec_num | attribute | +---++ | 1 | Thick | | 1 | Tall | | 1 | Green | | 2 | Narrow | | 2 | Yellow | +---++ How can I query the above tables so that if I select name and attribute, the result set comes out looking like this: +---+---+ | name | attribute | +---+---+ | Book | Thick/Tall/Green | | Game | Narrow/Yellow | +---+---+ Because every join query I've been able to think of always returns the result set as follows: +--+---+ | name | attribute | +--+---+ | Book | Thick | | Book | Tall | | Book | Green | | Game | Narrow| | Game | Yellow| +--+---+ So my question, is it even possible to do what I'd like to do? thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: is it possible...
Boy do I feel dumb. :^) -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Monday, February 09, 2004 4:54 PM To: [EMAIL PROTECTED] Subject: RE: is it possible... UPDATE EMAIL_TEST_LIST SET EMAIL_DATE = curdate(); -Original Message- From: Ligaya Turmelle [mailto:[EMAIL PROTECTED] Sent: Sunday, February 08, 2004 10:42 PM To: [EMAIL PROTECTED] Subject: is it possible... Is it possible to have an update statement that uses curdate()? Example: UPDATE EMAIL_TEST_LIST SET EMAIL_DATE = (SELECT curdate()); -- 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]
RE: is it possible...
UPDATE EMAIL_TEST_LIST SET EMAIL_DATE = curdate(); -Original Message- From: Ligaya Turmelle [mailto:[EMAIL PROTECTED] Sent: Sunday, February 08, 2004 10:42 PM To: [EMAIL PROTECTED] Subject: is it possible... Is it possible to have an update statement that uses curdate()? Example: UPDATE EMAIL_TEST_LIST SET EMAIL_DATE = (SELECT curdate()); -- 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: is this possible?
To drop all tables, yes do a drop database. To drop all with something like a exclude, no. What you can do is an export of the tables you don't want to drop, then do a drop database + create database + import Marc. -Message d'origine- De : Nitin Mehta [mailto:[EMAIL PROTECTED] Envoyé : vendredi 16 janvier 2004 06:28 À : [EMAIL PROTECTED] Objet : is this possible? hello everybody, i was wondering, if there's a single command to drop all or selected tables from the database, something like, where i can give the table names (1-2-3) not to be deleted. Thanx in advance Nitin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is this possible?
thanx for reply but not a good idea. Though i did it through PHP script Nitin - Original Message - From: Mechain Marc [EMAIL PROTECTED] To: Nitin Mehta [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 16, 2004 1:38 PM Subject: RE: is this possible? To drop all tables, yes do a drop database. To drop all with something like a exclude, no. What you can do is an export of the tables you don't want to drop, then do a drop database + create database + import Marc. -Message d'origine- De : Nitin Mehta [mailto:[EMAIL PROTECTED] Envoyé : vendredi 16 janvier 2004 06:28 À : [EMAIL PROTECTED] Objet : is this possible? hello everybody, i was wondering, if there's a single command to drop all or selected tables from the database, something like, where i can give the table names (1-2-3) not to be deleted. Thanx in advance Nitin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is It Possible To Change the Value of A Particular Field Manually?
Carlonie, Use the UPDATE. I assume you mean by database here you mean the database table named members... if so, try the following query. UPDATE members SET user_name = 'john_doe' WHERE user_name = 'John Doe'; -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Caroline Jen wrote: Hi, I am not a database person. I have a database called members. One of the fields in this database is user_name. There are a number of records in this database. Under the field user_name, I would like to make some changes manually; for example, I want to change John Doe to john_doe Is it possible to do it? How do I do it? Thank you very much in advance. __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is It Possible To Change the Value of A Particular Field Manually?
Caroline Jen wrote: Hi, I am not a database person. I have a database called members. One of the fields in this database is user_name. There are a number of records in this database. Under the field user_name, I would like to make some changes manually; for example, I want to change John Doe to john_doe Is it possible to do it? How do I do it? Thank you very much in advance. Yes. You can specify the records you want with a WHERE clause. First try a SELECT to test your WHERE clause to make sure you get just the record(s) you want: SELECT * FROM members WHERE user_name = 'John Doe'; As long as that looks right, then use UPDATE to modify the record(s): UPDATE members SET user_name='john_doe' WHERE user_name = 'John Doe'; See http://www.mysql.com/doc/en/UPDATE.html in the manual for more. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is It Possible To Change the Value of A Particular Field Manually?
you can optionally use MySQL Front to view and edit data in GUI mode get it free of cost athttp://www.mysqlfront.de/ Enjoy Nitin - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Caroline Jen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 11:28 AM Subject: Re: Is It Possible To Change the Value of A Particular Field Manually? Caroline Jen wrote: Hi, I am not a database person. I have a database called members. One of the fields in this database is user_name. There are a number of records in this database. Under the field user_name, I would like to make some changes manually; for example, I want to change John Doe to john_doe Is it possible to do it? How do I do it? Thank you very much in advance. Yes. You can specify the records you want with a WHERE clause. First try a SELECT to test your WHERE clause to make sure you get just the record(s) you want: SELECT * FROM members WHERE user_name = 'John Doe'; As long as that looks right, then use UPDATE to modify the record(s): UPDATE members SET user_name='john_doe' WHERE user_name = 'John Doe'; See http://www.mysql.com/doc/en/UPDATE.html in the manual for more. Michael -- 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: is it possible?
Eugene R. Miller wrote: This is kind of a silly question is there an easy way to ... SELECT Status, rating, COUNT(*) FROM song GROUP BY pldupldqd, rating ORDER BY Status, rating DESC This gives me all the information I need ... What I would like to do is something... like SELECT rating, count(WHERE pldupldqd = 0), count (WHERE pldupldqd = 1), COUNT (WHERE pldupldqd = 2) FROM song GROUP BY rating The idea is to get... 3 different columns counting what it equals. Yes, it's possible, the solution is very easy: SELECT rating, sum(CASE pldupldqd WHEN 0 THEN 1 ELSE 0 END), sum(CASE pldupldqd WHEN 1 THEN 1 ELSE 0 END), sum(CASE pldupldqd WHEN 2 THEN 1 ELSE 0 END), Alternatively, you case use function IF(), instead of CASE. Erm --- www.the-erm.com -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Barkov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia ___/ www.mysql.com +7-912-856-80-21 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to pipe wildcard searches into mySQL?
At 21:26 -0400 9/3/03, Dan Anderson wrote: I'm writing a search engine to query a database to my site. I know how to use a WHERE product_name = foo if somebody enters the exact product name, but how could I do something like: WHERE product_name = *foo* so all results containing foo in product name would be returned and not just products named only foo? Thanks in Advance, Dan Anderson Sounds like you want to use LIKE or REGEXP, which are described here: http://www.mysql.com/doc/en/String_comparison_functions.html Either of these expressions should do it: WHERE product_name LIKE '%foo%' or WHERE product_name REGEXP 'foo' -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is query possible? (Newbie)
Grant, Could you please be a bit more specific? What do you mean when you say I want to create a query that will take all the fields in. If no end_date exists then set to NULL.? Thanks -Original Message- From: Grant Cooper [mailto:[EMAIL PROTECTED] Sent: Friday, August 15, 2003 4:11 AM To: [EMAIL PROTECTED] Subject: Is query possible? (Newbie) I have 2 tables used for an online calendar... first table fields: primary_key , start_date, event_name, event_description second table fields: primary_key, end_date Tables fields are shortened and can't be changed. My second table only contains events that have a end date. I want to create a query that will take all the fields in. If no end_date exists then set to NULL. Been playing with it all day. Hoping some advance function exists. I thought of using a temp table but there must be a better way. -- 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: Is query possible? (Newbie)
If I understand your question correctly, what you want is: SELECT first_table.*, second_table.end_date FROM first_table LEFT JOIN second_table ON first_table.primary_key=second_table.primary_key cheers, Jan Sometime recently Grant Cooper said: I have 2 tables used for an online calendar... first table fields: primary_key , start_date, event_name, event_description second table fields: primary_key, end_date Tables fields are shortened and can't be changed. My second table only contains events that have a end date. I want to create a query that will take all the fields in. If no end_date exists then set to NULL. Been playing with it all day. Hoping some advance function exists. I thought of using a temp table but there must be a better way. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Janice Wright Ingenta plc [EMAIL PROTECTED] Tel: +44 (0) 01865 799114 http://www.ingentaselect.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is query possible? (Newbie)
I have 2 tables used for an online calendar... first table fields: primary_key , start_date, event_name, event_description second table fields: primary_key, end_date Tables fields are shortened and can't be changed. My second table only contains events that have a end date. I want to create a query that will take all the fields in. If no end_date exists then set to NULL. Been playing with it all day. Hoping some advance function exists. I thought of using a temp table but there must be a better way. I am confused by your question. It think it is missing words. If you are trying to select all records from first_table that do not have a record in second_table you can use a left join and is null... SELECT * from first_table LEFT JOIN second_table USING (primary_key) WHERE second_table.primary_key IS NULL Maybe I'm missing something here, but I don't see why you would want to split your tables up that way. You can't be saving that much room, and I don't think it really goes with standard normalization conventions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is query possible? (Newbie)
On Thursday, August 14, 2003, at 08:04 PM, Jennifer Goodie wrote: I have 2 tables used for an online calendar... first table fields: primary_key , start_date, event_name, event_description second table fields: primary_key, end_date Tables fields are shortened and can't be changed. My second table only contains events that have a end date. I want to create a query that will take all the fields in. If no end_date exists then set to NULL. Been playing with it all day. Hoping some advance function exists. I thought of using a temp table but there must be a better way. I am confused by your question. It think it is missing words. If you are trying to select all records from first_table that do not have a record in second_table you can use a left join and is null... SELECT * from first_table LEFT JOIN second_table USING (primary_key) WHERE second_table.primary_key IS NULL Maybe I'm missing something here, but I don't see why you would want to split your tables up that way. You can't be saving that much room, and I don't think it really goes with standard normalization conventions. If you don't want to join the two, you could use the query above to find out all of the event ID's that you need to add to the second field. The easiest way to deal with this would be to modify Jennifer's to: SELECT first_table.primary_key from first_table LEFT JOIN second_table USING (primary_key) WHERE second_table.primary_key IS NULL and export it, and then just straight import it into your second_table. If you leave what you're putting into the second_table.end_date blank, it should come through as NULL. Although, I would also definitely suggest joining the two tables. FWIW, you can do this fairly easily by: CREATE TABLE new_table SELECT first_table.primary_key AS primary_key, first_table.start_date AS start_date, first_table.event_name AS event_name, first_table.event_description AS event_description second_table.end_date AS end_date FROM first_table LEFT JOIN second_table USING (primary_key); Although this is using extremely redundant syntax, this should give you one table, where all of the end_dates that's aren't populated in second_table end up being NULL. From there you could run a query: SELECT * FROM new_table WHERE end_date IS NULL; Good luck, -Cameron Wilhelm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to dump images into a database?
take a look at: http://www.php4.com/forums/viewtopic.php?t=6 or search the mailing list archive.. there are plenty of threads talking about this: For list archives: http://lists.mysql.com/mysql On Wed, 9 Jul 2003, Dan Anderson wrote: Can anyone point me to a reference on how to insert images into a column in a mySQL database -- or is that not possible? Thanks in advance, Dan Anderson -- 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: Is it possible to dump images into a database?
Yes it is. If you are using VB look at www.vbmysql.com/articles/blobaccessvb.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Dan Anderson [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 8:49 AM To: [EMAIL PROTECTED] Subject: Is it possible to dump images into a database? Can anyone point me to a reference on how to insert images into a column in a mySQL database -- or is that not possible? Thanks in advance, Dan Anderson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is it possible to dump images into a database?
Use LOAD DATA INFILE if you want to import it from the filesystem or use INSERT/UPDATE if you do it from an application (e.g. perl, php). Make sure that the column which stores them is defined as BLOB and not as TEXT. Look up the BLOB definition for size limitation and variations on BLOBs. For getting them out of the DB use SELECT .. INTO DUMPFILE to get them onto the file system or normal SELECT for applications. HINT: normally it is MUCH better to store the image on the file system and only store the path to the image in the database. But this again depends on what you want to achieve. Cheers /rudy -Original Message- From: Dan Anderson [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 16:49 To: [EMAIL PROTECTED] Subject: Is it possible to dump images into a database? Can anyone point me to a reference on how to insert images into a column in a mySQL database -- or is that not possible? Thanks in advance, Dan Anderson -- 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: Is it possible to refresh data in a mySQL data via a script?
On Fri, 2003-06-20 at 12:16, Barbara Andrew wrote: snip I have three books on mySQL, they all talk about how to construct SQL statements and I'm fine with that. What I can't figure out is how to get those statements to the right place without having to do it manually. This would be the domain of some other tool, such as a shell script, or a commandline php/python/perl etc script that could be run nightly using a scheduling tool ie cron, at etc. You could also have mysql do the import: use databasename; drop table if exists myTable; create table myTable ( Field1 varchar(50) not null, field2 varchar(25) not null, PRIMARY KEY (Field1), KEY names(field2) ) TYPE=MyISAM; load data infile '/tmp/dataFile' into table myTable FIELDS TERMINATED BY '|'; put the above (or some variation) in a file (filename.mysql), and then have mysql execute those statements nightly: by putting the following into either a one line bash script, or having cron run it directly: mysql filename.mysql Of course, you will probably want to run some checks to make sure the data is in the file before you start Dropping tables etc. Check these out for reference: http://www.mysql.com/doc/en/mysqlimport.html http://www.mysql.com/doc/en/LOAD_DATA.html hth, gabe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is it possible to get around 4 billion row limit
Perhaps this has something to do with it: When you use a MyISAM table, MySQL uses the product of max_rows * avg_row_length to decide how big the resulting table will be. If you don't specify any of the above options, the maximum size for a table will be 4G (or 2G if your operating systems only supports 2G tables). http://www.mysql.com/doc/en/CREATE_TABLE.html Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Martin Waite [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Monday, 09 June, 2003 04:45 Subject: is it possible to get around 4 billion row limit Hi, I want to create a table with a lot (8 billion) small fixed-length records. I thought setting MAX_ROWS in the create table would do this for me, but it looks like it quietly ignores values over 4.2 billion. Is this a hard-limit in MySQL ? Does MySQL 4.0.x have the same limitation ? (MySQL v3.23.51) regards, Martin eg. CREATE TABLE `txn_tag` ( txn_id int unsigned not null, `tag_id` smallint unsigned NOT NULL default '0', `value_id` int(11) NOT NULL default '0', unique KEY (txn_id, tag_id, value_id), KEY `tag_id` (`tag_id`,`value_id`), KEY `value_id` (`value_id`,`tag_id`) ) max_rows=80; show table status like 'txn_tag'\G *** 1. row *** Name: txn_tag Type: MyISAM Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 47244640255 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2003-06-09 09:40:57 Update_time: 2003-06-09 09:40:57 Check_time: NULL Create_options: max_rows=4294967295 Comment: 1 row in set (0.00 sec) -- 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: is it possible to get around 4 billion row limit
Sorry, I meant to include this link, too. http://www.mysql.com/doc/en/Table_size.html Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Martin Waite [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Monday, 09 June, 2003 04:45 Subject: is it possible to get around 4 billion row limit Hi, I want to create a table with a lot (8 billion) small fixed-length records. I thought setting MAX_ROWS in the create table would do this for me, but it looks like it quietly ignores values over 4.2 billion. Is this a hard-limit in MySQL ? Does MySQL 4.0.x have the same limitation ? (MySQL v3.23.51) regards, Martin eg. CREATE TABLE `txn_tag` ( txn_id int unsigned not null, `tag_id` smallint unsigned NOT NULL default '0', `value_id` int(11) NOT NULL default '0', unique KEY (txn_id, tag_id, value_id), KEY `tag_id` (`tag_id`,`value_id`), KEY `value_id` (`value_id`,`tag_id`) ) max_rows=80; show table status like 'txn_tag'\G *** 1. row *** Name: txn_tag Type: MyISAM Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 47244640255 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2003-06-09 09:40:57 Update_time: 2003-06-09 09:40:57 Check_time: NULL Create_options: max_rows=4294967295 Comment: 1 row in set (0.00 sec) -- 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: is it possible to get around 4 billion row limit
Hi Edward, Thanks for the info, but I tried setting the avg_row_length with no effect - I'm pretty sure you only need that for dynamic format tables (with varchars and text). My table is fixed format, and the table does claim to get around the 2/4GB limit. My problem is the maximum number of rows, not the final table size. Max_data_length: 47244640255 Create_options: max_rows=4294967295 I'm guessing that you can't have more than 4294967295 rows in a MyISAM table (or maybe the SQL parser can't handle numbers 2^32 as the value for max_rows). I think I'll resubmit the question as What is the maximum number of rows MySQL can store in a table. thanks, Martin On Tue, 2003-06-10 at 11:54, Becoming Digital wrote: Sorry, I meant to include this link, too. http://www.mysql.com/doc/en/Table_size.html Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Martin Waite [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Monday, 09 June, 2003 04:45 Subject: is it possible to get around 4 billion row limit Hi, I want to create a table with a lot (8 billion) small fixed-length records. I thought setting MAX_ROWS in the create table would do this for me, but it looks like it quietly ignores values over 4.2 billion. Is this a hard-limit in MySQL ? Does MySQL 4.0.x have the same limitation ? (MySQL v3.23.51) regards, Martin eg. CREATE TABLE `txn_tag` ( txn_id int unsigned not null, `tag_id` smallint unsigned NOT NULL default '0', `value_id` int(11) NOT NULL default '0', unique KEY (txn_id, tag_id, value_id), KEY `tag_id` (`tag_id`,`value_id`), KEY `value_id` (`value_id`,`tag_id`) ) max_rows=80; show table status like 'txn_tag'\G *** 1. row *** Name: txn_tag Type: MyISAM Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 47244640255 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2003-06-09 09:40:57 Update_time: 2003-06-09 09:40:57 Check_time: NULL Create_options: max_rows=4294967295 Comment: 1 row in set (0.00 sec) -- 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]
Re: Is this possible?
sub-selects are not yet (to the best of my knowledge) supported in MySQL. =C= * * Cal Evans * Techno-Mage * http://www.calevans.com * - Original Message - From: Frank Peavy [EMAIL PROTECTED] To: Doug Coning [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 6:24 PM Subject: Re: Is this possible? I believe a sub-select with the DISTINCT command would do it. You would have to look up the syntax. Also, it seems that some people seem to be having a problem with sub-selects but I don't have any direct experience to convey. At 01:12 PM 1/23/03 -0600, Doug Coning wrote: Is this possible in MySQL without using a temporary table with multiple SQL commands: I am trying to SELECT the results of multiple columns into 1 column or Alias, group them and sort them. So if I had a table with 4 fields: Cat1, Cat2, Cat3, Cat4 like such: +-+--+-+--+ | Cat1| Cat2 | Cat3| Cat4 | +-+--+-+--+ | F | A | | A | | D | C | H | A | | G | | | | | F | B | I | A | +-+--+-+--+ It would select all the values, merge them into 1 column, group them, and sort them like such so that the result would be a single column, returning only 1 instance of each returned value: ++ | Result | ++ | A | | B | | C | | D | | F | | G | | H | | I | ++ Is there as singe Select command that can do this? Right now I am using a temporary table where I first clear out the values, then copy each column one at a time over into the tables... It works, however, I was wondering if there is an easier way to get the single column result with all the merged information without having to use a temporary table... Thanks, Doug Coning - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is this possible?
On Fri 2003-01-24 at 07:59:16 -0600, [EMAIL PROTECTED] wrote: sub-selects are not yet (to the best of my knowledge) supported in MySQL. To be a bit more precise: They are not supported in any stable release of MySQL. They are going to be supported in Version 4.1, which is still declared alpha. Most basic and not-so-basic tests already work fine, AFAICS. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is this possible?
- Original Message - From: [EMAIL PROTECTED] To: Doug Coning [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 9:15 AM Subject: Re: Is this possible? Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query,queries,smallint If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: I am trying to merge the results of multiple columns into 1 column or Alias, group them and sort them. So if I had a table with 4 fields: Cat1, Cat2, Cat3, Cat4 like such: +-+--+-+--+ | Cat1| Cat2 | Cat3| Cat4 | +-+--+-+--+ | F | A | | A | | D | C | H | A | | G | | | | | F | B | I | A | +-+--+-+--+ It would select all the values, merge them into 1 column, group them, and sort them like such so that the result would be a single column, returning only 1 instance of each returned value: ++ | Result | ++ | A | | B | | C | | D | | F | | G | | H | | I | ++ Is there as singe Select command that can do this? Right now I am using a temporary table where I first clear out the values, then copy each column one at a time over into the tables... It works, however, I was wondering if there is an easier way to get the single column result with all the merged information without having to use a temporary table... Thanks, Doug Coning - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is this possible?
A guess... select t1.cat1 as result from t1 union select t2.cat2 as result from t1 union select t3.cat3 as result from t1 union select t4.cat4 as result from t1 group by result order by result * * Cal Evans * Techno-Mage * http://www.calevans.com * - Original Message - From: Doug Coning [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 1:12 PM Subject: Is this possible? Is this possible in MySQL without using a temporary table with multiple SQL commands: I am trying to SELECT the results of multiple columns into 1 column or Alias, group them and sort them. So if I had a table with 4 fields: Cat1, Cat2, Cat3, Cat4 like such: +-+--+-+--+ | Cat1| Cat2 | Cat3| Cat4 | +-+--+-+--+ | F | A | | A | | D | C | H | A | | G | | | | | F | B | I | A | +-+--+-+--+ It would select all the values, merge them into 1 column, group them, and sort them like such so that the result would be a single column, returning only 1 instance of each returned value: ++ | Result | ++ | A | | B | | C | | D | | F | | G | | H | | I | ++ Is there as singe Select command that can do this? Right now I am using a temporary table where I first clear out the values, then copy each column one at a time over into the tables... It works, however, I was wondering if there is an easier way to get the single column result with all the merged information without having to use a temporary table... Thanks, Doug Coning - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is this possible?
I believe a sub-select with the DISTINCT command would do it. You would have to look up the syntax. Also, it seems that some people seem to be having a problem with sub-selects but I don't have any direct experience to convey. At 01:12 PM 1/23/03 -0600, Doug Coning wrote: Is this possible in MySQL without using a temporary table with multiple SQL commands: I am trying to SELECT the results of multiple columns into 1 column or Alias, group them and sort them. So if I had a table with 4 fields: Cat1, Cat2, Cat3, Cat4 like such: +-+--+-+--+ | Cat1| Cat2 | Cat3| Cat4 | +-+--+-+--+ | F | A | | A | | D | C | H | A | | G | | | | | F | B | I | A | +-+--+-+--+ It would select all the values, merge them into 1 column, group them, and sort them like such so that the result would be a single column, returning only 1 instance of each returned value: ++ | Result | ++ | A | | B | | C | | D | | F | | G | | H | | I | ++ Is there as singe Select command that can do this? Right now I am using a temporary table where I first clear out the values, then copy each column one at a time over into the tables... It works, however, I was wondering if there is an easier way to get the single column result with all the merged information without having to use a temporary table... Thanks, Doug Coning - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to backup a corrupt database without beingroot?
on 12/19/2002 5:00 PM, Paul Nolan at [EMAIL PROTECTED] wrote: I`ve got myself into a bit of a mess by allowing my raq3's 200mb var partition to fill up. This means I can no longer login to my server as root, because a log file of my login cannot be created. Naturally only root can delete or move any files in /var to fix this problem :o Anyway, this caused my MySQL database which was also in /var have problems, and before realising that disk space was the cause, I tried to repair the database using the repair command. Now I have a corrupt database that I cannot export due to Error: Cannot open database.MYD, and I cannot copy the actual data files on the server due to lack of permissions. Is there anyway I can get around this? If I delete the database I should have enough room to login as root again, but I`d really rather have a copy of the database before doing this. Just a offshot guess here, can you ftp in and delete a file? - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to backup a corrupt database without being
I can FTP and telnet in to my site as admin or a user, but all the files in /var are owned by root so cannot be deleted, with the exception of several mysql files owned by mysql, which I also cannot move or delete :( If I could extract the info in the database I could delete that remotely, then restore it after I get root access back again, but as it is now marked corrupt I can't seem to do anything with it. Is there any way of fixing a database with zero room on the partition it is in? I`ve got myself into a bit of a mess by allowing my raq3's 200mb var partition to fill up. This means I can no longer login to my server as root, because a log file of my login cannot be created. Naturally only root can delete or move any files in /var to fix this problem :o Anyway, this caused my MySQL database which was also in /var have problems, and before realising that disk space was the cause, I tried to repair the database using the repair command. Now I have a corrupt database that I cannot export due to Error: Cannot open database.MYD, and I cannot copy the actual data files on the server due to lack of permissions. Is there anyway I can get around this? If I delete the database I should have enough room to login as root again, but I`d really rather have a copy of the database before doing this. Just a offshot guess here, can you ftp in and delete a file? - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is it possible to backup a corrupt database without being
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] I can FTP and telnet in to my site as admin or a user, but all the files in /var are owned by root so cannot be deleted, with the exception of several mysql files owned by mysql, which I also cannot move or delete :( If I could extract the info in the database I could delete that remotely, then restore it after I get root access back again, but as it is now marked corrupt I can't seem to do anything with it. Is there any way of fixing a database with zero room on the partition it is in? Just a thought: FTP the database files to another machine. Drop the affected databases to delete the files and free up enough disk space to log in. Log in as root and free up more disk space. FTP the database files back to the server. Repair the database. My apologies if you've already thought of this. I don't have any experience with the raq3, so maybe this is unworkable. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
Download MySQL-Font v2.5 This is what I used when I switched from Access to MySQL. Once installed just load MySQL-Front and click on Im-/Export on the toolbar. Select ODBC Import. Viola! On Tue, 26 Nov 2002 16:37:45 +0200, Steve Jackson wrote: Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is it possible to export data from access to MySQL?
I ran across several quick and dirty, but very effective little converters at this site: http://www.intranet2internet.com Regards, Jim Jim Esten Chief Techbot WebDynamic http://www.wdynamic.com -Original Message- From: Steve Jackson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 26, 2002 8:38 AM To: MySQL General Mailing list Subject: Is it possible to export data from access to MySQL? Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Steve, It is quite possible. You can use MyODBC to connect your current Access database(s) into your MySQL server directly, the major drawback is that you'd have to create the overall table structure in MySQL first and then link the table from MySQL into Access through the File|External Data|Link Table... Menu. Or you can find and download DBTools which will export your tables for you into MySQL. I use MyODBC to connect some of my users to MySQL through Access, as that was the easiest route for my end-users. HTH, Bryant H. Hester - -Original Message- From: Steve Jackson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 26, 2002 8:38 AM To: MySQL General Mailing list Subject: Is it possible to export data from access to MySQL? Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 -BEGIN PGP SIGNATURE- Version: PGPfreeware 7.0.3 for non-commercial use http://www.pgp.com iQA/AwUBPeOJiklWu7/HFp4nEQLL8gCdGyKJWMwrVjqnyUczHBQswrt2xIYAoMJH H2FttK0BuepoUPFNby40uuGP =Yty5 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is it possible to export data from access to MySQL?
Cheers. Sorted very quickly using MySQL front. Good thing is I can use that to get the SQL and then cut and paste it to any DB I like which is PDG! Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 -Original Message- From: FlashGuy [mailto:[EMAIL PROTECTED]] Sent: 26. marraskuuta 2002 16:42 To: MySQL General Mailing list; [EMAIL PROTECTED] Subject: Re: Is it possible to export data from access to MySQL? Download MySQL-Font v2.5 This is what I used when I switched from Access to MySQL. Once installed just load MySQL-Front and click on Im-/Export on the toolbar. Select ODBC Import. Viola! On Tue, 26 Nov 2002 16:37:45 +0200, Steve Jackson wrote: Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
MySQL-Front is quite good for this... - Original Message - From: Steve Jackson [EMAIL PROTECTED] To: MySQL General Mailing list [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 4:37 PM Subject: Is it possible to export data from access to MySQL? Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is it possible to export data from access to MySQL?
A quick way. If you don't have phpMyAdmin running set it up. It has a great import feature. Just dump the access table to a delimited text file. Then go to phpMyAdmin, from the main page - create a new database, or select an existing one. from there scroll down to create a table, enter the table name and amount of fields. Enter all field names and types. Save it, then scroll down to Insert data from a textfile into a table and follow the instructions. Its a piece of cake from there. Steve Bradwell MIS Department. If you give someone a program, you will frustrate them for a day. If you teach them how to program, you will frustrate them for a lifetime. -Original Message- From: Steve Jackson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 26, 2002 9:38 AM To: MySQL General Mailing list Subject: Is it possible to export data from access to MySQL? Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
Hi! Yes, it's possible. Export your Access data to standard csv files, and then use mysqlimport with appropriate parameters to import the csvs into MySQL. The MySQL manual has excellent guidance on using mysqlimport or LOAD DATA INFILE statements (which can do the same as mysqlimport). Iikka On Tue, 26 Nov 2002, Steve Jackson wrote: Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Iikka Meriläinen Vaala, Finland E-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
I used MyAccess to convert my 40mb .mdb file into MySQL and it works great. You can find it and a whole bunch of other conversion tools on the MySQL site in Downloads/Other. Here's the link, http://www.mysql.com/downloads/os-win32.html Good Luck Steve Jackson [EMAIL PROTECTED] 11/26/02 6:37:45 AM Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
you're gonna get lots of neat high tech answers to your problem, so here's a low tech answer just because it's fun. construct the database in mysql. copy the access database to a working copy to be safe. in the working copy, link to the mysql tables. open the access table a and copy the data. open the mysql table a and paste the data. : ) the kid in me loves doing that. copy and paste is the most powerful concept in windows. the only reason that i don't use it in corereader is that it would permanently tie corereader into ms. windows. Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is it possible to export data from access to MySQL?
I like that one. Steve Bradwell MIS Department. If you give someone a program, you will frustrate them for a day. If you teach them how to program, you will frustrate them for a lifetime. -Original Message- From: John Ragan [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 26, 2002 12:50 PM To: MySQL General Mailing list; [EMAIL PROTECTED] Subject: Re: Is it possible to export data from access to MySQL? you're gonna get lots of neat high tech answers to your problem, so here's a low tech answer just because it's fun. construct the database in mysql. copy the access database to a working copy to be safe. in the working copy, link to the mysql tables. open the access table a and copy the data. open the mysql table a and paste the data. : ) the kid in me loves doing that. copy and paste is the most powerful concept in windows. the only reason that i don't use it in corereader is that it would permanently tie corereader into ms. windows. Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
Dear Steve, Is it possible to export data from MS Access into MySQL? If you don't care to export every single table manually, this is what you do: 1. Install MyODBC 3.51.04 2. Configure a system DSN for your MySQL server. 3. In Access, right-click the table to be exported. 4. Choose Export... 5. Choose Data Type ODBC. 6. Choose a name for the table (default: the Access name). 7. On Computer Data Source, choose the DSN you set up in step 2. The table will be created in the database you set up in step 2. The export will create the table definition (.frm) and import the data. Make sure _all_ your Access tables have primary keys, this makes things easier. You may run into trouble if your Access tables have strange column names (e.g., with spaces, umlauts etc.). Recently, I tried this procedure on a 500 MB Access table with 1.2 million records, and it took 4 minutes on a 2 GHz single processor Pentium machine running Win2K with Service Pack 2. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Steve Jackson [EMAIL PROTECTED] To: MySQL General Mailing list [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 3:37 PM Subject: Is it possible to export data from access to MySQL? Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to replicate just a couple of tables insteadof the whole database
Hi, Is it possible to replicate just some tables instead of a whole mysql database? A replication of the whole database would be not necessary in my case. It would be a one-way replication (means slave won't update master). Furthermore: Is there a cheep tool taking care of that (without changing the design of the master database)? Thank you in advance, Ralf Koellner - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: Is it possible to replicate just a couple of tables instead of the whole database
Ralf, Monday, October 28, 2002, 11:48:22 PM, you wrote: RK Is it possible to replicate just some tables instead of a whole mysql RK database? A replication of the whole database would be not necessary in RK my case. It would be a one-way replication (means slave won't update RK master). RK Furthermore: RK Is there a cheep tool taking care of that (without changing the design RK of the master database)? Yes. Take a look at the following options related to replication: replicate-do-table replicate-ignore-table replicate-wild-do-table replicate-wild-ignore-table You can read about them here: http://www.mysql.com/doc/en/Replication_Options.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to replicate just a couple of tables insteadof the whole database
Ralf Koellner wrote: Hi, Is it possible to replicate just some tables instead of a whole mysql database? A replication of the whole database would be not necessary in my case. It would be a one-way replication (means slave won't update master). You can use 'replicate-do-table' on slave side to restrict replication to some tables. BTW, replication is ALWAYS one way: slaves receive their updates from the master. Furthermore: Is there a cheep tool taking care of that (without changing the design of the master database)? Thank you in advance, Ralf Koellner Hope this helps -- Joseph Bueno - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to have MySQL perform an action at a specifictime?
Hummm..one solution is just to exploit the 'at' program of unix/linux systems... I use that for backups, table dumps etc. combined with perl or php scripts... $0.02; Eivind sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to have MySQL perform an action at a specifictime?
- Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 8:48 PM Subject: RE: Is it possible to have MySQL perform an action at a specific time? Sounds like you need to run a CRON job (you will have to know if your specific implementation has this.) You can have the CRON run a script that will perform the actions. CRON is definitely the way to go. You can use many script types to accomplish this such as PERL, AWK, PHP (you have to install PHP as a CGI binary) And if you use the ever-handy lynx command, PHP can be an apache_module too. :) - Mark System Administrator Asarian-host.org --- If you were supposed to understand it, we wouldn't call it code. - FedEx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is it possible to have MySQL perform an action at a specifictime?
[snip] I have a file containing commands to add and drop tables as a means of synchronizing two databases. What i want to happen is for MySQL to open this file and read it at a specific time without the user having to explicitly connect to MySQL and type in the commands in order to get it to read the file, such as mysql dbname text_file_name. Is there a way to schedule a process either using a function of MySQL or of the operating system to get it to do this? The operating system would be Linux to my knowledge. I am hosting the db on an ISP and am not sure what they will let me do in regards to interacting with MySQL but it is possible to get a telnet connection if need be to perhaps setup a script. if it has to be a shell script, i doubt they will let me do this, but you never know. Also, as another option, is it possible to create a web interface for this instead where a button hit by the user would initiate the reading of the file somehow? This might be easier but I was not sure how to exactly send command line directions from a webpage. [/snip] Sounds like you need to run a CRON job (you will have to know if your specific implementation has this.) You can have the CRON run a script that will perform the actions. You can use many script types to accomplish this such as PERL, AWK, PHP (you have to install PHP as a CGI binary) and others. This should give you enough to be able to ask your ISP some questions. HTH! Jay mysql, query, sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to have MySQL perform an action at a specifictime?
Have you looked at cron on LINUX? - Original Message - From: Jamie Buck [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 04, 2002 11:37 AM Subject: Is it possible to have MySQL perform an action at a specifictime? I have a file containing commands to add and drop tables as a means of synchronizing two databases. What i want to happen is for MySQL to open this file and read it at a specific time without the user having to explicitly connect to MySQL and type in the commands in order to get it to read the file, such as mysql dbname text_file_name. Is there a way to schedule a process either using a function of MySQL or of the operating system to get it to do this? The operating system would be Linux to my knowledge. I am hosting the db on an ISP and am not sure what they will let me do in regards to interacting with MySQL but it is possible to get a telnet connection if need be to perhaps setup a script. if it has to be a shell script, i doubt they will let me do this, but you never know. Also, as another option, is it possible to create a web interface for this instead where a button hit by the user would initiate the reading of the file somehow? This might be easier but I was not sure how to exactly send command line directions from a webpage. Thank you, Jamie Buck - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: VERY URGENT, possible bug. More on: What does this error message mean: InnoDB: Warning: difficult to find free blocks from the buffer pool..
I belive I have found a bug here, possibly. The last known commands to be run on the DB that are suspicious were a join between 2 tables. This is the info I got from the user who was workign on the DB: He was joining between keyword and billing select distinct(a.customerid) from cpcustomer.customerkeyword a left join cpbilling.billdetail b on a.customerid=b.customerid where b.customerid is null and active='y'; For some reason he hit ^c to exit (it didn't seem to be responding or somethign) He logged came back on to MySQL and ran: alter table add key bdetailcid (customerid); show create billdetail; It crashed (locked up) durning the last query mentioned above. I tried to shut down the server, but it wouldn't die so I ran the init-stop script a second time. It seems that the init start/stop script gets forceful on the second try. Started it back up, it took a _very_ long time recovering (InnoDB), then started in with the error message I first posted. Any ideas? I'm currently restoring from backups we'll see how it goes. Thanks. JW I forgot to point out that this is InnoDB JW JW I've searched Google and found the following disquiteing thing: JW http://www.innodb.com/oldbugs.html: JW JW Closed or old bug reports: Almost all of these bugs have been fixed. There are some old bug reports where the cause of the bug was never found, but because there have been no bug reports for newer versions of InnoDB, these reports are not considered actual any more. JW snip JW August 13, 2001: JW The fsync problem which was fixed in 3.23.40b and .41 could cause the following warning message on some Unix flavors: JW JW Innobase: Warning: difficult to find free blocks from JW Innobase: the buffer pool! Consider increasing the JW Innobase: buffer pool size. JW JW If you encounter the above message, upgrade to 3.23.41. JW JW However, I'm already using a newer vresion than that: JW JW ccs012:~ # rpm -qa |grep mysql JW mysql-shared-3.23.44-5 JW mysql-Max-3.23.44-5 JW mysql-devel-3.23.44-5 JW mysql-navigator-1.2.3-106 JW mysql-client-3.23.44-5 JW mysql-3.23.44-5 JW mysql-bench-3.23.44-5 JW ccs012:~ # JW JW This is a mission critical DB. Am I the lucky un-fortunate to re-dicover this supposedly fixed bug? JW JW JW I'm getting this error messge constantly in my error log: JW JW JW JW 020406 18:02:50 *** JW JW InnoDB: Warning: difficult to find free blocks from JW JW InnoDB: the buffer pool (200 search iterations)! Consider JW JW InnoDB: increasing the buffer pool size. JW JW InnoDB: It is also possible that in your Unix version JW JW InnoDB: fsync is very slow, or completely frozen inside JW JW InnoDB: the OS kernel. Then upgrading to a newer version JW JW InnoDB: of your operating system may help. Look at the JW JW InnoDB: number of fsyncs in diagnostic info below. JW JW InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0 JW JW InnoDB: 5703 OS file reads, 502 OS file writes, 82 OS fsyncs JW JW InnoDB: Starting InnoDB Monitor to print further JW JW InnoDB: diagnostics to the standard output. JW JW JW JW Running SuSE Linux 7.3: JW JW JW JW ccs012:/var/lib/mysql # uname -a ; df -h ; free -m JW JW Linux ccs012 2.4.10-64GB-SMP #1 SMP Fri Sep 28 17:26:36 GMT 2001 i686 unknown JW JW FilesystemSize Used Avail Use% Mounted on JW JW /dev/sda7 67G 59G 8.2G 88% / JW JW /dev/sda5 63M 36M 26M 58% /boot JW JW shmfs1007M 0 1006M 0% /dev/shm JW JW total used free sharedbuffers cached JW JW Mem: 2013 2008 4 0 10657 JW JW -/+ buffers/cache: 1340672 JW JW Swap: 1035 0 1035 JW JW JW JW JW JW TIA JW JW JW JW -- JW JW JW JW JW JW Jonathan Wilson JW JW System Administrator JW JW Clickpatrol.com JW JW Cedar Creek Software http://www.cedarcreeksoftware.com JW JW JW JW JW JW JW JW - JW JW Before posting, please check: JW JWhttp://www.mysql.com/manual.php (the manual) JW JWhttp://lists.mysql.com/ (the list archive) JW JW JW JW To request this thread, e-mail [EMAIL PROTECTED] JW JW To unsubscribe, e-mail [EMAIL PROTECTED] JW JW Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php JW JW JW JW JW JW JW JW -- JW JW JW Jonathan Wilson JW System Administrator JW Clickpatrol.com JW Cedar Creek Software http://www.cedarcreeksoftware.com JW JW JW JW - JW Before posting, please check: JWhttp://www.mysql.com/manual.php (the manual) JWhttp://lists.mysql.com/ (the list archive) JW JW To request this thread, e-mail [EMAIL PROTECTED] JW To unsubscribe, e-mail [EMAIL
Re: VERY URGENT, possible bug. More on: What does this error message mean: InnoDB: Warning: difficult to find free blocks from the buffer pool..
Hi! MySQL AB and Innobase Oy entry-level support contract costs 3770 euros annually (3250 USD). For corporations with requirements for high availability we have more expensive telephone support options. These are extremely low priced compared against your costs for proprietary database products, and are low even when compared to rates charged by other open-source vendors. So please consider investing in a MySQL/InnoDB support contract. You will be served not by a help desk, but directly by the developers who author MySQL/InnoDB's source code. Information: http://www.mysql.com/support/index.html To Purchase: https://order.mysql.com/ About the buffer pool error: a similar bug in recovery was fixed in 3.23.45: ... November 17, 2001: In recovery InnoDB may go into an infinite loop constantly printing a warning message that it cannot find free blocks from the buffer pool. Fixed in 3.23.45. ... I have also a faint recollection that I then also fixed some additional error in the buffer pool flush algorithm which could cause the error messages to be printed in normal operation, not just recovery. Regards, Heikki Tuuri Innobase Oy - Original Message - From: JW [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, April 07, 2002 5:04 AM Subject: Re: VERY URGENT, possible bug. More on: What does this error message mean: InnoDB: Warning: difficult to find free blocks from the buffer pool.. I belive I have found a bug here, possibly. The last known commands to be run on the DB that are suspicious were a join between 2 tables. This is the info I got from the user who was workign on the DB: He was joining between keyword and billing select distinct(a.customerid) from cpcustomer.customerkeyword a left join cpbilling.billdetail b on a.customerid=b.customerid where b.customerid is null and active='y'; For some reason he hit ^c to exit (it didn't seem to be responding or somethign) He logged came back on to MySQL and ran: alter table add key bdetailcid (customerid); show create billdetail; It crashed (locked up) durning the last query mentioned above. I tried to shut down the server, but it wouldn't die so I ran the init-stop script a second time. It seems that the init start/stop script gets forceful on the second try. Started it back up, it took a _very_ long time recovering (InnoDB), then started in with the error message I first posted. Any ideas? I'm currently restoring from backups we'll see how it goes. Thanks. JW I forgot to point out that this is InnoDB JW JW I've searched Google and found the following disquiteing thing: JW http://www.innodb.com/oldbugs.html: JW JW Closed or old bug reports: Almost all of these bugs have been fixed. There are some old bug reports where the cause of the bug was never found, but because there have been no bug reports for newer versions of InnoDB, these reports are not considered actual any more. JW snip JW August 13, 2001: JW The fsync problem which was fixed in 3.23.40b and .41 could cause the following warning message on some Unix flavors: JW JW Innobase: Warning: difficult to find free blocks from JW Innobase: the buffer pool! Consider increasing the JW Innobase: buffer pool size. JW JW If you encounter the above message, upgrade to 3.23.41. JW JW However, I'm already using a newer vresion than that: JW JW ccs012:~ # rpm -qa |grep mysql JW mysql-shared-3.23.44-5 JW mysql-Max-3.23.44-5 JW mysql-devel-3.23.44-5 JW mysql-navigator-1.2.3-106 JW mysql-client-3.23.44-5 JW mysql-3.23.44-5 JW mysql-bench-3.23.44-5 JW ccs012:~ # JW JW This is a mission critical DB. Am I the lucky un-fortunate to re-dicover this supposedly fixed bug? JW JW JW I'm getting this error messge constantly in my error log: JW JW JW JW 020406 18:02:50 *** JW JW InnoDB: Warning: difficult to find free blocks from JW JW InnoDB: the buffer pool (200 search iterations)! Consider JW JW InnoDB: increasing the buffer pool size. JW JW InnoDB: It is also possible that in your Unix version JW JW InnoDB: fsync is very slow, or completely frozen inside JW JW InnoDB: the OS kernel. Then upgrading to a newer version JW JW InnoDB: of your operating system may help. Look at the JW JW InnoDB: number of fsyncs in diagnostic info below. JW JW InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0 JW JW InnoDB: 5703 OS file reads, 502 OS file writes, 82 OS fsyncs JW JW InnoDB: Starting InnoDB Monitor to print further JW JW InnoDB: diagnostics to the standard output. JW JW JW JW Running SuSE Linux 7.3: JW JW JW JW ccs012:/var/lib/mysql # uname -a ; df -h ; free -m JW JW Linux ccs012 2.4.10-64GB-SMP #1 SMP Fri Sep 28 17:26:36 GMT 2001 i686 unknown JW JW FilesystemSize Used Avail Use% Mounted on JW JW /dev/sda7 67G 59G 8.2G 88% / JW JW /dev/sda5 63M 36M 26M 58% /boot JW JW shmfs
RE: Is this possible in one SQL statement
Please restate the following more completely. I think some typos got in there. If not, then we need more detail of what you really want. the condition is then value 1 and 3 and the record I want is A1 and A3 due to the fact that a recorc exists in table B with a value of on and one record with the value of 3 -Original Message- From: Steen Rabol [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 31, 2002 2:25 PM To: [EMAIL PROTECTED] Subject: Is this possible in one SQL statement Is this possible in a single select statement ? table A id (Unique), a_field1,a_field2,a_field3 table B id(NOT unique), b_field1 Id in table B is a ref to id in table A Now i would like to select all the id's from table A which have records i table B where b_field1 is 1 and b_field2 is 2 example: Table a record: 1, text,text,text - A1 Table a record: 2, text,text,text - A2 Table a record: 3, text,text,text - A3 Table b record 1, 1 Table b record 2, 2 Table b record 1, 3 Table b record 2, 1 Table b record 1, 4 Table b record 3, 1 Table b record 3, 3 the condition is then value 1 and 3 and the record I want is A1 and A3 due to the fact that a recorc exists in table B with a value of on and one record with the value of 3 Is it clean ;-)) I know that it can be done with a nested loops, but is it possible in one statement sql query ? Thanks in advance Steen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is this possible in one SQL statement
Hard to understand your example: b_field2 is not part of your table b table structure; your example table b rows are missing either their ID field or the non-existent b_field2 and the explanation as to why table A rows A1 and A3 would qualify is not clear when the values 1 and 3 are not related to fields. Original Message: - From: Steen Rabol [EMAIL PROTECTED] Date: Thu, 31 Jan 2002 21:24:43 +0100 To: [EMAIL PROTECTED] Subject: Is this possible in one SQL statement Is this possible in a single select statement ? table A id (Unique), a_field1,a_field2,a_field3 table B id(NOT unique), b_field1 Id in table B is a ref to id in table A Now i would like to select all the id's from table A which have records i table B where b_field1 is 1 and b_field2 is 2 example: Table a record: 1, text,text,text - A1 Table a record: 2, text,text,text - A2 Table a record: 3, text,text,text - A3 Table b record 1, 1 Table b record 2, 2 Table b record 1, 3 Table b record 2, 1 Table b record 1, 4 Table b record 3, 1 Table b record 3, 3 the condition is then value 1 and 3 and the record I want is A1 and A3 due to the fact that a recorc exists in table B with a value of on and one record with the value of 3 Is it clean ;-)) I know that it can be done with a nested loops, but is it possible in one statement sql query ? Thanks in advance Steen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php mail2web - Check your email from the web at http://mail2web.com/ . - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is this possible in one SQL statement
I want to select all the records in table A which have record in Table B where field1 is equal to 3 and have a record in Table B where field1 is equal to 1, in other word two records (or more) must exist in Table B in order to give me a record from Table A like this select * from table_a, tabele_b.field1 where table_a.id = table_b.id and (table_b.filed1 = 1 AND able_b.filed1 = 3) This is the 'problem' as I perfectly know that a field only can contain one value Hope this is more clear. Best Steen - Original Message - From: Rick Emery [EMAIL PROTECTED] To: 'Steen Rabol' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, January 31, 2002 9:35 PM Subject: RE: Is this possible in one SQL statement Please restate the following more completely. I think some typos got in there. If not, then we need more detail of what you really want. the condition is then value 1 and 3 and the record I want is A1 and A3 due to the fact that a recorc exists in table B with a value of on and one record with the value of 3 -Original Message- From: Steen Rabol [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 31, 2002 2:25 PM To: [EMAIL PROTECTED] Subject: Is this possible in one SQL statement Is this possible in a single select statement ? table A id (Unique), a_field1,a_field2,a_field3 table B id(NOT unique), b_field1 Id in table B is a ref to id in table A Now i would like to select all the id's from table A which have records i table B where b_field1 is 1 and b_field2 is 2 example: Table a record: 1, text,text,text - A1 Table a record: 2, text,text,text - A2 Table a record: 3, text,text,text - A3 Table b record 1, 1 Table b record 2, 2 Table b record 1, 3 Table b record 2, 1 Table b record 1, 4 Table b record 3, 1 Table b record 3, 3 the condition is then value 1 and 3 and the record I want is A1 and A3 due to the fact that a recorc exists in table B with a value of on and one record with the value of 3 Is it clean ;-)) I know that it can be done with a nested loops, but is it possible in one statement sql query ? Thanks in advance Steen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is this possible in one SQL statement
mysql select distinct a.* from tableA a left join tableB b1 using(id), tableB b2 where a.id=b1.id b1.id=1 b2.id=3; -Original Message- From: Steen Rabol [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 31, 2002 2:51 PM To: Rick Emery; [EMAIL PROTECTED] Subject: Re: Is this possible in one SQL statement I want to select all the records in table A which have record in Table B where field1 is equal to 3 and have a record in Table B where field1 is equal to 1, in other word two records (or more) must exist in Table B in order to give me a record from Table A like this select * from table_a, tabele_b.field1 where table_a.id = table_b.id and (table_b.filed1 = 1 AND able_b.filed1 = 3) This is the 'problem' as I perfectly know that a field only can contain one value Hope this is more clear. Best Steen - Original Message - From: Rick Emery [EMAIL PROTECTED] To: 'Steen Rabol' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, January 31, 2002 9:35 PM Subject: RE: Is this possible in one SQL statement Please restate the following more completely. I think some typos got in there. If not, then we need more detail of what you really want. the condition is then value 1 and 3 and the record I want is A1 and A3 due to the fact that a recorc exists in table B with a value of on and one record with the value of 3 -Original Message- From: Steen Rabol [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 31, 2002 2:25 PM To: [EMAIL PROTECTED] Subject: Is this possible in one SQL statement Is this possible in a single select statement ? table A id (Unique), a_field1,a_field2,a_field3 table B id(NOT unique), b_field1 Id in table B is a ref to id in table A Now i would like to select all the id's from table A which have records i table B where b_field1 is 1 and b_field2 is 2 example: Table a record: 1, text,text,text - A1 Table a record: 2, text,text,text - A2 Table a record: 3, text,text,text - A3 Table b record 1, 1 Table b record 2, 2 Table b record 1, 3 Table b record 2, 1 Table b record 1, 4 Table b record 3, 1 Table b record 3, 3 the condition is then value 1 and 3 and the record I want is A1 and A3 due to the fact that a recorc exists in table B with a value of on and one record with the value of 3 Is it clean ;-)) I know that it can be done with a nested loops, but is it possible in one statement sql query ? Thanks in advance Steen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Spam - a possible cause ?
- Original Message - From: Marjolein Katsma [EMAIL PROTECTED] To: [EMAIL PROTECTED] [RegSoft/mysql] [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Saturday, January 12, 2002 12:44 PM Subject: RE: Spam - a possible cause ? Matthew, snip Spam is people not interested in MySQL at all posting a Business opportunity (Dear mysql, Join today for FREE and a chance to win a $100 Shopping Spree at The DHS Club Outlet Center!) or Golden Investment Opportunity or Great Growth Potential stock scams. Marjolein, They sound like great opportunities. Do you have any more info? Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Spam - a possible cause ?
snip Spam is people not interested in MySQL at all posting a Business opportunity (Dear mysql, Join today for FREE and a chance to win a $100 Shopping Spree at The DHS Club Outlet Center!) or Golden Investment Opportunity or Great Growth Potential stock scams. Marjolein, They sound like great opportunities. Do you have any more info? Tony, Tony, Wouldn't your first step in such innocense and foolishness be to e-mail [EMAIL PROTECTED] !? =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Spam - a possible cause ?
Tony, You have the same info on these opportunities as I had, and any other subscriber, since they were all posted on (um, spammed to) this list. (If you're a *very* recent subscriber you should be able to find them in the archives or Google.) I gave literal quotes from messages sent to the list. Of course if you just deleted them you'll have to wait (just a bit, surely not long) for the next irresistable Business opportunity to be announced here ;-) At 18:49 2002-01-13, Tony Buckley wrote: - Original Message - From: Marjolein Katsma [EMAIL PROTECTED] To: [EMAIL PROTECTED] [RegSoft/mysql] [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Saturday, January 12, 2002 12:44 PM Subject: RE: Spam - a possible cause ? Matthew, snip Spam is people not interested in MySQL at all posting a Business opportunity (Dear mysql, Join today for FREE and a chance to win a $100 Shopping Spree at The DHS Club Outlet Center!) or Golden Investment Opportunity or Great Growth Potential stock scams. Marjolein, They sound like great opportunities. Do you have any more info? Tony -- Marjolein Katsma HomeSite Help - http://hshelp.com/ - Extensions, Tips and Tools The Bookstore - http://books.hshelp.com/ - Books for webmasters and webrookies - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Spam - a possible cause ?
Matthew, Posting simple questions, even different people repeatedly posting simple questions is NOT spam. It may be a problem for some people, but it is not what this discussion is about. Spam is people not interested in MySQL at all posting a Business opportunity (Dear mysql, Join today for FREE and a chance to win a $100 Shopping Spree at The DHS Club Outlet Center!) or Golden Investment Opportunity or Great Growth Potential stock scams. At 01:07 2002-01-12, you wrote: I am an experienced programmed and have worked on Unix and with Oracle and Informix for many years. I personally am struggling to get my head around Mysql (getting there now though) I think part of the reason I am posting simple questions is. -- Marjolein Katsma HomeSite Help - http://hshelp.com/ - Extensions, Tips and Tools The Bookstore - http://books.hshelp.com/ - Books for webmasters and webrookies - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Spam - a possible cause ?
I am an experienced programmed and have worked on Unix and with Oracle and Informix for many years. I personally am struggling to get my head around Mysql (getting there now though) I think part of the reason I am posting simple questions is. 1.) I find the manual quite combersome for particular reference, ie I found finding information on adding users and the like quite hard to find easy and had to trawl through similar topics when someone on this list got me on the right track straight away and it was easy to reference the manual from the info given on this list. 2.) My past experience in other languages and RDBMS is hindering my quite bad, ie I am expecting certain restuls from certain situations and not getting them, somethings that seem quite straight forward to people using mysql is totally confusing for someone used to other products. 3.) There is a lot of conflicting information. I have a number of books on Mysql and some conradict each other and the manual, so the only way to make sure is to play/test the methods mentioned, then ask the list when something doesn't make scense 4.) People of All levels join and leave the list each day (I imagane) so reposts are quite common, If they do not apply to me or I don't feel I can help I delete them, otherwist I reply, the number or reposts is becoming a problem due (in my opinion) to a select few elite mysql people, who have strong skills with MySql and have been using it for a while, instead of ignoring the reposts (which I am sure they have seen hundereds of times) they reply back with harsh (again my opinion) or acidic comments, like this was posted 3 days ago, read the manual before wasting our time. This points out peoples re-posts to others who are not so bothered, if they just deleted what did not apply I am sure someone of similar level to the question poster would reply with a helpfull comment. On the other hand these elitist people do provide good info when needed so I don't intend to be too blaming on them. I really like the way this list works, and I have found %90 usefull and had some really indepth discussions on even the simplist questions, giving me a better understanding of the problem that I first asked. Not sure how close to the mark I am but I thought I would put my opinion in. Matt. -Original Message- From: Robert Lucier [mailto:[EMAIL PROTECTED]] Sent: 11 January 2002 19:55 To: jds; Patrick Crowley; Keith C. Ivey Cc: MySQL List Subject: RE: Spam A little higher barrier to entry, even for non-spam posts might not be a bad thing. The list is already pretty well spammed with questions that are either in the manual, or result from a lack of programming experience in general. Making it harder to post might make it a little easier to stay subscribed --- jds [EMAIL PROTECTED] wrote: ummm. you go ahead with that and noone will stay on the list. again, now with my stupid reply, there has been more posts about spam than actual spam. let's do the list a favor and put this thread to bed. jOda -Original Message- From: Patrick Crowley [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 10, 2002 11:16 AM To: Keith C. Ivey Cc: MySQL List Subject: Re: Spam I have to say I'm shocked that the list is completely open. That's such a stupid newbie thing to do. Maybe we should initiate a spam strike until the list mom/dad listens. Just subscribe the list to *another* list, and watch the fun begin... best, patrick crowley mokolabs making sure the future doesn't suck... w http://mokolabs.com e [EMAIL PROTECTED] p 646.591.5477 Why *isn't* posting to the MySQL list restricted to subscribers? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
