Something strange here...
Hi all, Can someone explain to me why this is happening please: mysql> SELECT * FROM CustomerDetails WHERE Username=âblahâ\G *** 1. row *** EntryID: F83D4109-DCA8-426F-98B1-00B4AF117ACB Username: blah AccountVolume: 0 1 row in set (0.00 sec) mysql> UPDATE CustomerDetails SET AccountVolume=GREATEST(CAST(AccountVolume-2865 AS SIGNED), CAST(0 AS SIGNED)) WHERE Username='blah'; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`CustomerDetails`.`AccountVolume` - 2865)' Using GREATEST, shouldnât it be irrelevant whether AccountVolume-INT is signed or unsigned? How would I go about doing this? I have played quite a bit with CAST here, but I am not having much luck. Thnx, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: need help from the list admin
On Fri, Mar 18, 2016 at 3:43 PM, Lentes, Bernd < bernd.len...@helmholtz-muenchen.de> wrote: > i need your help. I'm trying to write an e-Mail to the list for already > one week. I always get it back because it's classified as spam. > Ditto. I've pretty much given up on this list...
Re: need help from the list admin
Ok :-) On Fri, Mar 18, 2016 at 4:34 PM, Reindl Harald <h.rei...@thelounge.net> wrote: > > > Am 18.03.2016 um 15:25 schrieb Lentes, Bernd: > >> >> - Am 18. Mrz 2016 um 14:52 schrieb Johan De Meersman >> vegiv...@tuxera.be: >> >> and yet, both of those messages made it through :-p >>> >>> Stick your domain in http://mxtoolbox.com to see if there's any >>> problems that >>> might be worth solving. If the mailserver classifies you as spam, that's >>> usually caused by something on your side :-) >>> >>> - Original Message - >>> >>>> From: "Chris Knipe" <sav...@savage.za.org> >>>> To: "Lentes, Bernd" <bernd.len...@helmholtz-muenchen.de> >>>> Cc: "MySql" <mysql@lists.mysql.com> >>>> Sent: Friday, 18 March, 2016 14:46:26 >>>> Subject: Re: need help from the list admin >>>> >>> >>> Ditto. I've pretty much given up on this list... >>>> >>> >>> >> Neither our outgoing mailserver ( >> http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#) >> nor our domain >> ( >> http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#) >> is listed there. >> I checked that before i wrote the e-Mail. If you could help me to point >> out what's wrong on our side i could ask our mail admin to correct it. >> Currently i don't have any idea >> > > as i already told you offlist > senderscore.com LISTED 127.0.4.67 > > this *is* a bad reputation > > and more worse: you did not manage to get your server on any DNSWL > > [harry@srv-rhsoft:~]$ nslookup 20.103.107.146.score.senderscore.com. > Server: 127.0.0.1 > Address:127.0.0.1#53 > Non-authoritative answer: > Name: 20.103.107.146.score.senderscore.com > Address: 127.0.4.67 > _ > > compare with 91.118.73.15 (our outgoing server) which has there the best > possible reputation (treated as whitelist) and is at the same time on the " > list.dnswl.org" and "hostkarma.junkemailfilter" while one of both would > possibly neutralize the BL listing in a scoring system > > [harry@srv-rhsoft:~]$ nslookup 15.73.118.91.score.senderscore.com. > Server: 127.0.0.1 > Address:127.0.0.1#53 > Non-authoritative answer: > Name: 15.73.118.91.score.senderscore.com > Address: 127.0.4.100 > > -- Regards, Chris Knipe
Re: need help from the list admin
Blah blah blah... Delivery to the following recipient failed permanently: mysql@lists.mysql.com Technical details of permanent failure: Your message was rejected by the server for the recipient domain lists.mysql.com by lists-mx.mysql.com. [137.254.60.71]. The error that the other server returned was: 550 Currently Sending Spam See: http://www.sorbs.net/lookup.shtml?5.200.22.158 Show me one site, where that IP is, or WAS ever blacklisted? -- Chris. On Fri, Mar 18, 2016 at 3:52 PM, Johan De Meersman <vegiv...@tuxera.be> wrote: > > and yet, both of those messages made it through :-p > > Stick your domain in http://mxtoolbox.com to see if there's any problems > that might be worth solving. If the mailserver classifies you as spam, > that's usually caused by something on your side :-) > > - Original Message - > > From: "Chris Knipe" <sav...@savage.za.org> > > To: "Lentes, Bernd" <bernd.len...@helmholtz-muenchen.de> > > Cc: "MySql" <mysql@lists.mysql.com> > > Sent: Friday, 18 March, 2016 14:46:26 > > Subject: Re: need help from the list admin > > > Ditto. I've pretty much given up on this list... > > -- > Unhappiness is discouraged and will be corrected with kitten pictures. > -- Regards, Chris Knipe
Indexes strangeness
Hi All, Can someone please fill me in as what I am seeing here... I have two identical tables, with identical indexes, having different records. Both tables have +- 15m records in it... mysql> EXPLAIN SELECT ArticleID, DateObtained, DateAccessed, TimesAccessed FROM IDXa ORDER BY DateAccessed LIMIT 10; ++-+--+---+---+-+-+--++---+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+---+---+-+-+--++---+ | 1 | SIMPLE | IDXa | index | NULL | idxDateAccessed | 5 | NULL | 10 | NULL | ++-+--+---+---+-+-+--++---+ 1 row in set (0,00 sec) mysql> EXPLAIN SELECT ArticleID, DateObtained, DateAccessed, TimesAccessed FROM IDXb ORDER BY DateAccessed LIMIT 10; ++-+--+--+---+--+-+--+--++ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+---+--+-+--+--++ | 1 | SIMPLE | IDXb | ALL | NULL | NULL | NULL| NULL | 15004858 | Using filesort | ++-+--+--+---+--+-+--+--++ 1 row in set (0,00 sec) Tables: mysql> SHOW CREATE TABLE IDXa\G *** 1. row *** Table: IDXa Create Table: CREATE TABLE `IDXa` ( `ArticleID` varchar(32) NOT NULL, `DateObtained` datetime NOT NULL, `DateAccessed` datetime NOT NULL, `TimesAccessed` int(5) unsigned NOT NULL, PRIMARY KEY (`ArticleID`), KEY `idxDateAccessed` (`DateAccessed`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0,00 sec) mysql> SHOW INDEXES FROM IDXa; +--++-+--+--+---+-+--++--++-+---+ | Table| Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--++-+--+--+---+-+--++--++-+---+ | IDXa | 0 | PRIMARY |1 | ArticleID| A |14086444 | NULL | NULL | | BTREE | | | | IDXa | 1 | idxDateAccessed |1 | DateAccessed | A | 1408644 | NULL | NULL | | BTREE | | | +--++-+--+--+---+-+--++--++-+---+ 2 rows in set (0,00 sec) mysql> SHOW CREATE TABLE IDXb\G *** 1. row *** Table: IDXb Create Table: CREATE TABLE `IDXb` ( `ArticleID` varchar(32) NOT NULL, `DateObtained` datetime NOT NULL, `DateAccessed` datetime NOT NULL, `TimesAccessed` int(5) unsigned NOT NULL, PRIMARY KEY (`ArticleID`), KEY `idxDateAccessed` (`DateAccessed`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0,00 sec) mysql> SHOW INDEXES FROM IDXb; +--++-+--+--+---+-+--++--++-+---+ | Table| Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--++-+--+--+---+-+--++--++-+---+ | IDXb | 0 | PRIMARY |1 | ArticleID| A |15007345 | NULL | NULL | | BTREE | | | | IDXb | 1 | idxDateAccessed |1 | DateAccessed | A | 1250612 | NULL | NULL | | BTREE | | | +--++-+--+--+---+-+--++--++-+---+ 2 rows in set (0,00 sec) Thnx. -- Regards, Chris Knipe
Query Statistics...
Hi All, Perhaps a bit of a trivial question, but in terms of query statistics (i.e. how many SELECT / INSERT / DELETE / etc. queries has been ran against the server)... When you take an INSERT ... ON DUPLICATE KEY UPDATE ... Under the two conditions (i.e. either INSERT, or UPDATE if the record already exist), how is this query logged in the statistics? When the ON DUPLICATE KEY UPDATE runs (i.e. it's updating a record), is it still logged as a INSERT query, or is it logged as a UPDATE query? Thnx. -- Regards, Chris Knipe
ORDER BY not using index?
Hi, Can someone perhaps assist with the below... I'm not sure at all why my index aren't being used for the ORDER BY. Currently some 443K records in the table, but this will grow to a good few million. I simply cannot, afford a filesort. mysql SELECT COUNT(*) FROM myTable; +--+ | COUNT(*) | +--+ | 443808 | +--+ 1 row in set (0.00 sec) mysql EXPLAIN SELECT * FROM myTable ORDER BY DateAccessed; ++-+--+--+---+--+-+--+++ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+---+--+-+--+++ | 1 | SIMPLE | myTable | ALL | NULL | NULL | NULL| NULL | 443808 | Using filesort | ++-+--+--+---+--+-+--+++ 1 row in set (0.00 sec) mysql EXPLAIN SELECT * FROM myTable FORCE INDEX (idx_DateAccessed) ORDER BY DateAccessed; ++-+--+---+---+--+-+--++---+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+---+---+--+-+--++---+ | 1 | SIMPLE | myTable | index | NULL | idx_DateAccessed | 4 | NULL | 443808 | | ++-+--+---+---+--+-+--++---+ 1 row in set (0.00 sec) mysql SHOW CREATE TABLE myTable; +--+-+ | Table| Create Table | +--+-+ | myTable | CREATE TABLE `myTable` ( `ArticleID` char(32) NOT NULL, `DateObtained` int(10) unsigned NOT NULL, `DateAccessed` int(10) unsigned NOT NULL, `TimesAccessed` int(10) unsigned NOT NULL, PRIMARY KEY (`ArticleID`), KEY `idx_DateAccessed` (`DateAccessed`) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1 | +--+-+ 1 row in set (0.00 sec) mysql SHOW INDEX FROM myTable; +--++--+--+--+---+-+--++--++-+---+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--++--+--+--+---+-+--++--++-+---+ | myTable | 0 | PRIMARY |1 | ArticleID| A | 443808 | NULL | NULL | | BTREE | | | | myTable | 1 | idx_DateAccessed |1 | DateAccessed | A | 147936 | NULL | NULL | | BTREE | | | +--++--+--+--+---+-+--++--++-+---+ 2 rows in set (0.00 sec) -- Regards, Chris Knipe
Re: mysql strangeness...
On Mon, Dec 8, 2014 at 3:02 PM, Wagner Bianchi wagnerbianch...@gmail.com wrote: Hello Chris, Can pls you share the below command output... SHOW STATUS LIKE 'Threads%'; SELECT @@thread_cache_size, @@net_buffer_length, @@max_allowed_packet; mysql SHOW STATUS LIKE 'Threads%'; +---+---+ | Variable_name | Value | +---+---+ | Threads_cached| 422 | | Threads_connected | 92| | Threads_created | 514 | | Threads_running | 1 | +---+---+ 4 rows in set (0.43 sec) mysql SELECT @@thread_cache_size, @@net_buffer_length, @@max_allowed_packet; +-+-+--+ | @@thread_cache_size | @@net_buffer_length | @@max_allowed_packet | +-+-+--+ |1024 | 16384 | 16777216 | +-+-+--+ 1 row in set (0.00 sec) mysql The server is very not busy at all right now however. The issues seems to start happening once we've reach the 250 to 300 concurrent connection mark. I'm tempted to just through another 64GB of memory to the box and up the max connections, but 1st price of course would be to identify and resolve the problem, rather than just throwing hardware at the problem :-) What about the limits.conf config on operating system level? They have been dealt with. mySQL has 4096 file descriptors available. Through all of this, not one single error is logged to the errorlog either. limits.conf: mysql soft nofile 4096 mysql hard nofile 4096 I'm personally actually leading more towards that this is a OS issue rather than a mysql issue, but I have no idea to where to even start looking to debug this :-( -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: mysql strangeness...
FYI - just an example... mysql SELECT VERSION(); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:203720459 Current database: NNTP +-+ | VERSION() | +-+ | 5.5.38-0ubuntu0.12.04.1-log | +-+ 1 row in set (33.94 sec) mysql SELECT VERSION(); +-+ | VERSION() | +-+ | 5.5.38-0ubuntu0.12.04.1-log | +-+ 1 row in set (0.00 sec) This is from the mysql client running on the same host as the mysql server, connected to localhost via TCP. Current connections to the DB was at about 200 out of 500 -- Chris. -Original Message- From: Chris Knipe [mailto:sav...@savage.za.org] Sent: Sunday, December 07, 2014 11:34 PM To: 'mysql@lists.mysql.com' Subject: mysql strangeness... Hi, I have a not so busy MySQL server (+- 150 Selects/sec, 180 Deletes/Sec, 320 Updates/Sec, 90 Inserts/Sec and 200 Replace/Sec), max 512 concurrent connections. The server is running on a Dell R720 with 64GB Ram, Xeon E5-2620. Data is on a 4 x 3TB (RAID10) SATA3 array, and binlogs on a 4 x 600GB (RAID10) SSD array. MySQL 5.5.38 Up to a while ago (few days / week), everything was running absolutely fine. Lately however, I have noticed more and more times that I am hitting my max connection limits. Standard tools like mysql-tuner (which took more than an hour to run because of slow connections to MySQL), tuning-primer, innotop, etc. all shows that the configured variables are within normal parameters, and MySQL is also not under *any* IO load what so ever. I'd say 99% of all tables are InnoDB, and even the one single Memory table that I have, seems to be running slow queries :-( I'm also seeing a lot of connection reset by peer network related errors. What I am seeing, is that even when there are only like 300 or so connections established to the server, even the mysql (command utility) running on the localhost, takes MINUTES to establish a connection to the mysql server - however, once the connection IS established, queries execute absolutely fine and within seconds. It's just the initial establishment of the connection to mysql which is taking forever. The process list shows 99% of all the active connections in a READING FROM NET state (unauthenticated users). Skip-name-resolve and all those things are disabled, and tcpdumps also confirmed that mysql is in fact NOT doing DNS lookups. The server IMHO is under no significant load at all (dedicated mysql system), and there's only +- 5Mbps of traffic on the 1Gbps interface in the server. All in all, graphs indicate 750/800 QPS with about 30% reads, and 70% writes. Can anyone perhaps suggest some things to look into here? I'm pretty sure that the problem is with the initial establishment of the TCP connection to mysql, rather than it being a mysql issue related to queries and what not. Telneting to the mysql socket, I do get the initial greeting from the server virtually instantaneous like it should be. So the only thing that I can think of is that something is whacked in terms of authenticating users? Box is firewalled, only authorised hosts are permitted to connect, all users are authenticating by password and IP (%) -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Avoiding table scans...
Hi All, I have a couple of *huge* tables, they're still busy populating, but once done I suspect it will hold well over 3 billion records (and that's more than likely the start of the problem). The mysql server is a highly optimized, powerful server with some 128GB ram, data + binlogs on RAID10 SSDs and is performing incredibly well with some 3K inserts/second whilst still doing random selects/updates in between. How can I go about not having the following: mysql SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber '2118806'; +---+--- + | ArtNumber | MessageID | +---+--- + | 2118807 | part26of79.GfYzwhqz$ORUpNi3tjsW@camelsystem-powerpost.local | +---+--- + 1 row in set (22.78 sec) mysql EXPLAIN SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber '2118806'; ++-+--+---+- --+-+-+--+--+-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+---+- --+-+-+--+--+-+ | 1 | SIMPLE | 78168ea0a9b3b513a1f2d39b559b406e | range | PRIMARY | PRIMARY | 8 | NULL | 31515172 | Using where | ++-+--+---+- --+-+-+--+--+-+ 1 row in set (0.19 sec) mysql explain SELECT MAX(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber '2118809'; ++-+--+---+- --+-+-+--+--+-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+---+- --+-+-+--+--+-+ | 1 | SIMPLE | 78168ea0a9b3b513a1f2d39b559b406e | range | PRIMARY | PRIMARY | 8 | NULL |3 | Using where | ++-+--+---+- --+-+-+--+--+-+ 1 row in set (0.17 sec) mysql SHOW CREATE TABLE 78168ea0a9b3b513a1f2d39b559b406e; +--+ ---+ | Table| Create Table | +--+ ---+ | 78168ea0a9b3b513a1f2d39b559b406e | CREATE TABLE `78168ea0a9b3b513a1f2d39b559b406e` ( `ArtNumber` bigint(20) unsigned NOT NULL, `MessageID` varchar(255) NOT NULL, `Date` int(10) unsigned NOT NULL, `Bytes` bigint(20) unsigned NOT NULL, `Lines` bigint(20) unsigned NOT NULL, `From` varchar(255) NOT NULL, `Subject` tinytext NOT NULL, PRIMARY KEY (`ArtNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='' | +--+ ---+ 1 row in set (0.70 sec) I realise that this is an enormous amount of data - especially once fully populated and we reach the over 3 billion records in the table. Is the only course of action here to re-look at how the data is stored? I suppose it can't get any better than querying directly against the PRIMARY key, can it? Many thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: Avoiding table scans...
On Thu, Jul 24, 2014 at 11:47 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Chris Knipe sav...@savage.za.org To: mysql@lists.mysql.com Sent: Thursday, 24 July, 2014 11:17:50 AM Subject: Avoiding table scans... mysql SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber '2118806'; You're putting quotes around ArtNumber in your where clause, where it really is a bigint. Thus, you're forcing implicity conversion in the parser, instead of simply doing an index lookup. mysql SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber 2118806; +---+---+ | ArtNumber | MessageID | +---+---+ | 2118807 | part26of79.GfYzwhqz$ORUpNi3tjsW@camelsystem-powerpost.local | +---+---+ 1 row in set (19.37 sec) mysql SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber '2118806'; +---+---+ | ArtNumber | MessageID | +---+---+ | 2118807 | part26of79.GfYzwhqz$ORUpNi3tjsW@camelsystem-powerpost.local | +---+---+ 1 row in set (19.43 sec) mysql EXPLAIN SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber 2118806; ++-+--+---+---+-+-+--+--+-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+---+---+-+-+--+--+-+ | 1 | SIMPLE | 78168ea0a9b3b513a1f2d39b559b406e | range | PRIMARY | PRIMARY | 8 | NULL | 31868953 | Using where | ++-+--+---+---+-+-+--+--+-+ 1 row in set (0.18 sec) Partitioning should help from what I am reading currently... Just not sure about a few things just yet. -- Regards, Chris Knipe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Avoiding table scans...
Try this SELECT ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber=(SELECT MIN(ArtNumber) FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber2118806) +---+---+ | ArtNumber | MessageID | +---+---+ | 2118807 | part26of79.GfYzwhqz$ORUpNi3tjsW@camelsystem-powerpost.local | +---+---+ 1 row in set (0.81 sec) If this wasn't a public mailing list I would have said something very strange now containing the word love! Can you perhaps just elaborate a bit as to *why* this is working so much better? The reason I ask is that I've only given one query as a example. There's quite a few slightly different queries like this, and I would just like to understand the mythology behind in order to be able to change the other queries as well... MANY thanks for the assistance :-) -- Chris.
Re: hypothetical question about data storage
Hi All, Thanks for the responces, and I do concur. I was taking a stab in the dark so to speak. We are working with our hosting providers currently and will be introducing a multitude of small iSCSI SANs to split the storage structure over a multitude of disks... This is something that needs to be addressed from a systems perspective rather than an architectural one. SSD (or Fusion and the like) are unfortunately still way to expensive for the capacity that we require (good couple of TBs) - so mechanical disks it would need to be. However, with the use of SANs as we hope, we should be able to go up from 4 to over 64 spindles whilst still being able to share the storage and have redundancy. Many thanks for the inputs and feedbacks... -- C On Fri, Jul 26, 2013 at 9:23 AM, Johan De Meersman vegiv...@tuxera.be wrote: Hey Chris, I'm afraid that this is not what databases are for, and the first thing you'll likely run into is amount of concurrent connections. This is typically something you should really tackle from a systems perspective. Seek times are dramatically improved on SSD or similar storage - think FusionIO cards, but there's also a couple of vendors (Violin comes to mind) who provide full-blown SSD SANs. If you prefer staying with spinning disks, you could still improve the seeks by focusing on the inner cylinders and potentially by using variable sector formatting. Again, there's SANs that do this for you. Another minor trick is to turn off access timestamp updates when you mount the filesystem (noatime). Also benchmark different filesystems, there's major differences between them. I've heard XFS being recommended, but I've never needed to benchmark for seek times myself. We're using IBM's commercial GPFS here, which is good with enormous amounts of huge files (media farm here), not sure how it'd fare with smaller files. Hope that helps, Johan - Original Message - From: Chris Knipe sav...@savage.za.org To: mysql@lists.mysql.com Sent: Thursday, 25 July, 2013 11:53:53 PM Subject: hypothetical question about data storage Hi all, We run an VERY io intensive file application service. Currently, our problem is that our disk spindles are being completely killed due to insufficient SEEK time on the hard drives (NOT physical read/write speeds). We have an directory structure where the files are stored based on the MD5 checksum of the file name, i.e. /0/00/000/44533779fce5cf3497f87de1d060 The majority of these files, are between 256K and 800K with the ODD exception (say less than 15%) being more than 1M but no more than 5M in size. The content of the files are pure text (MIME Encoded). We believe that storing these files into an InnoDB table, may actually give us better performance: - There is one large file that is being read/written, instead of BILLIONS of small files - We can split the structure so that each directory (4096 in total) sit's on their own database - We can move the databases as load increases, which means that we can potentially run 2 physical database servers, each with 2048 databases each) - It's easy to move / migrate the data due to mysql and replication - same can be said for redundancy of the data We are more than likely looking at BLOB columns of course, and we need to read/write from the DB in excess of 100mbit/s Would the experts consider something like this as being feasible? Is it worth it to go down this avenue, or are we just going to run into different problems? If we are facing different problems, what can we possibly expect to go wrong here? Many thanks, and I look forward to any input. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- Regards, Chris Knipe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: hypothetical question about data storage
that the next level of the BTree won't be fully cacheable. Now 300ms/30ms. I think it is likely that the small number of disk hits for InnoDB is better than the many disk hits for traversing a directory tree (with large directories) in the filesystem. I vote for InnoDB over the directory tree. Yes, you will have seeks. No, adding more RAM won't help much. Here's an argument: Suppose your data is 20 times as big as the buffer pool and you are doing random fetches (MD5, etc). Then 1/20 of fetches are cached; 95% cache miss. Estimated time: 0.95 * 100ms = 95ms. Now you double your RAM. 1/10 cached - 90% cache miss - 90ms average - Not much improvement over 95. -Original Message- From: ckn...@savage.za.org [mailto:ckn...@savage.za.org] On Behalf Of Chris Knipe Sent: Friday, July 26, 2013 12:30 AM To: Johan De Meersman Cc: mysql Subject: Re: hypothetical question about data storage Hi All, Thanks for the responces, and I do concur. I was taking a stab in the dark so to speak. We are working with our hosting providers currently and will be introducing a multitude of small iSCSI SANs to split the storage structure over a multitude of disks... This is something that needs to be addressed from a systems perspective rather than an architectural one. SSD (or Fusion and the like) are unfortunately still way to expensive for the capacity that we require (good couple of TBs) - so mechanical disks it would need to be. However, with the use of SANs as we hope, we should be able to go up from 4 to over 64 spindles whilst still being able to share the storage and have redundancy. Many thanks for the inputs and feedbacks... -- C On Fri, Jul 26, 2013 at 9:23 AM, Johan De Meersman vegiv...@tuxera.be wrote: Hey Chris, I'm afraid that this is not what databases are for, and the first thing you'll likely run into is amount of concurrent connections. This is typically something you should really tackle from a systems perspective. Seek times are dramatically improved on SSD or similar storage - think FusionIO cards, but there's also a couple of vendors (Violin comes to mind) who provide full-blown SSD SANs. If you prefer staying with spinning disks, you could still improve the seeks by focusing on the inner cylinders and potentially by using variable sector formatting. Again, there's SANs that do this for you. Another minor trick is to turn off access timestamp updates when you mount the filesystem (noatime). Also benchmark different filesystems, there's major differences between them. I've heard XFS being recommended, but I've never needed to benchmark for seek times myself. We're using IBM's commercial GPFS here, which is good with enormous amounts of huge files (media farm here), not sure how it'd fare with smaller files. Hope that helps, Johan - Original Message - From: Chris Knipe sav...@savage.za.org To: mysql@lists.mysql.com Sent: Thursday, 25 July, 2013 11:53:53 PM Subject: hypothetical question about data storage Hi all, We run an VERY io intensive file application service. Currently, our problem is that our disk spindles are being completely killed due to insufficient SEEK time on the hard drives (NOT physical read/write speeds). We have an directory structure where the files are stored based on the MD5 checksum of the file name, i.e. /0/00/000/44533779fce5cf3497f87de1d060 The majority of these files, are between 256K and 800K with the ODD exception (say less than 15%) being more than 1M but no more than 5M in size. The content of the files are pure text (MIME Encoded). We believe that storing these files into an InnoDB table, may actually give us better performance: - There is one large file that is being read/written, instead of BILLIONS of small files - We can split the structure so that each directory (4096 in total) sit's on their own database - We can move the databases as load increases, which means that we can potentially run 2 physical database servers, each with 2048 databases each) - It's easy to move / migrate the data due to mysql and replication - same can be said for redundancy of the data We are more than likely looking at BLOB columns of course, and we need to read/write from the DB in excess of 100mbit/s Would the experts consider something like this as being feasible? Is it worth it to go down this avenue, or are we just going to run into different problems? If we are facing different problems, what can we possibly expect to go wrong here? Many thanks, and I look forward to any input. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- Regards, Chris Knipe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Sent from Kaiten Mail. Please excuse my brevity. -- Regards, Chris Knipe -- MySQL
hypothetical question about data storage
Hi all, We run an VERY io intensive file application service. Currently, our problem is that our disk spindles are being completely killed due to insufficient SEEK time on the hard drives (NOT physical read/write speeds). We have an directory structure where the files are stored based on the MD5 checksum of the file name, i.e. /0/00/000/44533779fce5cf3497f87de1d060 The majority of these files, are between 256K and 800K with the ODD exception (say less than 15%) being more than 1M but no more than 5M in size. The content of the files are pure text (MIME Encoded). We believe that storing these files into an InnoDB table, may actually give us better performance: - There is one large file that is being read/written, instead of BILLIONS of small files - We can split the structure so that each directory (4096 in total) sit's on their own database - We can move the databases as load increases, which means that we can potentially run 2 physical database servers, each with 2048 databases each) - It's easy to move / migrate the data due to mysql and replication - same can be said for redundancy of the data We are more than likely looking at BLOB columns of course, and we need to read/write from the DB in excess of 100mbit/s Would the experts consider something like this as being feasible? Is it worth it to go down this avenue, or are we just going to run into different problems? If we are facing different problems, what can we possibly expect to go wrong here? Many thanks, and I look forward to any input. -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Possible causes of table crashing
No idea on the cause, but sitting with the exact same issue on 1 specific MyISAM table on mysql-5.1.50 Since the table was created it never had any change in data structure, the table is almost exclusively INSERTs and it is quite big in my instance however. Table crashes on average every 2 to 3 days for no apparent reason what so ever, and everytime it repairs successfully... -- Chris. On Tue, Nov 30, 2010 at 1:20 PM, Mark Goodge m...@good-stuff.co.uk wrote: Hi, I have a very strange problem whereby one particular table in our database is repeatedly (on average, every couple of days) generating errors stating that the table is crashed and needs to be repaired. Running a repair fixes it. What makes it strange (and something that I've never encountered before) is the following: 1. There is one particular table which it happens to a lot, and a few other tables where it happens occasionally. Everything else is fine. 2. None of the tables where it happens are among the most heavily used or commonly updated. 3. There is no shortage of disk space. 4. The disk system reports no errors. 5. We have multiple databases with an identical structure but different content (we are an online retailer and each database is a separate storefront), but the problem occurs in all the databases - but the same tables in each database. It doesn't always happen to each database at the same time - it seems to be random. 6. (This is the really strange one) We have two separate servers with identical copies of the databases, one for production use and one for development use, and it happens independently on both of them - but still the same tables (and, specifically, the same table that it happens to more often than any other). 7. And, of course, the obvious statement: we haven't changed the structure of this database recently (it hasn't changed for months, if not years). We are running MySQL 5.0.77 on Centos. All the databases use MyISAM exclusively. Given the above, can anyone suggest any possible causes? Thanks Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sav...@savage.za.org -- Regards, Chris Knipe
Where to index - over 15m records and growing
') | NO | |
NULL| |
| PlannedDepTime|
time | NO | |
NULL| |
| PlannedActDepTime |
time | NO | |
NULL| |
| PlannedEnroute|
time | NO | |
NULL| |
| PlannedFuel |
time | NO | |
NULL| |
| PlannedAltAirport |
varchar(5) | NO | |
NULL| |
| PlannedRemarks|
tinytext | NO | |
NULL| |
| PlannedRoute |
text | NO | |
NULL| |
| TimeConnected |
char(14) | NO | |
NULL| |
| ClientSoftwareName|
varchar(10) | NO | |
NULL| |
| ClientSoftwareVersion |
varchar(10) | NO | |
NULL| |
| PlannedAltAirport2|
varchar(5) | NO | |
NULL| |
| PlannedTypeOfFlight |
enum('','G','M','N','S','X') | NO | |
NULL| |
| PlannedPOB| smallint(3)
unsigned | NO | | NULL|
|
| TrueHeading | smallint(3) unsigned
zerofill| NO | | NULL| |
| OnGround |
enum('0','1')| NO | |
NULL| |
+---+--+--+-+-+---+
36 rows in set (0.00 sec)
mysql SHOW INDEX IN IVAOData;
+--++-+--++---+-+--++--++-+
| Table| Non_unique | Key_name| Seq_in_index | Column_name|
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--++-+--++---+-+--++--++-+
| IVAOData | 0 | PRIMARY |1 | EntryID|
A |13130556 | NULL | NULL | | BTREE | |
| IVAOData | 1 | ixFlightID |1 | FlightID |
A | 179870 | NULL | NULL | | BTREE | |
| IVAOData | 1 | ixProcessed |1 | isProcessed|
A | 2 | NULL | NULL | | BTREE | |
| IVAOData | 1 | ixProcessed |2 | TrackerTime|
A | 1193686 | NULL | NULL | | BTREE | |
| IVAOData | 1 | ixTracker |1 | VID|
A | 15744 | NULL | NULL | | BTREE | |
| IVAOData | 1 | ixTracker |2 | PlannedDepAirport |
A | 136776 | NULL | NULL | | BTREE | |
| IVAOData | 1 | ixTracker |3 | PlannedDestAirport |
A | 177439 | NULL | NULL | | BTREE | |
| IVAOData | 1 | ixTime |1 | TrackerTime|
A | 875370 | NULL | NULL | | BTREE | |
+--++-+--++---+-+--++--++-+
8 rows in set (0.00 sec)
mysql SELECT COUNT(FlightID) FROM FlightRoutes; ## Grows by a few houndred
records per day.
+-+
| COUNT(FlightID) |
+-+
| 106216 |
+-+
1 row in set (0.00 sec)
mysql SELECT COUNT(EntryID) FROM IVAOData; ## Grows by a few thousand
records per day.
++
| COUNT(EntryID) |
++
| 13130747 |
++
1 row in set (0.00 sec)
--
Regards,
Chris Knipe
Re: Where to index - over 15m records and growing
My appologies for leaving that bit out...
mysql EXPLAIN SELECT COUNT(FlightRoutes.FlightID) AS Count,
FlightRoutes.Dep AS Dep, FlightRoutes.Des AS Des FROM FlightRoutes LEFT JOIN
IVAOData ON FlightRoutes.FlightID=IVAOData.FlightID WHERE
IVAOData.TrackerTime = UNIX_TIMESTAMP('2010-04-01 00:00:00') AND
IVAOData.TrackerTime = UNIX_TIMESTAMP('2010-04-30 23:59:50') GROUP BY
FlightRoutes.Dep, FlightRoutes.Des ORDER BY COUNT(FlightRoutes.FlightID)
LIMIT 20;
++-+--+---+---++-+---++-+
| id | select_type | table| type | possible_keys | key|
key_len | ref | rows |
Extra |
++-+--+---+---++-+---++-+
| 1 | SIMPLE | FlightRoutes | index | PRIMARY | ixAirports |
14 | NULL | 106216 | Using temporary; Using
filesort |
| 1 | SIMPLE | IVAOData | ref | ixFlightID,ixTime | ixFlightID |
36 | tracker.FlightRoutes.FlightID | 73 | Using
where |
++-+--+---+---++-+---++-+
2 rows in set (0.33 sec)
Table / Index Sizes:
r...@netsonic:/var/lib/mysql/tracker# ls -lah IVAOData.* FlightRoutes.*
-rw-rw 1 mysql mysql 8.5K 2010-04-30 08:57 FlightRoutes.frm
-rw-rw 1 mysql mysql 9.7M 2010-05-07 01:13 FlightRoutes.MYD
-rw-rw 1 mysql mysql 6.1M 2010-05-07 01:39 FlightRoutes.MYI
-rw-rw 1 mysql mysql 11K 2010-05-06 11:23 IVAOData.frm
-rw-rw 1 mysql mysql 3.9G 2010-05-07 09:19 IVAOData.MYD
-rw-rw 1 mysql mysql 1.4G 2010-05-07 09:19 IVAOData.MYI
I expect the IVAOData table to roughly tripple in size. Currently it holds
2 months worth of data, the ideal situation would be to keep 6 months worth
of data in the table...
RAM Size on the machine is 8GB...
Regards,
Chris.
On Fri, May 7, 2010 at 9:17 AM, Anirudh Sundar sundar.anir...@gmail.comwrote:
Hey Chris,
Please send the explain plan for this query, the estimated table sizes (in
MB or GB) and the RAM capacity.
These are also the requisites for helping optimizing your query if
required...
Thanks.
Anirudh Sundar
On Fri, May 7, 2010 at 12:14 PM, Chris Knipe sav...@savage.za.org wrote:
Hi All,
I have a huge issue with a query - it copies the entire table to a tmp
table
when executing the query - and it's a big ass table Any help and/or
pointers please?
The query:
SELECT COUNT(FlightRoutes.FlightID) AS Count, FlightRoutes.Dep AS Dep,
FlightRoutes.Des AS Des FROM FlightRoutes LEFT JOIN IVAOData ON
FlightRoutes.FlightID=IVAOData.FlightID WHERE IVAOData.TrackerTime =
UNIX_TIMESTAMP('2010-04-01 00:00:00') AND IVAOData.TrackerTime =
UNIX_TIMESTAMP('2010-04-30 23:59:50') GROUP BY FlightRoutes.Dep,
FlightRoutes.Des ORDER BY COUNT(FlightRoutes.FlightID) LIMIT 20;
FlightRoutes:
mysql DESCRIBE FlightRoutes;
+--++--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--++--+-+-+---+
| FlightID | char(36) | NO | PRI | NULL| |
| Dep | varchar(5) | NO | MUL | NULL| |
| Des | varchar(5) | NO | | NULL| |
| Route| text | NO | | NULL| |
+--++--+-+-+---+
4 rows in set (0.00 sec)
mysql SHOW INDEX IN FlightRoutes;
+--+++--+-+---+-+--++--++-+
| Table| Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment
|
+--+++--+-+---+-+--++--++-+
| FlightRoutes | 0 | PRIMARY|1 | FlightID|
A | 106216 | NULL | NULL | | BTREE |
|
| FlightRoutes | 1 | ixAirports |1 | Dep |
A |3124 | NULL | NULL | | BTREE |
|
| FlightRoutes | 1 | ixAirports |2 | Des |
A | 26554 | NULL | NULL | | BTREE |
|
+--+++--+-+---+-+--++--++-+
3 rows in set (0.00 sec)
IVAOData:
mysql DESCRIBE IVAOData;
+---+--+--+-+-+---+
| Field |
Type
Re: Where to index - over 15m records and growing
On Fri, May 7, 2010 at 10:42 AM, Rob Wultsch wult...@gmail.com wrote:
Second thing:
How selective is
WHERE IVAOData.TrackerTime =UNIX_TIMESTAMP('2010-04-01 00:00:00')
AND IVAOData.TrackerTime =UNIX_TIMESTAMP('2010-04-30 23:59:50')
Test by running
SELECT COUNT(*)
FROM IVAOData
WHERE IVAOData.TrackerTime =UNIX_TIMESTAMP('2010-04-01 00:00:00')
AND IVAOData.TrackerTime =UNIX_TIMESTAMP('2010-04-30 23:59:50')
If this is a large proportion of the row count then you are probably
in store for pain. It sounds like you are matching half the table. Big
(intermediate) result sets often end in pain.
At this stage, you are correct. We have roughly 2 months worth of data in
the table and are selecting about half (one months worth), thus about 50%.
With 6 months worth of data in the table and selecting one months worth of
data, that's roughly 16% of the data - but it will still be a bulk large
result... Hmmm, something tells me I need to rethink this yes.
Third thing:
My (rather sleepy) gut thinks your best bet is a a composite index on
the table IVAOData on the columns TrackerTime and FlightID. This will
make all access to the table in this query hit a covering index.
Took over 12 hours to create the index on TrackerTime, and you're right - I
should have seen and realised this. I will drop the index on TrackerTime
and re-create it using both colums as I should have done in the first place.
Fourth thing:
What do you intend to ask the database with this query. This query
smells overly broad
The idea is to get a count of the number of entries from Dep to Des during
the last month. I.E. How many records are there where Dep and Des are the
same during the last month. With some changes to the application that
captures the data in the first place, I should be able to drop the need for
this query completely. You have made me think a bit here and you're right.
This is not the way to do it.
I'll rethink this a bit more and come up with something better.
PS - Started the query before my first email was even posted, it's still
running... 3948 Seconds the last time I checked...
--
Regards,
Chris Knipe
Small issue with FULLTEXT searches
Hi List,
Table structure:
mysql DESCRIBE FlightRoutes;
+-++--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-++--+-+-+---+
| EntryID | char(36) | NO | PRI | NULL| |
| Dep | varchar(5) | NO | MUL | NULL| |
| Des | varchar(5) | NO | | NULL| |
| Route | text | NO | | NULL| |
+-++--+-+-+---+
4 rows in set (0.01 sec)
Indexes:
mysql SHOW INDEXES FROM FlightRoutes;
+--+++--+-+---+-+--++--++-+
| Table| Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--+++--+-+---+-+--++--++-+
| FlightRoutes | 0 | PRIMARY|1 | EntryID |
A | 21375 | NULL | NULL | | BTREE | |
| FlightRoutes | 1 | ixAirports |1 | Dep |
A |1943 | NULL | NULL | | BTREE | |
| FlightRoutes | 1 | ixAirports |2 | Des |
A |7125 | NULL | NULL | | BTREE | |
| FlightRoutes | 1 | ixRoutes |1 | Dep |
NULL | 1 | NULL | NULL | | FULLTEXT | |
| FlightRoutes | 1 | ixRoutes |2 | Des |
NULL | 1 | NULL | NULL | | FULLTEXT | |
| FlightRoutes | 1 | ixRoutes |3 | Route |
NULL | 1 | NULL | NULL | | FULLTEXT | |
+--+++--+-+---+-+--++--++-+
6 rows in set (0.00 sec)
Query:
mysql SELECT Dep, Des, Route, MATCH(Dep, Des) AGAINST('FACT,OMDB') AS
Relevance FROM FlightRoutes;
ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
I don't get that.
What I have is a bunch of records:
Point A, Point B, Route 1
Point A, Point B, Route 2
Point A, Point B, Route 2
Point A, Point B, Route 3
What I want to achive, is to select the Route from Point A to Point B that
has the most relavence Naturally, I'm just starting to play with this
now, but I fail to see how I can possibly play with FULL TEXT indexes when
mySQL doesn't see / use the FULL TEXT that has already been created.
Thanks for the assistance.
--
Regards,
Chris Knipe
Re: Update Doesn't Update!
Quoting cars...@bitbybit.dk: Of course you can have ID=0. Definately agree mysql DESCRIBE test; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | autoinc | int(11) | NO | PRI | NULL| auto_increment | | value | varchar(10) | NO | | NULL|| +-+-+--+-+-++ 2 rows in set (0.00 sec) mysql SELECT * FROM test; +-++ | autoinc | value | +-++ | 0 | 1234567890 | +-++ 1 row in set (0.00 sec) mysql UPDATE test SET value='a' WHERE autoinc='0'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql SELECT * FROM test; +-+---+ | autoinc | value | +-+---+ | 0 | a | +-+---+ 1 row in set (0.00 sec) However, what I believe the problem is: mysql UPDATE test set value='12345678901' WHERE autoinc='0'; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql SELECT * FROM test; +-++ | autoinc | value | +-++ | 0 | 1234567890 | +-++ 1 row in set (0.00 sec) the value of value is too long for the varchar(10) in the table. It thus generates the warning, and truncate the field. The poster's table needs to be updated therefor to accept longer variables in the sizes column. -- Regards, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Server-Crash - What to do?
PS: I already tried with myisamchk -r -o *.MY*, but then all datasets are gone and the table is empty. :-( man myisamchk I tried myisamchk already. But after running the above rescue attempt, the tables are empty. Then I hope you made backups You, do keep backups right? -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server-Crash - What to do?
On filesystem level I made all rescue trials. My problem is, that only the MYD file is still available. I tried reconstructing it with myisamchk -r -o table_name.MYD, but as I said without the wanted result. MYD is your data, MYI is your indexes (Indexes are easy to rebuild, you should not worry about that). If myisamchk can't read the MYD, then I don't know what else there is. My guess would be that the data file is damage beyond repair :-( If you have a complete and working set of binary logs (incase you used replication which I doubt), you should also be able to get your data back out of that... But otherwise, I'd say you're out of luck. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IP Address Function?
Hi, I need to make *allot* of queries from a application dealing with IP Addresses. At the moment, I'm storing IP addresses as a VARCHAR(16). I would *like* to store them as Integers by converting the IP to it's numerical equivalent. I believe this would also save a enormous amount of table space. The problem is, I *must* do the conversion as part of my SQL Query. Either during the SELECT/UPDATE/INSERT, or via a Procedure... And I must obviously also be able to convert the Integer back into a IP address during queries Is this at all possible??? It's all explained very nicely at http://www.aboutmyip.com/AboutMyXApp/IP2Integer.jsp But how to do this as part of a query... *frown* Any help appreciated... Regards, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IP Address Function?
RTFM! Let that be a good lesson for me now :) INET_ATON() and INET_NTOA() Brilliant!!! Regards, Chris. - Original Message - From: Chris Knipe [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, August 26, 2006 9:03 PM Subject: IP Address Function? Hi, I need to make *allot* of queries from a application dealing with IP Addresses. At the moment, I'm storing IP addresses as a VARCHAR(16). I would *like* to store them as Integers by converting the IP to it's numerical equivalent. I believe this would also save a enormous amount of table space. The problem is, I *must* do the conversion as part of my SQL Query. Either during the SELECT/UPDATE/INSERT, or via a Procedure... And I must obviously also be able to convert the Integer back into a IP address during queries Is this at all possible??? It's all explained very nicely at http://www.aboutmyip.com/AboutMyXApp/IP2Integer.jsp But how to do this as part of a query... *frown* Any help appreciated... Regards, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL NOW() function producing 0000-00-00 00:00:00
We are using an NOW() function in our database and occasionally it produces odd results. There are entries where it states: -00-00 00:00:00 instead of the current time. Is this a bug, or are we using the function incorrectly? After patiently injecting at about 400 queries per seconds, a couple of hours later, I had about 5 million records in a table. Not a single one of them experienced the above I'm pretty sure your -00-00 does not come from NOW() -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Testing Email
INSERT INTO a VALUES (NOW()) ? Regards, Chris. - Original Message - From: Renato Golin [EMAIL PROTECTED] To: Nicholas Vettese [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, August 25, 2006 4:31 PM Subject: Re: Testing Email Nicholas Vettese wrote: I have been having problems with my email, and I wanted to test to this list. Will let you know when I receive it... --renato -- 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: MySQL NOW() function producing 0000-00-00 00:00:00
Doh.. Wrong email ;) INSERT INTO a VALUES (NOW()) ? Regards, Chris. - Original Message - From: Renato Golin [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, August 25, 2006 4:30 PM Subject: Re: MySQL NOW() function producing -00-00 00:00:00 Chris Knipe wrote: After patiently injecting at about 400 queries per seconds, a couple of hours later, I had about 5 million records in a table. Not a single one of them experienced the above It's one every 5 million and 1 entries... try again ;) Also, maybe (very improbable) it can be the way you are using NOW() within the query. Can you show us the query ? cheers, --renato -- 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]
What to index?
Hi,
I got 4 relatively big (for me at least) queries. At the moment, the data
in the tables are merely test data, but once the system goes into
production, I'm expecting millions of records in most of the tables. I'm
trying very hard thus to optimise my queries and tables to ensure I get a
reasonably good throughput once these tables start filling up, as altering
the tables for indexes once populated is obviously something I want to try
and avoid.
Having looked at one of the four queries (they're very similar), EXPLAIN
jeilds the following:
mysql EXPLAIN SELECT `UserChecks`.EntryID,
-`UserChecks`.Attribute,
-`UserChecks`.op,
-`UserChecks`.Value
- FROM `UserChecks`
- LEFT JOIN `User` ON `UserChecks`.UserID=`User`.EntryID
- LEFT JOIN `Group` ON `User`.GroupID=`Group`.EntryID
- LEFT JOIN `GroupTimes` ON `GroupTimes`.GroupID=`Group`.EntryID
- LEFT JOIN `Realm` ON `Group`.RealmID=`Realm`.EntryID
- LEFT JOIN `Client` ON `Realm`.ClientID=`Client`.EntryID
- WHERE `User`.isActive='y' AND
-`Group`.isActive='y' AND
-`Realm`.isActive='y' AND
-`Client`.isActive='y' AND
-`GroupTimes`.DOW=DAYOFWEEK(DATE_ADD('1970-01-01', INTERVAL
UNIX_TIMESTAMP() SECOND)) AND
-`GroupTimes`.StartTime DATE_FORMAT(DATE_ADD('1970-01-01',
INTERVAL UNIX_TIMESTAMP() SECOND), '%H:%i:%S') AND
-`GroupTimes`.StopTime DATE_FORMAT(DATE_ADD('1970-01-01',
INTERVAL UNIX_TIMESTAMP() SECOND), '%H:%i:%S') AND
-`Realm`.Realmname=SUBSTRING(TRIM(LOWER('[EMAIL PROTECTED]')),
(LOCATE('@', TRIM(LOWER('[EMAIL PROTECTED]'))) +1)) AND
-`User`.Username=LEFT(TRIM(LOWER('[EMAIL PROTECTED]')),
(LOCATE('@', TRIM(LOWER('[EMAIL PROTECTED]'))) -1))
- GROUP BY `UserChecks`.Attribute
- ORDER BY `UserChecks`.Attribute;
++-+++---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
++-+++---+--+-+--+--+-+
| 1 | SIMPLE | User | system | PRIMARY | NULL | NULL|
NULL |1 | Using temporary; Using filesort |
| 1 | SIMPLE | Group | system | PRIMARY | NULL | NULL|
NULL |1 | |
| 1 | SIMPLE | Realm | system | PRIMARY | NULL | NULL|
NULL |1 | |
| 1 | SIMPLE | Client | system | PRIMARY | NULL | NULL|
NULL |1 | |
| 1 | SIMPLE | UserChecks | ALL| NULL | NULL | NULL|
NULL |3 | Using where |
| 1 | SIMPLE | GroupTimes | ALL| NULL | NULL | NULL|
NULL |5 | Using where |
++-+++---+--+-+--+--+-+
6 rows in set (0.02 sec)
There are two items in my WHERE clause that does not use indexes. One on
the UserCheck table, and another on the GroupTimes table.
On the GroupTimes, I have DOW as a ENUM([1..7]) - I can more than likely add
a index, but with a cardinality of 7, I doubt it will be worth it.
StartTime and StopTime is a TIME field, I can add indexes on those. I'm
thus pretty sorted on the GroupTimes table (I think).
I have NO idea where in the WHERE clause I am referencing the UserChecks
tables (except for the GROUP BY and ORDER BY). I've added an index on the
Attribute column (VARCHAR(32)) but the EXPLAIN still shows that it is not
using the index
Is the above optimised? What can be done here to improve things
Thanks allot,
Chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.27-client and MySQL-4.1.21 server
http://dev.mysql.com/doc/mysql/en/old-client.html Regards, Chris. - Original Message - From: Odhiambo Washington [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 22, 2006 7:38 PM Subject: MySQL 4.0.27-client and MySQL-4.1.21 server hi Why is it that I cannot connect to mysql-4.1 server using mysql-4.0.27 client? ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client Where is the RTFM on this? I just want to read it. -Wash http://www.netmeister.org/news/learn2quote.html DISCLAIMER: See http://www.wananchi.com/bms/terms.php -- +==+ |\ _,,,---,,_ | Odhiambo Washington[EMAIL PROTECTED] Zzz /,`.-'`'-. ;-;;,_ | Wananchi Online Ltd. www.wananchi.com |,4- ) )-,_. ,\ ( `'-'| Tel: +254 20 313985-9 +254 20 313922 '---''(_/--' `-'\_) | GSM: +254 722 743223 +254 733 744121 +==+ Any fool can paint a picture, but it takes a wise person to be able to sell it. -- 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: Audit trail
Well MySQL will see all the changes coming from a single user (the user who the web site connects to the database as). You'll have to add your own routines to log that info to a table or something... Unless ofcourse, I'm missing something, which I doubt :) Regards, Chris. - Original Message - From: Chris W [EMAIL PROTECTED] To: MYSQL General List mysql@lists.mysql.com Sent: Monday, August 21, 2006 9:02 PM Subject: Audit trail I would like to create an audit trail for one table in my DB. Users will login to my web site and be able to enter and edit information, I want to keep a record of what changes are made by what user. These users will be web site users and not actual MySQL users. Is there an easy method in MySQL to do this, or do I just need to write code to track any changes as they are entered? -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- 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]
PURGE MASTER LOGS
Lo everyone, Small issue.. MySQL 4.1.12... PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 10 DAY); Absolutely nothing happens PURGE MASTER LOGS TO 'blah-bin.00030'; Logs are cleared immediately. Any reason why LOGS BEFORE is not working??? Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database problems.
Hi all, The moral of the story, is don't run out of disk space, but it's a bit to late for that now. A quick scenario One master server, two backups replicating from the master. Our data and bin logs are on two different partitions, and the partition holding the bin logs, ran out of disk space. We saw allot of errors in the mysql log on the master, stating that DELETE queries failed because it was unable to write this to the bin log. Question... Why would only DELETE fail? If it cannot write to the bin log because it is out of disk space, shouldn't INSERT / UPDATE also fail? Now, our slaves are going completely crazy right now. The data is beyond inconsistent, and we're desperately trying to figure out a way to restore the replication, without having to manually execute a good couple of million of DELETE queries on two seperate slaves, OR to take new snapshots from the master and redo the replication setup. It would SEEM to us that the bin log has gotten corrupted some time during the lack of disk space. Thus, I want to know now... - Generally, our slaves are missing ALLOT of DELETE queries, and the slave is now failing because it is getting duplicate records. - Running the slave with skip-errors untill it is up to date, is not a option. We NEED the DELETE queries to execute, because certain rows are DELETED and then RE-INSERTED with new values. Yes, I know we should use update, I'm just a administrator, not a programmer / developer. This is something that the developers needs to take up. - *IF* push comes to pull and we need to re-setup the slaves and replication, is there a way to take a snapshot from the master, WITHOUT having to shut down the database, OR lock the tables for long periods of time (We are talking about a DB that executes a good 20 queries per second on a slow day). - Can replication be 're-started' from the CURRENT bin-log position on the master, and if that has been done, can the 'missing' gaps in the two bin log positions (place of failure and place of current position) be manually / semi automatically replicated? I hope there is someone with some wise ideas I can use allot of them right now. Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: random rows selection
Put a index on col3 and it will be faster. That's the only way as far as I know. -- Chris. - Original Message - From: Michael Monashev [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, July 20, 2005 3:04 PM Subject: random rows selection Hello, How to select 5 random rows from big table with WHERE clause? This query very slow on 1 mln rows: SELECT col1, col2 FROM table WHERE col3=123 ORDER BY RAND() LIMIT 5 Have you a faster one? Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- 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]
urgent 4.1.11 / 4.1.12 upgrade
Hi, We've just upgraded (via FreeBSD Ports) our one database from 4.1.11 to 4.1.12, and we are being hit by http://bugs.mysql.com/bug.php?id=10674 - only on certain queries, using rather large temp tables. Now, from what I understand, there is a 4.1.12-1 available? Where is the source? It seems there is only a source RPM available on dev.mysql.com, the source tar ball will be good though. -- Chris. I love deadlines. I especially love the whooshing sound they make as they fly by... - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
memory errors / crashes
We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=536870912 read_buffer_size=2093056 max_used_connections=418 max_connections=2048 threads_connected=404 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 516080 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Righty. Now, the system has 4 x 3.2GHz Intel processors... It has 4GB of ram... systat shows that all 4 processors are definately used... NO swap space is used at all (4GB allocated), and yes, it uses the entire 4GB of ram under heavy load... I am aware of the POSSIBILITY of the 2GB limit... However, I highly doubt that this is related as the system has no problem to use the 4GB of RAM (According to top in any case). 1) Why does the system not swap, and 2) Why is Mysql complaining that it needs at least 512MB ram, whilst there are 4GB available to it?? I've been battling for over 3 weeks to get this right, I have made multiple posts on the list about this... can someone please just give me some answers We're running FBSD 5.4-STABLE. -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrating Database
Temporary replication comes to mind fs hot copy as well (maybe) -- Chris. I love deadlines. I especially love the whooshing sound they make as they fly by... - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' - Original Message - From: Brian Erickson [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, May 08, 2005 10:47 PM Subject: Migrating Database Greetings all, We are migrating our web site from one server to another and are having some issues with transferring our MySQL database. Here's the process we're currently using: 1) run mysqldump for each table in our database (i.e. one file for each table) 2) compress each file using gzip 3) transfer the files to our new server 4) decompress 5) import each table using: mysql [dbname] [filename] Here's the problem: several of our tables have over 20 million rows, and this import process is taking well over 6 hours. Obviously, our site cannot go live until the database is fully imported, and this is much too long for us to be down. The two possible solutions I've researched are: 1) Copy data files directly. This concerns me because of possible version incompatibilities. 2) Using LOAD DATA commands. I'm not familiar with these at all, and frankly, not real sure how they work. Can anyone offer us some advice as to the easiest way we can accomplish this, whether it's one of the above solutions, or another one completely? Thanks in advance! -- 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]
where and dates
Hi, I dont think it's needed to go into to much details here but the query: SELECT CONCAT(Airports.IATA, ' - ', Airports.Name) AS ArrivingFrom, ADDTIME(FlightData.TimeStamp, SUBTIME(FlightData.Enroute,DATE_FORMAT(SUBTIME(FlightData.TimeStamp,FlightData.ActDepTime),'%H:%i:%s'))) AS ArrvTime, CONCAT(FlightData.Network, ' - ', FlightData.PilotCallSign) AS FlightNum FROM FlightData LEFT JOIN Airports ON FlightData.AirportDep=Airports.ICAO WHERE FlightData.AirportDes='KJFK' AND DATE_SUB(CURDATE(),INTERVAL 2 HOUR) = ADDTIME(FlightData.TimeStamp, SUBTIME(FlightData.Enroute,DATE_FORMAT(SUBTIME(FlightData.TimeStamp,FlightData.ActDepTime),'%H:%i:%s'))) GROUP BY FlightData.PilotCallSign ORDER BY ArrvTime; Result: +---+-+---+ | ArrivingFrom | ArrvTime| FlightNum | +---+-+---+ | YYZ - Toronto/Pearson Intl| 2005-05-04 22:22:00 | V - ACA114| | LHR - Heathrow| 2005-05-04 22:42:00 | V - BAW115| | - Norman Manley Intl | 2005-05-04 22:45:00 | V - AAL645| | BOS - Logan Intl | 2005-05-04 22:50:00 | V - AE729 | | NULL | 2005-05-05 00:00:00 | V - N6340D| | JFK - Kennedy Intl| 2005-05-05 00:00:00 | V - AFA5290 | | MCO - Orlando Intl| 2005-05-05 00:05:00 | V - FEDEX1472 | | MCO - Orlando Intl| 2005-05-05 00:25:00 | V - FEDEX1563 | | BOS - Logan Intl | 2005-05-05 01:23:00 | V - AAL328| | YYZ - Toronto/Pearson Intl| 2005-05-05 01:40:00 | V - ACA221| | TPA - Tampa Intl | 2005-05-05 02:10:00 | V - N171E | | BOS - Logan Intl | 2005-05-05 02:10:00 | V - N37TJ | | MIA - Miami Intl | 2005-05-05 02:40:00 | V - JAL5837 | | - Eduardo Gomes Intl | 2005-05-05 02:42:00 | V - PST9864 | | CVG - Cincinnati/Northern Ky Intl | 2005-05-05 03:15:00 | V - MTN219| | YYZ - Toronto/Pearson Intl| 2005-05-05 03:45:00 | V - UAL392| | BOS - Logan Intl | 2005-05-05 03:50:00 | V - DVA2314 | | JFK - Kennedy Intl| 2005-05-05 05:30:00 | V - WSM-9950 | | YWG - Winnipeg Intl | 2005-05-05 05:40:00 | V - ML853 | | ZRH - Zurich | 2005-05-05 05:50:00 | V - AAL213| | ATL - The Hartsfield Atlanta Intl | 2005-05-05 06:05:00 | V - AAL209| | SJU - Luis Munoz Marin Intl | 2005-05-05 06:17:00 | V - AAL117| | LHR - Heathrow| 2005-05-05 08:40:00 | V - BAW01 | | ZRH - Zurich | 2005-05-05 13:00:00 | V - LH3733| | PRG - Ruzyne | 2005-05-05 13:10:00 | V - CSA255| | MAN - Manchester | 2005-05-05 15:30:00 | V - BAW1503 | | VIE - Schwechat | 2005-05-05 17:20:00 | V - AUA85Y| +---+-+---+ Problem... Well, the date clause on the WHERE part of the query is not doing what I am hoping it would. I need to get all records where ArrvTime is bigger than (i.e. in the future) of NOW() + 2Hrs. All my dates in the database are stored in GMT, so I only need to use NOW()-2H (which is the same as DATE_SUB(CURDATE(),INTERVAL 2 HOUR)). Can anyone help perhaps? -- Chris. I love deadlines. I especially love the whooshing sound they make as they fly by... - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where and dates
Found my problem... Thanks anyways :) -- Chris. I love deadlines. I especially love the whooshing sound they make as they fly by... - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' - Original Message - From: Chris Knipe [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, May 05, 2005 4:09 PM Subject: where and dates Hi, I dont think it's needed to go into to much details here but the query: SELECT CONCAT(Airports.IATA, ' - ', Airports.Name) AS ArrivingFrom, ADDTIME(FlightData.TimeStamp, SUBTIME(FlightData.Enroute,DATE_FORMAT(SUBTIME(FlightData.TimeStamp,FlightData.ActDepTime),'%H:%i:%s'))) AS ArrvTime, CONCAT(FlightData.Network, ' - ', FlightData.PilotCallSign) AS FlightNum FROM FlightData LEFT JOIN Airports ON FlightData.AirportDep=Airports.ICAO WHERE FlightData.AirportDes='KJFK' AND DATE_SUB(CURDATE(),INTERVAL 2 HOUR) = ADDTIME(FlightData.TimeStamp, SUBTIME(FlightData.Enroute,DATE_FORMAT(SUBTIME(FlightData.TimeStamp,FlightData.ActDepTime),'%H:%i:%s'))) GROUP BY FlightData.PilotCallSign ORDER BY ArrvTime; Result: +---+-+---+ | ArrivingFrom | ArrvTime| FlightNum | +---+-+---+ | YYZ - Toronto/Pearson Intl| 2005-05-04 22:22:00 | V - ACA114 | | LHR - Heathrow| 2005-05-04 22:42:00 | V - BAW115 | | - Norman Manley Intl | 2005-05-04 22:45:00 | V - AAL645 | | BOS - Logan Intl | 2005-05-04 22:50:00 | V - AE729 | | NULL | 2005-05-05 00:00:00 | V - N6340D | | JFK - Kennedy Intl| 2005-05-05 00:00:00 | V - AFA5290 | | MCO - Orlando Intl| 2005-05-05 00:05:00 | V - FEDEX1472 | | MCO - Orlando Intl| 2005-05-05 00:25:00 | V - FEDEX1563 | | BOS - Logan Intl | 2005-05-05 01:23:00 | V - AAL328 | | YYZ - Toronto/Pearson Intl| 2005-05-05 01:40:00 | V - ACA221 | | TPA - Tampa Intl | 2005-05-05 02:10:00 | V - N171E | | BOS - Logan Intl | 2005-05-05 02:10:00 | V - N37TJ | | MIA - Miami Intl | 2005-05-05 02:40:00 | V - JAL5837 | | - Eduardo Gomes Intl | 2005-05-05 02:42:00 | V - PST9864 | | CVG - Cincinnati/Northern Ky Intl | 2005-05-05 03:15:00 | V - MTN219 | | YYZ - Toronto/Pearson Intl| 2005-05-05 03:45:00 | V - UAL392 | | BOS - Logan Intl | 2005-05-05 03:50:00 | V - DVA2314 | | JFK - Kennedy Intl| 2005-05-05 05:30:00 | V - WSM-9950 | | YWG - Winnipeg Intl | 2005-05-05 05:40:00 | V - ML853 | | ZRH - Zurich | 2005-05-05 05:50:00 | V - AAL213 | | ATL - The Hartsfield Atlanta Intl | 2005-05-05 06:05:00 | V - AAL209 | | SJU - Luis Munoz Marin Intl | 2005-05-05 06:17:00 | V - AAL117 | | LHR - Heathrow| 2005-05-05 08:40:00 | V - BAW01 | | ZRH - Zurich | 2005-05-05 13:00:00 | V - LH3733 | | PRG - Ruzyne | 2005-05-05 13:10:00 | V - CSA255 | | MAN - Manchester | 2005-05-05 15:30:00 | V - BAW1503 | | VIE - Schwechat | 2005-05-05 17:20:00 | V - AUA85Y | +---+-+---+ Problem... Well, the date clause on the WHERE part of the query is not doing what I am hoping it would. I need to get all records where ArrvTime is bigger than (i.e. in the future) of NOW() + 2Hrs. All my dates in the database are stored in GMT, so I only need to use NOW()-2H (which is the same as DATE_SUB(CURDATE(),INTERVAL 2 HOUR)). Can anyone help perhaps? -- Chris. I love deadlines. I especially love the whooshing sound they make as they fly by... - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' -- 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]
High Load testing
Hi, I used sql-bench, but that is testing things we already know. We want to establish how many concurrent connections / queries our database server can handle before it starts getting into trouble (no, a different one from the email of last night). This system is a dual proc with 4GB ram and over 500GB on a RAID 5 setup I found super-smack (which tests 100% what I want), but the problem now is that it does not compile on FreeBSD, and the few (literally not even 3) linux boxes we have cannot handle the load to stress our DB server to the max. This I know because iostat on the FreeBSD DB Server shows that the entire box is virtually idle (the load averages confirms) while the tests on super-smack run. The worse I got was a load of 0.5 on the DB server with close to 800 threads running Any ideas please??? -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql mem usage
Hi, I have a P4 system with 1GB Ram and 512MB Swap (a little low I know). I'm running a multi threaded MySQL installation on it. With a bit of shock, I realised today that I ran completely out of swap space!!! Each MySQL thread is consuming about 160MB of ram, and I had close to 40 threads running. All my databases currently are approximately 1.5GB as far as file size goes, so it's not *that* much really. The biggest table is MyISAM (for delayed inserts), holding approximately 2million records currently. Is there a way to limit the amount of threads that MySQL can start, and to manage the pool of threads? I.E. min servers, max servers, idle servers, etc And, can someone point me to a good side regarding getting optimum values for my.cnf? I'm running MySQL 4.1.11 my.cnf: [mysqld] log-queries-not-using-indexes skip-name-resolve skip-show-database skip-external-locking port = 3306 socket = /tmp/mysql.sock key_buffer_size= 8M max_allowed_packet = 4M bind-address = x.x.x.x.122 server-id = 100 set-variable = back_log=10 set-variable = connect_timeout=10 set-variable = interactive_timeout=28800 set-variable = interactive_timeout=20 set-variable = join_buffer=256000 set-variable = key_buffer_size=64M set-variable = max_allowed_packet=1M set-variable = max_connect_errors=512 set-variable = max_connections=64 set-variable = max_user_connections=64 set-variable = myisam_sort_buffer_size=16M set-variable = net_buffer_length=32K set-variable = net_read_timeout=30 set-variable = net_retry_count=1 set-variable = net_write_timeout=60 set-variable = query_cache_size=16M set-variable = query_cache_limit=1M set-variable = query_cache_type=1 set-variable = read_buffer_size=2M set-variable = sort_buffer=2M set-variable = table_cache=64 set-variable = thread_stack=64k set-variable = thread_cache=16 set-variable = thread_concurrency=2 set-variable = tmp_table_size=20 set-variable = wait_timeout=28800 set-variable = delayed_insert_limit=2000 set-variable = delayed_insert_timeout=1800 set-variable = delayed_queue_size=8000 set-variable = max_delayed_threads=32 -- Chris. I love deadlines. I especially love the whooshing sound they make as they fly by... - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql mem usage
top... last pid: 56803; load averages: 0.29, 0.31, 0.14 up 5+11:10:10 20:09:05 174 processes: 1 running, 169 sleeping, 4 zombie CPU states: 0.0% user, 2.3% nice, 1.2% system, 0.0% interrupt, 96.5% idle Mem: 422M Active, 237M Inact, 217M Wired, 43M Cache, 111M Buf, 73M Free Swap: 512M Total, 297M Used, 215M Free, 58% Inuse, 16K In PID USERNAME PRI NICE SIZERES STATETIME WCPUCPU COMMAND 55651 mysql 8 12 138M 33524K nanslp 0:21 0.00% 0.00% mysqld 55649 mysql 20 14 138M 33524K pause0:21 0.00% 0.00% mysqld 55866 mysql 4 14 138M 33524K sbwait 0:12 0.00% 0.00% mysqld 55912 mysql 8 12 138M 33524K nanslp 0:04 0.00% 0.00% mysqld 55697 mysql 4 14 138M 33524K sbwait 0:03 0.00% 0.00% mysqld 55612 mysql110 14 138M 33524K select 0:01 0.00% 0.00% mysqld 55613 mysql110 14 138M 33524K select 0:00 0.00% 0.00% mysqld 55652 mysql 8 12 138M 33524K nanslp 0:00 0.00% 0.00% mysqld 55606 mysql107 11 138M 33524K select 0:00 0.00% 0.00% mysqld 55607 mysql107 11 138M 33524K select 0:00 0.00% 0.00% mysqld 56390 mysql 20 14 138M 33524K pause0:00 0.00% 0.00% mysqld 55653 mysql 4 14 138M 33524K sbwait 0:00 0.00% 0.00% mysqld 55614 mysql 20 14 138M 33524K pause0:00 0.00% 0.00% mysqld 55610 mysql 20 14 138M 33524K pause0:00 0.00% 0.00% mysqld 55615 mysql 20 11 138M 33524K pause0:00 0.00% 0.00% mysqld 55609 mysql 20 14 138M 33524K pause0:00 0.00% 0.00% mysqld 55611 mysql 20 14 138M 33524K pause0:00 0.00% 0.00% mysqld 55608 mysql 20 14 138M 33524K pause0:00 0.00% 0.00% mysqld 55616 mysql 20 12 138M 33524K pause0:00 0.00% 0.00% mysqld 76746 squid 960 90756K 38016K select 4:16 0.00% 0.00% squid 56725 pmx4 960 36524K 34908K select 0:01 0.00% 0.00% perl 56724 pmx4 960 36172K 34560K select 0:00 0.00% 0.00% perl FreeBSD 5.4-STABLE, linuxthreads -- Chris. I love deadlines. I especially love the whooshing sound they make as they fly by... - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' - Original Message - From: Dan Nelson [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 03, 2005 7:34 PM Subject: Re: mysql mem usage In the last episode (May 03), Chris Knipe said: I have a P4 system with 1GB Ram and 512MB Swap (a little low I know). I'm running a multi threaded MySQL installation on it. With a bit of shock, I realised today that I ran completely out of swap space!!! Each MySQL thread is consuming about 160MB of ram, and I had close to 40 threads running. All my databases currently are approximately 1.5GB as far as file size goes, so it's not *that* much really. The biggest table is MyISAM (for delayed inserts), holding approximately 2million records currently. Threads don't have their own memory. The process as a whole does. I can only assume you're running a Linux 2.4 kernel that displays threads with separate pids in top, which causes all sorts of confusion. They all share the same address space though so it's only using 160MB. If you're swapping, chances are it's something else at fault. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql mem usage
PID USERNAME PRI NICE SIZERES STATETIME WCPUCPU COMMAND 55651 mysql 8 12 138M 33524K nanslp 0:21 0.00% 0.00% mysqld 55649 mysql 20 14 138M 33524K pause0:21 0.00% 0.00% mysqld 55866 mysql 4 14 138M 33524K sbwait 0:12 0.00% 0.00% mysqld Ya, since you're using linuxthreads, these are all really one process with one single 138MB address space; note that SIZE and RES are identical all the way down. 76746 squid 960 90756K 38016K select 4:16 0.00% 0.00% squid 56725 pmx4 960 36524K 34908K select 0:01 0.00% 0.00% perl 56724 pmx4 960 36172K 34560K select 0:00 0.00% 0.00% perl Try running ps axlm, which will show all the processes sorted by memory usage. If there are more of those perl scripts running, they may be a contributing factor. Apache with script modules (perl/php etc) can also suck up lots of memory if you get lots of hits at once. You learn something new every day. It seems the culprit actually was our anti-spam engines + apache/mod_fastcgi. We're down to 100MB Swap used and 100MB free MEM - big difference. We'll need to get a upgrade though, we must be able to carry the load... Thanks for the help -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOINS same data twice?
SELECT tlb1.DepartureLocation AS Departure, tbl1.DepartureLocation AS Destination LEFT JOIN DepartureLocation ON tlb2.DepartureID=tlb1.LocationID LEFT JOIN DepartureLocation ON tbl2.ArrivalID=tbl1.LocationID ORDER BY tbl2.Time etc etc etc I get ERROR 1066 (42000): Not unique table/alias: 'tbl1' The statement above looks a bit strange, there is no FROM clause, and there seems to be 4 tables involved, named tbl1, tlb1, tbl2 and tlb2? According to your table description above, there is no column named DepartureLocation? According to the query, it seems like a table has that name? Sorry, was trying to keep things simple and made a couple of typos (yes, silly of me, I know). I'll pretend you have two tables named tbl1 and tbl2, containing the columns you described above: tbl1: LocationID, Location tbl2: DepartureID, ArrivalID, Time Then try something like this: SELECT Departure.Location, Arrival.Location AS Destination, Time FROM tbl2 LEFT JOIN tbl1 AS Departure ON Departure.LocationID = DepartureID LEFT JOIN tbl1 AS Arrival ON Arrival.LocationID = ArrivalID ORDER BY Time Note that there are two different uses of alias in this statementtable alias and column alias: the table tbl1 is aliased twice, to Departure and Arrival. Aliasing a table is necessary to be able to join the same table multiple times. The Arrival.Location column is aliased to Destination. This is necessary to avoid two columns from having the same name, in this case both columns would have been named Location. YAY :) Ok, that does explain allot, and it does work yes. Thank you VERY much. mysql SELECT FlightData.PilotCallSign AS FLNum, -FlightLogs.Tracker AS Tracker, -FlightLogs.TimeStart AS TimeDep, -Departure.IATA AS AirpDep, -Arrival.IATA AS AirpDes, -FlightLogs.Plane AS Plane - FROM FlightLogs - LEFT JOIN Airports AS Departure ON FlightLogs.AirpDep=Departure.ICAO - LEFT JOIN Airports AS Arrival ON FlightLogs.AirpDes=Arrival.ICAO - LEFT JOIN FlightData ON FlightLogs.Tracker=FlightData.Tracker - GROUP BY FlightData.Tracker - ORDER BY FlightLogs.TimeStart - LIMIT 30; +-+--+-+-+-+--+ | FLNum | Tracker | TimeDep | AirpDep | AirpDes | Plane| +-+--+-+-+-+--+ SNIP +-+--+-+-+-+--+ 30 rows in set (15.52 sec) My next question now, would be where to INDEX. Size is a MAJOR issue here. Two days worth of data, and I am already sitting with a DB over 60MB. The above query is simply for debug / testing purposes. There will be a WHERE clause, i.e. WHERE FlightLogs.CCID='blah' I will more than likely also only query BETWEEN certain dates, based on the FlightLogs.StartTime - thus, also no LIMIT. Airports have INDEX on Name, CountryID, ICAO as well as IATA. FlightData have INDEX on PilotCID, PillotCallSign, and Tracker. FlightLogs have INDEX on Tracker(Unique), CCID, AirpDes, AirpDep, and TimeStart Thus, all columns used for the JOINs, have indexes already? All GROUP BYs have indexes already? Why is this taking so long? FlightData has just over 130.5K records, Airports just over 23K records. Airports won't grow much anymore, FlightData will grow to a ENOURMOUSE size, FlightLogs will be exponentially smaller than FlightData - but also quite big over time... Hope you can be of assistance again :) -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOINS same data twice?
I eliminated your GROUP BY clause because you weren't actually GROUPing anything. You weren't looking for a MIN(), a MAX(), an AVG(), or anything else that GROUP BY was intended to be used for. That method of de-duplicating results will return potentially misleading information as it will randomly pick just one record from all of the records that fit within that group. This way, by using DISTINCT, you can see if things change mid-flight or not (which is either an indication of bad data or some other troubles). Ok, again I should have explained this. The reason why FlightData is so huge and growing so rapidly, is because we take a snapshot every minute of every single flight we monitor. I therefore group by a time stamp and/or FlightTracker (Depending what data I need), so that I can only get the data returned each time the data (plane's position) actually changed. A quick example: mysql SELECT * FROM FlightData LIMIT 2\G *** 1. row *** EntryID: 3471 Network: V TimeStamp: 2005-04-14 07:27:42 Tracker: df0363ea-fe06-1027-b1dd-00e01884a424 PilotCallSign: xxx PilotCID: xxx PilotRealName: Abdul Henderson KDFW CurHeading: 124 CurAlt: 34887 CurGS: 469 Plane: H/DC10/W Transponder: 7207 QNHHg: 29.80 QNHMb: 1009.14 Enroute: 06:00:00 Feul: 10:00:00 AirportDep: KLAX AirportDes: MMMX AirportAlt: PositionCur: 32.06073/-114.70269 PositionDep: 33.942536/-118.408075 PositionDes: 19.435278/-099.07 PLGS: 480 PLAlt: 35000 PLDepTime: 640 PLFlightType: I *** 2. row *** EntryID: 3472 Network: V TimeStamp: 2005-04-14 07:37:42 Tracker: df0363ea-fe06-1027-b1dd-00e01884a424 PilotCallSign: xxx PilotCID: xxx PilotRealName: xxx CurHeading: 255 CurAlt: 35707 CurGS: 470 Plane: B738 Transponder: 7207 QNHHg: 29.96 QNHMb: 1014.56 Enroute: 02:12:00 Feul: 05:30:00 AirportDep: KPDX AirportDes: KLAX AirportAlt: PositionCur: 33.94045/-118.40567 PositionDep: 45.588722/-122.597500 PositionDes: 33.942536/-118.408075 PLGS: 350 PLAlt: 34000 PLDepTime: 220 PLFlightType: I 2 rows in set (0.00 sec) FlightTracker therefore appears in the database a couple of hundred times for each flight. It is inserted each and every time we poll the flight, whether the data has changed or not. If I do not group therefore, my original query would return the Deparutre / Arrival for each and every occurance for FlightTracker. I do however understand what you are saying, and I was not aware that it would Group the GROUP / JOIN the entire table before adding the exceptions. This is obviously my problem as well. Mainly, I use the TimeStamp to determine WHEN last the position of a flight was changed (and thus I know when and what change was made in the flight), whilst FlightTracker is used to group all the occurances of the changes in the flight. This command would fit between the CREATE...SELECT... and the second SELECT... Even though the docs say that only 1 index is ever used per query, I believe that each JOIN can also make use of an index if it exists (this belief is based on the results of the EXPLAIN(s) of queries containing JOINed tables. However, I could be completely wrong in this belief.). I was seriously hoping to be able to do this effeciently with one query. But what you are saying does make sense however - especially when the database gets bigger and we're sitting with 1GB of flight data for example. One question though, if we sit with (for example) with 1,000,000 records instead of the odd 350K records in the database now. Will it not take just as long to populate the temp table? I do not think this technique is faster if you eliminate the WHERE clause from the first query. But if you do have a WHERE, it should return much faster than if you simply added a WHERE to your original query. Ok. That is something that I was not sure about either yes. WHERE would obviously make the JOINs / GROUP BY smaller in regards to the amount of data examined and processed. I thought perhaps LIMIT would do the same, in that it would use only the 30 records specified in my example query. Obviously it JOIN / GROUP the entire table first, and only then return the first 30 records - which is indeed a big difference. mysql SELECT FlightData.PilotCallSign AS FLNum, FlightLogs.Tracker AS Tracker, FlightLogs.TimeStart AS TimeDep, Departure.IATA AS AirpDep, Arrival.IATA AS AirpDes, FlightLogs.Plane AS PlaneFROM FlightLogsLEFT JOIN Airports AS Departure ON FlightLogs.AirpDep=Departure.ICAOLEFT JOIN Airports AS Arrival ON FlightLogs.AirpDes=Arrival.ICAOLEFT JOIN FlightData ON FlightLogs.Tracker=FlightData.Tracker GROUP BY FlightData.Tracker ORDER BY FlightLogs.TimeStart LIMIT 30;
very complicated queries (for me at least).
It's official. I need help ;)
Let's do the table structures quickly.
mysql DESCRIBE FlightData;
+---+--+--+-+-++
| Field | Type | Null | Key | Default
| Extra |
+---+--+--+-+-++
| EntryID | int(10) unsigned | | PRI | NULL
| auto_increment |
| Network | enum('I','V')| | | V
||
| TimeStamp | timestamp| YES | | -00-00
00:00:00 ||
| Tracker | varchar(38) | YES | | NULL
||
| PilotCallSign | varchar(20) | | MUL |
||
| PilotCID | mediumint(6) unsigned| | MUL | 0
||
| PilotRealName | varchar(50) | | |
||
| CurHeading| tinyint(3) unsigned zerofill | | | 000
||
| CurAlt| smallint(5) unsigned | | | 0
||
| CurGS | smallint(4) unsigned | | | 0
||
| Plane | varchar(20) | | |
||
| Transponder | smallint(4) unsigned | | | 0
||
| QNHHg | varchar(5) | | |
||
| QNHMb | varchar(8) | | |
||
| Enroute | time | | | 00:00:00
||
| Feul | time | | | 00:00:00
||
| AirportDep| varchar(4) | | |
||
| AirportDes| varchar(4) | | |
||
| AirportAlt| varchar(4) | | |
||
| PositionCur | varchar(22) | | |
||
| PositionDep | varchar(22) | | |
||
| PositionDes | varchar(22) | | |
||
| PLGS | smallint(4) unsigned | | | 0
||
| PLAlt | varchar(10) | | |
||
| PLDepTime | varchar(4) | YES | |
||
| PLFlightType | enum('','I','S','V') | | |
||
| FlightPlan| text | | |
||
| Remarks | varchar(150) | | |
||
| ActDepTime| time | | | 00:00:00
||
| Logon | time | | | 00:00:00
||
+---+--+--+-+-++
mysql DESCRIBE Airports;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| EntryID | smallint(5) unsigned | | PRI | NULL| auto_increment |
| CountryID | smallint(5) unsigned | | MUL | 0 ||
| Name | varchar(50) | | MUL | ||
| IATA | char(3) | | MUL | ||
| ICAO | varchar(4) | | MUL | ||
| Long | varchar(50) | | | ||
| Lat | varchar(50) | | | ||
| Alt | int(11) | | | 0 ||
| City | varchar(200) | | | ||
+---+--+--+-+-++
9 rows in set (0.00 sec)
Sample data.
mysql SELECT * FROM FlightData LIMIT 1\G
*** 1. row ***
EntryID: 3471
Network: V
TimeStamp: 2005-04-14 07:27:42
Tracker: df0363ea-fe06-1027-b1dd-00e01884a424
PilotCallSign: AAL133
PilotCID:
PilotRealName: x
CurHeading: 124
CurAlt: 34887
CurGS: 469
Plane: H/DC10/W
Transponder: 7207
QNHHg: 29.80
QNHMb: 1009.14
Enroute: 06:00:00
Feul: 10:00:00
AirportDep: KLAX
AirportDes: MMMX
AirportAlt:
PositionCur: 32.06073/-114.70269
PositionDep: 33.942536/-118.408075
PositionDes: 19.435278/-099.07
PLGS: 480
PLAlt: 35000
PLDepTime: 640
PLFlightType: I
FlightPlan: HOLTZ5 JLI ASUTA ALTAR PPE ZCL GABTU
Remarks: SEL/CJ-AG /V/
ActDepTime: 06:40:00
Logon: 06:32:49
mysql SELECT * FROM Airports LIMIT 1\G
***
Re: update ... where col is null problem.
I use = 0. It works for me. -- Chris. I love deadlines. I especially love the whooshing sound they make as they fly by... - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' - Original Message - From: Emil Wilmanski [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, April 14, 2005 11:19 AM Subject: update ... where col is null problem. Hi... I have problem with update query. Select query with the same where is OK. mysql select AcctStartTime,AcctSessionTime from radacct where AcctStopTime is null; +-+-+ | AcctStartTime | AcctSessionTime | +-+-+ | 2005-04-11 12:16:19 | 191154 | | 2005-04-13 06:51:59 | 37984 | | 2005-04-13 16:28:51 |3617 | | 2005-04-13 17:36:57 | 16279 | | 2005-04-13 17:37:01 | 16280 | | 2005-04-13 17:37:03 | 16279 | | 2005-04-13 22:15:41 | 0 | | 2005-04-13 22:15:56 | 0 | | 2005-04-13 22:23:55 | 44018 | | 2005-04-14 10:34:14 | 0 | +-+-+ 10 rows in set (0.00 sec) but update: mysql update radacct set AcctStopTime=null where AcctStopTime is null; Query OK, 0 rows affected (0.00 sec) I know that this update query is stupid byt it is only to show problem. this is more/less my table: mysql desc radacct; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | RadAcctId | bigint(21) | | PRI | NULL| auto_increment | | AcctStartTime | datetime| | MUL | -00-00 00:00:00 | | | AcctStopTime | datetime| | MUL | -00-00 00:00:00 | | | AcctSessionTime| int(12) | YES | | NULL| | my sql system/version: debian/mysql 4.1.11 -- Emil Wilmanski [EMAIL PROTECTED] Galeria-M -- 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]
LEFT JOINS same data twice?
Hi, Is it possible to left join the same data twice? TBL ONE: LocationID Location, varchar(100) TBL TWO: DepartureID, ArrivalID, Time SELECT tlb1.DepartureLocation AS Departure, tbl1.DepartureLocation AS Destination LEFT JOIN DepartureLocation ON tlb2.DepartureID=tlb1.LocationID LEFT JOIN DepartureLocation ON tbl2.ArrivalID=tbl1.LocationID ORDER BY tbl2.Time etc etc etc I get ERROR 1066 (42000): Not unique table/alias: 'tbl1' Thanks. -- Chris. I love deadlines. I especially love the whooshing sound they make as they fly by... - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ms Sql unique identifier field
AUTO_INCREMENT is not the same as newid(). Frankly, something like newid() would be very useful in MySQL. -- Chris. - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 17, 2005 5:53 PM Subject: Re: Ms Sql unique identifier field Hello. You should think about AUTO_INCREMENT. See: http://dev.mysql.com/doc/mysql/en/example-auto-increment.html [snip] Hi! i have a question: FIELD IN MS SQL SERVER FiledName: uiAutID DataType: unique identifier DefaultValue: newid() how i can describe the some column in MySQL? if isn't possible, how can i solve my problem? P.S.: is necessary for us, use unique identifier data type because the datas must be exchanged with others companies and we need garantee unique identification of all the informations. Maurizio, Sponda [snip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication errors
Lo again,
Master and Slave server, both running 5.0.2-LOG (Exactly the same versions).
On the master, everything is working fine. Data that gets inserted /
updated / deleted etc goes into the bin log, and gets replicated to the
slave.
HOWEVER
On the slave, I am getting syntax errors from the queries received from the
master?!?!?!?! HOW can I query NOT be correct when it gets to the slave, but
yet on the master, the query was issued successfully???
The latest example:
mysql SHOW SLAVE STATUS\G
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 198.19.0.35
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.01
Read_Master_Log_Pos: 8916111
Relay_Log_File: netsphere-relay-bin.09
Relay_Log_Pos: 956043
Relay_Master_Log_File: mysql-bin.01
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1064
Last_Error: Error '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 ''64.7.0/B' at line 1' on query. Default database: 'DB'.
Query: 'INSERT INTO GlobalCountryPrefix (CountryID, Network, Prefix) VALUES
('230', '64.7.0/B'
Skip_Counter: 0
Exec_Master_Log_Pos: 5911231
Relay_Log_Space: 3963045
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: 79761
1 row in set (0.00 sec)
Sure, there is a syntax error in the query - yes. But why does the master
not put the complete query in the bin log? This is the second time
in 3 days that it has happened now. Whilst we are on this, how do I tell
the slave to skip the query and proceed with the rest of the binlog...
Thanks to this now, we already have a MASSIVE backlog that needs to be
processed.
--
Chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
Chris Knipe wrote: [..] Sure, there is a syntax error in the query - yes. But why does the master not put the complete query in the bin log? This is the second time in 3 days that it has happened now. Whilst we are on this, how do I tell the slave to skip the query and proceed with the rest of the binlog... Thanks to this now, we already have a MASSIVE backlog that needs to be processed. [..] I do not know if it is a best solution but that's shure that it's an easiest. Just add slave-skip-errors = all to the config... or just with errors that you want to skip, refer to the manual for an extensive description. Ok, that's fair enough, and it seems to be working again for now. But just how realiable is MySQL's replication to begin with? Skipping the errors does not resolve my problem. Skiping the errors mearly means that my slave server will not have a accurate representation of the data on the master. Surely, there must be a reason why the Master server is not logging queries properly into the binary log??? Either it's not being logged right, or the Slave is not reading it correctly. If it was different versions of MySQL, I could have perhaps let this fly with a incompatibility of sorts, but they are exactly the same versions?? And this is not the first time it's happening either... It really makes me doubt whether MySQL is the right approach to take to this whole replication vs data redundancy scenario. -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
Chris Knipe wrote: [..] Sure, there is a syntax error in the query - yes. But why does the master not put the complete query in the bin log? This is the second time in 3 days that it has happened now. Whilst we are on this, how do I tell the slave to skip the query and proceed with the rest of the binlog... Thanks to this now, we already have a MASSIVE backlog that needs to be processed. [..] I do not know if it is a best solution but that's shure that it's an easiest. Just add slave-skip-errors = all to the config... or just with errors that you want to skip, refer to the manual for an extensive description. Ok, that's fair enough, and it seems to be working again for now. But just how realiable is MySQL's replication to begin with? Skipping the errors does not resolve my problem. Skiping the errors mearly means that my slave server will not have a accurate representation of the data on the master. Surely, there must be a reason why the Master server is not logging queries properly into the binary log??? Either it's not being logged right, or the Slave is not reading it correctly. If it was different versions of MySQL, I could have perhaps let this fly with a incompatibility of sorts, but they are exactly the same versions?? And this is not the first time it's happening either... It really makes me doubt whether MySQL is the right approach to take to this whole replication vs data redundancy scenario. -- Chris. Now, I've been running for not even 1 hour with the skip-errors enabled A quick check, on ONE table... [EMAIL PROTECTED]:~# mysql --host=mysqldb01 --database=DB -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A elcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 159 to server version: 5.0.2-alpha-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SELECT COUNT(RadAcctId) FROM RadiusAccounting; +--+ | COUNT(RadAcctId) | +--+ |1144320 | +--+ 1 row in set (1.69 sec) mysql quit Bye [EMAIL PROTECTED]:~# mysql --host=mysqldb02 --database=DB -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 105 to server version: 5.0.2-alpha-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SELECT COUNT(RadAcctId) FROM RadiusAccounting; +--+ | COUNT(RadAcctId) | +--+ |1144218 | +--+ 1 row in set (0.05 sec) mysql So there's already data missing on the slave *shrugs* -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
- Original Message - From: Richard Lynch [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, March 11, 2005 12:08 AM Subject: Re: replication errors mysql SELECT COUNT(RadAcctId) FROM RadiusAccounting; +--+ | COUNT(RadAcctId) | +--+ |1144320 | +--+ 1 row in set (1.69 sec) [EMAIL PROTECTED]:~# mysql --host=mysqldb02 --database=DB -p mysql SELECT COUNT(RadAcctId) FROM RadiusAccounting; +--+ | COUNT(RadAcctId) | +--+ |1144218 | +--+ 1 row in set (0.05 sec) So there's already data missing on the slave *shrugs* Or there is data still pending in the binlog between master and slave. Replication is not instantaneous. If you shut down the Master and wait, does all the data make it to the slave? I'm not promising it's not broken: I'm just promising that your analysis is too brief and incomplete to be conclusive. :-) I thought someone would bring this up. I should have mentioned in my original post, I did check, the slave was up to date... Quick test on the slave: [EMAIL PROTECTED]:~# mysql --host=mysqldb02 --database=DB -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1806 to server version: 5.0.2-alpha-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SHOW SLAVE STATUS\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 198.19.0.35 Master_User: replicator Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.06 Read_Master_Log_Pos: 7634 Relay_Log_File: netsphere-relay-bin.29 Relay_Log_Pos: 7768 Relay_Master_Log_File: mysql-bin.06 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: 7634 Relay_Log_Space: 7768 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: 9 1 row in set (0.00 sec) mysql SELECT COUNT(RadAcctID) FROM RadiusAccounting; +--+ | COUNT(RadAcctID) | +--+ |1144374 | +--+ 1 row in set (0.05 sec) mysql On the master... [EMAIL PROTECTED]:~# mysql --host=mysqldb01 --database=DB -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 225 to server version: 5.0.2-alpha-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SHOW MASTER STATUS; +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql-bin.06 | 7634 | | | +--+--+--+--+ 1 row in set (0.04 sec) mysql SELECT COUNT(RadAcctID) FROM RadiusAccounting; +--+ | COUNT(RadAcctID) | +--+ |1144475 | +--+ 1 row in set (0.12 sec) mysql -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
So there's already data missing on the slave *shrugs* Yep, you told it to SKIP ERRORS. That means that if any query generates an error, for any reason, it's just going to skip over it and move on. So data was missing on your slave the moment you restarted replication -- it skipped the UPDATE it had a problem with. From that moment on, your replication was out of sync. Correct yes. As I said, my problem is not the data that is missing, my problem is the fact that a update / insery query executes successfully on the master, but generates a error on the slave because the bin logs aren't read / written properly... That's the problem here, not the missing data... For that I have backups. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
safe way of replication?
Lo all, Just wondering... Would the below be considered a safe way to do replication... MasterBD: One Database (most Critical) SlaveDB: Replicating all databases from MasterBD as well as hosting allot of other 3rd party, or customer DBs. Basically, the MasterBD holds a single critical database for our company (i.e. main database). The slave DB then becomes a slave for the MasterDB (one way replication), but also hosts allot of other not so critical databases? So far, this seems to be working, but I am getting a couple of errors or problems in regards to the mysql table for user authentication... My guess is that I more than likely just don't need to replicate that DB from the master. Would this be considered safe?? Or should I look at a third database server for deployment? -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: safe way of replication?
Hmm, We'll we just won't give them access to the replicated database to begin with... It's used internally, and the few people in the company that do need to write to the database are all skilled administrators / programmers who know what to do :) Dont think we'll be having those problem. I'll stop replicating mysql tables then... Thanks ;) -- Chris. - Original Message - From: Gary Richardson [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, March 09, 2005 6:23 PM Subject: Re: safe way of replication? I would probably not replica the mysql database in your case. We run a similar setup. One problem we have is people connecting to the wrong database server and applying updates (for example, they think they are in the master, but it's really the slave and they perform an update or an insert). As you can guess, it causes problems with data integrity. In order to get around this, we remove insert,update,delete from our users on the slave.. out. On Wed, 9 Mar 2005 17:49:10 +0200, Chris Knipe [EMAIL PROTECTED] wrote: Lo all, Just wondering... Would the below be considered a safe way to do replication... MasterBD: One Database (most Critical) SlaveDB: Replicating all databases from MasterBD as well as hosting allot of other 3rd party, or customer DBs. Basically, the MasterBD holds a single critical database for our company (i.e. main database). The slave DB then becomes a slave for the MasterDB (one way replication), but also hosts allot of other not so critical databases? So far, this seems to be working, but I am getting a couple of errors or problems in regards to the mysql table for user authentication... My guess is that I more than likely just don't need to replicate that DB from the master. Would this be considered safe?? Or should I look at a third database server for deployment? -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
two-way replication
Hi, Is two-way replication possible with MySQL 5.x? Any good sites / docs describing this type of setup? -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: two-way replication
- Original Message - From: [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, February 28, 2005 7:11 PM Subject: Re: two-way replication Chris Knipe [EMAIL PROTECTED] wrote on 02/28/2005 11:53:14 AM: Hi, Is two-way replication possible with MySQL 5.x? Any good sites / docs describing this type of setup? -- Chris. By two-way replication, do you mean changes to either database are synchronized to the other? This is a dual-master situation and not yet supported by MySQL. The problem is with cross-server locking. Right now (even with v5.0) MySQL does not know to lock a record on your other server to prevent someone over there from changing that record while you are editing it on this server. Same goes for inserts (auto_increment values are not coordinated) and deletes. Now, I have been monitoring the development lists on the MySQL Cluster and MySQL Federated lines and both are working on inter-server coordination but I have no idea if either of those products will fit your need. As of today, MySQL does support 1-way, and chained replication. There is no rule that prevents you from creating circular replication by making each server the child of the other. The danger with circular replication is that you need to ensure (by application-level logic, data partitioning, or any of other techniques) that insert collisions do not occur (no two new records get the same primary key from different servers) and that you can gracefully handle simultaneous updates (as they are not prevented at the server level). Search this list's and the other lists' archives (http://lists.mysql.com/) for some pretty clever workarounds to help stabilize circular replication. During your searches, you will find links to several books that are online, I highly recommend you read ALL of those links. Thanks Shawn, I will look into it as well as clustering. I think clustering may solve my problems, I am not sure myself to be honest. We basically operate a large WAN, and due to time critical queries on the databases (such as Radius AAA), I want to find a way to roll out multiple databases at strategic locations on the network. The problem comes in that all these databases MUST handle INSERT, SELECT, UPDATE, and DELETE propperly, and migrate any changes through all the databases servers on the network (for now, it will just be 2, but I'm sure this will become more soon). I have to admit, I haven't been following the MySQL developments too much since 4.x came out, so yes, I do have some reading to do. Thanks again for your input. -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
first day of week/month
Hi, I know this might be a little silly, but can anyone give me a example on how to get the date of the first day of a week and month? -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
*very* strange...
Lo everyone,
I'm *baffled* completely I've never seen something like this before.
: I tried this exact query from PHP, Perl, as well as the MySQL
thingy... They ALL give the same result - it must therefore be my table
mysql SELECT VERSION();
++
| VERSION() |
++
| 4.0.14-log |
++
1 row in set (0.00 sec)
mysql SHOW TABLES LIKE 'Accounts';
++
| Tables_in_SAV001 (DSLAccounts) |
++
| Accounts |
++
1 row in set (0.00 sec)
mysql SHOW TABLE STATUS LIKE 'Accounts';
+-+++--++-+-
+--+---++---
--+-+++---+
| Name| Type | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time
| Update_time | Check_time | Create_options | Comment |
+-+++--++-+-
+--+---++---
--+-+++---+
| Accounts| MyISAM | Dynamic| 42 | 44 |1888 |
4294967295 | 7168 | 0 | 49 | 2004-03-20 08:53:01
| 2004-03-20 08:54:42 | NULL || Accounts |
+-+++--++-+-
+--+---++---
--+-+++---+
1 row in set (0.00 sec)
mysql SHOW KEYS FROM `Accounts`;
+-++--+--+-+
---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-++--+--+-+
---+-+--++--++-+
| Accounts| 0 | PRIMARY |1 | EntryID | A
| 42 | NULL | NULL | | BTREE | |
| Accounts| 0 | Username |1 | Username| A
| 42 | NULL | NULL | | BTREE | |
| Accounts| 1 | isActive |1 | isActive| A
|NULL | NULL | NULL | | BTREE | |
| Accounts| 1 | isCapped |1 | isCapped| A
|NULL | NULL | NULL | | BTREE | |
+-++--+--+-+
---+-+--++--++-+
4 rows in set (0.00 sec)
mysql SHOW FULL FIELDS FROM `Accounts`;
+--+--+--+-+-++-
+
| Field| Type | Null | Key | Default | Extra |
Privileges |
+--+--+--+-+-++-
+
| EntryID | smallint(6) unsigned | | PRI | NULL| auto_increment |
select,insert,update,references |
| Username | varchar(150) | | UNI | ||
select,insert,update,references |
| Password | varchar(150) | | | ||
select,insert,update,references |
| isActive | enum('y','n')| | MUL | y ||
select,insert,update,references |
| isCapped | enum('y','n')| | MUL | n ||
select,insert,update,references |
+--+--+--+-+-++-
+
5 rows in set (0.00 sec)
mysql SHOW FIELDS FROM `Accounts` FROM `SAV001`;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| EntryID | smallint(6) unsigned | | PRI | NULL| auto_increment |
| Username | varchar(150) | | UNI | ||
| Password | varchar(150) | | | ||
| isActive | enum('y','n')| | MUL | y ||
| isCapped | enum('y','n')| | MUL | n ||
+--+--+--+-+-++
5 rows in set (0.00 sec)
mysql INSERT INTO Accounts (Username, Password) VALUES
(Username='[EMAIL PROTECTED]', Password='password');
Query OK, 1 row affected (0.00 sec)
mysql SELECT * FROM Accounts WHERE Username='[EMAIL
Re: *very* strange...
insert into table (username, password) values ('username', 'password')
Skip out the username= and password= part. What you are ending up
doing here is that the values portion of the insert statement, these two
You may kick my ass... Royally.
Sorry, I feel like a phrick to say the least... That's what copy and pasting
code to save time does for you
--
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sub query format...
Lo all, What's the format for a sub query in MySQL?? I've tried the below with a few variants but I can't seem to get it sorted... SELECT songlist.ID AS ID, songlist.duration AS Duration, songlist.artist AS Artist, songlist.title AS Title, songlist.filename AS FileName FROM songlist LEFT JOIN categorylist ON songlist.ID=categorylist.songID WHERE categorylist.categoryID=(SELECT category.ID FROM category WHERE category.parentID='4' ORDER BY RAND() LIMIT 1) ORDER BY songlist.filename; This is a InnoDB Table, running on 4.0.14... Thanks, me -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to pass syslog data to a MySql Database?
Have a look on sourceforge. I think it's called mylogd or something similar. It's syslogd with sql support in it. Alternatively, a simple perl script will be able to parse the data and insert it manually into a db. -- me - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 1:42 AM Subject: How to pass syslog data to a MySql Database? Dear list: I`m running a syslogd into my Linux box for logging all event send from our Cisco routers, these event are logged in text format, for instance: Dec 3 15:01:36 sin-trj 8351: Dec 3 15:01:35.322 BO: %SNMP-3-AUTHFAIL:Authentication failure for SNMP req from host 10.1.10.12 Dec 3 15:01:38 sin-trj 8352: Dec 3 15:01:37.318 BO: %SNMP-3-AUTHFAIL:Authentication failure for SNMP req from host 10.1.10.12 Dec 3 15:01:40 sin-trj 8353: Dec 3 15:01:39.318 BO: %SNMP-3-AUTHFAIL:Authentication failure for SNMP req from host 10.1.10.12 Dec 3 15:01:42 sin-tnd 8286: Dec 3 15:01:41.328 BO: %SNMP-3-AUTHFAIL:Authentication failure for SNMP req from host 10.1.10.12 Dec 3 15:01:42 nas-cbb-gisuq 10124: Dec 3 15:01:41.818 BO:%PQUICC-1-LOSTCARR: Unit 0, lost carrier. Transceiver problem? Dec 3 15:01:44 sin-tnd 8287: Dec 3 15:01:43.324 BO: %SNMP-3-AUTHFAIL:Authentication failure for SNMP req from host 10.1.10.12 Dec 3 15:01:46 sin-tnd 8288: Dec 3 15:01:45.324 BO: %SNMP-3-AUTHFAIL:Authentication failure for SNMP req from host 10.1.10.12 Dec 3 15:01:48 sin-tnd 8289: Dec 3 15:01:47.324 BO: %SNMP-3-AUTHFAIL:Authentication failure for SNMP req from host 10.1.10.12 Dec 3 15:01:50 sin-tnd 8290: Dec 3 15:01:49.320 BO: %SNMP-3-AUTHFAIL:Authentication failure for SNMP req from host 10.1.10.12 Dec 3 15:01:52 172.30.1.49 8234: Dec 3 15:01:51.601 BO: %SNMP-3-AUTHFAIL:Authentication failure for SNMP req from host 10.1.10.12 Dec 3 15:01:54 172.30.1.49 8235: Dec 3 15:01:53.597 BO: %SNMP-3-AUTHFAIL:Authentication failure for SNMP req from host 10.1.10.12 Dec 3 15:01:56 172.30.1.49 8236: Dec 3 15:01:55.589 BO: %SNMP-3-AUTHFAIL:Authentication failure for SNMP req from host 10.1.10.12 Dec 3 15:01:58 172.30.1.49 8237: Dec 3 15:01:57.597 BO: %SNMP-3-AUTHFAIL:Authentication failure for SNMP req from host 10.1.10.12 Dec 3 15:02:00 172.30.1.49 8238: Dec 3 15:01:59.589 BO: %SNMP-3-AUTHFAIL:Authentication failure for SNMP req from host 10.1.10.12 I`d like to get some suggestion over how can I to load these text file to a MySql database automatically. Thank you for your help. EDWIN LIMACHI N. DATACOM - Instalaciones Phone. 591-2-2123978 Movil: 591-715-29967 Fax: 591-2-2123975 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is a good benchmark?
+--+ | BENCHMARK(100,ENCODE(hello,goodbye)) | +--+ |0 | +--+ 1 row in set (1.91 sec) PIII 850, with 1GB Ram. - Original Message - From: Jim Dickenson [EMAIL PROTECTED] To: MySQL Mail List [EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 5:51 PM Subject: Re: What is a good benchmark? On my 1Ghz Mac PowerBook with 1GB RAM using version 4.0.13 with OS 10.2.6 mysql SELECT BENCHMARK(100,ENCODE(hello,goodbye)); +--+ | BENCHMARK(100,ENCODE(hello,goodbye)) | +--+ |0 | +--+ 1 row in set (1.07 sec) -- Jim Dickenson mailto:[EMAIL PROTECTED] Computers for Marketing Corporation http://www.cfmc.com/ From: Hubbard, Dan [EMAIL PROTECTED] Date: Wed, 23 Jul 2003 08:39:01 -0700 To: [EMAIL PROTECTED] Subject: RE: What is a good benchmark? On my 2 * 2.8Ghz 2GB RAM, Redhat 8: mysql SELECT BENCHMARK(100,ENCODE(hello,goodbye)); +--+ | BENCHMARK(100,ENCODE(hello,goodbye)) | +--+ |0 | +--+ 1 row in set (0.66 sec) -Original Message- From: John Griffin [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 8:02 AM To: Bryan Koenig; [EMAIL PROTECTED] Subject: RE: What is a good benchmark? On my 1.8GHz p4 with 512Gig of RAM I get: mysql SELECT BENCHMARK(100,ENCODE(hello,goodbye)); +--+ | BENCHMARK(100,ENCODE(hello,goodbye)) | +--+ |0 | +--+ 1 row in set (0.91 sec) mysql John -Original Message- From: Bryan Koenig [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 10:46 AM To: [EMAIL PROTECTED] Subject: RE: What is a good benchmark? On my p4 2gig mysql SELECT BENCHMARK(100,ENCODE(hello,goodbye)); +--+ | BENCHMARK(100,ENCODE(hello,goodbye)) | +--+ |0 | +--+ 1 row in set (0.86 sec) -Original Message- From: Jake Johnson [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 8:34 AM To: Mysql Subject: What is a good benchmark? I ran this benchmark on my pIII 500 and was wondering what everyone else was getting? mysql SELECT BENCHMARK(100,ENCODE(hello,goodbye)); +--+ | BENCHMARK(100,ENCODE(hello,goodbye)) | +--+ |0 | +--+ 1 row in set (2.59 sec) Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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: MySQL 4.0.12 proftpd 1.2.8
Jun 02 08:07:17 mod_sql/4.10[1108]: message: 'Unknown column 'password' in 'field list'' mysql SELECT * FROM users WHERE userid = test - ; ++-+--+--+-+--+---+- ---+-+---+--+--+--+- +--+--++ | userid | uid | gid | passwd | description | disabled | shell | It should be obvious. Fix your spelling mistake. -- me -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unique select between two tables...
lo everyone, I have two tables Both have a DebtCode VARCHAR(6) in them, and both has UNIQUE Indexes on them. How can I select DebtCode as being unique in both tables? DebtCode in both tables, will be three alphabetical characters, followed by three numbers, like ABC001 ... ABC999, etc etc etc. Now, let's say I want to see the next available *UNIQUE* DebtCode I can assign, provided that I have ABC002 in table1 already. mysql SELECT COUNT(table1.DebtCode) + COUNT(table2.DebtCode) + 1 AS DebtID FROM table1, table1 WHERE table1.DebtCode LIKE 'ABC%' OR table2.DebtCode LIKE 'ABC%'; ++ | DebtID | ++ |245 | ++ 1 row in set (0.01 sec) Which, is the total number of ROWS on Table1 + Table2 + 1. Alas, not what I was expecting... I'm sure this should be possible, it's obviously my query that is lacking... If there's any one with ideas / a quick fix, please let me know!! -- me sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: unique select between two tables...
OK, this just doesn't make sense to me mysql SELECT COUNT(DISTINCT table1.DebtCode) AS table1, COUNT(DISTINCT table2.DebtCode) AS table2 FROM table1, table2 WHERE (table1.DebtCode LIKE 'CHR%' OR table2.DebtCode LIKE 'CHRI%'); +++ | table1 | table2 | +++ | 3 | 79 | +++ 1 row in set (0.02 sec) mysql SELECT COUNT(DISTINCT DebtCode) AS table2 FROM table2 WHERE DebtCode LIKE 'CHR%'; +--+ | table2 | +--+ |0 | +--+ 1 row in set (0.00 sec) -- me - Original Message - From: Chris Knipe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, January 12, 2003 5:19 PM Subject: unique select between two tables... lo everyone, I have two tables Both have a DebtCode VARCHAR(6) in them, and both has UNIQUE Indexes on them. How can I select DebtCode as being unique in both tables? DebtCode in both tables, will be three alphabetical characters, followed by three numbers, like ABC001 ... ABC999, etc etc etc. Now, let's say I want to see the next available *UNIQUE* DebtCode I can assign, provided that I have ABC002 in table1 already. mysql SELECT COUNT(table1.DebtCode) + COUNT(table2.DebtCode) + 1 AS DebtID FROM table1, table1 WHERE table1.DebtCode LIKE 'ABC%' OR table2.DebtCode LIKE 'ABC%'; ++ | DebtID | ++ |245 | ++ 1 row in set (0.01 sec) Which, is the total number of ROWS on Table1 + Table2 + 1. Alas, not what I was expecting... I'm sure this should be possible, it's obviously my query that is lacking... If there's any one with ideas / a quick fix, please let me know!! -- me sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: request
LOOL! This *IS* a joke right? ;) - Original Message - From: lateef ayinla [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, December 22, 2002 2:13 AM Subject: request Dear sir, i come across yourmanual [mySQL Reference] when browsing through the internet, i read it but i didn't understand it. i like to develop software of my own through writing software programs but don't know how to do it, i would like you to teach me how to write software programme. i want to use it to find people's data and information on the internet. this isbecause i've lost contact with most of my friends in usa. i want to use the software programme i'm asking you to teach me to find their whereabout. i'm a beginner in internet browsingi want you to teach me exclusively how to software use progmmam to find people information. iwill expecting your repy soon. thanks for anticipaion co-operation yours biola rufai -- __ Sign-up for your own FREE Personalized E-mail at Mail.com http://www.mail.com/?sr=signup Meet Singles http://corp.mail.com/lavalife - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
table upgrades
Lo all, If I have a database with various tables, and I want to upgrade the tables and the data in the tables, will it screw up the permissions assigned to users if I use DROP TABLE / CREATE TABLE (to recreate the changed tables), and then just populate them with the data again? I'm doing it this way because it's easier for us to dump the data from our development databases and reload them onto our production systems, but I'm worring now that I am going to loose my permissions (there is a TON of users / permissions in place)... Any feedback appreciated. -- me sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: this is WEAK!
where member='me ' is not the same as where member='me' ('me ' != 'me')
I've noticed this as well with just about any mysql version, on any
platform.
The tables / columns really isn't important here IMHO... From what I can
see, trailing spaces are always droped, which shouldn't be.
- Original Message -
From: Paul DuBois [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, November 27, 2002 11:36 PM
Subject: Re: this is WEAK!
At 15:06 -0600 11/27/02, [EMAIL PROTECTED] wrote:
member varchar(16) NOT NULL default '',
PRIMARY KEY (member),
Huh. That is weird, then. What does LENGTH(contact) return?
On Wed, Nov 27, 2002 at 03:28:15PM -0600, Paul DuBois wrote:
At 14:48 -0600 11/27/02, [EMAIL PROTECTED] wrote:
Why? (Note the extra space after me in the second select)
Hardly enough information to go on. Apparently you're using a
column type like BLOB or TEXT for which trailing spaces are not
removed?
Your MySQL connection id is 6021 to server version: 3.23.41-log
mysql select contact from options where member='me';
+-+
| contact |
+-+
| me |
+-+
1 row in set (0.00 sec)
mysql select contact from options where member='me ';
+-+
| contact |
+-+
| me |
+-+
1 row in set (0.00 sec)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is it possible to export data from access to MySQL?
MySQL-Front is quite good for this... - Original Message - From: Steve Jackson [EMAIL PROTECTED] To: MySQL General Mailing list [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 4:37 PM Subject: Is it possible to export data from access to MySQL? Is it possible to export data from MS Access into MySQL? If so how? I have a huge amount of data that will take weeks to input and I've convinced my boss to use MySQL rather than any other DB because of it's compatibility with PHP. However I (probably hastily) *assumed* that there would be a way to export the data. Any ideas? Steve Jackson Web Developer Viola Systems Ltd. http://www.violasystems.com [EMAIL PROTECTED] Mobile +358 50 343 5159 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Two queries, can I make it one?
Lo everyone,
I have a bit of a dilemma. I use MySQL for all my authentication (user
accounts etc, via ftp / mail / radius / http / etc), and currently am using
PureFTPD as my FTPD to allow my clients to upload web sites.
I use the following to authenticate users for their personal web space...
SELECT Password FROM UserAccounts RIGHT JOIN DNSZones ON
UserAccounts.ZoneID=DNSZones.ZoneID WHERE UserAccounts.Username=LEFT('\L',
(LOCATE('@', '\L') -1)) AND UserAccounts.StatusID 10 AND
DNSZones.ZoneName='mydomain.com' LIMIT 1;
\L is replaced by the username, where the usernames looks at
[EMAIL PROTECTED]
Now, this is all very nice and simple, but it is limited in the sense that I
can only allow that query on the one web site. In other words, I'll need to
run at least two different FTP servers (one for user web sites, and one for
other web sites).
In the other web sites, I'll be using something like this:
SELECT Password FROM WebAuth RIGHT JOIN DNSZones ON
WebAuth.ZoneID=DNSZones.ZoneID WHERE WebAuth.UserName=LEFT('\L',
(LOCATE('@', '\L') -1)) AND WebAuth.StatusID 10 LIMIT 1;
In this instance, the user account will be something like
[EMAIL PROTECTED]
Now, is there a way that I can do both those queries in one go? Basically,
I want to provide PureFTP with one SQL query, that will either return the
account details on the user account ([EMAIL PROTECTED]), OR, for a
virtual web site ([EMAIL PROTECTED])
If I can write this quick of what I have in mind, I'm looking for something
like
if (\L LIKE '[EMAIL PROTECTED]) {
the user is authenticating for their personal web space
} else {
the user is authenticating for a dedicated virtual host
}
Is this possible??? :/
--
me
sql,query
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Lost connections on Freebsd
Lo Michael, I digged a bit more, and while running under debug mode, MySQL doesnt crash, it does complain about a bad file descriptor on /etc/hosts.allow. If this is the cause of the failure, there might be a possibly bug in the code, because I really think it's arb that the entire MySQL needs to bail because it can't open a file... Here's the relavent messages from syslog, Aug 18 16:03:29 netsonic mysqld[15158]: warning: /etc/hosts.allow, line 23: can't get client address: Bad file descriptor Aug 18 16:03:30 netsonic mysqld[15158]: warning: /etc/hosts.allow, line 23: can't get client address: Bad file descriptor When I run without debug, I think this is what *may* be causing the crash, whilst when running under debug mode, MySQL obviously has some sort of protection so that it sends out only a warning instead of crashing Chris I've recompiled the master about three times now, and it still crashed. Chris Eventually, it came to such a extend that the moment I open port 3306 MySQL Chris would crash. I can just use telnet ip 3306, or open a netcat on port Chris 3306, and MySQL would die. Can you try to configure MYSQL with CFLAGS=-g and run the server under gdb. If we could get a full back trace where it fails, it could help us find out what's wrong. I tried. Now, when I recompiled it again without debug support, it doesn't crash for some reason. At least not for now The below log is issued to syslog at every connection however. Aug 18 17:03:53 netsonic mysqld[47947]: warning: can't get client address: Bad file descriptor I'll keep my eyes on it like a hawk. First crash I get, it's back to gdb we go... It looks to me as if it may be tcpwrappers that's busted however... :-( Chris So far, the only way I found to keep the FreeBSD Port up, was to recompile Chris it with debug support. I so far have 90 minutes uptime on the Master with Chris debug support compiled in, but it's needless to say not really the desired Chris result. How did you configure MySQL in this case ? Altered the Makefile in the ports, compiled / installed, and did a cvsup to sync the ports again... MySQL compiles per default with CFLAGS=-g from the ports, I just need had to recompile it again now, so that I could remove the debug support and let MySQL actually crash again :P -- me - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Lost connections on Freebsd
...or you have too many open files in the system.You reach the max. number of files which can be opened by the OS.Try to increase file-max from /proc/sys/fs. Highly doubtful. It's a brand new server installation, exactly the same as all my others, and it's not doing half as much as the others are The box runs mysql, apache, and exim... With less than 50 processes running at any given time... It's FreeBSD as well btw, /proc/fs/ doesn't exist :P -SNIP- DESCRIPTION The fstat utility identifies open files. A file is considered open by a process if it was explicitly opened, is the working directory, root directory, active executable text, or kernel trace file for that process. If no options are specified, fstat reports on all open files in the sys- tem. -SNIP- 749 open files reported... Far less than the maximum. -- me - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Lost connections on Freebsd
Fair enough, But then why is MySQL the *only* thing I am having problems with? MySQL's not the only application running on the box, it's not the only application opening / closing tcp sockets, its not the only thing opening / closing file sockets *shrugs*... Maybe I should just rm -rf / and reinstall. - Original Message - From: Gelu Gogancea [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, August 18, 2002 8:01 PM Subject: Re: Lost connections on Freebsd For sure Bad file descriptor error occur because your kernel it's not able to create handler for the new open file(don't forget...socket it's also a file ) which must be open. _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Chris Knipe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 18, 2002 8:14 PM Subject: Re: Lost connections on Freebsd ...or you have too many open files in the system.You reach the max. number of files which can be opened by the OS.Try to increase file-max from /proc/sys/fs. Highly doubtful. It's a brand new server installation, exactly the same as all my others, and it's not doing half as much as the others are The box runs mysql, apache, and exim... With less than 50 processes running at any given time... It's FreeBSD as well btw, /proc/fs/ doesn't exist :P -SNIP- DESCRIPTION The fstat utility identifies open files. A file is considered open by a process if it was explicitly opened, is the working directory, root directory, active executable text, or kernel trace file for that process. If no options are specified, fstat reports on all open files in the sys- tem. -SNIP- 749 open files reported... Far less than the maximum. -- me - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Another one on lost connections( using web interface)
PHP's more than likely just loosing persistent connections to the DB. It's a coding thing I think... You need to call mysql_connect() on every page where you need to use the database, even if you use mysql_pconnect(). If there's a mysql specific error, I think MySQL would most definitely write something to the error log... Tweaking your my.cnf and php.ini may also help you cause here... -- me - Original Message - From: Defryn, Guy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 18, 2002 11:25 PM Subject: Another one on lost connections( using web interface) I also have a problem with lost connections. I have small mysql database (version 3.23.49 on FreeBSD 4.5) I have created a website interface to enter and retrieve data. I used ASP to do this. Sometimes when I go to the website I get the error can't connect or lost connection to server. The mysql install has been done with default settings and There are never more than 2 people using the database at the same time. I also created a PHP interface to the database and I received the same error. The error does not occur all the time and when it occurs it goes away by refreshing the web browser. I never have any problems when working on the mysql server from the command line. Any ideas? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Lost connections on Freebsd
Seems to be getting more critical now as well. Aug 19 01:54:27 netsonic mysqld[48323]: warning: can't get client address: Bad file descriptor Aug 19 01:54:59 netsonic last message repeated 13 times Aug 19 01:54:59 netsonic last message repeated 2 times From what I can see, it's spitting out the errors now on every single query. Running under gdb also doesn't return anything (or I'm debugging it wrong?) MySQL's error log is empty - nothing logged -- me - Original Message - From: Chris Knipe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Victoria Reznichenko [EMAIL PROTECTED] Sent: Sunday, August 18, 2002 5:08 PM Subject: Re: Lost connections on Freebsd Lo Michael, I digged a bit more, and while running under debug mode, MySQL doesnt crash, it does complain about a bad file descriptor on /etc/hosts.allow. If this is the cause of the failure, there might be a possibly bug in the code, because I really think it's arb that the entire MySQL needs to bail because it can't open a file... Here's the relavent messages from syslog, Aug 18 16:03:29 netsonic mysqld[15158]: warning: /etc/hosts.allow, line 23: can't get client address: Bad file descriptor Aug 18 16:03:30 netsonic mysqld[15158]: warning: /etc/hosts.allow, line 23: can't get client address: Bad file descriptor When I run without debug, I think this is what *may* be causing the crash, whilst when running under debug mode, MySQL obviously has some sort of protection so that it sends out only a warning instead of crashing Chris I've recompiled the master about three times now, and it still crashed. Chris Eventually, it came to such a extend that the moment I open port 3306 MySQL Chris would crash. I can just use telnet ip 3306, or open a netcat on port Chris 3306, and MySQL would die. Can you try to configure MYSQL with CFLAGS=-g and run the server under gdb. If we could get a full back trace where it fails, it could help us find out what's wrong. I tried. Now, when I recompiled it again without debug support, it doesn't crash for some reason. At least not for now The below log is issued to syslog at every connection however. Aug 18 17:03:53 netsonic mysqld[47947]: warning: can't get client address: Bad file descriptor I'll keep my eyes on it like a hawk. First crash I get, it's back to gdb we go... It looks to me as if it may be tcpwrappers that's busted however... :-( Chris So far, the only way I found to keep the FreeBSD Port up, was to recompile Chris it with debug support. I so far have 90 minutes uptime on the Master with Chris debug support compiled in, but it's needless to say not really the desired Chris result. How did you configure MySQL in this case ? Altered the Makefile in the ports, compiled / installed, and did a cvsup to sync the ports again... MySQL compiles per default with CFLAGS=-g from the ports, I just need had to recompile it again now, so that I could remove the debug support and let MySQL actually crash again :P -- me - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Urgent: lost connections,
Lo Victoria FreeBSD 4.6-STABLE (Master), MySQL 3.23.51 Windows 2000 Professional (Slave):, MySQL 3.23.49-nt I've recompiled the master about three times now, and it still crashed. Eventually, it came to such a extend that the moment I open port 3306 MySQL would crash. I can just use telnet ip 3306, or open a netcat on port 3306, and MySQL would die. So far, the only way I found to keep the FreeBSD Port up, was to recompile it with debug support. I so far have 90 minutes uptime on the Master with debug support compiled in, but it's needless to say not really the desired result. I also don't think it's got anything to do with the replication. In my attempts to debug this, I've disabled (shut down), the slave mysql server, and the master would still crash the moment I open a tcp socket... It also doesn't seem to be a generic system library or something as the error log states, because I have *nothing* else on my box that is giving me any problems... If it where to be a shared lib used by many applications, surely more than just MySQL would have been dying because of the faulty / corrupted library. -- me - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, August 17, 2002 5:13 PM Subject: Re: Urgent: lost connections, Chris, Saturday, August 17, 2002, 2:04:40 AM, you wrote: CK Lo everyone, CK I've implemented replication on my MySQL Server... The master is mysql 3.23 CK (FreeBSD Ports), and my one and only slave is mysql 3.23 (Win32). I added CK the slave so that I can have a hardcopy / backup database in the event that CK I have a data loss on the master. 3.23 says almost nothing about MySQL server. What exact version do you run at master and slave? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I want to add up in a select Statement
sqlst = update sfproducts set projectdonations = projectdonations + testvalue WHERE prodID = ' sProdID ' ^^ $sqlst = update sfproducts set projectdonations = projectdomains + . $testvalue . WHERE prodID = ' . $sProdID . '; $sqlstr then looks like this: update sfproducts set projectdonations = projectdomains + 1 WHERE prodID = '1' if $testvalue and $sProdID had the value of 1 for example. -- me - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
where clause structures
Lo everyone, I seem to be having a problem with the structuring of my query - more specifically, with the WHERE clause... mysql SELECT ZoneName - FROM CompanyDNSZones - LEFT JOIN CompanyDetails ON CompanyDetails.CompanyID=CompanyDNSZones.CompanyID - WHERE CompanyDetails.CompanySuspended='0' AND - CompanyDNSZones.ZoneName='megalan.co.za' AND - CompanyDNSZones.ZoneServices LIKE '%HasMail%' OR - CompanyDNSZones.ZoneServices LIKE '%HasMailingList%'; +-+ | ZoneName| +-+ | megalan.co.za | | lists.megalan.co.za | +-+ 2 rows in set (0.01 sec) How do I get it to only return the item specified in the WHERE clause? I explictly tell MySQL in my where clause to only return results where CompanyDNSZones.ZoneName='megalan.co.za', so where does it fall out returning lists.megalan.co.za as well? -- me PS: Just for interest takes, can LEFT / RIGHT / a combination of LEFT RIGHT joins be used to cross-reference three or more tables, or is it limited to 2 tables only? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: where clause structures
EEK
Tables structures :P
mysql describe CompanyDetails;
+---+---+--+-+-+
+
| Field | Type | Null | Key | Default |
Extra |
+---+---+--+-+-+
+
| CompanyID | tinyint(4) unsigned | | PRI | NULL|
auto_increment |
| CompanyActive | enum('1','0') | | MUL | 0 |
|
| CompanySuspended | enum('0','1','2','3','4','5') | | MUL | 0 |
|
snip
mysql describe CompanyDNSZones;
+--+
---+--+-+-++
| Field| Type
| Null | Key | Default | Extra |
+--+
---+--+-+-++
| ZoneID | int(11)
| | PRI | NULL| auto_increment |
| CompanyID| smallint(6)
| | MUL | 0 ||
| ZoneName | varchar(250)
| | UNI | ||
| ZoneServices |
set('HasMail','HasMailingList','HasBackupMail','IsMegaDNS','IsSecondary') |
| MUL | ||
- Original Message -
From: Chris Knipe [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, August 17, 2002 11:44 PM
Subject: where clause structures
Lo everyone,
I seem to be having a problem with the structuring of my query - more
specifically, with the WHERE clause...
mysql SELECT ZoneName
- FROM CompanyDNSZones
- LEFT JOIN CompanyDetails ON
CompanyDetails.CompanyID=CompanyDNSZones.CompanyID
- WHERE CompanyDetails.CompanySuspended='0' AND
- CompanyDNSZones.ZoneName='megalan.co.za' AND
- CompanyDNSZones.ZoneServices LIKE '%HasMail%' OR
- CompanyDNSZones.ZoneServices LIKE '%HasMailingList%';
+-+
| ZoneName|
+-+
| megalan.co.za |
| lists.megalan.co.za |
+-+
2 rows in set (0.01 sec)
How do I get it to only return the item specified in the WHERE clause?
I explictly tell MySQL in my where clause to only return results where
CompanyDNSZones.ZoneName='megalan.co.za', so where does it fall out
returning lists.megalan.co.za as well?
--
me
PS: Just for interest takes, can LEFT / RIGHT / a combination of LEFT
RIGHT joins be used to cross-reference three or more tables, or is it
limited to 2 tables only?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Urgent: lost connections,
Lo everyone, I've implemented replication on my MySQL Server... The master is mysql 3.23 (FreeBSD Ports), and my one and only slave is mysql 3.23 (Win32). I added the slave so that I can have a hardcopy / backup database in the event that I have a data loss on the master. This is all very well, and working like a charm. However, mysql client, php, apache, anything that connects to the master database now all of a sudden randomly and intermittently loses their connection to the master MySQL Server, and the master server also crashes quite often. Taken from the error log: snip /usr/local/libexec/mysqld: ready for connections mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=12288 record_buffer=131072 sort_buffer=1048568 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 115211 K bytes of memory Hope that's ok, if not, decrease some variables in the equation /snip And the browser reports a standard PHP error: Lost connection to MySQL server during query Any advice would be gladly appreciated. --me sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: can't use the table. What happen, please!!!!!!!!!!
THe PHP's prob still on the test database. You can try mysql_select_db(databasename); before you're query. - Original Message - From: Cliff [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, August 10, 2002 2:07 AM Subject: Re: can't use the table. What happen, please!! That is a problem with a php script you are running, it is not passing the expected data as an argument. - Original Message - From: Sandra [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 09, 2002 4:41 PM Subject: can't use the table. What happen, please!! Hi all, I am getting the error when trying to connect server from my client: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in . I had my tables in test database, but created a new database and passed my tables to there. Since then I receive that message when I execute my .php I used GRANT to assing the user privileges. any answer? thanks, Sandra - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--bind-address
Lo all, Can MySQL bind to two or more IP addresses? i.e. --bind-address 127.0.0.1 --bind-address x.x.x.x --bind-address y.y.y.y ? Thanks, -- me query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Don't even know where to start on this one!!!
Lo all, I don't know if this can be reproduced, whether it's just general coding errors, or whether it's simply a Windows thing But I do my development on a NT 4.0 system, and I've been starting to notice rather weird things I use various languages (Perl, PHP, Delphi, C, etc), all of which access a single database (various tables) on my MySQL server (3.23.49-max). The database currently holds about 60 odd tables, with a total of over 2 million records. A additional table I use for testing, has one table with over 23 million records in it (255x255x255x255 records). My PHP code also use persistent connections, and I don't think this is the problem, but basically, how more queries I throw at MySQL how more my PC crawls down to a halt - even after the application with the queries disconnected from the database. I can understand that my system may take some sever stress when I work with a table with 23 million odd records in it, but why would my system still remain in a state of slowness? At any time, I normally don't have more than 15 concurrent connections to MySQL, and most of them are normally in a idle state in any case. I am 100% sure it is MySQL that is slowing my system down. When things start to become really irritating (i.e. I wait like over 5 seconds for my start menu to show up), I simply stop and restart the MySQL server, and all of a sudden, my system is fine again. I'll need to track the cause of this before I can use all my sites and data in any live environment first... But I don't have a *clue* where to start looking Can anyone perhaps give me some insight or advice on this rather weird behaviour?? -- me - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Don't even know where to start on this one
Lo all, I don't know if this can be reproduced, whether it's just general coding errors, or whether it's simply a Windows thing But I do my development on a NT 4.0 system, and I've been starting to notice rather weird things I use various languages (Perl, PHP, Delphi, C, etc), all of which access a single database (various tables) on my MySQL server (3.23.49-max). The database currently holds about 60 odd tables, with a total of over 2 million records. A additional table I use for testing, has one table with over 23 million records in it (255x255x255x255 records). My PHP code also use persistent connections, and I don't think this is the problem, but basically, how more queries I throw at MySQL how more my PC crawls down to a halt - even after the application with the queries disconnected from the database. I can understand that my system may take some sever stress when I work with a table with 23 million odd records in it, but why would my system still remain in a state of slowness? At any time, I normally don't have more than 15 concurrent connections to MySQL, and most of them are normally in a idle state in any case. I am 100% sure it is MySQL that is slowing my system down. When things start to become really irritating (i.e. I wait like over 5 seconds for my start menu to show up), I simply stop and restart the MySQL server, and all of a sudden, my system is fine again. I'll need to track the cause of this before I can use all my sites and data in any live environment first... But I don't have a *clue* where to start looking Can anyone perhaps give me some insight or advice on this rather weird behaviour?? -- me - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Don't even know where to start on this one
My PHP code also use persistent connections, and I don't think this is the problem, but basically, how more queries I throw at MySQL how more my PC crawls down to a halt - even after the application with the queries disconnected from the database. I can understand that my system may take Are you swapping? Allot The development box is only a 166MMX with 64MB ram unfortunately :-( NT's virtual memory is on average 220MB / 230MB with a peak load currently at 250MB... Perhaps it is just NT being funny with me... Hopefully I'll have the DBs on a decent FreeBSD box soon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....
Hiya again, Ok, based on all the info, feedback and help I got the last time, I was able to construct the following query: mysql SELECT weather_data.Temp, -weather_locations.Name - FROM weather_data - LEFT JOIN weather_data ON - weather_locations.MetarCode = weather_data.Site - WHERE weather_locations.MetarCode IS NULL; ERROR 1066: Not unique table/alias: 'weather_data' The tables look as follows: mysql DESCRIBE weather_data; +---+--+--+-+-+- ---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+- ---+ | EntryID | bigint(20) | | PRI | NULL| auto_increment | | RawData | varchar(250) | | MUL | | | | Date | datetime | | MUL | -00-00 00:00:00 | | | Site | varchar(4) | | MUL | | | | WindSpeed | tinyint(4) | YES | MUL | NULL| | | WindDirection | varchar(250) | YES | MUL | NULL| | | Temp | tinyint(4) | YES | MUL | NULL| | | DewPoint | tinyint(4) | YES | MUL | NULL| | +---+--+--+-+-+- ---+ 8 rows in set (0.29 sec) mysql DESCRIBE weather_locations; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | LocationID | tinyint(4) | | PRI | NULL| auto_increment | | MetarCode | varchar(4) | | UNI | || | Name | varchar(250) | | UNI | || | Province | varchar(250) | YES | MUL | NULL|| ++--+--+-+-++ 4 rows in set (0.03 sec) The basic background It's used to compile a history of weather information on approximately 75 locations in my country (ala .za). The weather_locations table, holds unique rows describing the various locations of the weather stations. In this table, MetarCode is a 4 character key identifing the weather station, while Name represents the physical location (such as the Town or airport). In my weather_data table, I save the actual weather data. I cannot use UNIQUE keys in this table, because I need to build up a history of the weather changes. In this table, Site represents the same weather station identifier as MetarCode in the weather_locations table. So, I'm trying to accomplish my JOIN on weather_locations.MetarCode and weather_data.Site. Basically, the data that I want to have returned: SELECT weather_locations.Name, weather_data.Temp FROM weather_data WHERE weather_data.Site='FACT'; Yes, this is a broken query, but I think it would give a good idea of what I want I want the weather site's name from the weather_locations table, plus whatever column I want from the weather_data table (such as temprature, WindSpeeds, WindDirection, etc). Bah... While typing this and playing arround some more, I got it working SELECT weather_data.Temp AS Temp, weather_locations.Name AS Name FROM weather_locations LEFT JOIN weather_data ON weather_locations.MetarCode = weather_data.Site WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR weather_data.Site='FAJS'; Thanks anyways :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....
Ok, It seems I spoke to soon here... SELECT weather_data.Temp AS Temp, weather_locations.Name AS Name FROM weather_locations LEFT JOIN weather_data ON weather_locations.MetarCode = weather_data.Site WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR weather_data.Site='FAJS'; That, does what I want... However, there's a catch now, that I became aware of after the duplicated data started filling the weather_data table. mysql SELECT * FROM weather_data WHERE Site='FACT'; +-++ -+--+---+---+--+--+ | EntryID | RawData| Date | Site | WindSpeed | WindDirection | Temp | DewPoint | +-++ -+--+---+---+--+--+ | 5 | FACT 161200Z 31008KT FEW030 17/11 Q1021 NOSIG | 2002-06-16 12:00:00 | FACT |19 | Northwest | 17 | 11 | | 36 | FACT 161300Z 31009KT FEW030 16/10 Q1021 NOSIG | 2002-06-16 13:00:00 | FACT |21 | Northwest | 16 | 10 | | 64 | FACT 161400Z 30007KT FEW030 16/11 Q1020 NOSIG | 2002-06-16 14:00:00 | FACT |16 | Northwest | 16 | 11 | | 73 | FACT 161500Z 30007KT FEW030 15/11 Q1020 NOSIG | 2002-06-16 15:00:00 | FACT |16 | Northwest | 15 | 11 | +-++ -+--+---+---+--+--+ 4 rows in set (0.13 sec) The problem now, is that the above query will return all four records for the FACT site as specified in my WHERE clause. Is there a way that I can add to that query, the funcionailty to: -- Order by reverse Date, and show only 1 record for every site specified by the WHERE Clause (i.e., only get the newest data for each site I specified). OR -- Use a SELECT DISTINCT so that I can have distinct SITE values in the JOIN query... Thanks... - Original Message - From: Peter Normann [EMAIL PROTECTED] To: 'Chris Knipe' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, June 16, 2002 5:44 PM Subject: RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one Sorry, I didn't read the mail through before replying... :-/ -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 15:43 To: [EMAIL PROTECTED] Subject: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one Hiya again, Ok, based on all the info, feedback and help I got the last time, I was able to construct the following query: mysql SELECT weather_data.Temp, -weather_locations.Name - FROM weather_data - LEFT JOIN weather_data ON - weather_locations.MetarCode = weather_data.Site - WHERE weather_locations.MetarCode IS NULL; ERROR 1066: Not unique table/alias: 'weather_data' The tables look as follows: mysql DESCRIBE weather_data; +---+--+--+-+-+- +---+--+--+-+-+ ---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+- +---+--+--+-+-+ ---+ | EntryID | bigint(20) | | PRI | NULL| auto_increment | | RawData | varchar(250) | | MUL | | | | Date | datetime | | MUL | -00-00 00:00:00 | | | Site | varchar(4) | | MUL | | | | WindSpeed | tinyint(4) | YES | MUL | NULL| | | WindDirection | varchar(250) | YES | MUL | NULL| | | Temp | tinyint(4) | YES | MUL | NULL| | | DewPoint | tinyint(4) | YES | MUL | NULL| | +---+--+--+-+-+- +---+--+--+-+-+ ---+ 8 rows in set (0.29 sec) mysql DESCRIBE weather_locations; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | LocationID | tinyint(4) | | PRI | NULL| auto_increment | | MetarCode | varchar(4) | | UNI | || | Name | varchar(250) | | UNI | || | Province | varchar(250) | YES | MUL | NULL|| ++--+--+-+-++ 4 rows in set (0.03 sec) The basic background It's used to compile a history
Re: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....
mysql SELECT weather_data.Site, weather_data.Temp, MAX(weather_data.Date) AS - Temp, weather_locations.Name AS Name - FROM weather_locations - LEFT JOIN weather_data ON weather_locations.MetarCode = - weather_data.Site - WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR - weather_data.Site='FAJS'; ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP colum ns is illegal if there is no GROUP BY clause mysql Hmmm, I wonder what the manual can tell me about group by... I'll have a look and see what I can come up with. If you know what's missing, I wont mind finding out :-) Kind Regards, Chris Knipe MegaLAN Corporate Networking Services Tel: +27 21 854 7064 Cell: +27 72 434 7582 - Original Message - From: Peter Normann [EMAIL PROTECTED] To: 'Chris Knipe' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, June 16, 2002 7:34 PM Subject: RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one Does SELECT weather_data.Site, weather_data.Temp, MAX(weather_data.Date) AS Temp, weather_locations.Name AS Name FROM weather_locations LEFT JOIN weather_data ON weather_locations.MetarCode = weather_data.Site WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR weather_data.Site='FAJS'; Work? You can add LIMIT 1 to the query... Peter Normann -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 19:00 To: [EMAIL PROTECTED] Subject: Re: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one Ok, It seems I spoke to soon here... SELECT weather_data.Temp AS Temp, weather_locations.Name AS Name FROM weather_locations LEFT JOIN weather_data ON weather_locations.MetarCode = weather_data.Site WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR weather_data.Site='FAJS'; That, does what I want... However, there's a catch now, that I became aware of after the duplicated data started filling the weather_data table. mysql SELECT * FROM weather_data WHERE Site='FACT'; +-++ -+--+---+---+--+--+ | EntryID | RawData| Date | Site | WindSpeed | WindDirection | Temp | DewPoint | +-++ -+--+---+---+--+--+ | 5 | FACT 161200Z 31008KT FEW030 17/11 Q1021 NOSIG | 2002-06-16 12:00:00 | FACT |19 | Northwest | 17 | 11 | | 36 | FACT 161300Z 31009KT FEW030 16/10 Q1021 NOSIG | 2002-06-16 13:00:00 | FACT |21 | Northwest | 16 | 10 | | 64 | FACT 161400Z 30007KT FEW030 16/11 Q1020 NOSIG | 2002-06-16 14:00:00 | FACT |16 | Northwest | 16 | 11 | | 73 | FACT 161500Z 30007KT FEW030 15/11 Q1020 NOSIG | 2002-06-16 15:00:00 | FACT |16 | Northwest | 15 | 11 | +-++ -+--+---+---+--+--+ 4 rows in set (0.13 sec) The problem now, is that the above query will return all four records for the FACT site as specified in my WHERE clause. Is there a way that I can add to that query, the funcionailty to: -- Order by reverse Date, and show only 1 record for every site specified by the WHERE Clause (i.e., only get the newest data for each site I specified). OR -- Use a SELECT DISTINCT so that I can have distinct SITE values in the JOIN query... Thanks... - Original Message - From: Peter Normann [EMAIL PROTECTED] To: 'Chris Knipe' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, June 16, 2002 5:44 PM Subject: RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one Sorry, I didn't read the mail through before replying... :-/ -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 15:43 To: [EMAIL PROTECTED] Subject: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one Hiya again, Ok, based on all the info, feedback and help I got the last time, I was able to construct the following query: mysql SELECT weather_data.Temp, -weather_locations.Name - FROM weather_data - LEFT JOIN weather_data ON - weather_locations.MetarCode = weather_data.Site - WHERE weather_locations.MetarCode IS NULL; ERROR 1066: Not unique table/alias: 'weather_data' The tables look as follows: mysql DESCRIBE weather_data
Re: table is read only when DB copied from Win to UNIX
Don't copy the physical files. Use mysqldump and mysqlimport instead. Kind Regards, Chris Knipe MegaLAN Corporate Networking Services Tel: +27 21 854 7064 Cell: +27 72 434 7582 - Original Message - From: Francisco Reinaldo [EMAIL PROTECTED] To: Tonu Samuel [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Thursday, June 13, 2002 12:14 AM Subject: Re: table is read only when DB copied from Win to UNIX Thanks for the tip but I have added to the table db in mysql database full permission for everybody to that database. I don't think that is a permission thing because when I add a new table, I can insert data into that new table. Besides it does not happen when I copy the database from UNIX to Windows. I'll keep trying though but if somebody already knows the solution, please let me know. Thanks. --- Tonu Samuel [EMAIL PROTECTED] wrote: On Wed, 12 Jun 2002, Francisco Reinaldo wrote: Fine, I can see the database, show tables works, selects works. The whole nine yards? Nope! I cannot insert any values in that database. I get the following error: Table xxx is read only. I have checked the OS permission for that directory and I have given full persmission (write, read and execute). Also I have checked MySQL permissions, adding full permission for all users to that database but nothing. It looks like the problem is at the table level because when I manually add a new table to the MySQL UNIX, the inserts work. The only reason this can happen is wring permission bits no tables. Internally there is no way to make MyISAM (I assume you use them) read-only. Look as which user mysqld runs and make tables writable to that user. Tonu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 45 seconds
I have multiple tables on a 166MMX with 64MB ram, all containing more than 250,000 records each Mine takes less than 5 seconds I think there's something wrong at your side :-) Kind Regards, Chris Knipe MegaLAN Corporate Networking Services Tel: +27 21 854 7064 Cell: +27 72 434 7582 - Original Message - From: Elsad YUSIFLI [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 10, 2002 4:10 PM Subject: 45 seconds i have a table and 51000 records in it. it has got an index on HOST_NAME field. next query lasts 45 seconds to execute... is it normal ? server is PIII 500 double cpu SELECT b.ip as ip, b.country , b.hostname , a.HOST_NAME as host, COUNT(a.HOST_NAME) as number FROM new_raw_log as a, dns as b WHERE (a.HOST_NAME regexp rtrim(b.ip)+'%') AND a.GID=44 AND a.SID=12 GROUP BY a.HOST_NAME ORDER BY number DESC LIMIT 0,20 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Storing PGP keys
What would be the best way to store PGP keys for optimal queries in MySQL (using search / indexes on the columns) text / blob?? Also, for a PHP Perl based series of applications, would it be better to store / validate / compare these values in a database, or should it be better managing them via the pgp binaries? I'm intending to use PGP keys to establish authentication (trusts) from incoming email messages to register items in a database... Kind Regards, Chris Knipe MegaLAN Corporate Networking Services Tel: +27 21 854 7064 Cell: +27 72 434 7582 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Unique Indexes across multiple columns
Hi again, Not to long ago, I had a query regarding the best way to store IP addresses in a DB, and make sure that they are unique. It was pointed out to me that I could use four smallint columns instead of a varchar to store these numbers, and just implement a UNIQUE index across all four columns to make sure the combination of the four columns, would never be in duplicate. It seems, the UNIQUE index however still insist on having unique values for all of the four columns... The DB looks like this (the four smallint columns): NS1_IP1 smallint(5) NS1_IP2 smallint(5) NS1_IP3 smallint(5) NS1_IP4 smallint(5) SQL-query : ALTER TABLE `domains` ADD UNIQUE `NS1_Unique` (`NS1_IP1`,`NS1_IP2`,`NS1_IP3`,`NS1_IP4`) MySQL said: Duplicate entry '127-30-127-1' for key 2 Which, tells me that I can have the following: a, b, c, d - works b, c, d, e - works a, b, c, d - fails a, a, b, b - fails -- This should however not fail... (the combination is unique??) Any idea on how I can get this working?? -- me - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: newbie
INSERT INTO table (column) VALUES ('path');
I'd recommend you goto www.mysql.com and download the manual - then read it.
Kind Regards,
Chris Knipe
MegaLAN Corporate Networking Services
Tel: +27 21 854 7064
Cell: +27 72 434 7582
- Original Message -
From: Silmara Cristina Basso [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, June 07, 2002 9:44 PM
Subject: newbie
I'm newbie MySql
I have one field path-char-250.
How can i insert the following value ('c:\temp\calc.exe')?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: insert
Escape the \ like so: 'c:\\temp\\calc.exe' It's all in the manuals. Kind Regards, Chris Knipe MegaLAN Corporate Networking Services Tel: +27 21 854 7064 Cell: +27 72 434 7582 - Original Message - From: Silmara Cristina Basso To: [EMAIL PROTECTED] Sent: Friday, June 07, 2002 10:19 PM Subject: insert I think you don't undestand... My problem is when i run the select the result is the folling c:temcalc.exe); not c:\temp\calc.exe query, sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I KNOW one of you LISTers has experienced this
Only users of the Administrators group on NT can start system services by default. Have a look in your user manager for domains, policies, user rights Kind Regards, Chris Knipe MegaLAN Corporate Networking Services Tel: +27 21 854 7064 Cell: +27 72 434 7582 - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 3:28 PM Subject: RE: I KNOW one of you LISTers has experienced this [snip] I've asked this question once before with no response (and have asked on other lists), I'll give it one more try before giving in to semi-Gates domination (my last holdout is the Apache server I'm running): Upon installing MySQL as a service, why will it not start for all users? Only my admin account allows the MySQL service to start. I cannot start the service manually or automatically from any regular (power user) account. MySQL was installed as a service using mysqld-nt --install with no problems. I've come across a vague reference in the manual for a manual installation of MySQL as a service if problems occur, but have no idea if that is the course of action to take since I don't really know why there is a problem. Putting MySQL in the startup items makes no difference. HELP! [/snip] Kirk, Are you starting MySQL on individual platforms? Are you starting it on a server and then attempting to connect to it from another user's computer? If the latter is the case you do not need to start MySQL, you just need to connect to it on the server it resides on. I think the lack of answer is due to the vaguery of the question. Maybe we can get going the right direction here. Thanks! Jay mysql, query, sql It's hip to snip! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems starting MySQL as a Service for all users
FOR MY ADMIN ACCOUNT C:\netstat -a -p tcp TCPdynamic:3306 dynamic:0 LISTENING FOR THE REGULAR USER ACCOUNT C:\netstat -a -p tcp TCPdynamic:3306 dynamic:0 LISTENING ^ The port is available under both accounts - the server is running. I was under the impression that once something is installed as a service, it is available to the system for all users. This is not the case here. In fact, once I log off admin and onto the other accounts I just proove you wrong... Again, when I was on my admin account I installed the MySQL service from the command prompt using: mysqld-nt --install and the result was service has been installed (paraphrasing). I have uninstalled MySQL completely and reinstalled only to run into the same problem. I'm using the 3.23.49 binary package. In your service manager (in the control panel), is the service listed there, and is it started? I personally, don't use the GUI mysqladmin utility. mysql-nt --install installs the service, then I use service manager just like I would for any other service (like IIS) on NT to be started. When you're as a default non admin user account, did you ever try mysql to connect to the server, or even better, telnet to port 3306 ? If so, what errors did you receive back from mysql or the telnet? If you didn't maybe it's time that you do. I doubt this is a service related problem, but I may be wrong... Using the right usernames / passwords / host addresses will also help your cause when you have trouble connecting to the mysql server. -Original Message- From: Charles Quesenberry [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 11:25 AM To: Kirk Brannan Babb Cc: [EMAIL PROTECTED] Subject: Re: Problems starting MySQL as a Service for all users Kirk, I am somewhat confused by your question. I must misunderstand what it is that you are trying to ask, because what it appears that you are asking makes no sense. Once MySQL was installed as a service, why does it matter which account starts it? Are you not the admin on the box? Are you trying to start multiple instances of the MySQL service? Logging off of the admin account will not stop a service on a Windows 2000 box. Even though you log off the admin account, MySQL should still be running. Or, when you say started do you mean connect to the running server? What is the output of the following command on the server running MySQL? netstat -a -p tcp Respectfully, Charles Q. At 08:23 PM 6/5/2002 -0500, you wrote: I've installed MySQL as a service using mysqld-nt --install on W2K. No problem there, says service successfully installed. BUT, when I try to log off the admin account and on to the regular power user account MySQL will not start automagically and cannot be started manually. Pop back over to the admin account and MySQL acts like nothing was ever wrong. Is this an issue that can be overcome by manually installing MySQL as a service instead of using the above? And how do you accomplish that? If you know or have heard of this before please help me out; I'd really like to have MySQL running for all users (that would enable the Apache-based site I'm running on the same box to access the database at all times). Thanks! Kirk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sub-queries
Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub-queries
Fair enough (and also what I thought) Does anyone have any idea how I can implement the below in a similar fashion then? I have a list of items, and a list of groups. I want to retrieve all the items from a table that is not in a specific group... - Original Message - From: Kiss Dániel [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 9:47 PM Subject: Re: sub-queries I'm sorry to disappoint you, but subqueries are NOT supported int any MySQL version, yet. You can read the MySQL manual about it. You can find there when and how it will be implemented. Bye Daniel At 21:39 2002.06.06. +0200, you wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
