Any issues migrating Solaris - Linux?
Dear list, My company wants to migrate MySQL 5.0.18 from SPARC/Solaris 8 to Intel/RedHat Enterprise Linux 4 update 4. Are there any incompatibilities or snags to be expected (expect from the endian issue, which will be solved by exporting/importing the data)? Thank you for your time, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
after upgrade to 4.1 - error in multi delete
Hi there, I have recently upgraded from mysql 4.0 to 4.1.x Now I am facing an error while issuing this delete command: # delete report images in db $stmt= DELETE FROM $DB.$T21 USING $DB.$T21 AS rp, $DB.$T13 AS r WHERE r.ID = rp.recommendations_id AND r.user_recommending = '$user_id' ; Error: 1109 Unknown table 'the_table_name' in MULTI DELETE Has there something changed in the syntax? Thank you for any help, Merlin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
spool data/log into a file.
Hi All, I have written a script to connect to mysql and all the command results is spooled into a file. But when an error occurs in mysql, it stops at that point and does not proceed to the next command, also these error command is not written to the output file. Can you please tell me what i need to do, for the script to continue even if there is an error and that these errors should be spooled into a file. I have used -f(force) option while connecting to mysql db, it continues even if there is any error in the script, but it does not write error command into the output file. Can you please help how can i spool all the output into a file. Below is the simple script i am using, ttt.txt is the file which will contain the all the output of the script. mysql -u -p -f eof | tee ttt.txt use dev1; select count(*) from rep_abc; select count(*) from rep_fact; exit eof
RE: select first letters
I must have misunderstood, then. I thought the Greek characters were more than one byte. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: nikos [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 25, 2007 12:15 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: select first letters Finaly I use ord(). Thank you. Jerry Schwartz wrote: The multi-byte extension doesn't seem to include one, but it appears that somebody put one together and posted it in the notes on chr(). http://us2.php.net/manual/en/function.chr.php#69082 Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- -- *From:* nikos [mailto:[EMAIL PROTECTED] *Sent:* Wednesday, April 25, 2007 11:41 AM *To:* Jerry Schwartz; mysql@lists.mysql.com *Subject:* Re: select first letters Jerry do you know if there is a php command that returns ascci number of a letter? I'll want to use chr() command because I want to transfer via link the letter to next page but greek characters transformed to something like %CE%9C. Thank you Jerry Schwartz wrote: It would depend upon the collating sequence for the field. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: nikos [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 25, 2007 10:07 AM To: Dus(an Pavlica; mysql@lists.mysql.com Subject: Re: select first letters Seems that work in v. 4.1.21 but not in 5.0.27 Thank you Dusan Dus(an Pavlica wrote: I'm not sure, but I think that greek characters are sorted after English chars so try this: SELECT DISTINCT LEFT(title,1) FROM odigos_details WHERE LEFT(title,1) 'z' ORDER BY title HTH, Dusan nikos napsal(a): Hello list. I want to select discinct the first letters of titles in a UTF8 table but only the greek ones. There are both english and greek charakter titles. How can I exclude the english from selection? My table is: CREATE TABLE `odigos_details` ( `id` int(11) NOT NULL auto_increment, `cat` tinyint(3) default NULL, `territory` tinyint(3) default NULL, `title` varchar(150) default NULL, `story` text, `link` varchar(100) default NULL, `address` varchar(150) default NULL, `tel` varchar(50) default NULL, `fax` varchar(11) default NULL, `photo` varchar(100) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 and I run: SELECT DISTINCT LEFT(title,1) FROM odigos_details ORDER BY title witch is working fine. Thank you. Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Any issues migrating Solaris - Linux?
Martjin, I've run various versions of MySQL from 3.2 through 5.1 on various platforms (Mac OS X, FreeBSD, RedHat/Fedora Linux, Windows, Solaris), exporting and importing as needed, and haven't encountered any problems. I've even transferred the MyISAM tables between machines in a few cases, rather than wait on the export/import process. Works great across platforms. One thing to ensure is that any firewall or security settings you employ allow traffic on MySQL's TCP port (3306 by default), assuming you are using network connections. Dan On 4/26/07, Martijn van den Burg [EMAIL PROTECTED] wrote: Dear list, My company wants to migrate MySQL 5.0.18 from SPARC/Solaris 8 to Intel/RedHat Enterprise Linux 4 update 4. Are there any incompatibilities or snags to be expected (expect from the endian issue, which will be solved by exporting/importing the data)? Thank you for your time, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spool data/log into a file.
most likely you just need to redirect STDERR to tee as well so it's not a mysql problem: mysql -u -p -f eof 21 | tee ttt.txt ... assuming it's Bourne or ksh, don't remember what's csh for 21 Regards, Michael -Original Message- From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thu, 26 Apr 2007 8:15 AM Subject: spool data/log into a file. Hi All, I have written a script to connect to mysql and all the command results is spooled into a file. But when an error occurs in mysql, it stops at that point and does not proceed to the next command, also these error command is not written to the output file. Can you please tell me what i need to do, for the script to continue even if there is an error and that these errors should be spooled into a file. I have used -f(force) option while connecting to mysql db, it continues even if there is any error in the script, but it does not write error command into the output file. Can you please help how can i spool all the output into a file. Below is the simple script i am using, ttt.txt is the file which will contain the all the output of the script. mysql -u -p -f eof | tee ttt.txt use dev1; select count(*) from rep_abc; select count(*) from rep_fact; exit eof Check Out the new free AIM(R) Mail -- 2 GB of storage and industry-leading spam and email virus protection. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_upgrade not running mysql_check: Access denied for user 'root'@'localhost'
Hi, When I try mysql_upgrade I get a connection problem, $ mysql_upgrade -p Enter password: /usr/bin/mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect Error executing '/usr/bin/mysqlcheck --check-upgrade --all-databases --auto-repair --user=root' but I am able to run mysql_check directly without a problem, $ mysqlcheck --check-upgrade --all-databases --auto-repair -u root -p Enter password: main.provider OK main.request_dispatch OK mysql.columns_priv OK mysql.db OK mysql.func OK mysql.help_categoryOK mysql.help_keyword OK mysql.help_relationOK mysql.help_topic OK mysql.host OK mysql.proc OK mysql.procs_priv OK mysql.tables_priv OK mysql.time_zoneOK mysql.time_zone_leap_secondOK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_typeOK mysql.user OK Does anyone know why this would be? This is my version information: $ mysqladmin version mysqladmin Ver 8.41 Distrib 5.0.32, for pc-linux-gnu on i486 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.0.32-Debian_7etch1-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Cheers, -Janek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update question
On Wednesday 25 April 2007 23:14, you wrote: try this: update table1, table2 set table1.value = table2.value where table1.id = table2.id Thanks for the replies... It was late evening when I tried to figure out how to do this. Today I found the answer myself, which is exactly as described above. Lesson learned: Get a good night sleep and then try to figure out how to do things... ;-) -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what kind of indices to set up
I was a little to quick with the send button. Can you do a query like this: (I know that the * syntax is not correct, but is there something equivalent to it? SELECT from cars WHERE make=5 AND model=* AND body_color=7 AND tire_type = * AND hub_caps_type = 1 If you could perform a query like the one above, would MySQL still use the multi-column index that I set up? -James On Apr 24, 2007, at 4:47 PM, James Tu wrote: What do you guys think of this approach... Always query on all 5 columns...and then create a multicolumn index using all 5 columns? -James On Apr 24, 2007, at 11:42 AM, James Tu wrote: Thanks Mike. So let's say I have in index on each of the columns below...and I do a search for make=5 model=2 body_color=7 tire_type=11 hub_caps_type=1 MySQL will only pick one of them right? Let's say it picks make_index. Then what does it do? Does it scan the entire set of results returned by make=5 to match the other criteria? -James On Apr 23, 2007, at 5:49 PM, mos wrote: James, A lot depends on how many rows you are searching on. If you only have a couple thousand rows, then a table scan will still be fast. If you are searching more rows, say more than 10,000, then using the proper index will speed things up. Using a compound index is only useful if the user is searching on at least the first field of the index. For now, your best bet is to build an index on each of the commonly searched columns and MySQL will choose the best index for the search. Mike At 11:16 AM 4/23/2007, James Tu wrote: I have a table which will be searched via some of the fields in the column. An example of the list of searcheable columns: make model body_color tire_type hub_caps_type The thing is that people might do a search using one or many of the fields as criteria. For example someone might search for : body_color = 1 AND tire_type = 11 or just model = 22 I read that MySQL only uses one index when it performs a query. I did an EXPLAIN and it appears that only one of the indices is used. What is the proper way to setup indices in this case? Shoud I add an Index for each of these fields OR create a multicolumn index using all of these fields? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Examples of commodity servers for MySQL
What exactly are examples of “commodity servers”? I know what characteristics they have, but could somebody point out several examples that’d be used in a MySQL scale-out? e.g. What do you use? Also, would these servers be 1U or other configurations that take up very little room in a rack? Would something like http://www.pcmag.com/article2/0,1759,1645657,00.asp be considered a commodity server? With a price tag of $6K, I’d guess no. -- View this message in context: http://www.nabble.com/Examples-of-commodity-servers-for-MySQL-tf3652386.html#a10202903 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Building MySQL Under low memory conditions (CFLAGS not working?)
Hi, I'm trying to build MySQL 4.1.22 on a VPS where it looks like our available memory could be as low as 32MB, and compilation is erroring out partway through with a virtual memory exhausted: cannot allocate memory message. I tried invoking configure with the --with-low-memory flag, and then recompiling, and was still bumping up against the same issue. So I went looking for any other info I could find out on the net, and did find some specific info about flags you can feed to gcc to keep memory usage low here: http://hostingfu.com/article/compiling-with-gcc-on-low-memory-vps However, near as I can tell, when I add the flags --param ggc-min-expand=0 and --param ggc-min-heapsize=8192 to CFLAGS in the Makefile in the top-level source directory, nothing changes. The output of make seems to indicate it's using the same flags as before I changed the makefile. In fact, it looks like I can remove CFLAGS entirely or change it to something like CFLAGS = --hovercraft-full-of-eels and nothing changes. Any obvious things I'm missing? Ideas for other things I could try? Thanks, Weston -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what kind of indices to set up
James Tu wrote: I was a little to quick with the send button. Can you do a query like this: (I know that the * syntax is not correct, but is there something equivalent to it? SELECT from cars WHERE make=5 AND model=* AND body_color=7 AND tire_type = * AND hub_caps_type = 1 If you could perform a query like the one above, would MySQL still use the multi-column index that I set up? -James Select * from cars WHERE make=5 AND model LIKE '%' AND body_color=7 AND tyre_type LIKE '%' AND hub_caps_type = 1 If your multi-column index starts with model or tyre_type, then no. The index can be used down to, but not including the first column that is compared against '%'. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
My bin.log directory is getting full
Hello All. My bin.log directory is getting full with bin.log files. We are running out of space. What can I do in the short term? Is there a command that I can issue that will get rid of old bin log files not needed? This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what kind of indices to set up
Gerald L. Clark wrote: James Tu wrote: I was a little to quick with the send button. Can you do a query like this: (I know that the * syntax is not correct, but is there something equivalent to it? SELECT from cars WHERE make=5 AND model=* AND body_color=7 AND tire_type = * AND hub_caps_type = 1 If you could perform a query like the one above, would MySQL still use the multi-column index that I set up? -James Select * from cars WHERE make=5 AND model LIKE '%' AND body_color=7 AND tyre_type LIKE '%' AND hub_caps_type = 1 If your multi-column index starts with model or tyre_type, then no. The index can be used down to, but not including the first column that is compared against '%'. I think, but am not sure, that MySQL query plan optimizer will in fact remove the '%' condition entirely, if the column is defined as NOT NULL. It is a tautology after all. Test with EXPLAIN EXTENDED followed by SHOW WARNINGS to see the optimized query on MySQL 5 and up. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: My bin.log directory is getting full
Hello, Brown, Charles wrote: Hello All. My bin.log directory is getting full with bin.log files. We are running out of space. What can I do in the short term? Is there a command that I can issue that will get rid of old bin log files not needed? In the short term, see the manual page for PURGE MASTER LOGS. In the long term, write a cron job. innotop (http://sourceforge.net/projects/innotop) also has a new feature, unreleased because I just wrote it a few hours ago, which will help you figure out which binlogs can be purged safely with a single keystroke :-) Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Interesting SQL Query - Total and Grouped Counts together?
I'm wondering if any of you can assist with an interesing SQL query. I have a single table within a database, the relevant fields of which are defined as: CREATE TABLE tableA ( domain text, mime text ); Where domain is a domain, such as: google.com emeraldcity.oohisntitgreen.com teddybears.com An example of rows might be: google.com, text/html google.com, image/gif google.com, image/jpeg google.com, text/html teddybears.com, text/html teddybears.com, image/png google.com, text/html google.com, image/png ... mime is defined as having entries such as: text/html image/png image/jpg image/gif application/x-tar What I am after doing with this table is writing an SQL query which produces a count of all domains where the mime is equal to text/html and next to that, a total count for that domain where the mime type is image/* -- so for example, I might expect the returned resultset to look like: Domain domaincount Mimecountimages - google.com120 12 emeraldcity. 200 40 teddybears.com 11 2 So far, we've considered and tried using a same-table join, various group-by and rollup ideas, but am unable to come up with anything which will produce the above in ONE row for each domain. Any advice and assistance would be great! -- Atom Database A Custom Database Designed for Your Business [EMAIL PROTECTED] http://atomdatabase.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bin logs and mysql 4
Running mysql 4, just poked into data and see I have gigs and gigs of hostname-bin.xxx log files. How does one maintain these, can someone point me to relevant data on what to do about drive space being lost to these? thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interesting SQL Query - Total and Grouped Counts together?
Hi, Imran Chaudhry wrote: I'm wondering if any of you can assist with an interesing SQL query. I have a single table within a database, the relevant fields of which are defined as: CREATE TABLE tableA ( domain text, mime text ); Where domain is a domain, such as: google.com emeraldcity.oohisntitgreen.com teddybears.com An example of rows might be: google.com, text/html google.com, image/gif google.com, image/jpeg google.com, text/html teddybears.com, text/html teddybears.com, image/png google.com, text/html google.com, image/png ... mime is defined as having entries such as: text/html image/png image/jpg image/gif application/x-tar What I am after doing with this table is writing an SQL query which produces a count of all domains where the mime is equal to text/html and next to that, a total count for that domain where the mime type is image/* -- so for example, I might expect the returned resultset to look like: Domain domaincount Mimecountimages - google.com120 12 emeraldcity. 200 40 teddybears.com 11 2 So far, we've considered and tried using a same-table join, various group-by and rollup ideas, but am unable to come up with anything which will produce the above in ONE row for each domain. Any advice and assistance would be great! Try IF or CASE expressions: SELECT foo, count(*), sum(case when foo = 'bar' then 1 else 0 end) FROM tbl GROUP BY foo Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
Hi, Scott Haneda wrote: Running mysql 4, just poked into data and see I have gigs and gigs of hostname-bin.xxx log files. How does one maintain these, can someone point me to relevant data on what to do about drive space being lost to these? thanks See attached message I just sent to another user a bit ago :-) Baron ---BeginMessage--- Hello, Brown, Charles wrote: Hello All. My bin.log directory is getting full with bin.log files. We are running out of space. What can I do in the short term? Is there a command that I can issue that will get rid of old bin log files not needed? In the short term, see the manual page for PURGE MASTER LOGS. In the long term, write a cron job. innotop (http://sourceforge.net/projects/innotop) also has a new feature, unreleased because I just wrote it a few hours ago, which will help you figure out which binlogs can be purged safely with a single keystroke :-) Baron ---End Message--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
In the short term, see the manual page for PURGE MASTER LOGS. In the long term, write a cron job. innotop (http://sourceforge.net/projects/innotop) also has a new feature, unreleased because I just wrote it a few hours ago, which will help you figure out which binlogs can be purged safely with a single keystroke :-) I don't quite get this, if SHOW SLAVE STATUS shows empty result set, and I am just running one server, not a master + slave setup at all, its really rather simple. So, how would I ever know what logs I can safely delete or purge? Do I really need to use mysql to purge them or can I just `rm` them? I guess I could push this to cron? PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); My question is, what are these logs really good for, I assume restoration, and from what I read, but how do I know how far back I should keep? thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
Hi Scott,
Scott Haneda wrote:
In the short term, see the manual page for PURGE MASTER LOGS. In the
long term, write a cron job.
innotop (http://sourceforge.net/projects/innotop) also has a new
feature, unreleased because I just wrote it a few hours ago, which will
help you figure out which binlogs can be purged safely with a single
keystroke :-)
I don't quite get this, if SHOW SLAVE STATUS shows empty result set, and I
am just running one server, not a master + slave setup at all, its really
rather simple.
So, how would I ever know what logs I can safely delete or purge?
Do I really need to use mysql to purge them or can I just `rm` them?
I guess I could push this to cron?
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);
My question is, what are these logs really good for, I assume restoration,
and from what I read, but how do I know how far back I should keep?
thanks
Yes -- sorry for being so general. You can use the binlogs for a)
replication b) replaying changes since your last backup so you get
point-in-time recovery. If you have no replication slaves, just delete
everything older than your latest backup. You can just use 'rm'. If
you use PURGE MASTER LOGS BEFORE, it's a bit easier than cron because
you can do it across all platforms easily. On UNIX of course, you'd use
something like
find /var/lib/mysql/data -name *.bin -mtime +7 -exec rm {} \
(My find syntax is guaranteed to be wrong there... don't run that as I
typed it).
But if you do it via SQL, you don't have to mess with this.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Examples of commodity servers for MySQL
The smaller Dells, like the 28xx and 18xx series, are exactly what I consider commodity hardware. Other companies (Sun, HP, Gateway, IBM, Apple, others) make comparable equipment at comparable prices. Whether you need to spend the money on redundant power supplies and a redundant drive setup is up to you - will depend on how well you and the system you are building will tolerate machines going belly up due to hardware failure. Dan On 4/26/07, lightbulb432 [EMAIL PROTECTED] wrote: What exactly are examples of commodity servers? I know what characteristics they have, but could somebody point out several examples that'd be used in a MySQL scale-out? e.g. What do you use? Also, would these servers be 1U or other configurations that take up very little room in a rack? Would something like http://www.pcmag.com/article2/0,1759,1645657,00.aspbe considered a commodity server? With a price tag of $6K, I'd guess no. -- View this message in context: http://www.nabble.com/Examples-of-commodity-servers-for-MySQL-tf3652386.html#a10202903 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
Yes -- sorry for being so general. You can use the binlogs for a)
replication b) replaying changes since your last backup so you get
point-in-time recovery. If you have no replication slaves, just delete
everything older than your latest backup. You can just use 'rm'. If
you use PURGE MASTER LOGS BEFORE, it's a bit easier than cron because
you can do it across all platforms easily. On UNIX of course, you'd use
something like
find /var/lib/mysql/data -name *.bin -mtime +7 -exec rm {} \
(My find syntax is guaranteed to be wrong there... don't run that as I
typed it).
But if you do it via SQL, you don't have to mess with this.
Thanks!
So, I take it since I do not have a slave at all, I could safely just
disable this feature altogether?
If I do not need point in time recovery, and the once every 12 hour dump I
do across all databases is ok with me, I suppose I can just disable said
feature? Heck, some of these boogers are a GB each :-)
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
an't connect to local MySQL -- Help me!
I am getting this message from mysql. We had our binlog directory full. What is the resolution? Error 2002 (HY000) Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: MySQL patches from Google
Hi I read the Google blog post regarding these patches. They admit using MySQL for some internal data storage needs but not in the general search system. Here is the link to the blog post http://google-code-updates.blogspot.com/2007/04/google-releases-patches-that -enhance.html Mike O'Krongli Acorg Inc - Original Message - From: David T. Ashley [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: April 25, 2007 10:20 PM Subject: Re: FW: MySQL patches from Google On 4/25/07, mos [EMAIL PROTECTED] wrote: At 02:36 PM 4/25/2007, you wrote: On 4/25/07, Daevid Vincent [EMAIL PROTECTED] wrote: A co-worker sent this to me, thought I'd pass it along here. We do tons of failover/replication and would be eager to see mySQL implment the Google patches in the stock distribution. If anyone needs mission critical, scaleable, and failover clusters, it's Google -- so I have every confidence their patches are solid and worthy of inclusion... This isn't surprising for Google. They've done the same thing to Linux. I don't know much about Google's infrastructure these days, but several years ago they had a server farm of about 2,000 identical x86 Linux machines serving out search requests. Each machine had a local hard disk containing the most recent copy of the search database. So you're saying they had a MySQL database on the same machine as the webserver? Or maybe 1 webserver machine and one MySQL machine? I would have thought a single MySQL database could handle the requests from 25-50 webservers easily. Trying to maintain 2000 copies of the same database requires a lot of disk writes. I know Google today is rumored to have over 100,000 web servers and it would be impossible to have that many databases in sync at all times. When I read the article some years ago, I got the impression that it was a custom database solution (i.e. nothing to do with MySQL). If you think about it, for a read-only database where the design was known in advance, nearly anybody on this list could write a database solution in 'C' that would outperform MySQL (generality always has a cost). Additionally, if you think about it, if you have some time to crunch on the data and the data set doesn't change until the next data set is released, you can probably optimize it in ways that are unavailable to MySQL because of the high INSERT cost. There might even be enough time to tune a hash function that won't collide much on the data set involved so that the query cost becomes O(1) rather than O(log N). You can't do that in real time on an INSERT. It may take days to crunch data in that way. My understanding was the Google's search servers had custom software operating on a custom database format. My understanding was also that each search server had a full copy of the database (i.e. no additional network traffic involved in providing search results). As far as keeping 100,000 servers in sync, my guess would be that most of the data is distilled for search by other machines and then it is rolled out automatically in a way to keep just a small fraction of the search servers offline at any one time. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.463 / Virus Database: 269.6.1/777 - Release Date: 2007-04-26 3:23 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interesting SQL Query - Total and Grouped Counts together?
On Thu, April 26, 2007 18:38, Baron Schwartz wrote: Hi, Imran Chaudhry wrote: I'm wondering if any of you can assist with an interesing SQL query. I have a single table within a database, the relevant fields of Try IF or CASE expressions: SELECT foo, count(*), sum(case when foo = 'bar' then 1 else 0 end) FROM tbl GROUP BY foo Baron Cool, it's actually working :) I've been looking for something like that before. SELECT * FROM tablea t order by domain,mime; domainmime -- 'google.com', 'image/gif' 'google.com', 'image/jpeg' 'google.com', 'image/png' 'google.com', 'text/html' 'google.com', 'text/html' 'google.com', 'text/html' 'teddybears.com', 'image/png' 'teddybears.com', 'text/html' SELECT domain, count(*) `all`, sum(case when mime = 'text/html' then 1 else 0 end) html, sum(case when mime like 'image/%' then 1 else 0 end) image FROM tablea GROUP BY domain; domain all html image - 'google.com', 6, 3, 3 'teddybears.com', 2, 1, 1 -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
Issuing a 'reset master' will purge all of the logs as well. I wouldn't just rm them, as they are being tracked in the index file. If you aren't running a slave, then these files are only good for data recovery purposes. Say a DBA goes crazy and deletes all of the databases mid-day (too much stress). You could restore the previous nights backup and run these bin logs up to the point of the delete command - a little bit of editing would be needed to do this, but you get the idea. For this to work smoothly, you need to reset the logs after every backup. If your using mysqldump, just add the --delete-master-logs option. If you want to turn the logs off, remove log-bin and log-bin-index from the conf file. Regards, Scott Tanner On Thu, 2007-04-26 at 09:47 -0700, Scott Haneda wrote: In the short term, see the manual page for PURGE MASTER LOGS. In the long term, write a cron job. innotop (http://sourceforge.net/projects/innotop) also has a new feature, unreleased because I just wrote it a few hours ago, which will help you figure out which binlogs can be purged s keystroke :-)--delete-master-logs I don't quite get this, if SHOW SLAVE STATUS shows empty result set, and I am just running one server, not a master + slave setup at all, its really rather simple. So, how would I ever know what logs I can safely delete or purge? Do I really need to use mysql to purge them or can I just `rm` them? I guess I could push this to cron? PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); My question is, what are these logs really good for, I assume restoration, and from what I read, but how do I know how far back I should keep? thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
So, I take it since I do not have a slave at all, I could safely just disable this feature altogether? If I do not need point in time recovery, and the once every 12 hour dump I do across all databases is ok with me, I suppose I can just disable said feature? Heck, some of these boogers are a GB each :-) Sounds reasonable to me! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: MySQL patches from Google
On 4/26/07, Mike OK [EMAIL PROTECTED] wrote: I read the Google blog post regarding these patches. They admit using MySQL for some internal data storage needs but not in the general search system. Still, that leaves many other applications. Groups, gmail, reader, news et al... -- Kevin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: My bin.log directory is getting full
# cat /etc/cron.mysql/20-purgemasterlogs #!/bin/sh /usr/bin/mysql --defaults-file=/root/.my.cnf -e 'show master logs; purge master logs before date_sub(now(), interval 30 day); show master logs;' /var/log/20-purgemasterlogs.log 21 This purges anything older than 30 days. HTH, Tim -Original Message- From: Brown, Charles [mailto:[EMAIL PROTECTED] Sent: Thursday, April 26, 2007 12:03 PM To: mysql@lists.mysql.com Subject: My bin.log directory is getting full Hello All. My bin.log directory is getting full with bin.log files. We are running out of space. What can I do in the short term? Is there a command that I can issue that will get rid of old bin log files not needed? This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- 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 e-zine
Hey everyone, I have been considering putting together a e-zine for MySQL as my way to give back to the community. I was curious as to what type of interest there would be in this. I am thinking something quarterly to start with and probably 15 - 20 pages. Nothing huge. Topics would vary from everything like standard DBA stuff like backups, replication, system architecture and such to stuff for the developers out there..php, perl, python whatever. My background is more of a DBA so I would definitely need some input/articles for the developer side of things. It is just funny that in this day and age there is no magazines specifically for MySQL. I even saw a php magazine at the bookstore the other day! I can do the layout in QuarkXpress and generate pdfs that I can host on my website. Any thoughts? Any desires to contribute? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL e-zine
Hey everyone, I have been considering putting together a e-zine for MySQL as my way to give back to the community. I was curious as to what type of interest there would be in this. I am thinking something quarterly to start with and probably 15 - 20 pages. Nothing huge. Topics would vary from everything like standard DBA stuff like backups, replication, system architecture and such to stuff for the developers out there..php, perl, python whatever. My background is more of a DBA so I would definitely need some input/articles for the developer side of things. It is just funny that in this day and age there is no magazines specifically for MySQL. I even saw a php magazine at the bookstore the other day! I can do the layout in QuarkXpress and generate pdfs that I can host on my website. Any thoughts? Any desires to contribute? Anything on the topic of actual queries I would skip, those are narrow and finite questions, usually specific to a certain application, and quickly answered on the list. I would cover what is more mysterious to most, which is the underbelly of mysql. Today there was good dialogue on the bin logs, that I think is stuff that helps people. Demystifying all the .cnf options, how to optimize for load, test the load etc. Steer clear of high end features like replication and the like, things that generally, if you are worried about them, your app is doing well enough you should be around knowledgeable people already. For me at least, it would be more about internals, that the step by step of how to do a join. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: MySQL patches from Google
This sounds a lot like what I'm attempting. I tried a proprietary database and got around 30k queries/second, compared to MySQL's of only 1-1.5k queries /second. I'm torn between using the Windows proprietary database (still has some minor buggy parts) on each webserver or going with MySQL 5.x and perhaps using clusters to improve scalability. I'll need to do more testing before deciding. The thing that has me leaning towards MySQL is if I stick with Windows, I'll have to use Vista eventually because XP will no longer be sold. And that's not something I'd wish on anyone. :) Mike At 08:20 PM 4/25/2007, David T. Ashley wrote: On 4/25/07, mos [EMAIL PROTECTED] wrote: At 02:36 PM 4/25/2007, you wrote: On 4/25/07, Daevid Vincent [EMAIL PROTECTED] wrote: A co-worker sent this to me, thought I'd pass it along here. We do tons of failover/replication and would be eager to see mySQL implment the Google patches in the stock distribution. If anyone needs mission critical, scaleable, and failover clusters, it's Google -- so I have every confidence their patches are solid and worthy of inclusion... This isn't surprising for Google. They've done the same thing to Linux. I don't know much about Google's infrastructure these days, but several years ago they had a server farm of about 2,000 identical x86 Linux machines serving out search requests. Each machine had a local hard disk containing the most recent copy of the search database. So you're saying they had a MySQL database on the same machine as the webserver? Or maybe 1 webserver machine and one MySQL machine? I would have thought a single MySQL database could handle the requests from 25-50 webservers easily. Trying to maintain 2000 copies of the same database requires a lot of disk writes. I know Google today is rumored to have over 100,000 web servers and it would be impossible to have that many databases in sync at all times. When I read the article some years ago, I got the impression that it was a custom database solution (i.e. nothing to do with MySQL). If you think about it, for a read-only database where the design was known in advance, nearly anybody on this list could write a database solution in 'C' that would outperform MySQL (generality always has a cost). Additionally, if you think about it, if you have some time to crunch on the data and the data set doesn't change until the next data set is released, you can probably optimize it in ways that are unavailable to MySQL because of the high INSERT cost. There might even be enough time to tune a hash function that won't collide much on the data set involved so that the query cost becomes O(1) rather than O(log N). You can't do that in real time on an INSERT. It may take days to crunch data in that way. My understanding was the Google's search servers had custom software operating on a custom database format. My understanding was also that each search server had a full copy of the database (i.e. no additional network traffic involved in providing search results). As far as keeping 100,000 servers in sync, my guess would be that most of the data is distilled for search by other machines and then it is rolled out automatically in a way to keep just a small fraction of the search servers offline at any one time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: MySQL patches from Google
Kevin Spencer wrote: On 4/26/07, Mike OK [EMAIL PROTECTED] wrote: I read the Google blog post regarding these patches. They admit using MySQL for some internal data storage needs but not in the general search system. Still, that leaves many other applications. Groups, gmail, reader, news et al... -- Kevin. My understanding from some posting is that they do specifically use it for gmail which makes a lot of sense. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL e-zine
Hi Scott, This would be a great start to have an e-zine mag for Mysql. Let me see how I can be of helpful in this regard. Regards, S.Mugunthan [EMAIL PROTECTED] -Original Message- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: Friday, April 27, 2007 8:38 AM To: MySql Subject: Re: MySQL e-zine Hey everyone, I have been considering putting together a e-zine for MySQL as my way to give back to the community. I was curious as to what type of interest there would be in this. I am thinking something quarterly to start with and probably 15 - 20 pages. Nothing huge. Topics would vary from everything like standard DBA stuff like backups, replication, system architecture and such to stuff for the developers out there..php, perl, python whatever. My background is more of a DBA so I would definitely need some input/articles for the developer side of things. It is just funny that in this day and age there is no magazines specifically for MySQL. I even saw a php magazine at the bookstore the other day! I can do the layout in QuarkXpress and generate pdfs that I can host on my website. Any thoughts? Any desires to contribute? Anything on the topic of actual queries I would skip, those are narrow and finite questions, usually specific to a certain application, and quickly answered on the list. I would cover what is more mysterious to most, which is the underbelly of mysql. Today there was good dialogue on the bin logs, that I think is stuff that helps people. Demystifying all the .cnf options, how to optimize for load, test the load etc. Steer clear of high end features like replication and the like, things that generally, if you are worried about them, your app is doing well enough you should be around knowledgeable people already. For me at least, it would be more about internals, that the step by step of how to do a join. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Complete Coverage of the ICC World Cup '07! Log on to www.sify.com/khel for latest updates, expert columns, schedule, desktop scorecard, photo galleries and more! Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com For the Expert view of the ICC World Cup log on to www.sify.com/khel. Read exclusive interviews with Sachin, Ganguly, Yuvraj, Sreesanth, Expert Columns by Gavaskar, Web chat with Dhoni and more! . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: FW: MySQL patches from Google
Hi Keith, This would be a great start to have an e-zine mag for Mysql. Let me see how I can be of helpful in this regard. Regards, S.Mugunthan [EMAIL PROTECTED] -Original Message- From: B. Keith Murphy [mailto:[EMAIL PROTECTED] Sent: Friday, April 27, 2007 9:55 AM To: Kevin Spencer Cc: mysql@lists.mysql.com Subject: Re: FW: MySQL patches from Google Kevin Spencer wrote: On 4/26/07, Mike OK [EMAIL PROTECTED] wrote: I read the Google blog post regarding these patches. They admit using MySQL for some internal data storage needs but not in the general search system. Still, that leaves many other applications. Groups, gmail, reader, news et al... -- Kevin. My understanding from some posting is that they do specifically use it for gmail which makes a lot of sense. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Complete Coverage of the ICC World Cup '07! Log on to www.sify.com/khel for latest updates, expert columns, schedule, desktop scorecard, photo galleries and more! Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com For the Expert view of the ICC World Cup log on to www.sify.com/khel. Read exclusive interviews with Sachin, Ganguly, Yuvraj, Sreesanth, Expert Columns by Gavaskar, Web chat with Dhoni and more! . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
