Re: Using AVG
Try this: SELECT AVG((5+8+10)/3) AS rate FROM an_existing_table_name; - Original Message - From: Mike Blezien [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Thursday, May 19, 2005 9:24 AM Subject: Using AVG Hello, when using the AVG function like this: SELECT AVG((5+8+10)/3) AS rate; it returns NULL?? the AVG can be used to do a literal math calculation ?? TIA -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum number of user variables
- Original Message - From: Neculai Macarie [EMAIL PROTECTED] To: Mysql mysql@lists.mysql.com Sent: Thursday, May 12, 2005 1:20 PM Subject: Re: Maximum number of user variables Not that I'm aware of. What type of conversions are you doing that you need 30,000 use vars? An easy solution would be to try it and find out :) I need to move multiple linked entries (in around 12 tables) from one running server to another. I'm using auto_increment's all over the place and I have the following problem: I need to move (no replication possible) the information in those 12 tables to an identical functioning system (same software, same db, but other server) and I need to preserve the relations between the tables. The problem that I have is that the systems evolve independently and I can have the same id for 2 different entries on the system (e.g. on the first system I can have the id 10 for User1, but on second system id 10 would be for another user). Perhaps after you dump the structure and data into sql files, you could remove temporarily the extra attribut auto increment to those columns. Then start inserting. After that add the auto increment attribut again. OR use bulkcopy(?) if im not mistaken, or any other methods to copy as it is to new dbservers (compress it first). Donny. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL user in Indonesia
My company use it :) PT. Tempo Inti Media Tbk For experience sharing, contact me in private. I believe there're alot of company here using MySQL. Just take a look around. Me fail English? That's unpossible ###___Archon___### - Original Message - From: Leo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 1:24 PM Subject: MySQL user in Indonesia Hi All, im sorry if this mail a bit out of topic =) im currently developing a new system based on MySQL for the company i work on. and i have to report how wide and famous is mysql had been used, especially in indonesia. i need the fact that i can show to my boss, why i choose mysql.. well i have a lot of it, but he demand some example, which company in indonesia had successfully implemented mysql in their core bussiness so please, let me know.. i already run a search at google, but i have trouble determining what kwyword should i use if there is among you that work in a company here in indonesia, please, would you kindly share your experience thanks in advance -- Regards Leonardus Setiabudi IT Project Coordinator PT Bina San Prima, www.binasanprima.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication stopped without reason
Assume you leave the default connect-retry to 60 secs. Is the replication still stopped in the next 60 secs after that? Me fail English? That's unpossible ###___Archon___### - Original Message - From: trashMan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 24, 2003 3:38 PM Subject: Replication stopped without reason Hi, I've a big problem!!! The replication start and work correctly but after 10 minutes slave don't update anymore. I must do slave stop - slave start for restarting replication. In the log there are not error. The slave is connected to internet with a isdn-router while the master is connected with a cdn. What can i do for stabilize the replication?? Massimiliano -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL cluster capable?
Im thinking of using OpenMOSIX for that kind of clustering (load balancing and shared filesystem/storage). But it doesn't support application that uses shared memory. That's including Oracle, MySQL, etc... But the shared storage feature gonna be useful for our hundred gigs of images :) Me fail English? That's unpossible ###___Archon___### - Original Message - From: Jordi Sánchez López [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 7:58 PM Subject: Is MySQL cluster capable? Can two mysql processes (running in different machines) access and work with the same database files in a shared storage scenario? Would there be any problem? Thanks :) -- 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: Faster reindexing
Yep... im using fulltext indexes That is, multiple indexing would be the best. Nut now i add more memory and do some fine-tuning on my.cnf, perhaps the result won't be the same. Me fail English? That's unpossible ###___Archon___### - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, July 18, 2003 11:42 AM Subject: Re: Faster reindexing umm say i reindex after a reimport i usually drop all the indexes first , then add indexes again one by one , seems to be quicker for me especially for fulltext indexes , doing it at once tends to be slower, must crunch the cpu a bit dont know On Wed, Jul 09, 2003 at 11:51:01PM -0500, Paul DuBois wrote: At 14:14 +0700 7/9/03, Dominicus Donny wrote: Sorry, my mistake. It's reindexing then. Once i should add 2 key indexes on a huge table. But instead of alter the table in 1 single query, I build each index 1 by 1. Generally, it is faster to build all your indexes with a single ALTER TABLE statement than to build them one by one with separate ALTER TABLE statements. Single. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 8 days, processed 247,399,591 queries (350/sec. avg) -- 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: Problem When Mysql runs on a Machine with multiple NIC.
Specify other account(s)? [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] etc... Me fail English? That's unpossible ###___Archon___### - Original Message - From: P Srinivasulu [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, July 10, 2003 2:40 PM Subject: Re: Problem When Mysql runs on a Machine with multiple NIC. Paul DuBois [EMAIL PROTECTED] 7/10/2003 10:22:27 AM At 2:30 -0600 7/9/03, P Srinivasulu wrote: When Mysql runs on machine with multiple NIC's. Mysql server takes the IP Address that we specify in the configuration file. This IP Address may or may not be a primary IP Address of that machine. So we specify secondary IP Address for the Mysql server. It listens in that IP Address. When Mysql client from the same machine connects to that server, Mysql Client is treated,as if the connection is coming from the different host. i.e. The Rights of the Mysql client will not be same as the rights of [EMAIL PROTECTED] instead it will be equal to the rights of the [EMAIL PROTECTED] Is this behaviour correct or a bug. This is correct behavior. MySQL doesn't know that localhost and PrimaryIPAddress are the same host. How to make the mysql client to use the secondary IP Address to connect to the Mysql server?. Thanks, Srinivasulu. -- 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: Faster reindexing
Sorry, my mistake. It's reindexing then. Once i should add 2 key indexes on a huge table. But instead of alter the table in 1 single query, I build each index 1 by 1. And the responses of the slaves also great, too. Anyway, im using the standard my-medium.cnf setup. The huge table/db also located on another drive/partition. Me fail English? That's unpossible ###___Archon___### - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Dominicus Donny [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 11:22 AM Subject: Re: Faster reindexing At 11:23 +0700 7/9/03, Dominicus Donny wrote: Try analyze your table(s). What information will this yield to make indexing faster? Me fail English? That's unpossible ###___Archon___### - Original Message - From: electroteque [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED]; Florian Weimer [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 10:23 AM Subject: RE: Faster reindexing when reimporting or reinserting or whatever from a huge db i usually drop all the indexes reimport then create them again much quicker -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 1:09 PM To: Florian Weimer; [EMAIL PROTECTED] Subject: Re: Faster reindexing At 9:39 +0200 7/7/03, Florian Weimer wrote: I've got a table with 100 million rows and need some indexes on it (one row is 126 bytes). I'm currently using MyISAM and the indexing proceeds at an astonishingly low rate: about 200 MB per hour. This is rate is far too low; if we had to recover the database for some reason, we'd have to wait for days. The table looks like this: CREATE TABLE flows ( versionCHAR NOT NULL, router CHAR(15) NOT NULL, src_ip CHAR(15) NOT NULL, dst_ip CHAR(15) NOT NULL, protocol TINYINT UNSIGNED NOT NULL, src_port MEDIUMINT UNSIGNED NOT NULL, dst_port MEDIUMINT UNSIGNED NOT NULL, packetsINTEGER UNSIGNED NOT NULL, bytes INTEGER UNSIGNED NOT NULL, src_if MEDIUMINT UNSIGNED NOT NULL, dst_if MEDIUMINT UNSIGNED NOT NULL, src_as MEDIUMINT UNSIGNED NOT NULL, dst_as MEDIUMINT UNSIGNED NOT NULL, src_netCHAR(1) NOT NULL, dst_netCHAR(1) NOT NULL, direction CHAR(1) NOT NULL, class CHAR(1) NOT NULL, start_time CHAR(24), end_time CHAR(24) ); Indexes are created using this statement: mysql ALTER TABLE flows - ADD INDEX dst_ip (dst_ip, src_ip), - ADD INDEX dst_port (dst_port, start_time), - ADD INDEX src_ip (src_ip, start_time), - ADD INDEX time (start_time); In theory, we could represent the columns router, src_ip, dst_ip, start_time, end_time using integers of the appropriate size, but this would make ad-hoc queries harder to type (and porting our applications would be even more difficult). Perhaps, but as a test, you might add a couple of extra columns to the table, then populate them like this after loading the table: UPDATE flows SET int_src_ip = INET_ATON(src_ip), int_dst_ip = INET_ATON(dst_ip); Then try creating the indexes using int_src_ip and int_dst_ip rather than src_ip and dst_ip. If it's significantly faster, you may want to reconsider whether it might not be worth using INET_ATON(X) in your queries rather than X. Should I switch to another table type? It's easy enough to convert the table to, e.g., InnoDB and then create the indexes, so an empirical test should not be difficult. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication stops for no reason...
- Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 7:58 PM Subject: Re: Replication stops for no reason... Jeff McKeon [EMAIL PROTECTED] wrote: I've seen one other reference to this exact situation but it didn't have a resolution. What I've done is created a script that runs every 10 minutes via cron and checks a value from both databases and if it's not equal then it issues a slave stop and slave start command on the slave machine. Our DB's don't have a firewall or anything between them. Does anyone know if this is a known bug in 3.23.41 that is fixed in later versions? 3.23.41 is about 2 years old and upgrade is recommended in any case. Yep, i upgraded 2 dbservers from 3.23.x to 4.x with no problems... And 'helped' (more precisely, encourage and guide) another admin to upgrade 3 of their dbservers to 4.x As soon as all have the same version, we could set a nicely done daisy chain replication within our servers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Faster reindexing
Try analyze your table(s). Me fail English? That's unpossible ###___Archon___### - Original Message - From: electroteque [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED]; Florian Weimer [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 10:23 AM Subject: RE: Faster reindexing when reimporting or reinserting or whatever from a huge db i usually drop all the indexes reimport then create them again much quicker -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 1:09 PM To: Florian Weimer; [EMAIL PROTECTED] Subject: Re: Faster reindexing At 9:39 +0200 7/7/03, Florian Weimer wrote: I've got a table with 100 million rows and need some indexes on it (one row is 126 bytes). I'm currently using MyISAM and the indexing proceeds at an astonishingly low rate: about 200 MB per hour. This is rate is far too low; if we had to recover the database for some reason, we'd have to wait for days. The table looks like this: CREATE TABLE flows ( versionCHAR NOT NULL, router CHAR(15) NOT NULL, src_ip CHAR(15) NOT NULL, dst_ip CHAR(15) NOT NULL, protocol TINYINT UNSIGNED NOT NULL, src_port MEDIUMINT UNSIGNED NOT NULL, dst_port MEDIUMINT UNSIGNED NOT NULL, packetsINTEGER UNSIGNED NOT NULL, bytes INTEGER UNSIGNED NOT NULL, src_if MEDIUMINT UNSIGNED NOT NULL, dst_if MEDIUMINT UNSIGNED NOT NULL, src_as MEDIUMINT UNSIGNED NOT NULL, dst_as MEDIUMINT UNSIGNED NOT NULL, src_netCHAR(1) NOT NULL, dst_netCHAR(1) NOT NULL, direction CHAR(1) NOT NULL, class CHAR(1) NOT NULL, start_time CHAR(24), end_time CHAR(24) ); Indexes are created using this statement: mysql ALTER TABLE flows - ADD INDEX dst_ip (dst_ip, src_ip), - ADD INDEX dst_port (dst_port, start_time), - ADD INDEX src_ip (src_ip, start_time), - ADD INDEX time (start_time); In theory, we could represent the columns router, src_ip, dst_ip, start_time, end_time using integers of the appropriate size, but this would make ad-hoc queries harder to type (and porting our applications would be even more difficult). Perhaps, but as a test, you might add a couple of extra columns to the table, then populate them like this after loading the table: UPDATE flows SET int_src_ip = INET_ATON(src_ip), int_dst_ip = INET_ATON(dst_ip); Then try creating the indexes using int_src_ip and int_dst_ip rather than src_ip and dst_ip. If it's significantly faster, you may want to reconsider whether it might not be worth using INET_ATON(X) in your queries rather than X. Should I switch to another table type? It's easy enough to convert the table to, e.g., InnoDB and then create the indexes, so an empirical test should not be difficult. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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: specific records
SELECT * FROM tablename LIMIT 5, 6 Me fail English? That's unpossible ###___Archon___### - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Maciej Bobrowski [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 4:47 PM Subject: Re: specific records SELECT * FROM tablename where column5 AND column12; Best regards Nils Valentin Tokyo/Japan 2003 7 2 18:26Maciej Bobrowski : Hi, Let's say I have 1000 records in a 'table'. I want to select rows from 6 to 11. How can I do this? Regards, Maciej Bobrowski -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- 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: URGENT problem with mysql processes
If it is the replication problem...:: Check the replication account on the master server: ...[EMAIL PROTECTED] identified by ... ...[EMAIL PROTECTED] identified by ... ...[EMAIL PROTECTED] identified by ... ... ...[EMAIL PROTECTED] identified by ... or perhaps simply set to ...replication_account@% identified by ... make sure the account has access to the specified db.tables, too. Me fail English? That's unpossible ###___Archon___### - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Keith Bussey [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, June 27, 2003 1:30 PM Subject: Re: URGENT problem with mysql processes Hi Keith, from your last e-mail I understand now finally that we are talking about replication here. Would have been a short cut to mention it in your first e-mail. The failed login attempts couldn't be from your slaves trying to login, but you havent set them up on the master yet ? Just a guess, so. 2003 6 27 09:05Keith Bussey : | 106 | unauthenticated user | 192.168.1.59:1925 | NULL | Connect | NULL | | login NULL | | 115 | unauthenticated user | 192.168.1.53:2041 | NULL | Connect | NULL | | login NULL | | 118 | unauthenticated user | 192.168.1.56:4172 | NULL | Connect | NULL | | login NULL | | 119 | unauthenticated user | 192.168.1.56:4173 | NULL | Connect | NULL | | login NULL Best regards Nils Valentin Tokyo/Japan -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- 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: SELECT single record
LIMIT 1 Me fail English? That's unpossible ###___Archon___### - Original Message - From: [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Friday, June 27, 2003 12:08 PM Subject: SELECT single record Thanks everybody for all you great help! Ok, this statement will select all of my records nicely: SELECT firstName, lastName, deptPOS, workPH, homePH, location, model, make, propID, addressIP, OS FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID How do I write it to retrieve only a single record? (I think I'll be a bit dangerous after I know this! ;-) Thanks, Ted -- 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]
