delete query question
I think this is possible but I'm having a total brain fart as to how to construct the query.. Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Can't this be done in one query? Or two? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: delete query question
-Original Message- From: Ian Simpson [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2008 11:27 AM To: Jeff Mckeon Cc: mysql@lists.mysql.com Subject: Re: delete query question If the tables are InnoDB, you could temporarily set up a foreign key relationship between the two, with the 'ON DELETE CASCADE' option. Nope, MyISAM... On Tue, 2008-07-08 at 11:14 -0400, Jeff Mckeon wrote: I think this is possible but I'm having a total brain fart as to how to construct the query.. Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Can't this be done in one query? Or two? Thanks, Jeff -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: delete query question
Thanks, that did it! -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2008 11:57 AM To: Jeff Mckeon Cc: mysql@lists.mysql.com Subject: Re: delete query question Jeff, Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Like this (untested)? DELETE table1,table2 FROM table1 t1 JOIN table2 t2 ON t1.id=t2.ticket WHERE t2.created UNIX_TIMESTAMP( DATE_SUB( NOW(), INTERVAL 3 MONTH )) ; PB - Jeff Mckeon wrote: I think this is possible but I'm having a total brain fart as to how to construct the query.. Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Can't this be done in one query? Or two? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error: No query specified
When I run a Show slave status \G I get a message at the bottom that says Error: No query specified I don't recall ever seeing this before and can't find anything online about it. Anyone know what it means? Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 845962457 Relay_Log_Space: 739790470 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.01 sec) ERROR: No query specified mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error: No query specified
DUH!!! LOL, I'm an idiot.. Gonna go flush my head down the toilet now. Thanks guys. From: William Newton [mailto:[EMAIL PROTECTED] Sent: Thursday, December 06, 2007 1:29 PM To: Jeff Mckeon; MySql Subject: Re: Error: No query specified I'm guessing you are adding a semi-colon (;) to the end of the statement. Its unnecessary with the \G - Original Message From: Jeff Mckeon [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Thursday, December 6, 2007 12:19:22 PM Subject: Error: No query specified When I run a Show slave status \G I get a message at the bottom that says Error: No query specified I don't recall ever seeing this before and can't find anything online about it. Anyone know what it means? Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 845962457 Relay_Log_Space: 739790470 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.01 sec) ERROR: No query specified mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Never miss a thing. Make Yahoo http://us.rd.yahoo.com/evt=51438/*http:/www.yahoo.com/r/hs your homepage.
RE: REPLICATION
No, I do not think this is possible. -Original Message- From: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] Sent: Thursday, December 06, 2007 8:38 AM To: ars k; MySql Subject: Re: REPLICATION A, B, C, D are mysql Servers On Dec 6, 2007 12:18 PM, ars k [EMAIL PROTECTED] wrote: are A,B,C,D servers or separate mysql instances? On Dec 6, 2007 10:18 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Does any body has tried this On Dec 6, 2007 10:08 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Yes, Is it possible or not -Krishna Chandra Prajapati On Dec 5, 2007 8:56 PM, Jeff Mckeon [EMAIL PROTECTED] wrote: -Original Message- From: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 05, 2007 8:09 AM To: MySql Subject: REPLICATION Hi, I am working on production and thinking of implementing chain replication A-B-C. A is replicated to B. B is being replicated to C. I want to know that there is any script or any cron by which i can replicate (manually or automatically) D server to C. (D is another replication server). I was thinking that manually i can get the mysql-bin log sql and execute it on server C. In this way D will replicate to C. The above task is possible or not. I haven't tested till now. Any other idea any body have. So you want to do this? A-B-C-D -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED] -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: REPLICATION
-Original Message- From: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 05, 2007 8:09 AM To: MySql Subject: REPLICATION Hi, I am working on production and thinking of implementing chain replication A-B-C. A is replicated to B. B is being replicated to C. I want to know that there is any script or any cron by which i can replicate (manually or automatically) D server to C. (D is another replication server). I was thinking that manually i can get the mysql-bin log sql and execute it on server C. In this way D will replicate to C. The above task is possible or not. I haven't tested till now. Any other idea any body have. So you want to do this? A-B-C-D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with SQL query construction
-Original Message-
From: Marcus Claesson [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 7:49 AM
To: mysql@lists.mysql.com
Subject: Help with SQL query construction
Hi!
I have a SQL query construction question that I hope someone can help
me with. After comparing a bunch of DNA fragments (see name below) with
a larger reference sequence I get a ordered list ranked according to
similarities, and with start/stop co-ordinates where the fragments map
to the reference sequence:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | NULL |
| B|2 | 2 | 998 | NULL |
| C|4 | 1100 | 2000 | NULL |
| D|3 | 3050 | 4100 | NULL |
| E|5 | 2040 | 3000 | NULL |
| F|6 | 1102 | 2000 | NULL |
| G|7 | 1098 | 1998 | NULL |
| H|8 | 3048 | 4100 | NULL |
| I|9 | 3051 | 4102 | NULL |
+--+--+---+--+--+
A graphical representation of fragments mapped to the ref sequence:
ref
1 A--
2 B
3 D--
4 C--
5 E
6 F---
7 G---
8 H---
9 I---
Now, I want to group fragments in each overlapping position and sub-
rank
them according to their rank in that position. The final table would
then look like:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | 1|
| B|2 | 2 | 998 | 2|
| C|4 | 1100 | 2000 | 1|
| D|3 | 3050 | 4100 | 1|
| E|5 | 2040 | 3000 | 1|
| F|6 | 1102 | 2000 | 2|
| G|7 | 1098 | 1998 | 3|
| H|8 | 3048 | 4100 | 2|
| I|9 | 3051 | 4102 | 3|
+--+--+---+--+--+
Is this possible to achieve using SQL queries alone (perhaps with GROUP
BY, nested SELECTs etc)?
I've managed to do this with a Perl-DBI script, but would much prefer
to
do it completely with MySQL instead. The Perl code is below and below
that is the MySQL-dump of the test data set...
Many thanks in advance!
Marcus
while (@{$dbh-selectcol_arrayref(SELECT rank FROM test WHERE sub_rank
IS NULL)}) {
@null_sub_ranks = @{$dbh-selectcol_arrayref(SELECT rank FROM test
WHERE sub_rank IS NULL AND NOT (start=(SELECT stop FROM test WHERE
rank
= (SELECT min(rank) FROM test WHERE sub_rank IS NULL)) OR stop =
(SELECT start FROM test WHERE rank = (SELECT min(rank) FROM test WHERE
sub_rank IS NULL};
for ($rank=0; $rank scalar(@null_sub_ranks); $rank++ ) {
$sub_rank = $rank + 1;
$dbh-do(UPDATE test SET sub_rank=$sub_rank WHERE rank=
$null_sub_ranks[$rank]);
}
}
-- MySQL dump 10.10
--
-- Host: localhostDatabase: bxb
-- --
-- Server version 5.0.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`name` text,
`rank` int(11) default NULL,
`start` int(11) default NULL,
`stop` int(11) default NULL,
`sub_rank` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `test`
--
/*!4 ALTER TABLE `test` DISABLE KEYS */;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES
('A',1,1,1000,NULL),('B',2,2,998,NULL),('C',4,1100,2000,NULL),('D',3,30
50,4100,NULL),('E',5,2040,3000,NULL),('F',6,1102,2000,NULL),('G',7,1098
,1998,NULL),('H',8,3048,4100,NULL),('I',9,3051,4102,NULL);
UNLOCK TABLES;
/*!4 ALTER TABLE `test` ENABLE KEYS */;
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
I'd say perl is
RE: backup InnoDB db to another server
-Original Message- From: js [mailto:[EMAIL PROTECTED] Sent: Saturday, December 01, 2007 8:11 PM To: Jeff Mckeon Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server You might want to use --single-transaction option when mysqldumping innodb We have a mix of InnoDB and MyIsam tables so that's really not an option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
-Original Message- From: Osvaldo Sommer [mailto:[EMAIL PROTECTED] Sent: Saturday, December 01, 2007 8:23 AM To: 'Jeff Mckeon'; 'David Campbell'; mysql@lists.mysql.com Subject: RE: backup InnoDB db to another server Jeff: Mysqldump don't back up your index, that's your data only. Osvaldo Sommer Actually I think it's more than that. We have cleaning scripts put place to delete records older than 3 months from certain tables. I think the users have been running these without optimizing the tables afterwards and therefore never reclaiming the space the created with the deletes. These tablename_Old tables were huge. On the main systems I did a mysqldump of just these tables, then dropped the originals from the db and restored them. The entire db size went from 65G to 20G. The database was already screwed up and I have another master running for our applications so there was no risk if I screwed something up. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
-Original Message- From: David Campbell [mailto:[EMAIL PROTECTED] Sent: Friday, November 30, 2007 11:29 AM To: mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server Jørn Dahl-Stamnes wrote: On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or scp dump.sql [EMAIL PROTECTED]:. Onliner mysqldump DB1 -uroot -ppassword dump.sql | ssh 10.10.0.2 cat dump.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The Mysqldump has finished but I've only got a 10gig .sql file. The db is about 65gig in raw size. Does this sound right? Is there a filesize limit for mysqldump .sql files? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Baron Schwartz Sent: Friday, November 30, 2007 11:06 AM To: Jeff Mckeon Cc: mysql list Subject: Re: backup InnoDB db to another server On Nov 30, 2007 10:55 AM, Jeff Mckeon [EMAIL PROTECTED] wrote: I'm trying to use mysqldump to backup an innoDB based db from one server to an sql file on another. It doesn't seem to be working however... Here is the command I'm using on the source server mysqldump DB1 -uroot -ppassword | mysql --compress -h 10.10.0.1 - uroot -ppassword DB1 /DATA/DB1backup.sql I see a /DATA/DB1backup.sql file created on the source server with 0 size, but nothing on the destination server. What am I screwing up here? Your command is actually telling mysql on 10.01.0.1 to execute the dumped output. It is doing so without creating any ouput, but the is creating an output file anyway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
backup InnoDB db to another server
I'm trying to use mysqldump to backup an innoDB based db from one server to an sql file on another. It doesn't seem to be working however... Here is the command I'm using on the source server mysqldump DB1 -uroot -ppassword | mysql --compress -h 10.10.0.1 -uroot -ppassword DB1 /DATA/DB1backup.sql I see a /DATA/DB1backup.sql file created on the source server with 0 size, but nothing on the destination server. What am I screwing up here? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
-Original Message- From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED] Sent: Friday, November 30, 2007 11:16 AM To: mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or mysqldump DB1 -uroot -ppassword dump.sql scp dump.sql [EMAIL PROTECTED]:. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Ok so on 10.10.0.2 (destination server) issue a: % mysqldump DB1 -h10.10.0.1 -C -uroot -ppassword /DATA/DB01bacup.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
server optimization
Hey all, I've got a new server set up, with dual Intel quad core processors, 4 gig of ram, OpenSuse 10.3 (64bit) and MySql 5.0.45. The majority of the tables are MyISAM with a few InnoDB here or there. I'm using the huge-my.cnf as the base for my config. Can anyone suggest some tweeking to the my.conf that will give me the best performance on this platform? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysqlhotcopy and replication
Excellent Dan! Thanks! -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Thu 6/29/2006 4:35 PM To: Jeff Cc: mysql@lists.mysql.com Subject: Re: Mysqlhotcopy and replication Jeff, that is indeed the case - the replication thread will freeze just like any other thread, and pick up where it left off. In fact, the statements are still replicated to the slave by the replication thread, stored in the slave's relay-bin file I think. Therefore even though the slave data is not being updated, the slave is in a sense staying current with the master even during the hotcopy (though you would have to wait for all those statements to be applied for the data to be synchronized). Dan On 6/29/06, Jeff [EMAIL PROTECTED] wrote: I've got a replication slave db (all MyISAM tables) that I currently back up every night by stopping MySQL, tarballing the table files, then starting MySQL up again. I'd like to use Mysqlhotcopy to do this so that the db can stay up for reads while the backup is being done. Here's my question. While the tables are flushed with read lock, what becomes of all the Insert,Update and delete queries that come in via replication? Will they be applied after the backup is done and the tables unlocked? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimize: 14 hours and still running!
-Original Message- From: Nathan Gross [mailto:[EMAIL PROTECTED] Sent: Thursday, December 08, 2005 13:58 To: mysql@lists.mysql.com Subject: Optimize: 14 hours and still running! On a 1.6ghz, 1gb ram, Linux machine running Mysql 4.1x. I have an Innodb table with over 20 million records and index size about 3.7 gig, data size 2.2gig (yes, many indexes, more space then the data itself). Last night I tried an Optimize from the Admin gui console (logged in as root at the host server), and the thing is still running! Problem is I need to leave early this evening and have to take some action. The Linux 'top' utility has it on the top since then at about 11%-18% cpu Disk activity is continuously heavy. 1. How long should it take? 2. If I hit cancel will it: a) Roll back what it did, another 14 hours! b) Just stop as if nothing happened. c) The table will be partially optimized and will run normally. d) hang the process and/or machine. 3. Is the data in jeopardy? Thank you all. -nat -- From my understanging of the memory needs of an InnoDB engine with tables of that size, you're system is very underpowered. Depending on your system innodb variables you could be using up all the available ram and Bogging down the OS or not giving the db enough. I think you just need to let it go and wait. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB
Jeff [EMAIL PROTECTED] wrote on 09/29/2005 08:47:52 AM: Jeff wrote: Ugh... mysqladmin -uroot -ptelaurus processlist | grep -c Sleep And it returned 200 sleeping connections, all persistant connections from our app servers and 4 threads_running Also a show status gave me a max_used_connections of 236. If that's the case then I can probably only set it to about 250 which means if I set my innodb_buffer_pool_size = 100M and dropping my key_buffer_size to 250, I'll need 1884M of ram according to the formula above, which is dangerously close to the 2G limit specified in the warning on the link above. Currently the key_reads to Key_reads_requests is about 1:1970 with the key_buffer_size of 384M, so I guess I can safely drop this to 250M Even if I changed the entire DB over to InnoDB, and pushed the key_buffer_size down really low it wouldn't drop the total memory usage below 1600M. So what is this telling me? I need more ram or less connections or I should just stay with MyISAM? Thanks, Jeff I would suggest taking a hard look at why your application servers are creating 200 sleeping connections, and if that is necessary. You may also be able to reduce sort_ and read_buffer_size to 1M each, but I couldn't tell you how that might affect your application, so you may not want to do that. (Does anyone on the list have experience modifying these?) I think the biggest issue will be the system's RAM - the 2G limit on MySQL's total allocated RAM is a per-process hard limit on 32-bit architecture, but most 32-bit systems benefit greatly from having more than 2G total RAM (the OS may use the rest for disk caching, etc). If, say, your server had 4G RAM, then you could safely configure MySQL to use very close to 2G, and performance should fly. With only 2G in the system, setting MySQL to use as much RAM as possible would leave next to nothing for the OS or other processes, and that is the problem (as I see it). However, that said, more RAM is not always the answer. You may get much more of a performance increase by modifying your application code so that it doesn't waste so many connections (thus allowing you to allocate plenty of RAM to the innodb_buffer_pool). Of course, you can do both (just to play it safe, right?). ;) Well the applications with persistant connections is a touchy subject. Our apps send and rec data over satelite links which are very expensive. The shorter the duration of the link the less it costs us. So the pervailing theory is that with persistant connections the apps will spend less time re-connecting/dis-connecting from the db. Even fractions of a second counts when you're talking about thousands of connections a day and we are charged by the second for airtime. And all of those sleeping connections are costing you how much in unused air time? I think there's a missunderstanding here. The applications run on servers in our datacenter and wait for client connections to call in. The client which is another server on the other end of the sat link, transfers the data to our apps and our apps send data to it (depending on whether or not data is waiting for it, one of the db queries tells the local app this) and the local apps in turn write the connection information to the database. Compared with many other databases, the cost (time and data) of making and breaking a MySQL connection is cheap. Try a small set of test cases and see for yourself. Maybe you could move 10 of your normal clients from using your persistent connections into a connect-as-needed model and see what that does to your air-time, sleeping connection counts, and total throughput. The only way to know for certain is to try it in your environment but I know that in the world of web development (where connections are also precious and throughput is king) that being connected only when necessary usually works much better than trying to stay connected all of the time. By minimizing the communications overhead imposed on the server by maintaining unused open connections, the server should be able to respond better. You should not only have less dead air but each connection itself will take less time as the server will be more responsive. Remember, I recommend making and breaking connections around blocks of execution not per-statement. Let's say you have a lookup routine that uses 6 queries and massages the data into something useful client-side. It makes no sense to flip a connection 6 times for those 6 queries as they are all part of one larger process. Prepare your SQL statements as much as possible, make one connection, run the 6 queries, cache the
RE: Weird database files
-Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 28, 2005 06:30 To: mysql@lists.mysql.com Subject: Re: Weird database files Hello. On the master we're still running 4.0.16, the slaves are up to 4.1.13. If you can - upgrade the master server. It's in the plans but that is our main production server so it's not something we can just do at any time. I've upgraded the slaves first because generally you can replicate from an older version to a newer one but not the other way around. Jeff McKeon wrote: Jeff wrote: Had problem with our database this weekend, apparently an app did an insert query that was huge size wise and this totally boogered up replication downstream. Also I cant read past that point in the=20 binlog using mysqlbinlog on the master server. It complains that: =20 ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1953458240, event_type: 119 ERROR: Could not read entry at offset 66113944 : Error in=20 log format or read error =20 And then there are the weird table files that showed up in the data directory for the database (all MyISAM): =20 -rw-rw1 mysqlmysql 14K Sep 12 11:50 #sql-7c1c_217c.frm -rw-rw1 mysqlmysql1.8G Sep 12 11:54 #sql-7c1c_217c.MYD -rw-rw1 mysqlmysql 92M Sep 12 12:09 #sql-7c1c_217c.MYI =20 Anyone ever see something like this before? Are they files for a temp table maybe? =20 Jeff =20 =20 Hello. =20 Yes, these files are from some unterminated query. See: http://dev.mysql.com/doc/mysql/en/temporary-files.html =20 You may want to use --start-position (--start-datetime) and --stop-position (--stop-datetime) to skip the problematic=20 statement and perform necessary updates on the slave by hand.=20 What versions of=20 MySQL do you use? =20 On the master we're still running 4.0.16, the slaves are up to 4.1.13. =20 To repair the problem with replication I simply restarted the master so it created another binlog and then took a snapshot and recreated the slaves. I found out just this morning however that one of the tables has a corrupted MYI file. When I try to run a query on it, I get... ERROR 1016: Can't open file: 'Mailbox_Old.MYI'. (errno: 144) Running perror I get: Error code 144: Unknown error 144 144 =3D Table is crashed and last repair failed I'm running mysqlcheck on the offending table now. Thanks, Jeff -- /_/ /_/\_, /___/\___\_\___/ 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird database files
Jeff wrote: Had problem with our database this weekend, apparently an app did an insert query that was huge size wise and this totally boogered up replication downstream. Also I cant read past that point in the binlog using mysqlbinlog on the master server. It complains that: ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1953458240, event_type: 119 ERROR: Could not read entry at offset 66113944 : Error in log format or read error And then there are the weird table files that showed up in the data directory for the database (all MyISAM): -rw-rw1 mysqlmysql 14K Sep 12 11:50 #sql-7c1c_217c.frm -rw-rw1 mysqlmysql1.8G Sep 12 11:54 #sql-7c1c_217c.MYD -rw-rw1 mysqlmysql 92M Sep 12 12:09 #sql-7c1c_217c.MYI Anyone ever see something like this before? Are they files for a temp table maybe? Jeff Hello. Yes, these files are from some unterminated query. See: http://dev.mysql.com/doc/mysql/en/temporary-files.html You may want to use --start-position (--start-datetime) and --stop-position (--stop-datetime) to skip the problematic statement and perform necessary updates on the slave by hand. What versions of MySQL do you use? On the master we're still running 4.0.16, the slaves are up to 4.1.13. To repair the problem with replication I simply restarted the master so it created another binlog and then took a snapshot and recreated the slaves. I found out just this morning however that one of the tables has a corrupted MYI file. When I try to run a query on it, I get... ERROR 1016: Can't open file: 'Mailbox_Old.MYI'. (errno: 144) Running perror I get: Error code 144: Unknown error 144 144 = Table is crashed and last repair failed I'm running mysqlcheck on the offending table now. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Timezone setting wrong?
-Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 21, 2005 08:55 To: mysql@lists.mysql.com Subject: Timezone setting wrong? I've got a RHEL3 server I just installed with mysql 4.0.16. The hardware clock and system clock are both set to UTC and show the correct time. If I do a select Now(); from mysql it show's the correct time However, Unixtimestamp fields written to a table all are an hour off. They're one hour ahead. All the data on this system is replicated from a master. The master is set correctly, hwclock and sysclock at UTC. The same query: Select max(from_unixtime(timestamp_field)) from table; Run on both servers returns a result 1 hour ahead on the new slave. A show variables on the new slave returns *** 120. row *** Variable_name: timezone Value: IST I'm sure I've missed something simple here but what? Thanks, Jeff Ok, update, show variables on the master returns timezone of GMT, the slave IST. The master is on RH9 and the slave on RHEL3. So on mysql 4.0.16 where do I force the timezone to be GMT? I've tried putting TZ = GMT and TZ = UTC into the my.cnf file but then mysql won't even start. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Circular Replication
-Original Message- From: Devananda [mailto:[EMAIL PROTECTED] Sent: Friday, September 16, 2005 11:55 To: Jeff Cc: mysql@lists.mysql.com Subject: Re: Circular Replication Jeff wrote: Am I correct in this setup process: Server A exists Server B to be built Stop Server A, take snapshot, record Master info. Start Server A Setup server B, Install snapshot from A Set B up as a master Set B up as a slave to A Set A up as a slave to B, no need for binlog file or position info as it will start at the default 001 and pos 4 Circular replication is now running... Did I mis anything? Thanks, Jeff If you are using InnoDB, then you do not need to stop server A to take a snapshot. (see http://dev.mysql.com/doc/mysql/en/mysqldump.html) The simultaneous use of --master-data and --single-transaction works as of MySQL 4.1.8. It provides a convenient way to make an online backup suitable for point-in-time recovery if tables are stored in the InnoDB storage engine. As far as setting up the replication, that looks ok. Each server needs to have binary logging enabled, and needs to have the other server as it's master. Regards, Devananda vdv Nope, all tables are MyISAM. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backing up live MySQL Databases
Anyone using any third party products like Arkeia that enable you to do a live backup of MySQL database? I'm searching for a backup solution for our new data center and would like to here what other people are using for MySQL. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backing up live MySQL Databases
Yes, that's the way we currently do it but we were looking for more options. Jeff -Original Message- From: Kieran Kelleher [mailto:[EMAIL PROTECTED] Sent: Thursday, June 16, 2005 11:26 AM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Backing up live MySQL Databases I have a master-slave replication setup. A scheduled backup script on the slave runs automatically at regular intervals. The script slave does this: shutdown the mysql server on the slave backup the mysql data directory to backup media restart the mysql server on the slave. Using a setup like this means that the master NEVER has to be stopped or interrupted. Jeremy Zawdowny's book Advanced MySQL has excellent information on backup and the various ways to setup a backup strategy. Regards, Kieran -Kieran Blog: http://webobjects.webhop.org/ On Jun 16, 2005, at 11:06 AM, Jeff McKeon wrote: Anyone using any third party products like Arkeia that enable you to do a live backup of MySQL database? I'm searching for a backup solution for our new data center and would like to here what other people are using for MySQL. Thanks, Jeff -- 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]
mysqlhotcopy
Hello, Anyone here run mysqlhotcopy? I've read the docs on it but they are basicly just a howto and don't go too in depth. I've got a DB that is the back end to a 24/7 application. I ususally do backups from a replicated db by shutting down the Replicated DB then doing a tar are all the db files, then starting the db when done. Does mysqlhotcopy allow you to take a full snapshot of the db without needing to shut it down? What happens to read writes and updates while mysqlhotcopy is running? Any info or experiences anyone has would be greatly appreciated. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqlhotcopy
Am I right in assuming that while mysqlhotcopy is running, nobody else can write to or update the DB? Jeff -Original Message- From: Jeremiah Gowdy [mailto:[EMAIL PROTECTED] Sent: Friday, June 03, 2005 2:24 PM To: Jeff McKeon; mysql@lists.mysql.com Subject: Re: mysqlhotcopy I run 24/7 applications also. Use mysqlhotcopy to do exactly what you're doing by hand now. Run mysqlhotcopy on a slave server. It does exactly what you think. Lock and flush the tables, tarball them, and unlock them. No shutdown required. - Original Message - From: Jeff McKeon [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, June 03, 2005 11:11 AM Subject: mysqlhotcopy Hello, Anyone here run mysqlhotcopy? I've read the docs on it but they are basicly just a howto and don't go too in depth. I've got a DB that is the back end to a 24/7 application. I ususally do backups from a replicated db by shutting down the Replicated DB then doing a tar are all the db files, then starting the db when done. Does mysqlhotcopy allow you to take a full snapshot of the db without needing to shut it down? What happens to read writes and updates while mysqlhotcopy is running? Any info or experiences anyone has would be greatly appreciated. Thanks, Jeff -- 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]
Restoring a database from binlogs
Hey all, I've got a big problem. Seems one of our programmers decided to write a script that deletes all records from a log table older than 3 months. Problem is, we need old data from this log to reconcile our customer accounts. Our backups only go back 2 weeks. What I do have however is replication running and therefore old binlogs. These binlogs go back as far as Nov 12, 2004. The cleaning of the log table didn't start until about a month ago and has deleted any data prior to Jan 18, 2005. The only snapshot I can find of the database is from Sept 10, 2004. So.. (shooting off into the dark) I have binlogs starting from Nov 12, a snapshot from sept 10th. Is there some way anyone can suggest that I can reconstruct the database table with what I have? Best Reguards, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Restoring a database from binlogs
Interesting idea. So if I take the table from Sept 10th and then do that with the binlog I'll be missing about 2 months worth of data because my earliest binlog file is Nov 12th. The queries are always one to a line in the binlog? Best Reguards, Jeff -Original Message- From: Dathan Pattishall [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 18, 2005 2:16 PM To: Jeff McKeon; mysql@lists.mysql.com Subject: RE: Restoring a database from binlogs Dump the binarylogs into a text file greping all the log data in order of oldest to newest (minus the massive delete). Then reply the events backinto mysql Mysqlbinlog binlogs in order |grep [your tablename] BIGSQLFILE.sql mysql -uroot database BIGSQLFILE.sql DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 18, 2005 11:31 AM To: mysql@lists.mysql.com Subject: Restoring a database from binlogs Hey all, I've got a big problem. Seems one of our programmers decided to write a script that deletes all records from a log table older than 3 months. Problem is, we need old data from this log to reconcile our customer accounts. Our backups only go back 2 weeks. What I do have however is replication running and therefore old binlogs. These binlogs go back as far as Nov 12, 2004. The cleaning of the log table didn't start until about a month ago and has deleted any data prior to Jan 18, 2005. The only snapshot I can find of the database is from Sept 10, 2004. So.. (shooting off into the dark) I have binlogs starting from Nov 12, a snapshot from sept 10th. Is there some way anyone can suggest that I can reconstruct the database table with what I have? Best Reguards, Jeff -- 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]
Query question
I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Peter, I'm unfamiliar with the @d := section you describe. Is this psudo code or real syntax? thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:17 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
RE: Query question
3.23. no control over this right now or i'd upgrade, believe me! jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:43 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question That's real syntax for inline assignment of a column value to a user variable. What MySQL version are you using? PB Jeff McKeon wrote: Peter, I'm unfamiliar with the @d := section you describe. Is this psudo code or real syntax? thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:17 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff
RE: Query question
thanks, I'll give that a try tomorrow. :o) Jeffrey S. McKeon Manager of Information Technology Telaurus Communications LLC [EMAIL PROTECTED] +1 (973) 889-8990 ex 209 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:36 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: RE: Query question Jeff McKeon [EMAIL PROTECTED] wrote on 04/25/2005 04:08:29 PM: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 OK, then you need to collect your child-table maximums in one pass and build your actual query in the second (the non-subquery version of the example I sent). Let's find all of the child records where user not like 'john'. ( I will exclude all users whose name starts with 'john') CREATE TEMPORARY TABLE lastRecords SELECT parentID, max(datetime_field_name_here) as latest FROM table2 WHERE user NOT LIKE 'john%' GROUP BY parentID; You had to exclude 'john' at this stage because you want the latest child record that isn't 'john'. Make sense? Of course, you will need to adjust this to meet whatever conditions you really want. SELECT t1.*, t2.* FROM table1 t1 LEFT JOIN lastRecords r ON r.parentID = t1.id LEFT JOIN table2 t2 ON t2.parentID = r.parentID AND t2.datetime_field_name_here = r.latest; That will give you all of the records from table1 and only the most recent record from table2 (if it even exists). I used the LEFT JOIN (not an INNER JOIN) so that you can see all of the records from table1. If I had used INNER JOINs you would have only seen those records that matched up with the conditions you placed on table2. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Update query help
I have two tables. One has a list of customers. The other has a record of customer transactions including unix datestamps of each transaction. I've added a field to the customer table called First_Transaction I want to update this field with the datestamp of the first transaction for each customer from the Transaction table. I tried this... UPDATE Customer,Transactions set Customer.First_Transaction = MIN(Transactions.Datestamp) Where Customer.ID = Transactions.CustID But this doesn't work because of MIN() grouping. I'm stumped, anyone know how to accomplish this? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Update query help
Yeah I thought of that but was hoping not to have to use a temp table. Thanks! Jeff -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 05, 2004 9:25 AM To: Jeff McKeon Cc: [EMAIL PROTECTED] Subject: Re: Update query help Break it down into two steps. Compute your new values by customerid, then update your customer table with your computed data. CREATE TEMPORARY TABLE tmpFirstTran SELECT CustID, min(Datestamp) as mindate from Transactions group by CustID; update Customer c INNER JOIN tmpFirstTran ft ON ft.CustID = c.ID SET c.First_Transaction = ft.mindate; DROP TEMPORARY TABLE tmpFirstTran; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff McKeon [EMAIL PROTECTED] wrote on 11/05/2004 09:04:06 AM: I have two tables. One has a list of customers. The other has a record of customer transactions including unix datestamps of each transaction. I've added a field to the customer table called First_Transaction I want to update this field with the datestamp of the first transaction for each customer from the Transaction table. I tried this... UPDATE Customer,Transactions set Customer.First_Transaction = MIN(Transactions.Datestamp) Where Customer.ID = Transactions.CustID But this doesn't work because of MIN() grouping. I'm stumped, anyone know how to accomplish this? Thanks, Jeff -- 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]
Slave replication problem
Hello all, We had a power outage this morning and before we could shut down our main MySQL server, power to it was lost. Bad UPS battery. Long story. We replicate this server to two others. On one I get the following error... Jeff McKeon IT Manager*** 1. row *** Master_Host: 10.32.1.10 Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: DB01TC07927-bin.022 Read_Master_Log_Pos: 2223919 Relay_Log_File: DB02TC07927-relay-bin.286 Relay_Log_Pos: 16361931 Relay_Master_Log_File: DB01TC07927-bin.021 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Skip_counter: 0 Exec_master_log_pos: 85068331 Relay_log_space: 18604700 1 row in set (0.00 sec) On the other I'm getting... *** 1. row *** Master_Host: 10.32.1.10 Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: DB01TC07927-bin.021 Read_Master_Log_Pos: 85079027 Relay_Log_File: mis02tc07927-relay-bin.106 Relay_Log_Pos: 4 Relay_Master_Log_File: DB01TC07927-bin.021 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 85079027 Relay_log_space: 4 1 row in set (0.00 sec) From the server.err log I have: 040903 14:00:01 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'DB01TC07927-bin.021' at position 85079027 040903 14:00:01 Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236) 040903 14:00:01 Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log 040903 14:00:01 Slave I/O thread exiting, read up to log 'DB01TC07927-bin.021', position 85079027 How do I fix these problems? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Into outfile
Is there a way to get the output of a select into outfile statement to have the column headers in it and not just the data? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Force the use of an index
Is there a way to force the use of a specific index when issuing a select querie? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very Strange data corruption
Query:
insert into
MIS.simcard(ID,ShipID,Service_Provider,SN,v1,v2,f1,d1,puk1,puk2,pin1,pin
2,TwoStage,Status,DateAssigned,DateDisabled,UserID)
VALUES('NULL', '6889927707', '1', '8988169214000421398', '881621456175',
'', '', '881693156175', '62982149', '', '', '', '1307', '1',
'1085508771', 'NULL', 'jsm');
Always results in a ShipID field value of 2147483647 instead of
6889927707
Even if I just do a simple:
insert into MIS.simcard (ShipID) values ('6889927707');
It does the same darn thing.
ShipID is an Int(11) field
Version 4.0.15
If I change the first digit of the input from a 6 to any other digit, it
gets entered correctly. Any idea what is going on here!?
Version 4.0.15
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very Strange data corruption
Actually the Field type is fine. It was user input error. The person
who created the ship ID added an extra digit, normally they are only 9
digits long. I didn't catch it until I looked at a list of all the
ShipID's together and then it stuck out like a sore thumb.
Thanks for all the explanations..
Jeff
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 25, 2004 2:41 PM
To: Jeff McKeon
Cc: [EMAIL PROTECTED]
Subject: Re: Very Strange data corruption
Jeff,
You are trying to exceed the limits of the INTEGER column.
INTEGERs top out at 2GB-1 (or 2147483647). May I suggest you
change your table to use a larger integer type like BIGINT.
With BIGINT fields you can go all the way to 9223372036854775807.
MySQL will give you the nearest possible number in the event
of an overflow or an underflow. That's why you see the
wrong value for your column after the INSERT.
Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Jeff McKeon
[EMAIL PROTECTED]To:
[EMAIL PROTECTED]
.comcc:
Fax to:
05/25/2004 02:29 Subject: Very
Strange data corruption
PM
Query:
insert into
MIS.simcard(ID,ShipID,Service_Provider,SN,v1,v2,f1,d1,puk1,puk
2,pin1,pin
2,TwoStage,Status,DateAssigned,DateDisabled,UserID)
VALUES('NULL', '6889927707', '1', '8988169214000421398',
'881621456175', '', '', '881693156175', '62982149', '',
'', '', '1307', '1', '1085508771', 'NULL', 'jsm');
Always results in a ShipID field value of 2147483647
instead of 6889927707
Even if I just do a simple:
insert into MIS.simcard (ShipID) values ('6889927707');
It does the same darn thing.
ShipID is an Int(11) field
Version 4.0.15
If I change the first digit of the input from a 6 to any
other digit, it gets entered correctly. Any idea what is
going on here!?
Version 4.0.15
Jeff
--
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]
Dropping tables...
I have to re-create a table every month with a section of data from the main table. I suppose this would be an excellent situation to use views for but being as how MySQL doesn't have view capability yet I'm stuck with this. It's not a big deal but I just want to make sure that Creating, populating, using, dropping a table every month won't impact my database performance. Is there any specific optimization or maintainence I should be doing after dropping the table each month? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Corrupted relay log
Is there any way to fix a corrupted relay log on a slave? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Corrupted relay log
Here's what I did to fix it... Excert from MySql Manual... When you back up your slave's data, you should back up these two small files as well, along with the relay log files. They are needed to resume replication after you restore the slave's data. If you lose the relay logs but still have the `relay-log.info' file, you can check it to determine how far the SQL thread has executed in the master binary logs. Then you can use CHANGE MASTER TO with the MASTER_RELAY_LOG and MASTER_RELAY_POS options to tell the slave to re-read the binary logs from that point. This requires that the binary logs still exist on the master server. I performed these steps... Read info form relay-log.info file. Mysql: Slave Stop; Mysql: Change master to master_host = '192.168.1.10', Master_User = 'name', Master_Password = 'pass', Master_log_File = 'get this from relay-log.info', Master_log_pos = get this from relay-log.info; Mysql: Slave Start; Does anyone see a problem with this method? Thanks, Jeff -Original Message- From: Jeff McKeon Sent: Thursday, April 08, 2004 8:54 AM To: [EMAIL PROTECTED] Subject: Corrupted relay log Is there any way to fix a corrupted relay log on a slave? Jeff -- 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: Does Dropping a table affect it's indexes?
The command you're looking for that drops and recreates in one go is TRUNCATE (http://www.mysql.com/doc/en/TRUNCATE.html). This is the same as: DROP TABLE table; CREATE TABLE table (...); For MyISAM tables, this is likely to be much faster as MySQL can just delete the MYI and MYD files associated with the particular table. I'm using MyIsam table, doing a drop and insert into once a night, then just reading from the table so no optimizing should be needed. If I understand you correctly then, the Truncate command WILL recreate any indexes the table had when it recreates the table? Thanks for the help! Jeff -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Thursday, February 19, 2004 9:35 PM To: Steve Edberg Cc: Jeff McKeon; [EMAIL PROTECTED] Subject: Re: Does Dropping a table affect it's indexes? Steve Edberg wrote: It's my understanding that doing a simple delete delete from table_name actually DOES drop and recreate the table (and thus its indexes). On the other hand, if you are continually adding deleting records, you might well need to do a periodic 'analyze table_name' or 'optimize table_name' to maintain optimum performance clear the deleted record chain. steve Sort of. There's a subtlty here: DELETE FROM table; Will go and delete all rows from a table. If you're using InnoDB tables, new transactions will not see this take effect until you have issued a COMMIT (unless they're set to READ_UNCOMMITED isolation level). I'm not sure if MyISAM is optimised for this special case. The command you're looking for that drops and recreates in one go is TRUNCATE (http://www.mysql.com/doc/en/TRUNCATE.html). This is the same as: DROP TABLE table; CREATE TABLE table (...); For MyISAM tables, this is likely to be much faster as MySQL can just delete the MYI and MYD files associated with the particular table. For InnoDB tables, dropping a table involves manipulating the tablespace. As a result, it doesn't currently support the TRUNCATE statement. DELETE does specifically delete rows one by one, so actually DROPing the table and reCREATE-ing it will be faster. Regards, Chris At 03:21 PM 2/19/04, Jeff McKeon wrote: Quick question... What you drop a table are the indexes for that table dropped to? I'm about to write a script to take a data pull every night and re-populate a table with the results, then have my apps run off of the new consolidated table for a speed increase. If I drop the Consolidated table, then re-create it with the new data pull, will I need to re-create the indexes as well? Is there any performance cost/benefit to simply deleting all data from the table and then re-populating it as opposed to droping and re-creating it? Thanks, Jeff +- ---+ | Steve Edberg [EMAIL PROTECTED] | | Database/Programming/SysAdmin (530)754-9127 | | University of California, Davis http://pgfsun.ucdavis.edu/ | +-- Gort, Klaatu barada nikto! --+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does Dropping a table affect it's indexes?
Quick question... What you drop a table are the indexes for that table dropped to? I'm about to write a script to take a data pull every night and re-populate a table with the results, then have my apps run off of the new consolidated table for a speed increase. If I drop the Consolidated table, then re-create it with the new data pull, will I need to re-create the indexes as well? Is there any performance cost/benefit to simply deleting all data from the table and then re-populating it as opposed to droping and re-creating it? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication corrupted
I rebooted the slave machine after it went unresponsive and now I can't get replication to start. Here is the show slave status printout. Anyone have any idea how to fix this? mysql show slave status \G *** 1. row *** Master_Host: 10.32.1.10 Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: DB01TC07927-bin.005 Read_Master_Log_Pos: 723396631 Relay_Log_File: MIS01TC07927-relay-bin.039 Relay_Log_Pos: 13876528 Relay_Master_Log_File: DB01TC07927-bin.005 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Skip_counter: 0 Exec_master_log_pos: 723207989 Relay_log_space: 14059362 Jeff McKeon IT Manager Telaurus Communications LLC [EMAIL PROTECTED] (973) 889-8990 ex 209 ***The information contained in this communication is confidential. It is intended only for the sole use of the recipient named above and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication, or any of its contents or attachments, is expressly prohibited. If you have received this communication in error, please re-send it to the sender and delete the original message, and any copy of it, from your computer system. Thank You.*** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Question
Is it possible to have 2 database on one server replicating from the same Master server? In other words. DB01 is the Master on System01, System02 has DB01_rep1 and DB01_rep2, each with their own replication from DB01. I need to do this to have a development copy of DB01 on System02. I have production software that pulls data from DB01_rep1 and I need to set up DB01_rep2 to do some development work. When I copy the DB01 snapshot into a new database (DB01_rep2) on System02 and then log into that database and do a Show slave status it shows me the slave status information that was set up for DB01_rep1. Is replication System dependant or database dependant? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Question
-Original Message- From: Tobias Asplund [mailto:[EMAIL PROTECTED] Sent: Monday, December 22, 2003 11:21 AM To: Jeff McKeon Cc: [EMAIL PROTECTED] Subject: Re: Replication Question On Mon, 22 Dec 2003, Jeff McKeon wrote: Is it possible to have 2 database on one server replicating from the same Master server? yes. In other words. DB01 is the Master on System01, System02 has DB01_rep1 and DB01_rep2, each with their own replication from DB01. Shouldn't be a problem. I need to do this to have a development copy of DB01 on System02. I have production software that pulls data from DB01_rep1 and I need to set up DB01_rep2 to do some development work. When I copy the DB01 snapshot into a new database (DB01_rep2) on System02 and then log into that database and do a Show slave status it shows me the slave status information that was set up for DB01_rep1. Just make sure that you're not running against the same logfiles/data, so you can have different relay-logs and master.info files. So I have to do something on the master then to add this second slave on the same system as the first slave? I'm not sure how to set up a separate logfile/data for the second slave... Is this done on the master or slave? Is replication System dependant or database dependant? Neither, it's server instance dependant You mean mysql server as in the software correct? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query help
I have two tables, customer table and a company table The customer table has an ID field that is 8 characters long. The first 4 characters are the company code. We just added a company table that has an id field that contains that companies id code. We also added a field to the customer table that will hold the company ID that the customer belongs to. So. Customer table ID namecompany 12347771joe null 12347772marynull 43210001bob null Company Table ID name 1234Acme 4321Acme_Europe What I now need to do is create an update statement that will match the customer to the company by substring(Customer.ID,1,4) to Company.ID I tried: Jeff McKeon IT Manager Telaurus Communications LLC [EMAIL PROTECTED] (973) 889-8990 ex 209 ***The information contained in this communication is confidential. It is intended only for the sole use of the recipient named above and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication, or any of its contents or attachments, is expressly prohibited. If you have received this communication in error, please re-send it to the sender and delete the original message, and any copy of it, from your computer system. Thank You.*** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Help 2
Damn fat fingers and MS Outlook. I sent the Query Help message before I was finishes typing. Sorry... I have two tables, customer table and a company table The customer table has an ID field that is 8 characters long. The first 4 characters are the company code. We just added a company table that has an id field that contains that companies id code. We also added a field to the customer table that will hold the company ID that the customer belongs to. So. Customer table ID namecompany 12347771joe null 12347772marynull 43210001bob null Company Table ID name 1234Acme 4321Acme_Europe What I now need to do is create an update statement that will match the customer to the company by substring(Customer.ID,1,4) to Company.ID I tried: update RemoteStation set Company_ID=Company.ID where substring(Company_ID,1,4) like Company.ID; But it didn't work. Any suggestions? Thanks for the help, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Find duplicates query
I'm trying to search a table for duplicate entries. A record is a dup if fields Fee, Fie, Foe are equal in two records. Would this query be correct to search the table for duplicates? Select Fee,Fie,Foe From TableFoo Group by Fee,Fie,Foe Having Count(*) 1; Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing and repair impact
We have a database that supports our customers and runs pretty much 24/7/365. Obviously we need to keep this database healthy and optimized. I've read the official documentation and third party books on MySQL and they all are very good at explaining what should be done and how to do it but none that I can find explain the impact of running mysqlcheck commands on the other applications or user issuing reads and writes to the database at the same time. Is it prudent to set up a script that runs mysqlcheck on the whole database at a set time each week? Will users and applications be prevented from accessing tables for reads and writes while the mysqlcheck process is running or will they simply suffer a performance hit? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behavior on insert
I have a PHP page that takes data from a form and inserts it into a
table:
Show columns:
+++--+-+-+--
--+
| Field | Type | Null | Key | Default | Extra
|
+++--+-+-+--
--+
| ID | mediumint(10) | | PRI | NULL|
auto_increment |
| userid | varchar(20) binary | | | |
|
| name | varchar(20) binary | | | |
|
| tier | int(2) | | | 0 |
|
| price | double | | | 0 |
|
| tierNumber | int(2) | | | 0 |
|
| Min| double | | | 0 |
|
+++--+-+-+--
--+
I have a while loop that insterts the records in the correct order (by
tierNumber).
INSERT INTO TarifBuilder SET
name='{$_POST'name']}',tier='$tier',price='$price',tierNumber='$count',M
in='{$_POST'min']}';
However when I go to the database and do a select * from tablename;
the records are in the table in the reverse order!!
Even the auto increment is in reverse order...
| 65 || 2-gaf | 0 | 0.0004688 | 0 | 0.3
|
| 66 || 2-gaf | 1536 | 0.0002917 | 1 | 0.3
|
| 67 || 2-gaf | 6144 | 0.0002344 | 2 | 0.3
|
| 68 || 2-gaf | 15360 | 0.0001172 | 3 | 0.3
|
| 69 || 3-gaf | 0 | 0.0001172 | 0 |0
|
| 70 || 3-gaf | 0 | 0.0001172 | 1 |0
|
| 122 || test-decreasing | 200 | 1.001e-05 | 2 | 0.51
|
| 121 || test-decreasing | 100 | 2.002e-05 | 1 | 0.51
|
| 120 || test-decreasing | 0 | 3.003e-05 | 0 | 0.51
|
+-++-+---+---++-
-+
Why is this?
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange behavior on insert
I understand how to use the Order By clause on a select, I'm trying to better understand why does this happen on the insert. Jeff -Original Message- From: Dan Wilterding [mailto:[EMAIL PROTECTED] Sent: Thursday, November 20, 2003 11:39 AM To: [EMAIL PROTECTED] Subject: Re: Strange behavior on insert On 20 Nov 2003 at 11:12, Jeff McKeon wrote: However when I go to the database and do a select * from tablename; the records are in the table in the reverse order!! Even the auto increment is in reverse order... If you wish to retrieve the data in a particular order you must use order by because the database itself does not depend on a sequential storage of the records. Dan Wilterding [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]
Mysql server time setting.
I have two servers replicating. Server one to server two. If I do a show variables on server one it show the timezone as EST, on server two it shows it as GMT. Where is this set and how can I change server one to GMT? The linux box itself, that mysql server one runs on is set for GMT Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql server time setting.
I'm running mysql on a redhat system starting it from a script in /etc/init.d/ as is the default with the rpm install. I can't seem to set the timezone environmental variable to change nomatter what I put in the /etc/my.cnf file. Can anyone help me with this? Jeff -Original Message- From: Jeff McKeon Sent: Wednesday, November 19, 2003 4:54 PM To: [EMAIL PROTECTED] Subject: Mysql server time setting. I have two servers replicating. Server one to server two. If I do a show variables on server one it show the timezone as EST, on server two it shows it as GMT. Where is this set and how can I change server one to GMT? The linux box itself, that mysql server one runs on is set for GMT Thanks, Jeff -- 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]
Data definition problem?
I have a table that has a field type DOUBLE. I need to put into it data like '0.123' however any number less than .0001 (like .999) show up in the field like '1e-05' Also when I retieve the field in a PHP page it is return like '1e-05'. Do I have the wrong column definition or am I doing something else wrong here. How can I get it to display correctly when returned by a query? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error message problem
I just upgraded to Server version 4.0.15-standard, I just tried to do a query and got a syntax error but the error message is incomplete. ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where RemoteStation.Name not like 'zz%' and CopyOf = '0'' at li Is this a known bug or a setting I have wrong somehwere? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error message problem
Yes there was a syntax error. I used an and instead of in the where clause. However, my concern is that the error message was truncated. Jeff -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 9:53 AM To: Jeff McKeon; [EMAIL PROTECTED] Subject: RE: Error message problem Was there actually a syntax error? Can you please post the query? The display for the error message appears to be truncated. -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 8:44 AM To: [EMAIL PROTECTED] Subject: Error message problem I just upgraded to Server version 4.0.15-standard, I just tried to do a query and got a syntax error but the error message is incomplete. ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where RemoteStation.Name not like 'zz%' and CopyOf = '0'' at li Is this a known bug or a setting I have wrong somehwere? Thanks, Jeff -- 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]
Kill a query
Is there a way to abort a query after it's running? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query with temporary table
Is is possible to do a select query with a left join from a real table to a temporary table? I'm trying it but keep getting unkown table 'tablename' in field list error. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Detect temporary tables
I know I can issue show tables to give me a list of tables from the current database, how can I do the same thing with temporary tables? That is, is there a command to list the current temporary tables? You can't. RATS!!! Jeff -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 4:38 AM To: [EMAIL PROTECTED] Subject: Re: Detect temporary tables Jeff McKeon [EMAIL PROTECTED] wrote: I know I can issue show tables to give me a list of tables from the current database, how can I do the same thing with temporary tables? That is, is there a command to list the current temporary tables? You can't. -- 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Detect temporary tables
I know I can issue show tables to give me a list of tables from the current database, how can I do the same thing with temporary tables? That is, is there a command to list the current temporary tables? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Challenging query....
I have a table that contains customer revenue information. REVENUE TABLE: Date, customer name, CustomerID, revenue, quantity I need to create a query that will produce the following result Year, Month, Customer1_rev, customer2_REV, customer3_rev, etc... 2002, 01, 0, $30.00, $15.00 2002, 02, $25.00, $50.00, $10.00 2002, 03, $10.00, $25.00, $40.00 Etc.. Can this be done with a single query??? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Challenging query....
Yeah, I already got that far but it's just short of what I need as an end result. Is there a way to generate a line number for a query return within the returned rows?? For instance, if I return 5 rows from a query, is there a command or function I can put in a query to add a column that contains the row number returned? Select somecommand(),blah, blew from table where blah 1; +---+---+---+ | row | blah | blew | +---+---+---+ | row | blah | blew | | row | blah | blew | | row | blah | blew | | row | blah | blew | Jeff -Original Message- From: Gabriel Ricard [mailto:[EMAIL PROTECTED] Sent: Thursday, October 16, 2003 11:40 AM To: Jeff McKeon Cc: [EMAIL PROTECTED] Subject: Re: Challenging query I think the closest you can get is something like this: SELECT YEAR(TheDate) AS Year,MONTH(TheDate) AS Month,CustomerName,CustomerID,SUM(Revenue*Quantity) AS Revenue FROM rev GROUP BY Year, Month ORDER BY Year,Month,CustomerID A table with this data: ++--++-+--+ | TheDate| CustomerName | CustomerID | Revenue | Quantity | ++--++-+--+ | 2003-10-16 | Bob | 1 | 10.00 |1 | | 2003-10-16 | Bob | 1 |5.00 |2 | | 2003-09-01 | Bob | 1 | 20.00 |5 | | 2003-10-10 | Bob | 1 |5.00 |2 | ++--++-+--+ Would give you something like this: +--+---+--++-+ | Year | Month | CustomerName | CustomerID | Revenue | +--+---+--++-+ | 2003 | 9 | Bob | 1 | 100.00 | | 2003 |10 | Bob | 1 | 30.00 | +--+---+--++-+ Or if the Revenue field is a total, then just don't multiply it by Quantity. So far as I know, there is no [easy?] way to generate dynamic columns in the result set like you're looking for. You can generate it the way I described and then manipulate that data into your desired format in the application layer. - Gabriel On Thursday, October 16, 2003, at 10:21 AM, Jeff McKeon wrote: I have a table that contains customer revenue information. REVENUE TABLE: Date, customer name, CustomerID, revenue, quantity I need to create a query that will produce the following result Year, Month, Customer1_rev, customer2_REV, customer3_rev, etc... 2002, 01, 0, $30.00, $15.00 2002, 02, $25.00, $50.00, $10.00 2002, 03, $10.00, $25.00, $40.00 Etc.. Can this be done with a single query??? Jeff -- 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: Challenging query....
Sorry, hit ctrl-s by accident and sent the email before I was done... Yeah, I already got that far but it's just short of what I need as an end result. Is there a way to generate a line number for a query return within the returned rows?? For instance, if I return 5 rows from a query, is there a command or function I can put in a query to add a column that contains the row number returned? Select some_command(),blah, blew from table where blah 1; +---+---+---+ | row | blah | blew | +---+---+---+ | 1 | blah | blew | | 2 | blah | blew | | 3 | blah | blew | | 4 | blah | blew | +---+---+---+ Thanks, Jeff -Original Message- From: Gabriel Ricard [mailto:[EMAIL PROTECTED] Sent: Thursday, October 16, 2003 11:40 AM To: Jeff McKeon Cc: [EMAIL PROTECTED] Subject: Re: Challenging query I think the closest you can get is something like this: SELECT YEAR(TheDate) AS Year,MONTH(TheDate) AS Month,CustomerName,CustomerID,SUM(Revenue*Quantity) AS Revenue FROM rev GROUP BY Year, Month ORDER BY Year,Month,CustomerID A table with this data: ++--++-+--+ | TheDate| CustomerName | CustomerID | Revenue | Quantity | ++--++-+--+ | 2003-10-16 | Bob | 1 | 10.00 |1 | | 2003-10-16 | Bob | 1 |5.00 |2 | | 2003-09-01 | Bob | 1 | 20.00 |5 | | 2003-10-10 | Bob | 1 |5.00 |2 | ++--++-+--+ Would give you something like this: +--+---+--++-+ | Year | Month | CustomerName | CustomerID | Revenue | +--+---+--++-+ | 2003 | 9 | Bob | 1 | 100.00 | | 2003 |10 | Bob | 1 | 30.00 | +--+---+--++-+ Or if the Revenue field is a total, then just don't multiply it by Quantity. So far as I know, there is no [easy?] way to generate dynamic columns in the result set like you're looking for. You can generate it the way I described and then manipulate that data into your desired format in the application layer. - Gabriel On Thursday, October 16, 2003, at 10:21 AM, Jeff McKeon wrote: I have a table that contains customer revenue information. REVENUE TABLE: Date, customer name, CustomerID, revenue, quantity I need to create a query that will produce the following result Year, Month, Customer1_rev, customer2_REV, customer3_rev, etc... 2002, 01, 0, $30.00, $15.00 2002, 02, $25.00, $50.00, $10.00 2002, 03, $10.00, $25.00, $40.00 Etc.. Can this be done with a single query??? Jeff -- 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: Challenging query....
If you have a way to generate the query code dynamically (e.g. using a loop in C, PHP etc.), you can build a query using aliased tables : SELECT YEAR(a.date) AS year, MONTH(a.date) AS month, SUM(a.revenue) AS cust1_rev, SUM(b.revenue) AS cust2_rev, SUM(c.revenue) AS cust3_rev FROM revenue a, revenue b, revenue c WHERE a.customer_id = 1 AND (YEAR(b.date) = YEAR(a.date) AND MONTH(b.date) = MONTH(a.date) AND b.customer_id = 2) AND (YEAR(c.date) = YEAR(c.date) AND MONTH(c.date) = MONTH(c.date) AND c.customer_id = 3) GROUP BY year, month Ok, that looks promising as I'll be using PHP, but I'm a little fuzzy on the logic you've set. What are aliased tables and how would I define, use them in an loop? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Challenging query....
Do this query:
SELECT YEAR(TheDate) AS Year,MONTH(TheDate) AS
Month,CustomerName,CustomerID,SUM(Revenue*Quantity) AS
Revenue FROM rev GROUP BY Year, Month ORDER BY Year,Month,CustomerID
In practice this would change to something like...
$data=mysql_query(SELECT YEAR(TheDate) AS Year,MONTH(TheDate) AS
Month,CustomerName,CustomerID,SUM(Revenue*Quantity) AS
Revenue FROM rev GROUP BY Year, Month ORDER BY Year,Month,CustomerID,
$link_id);
Then use this code:
$revenues = array();
There's obviously some php code missing here, I'd need to retrive the
query results with something like
$revenues = mysql_fetch_rows($data);
To dump the data into the array. Is this correct?
foreach( $results as $result )
{
$revenues[ $result['Year'] ][ $result['Month'] ][
$result['CustomerID'] ] = $result['Revenue'];
}
The result will be a simple multi-dimensional array with a
minimal SQL
query and minimal application code.
Jeff
-Original Message-
From: Gabriel Ricard [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 1:47 PM
To: Jeff McKeon; [EMAIL PROTECTED]
Subject: Re: Challenging query
Or you could just do one simply query as I explained previously,
retrieve the data in PHP, and group it by date rather than
spending the
same time in PHP generating a massive, inefficient query (and if you
have a large number of customers, you won't generate a query larger
than the maximum MySQL packet size, or incur any limits on the number
of joins or aliases).
Do this query:
SELECT YEAR(TheDate) AS Year,MONTH(TheDate) AS
Month,CustomerName,CustomerID,SUM(Revenue*Quantity) AS
Revenue FROM rev GROUP BY Year, Month ORDER BY Year,Month,CustomerID
Then use this code:
$revenues = array();
foreach( $results as $result )
{
$revenues[ $result['Year'] ][ $result['Month'] ][
$result['CustomerID'] ] = $result['Revenue'];
}
The result will be a simple multi-dimensional array with a
minimal SQL
query and minimal application code.
- Gabriel
On Thursday, October 16, 2003, at 01:19 PM, Rory McKinley wrote:
Hi Jeff
OK, aliasing table is creating a copy of one table but calling it
something
different, so you compare a table to itself e.g.:
FROM revenue a, revenue b, revenue c COULD ALSO BE FROM
revenue AS a,
revenue AS b, revenue AS c
I am referencing revenue three times but have aliased it as
a, b, and
c to
make sure that my predicate makes sense.
As for the loop, I can give you something off the top of my head in
rough
(very!) PHP , if you don't come right, I can sit down and
do the code a
little more detailed
For simplification purposes, I am going to assume that you can alias
tables
as numbers (e.g. 1, 2, 3 instead of a, b, c) but I suggest
you check
if this
is possible - if you can't there is a work around that just
requires a
bit
more thought
//Assume you have an array that has all your client ids in
$client_id_array.
//Create base values based on the first id...
$select_base = YEAR(1.date) AS year, MONTH(1.date) AS month,
SUM(1.revenue) AS cust1_rev
$for_base = FROM revenue 1
$predicate_base = WHERE 1.customer_id = .$client_id_array[0]
//Now loop through and append additional items to each
string for each
instance of a client
//Start at 1 not zero as we already have accounted for the first id
above
for($j=1; $j count($client_id_array); $j++)
{
$select_base = $select_base.', SUM('.($j+1).') AS
cust'.($j+1).'_rev';
$for_base = $for_base.', revenue '.($j+1);
$predicate_base = $predicate_base.' AND (YEAR('.($j+1).'.date) =
YEAR(1.date) AND MONTH('.($j+1).'.date) =MONTH(1.date) AND
'.($j+1).'.customer_id = '.$client_id_array[$j].')';
}
//Once your loop is done, put the parts together
$query = $select_base.$for_base.$predicate_base;
If you can't use numbers, you can use single letters, but that
requires a
little more work incrementing ASCII numbers and then converting to
letters -
also makes things way more complicated if you have more than 26
clients :)
but still doable.
HTH
Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
There are 10 kinds of people in this world,
those who understand binary and those who don't (Unknown)
- Original Message -
From: Jeff McKeon [EMAIL PROTECTED]
To: Rory McKinley [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 6:51 PM
Subject: RE: Challenging query
If you have a way to generate the query code dynamically
(e.g. using
a loop in C, PHP etc.), you can build a query using
aliased tables :
SELECT YEAR(a.date) AS year, MONTH(a.date) AS month,
SUM(a.revenue) AS cust1_rev,
SUM(b.revenue) AS cust2_rev, SUM(c.revenue) AS cust3_rev
FROM revenue
a, revenue b, revenue c WHERE a.customer_id = 1 AND
(YEAR(b.date) =
YEAR(a.date
RE: Challenging query....
$revenues = mysql_fetch_rows($data);
To dump the data into the array. Is this correct?
There is no single function in the mysql extension to retrieve all
records at once. You can do this with the dbx extension, which wraps
MySQL ( other DBMS) functions. with the MySQL extension
you'll have to
loop through the results and call mysql_fetch_row. There is a
comprehensive manual on these functions available at
http://php.net/mysql, which includes many useful tips examples from
other users that will help you out.
So something like the while loop
While($query_data=mysql_fetch_row($data)) {
$revenues[ $query_data[0] ][ $query_data[1] ][ $query_data[2] ]
= $result['Revenue'];
}
Then I just need to retrieve the data out of the multi-dimensional array
and display it or write it to a file?
Jeff
-Original Message-
From: Gabriel Ricard [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 2:11 PM
To: Jeff McKeon
Cc: [EMAIL PROTECTED]
Subject: Re: Challenging query
On Thursday, October 16, 2003, at 02:04 PM, Jeff McKeon wrote:
Do this query:
SELECT YEAR(TheDate) AS Year,MONTH(TheDate) AS
Month,CustomerName,CustomerID,SUM(Revenue*Quantity) AS
Revenue FROM
rev GROUP BY Year, Month ORDER BY Year,Month,CustomerID
In practice this would change to something like...
$data=mysql_query(SELECT YEAR(TheDate) AS Year,MONTH(TheDate) AS
Month,CustomerName,CustomerID,SUM(Revenue*Quantity) AS Revenue FROM
rev GROUP BY Year, Month ORDER BY Year,Month,CustomerID, $link_id);
Yep.
Then use this code:
$revenues = array();
There's obviously some php code missing here, I'd need to
retrive the
query results with something like
Indeed. I made no assumptions as to whether or not you use
the mysql_*
functions directly, or perhaps dbx or PEAR__DB, ADOdb, etc.
$revenues = mysql_fetch_rows($data);
To dump the data into the array. Is this correct?
There is no single function in the mysql extension to retrieve all
records at once. You can do this with the dbx extension, which wraps
MySQL ( other DBMS) functions. with the MySQL extension
you'll have to
loop through the results and call mysql_fetch_row. There is a
comprehensive manual on these functions available at
http://php.net/mysql, which includes many useful tips examples from
other users that will help you out.
- Gabriel
foreach( $results as $result )
{
$revenues[ $result['Year'] ][ $result['Month'] ][
$result['CustomerID'] ] = $result['Revenue']; }
The result will be a simple multi-dimensional array with a minimal
SQL query and minimal application code.
Jeff
-Original Message-
From: Gabriel Ricard [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 1:47 PM
To: Jeff McKeon; [EMAIL PROTECTED]
Subject: Re: Challenging query
Or you could just do one simply query as I explained previously,
retrieve the data in PHP, and group it by date rather than
spending the
same time in PHP generating a massive, inefficient query
(and if you
have a large number of customers, you won't generate a query larger
than the maximum MySQL packet size, or incur any limits on
the number
of joins or aliases).
Do this query:
SELECT YEAR(TheDate) AS Year,MONTH(TheDate) AS
Month,CustomerName,CustomerID,SUM(Revenue*Quantity) AS
Revenue FROM rev GROUP BY Year, Month ORDER BY
Year,Month,CustomerID
Then use this code:
$revenues = array();
foreach( $results as $result )
{
$revenues[ $result['Year'] ][ $result['Month'] ][
$result['CustomerID'] ] = $result['Revenue'];
}
The result will be a simple multi-dimensional array with a
minimal SQL
query and minimal application code.
- Gabriel
On Thursday, October 16, 2003, at 01:19 PM, Rory McKinley wrote:
Hi Jeff
OK, aliasing table is creating a copy of one table but calling it
something
different, so you compare a table to itself e.g.:
FROM revenue a, revenue b, revenue c COULD ALSO BE FROM
revenue AS a,
revenue AS b, revenue AS c
I am referencing revenue three times but have aliased it as
a, b, and
c to
make sure that my predicate makes sense.
As for the loop, I can give you something off the top of
my head in
rough
(very!) PHP , if you don't come right, I can sit down and
do the code a
little more detailed
For simplification purposes, I am going to assume that
you can alias
tables
as numbers (e.g. 1, 2, 3 instead of a, b, c) but I suggest
you check
if this
is possible - if you can't there is a work around that just
requires a
bit
more thought
//Assume you have an array that has all your client ids in
$client_id_array.
//Create base values based on the first id...
$select_base = YEAR(1.date) AS year, MONTH(1.date) AS month,
SUM(1.revenue) AS cust1_rev
$for_base = FROM revenue 1
Data from two tables in one query
We're currently running mysql ver 3.23 with plans to upgrade to 4.x soon but we're not ready yet. I have two tables that are identical, table1 and table1_old. Table1_old contains all data writen to table1 prior to july of 2003. I need to do a single query that pulls the same fields from both tables and output's them in the correct order. Is this possible? I'm outputing this data to PHP web page. I know in ver 4.x there is table called a merge table for this type of thing but we're stuck with 3.23 for now. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data from two tables in one query
Paul, - Original poster suggests that MERGE tables are not implemented until 4.0, but this is incorrect. MERGE tables are available as of MySQL 3.23.25. Very true, I must have misread. Now my question is, when I create a merge table, do I have to include every field from the two tables I'm merging or can I just grab the ones I want? Jeff -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 10:54 AM To: Rory McKinley; Jeff McKeon; [EMAIL PROTECTED] Subject: Re: Data from two tables in one query Two points about the messages below: - With respect to the suggestion to use UNION: Original poster is using 3.23, so UNION cannot be used (it's implemented in MySQL 4.0) - Original poster suggests that MERGE tables are not implemented until 4.0, but this is incorrect. MERGE tables are available as of MySQL 3.23.25. So it may be the case that a MERGE table will solve the problem. At 16:31 +0200 10/14/03, Rory McKinley wrote: Hi Jeff Have you looked at UNIONS? An example would be something as follows: SELECT field_1, field_2, field_3 FROM table 1 WHERE blah blah blah UNION SELECT field_1, field_2, field_3 FROM table 1_old WHERE blah blah blah ORDER BY field_1 This should do the trick... Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Jeff McKeon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 4:01 PM Subject: Data from two tables in one query We're currently running mysql ver 3.23 with plans to upgrade to 4.x soon but we're not ready yet. I have two tables that are identical, table1 and table1_old. Table1_old contains all data writen to table1 prior to july of 2003. I need to do a single query that pulls the same fields from both tables and output's them in the correct order. Is this possible? I'm outputing this data to PHP web page. I know in ver 4.x there is table called a merge table for this type of thing but we're stuck with 3.23 for now. Jeff -- 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: Data from two tables in one query
Ok, created the merge table, now I get this error when I log into the mysql server [snip] Didn't find any fields in table 'SuperMailbox' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3903 to server version: 3.23.56 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show columns from SuperMailbox; ERROR 1016: Can't open file: 'SuperMailbox.MRG'. (errno: 143) [snip] Any ideas what I've screwed up now? Thanks, Jeff -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 11:19 AM To: Jeff McKeon; [EMAIL PROTECTED] Subject: RE: Data from two tables in one query At 11:14 -0400 10/14/03, Jeff McKeon wrote: Paul, - Original poster suggests that MERGE tables are not implemented until 4.0, but this is incorrect. MERGE tables are available as of MySQL 3.23.25. Very true, I must have misread. Now my question is, when I create a merge table, do I have to include every field from the two tables I'm merging or can I just grab the ones I want? The former. http://www.mysql.com/doc/en/MERGE.html -- 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: Data from two tables in one query
, Status tinytext, StatusDesc text, DontCharge tinyint(4), PendingTo tinyint(4), SentTo tinytext, FromAddress text, ToAddress tinytext, FromName text, ToName text, Size INT(11), CompressedSize INT(11), Type tinytext, Priority tinyint(4), ReadFlagSent int(11), Accounting int(11), QRG tinytext, Modulation tinytext, RetryCount tinyint(4), LastTry int(11), RemoteFaxID tinytext, LLCharge tinytext, LLTime int(11), LLPages int(11), ExternalSerial text, GPS tinytext, Price Double, User_ID int(11),Pay_ID int(11), Tarif_ID int(11), CopyOf int(11), ShipID int(11), key(ID)) Type=MERGE UNION=(Mailbox_Old,Mailbox); Jeff -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 11:59 AM To: Jeff McKeon; [EMAIL PROTECTED] Subject: RE: Data from two tables in one query At 11:50 -0400 10/14/03, Jeff McKeon wrote: Ok, created the merge table, now I get this error when I log into the mysql server [snip] Didn't find any fields in table 'SuperMailbox' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3903 to server version: 3.23.56 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show columns from SuperMailbox; ERROR 1016: Can't open file: 'SuperMailbox.MRG'. (errno: 143) [snip] Any ideas what I've screwed up now? Thanks, Jeff Hmmm... % perror 143 Error code 143: Unknown error: 143 143 = Conflicting table definitions in sub-tables of MERGE table Can you post the results for SHOW CREATE TABLE for the tables you're trying to merge? -- 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: Data from two tables in one query
Never mind, I used the show create table Mailbox results as my create table supermailbox query and it worked. Thanks Jeff -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 11:59 AM To: Jeff McKeon; [EMAIL PROTECTED] Subject: RE: Data from two tables in one query At 11:50 -0400 10/14/03, Jeff McKeon wrote: Ok, created the merge table, now I get this error when I log into the mysql server [snip] Didn't find any fields in table 'SuperMailbox' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3903 to server version: 3.23.56 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show columns from SuperMailbox; ERROR 1016: Can't open file: 'SuperMailbox.MRG'. (errno: 143) [snip] Any ideas what I've screwed up now? Thanks, Jeff Hmmm... % perror 143 Error code 143: Unknown error: 143 143 = Conflicting table definitions in sub-tables of MERGE table Can you post the results for SHOW CREATE TABLE for the tables you're trying to merge? -- 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]
Finding time elapsed
I have two columns in a table, both timestamp(14). The first one holds a start date and the second one holds an end date. Is there any built in mysql function to subtrack timestamp1 from timestamp2 and get the elapsed time between? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Shell Script to Insert Data
Put the full path to mysql in the script... Jeff -Original Message- From: Mike Tuller [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 1:07 PM To: [EMAIL PROTECTED] Subject: Shell Script to Insert Data I am trying to create a script that will insert data. Right now I am just using something simple to test this out, but I can't get it to work. Here is what I have. mysql --user=root --password= Database_Name; INSERT INTO table_name (column_name) VALUES (value); After I run the script, I check the data, and nothing was entered. When I run each statement on it's own (not from a script file, but in the shell) Everything seems to work. It just doesn't work when you try to run it from a script. Any ideas? Mike Tuller -- 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: Shell Script to Insert Data
The way I usually do it is this.. I have a shell scipt called query It contains: /fullpath/mysql --user=root --password= Database_Name I then make new scripts for the queries as such: (query file name: Select_all.sql) Select * from Table where blah=foo; To run this I then execute the command ./query Select_all.sql This outputs the results to the standard out. You could also ./query Select_all.sql /tmp/some_output_file Hope this helps. Jeff -Original Message- From: Mike Tuller [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 1:07 PM To: [EMAIL PROTECTED] Subject: Shell Script to Insert Data I am trying to create a script that will insert data. Right now I am just using something simple to test this out, but I can't get it to work. Here is what I have. mysql --user=root --password= Database_Name; INSERT INTO table_name (column_name) VALUES (value); After I run the script, I check the data, and nothing was entered. When I run each statement on it's own (not from a script file, but in the shell) Everything seems to work. It just doesn't work when you try to run it from a script. Any ideas? Mike Tuller -- 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: Changing the data directory.
At 4:22 PM -0400 9/19/03, Jeff McKeon wrote: We've got mysql 3.23 installed on a redhat system via the rpm's that come with RedHat 8.0. I'd like to change the default data directory so something other than /var/lib/mysql. I know this is supposed to be possible with a start switch of --datadir=/path/to/data but it doesn't seem to work. It should work. Perhaps I'm putting the switch in incorrectly then because when I do... /etc/init.d/mysqld --datadir=/path/to/data And then do... Mysqladmin variables The output says the data directory is the default /var/lib/mysql/ The startup of mysql uses the /etc/init.d/mysqld script so I suppose I'll need to change something in that and add the switch, I just can't seem to figure out where. I wouldn't change the script, it'll get overwritten if you upgrade. You might try editing /etc/my.cnf (create it if it doesn't exist) and add this to it: [mysqld] datadir=/path/to/data This I tried and the results were that the server didn't start properly... Changed the my.cnf to: [mysqld] datadir=/DATA/mysql/data socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Stopped and started mysqld: [EMAIL PROTECTED] root]# /etc/init.d/mysqld stop Stopping MySQL:[ OK ] [EMAIL PROTECTED] root]# /etc/init.d/mysqld start Starting MySQL:[ OK ] [EMAIL PROTECTED] root]# mysqladmin variables mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists! Alternatively, rename /var/lib/mysql to something else (or remove it) and recreate /var/lib/mysql as a symlink to where you really want the data directory. Make sure the target of the symlink exists. Any suggestions? Thanks, Jeff -- 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]
Changing the data directory.
We've got mysql 3.23 installed on a redhat system via the rpm's that come with RedHat 8.0. I'd like to change the default data directory so something other than /var/lib/mysql. I know this is supposed to be possible with a start switch of --datadir=/path/to/data but it doesn't seem to work. The startup of mysql uses the /etc/init.d/mysqld script so I suppose I'll need to change something in that and add the switch, I just can't seem to figure out where. Any suggestions? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joining tables from two different databases
I have an existing database with a lot of information, I need to create a new database to record inventory information that pertains to records in the first database. I'd like to keep these two database's separate. Is it possible to relate a record in one database to a record in another and do queries that pull from both databases? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Joining tables from two different databases
That's what I thought. Thanks for the advise!! :o) Jeff -Original Message- From: Joseph Bueno [mailto:[EMAIL PROTECTED] Sent: Monday, September 15, 2003 9:26 AM To: Jeff McKeon Cc: [EMAIL PROTECTED] Subject: Re: Joining tables from two different databases Jeff McKeon wrote: I have an existing database with a lot of information, I need to create a new database to record inventory information that pertains to records in the first database. I'd like to keep these two database's separate. Is it possible to relate a record in one database to a record in another and do queries that pull from both databases? Thanks, Jeff Hi, Yes you can do that. Just prefix table name with database name in your query: select * from table1,database2.table2 where table1.field1=database2.table2.field2 Hope this helps, Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrading
We are currently running production on ver 3.23. We have two db servers that are in need of hardware upgrade. DB1 replicates to DB2. I plan on taking DB2 offline, upgrading RAM and Processors, installing latest RH OS and MySQL 4.0. Then replace DB1 with the upgraded DB2 making it the new DB1 and then repeating the process with the old DB1 making it the new DB2. Here are my questions. Any known problmes with RH9.0 and MySQL 4.0? Our current 3.23 db uses MyISAM tables. I've read the how to on upgrading from 3.23 to 4.0 but I'm not really upgrading as much as moving the database to another server that just happens to be ver 4.0 instead of 3.23. If I do a On new server: # mysql create dbname On old server: # mysqldump dbname | mysql -h newserver dbname Will this take care of any table changes or will this cause problems? If I do things this way will I still need to run the mysql_fix_privilege_tables, mysql_convert_table_format etc? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup procedure
All, I'm looking for opinions/suggestions on a backup procedure I plan on implementing. All databases (DBXX) will be MySQL ver 4.0 All our applications work with DB01. DB01 replicates to DB02. Once a day I will Stop the slave on DB02, lock the tables, flush the logs and perform a mysqldump of the database. I will then copy the existing bin.log files and config filesls to a backup directory and then backup the db dump and bin.logs to an external backup device. A) this seems like the most non-intrusive way to get a clean backup of the database, is it? B) I'm not sure how I'm going to automate the unlocking of the tables and the restart of the slave only AFTER the dump is done. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading to ver 4.0
We are currently running production on ver 3.23. We have two db servers that are in need of hardware upgrade. DB1 replicates to DB2. I plan on taking DB2 offline, upgrading RAM and Processors, installing latest RH OS and MySQL 4.0. Then replace DB1 with the upgraded DB2 making it the new DB1 and then repeating the process with the old DB1 making it the new DB2. Here are my questions. Any known problmes with RH9.0 and MySQL 4.0? Our current 3.23 db uses MyISAM tables. I've read the how to on upgrading from 3.23 to 4.0 but I'm not really upgrading as much as moving the database to another server that just happens to be ver 4.0 instead of 3.23. If I do a On new server: # mysql create dbname On old server: # mysqldump dbname | mysql -h newserver dbname Will this take care of any table changes or will this cause problems? If I do things this way will I still need to run the mysql_fix_privilege_tables, mysql_convert_table_format etc? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup procedure
All, I'm looking for opinions/suggestions on a backup procedure I plan on implementing. All databases (DBXX) will be MySQL ver 4.0 All our applications work with DB01. DB01 replicates to DB02. Once a day I will Stop the slave on DB02, lock the tables, flush the logs and perform a mysqldump of the database. I will then copy the existing bin.log files and config filesls to a backup directory and then backup the db dump and bin.logs to an external backup device. A) this seems like the most non-intrusive way to get a clean backup of the database, is it? B) I'm not sure how I'm going to automate the unlocking of the tables and the restart of the slave only AFTER the dump is done. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Var/log/mysql/mysql.log
Quick log question... We've got our 3.23 db installed with all the data on the /home partition which is ok. The *-bin.00x logs are also in that data directory. The Var/log/mysql/mysql.log however on our root drive has grown to 11gig. Our Root partion is not as large as our Home partition and I need to know if I can delete or trim down the mysql.log without hurting replication at all. If not, how can I move it to the Home partition? If it can't be done without a recompile, it's not a huge problem as I plan on upgrading the hardware and db to 4.x in the next week or so, but I just need to know. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Var/log/mysql/mysql.log
We've got our 3.23 db installed with all the data on the /home partition which is ok. The *-bin.00x logs are also in that data directory. The Var/log/mysql/mysql.log however on our root drive has grown to 11gig. Our Root partion is not as large as our Home partition and I need to know if I can delete or trim down the mysql.log without hurting replication at all. If not, how can I move it to the Home partition? If it can't be done without a recompile, it's not a huge problem as I plan on upgrading the hardware and db to 4.x in the next week or so, but I just need to know. It's a file of general query logs. It isn't related to the replication. If you want to turn off logging just stop MySQL server and start it without --log option. -- 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/mysql? [EMAIL PROTECTED] Egor, Thanks for the reply. That is what I thought but wanted to make sure. Can logrotate be used with this log file or will there be problems? Also, if I just wanted to kill the current mysql.log file without restarting mysql by simply deleting it(it's in production and stopping the db means our entire software system must be shutdown which is a pain)? Will MySql simply create a new file and go about it's merry way or will it error out? The current file is 12gig. If we could kill the current file in this manner it would buy us the time we need until we do the rebuild/upgrade later next week. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: month by month count
Select monthname(yourdatefield) as month, year(yourdatefield) as year, count(*) From yourtable Group by year, month; Jeff McKeon IT Manager Telaurus Communications LLC [EMAIL PROTECTED] (973) 889-8990 ex 209 ***The information contained in this communication is confidential. It is intended only for the sole use of the recipient named above and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication, or any of its contents or attachments, is expressly prohibited. If you have received this communication in error, please re-send it to the sender and delete the original message, and any copy of it, from your computer system. Thank You.*** -Original Message- From: Terence [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 1:16 AM To: Scott Haneda; MySql Subject: Re: month by month count select count(*), monthname(yourfield) from yourtable where year = '2003' group by monthname(yourfield) - Original Message - From: Scott Haneda [EMAIL PROTECTED] To: MySql [EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 12:38 PM Subject: month by month count I need to generate record counts for each month in a year, just one year at a time, but a full year at a glance. I am thinking 12 hits to the DB is perhaps not the most idea way to do it, is there a way to rip this out in one go? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- 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]
Replication question
Hey all, I have 3 databases replicating (ver 3.23) A to B and B to C On C I want to modify one of the tables and add a column. Tables A and B will not have this new column added. Will this cause a problem replicating data form B to C? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tracking a delete
Ok, I know it WAS there because we have two similar tables that should contain sister records. One table has a record the other doesn't so it had to have been deleted. I need to find out WHEN it was deleted. How do I create a log of record deletes? Jeff McKeon IT Manager Telaurus Communications LLC [EMAIL PROTECTED] (973) 889-8990 ex 209 ***The information contained in this communication is confidential. It is intended only for the sole use of the recipient named above and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication, or any of its contents or attachments, is expressly prohibited. If you have received this communication in error, please re-send it to the sender and delete the original message, and any copy of it, from your computer system. Thank You.*** -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 1:59 PM To: Jeff McKeon; [EMAIL PROTECTED] Subject: Re: Tracking a delete At 12:54 -0400 8/8/03, Jeff McKeon wrote: How can I see if a record was deleted from a database? Jeff You can attempt to SELECT it, and if you get no result, it's not there. But that doesn't necesarily mean that it was once there and has now been deleted. If you want to determine that, you'll need to create a log of record deletions. -- 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: Tracking a delete
Ok, I know it WAS there because we have two similar tables that should contain sister records. One table has a record the other doesn't so it had to have been deleted. I need to find out WHEN it was deleted. How do I create a log of record deletes? If you keep the update log or the binlog you can look through it. Ok, replication is on so that means I have a bin log, now how do look through it? This is mysql 3.23 on linux. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tracking a delete
How can I see if a record was deleted from a database? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple table joins in a select
Ver. 3.23 How do I write a select query to join more than two tables? Table A relates to table B and table B relates to Table C. I need to return fields from Table A and C that are related.. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Help
I have a table that records connections from customers to our server. When there is a software problem with our customer's that have older versions of our software, it will dial our server constantly over and over again. I want to be able to detect this by having a query that does something like this.. (I know this where clause won't really work) Select count(cust.id) from cust where cust.time UNIX_TIMESTAMP(DATE_SUB(NOW(),interval 1 HOUR)) count(cust.id) 5 Group by cust.id; Is there a way to do this with one query? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help
Thanks, that did it! Jeff -Original Message- From: Kevin Fries [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 3:05 PM To: Jeff McKeon; [EMAIL PROTECTED] Subject: RE: Query Help So you want to group by the customer, but only show those gorupings with a count 5. That means you want to apply your restriction after the GROUP BY. Thus, the clause goes into the HAVING area. Try: Select count(*), cust.id from cust where cust.time UNIX_TIMESTAMP(DATE_SUB(NOW(),interval 1 HOUR)) Group by cust.id HAVING count(cust.id) 5; -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 11:44 AM To: [EMAIL PROTECTED] Subject: Query Help I have a table that records connections from customers to our server. When there is a software problem with our customer's that have older versions of our software, it will dial our server constantly over and over again. I want to be able to detect this by having a query that does something like this.. (I know this where clause won't really work) Select count(cust.id) from cust where cust.time UNIX_TIMESTAMP(DATE_SUB(NOW(),interval 1 HOUR)) count(cust.id) 5 Group by cust.id; Is there a way to do this with one query? Thanks, Jeff -- 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: Deleting duplicating records
How bout Delete from tablename where email like [EMAIL PROTECTED] ID 1 Jeff -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 10:33 AM To: [EMAIL PROTECTED] Subject: Deleting duplicating records Greetings I manage a website wherein i keep track of the people email who have downloaded my software and the version number. the structure is like - id int auto_increment primary key, email char, version now the same person can download different version therfore my table has data like this - 1,[EMAIL PROTECTED],1.0 2,[EMAIL PROTECTED],2.0 3,[EMAIL PROTECTED],3.0 Now I want to delete all the records wherein all rows with duplicate email addresses are deleted so that i have data like 1,[EMAIL PROTECTED],1.0 ... What should be the query? Thanks in advance. Karam __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- 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]
FW: rights to create table, select, then drop table..
Nobody has any ideas on this one? Jeff -Original Message- From: Jeff McKeon Sent: Friday, July 25, 2003 3:23 PM To: [EMAIL PROTECTED] Subject: rights to create table, select, then drop table.. I have a need to get data from the db that requires me to 1) do a select and create a new table with the results 2) run a query against that new table 3) drop the new table I have a script on my server that does this using the root account that has all on *.* for the db. It works fine. I now want to get these results on a web page. I want to create a new db user for my .php web page to use to connect to the db that only has the needed priviledges on that specific db to get the job done. what priviledges do I need to give that user? currently I have the following but the user can't even log into the db from the command line.. mysql show grants for user; +--- -+ | Grants for [EMAIL PROTECTED] | +--- -+ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '6fe4c0ab2cf30ae3' | | GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `db1`.* TO 'user'@'%' | +--- -+ 2 rows in set (0.00 sec) when I do a show grants for user, what should I see to allow what I want? Thanks, Jeff McKeon -- 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]
Aborted clients
Ver 3.23 on RH Linux. We came in this morning and were greeted by our DB server rejecting connections to the db from our application. There seems to be a high number of Aborted_clients. How can we tell what clients/connections are causing this? mysql show status; +--++ | Variable_name| Value | +--++ | Aborted_clients | 149| | Aborted_connects | 122| | Bytes_received | 422801700 | | Bytes_sent | 3604618252 | | Connections | 7375 | | Created_tmp_disk_tables | 885| | Created_tmp_tables | 20331 | | Created_tmp_files| 509| | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 2 | | Handler_delete | 5252 | | Handler_read_first | 35028 | | Handler_read_key | 95425837 | | Handler_read_next| 2978256304 | | Handler_read_prev| 0 | | Handler_read_rnd | 7727972| | Handler_read_rnd_next| 3143081074 | | Handler_update | 45487 | | Handler_write| 2010283| | Key_blocks_used | 7793 | | Key_read_requests| 585802473 | | Key_reads| 18261 | | Key_write_requests | 3740894| | Key_writes | 254091 | | Max_used_connections | 100| | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 64 | | Open_files | 109| | Open_streams | 0 | | Opened_tables| 252| | Questions| 5171955| | Select_full_join | 881| | Select_full_range_join | 0 | | Select_range | 22 | | Select_range_check | 0 | | Select_scan | 280668 | | Slave_running| ON | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 0 | | Slow_queries | 138| | Sort_merge_passes| 254| | Sort_range | 474| | Sort_rows| 40227394 | | Sort_scan| 22550 | | Table_locks_immediate| 5695456| | Table_locks_waited | 8278 | | Threads_cached | 0 | | Threads_created | 7373 | | Threads_connected| 97 | | Threads_running | 2 | | Uptime | 317854 | +--++ Jeff McKeon IT Manager Telaurus Communications LLC [EMAIL PROTECTED] (973) 889-8990 ex 209 ***The information contained in this communication is confidential. It is intended only for the sole use of the recipient named above and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication, or any of its contents or attachments, is expressly prohibited. If you have received this communication in error, please re-send it to the sender and delete the original message, and any copy of it, from your computer system. Thank You.*** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rights to create table, select, then drop table..
I have a need to get data from the db that requires me to 1) do a select and create a new table with the results 2) run a query against that new table 3) drop the new table I have a script on my server that does this using the root account that has all on *.* for the db. It works fine. I now want to get these results on a web page. I want to create a new db user for my .php web page to use to connect to the db that only has the needed priviledges on that specific db to get the job done. what priviledges do I need to give that user? currently I have the following but the user can't even log into the db from the command line.. mysql show grants for user; +--- -+ | Grants for [EMAIL PROTECTED] | +--- -+ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '6fe4c0ab2cf30ae3' | | GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `db1`.* TO 'user'@'%' | +--- -+ 2 rows in set (0.00 sec) when I do a show grants for user, what should I see to allow what I want? Thanks, Jeff McKeon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Aborted clients
*** Variable_name: max_connect_errors Value: 10 *** 41. row *** Variable_name: max_delayed_threads Value: 20 *** 42. row *** Variable_name: max_heap_table_size Value: 16777216 *** 43. row *** Variable_name: max_join_size Value: 4294967295 *** 44. row *** Variable_name: max_sort_length Value: 1024 *** 45. row *** Variable_name: max_user_connections Value: 0 *** 46. row *** Variable_name: max_tmp_tables Value: 32 *** 47. row *** Variable_name: max_write_lock_count Value: 4294967295 *** 48. row *** Variable_name: myisam_recover_options Value: 0 *** 49. row *** Variable_name: myisam_max_extra_sort_file_size Value: 256 *** 50. row *** Variable_name: myisam_max_sort_file_size Value: 2047 *** 51. row *** Variable_name: myisam_sort_buffer_size Value: 8388608 *** 52. row *** Variable_name: net_buffer_length Value: 16384 *** 53. row *** Variable_name: net_read_timeout Value: 30 *** 54. row *** Variable_name: net_retry_count Value: 10 *** 55. row *** Variable_name: net_write_timeout Value: 60 *** 56. row *** Variable_name: open_files_limit Value: 0 *** 57. row *** Variable_name: pid_file Value: /home/data/mysql/data/db01tc0927.pid *** 58. row *** Variable_name: port Value: 3306 *** 59. row *** Variable_name: protocol_version Value: 10 *** 60. row *** Variable_name: record_buffer Value: 131072 *** 61. row *** Variable_name: record_rnd_buffer Value: 131072 *** 62. row *** Variable_name: query_buffer_size Value: 0 *** 63. row *** Variable_name: safe_show_database Value: OFF *** 64. row *** Variable_name: server_id Value: 1 *** 65. row *** Variable_name: slave_net_timeout Value: 3600 *** 66. row *** Variable_name: skip_locking Value: ON *** 67. row *** Variable_name: skip_networking Value: OFF *** 68. row *** Variable_name: skip_show_database Value: OFF *** 69. row *** Variable_name: slow_launch_time Value: 2 *** 70. row *** Variable_name: socket Value: /var/local/mysql/mysql.sock *** 71. row *** Variable_name: sort_buffer Value: 2097144 *** 72. row *** Variable_name: sql_mode Value: 0 *** 73. row *** Variable_name: table_cache Value: 64 *** 74. row *** Variable_name: table_type Value: MYISAM *** 75. row *** Variable_name: thread_cache_size Value: 0 *** 76. row *** Variable_name: thread_stack Value: 65536 *** 77. row *** Variable_name: transaction_isolation Value: READ-COMMITTED *** 78. row *** Variable_name: timezone Value: UTC *** 79. row *** Variable_name: tmp_table_size Value: 33554432 *** 80. row *** Variable_name: tmpdir Value: /tmp/ *** 81. row *** Variable_name: version Value: 3.23.41-log *** 82. row *** Variable_name: wait_timeout Value: 28800 Jeff McKeon IT Manager Telaurus Communications LLC [EMAIL PROTECTED
