problem to run setup under windows xp sp1
I need to upgrade my current mysql installation . But now there is a problem to run the setup.exe of installshield 5.x under windows xp sp1 or sp2. The program hung after few seconds. In the memory remain active woexec, ntvdm and setup, but the process is freezed. It is possibile to convert the current setup mode of mysql4.014 in other install system, as for in mysqlcc092 ? Otherwise is no possible to install MYSQL under the current version of windows xp. Tks in advance. - Massimo Petrini c/o Omt spa Via Ferrero 67/a 10090 Cascine Vica (TO) Tel.+39 011 9505334 Fax +39 011 9575474 E-mail [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's the meaning of: InnoDB: Warning: using a partial-field key prefix in search
Marc, what MySQL version you are using? Can you figure out what query is causing the warning? Have you possibly mixed .frm files so that MySQL may have wrong information about column lengths? I have now added the following diagnostic code to a future version on InnoDB. It also explains what the warning is about. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ ... if (key_ptr key_end) { /* The last field in key was not a complete key field but a prefix of it. Print a warning about this! HA_READ_PREFIX_LAST does not currently work in InnoDB with partial-field key value prefixes. Since MySQL currently uses a padding trick to calculate LIKE 'abc%' type queries there should never be partial-field prefixes in searches. */ ut_print_timestamp(stderr); fprintf(stderr, InnoDB: Warning: using a partial-field key prefix in search.\n InnoDB: Table name %s, index name %s. Dump of the key\n InnoDB: value in the MySQL format:\n, index-table_name, index-name); fflush(stderr); ut_print_buf(original_key_ptr, key_len); fflush(stdout); fprintf(stderr, \n); if (!is_null) { dfield-len -= (ulint)(key_ptr - key_end); } } - Original Message - From: Mechain Marc [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, August 19, 2003 5:09 PM Subject: What's the meaning of: InnoDB: Warning: using a partial-field key prefix in search In one of my logfiles I have quite repeatedly the following message: InnoDB: Warning: using a partial-field key prefix in search What does this warning mean ? Regards, Marc Mechain Atos Origin -- 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]
Fw: mysql 4.0.14 + replication + windows XP PROF
No one has replied to my post. - Original Message - From: I.P. [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, August 18, 2003 1:01 PM Subject: mysql 4.0.14 + replication + windows XP PROF Hi, it's my story. I have two 4.0.14 mysql server on one machine with win XP Professional polish version. First acts as master: on port 3300 Second acts as slave: on port 3301 below my configuration: ### FOR MASTER # # This will be passed to all mysql clients [client] #password=my_password port=3300 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] basedir = c:/mysql4/ datadir = c:/mysql4/data/ port=3300 language=polish default-character-set=latin2 log-bin server-id=1 log-warnings set-variable = key_buffer=16K set-variable = max_allowed_packet=1M set-variable = thread_stack=64K set-variable = table_cache=4 set-variable = sort_buffer=64K set-variable = net_buffer_length=2K query_cache_size = 1024K # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:50M innodb_data_home_dir = d:\\innodb\\mysql4\\master\\ibdata innodb_log_group_home_dir = d:\\innodb\\mysql4\\master\\iblogs innodb_log_arch_dir = d:\\innodb\\mysql4\\master\\iblogs set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [myisamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [mysqlhotcopy] interactive-timeout ### ###FOR SLAVE # # This will be passed to all mysql clients [client] #password=my_password port=3301 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] basedir = c:/mysql4_slave/ datadir = c:/mysql4_slave/data/ port=3301 language=polish default-character-set=latin2 server-id=2 # log-bin # log-slave-updates master-host=127.0.0.1 master-user=irek master-password=XX master-port=3300 master-connect-retry=30 # log-update=log_updates.log log-warnings set-variable = key_buffer=16K set-variable = max_allowed_packet=1M set-variable = thread_stack=64K set-variable = table_cache=4 set-variable = sort_buffer=64K set-variable = net_buffer_length=2K query_cache_size = 1024K # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:50M innodb_data_home_dir = d:\\innodb\\mysql4\\slave\\ibdata innodb_log_group_home_dir = d:\\innodb\\mysql4\\slave\\iblogs innodb_log_arch_dir = d:\\innodb\\mysql4\\slave\\iblogs set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [myisamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M ## END configuration So i have noticed this things. I start replication with master and slave: 1) Master is running ... --- Slave is running ... and at console can i see: 030804 22:55:36 InnoDB: Started 030804 22:55:36 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3300', r eplication started in log 'FIRST' at position 4 030804 22:55:36 Slave SQL thread initialized, starting replication in log 'FIRS T' at position 0, relay log '.\hq-relay-bin.001' position: 4 mysqld-max-nt: ready for connections. Version: '4.0.14-max-nt' socket: '' port: 3301 --- That is ok. 3) checked master data dir: hq-bin.001 hq-bin.index 4) checked slave data dir: hq-relay-bin.001 hq-relay-bin.index master.info
Delete questions and speed/safety issues
I'm in the process of writing a Perl script to delete records out of a database when the records are more than 90 days old. I'm running MySQL version 4.0.12-standard. The db consists of around 620 MyISAM tables with the following structure: CREATE TABLE `ifInErrors_2` ( `id` int(11) NOT NULL default '0', `dtime` datetime NOT NULL default '-00-00 00:00:00', `counter` bigint(20) NOT NULL default '0', KEY `ifInErrors_2_idx` (`dtime`) ) TYPE=MyISAM; The tables range in size from 1-2 rows upwards to over 9,200,000 with an average of around 570,000 rows. Updates to this database run constantly (it records information from a program that monitors the routers and switches on my company's WAN) at a rate of around 2600 inserts/minute. I had originally planned to use syntax similar to: DELETE * FROM table_name WHERE `dtime` [90 days ago] After the DELETE runs, I plan on running MYISAMCHK on the affected table. Then I'll repeat both steps for all of the other tables in turn. Does anyone have any suggestions for alternatives or is there anything I'm missing here? I'm new to this stuff and may be way off base here. If so, please tell me. One last thing. I'm running out of Drive space and am I/O bound - I'm writing this script in case I can't get the RAID array I'm hoping for. Because of the I/O problem, execution time can sometimes be a factor in what I do. Execution speed is a primary concern. If this takes a day or two to run but the application can be running at the same time then there's no problem. But if I have to shut down my application for any appreciable length I time, then I have to find another way of doing this. Thanks, Jack Jack Coxen IP Network Engineer TelCove 712 North Main Street Coudersport, PA 16915 814-260-2705
Need help optimizing query, awfully slow on only 20000 records
Two Questions: Is the same query running directly on the linux server thru mysql is also very slow ? Have you done a explain plan on the query ? Marc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: punctuation limitation in fulltext search
Daniel Rossi [EMAIL PROTECTED] wrote: Can someone please tell me if my problem with the punctuation in a fulltext search is a bug or limitation? Ultimately the users of the system i built do not want to have to try using the + search then trying the * search for all the words which will obviouslly return a lot of results, is there a work around please let me know thanks. It's not a bug, it's how full-text search works. If you specify as a search word SEARCH_WORD without * operator, MySQL finds rows that contain SEARCH_WORD as entry word, not as a part of some word. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Oracle DBA here looking for advice on MySQL ....
I'd say MySQL 2nd edition by Paul Dubois. It has the first 200 pages with stuff you already know, but the next 800 pages are mysql specific. Very good reference book and best practices guide. The only thing it's missing is what I think should be in every book (and is in virtually none). 5 pages devoted to the initial setup of the program on each major OS. It's such a simple thing, but often there are very competent individuals who just want to read the best practices setup in concise form. Then, when more time is available, go back and tweak, etc.. -Original Message- From: Johnson, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2003 6:43 PM To: MySQL Users Subject: Oracle DBA here looking for advice on MySQL What is the best book on MySQL with regard to its Architecture and how it starts up, shutdowns, processes queries, rolls back data, etc etc. ? I am not looking for a SQL book here. What is the best My SQL book you have read ? Thank you in advance. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mast-Master Replication
On Tue, Aug 19, 2003 at 01:52:26PM -0700, Sanya Shaik wrote: I am unable to find any information about master-master replication. I need to replicate 1 mysql server over to other as a standby master server. If the second server is merely standby, you probably want master/slave rather than master/master. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 18 days, processed 911,623,980 queries (570/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP mysql_connect randomly failing
On Tue, 19 Aug 2003 14:36:15 -0700 Jon Drukman [EMAIL PROTECTED] wrote: I've got a library of PHP code whose first line is a mysql_connect statement, like this: $dbh=mysql_connect() or die(mysql connect failed: $php_errmsg); Approximately 1% of the time it just fails, for no stated reason: Warning: mysql_connect() [http://www.php.net/function.mysql-connect]: in /var/httpd/htdocs/pi/pi.php on line 3 mysql connect failed: Any ideas why this would be happening? PHP is version 4.3.1 (same results with the latest 4.3.3 release candidate), Mysql is 4.0.12 You should turn track_errors = On in your php.ini (default value is Off), if you want to see last PHP error in $php_errmsg variable. But I suppose the best solution would be not to use $php_errmsg, but to use mysql_error() - at least this function does not depend on configuration settings. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database reverted to 18hr old state after power outage
It depends if you had any kind of query logging enabled (binary or text) .. If you started safe_mysqld with -l (that's text logging of queries) .. or configured my.cnf with bin-log (that's binary logging).. You should be able to pipe/patch the logs against the database and let it run all the updates... I fixed all my errors by simply: 1. show tables 2. check table name1, name2, etc.. 3. any problems listed above 4. repair table name1 (repeat) On Wed, 20 Aug 2003, Brian Chan wrote: hi, the power outage in ontario caused my server to crash. after i booted up again it was as if the past 18 hrs never happened. all the changes to the database were gone. is there anyway to recover the missing data? it's probably too late now since new data has been put into the database since then... I now also have errors with my database. I run myisamchk /var/lib/mysql/*/*.MYI -e -o -r and I get many errors such as: Found block that points outside data file at 9464 or Found block with too small length at 980; Skipped Is there anyway I can resolve these errors? Thanks, Brian. -- 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: punctuation in fulltext searching
hmm well sorry to be unclear i know this works but it would return more results than needed also i cant expect users to add this themselves, like i would have to add the astrerix to every word in that case like i do to get all words ;\ I don't know the MySQL issues (I'm now using some FULLTEXT indexes but haven't played with them yet). Note that the issue you're talking about is one of stemming. The most common examples are singular versus plurals. One solution (if the built-in MySQL stuff proves unsatisfactory) is to build your own stemmer (assuming you're building some kind of interface to MySQL, using the MySQL API). -S -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2003 7:41 PM To: [EMAIL PROTECTED] Subject: Re: punctuation in fulltext searching Daniel Rossi [EMAIL PROTECTED] wrote: Hi i have just come across an issue where a word is not being searched up if there is any punctuation ie. AMROZI'S will not be search upon if you type AMROZI , please help Take a look at * operator which you can use in BOOLEAN MODE. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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: Simple error ... Im sure
Soren O'Neill [EMAIL PROTECTED] wrote: I keep getting a syntax error, when trying to run this query (perl script): SELECT * FROM temp_hits WHERE url NOT IN SELECT * FROM hits Ive tried with brackets around the last SELECT statement ... no better ... Subselects are supported in version 4.1. If your version of MySQL doesn't support subselects, at the following link you can find how to rewrite your query: http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database reverted to 18hr old state after power outage
So basically all the changes from those 18hrs were stored in logs and not yet commited to the database? So maybe if I just commited the logs every so often I wouldn't have this problem? It depends if you had any kind of query logging enabled (binary or text) .. If you started safe_mysqld with -l (that's text logging of queries) .. or configured my.cnf with bin-log (that's binary logging).. You should be able to pipe/patch the logs against the database and let it run all the updates... I fixed all my errors by simply: 1. show tables 2. check table name1, name2, etc.. 3. any problems listed above 4. repair table name1 (repeat) On Wed, 20 Aug 2003, Brian Chan wrote: hi, the power outage in ontario caused my server to crash. after i booted up again it was as if the past 18 hrs never happened. all the changes to the database were gone. is there anyway to recover the missing data? it's probably too late now since new data has been put into the database since then... I now also have errors with my database. I run myisamchk /var/lib/mysql/*/*.MYI -e -o -r and I get many errors such as: Found block that points outside data file at 9464 or Found block with too small length at 980; Skipped Is there anyway I can resolve these errors? Thanks, Brian. -- 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: month by month count
Select monthname(yourdatefield) as month, year(yourdatefield) as year, count(*) From yourtable Group by year, month; Jeff McKeon IT Manager Telaurus Communications LLC [EMAIL PROTECTED] (973) 889-8990 ex 209 ***The information contained in this communication is confidential. It is intended only for the sole use of the recipient named above and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication, or any of its contents or attachments, is expressly prohibited. If you have received this communication in error, please re-send it to the sender and delete the original message, and any copy of it, from your computer system. Thank You.*** -Original Message- From: Terence [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 1:16 AM To: Scott Haneda; MySql Subject: Re: month by month count select count(*), monthname(yourfield) from yourtable where year = '2003' group by monthname(yourfield) - Original Message - From: Scott Haneda [EMAIL PROTECTED] To: MySql [EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 12:38 PM Subject: month by month count I need to generate record counts for each month in a year, just one year at a time, but a full year at a glance. I am thinking 12 hits to the DB is perhaps not the most idea way to do it, is there a way to rip this out in one go? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
curtime
When I write select curtime(); It´s return the mysql´s system time, but I would like to change this time (because it´s late 3 minutes) Any ideas? Thanks a lot
Re: Mast-Master Replication
Sanya Shaik [EMAIL PROTECTED] wrote: I am unable to find any information about master-master replication. I need to replicate 1 mysql server over to other as a standby master server. Master-master? Do you mean circle replication A --- B or what ? -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlbinlog wrong output
Jan Josefowicz [EMAIL PROTECTED] wrote: This version of mysqlbinlog can work properly only with 3.23 server binary logs. is there a mysqlbinlog version that matches to MySQL 4.0.xx? Version 2.3 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW DATABASES works in 4.0.14
Vladimir Trebicky [EMAIL PROTECTED] wrote: VR What do you mean didn't work properly'? What exactly was VR wrong with mysql_fix_privilege_tables? Sorry, cannot reproduce the error. The result was that some of the tables were corrected, some not. Anyway, even old privilege tables shouldn't IMHO cause the SHOW DATABASES to work. In addition, I corrected the tables manually and are ok now. VR In 4.0.14 --safe-show-database is enabled by default. I know, another reason why I'm so confused ;-) Is there any way to debug this? I attach my SHOW VARIABLES. (which are btw also accessable by normal users) What is the output of SHOW GRANTS for the users that can show list of all databases? -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: punctuation limitation in fulltext search
The limitation you are indicating is in the interface, which MySQL really doesn't have. MySQL is a database engine, it's up to you to design the interface with whatever tool you want (i.e. web interface). For my full text search systems, which have a web interface, I have one field for required words, one for additional words and I assume they want to do a wild card search, but I have a check box for doing exact matches. If the users wants to search on phrases, they enclose the words in quotes. I have to do some parsing to translate what the user entered to the right syntax, but it works pretty well. I got my interface ideas from the Google Advanced Search interface. On Wednesday, August 20, 2003, at 01:15 AM, Daniel Rossi wrote: Can someone please tell me if my problem with the punctuation in a fulltext search is a bug or limitation? Ultimately the users of the system i built do not want to have to try using the + search then trying the * search for all the words which will obviouslly return a lot of results, is there a work around please let me know thanks. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
test
sorry this is only a test. query MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
moving MySQL
We've run out of disk space and would like to move either the entire MySQL system or if possible move the databases. Can this be done without any difficulties? Thanks Jon L. Miller, MCNE, CNS Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problem - Sorry
I should appologize for my earlier post Another Replication Problem. I had not properly analyzed the problem before posting. Although I have been administering a MySQL database for about a year now, replication is completely new to me. Sorry if I wasted anyone's time. Thanks to Jeremy Zawodny for leading me in the right direction. I have concluded that my problem lies in a corrupt bin-log file following a system crash. We had a bad power supply in one of our servers which caused it to randomly crash. It has since been replaced. I do have two questions if anyone has the time to answer them. Is there anything that can be done to prevent the bin-log files from becoming corrupt when a system crashes? What is the best course of action to take when the bin-log files become corrupt? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mast-Master Replication
Well , what I want is the second server to take the place of the first one in case of any problems with the master without having to come to a stand still Jeremy Zawodny [EMAIL PROTECTED] wrote:On Tue, Aug 19, 2003 at 01:52:26PM -0700, Sanya Shaik wrote: I am unable to find any information about master-master replication. I need to replicate 1 mysql server over to other as a standby master server. If the second server is merely standby, you probably want master/slave rather than master/master. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! | http://jeremy.zawodny.com/ MySQL 4.0.13: up 18 days, processed 911,623,980 queries (570/sec. avg) - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo.
RE: marking all records based on a text file
Another way to do it, create a temporary table load the content of your text file in it, and then do your update crossing the two tables. Marc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
runlevel setting on default rpm is wrong (in my opinion)
We were affected by the power outage last week and I think people should be aware - I'm not sure if it's fixed (I'm virtually certain it is not), but the default rpm install of 4.0.12 has mysql starting up in runlevel 4 only. When our power was restored, RedHat went to runlevel 3 and mysql never started on its own. To find out if you are affected run: /sbin/chkconfig --list mysql Then, run: /sbin/runlevel If chkconfig shows the 3:off and runlevel shows N 3, then mysql will not start on its own. This is probably because I don't have X running by default on the DB machine. I consider this a good thing. To rectify the situation: /sbin/chkconfig --level 3 mysql on -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems??
Hi all, I'm not receiving your emails since this morning at 7:30AM. Are there some problems? Or maybe I have problems with my subscription but it's strange. Thanks in advance. Dave. - L'Eco della Stampa, http://www.ecostampa.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving MySQL
On Wed, Aug 20, 2003 at 11:41:22PM +0800, Jon Miller wrote: We've run out of disk space and would like to move either the entire MySQL system or if possible move the databases. Can this be done without any difficulties? Yes, if you can afford to turn off your server. If you need to keep your server live, then things become more complicated, but solvable... Thanks Jon L. Miller, MCNE, CNS Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help optimizing query, awfully slow on only 20000 records
When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help optimizing query, awfully slow on only 20000 records
Result of EXPLAIN is: table|type|possible_keys|key|key_len|ref|rows|Extra inquiries|ALL|contact_id| | | |8253|Using filesort individual_contacts|eq_ref|PRIMARY,indiv_contact_id|PRIMARY|3|inquiries.indiv_contact_id|1 source_for_inquiries_form|eq_ref|PRIMARY,contact_id|PRIMARY|3|inquiries.contact_id|1 What does EXPLAIN SELECT query show? Have you read the chapter in the manual on optimizing queries? Do you have all the proper indices set up? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 2:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] Apolinaras Apollo Sinkevicius Carmel Music Entertainment, LLC 701 Main Street Evanston, IL 60202 Phone: (847) 864-5969 X110 Fax: (847) 864-6149 Toll-free: 800-276-5969 X110 e-mail: [EMAIL PROTECTED] web-site: http://carmelme.com Having an event in Chicago, or would you like to bring Chicago entertainment to your event? Give Carmel Music Entertainment a call for the finest entertainment available in Chicago. - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL
Re: moving MySQL
On Wed, Aug 20, 2003 at 11:41:22PM +0800, Jon Miller wrote: We've run out of disk space and would like to move either the entire MySQL system or if possible move the databases. Can this be done without any difficulties? Yes. If you're on a Unix-like platform, you can symlink one or more database directories to another disk. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 947,126,092 queries (572/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: curtime
On Wed, Aug 20, 2003 at 10:54:28AM -0300, Fabio Bernardo wrote: When I write select curtime(); It?s return the mysql?s system time, but I would like to change this time (because it?s late 3 minutes) Then your system's time need to be updated correctly. Any ideas? Get your sysadmin to install an NTP client on your server. Thanks a lot -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help optimizing query, awfully slow on only 20000 records
Query takes 3.4 seconds to run on the server, but it takes 1-2minutes to run via MyODBC 3.51.06 using passthrough (Access97 is the front end, but it has query type that allows bypass of Access interpretation. Two Questions: Is the same query running directly on the linux server thru mysql is also very slow ? Have you done a explain plan on the query ? Marc. - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help optimizing query, awfully slow on only 20000 records
When I send the query throuh comman line, it works perfect 3-4 seconds, but when I do Access97 pass-through query, that is when it runs into 1-2 minutes. It is almost as slow as using Access97 native query that goes through MyODBC, so... That doesn't look too bad. Is the query that slow when you use the command-line client alone (connected directly to the server), or is it just when accessing the database through the ODBC tunnel? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 3:14 PM To: Michael S. Fischer Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records Result of EXPLAIN is: table|type|possible_keys|key|key_len|ref|rows|Extra inquiries|ALL|contact_id| | | |8253|Using filesort individual_contacts|eq_ref|PRIMARY,indiv_contact_id|PRIMARY|3| inquiries.indiv_contact_id|1 source_for_inquiries_form|eq_ref|PRIMARY,contact_id|PRIMARY|3| inquiries.contact_id|1 What does EXPLAIN SELECT query show? Have you read the chapter in the manual on optimizing queries? Do you have all the proper indices set up? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 2:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit
The Oracle is ignoring you!
If you want the Oracle to answer a question for you, you *must* have the words tell me or tellme somewhere in your subject line. Capitalization doesn't matter. If you are answering a question, you must have the word answer and the Question ID in the subject. Usually you can get this by replying to the question in the normal way in your mail program, inheriting the question's Subject: line. If you want a helpfile from the Oracle, send a message with the word help as the subject. If you want the Oracle to send you a question to answer, put the words ask me or askme in the subject. Please read the helpfile before trying to use the Oracle. It is also available at the Oracle's website http://www.cs.indiana.edu/~oracle/. If you wish to subscribe or unsubscribe to the Internet Oracularities, this is the wrong address. Send your subscription requests to [EMAIL PROTECTED] as described in the helpfile. If you wish to submit your ratings on the Internet Oracularities, send them to [EMAIL PROTECTED] as described at the top of each issue of the Oracularities digests. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Differential backup
Hi list, Does any know how to do differential backups or does exist a tool that do this kind of backups. I have mysql 4.0.12 and use innodb tables, mysql is running on redhat 7.3 Greetings and thnx in advanced _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DIFFERENTIAL BACKUPS
Hi list, Does anyone know how to do a differntial backup for mysql databases. Is it possible?. I use mysql 4.0.12 on a redhat 7.3 box. Greetings and thnx in advanced _ MSN Fotos: la forma más fácil de compartir e imprimir fotos. http://photos.msn.es/support/worldwide.aspx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT SPEEDS......
On 20 Aug 2003 at 15:08, Tom O'Neill (MySQL User) wrote: Is there any difference in speed between the following select statements? SELECT yada,yda FROM test WHERE id IN(1,2,3) OR SELECT yada,yda FROM test WHERE (id =1 or id = 2 or id =3) You can put EXPLAIN in front of your SELECT statement to see how MySQL plans to execute it. Those seem to be treated identically in version 4.0.14, but it's possible that the second one wasn't as optimized in some earlier version. Documentation on EXPLAIN is here: http://www.mysql.com/doc/en/EXPLAIN.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Delete questions and speed/safety issues
If the database cannot grow unbounded, and you have to prune the database from time to time (no matter how much disk space you may have) you will have to perform regular table defragmentation if you want to minimize performance degradation. So, you'll need to schedule periodic outages to do that, or alternatively, you can establish two database instances and swap between them, performing the defragmentation on the inactive database. --Michael -Original Message- From: Jack Coxen [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 11:43 AM To: 'Michael S. Fischer'; Jack Coxen; 'MySQL List (E-mail)' Subject: RE: Delete questions and speed/safety issues Switching to another database isn't really an option. I didn't write the package and I'm not good enough to port it to another database or to rewrite it for a multiple machine architecture. Probably the only non-RAID option I have (assuming I want to keep more than 3 months worth of data) would be to add another couple of drives to the server and then split the tables among them with links back to the original database directory - sort of 'poor man's RAID'. Drives I can get...it's the RAID controller and enclosure that is the sticking point. Jack -Original Message- From: Michael S. Fischer [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 1:43 PM To: 'Jack Coxen'; 'MySQL List (E-mail)' Subject: RE: Delete questions and speed/safety issues It's quite possible you're using the wrong tool for the job. Since this is a write-intensive environment, you may get better performance by using another database such as PostgreSQL or Oracle. Alternatively, consider the option of re-architecting the application to distribute the writes across multiple machines, each with its own small disk and running its own instance of MySQL. You need not necessarily have a big RAID array to scale effectively, and sometimes the small soldiers approach is more cost-effective. In order to safely run myisamchk on a table, mysqld must be shut down, or, alternatively, you must find some way to guarantee that the table is not presently open by mysqld and that mysqld will not try to open the files corresponding to the table while the check is in progress. --Michael -Original Message- From: Jack Coxen [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 6:07 AM To: MySQL List (E-mail) Subject: Delete questions and speed/safety issues I'm in the process of writing a Perl script to delete records out of a database when the records are more than 90 days old. I'm running MySQL version 4.0.12-standard. The db consists of around 620 MyISAM tables with the following structure: CREATE TABLE `ifInErrors_2` ( `id` int(11) NOT NULL default '0', `dtime` datetime NOT NULL default '-00-00 00:00:00', `counter` bigint(20) NOT NULL default '0', KEY `ifInErrors_2_idx` (`dtime`) ) TYPE=MyISAM; The tables range in size from 1-2 rows upwards to over 9,200,000 with an average of around 570,000 rows. Updates to this database run constantly (it records information from a program that monitors the routers and switches on my company's WAN) at a rate of around 2600 inserts/minute. I had originally planned to use syntax similar to: DELETE * FROM table_name WHERE `dtime` [90 days ago] After the DELETE runs, I plan on running MYISAMCHK on the affected table. Then I'll repeat both steps for all of the other tables in turn. Does anyone have any suggestions for alternatives or is there anything I'm missing here? I'm new to this stuff and may be way off base here. If so, please tell me. One last thing. I'm running out of Drive space and am I/O bound - I'm writing this script in case I can't get the RAID array I'm hoping for. Because of the I/O problem, execution time can sometimes be a factor in what I do. Execution speed is a primary concern. If this takes a day or two to run but the application can be running at the same time then there's no problem. But if I have to shut down my application for any appreciable length I time, then I have to find another way of doing this. Thanks, Jack Jack Coxen IP Network Engineer TelCove 712 North Main Street Coudersport, PA 16915 814-260-2705
RE: SELECT SPEEDS......
This is trivial to benchmark yourself. Try: BENCHMARK(10, SELECT yada, yada FROM test WHERE id IN(1,2,3)) And compare to BENCHMARK(10, SELECT yada,yda FROM test WHERE (id =1 or id = 2 or id =3)) See http://www.mysql.com/doc/en/Miscellaneous_functions.html for documentation on the BENCHMARK command. --Michael -Original Message- From: Tom O'Neill (MySQL User) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 1:09 PM To: [EMAIL PROTECTED] Subject: SELECT SPEEDS.. Is there any difference in speed between the following select statements? SELECT yada,yda FROM test WHERE id IN(1,2,3) OR SELECT yada,yda FROM test WHERE (id =1 or id = 2 or id =3) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help optimizing query, awfully slow on only 20000 records
That doesn't look too bad. Is the query that slow when you use the command-line client alone (connected directly to the server), or is it just when accessing the database through the ODBC tunnel? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 3:14 PM To: Michael S. Fischer Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records Result of EXPLAIN is: table|type|possible_keys|key|key_len|ref|rows|Extra inquiries|ALL|contact_id| | | |8253|Using filesort individual_contacts|eq_ref|PRIMARY,indiv_contact_id|PRIMARY|3| inquiries.indiv_contact_id|1 source_for_inquiries_form|eq_ref|PRIMARY,contact_id|PRIMARY|3| inquiries.contact_id|1 What does EXPLAIN SELECT query show? Have you read the chapter in the manual on optimizing queries? Do you have all the proper indices set up? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 2:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] Apolinaras Apollo Sinkevicius Carmel Music Entertainment, LLC 701 Main Street Evanston, IL 60202
RE: moving MySQL
Sure, just shut down the database cleanly (mysqladmin shutdown), move the data directory intact to the new filesystem, and start up again. --Michael -Original Message- From: Jon Miller [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 8:41 AM To: [EMAIL PROTECTED] Subject: moving MySQL We've run out of disk space and would like to move either the entire MySQL system or if possible move the databases. Can this be done without any difficulties? Thanks Jon L. Miller, MCNE, CNS Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mast-Master Replication
You're going to need to architect more than just a master-slave relationship to do what you want to do. All replication does is copy commands from one MySQL server to the other; it does not create a failover environment by itself. Creating a failover environment is beyond the scope of MySQL; you'll need to handle things like fault detection of the master server, redirecting traffic from the master to the slave when a fault occurs, and ensuring that you don't flap connections between master and slave (i.e. when you switch over to the slave, you don't switch back to the master without bringing the master back up to date first). You also need to take care of the double failure case and probably many other corner cases I haven't listed. In short, MySQL's failover is *not* a multiple-master replicated database system like Oracle Parallel Server. There's a reason why the latter is so pricey. :-) --Michael -Original Message- From: Sanya Shaik [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 6:11 AM To: [EMAIL PROTECTED] Subject: Re: Mast-Master Replication Well , what I want is the second server to take the place of the first one in case of any problems with the master without having to come to a stand still Jeremy Zawodny [EMAIL PROTECTED] wrote:On Tue, Aug 19, 2003 at 01:52:26PM -0700, Sanya Shaik wrote: I am unable to find any information about master-master replication. I need to replicate 1 mysql server over to other as a standby master server. If the second server is merely standby, you probably want master/slave rather than master/master. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! | http://jeremy.zawodny.com/ MySQL 4.0.13: up 18 days, processed 911,623,980 queries (570/sec. avg) - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle DBA here looking for advice on MySQL ....
As a Oracle followup question Oracle supports Tablespaces That is 2 or more logically separate entities for System Data and User Data Does MySQL have Tablespace support? Many Thanks, Martin - Original Message - From: Adam Nelson [EMAIL PROTECTED] To: 'Johnson, Michael ' [EMAIL PROTECTED]; 'MySQL Users' [EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 7:49 AM Subject: RE: Oracle DBA here looking for advice on MySQL I'd say MySQL 2nd edition by Paul Dubois. It has the first 200 pages with stuff you already know, but the next 800 pages are mysql specific. Very good reference book and best practices guide. The only thing it's missing is what I think should be in every book (and is in virtually none). 5 pages devoted to the initial setup of the program on each major OS. It's such a simple thing, but often there are very competent individuals who just want to read the best practices setup in concise form. Then, when more time is available, go back and tweak, etc.. -Original Message- From: Johnson, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2003 6:43 PM To: MySQL Users Subject: Oracle DBA here looking for advice on MySQL What is the best book on MySQL with regard to its Architecture and how it starts up, shutdowns, processes queries, rolls back data, etc etc. ? I am not looking for a SQL book here. What is the best My SQL book you have read ? Thank you in advance. Mike -- 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]
Seeking advice on best table structure
What would be a good way to deal with the following... I have a form that has 5 checkboxes on it, lets say the checkboxes are for categories, and more than one can be selected. For example: please tell is what brochure you want [] car [] boat [] truck [] SUV [] beetle I have been asked to provide statistics on how many total form submissions there are per month. This part seems pretty simple, I just add a new record every time the form is sent. They also want to know stats on which categories were picked as well. I don't know if I should create one table to store just the fact that a form has been posted, and then another to store the 5 above values. I don't see the 5 categories being changed, so I could create 5 fields in one table, and tally them that way, or I could create one field and put the actual value in that field and tally them that way. Any suggestions are appreciated. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple error ... Im sure
Victoria Reznichenko wrote: Soren O'Neill [EMAIL PROTECTED] wrote: I keep getting a syntax error, when trying to run this query (perl script): SELECT * FROM temp_hits WHERE url NOT IN SELECT * FROM hits Ive tried with brackets around the last SELECT statement ... no better ... Subselects are supported in version 4.1. If your version of MySQL doesn't support subselects, at the following link you can find how to rewrite your query: http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html Thank you very much ... the following seems to work for me:' SELECT temp_hits.* FROM temp_hits LEFT JOIN hits ON temp_hits.url=hits.url WHERE hits.url IS NULL Kind regards Soren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Will a SIGTERM shutdown while using LinuxThreads?
On another mailing list, there is a discussion about whether or not it is adequate/proper to manage the MySQL server process via a watchdog script (namely, DJB's daemontools). The point behind this watchdog script is to launch a service (such as mysqld) in the foreground, and notice if that process exits. An auxilary tool will send this managed process a SIGTERM, to request shutdown. It has been working for me under FreeBSD, and the MySQL docs imply this should work for other OSes, but someone just pointed out to me this info: http://www.mysql.com/doc/en/Linux.html When using LinuxThreads you will see a minimum of three processes running. These are in fact threads. There will be one thread for the LinuxThreads manager, one thread to handle connections, and one thread to handle alarms and signals. I myself don't use Linux, much less LinuxThreads. Can anyone advise how the theree process/threads should handle a SIGTERM in such a watchdogged environment? -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Raw partition with InnoDB on Windows 2000
Hello guys !!! I have MySQL/InnoDB on Windows 2000 and i want to create a RAW PARTITION to use InnoDB tablespace... In Linux the configurarion is: innodb_data_file_path = /dev/hda1:32Gnewraw innodb_data_home_dir = and in Windows 2000 ?? Tnks in advance... InnoDB,MySQL,SQL,HELLLP !!! - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 296-2311 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: curtime
log into the machine that runs the mysql server (usually as root user) and change the time with 'date -s ...' -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) -Ursprüngliche Nachricht- Von: Fabio Bernardo [EMAIL PROTECTED] An: Mysql (E-mail) [EMAIL PROTECTED] Gesendet: Mittwoch, 20. August 2003 15:54 Betreff: curtime When I write select curtime(); It´s return the mysql´s system time, but I would like to change this time (because it´s late 3 minutes) Any ideas? Thanks a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Raw partition with InnoDB on Windows 2000
Hello guys !!! I have MySQL/InnoDB on Windows 2000 and i want to create a RAW PARTITION to use InnoDB tablespace... In Linux the configurarion is: innodb_data_file_path = /dev/hda1:32Gnewraw innodb_data_home_dir = and in Windows 2000 ?? Tnks in advance... InnoDB,MySQL,SQL,query,HELLLP !!! - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 296-2311 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doing Differential backup
Hi list, Does any know how to do differential backups or does exist a tool that do this kind of backups. I have mysql 4.0.12 and use innodb tables, mysql is running on redhat 7.3 Greetings and thnx in advanced _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.13 Memory problem under heavy load
Hi all, I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 10-13 threads serving databases. To increase available free memory I've did the following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 128Mb 5- Have increased max_connection from 100 to 150 6- Have increased thread_cache to 5 This changes helped a bit but still memory is a problem for MySQL. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 256Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time. Thank you for your time Kayra Otaner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mast-Master Replication
I think you also want what ever the standby master gets a write /*assuming that the primary master where to go down or some other reason*/ the submaster needs to replicate that insert so In /etc/my.cnf Use log-bin server-id=different int then the master log-slave-updates replicate-* = replicate from master | replicate from submaster on master ---Original Message- --From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] --Sent: Tuesday, August 19, 2003 10:38 PM --To: Sanya Shaik --Cc: [EMAIL PROTECTED] --Subject: Re: Mast-Master Replication -- --On Tue, Aug 19, 2003 at 01:52:26PM -0700, Sanya Shaik wrote: -- I am unable to find any information about master-master replication. I -- need to replicate 1 mysql server over to other as a standby master -- server. -- --If the second server is merely standby, you probably want master/slave --rather than master/master. --Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! --[EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- --MySQL 4.0.13: up 18 days, processed 911,623,980 queries (570/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]
moving MySQl database
I've tried moving the database from one partition to another and now I'm getting the following error: # Starting mysqld daemon with databases from /data/mysql 030820 23:48:08 mysqld ended When I issue #/ /usr/share/mysql/mysql.server start Anyone have any idea what's gone wrong. I changed the datadir in both /etc/my.cnf and /usr/share/mysql/mysql.server. This is on a MySQL-3.23.55-1 server. Thanks Thanks Jon L. Miller, MCNE, CNS Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help optimizing query, awfully slow on only 20000 records
What does EXPLAIN SELECT query show? Have you read the chapter in the manual on optimizing queries? Do you have all the proper indices set up? --Michael -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 2:52 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Need help optimizing query, awfully slow on only 2 records When following query is pulled up, it takes about whole 1-2 minutes to come up. inquiries table has 17000 records, contacts has about 7000, individual_contacts has about 16000. It has gotten worse once I upgraded to 4.0 and latest MyODBC. Clients are separate machines (mix of Win98 and WinXP). Those 20K records is what feeds the Access97 form, pull down list filters out some and pulls up about 3K and people just start typing a name and then (since there multiple inquiries for some clients) pull down the list to pick inquiry they want. What are you defining as a huge performance hit? Is the result set 20K records, or the base tables? How big are the base tables? Are the client and server on the same machine? Pulling 20K records across the network could take some time. Formatting 20K records into a pull -down list in access will also take a long time. Anyway who reads a 20K list? Which parts of the process are slow? How does the query perform from the mysql command line? -Original Message- From: Apollo (Carmel Entertainment) [mailto:[EMAIL PROTECTED] Sent: 19 August 2003 17:29 To: [EMAIL PROTECTED] Subject: Need help optimizing query, awfully slow on only 2 records 1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0 I have about 20K records that result from the following query. Front end for the database is ACCESS97 and pulling up 20K records makes a huge performance hit. For the form in question I am using PASSTHROUGH type query (the one that just passes everything straight to server without ODBC). NOTE: souce_for_inquiries_form is the join table and is searchable in the from (it feeds a pull-down list). SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id, inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date, inquiries.event_type, inquiries.letter_type, inquiries.event_date, inquiries.event_date_general, inquiries.event_location, inquiries.guests, inquiries.hours, inquiries.budget, inquiries.event_description, inquiries.talent_description, inquiries.past_use, inquiries.referred_by, inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes, source_for_inquiries_form.organization, source_for_inquiries_form.mailing_address_1, source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city, source_for_inquiries_form.state, source_for_inquiries_form.zip, source_for_inquiries_form.contact_type, individual_contacts.contact_name_first, individual_contacts.contact_name_last, individual_contacts.contact_prefix, individual_contacts.contact_title, individual_contacts.email FROM inquiries LEFT JOIN individual_contacts ON inquiries.indiv_contact_id = individual_contacts.indiv_contact_id INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id = source_for_inquiries_form.contact_id ORDER BY inquiries.inquiry_id DESC; - - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Grown defects in a RAID array
I've notice that when grown defects (bad blocks on the disk caused by usage over time) that my dedicated mysql server is adversely effected. Since the action of Grown defects does not flush the table with a write lock-that the block marked as bad will effect prior to that block becoming a grown defect-what is another way for mysql to recover from this situation? I'm currently using EXT3 does this same effect happen with Reiser-Fs / UFS / Veritas? Maybe Jeremy Zawodny can help me with this?
RE: Oracle DBA here looking for advice on MySQL ....
In a word, no. The way MySQL organizes its datafiles is trivial by comparison: one directory per database, two files per table (table.MYI and table.MYD), one is the datafile, the other is the index file. MySQL also does not preallocate space for its tables like Oracle does. --Michael -Original Message- From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 2:20 PM To: 'MySQL Users' Subject: Re: Oracle DBA here looking for advice on MySQL As a Oracle followup question Oracle supports Tablespaces That is 2 or more logically separate entities for System Data and User Data Does MySQL have Tablespace support? Many Thanks, Martin - Original Message - From: Adam Nelson [EMAIL PROTECTED] To: 'Johnson, Michael ' [EMAIL PROTECTED]; 'MySQL Users' [EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 7:49 AM Subject: RE: Oracle DBA here looking for advice on MySQL I'd say MySQL 2nd edition by Paul Dubois. It has the first 200 pages with stuff you already know, but the next 800 pages are mysql specific. Very good reference book and best practices guide. The only thing it's missing is what I think should be in every book (and is in virtually none). 5 pages devoted to the initial setup of the program on each major OS. It's such a simple thing, but often there are very competent individuals who just want to read the best practices setup in concise form. Then, when more time is available, go back and tweak, etc.. -Original Message- From: Johnson, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2003 6:43 PM To: MySQL Users Subject: Oracle DBA here looking for advice on MySQL What is the best book on MySQL with regard to its Architecture and how it starts up, shutdowns, processes queries, rolls back data, etc etc. ? I am not looking for a SQL book here. What is the best My SQL book you have read ? Thank you in advance. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mast-Master Replication
You can do it if you can handle primary key violations and don't have auto_increment columns in your tables. Race conditions can occur. ---Original Message- --From: Sanya Shaik [mailto:[EMAIL PROTECTED] --Sent: Wednesday, August 20, 2003 6:11 AM --To: [EMAIL PROTECTED] --Subject: Re: Mast-Master Replication -- --Well , what I want is the second server to take the place of the first --one in case of any problems with the master without having to come to a --stand still -- --Jeremy Zawodny [EMAIL PROTECTED] wrote:On Tue, Aug 19, 2003 at --01:52:26PM -0700, Sanya Shaik wrote: -- I am unable to find any information about master-master replication. I -- need to replicate 1 mysql server over to other as a standby master -- server. -- --If the second server is merely standby, you probably want master/slave --rather than master/master. --Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! --| http://jeremy.zawodny.com/ -- --MySQL 4.0.13: up 18 days, processed 911,623,980 queries (570/sec. avg) -- -- --- --Do you Yahoo!? --The New Yahoo! Search - Faster. Easier. Bingo. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking advice on best table structure
At 03:30 PM 8/20/2003 -0700, Scott Haneda wrote: What would be a good way to deal with the following... I have a form that has 5 checkboxes on it, lets say the checkboxes are for categories, and more than one can be selected. For example: please tell is what brochure you want [] car [] boat [] truck [] SUV [] beetle snip I don't see the 5 categories being changed, so I could create 5 fields in one table, and tally them that way, or I could create one field and put the actual value in that field and tally them that way. Any suggestions are appreciated. I'm gonna go out on a limb here. My intuition tells me to look for the opportunity to add some data to this process... For example, what if a record were created for each brochure that needed to be processed, and suppose that in some back-end application the person fulfilling these requests completed the data in the record... Then you might justify making a separate table for each brochure (or one with a two column key)... Then you would not waste space, you could gather statistics easily with joins, and you'd satisfy both processes at once... then again, if none of that back-end process exists, then it's probably simplest to just make an integer column and bit-map the check-boxes into it (if you want to save space)... or if you don't care about space just create a true/false column for each check box. The short answer is - more info is needed to pick the best approach. HTH, _M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication question
Should be fine as long as the column on C has a default and is not a unique index. ---Original Message- --From: Jeff McKeon [mailto:[EMAIL PROTECTED] --Sent: Wednesday, August 20, 2003 6:05 AM --To: MySql --Subject: RE: Replication question -- --Nobody has any advice for this one? -- --Jeff -- -- -Original Message- -- From: Jeff McKeon -- Sent: Tuesday, August 19, 2003 8:22 AM -- To: [EMAIL PROTECTED] -- Subject: Replication question -- -- -- Hey all, -- -- I have 3 databases replicating (ver 3.23) A to B and B to C -- -- On C I want to modify one of the tables and add a column. -- Tables A and B will not have this new column added. Will -- this cause a problem replicating data form B to C? -- -- Thanks, -- -- Jeff -- -- -- -- MySQL General Mailing List -- For list archives: http://lists.mysql.com/mysql -- To unsubscribe: -- http://lists.mysql.com/mysql? [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: moving MySQL
If you can't turn off your db use mysql_hotcopy and copy the data over NFS ---Original Message- --From: Brian Reichert [mailto:[EMAIL PROTECTED] --Sent: Wednesday, August 20, 2003 2:24 PM --To: Jon Miller --Cc: [EMAIL PROTECTED] --Subject: Re: moving MySQL -- --On Wed, Aug 20, 2003 at 11:41:22PM +0800, Jon Miller wrote: -- We've run out of disk space and would like to move either the entire --MySQL system or if possible move the databases. Can this be done without --any difficulties? -- --Yes, if you can afford to turn off your server. -- --If you need to keep your server live, then things become more --complicated, but solvable... -- -- -- Thanks -- -- Jon L. Miller, MCNE, CNS -- Director/Sr Systems Consultant -- MMT Networks Pty Ltd -- http://www.mmtnetworks.com.au -- -- I don't know the key to success, but the key to failure -- is trying to please everybody. -Bill Cosby -- --Brian 'you Bastard' Reichert [EMAIL PROTECTED] --37 Crystal Ave. #303 Daytime number: (603) 434-6842 --Derry NH 03038-1713 USA BSD admin/developer at large -- --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]
join tables on a large database 200 meg
Hi there i am trying to work out the most efficient way to list say multiple categories of entries, the database is quite large about 200 meg. I would like to know if using join tables is more efficient than storing the keys in a varchar field then within the second loop doing a where in (1,2,3,4) where the 1,2,3,4 are the stored category keys in the varchar field rather than a where in (1), where 1 is the pirmaryID of the entry for instance ? i was told the join table could be slow on a large database let me know thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]