RE: MySQL Newbie: Running in UNIX
Fix your mysql privileges http://dev.mysql.com/doc/mysql/en/GRANT.html DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Hossain, Ashfaq (Ashfaq) [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 8:39 AM To: [EMAIL PROTECTED] Subject: MySQL Newbie: Running in UNIX I would like to try out MySQL on my UNIX account (Solaris). I DO NOT HAVE root privilieges in UNIX. I only have a normal user privilege. I have installed MySQL on my user UNIX account. Looks like I get the Server going and can also get the status from the Server. But, I get an error msg when I try to create a database. I have attached the unix screen dump below. Any help will be greatly appreciated. Thanks, -Ashfaq Hossain / UNIX screen dump / Unix System / unix uname -a SunOS 5.8 Generic_108528-24 sun4u sparc SUNW,Sun-Fire-880 / Running MySQL Processes / unix ps -ef | grep mysql ashfaq 22040 22015 0 11:13:23 pts/63 0:00 /home/ashfaq/MySQL/mysql-standard-4.1.7-sun-solaris2.9-sparc-64bit/bin/m ysqld - ashfaq 14026 9830 0 11:18:08 pts/63 0:00 grep mysql ashfaq 22015 9830 0 11:13:23 pts/63 0:00 /bin/sh ./bin/mysqld_safe / Starting the Client / unix bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.1.7-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. / Checking Status / mysql status -- bin/mysql Ver 14.7 Distrib 4.1.7, for sun-solaris2.9 (sparc) Connection id: 7 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Using delimiter:; Server version: 4.1.7-standard Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 8 min 0 sec Threads: 1 Questions: 24 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 1 Queries per second avg: 0.050 / / / Error Message : Please HELP! / / / mysql create database info ; ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'info' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL support for AMD64
I have 400 AMD 64 box 60 of which have 8 GB of data use for databases. Each server does about 5K qps and many of our clusters do 30K qps. We use Suse Enterprise Linux 8. I've done numerous benchmarks to know that AMD Operton is a better platform over XEONS period. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Donny Simonton [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 12:04 PM To: 'Steve Poirier'; 'Lynn Bender'; [EMAIL PROTECTED] Subject: RE: MySQL support for AMD64 I've got 3 amd64 machines running mysql. One with 32 gigs of memory and 2 with 16gigs. All of them are quad 848's. We use fedora core 2 on all of our boxes. 2 of the boxes are pushing over 3000 queries per second. And one is over 4k per second. Personally, I have about 30 mysql boxes, and I will never buy a non-64 bit machine again. Donny -Original Message- From: Steve Poirier [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 8:29 AM To: 'Lynn Bender'; [EMAIL PROTECTED] Subject: RE: MySQL support for AMD64 I would recommend Raid 10 over Raid 5 even if it's kinda a big hit on your storage cabality. http://www.experts-exchange.com/Storage/Q_20640972.html I'm successfully running a Master/Slave setup with the following machines: Quad Opteron 64 / 32G RAM Dual Opteron 64 / 16G RAM Using gentoo compiled from scratch (stage 1) 100% stability around 1000 queries / second _ Steve -Original Message- From: Lynn Bender [mailto:[EMAIL PROTECTED] Sent: November 30, 2004 2:23 PM To: [EMAIL PROTECTED] Subject: MySQL support for AMD64 I just received a box with the following specs: Dual AMD64 8G ram Two 3ware 2.4 terabyte RAID 5 arrays. My company has been using Redhat for most of its production machines. 1. Does anyone have any success/horror stories running MySQL 4.0.x on RHES 3/ AMD64? 2. Does anyone have alternate recommendations for running MySQL databases in the terabyte range on AMD64? Thanks Lynn Bender UnsubCentral Secure Email List Suppression Management Neutral. Bonded. Trusted. You are receiving this commercial email from a representative of UnsubCentral, Inc. 13171 Pond Springs Road, Austin, TX 78729 Toll Free: 800.589.0445 To cease all communication with UnsubCentral, visit http://www.unsubcentral.com/unsubscribe or send an email to [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Heap Help
Create a myISAM table and put the table on a RAM DISK. It will support everything that heap does and more, like ranges ( 4.1 only allows Hash lookups). CREATE TABLE TABLE NAME ( .. ) INDEX DIRECTORY = /dev/shm DATA DIRECTORY = /dev/shm DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, November 29, 2004 12:10 PM To: Mysql Subject: Heap Help I want to put a table in Ram (HEAP) with a field of at least 500 characters. I do I do this if Blob and text are not allowed? Thanks Donny Lairson President http://www.gunmuse.com http://www.gunmuse.com/ 469 228 2183
RE: using IN()
Although parentUserId is indexed, the fact that you're using an IN list turns the query into a range. IN lists are fast but at certain levels such as yours it is not. The reasons are listed below: The query parser must allocate memory for every string in the list and convert it into an int. So your using prob a few megs of memory to parse the IN list. Next the query optimizer notices that the IN list is very large, thus for efficiency it must perform passes or a range across the binary tree. If this range covers more then 30% of your table, the optimizer will determine that a full table scan is faster and will not use the index. To improve throughput: Put the 60K ints into a temporary table. Next join against this temporary table. This join forces the optimizer to translate the range into an eq_ref-one of the fastest type joins. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Mitul Bhammar [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 9:22 PM To: [EMAIL PROTECTED] Subject: using IN() I've a bunch of IDs fetched(around 60,000) from a DB. I'm using these IDs to fetch data from another DB having a related fields in its tables. I'm using IN clause for it. i.e. for e.g. SELECT * FROM site_users WHERE parentUserId IN (1,2,3,4) Again here parentUserId is Indexed. The query is running fine for now. I wanted to know how MySQL interprets and executes this query and can it have problems in future if number exceeds 60,000?? __ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.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]
RE: innodb data file grew beyond the specified max size in config
When ever you use INNODB it must create a table space, something like a virtual file space or system for the data, that sits on top of the OS filesystem. By default it has allocated a certain size, change the value of innodb_data_file_path = ibdata1:500M:autoextend:max:2000M 500M back to the original size. The data will grow as large as the filesystem will allow it since the data is autoextended. Innodb datafiles contain the index and the data in one file for 4.0.x. This means that data will be the size of the columns + the indexes + some padding per row. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Hristo Chernev [mailto:[EMAIL PROTECTED] Sent: Thursday, November 18, 2004 9:14 AM To: [EMAIL PROTECTED] Subject: innodb data file grew beyond the specified max size in config Hi all I converted my 6GB MyISAM database to Innodb using ALTER TABLE table TYPE = INNODB. There was no errors in the err log and database is working ok. But after stopping mysql server It refuse to start again complaining: 041118 16:55:45 mysqld started 041118 16:55:45 [Warning] Asked for 1048576 thread stack, but got 126976 InnoDB: Error: auto-extending data file /data/mysql_4.1_ibdata/ibdata1 is of a different size InnoDB: 779008 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 32000 pages, max 128000 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 041118 16:55:45 [ERROR] Can't init databases 041118 16:55:45 [ERROR] Aborting 041118 16:55:45 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 041118 16:55:45 mysqld ended The server enlarged the initial tablespace file and made it 12GB regardless my settings of 2000M max for one ibdata file. It should split them into 2G pieces , correct? Is this a bug or I am missing something? I am running Mysql 4.1.7 on Linux Here is my config file: [mysqld] port= 3307 socket = /usr/local/mysql/mysql.sock pid-file= /usr/local/mysql/mysql.pid datadir = /data/mysql_4.1_data skip-locking key_buffer_size = 64M max_allowed_packet = 2M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 16M net_buffer_length = 2M thread_stack = 1M max_connections = 100 query_cache_type = 1 maximum-query_cache_size = 24M thread_cache = 8 thread_concurrency = 2 server-id = 1 innodb_data_home_dir = /data/mysql_4.1_ibdata/ innodb_data_file_path = ibdata1:500M:autoextend:max:2000M innodb_log_group_home_dir = /data/mysql_4.1_iblog/ innodb_log_arch_dir = /data/mysql_4.1_iblog/ innodb_buffer_pool_size = 64M innodb_additional_mem_pool_size = 8M innodb_log_file_size = 16M innodb_log_buffer_size = 4M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 -- Hristo Chernev --- ÁÅÇÏËÀÒÅÍ ëè÷åí ÔÀÊÑ íîìåð çà âñåêè àáîíàò íà Mail.bg http://mail.bg -- 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: error starting mysql 4.1.7 cannot create/write to /root/tmp
perror 13 System error: 13 = Permission denied The mysql user cannot read the /root filesystem. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Gail Lange [mailto:[EMAIL PROTECTED] Sent: Thursday, November 18, 2004 10:23 AM To: [EMAIL PROTECTED] Subject: error starting mysql 4.1.7 cannot create/write to /root/tmp Hello I have just installed mysql 4.1.7 on Mandrake 10.0 (final). All went smoothly. However, when I issue the following command: /usr/local/mysql/bin/mysqld_safe --user=mysql I get the error: Can't create/write to file /root/tmp/ibLmfg7w (Errcode: 13) When I examine the error file in /usr/local/mysql/var it says it cannto read /root and it cannot write to /root/tmp If I give others (beside root) r permission to /root and rw permissions to /root/tmp, it start up fine. I have looked as startup options for the mysqld_safe as well as the /etc/my.cnf file but have not found anyway to alter where it wants to write its temp files. Can anyone please help? Thanks, Gail -- 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: Optimizing MySQL
Look at Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Increase tmp_table_size = 64M: it's used to stop going to disk and some internal mysql operations. Handler_read_rnd_next 58229817 Your tables are not index properly, your doing a lot of table scans. Your biggest perf. Gain will come from changing your schema and or optimizing your queries. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Shaun [mailto:[EMAIL PROTECTED] Sent: Thursday, November 18, 2004 10:56 AM To: [EMAIL PROTECTED] Subject: Optimizing MySQL Hello, I was wondering if a more knowledgeable person could help me out with my configuration and let me know how I could further optimize MySQL. Here's the hardware on my dedicated server: Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz Processor #1 speed: 2400.152 MHz Processor #1 cache size: 1024 KB Memory 512 MB Here's what I get for the command SHOW STATUS: Aborted_clients247 Aborted_connects483 Bytes_received 531539854 Bytes_sent 503095410 Connections 450758 Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_commit 0 Handler_delete 27837 Handler_read_first 670529 Handler_read_key 285579436 Handler_read_next 394084433 Handler_read_prev 680815 Handler_read_rnd5230552 Handler_read_rnd_next 58229817 Handler_rollback 0 Handler_update 384098 Handler_write 77442968 Key_blocks_used 50333 Key_read_requests 1081940322 Key_reads 45598 Key_write_requests 66458416 Key_writes 41372551 Max_used_connections 154 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables256 Open_files 323 Open_streams 0 Opened_tables 1022 Questions 30428972 Qcache_queries_in_cache0 Qcache_inserts0 Qcache_hits 0 Qcache_lowmem_prunes0 Qcache_not_cached 0 Qcache_free_memory 0 Qcache_free_blocks 0 Qcache_total_blocks 0 Rpl_status NULL Select_full_join 268 Select_full_range_join0 Select_range 66211 Select_range_check 0 Select_scan 151459 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 2 Slow_queries 15783 Sort_merge_passes 0 Sort_range 476962 Sort_rows 5241809 Sort_scan 283556 Table_locks_immediate31443397 Table_locks_waited 20243 Threads_cached 4 Threads_created 2423 Threads_connected5 Threads_running 1 Uptime 771502 Here are the complete contents of my my.cnf file [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 log-slow-queries=/var/log/slow-queries.log set-variable = long_query_time=1 safe-show-database Thanks a lot for your help! http://www.hdtv-info.org http://www.entertainment-news.org http://www.political-news.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Behind Firewall
3306 DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: A. Clausen [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 1:21 PM To: MySQL Mailing List Subject: MySQL Behind Firewall We are running MySQL 3.23.58-max-nt and are preparing to put the server it's running on behind a firewall. What ports do I have to leave open to the outside world so that outside customers can still access their databases, run queries, manage databases, etc? -- A. Clausen[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql-4.1.7 and client library - Client does not support authentication protocol requested by server; consider upgrading MySQL client
You need to reinstall your mysql api library to talk to mysql-4.1.7: 4.1.7 has a different auth implementation that is more secure then the 3.23 mysql protocol which your using. I suggest you link against the C-api that comes with 4.1.7. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 11:38 AM To: [EMAIL PROTECTED] Subject: Mysql-4.1.7 and client library - Client does not support authentication protocol requested by server; consider upgrading MySQL client They are successful to install mysql-4.1.7 on o.s. solaris 8 thanks to the suggestions of Gleb Paharenko. Now when use webmin (last version 1.170) o phpmyadmin (last version -2.6.0-pl2) I have this problem: DBI connect failed : Client does not support authentication protocol requested by server; consider upgrading MySQL client From shell it works all the solution to the problem exists? Or it is better install the version 4.0.21? Thank's Alessio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
gcc 3.4
Has anyone does any benchmarks with mysql, AMD and gcc 3.4? Gcc 3.4 is suppose to support AMD specific instructions giving a boost in performance. DVP Dathan Vance Pattishall http://www.friendster.com
RE: Undefined symbols compiling against 4.1.7 on Solaris 2.8
Are you linking against an old library binary? DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: V. M. Brasseur [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 03, 2004 10:19 AM To: MySQL Listserv Subject: Undefined symbols compiling against 4.1.7 on Solaris 2.8 We're trying to get 4.1.7 to play nicely with our code, but something isn't cooperating. First of all, the MySQL installation: Version 4.1.7, compiled from source using GCC 3.3. The source compile is required because we need a lot of default settings to use our own paths. This is the configure used for the compile (some values altered here to protect the innocent): CC=gcc CFLAGS=-O3 \ CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti \ ./configure --prefix=/path/to/mysql \ --libexecdir=/path/to/mysql/bin \ --with-mysqld-user=ouruser \ --with-unix-socket-path=/path/to/mysql.sock --with-tcp-port=ourport \ --without-docs --without-bench --with-extra-charsets=complex \ --enable-thread-safe-client --enable-local-infile \ --localstatedir=/path/to/mysql/data \ --with-prefix=/path/to/mysql \ --with-low-memory --enable-assembler --disable-shared It appears to function well on its own. However, when attempting to compile a program which requires libmysqlclient.a... compiling -o progname /path/to/progname.c Undefined first referenced symbol in file __floatdisf /path/to/mysql/lib/libmysqlclient.a(libmysql.o) __floatdidf /path/to/mysql/lib/libmysqlclient.a(libmysql.o) __cmpdi2 /path/to/mysql/lib/libmysqlclient.a(libmysql.o) ld: fatal: Symbol referencing errors. No output written to progname compile:error=256,command=compiling -o progname /path/to/progname.c It's worth noting that we are only experiencing this problem on Solaris 2.8. Another 4.1.7 installation on OSF1 5.1 is chugging along nicely with no complaints. Google contains many references to these undefined symbols, encountered mostly when compiling other products. Yet none of those references contain an actual SOLUTION to the problem. Could someone out there with a bit more ld/gcc/C API experience than I have (which is not setting the bar high, I assure you) lend a hand dispersing these clouds? Many thanks, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql does not respond
Strace the thread to see what it's cycling on. Sending a HUP signal should shut it down. Since max is a dynamic binary ie not statically built you might have some weird operation with your environment. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Ginger Cheng [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 03, 2004 11:09 AM To: [EMAIL PROTECTED] Subject: mysql does not respond Hello, MySQL Gurus, I have a mysql server (4.0.20) that has been flaky these days. I can't connect to it by 'mysql -p' or shutdown thru mysqladmin. It just hangs there forever without doing anything. I tried to 'ps -Aef | grep mysql' and sees nothing but the mysqld_safe and the mysqld_max thread initiated by mysqld_safe, and of course, the mysqladmin thread to shutdown mysql as it just hangs in there, ie, no client connected to mysql server that is preventing it from termination. Can anyone give me some help on this? Is there anyway I can find out what is going on in there or shut it down cleanly without having to worry about having defective files? Thanks a lot -- ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication basics
DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: ian douglas [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 02, 2004 9:30 AM To: [EMAIL PROTECTED] Subject: Replication basics Hi everyone, I've been browsing the online archives for a while and haven't found as much data as I'd like to feel really comfortable about replication. I've been a pretty busy developer in terms of using MySQL, but never so much in the way of administration. Also, I've only ever had to deal with a single machine until about a month ago when I was asked to extend our database to multiple machines for security and redundancy. As it stands right now, I have 3 machines running MySQL, set up as hosts db1, db2 and db3. db1 is a master db2 and db3 are set as slaves, and replicate data just fine with the premise that we'd like to add numerous slave machines at any time. Problems I'm having: 1. If a new database is created on db1, this is not replicated on db2 and db3. Should my permissions be GRANT FILE on *.* ... or is there a better way to automate duplicating this database on the slaves? Yes put in you're my.cnf on the master binlog-ignore-db=mysql,test this means the master will replicate any event to any database other then mysql, test. 2. If a database exists on all three machines already and I create a table on db1, this is also not replicated on db2 or db3. Why not? Are you explicitly replicating a set of tables from your slaves via replicate-do-table=foo.bar? 3. I haven't checked yet whether ALTER TABLE ... commands have worked across the replication. Yes they do. 4. Darn RedHat and their logrotate utility: db1 was having MySQL restart once a week, which created db1.001, db1.002, db1.003, etc., every time it restarted, yet db2 and db3 also running logrotate and having MySQL restart because of it, were not updating their master.info files to point at the .002 or .003 or .004 files whenever db1 was restarted. This also happens if db1 reboots. Stop the logrotate script. 5. In the event of power failure, or system failure, if db1 is offline, how can I set the systems so either db2 or db3 becomes a master? And if db1 comes back online later, could I set it as a slave to whichever other machine became a master? Can this be automated, or will it always require manual intervention? Use NDB or write your own load / failover software. 6a. I rewrote my Perl applications to connect to db1 and return a handle I call $dbh_w for any SELECT/INSERT/UPDATE/DELETE queries, and to connect to any of db1/db2/db3 for any SELECT queries as $dbh_r. However, we want to use some third-party software that contains a few MB of PHP code, and only connects to the database one time - I'm not sure how much time to allocate (to tell my CTO/CEO) to rewrite the entire application the same way I did my Perl applications. Any advice? 6b. My Perl scripts are 90% reading data back from the database, the PHP scripts are more like 80% writing to the database so being able to load-balance the writing to the database farm is ideal. In my current scenario, the PHP application can only write to db1, which will ultimately cap out the machine. Surely there's a better way than making two connections ($dbh_w/$dbh_r) to the database for each copy of my Perl or PHP processes running? Look at DBI::Multiplex 7. Finally, would 'fake replication' work if the MySQL database files were on a RAID system and mounted via NFS to multiple machines to actually run the MySQL engine? The tables we use have a lot of auto_increment fields, and I'd be worried about data being corrupted or lost. No, not reliable. Locks would be ignored via NFS and mysql will detect that the index file changed on server b marking the record or index file as crashed. Thanks, Ian Douglas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql optimizing large table
My.cnf.huge is not good enough for your system specs. Calculate the Key efficiency from the show status command. I bet the key efficiency is less then 90% or so. In this case increase the key_buffer_size try 512M. A good stat for a proper key_buffer_size in the sum of all index files block size. This would be optimal since the index remains in memory. Increase your tmp_table_size to 64 MB your prob going to tmp_table and mysql uses this buffer for some internal optimizations. Also try increasing range alloc block size a little bit, you might see a 5% perf boost. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Richard Bennett [mailto:[EMAIL PROTECTED] Sent: Thursday, October 21, 2004 5:48 AM To: [EMAIL PROTECTED] Subject: mysql optimising large table Hi, I have a table containing logfiles in mysql v4.0, myISAM. The table has about 8.5 million records. I'm using the my_huge.cnf file on mandrake10 Linux with 1 gig ram and 250gig HD space. Some Info: Space usage : Type Usage Data 3,063 MB Index 660,855 KB Total 3,708 MB Row Statistic : Statements Value Format dynamic Rows 8,781,134 Row length ø 365 Row size ø 443 Bytes Next Autoindex 8,781,135 Creation Oct 14, 2004 at 09:23 PM Last update Oct 20, 2004 at 11:57 AM Last check Oct 14, 2004 at 09:34 PM Indexes : Keyname Type Cardinality Field PRIMARY PRIMARY 8781134 id originalID UNIQUE 8781134 originalID databaseName INDEX 9 databaseName origID INDEX 8781134 origID destinationcode INDEX 8625 destinationcode finaldestination INDEX 2195283 finaldestination datetime INDEX 8781134 datetime Normally i'd like to be able to get statistics from the database in 1month chunks (about 1 million records) but if I do a: SELECT count( * ) FROM `table` WHERE datetime BETWEEN '2004-09-01 00:00:00' AND '2004-10-01 00:00:00' It will return the count: 1372668, but it takes 2 or 3 minutes to do this. If I add any other (indexed) criteria it becomes even slower. I have noticed if I just request 1 or 2 days' records, the result comes fast, but once the count gets over 10 or so, everything slows down. My own solution at the moment is to make temporary tables for each month, as things seem to stay fast with less than 2mil. records in a table. Does anyone have any advice on how to optimise this setup? Thanks, Richard. PS, some extra mysql info: (sorry for the long post) Server variables and settings Variable Global value back log 50 basedir / binlog cache size 32768 bulk insert buffer size 8388608 character set latin1 character sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent insert ON connect timeout 5 convert character set datadir /var/lib/mysql/ default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1000 flush OFF flush time 0 ft boolean syntax + -()~*:| ft min word len 4 ft max word len 254 ft max word len for sort 20 ft stopword file (built-in) have bdb NO have crypt YES have innodb YES have isam YES have raid NO have symlink YES have openssl NO have query cache YES init file innodb additional mem pool size 1048576 innodb buffer pool size 8388608 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb file io threads 4 innodb force recovery 0 innodb thread concurrency 8 innodb flush log at trx commit 1 innodb fast shutdown ON innodb flush method innodb lock wait timeout 50 innodb log arch dir ./ innodb log archive OFF innodb log buffer size 1048576 innodb log file size 5242880 innodb log files in group 2 innodb log group home dir ./ innodb mirrored log groups 1 innodb max dirty pages pct 90 interactive timeout 28800 join buffer size 131072 key buffer size 402653184 language /usr/share/mysql/english/ large files support ON local infile ON locked in memory OFF log OFF log update OFF log bin ON log slave updates OFF log slow queries OFF log warnings OFF long query time 10 low priority updates OFF lower case table names 0 max allowed packet 1047552 max binlog cache size 4294967295 max binlog size 1073741824 max connections 100 max connect errors 10 max delayed
RE: Replication / Purge Logs
No look at Relay_Master_Log_File and Exec_master_log_pos for the position of that master log file. The Master_Log_File indicates what position the IO thread is on. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Marvin Wright [mailto:[EMAIL PROTECTED] Sent: Thursday, October 21, 2004 4:01 AM To: [EMAIL PROTECTED] Subject: Replication / Purge Logs Hi, Just wanted to check something from the documentation. The recommended procedure for removing the bin logs on the master is On each slave server, use SHOW SLAVE STATUS to check which log it is reading. Obtain a listing of the logs on the master server with SHOW MASTER LOGS. Determine the earliest log among all the slaves. This is the target log. If all the slaves are up to date, this will be the last log on the list. Make a backup of all the logs you are about to delete. (The step is optional, but a good idea.) Purge all logs up to but not including the target log. When I do a show slave status, I should be looking at the Master_Log_File field ? Thanks. Marvin Wright Flights Developer Lastminute.com [EMAIL PROTECTED] +44 (0) 207 802 4543 ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- 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: Partial Row Reads?
DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- So, is there a faster way to insert/index the data? Would a different table or index type improve performace? Use Load data from infile .. IGNORE ... u might get a better insert speed increase. A different table and a different index / compound index would improve performance from an insert to a select perspective. above query, according to explain), the current behavior makes it reads 29548800 complete rows, which is 275.4 GB of data to read, even though the desired return is about 1/2500th of that (112.7 MB). If a range covers more then 30% of the table a table scan is performed, instead of an index scan. Any/all suggestions, comments, even flames are welcoome :) Thanks in advance! ken Look at some my.cnf options. You can tell mysql to use keys more often the table scans with a var called max_seeks_keys=100 // something like that == = Diplomacy is the weapon of the Civilized Warrior - Hun, A.T. Ken Gieselman [EMAIL PROTECTED] System Administrator http://www.endlessknot.com/~kgieselm Endlessknot Communications http://www.endlessknot.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]
RE: mysql_config (4.0.21) returning the wrong socket location
No it's not a bug. Look up the DBI documentation and change the mysql_sock= location in your code OR in /etc/my.cnf define the mysqld.sock file to be stored in /tmp. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Caron, Christian [mailto:[EMAIL PROTECTED] Sent: Thursday, October 21, 2004 10:54 AM To: MySQL - General (E-mail) Subject: RE: mysql_config (4.0.21) returning the wrong socket location When I start MySQL, it does create the socket in /usr/local/mysql/. When I (or PHP, or Perl) ask mysql_config about the socket, it returns /tmp/mysql.sock... nrn6# ls -l /usr/local/mysql/mysql.sock srwxrwxrwx 1 mysqlmysql 0 Oct 21 09:30 /usr/local/mysql/mysql.sock nrn6# ls -l /tmp/mysql.sock /tmp/mysql.sock: No such file or directory nrn6# /usr/local/mysql/bin/mysql_config --socket /tmp/mysql.sock Any idea? I reinstalled MySQL using the --with-unix-socket-path=/usr/local/mysql/mysql.sock option in the configure string and mysql_config now returns the socket path correctly (I removed the socket option from the my.cnf). I guess mysql_config should always return the real path to the socket, that is the one from the installation files or the my.cnf (that should overwrite the default one). nrn6# /usr/local/mysql/bin/mysql_config --socket /usr/local/mysql/mysql.sock Is it a bug? Christian -- 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: Fulltext doesn't seem to find certain text
That might be a word defined in the stopword file, excluding full text lookup on. Stop words are words like The As In If And so on. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: joe mcguckin [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 10:55 AM To: [EMAIL PROTECTED] Subject: Fulltext doesn't seem to find certain text If I perform a fulltext search for 'foo', it won't match text like 'foo, inc'. Why? Mysql 3.23.52 -- Joe McGuckin ViaNet Communications 994 San Antonio Road Palo Alto, CA 94303 Phone: 650-213-1302 Cell: 650-207-0372 Fax: 650-969-2124 -- 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: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
Friendster has for lack of better words HAVE A LOT of Opterons. In fact we have become experts with configuring what would be best with mySQL + Linux + opterons. I have a ton of benchmarks for various kernels and configs on local disk and or SAN configs. Just to give you some really basic stats we do more then 1 billion queries per day on less servers then Live Journal (source from mySQL conference). If I get permission from my bosses to release the benchmarks I will send it to the list. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Donny Simonton [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:15 PM To: 'Brian Abbott'; [EMAIL PROTECTED] Cc: 'Miles Keaton' Subject: RE: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE? MySQL released this a few weeks ago. http://www.mysql.com/news-and-events/press-release/release_2004_27.html As far as personal benchmarks, it's fast. Real fast. With a quad Xeon (which was more expensive than the quad opteron) our master server had a normal load of 2-3. With the the quad opteron it's less than .25. Donny -Original Message- From: Brian Abbott [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 4:32 PM To: [EMAIL PROTECTED]; 'Donny Simonton' Cc: 'Miles Keaton' Subject: RE: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE? Do you guys have metrics on this that you would be willing to share? We are looking at upgrading to the Opteron (from the Xeon) at the moment. Any information would be very helpful. Brian Abbott -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:21 PM To: Donny Simonton Cc: [EMAIL PROTECTED]; 'Miles Keaton' Subject: Re: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE? On Tue, Sep 14, 2004 at 03:05:07PM -0500, Donny Simonton wrote: I can verify that a quad opteron 2.2 runs about a million times better than a quad xeon 3.06. The opteron can handle more than 3 gigs of memory which is a 32 bit limitation. Right now in my quad opteron we have 32 gigs of memory and MySQL is using 16.8 gigs of the memory. We run fedora core 2, with the rpm built by MySQL. We don't run anything else any longer. And we've had good but limited experiences so far with 64 bit FreeBSD 5 on amd64 (also a quad w/32GB). Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.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: IN operator
IN is a fantastic operator, but there are some limitations especially memory wise. Check out this algorithm Say you're using an Integer with an average of 8 digits, i.e. in the 100s millions, now you send an in list of say 20 of these 8 digits numbers. Since the data is passed to mysql as a string, the parser has to allocate memory for 160 bytes (20 * 8 bytes) + 19 bytes for each comma. 339 bytes BAH that's nothing right? Well, these are bytes allocated outside of a key buffer, thus if your key buffer is set to 1.9 GB on a 32 bit system, your application has many of these IN list passed to it, mysql will crash because it just hit the 2 GB limit. Does this explain your issue, no not necessarily but it's good to add and might explain some weird experiences. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Monet [mailto:[EMAIL PROTECTED] Sent: Monday, September 13, 2004 10:22 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: IN operator Hi there, I used very likely statement last week to update one table. My IN value is around 20. I checked the manual and there is nothing about any limitation on IN values. However, when i was running it, it worked very well sometimes, while sometimes, the query crashed in the middle and i have to REPAIR table. I've not figure out the reason of the crash yet. but i think you should be aware of it. Monet --- Oliver Hirschi [EMAIL PROTECTED] wrote: Hi people Due to MySQL does not support inner-selects, I generate a string (I programm java-client) with the values I used in an IN-operator for an update onto a mySQL database. The statement looks like this: UPDATE layer SET State=1 WHERE fpObjectID IN (1,3,4,5,20,34,56,24,56,11,45) Now, the question came up if there is a maximum of values or length in an IN operator which can used on mySQL? Does anybody know something about that? Thanks Regards -- Oliver Hirschi http://www.FamilyHirschi.ch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.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]
RE: Innodb Message Lock wait timeout exceeded; Try restarting transaction
Basically the application can get this message because another process has a
lock on the rows that the delete needs to cover for a time period then your
lock_wait_timeout. Is there some cron process? Is the table index properly?
What your average query transaction?
Active your slow query log to see what queries are taking a long time to
execute and are covering many rows-look for table scans.
DVP
Dathan Vance Pattishall http://www.friendster.com
-Original Message-
From: Tucker, Gabriel [mailto:[EMAIL PROTECTED]
Sent: Monday, September 13, 2004 9:35 AM
To: Mysql General (E-mail)
Subject: Innodb Message Lock wait timeout exceeded; Try restarting
transaction
Hello All
V4.0.16 on Sun Sparc 5.8
Innodb_lock_wait_timeout = 50.
A application using jboss got this error a few days ago. The SQL being
used, AFAIK, was a simple delete from table where date = {date}.
I am trying to determine why this happened. Searching back in the MySQL
lists archives, I was unable to find anything that I thought could help.
Any ideas of troubleshooting this problem would be appreciated.
Thanks - Gabe
There are no problems, only solutions.
Gabe Tucker
Bloomberg LP
(609) 750 6668 - P
(646) 268 5681 - F
--
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: Error 2013
http://dev.mysql.com/doc/mysql/en/Error-handling.html I would look at your wait_timeout settings, if the mysqldump slept for greater then this setting then this could be the cause of your error. Why would the connection sleep? It has to flush the buffer to disk or STDOUT. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Emmett Bishop [mailto:[EMAIL PROTECTED] Sent: Monday, September 13, 2004 8:25 AM To: [EMAIL PROTECTED] Subject: Error 2013 Howdy all, I got this error while running a routine mysqldump last night: Error 2013: Lost connection to MySQL server during query when dumping table `contact_log` at row: 41245 I didn't find any good information on this error at mysql. Is there a page there that lists all error codes? I found one such page just for INNODB errors, but that doesn't help me much. Google didn't seem to have much either (mainly people asking the same question I'm asking now). Any insight on how to trouble shoot this error would be greatly appreciated. Cheers, Tripp __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- 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: mysqld_multi different server versions
DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: sean c peters [mailto:[EMAIL PROTECTED] Sent: Monday, August 09, 2004 1:43 PM To: [EMAIL PROTECTED] Subject: mysqld_multi different server versions So am i correct in thinking that i wont be able to use mysqld_multi for running two different server versions? This shouldn't be a problem, I dont think i'll need it. Just wanted to check if im missing something. This is not entirely correct. A basedir definition will allow you do execute the safe_mysqld in the directory installed. thanks sean peters [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: Kernel 2.4 and 2.6
2.6 - IO scheduler has some major problems. The DeadLine and Anticipatory schedulers underperforms compared to 2.4 Linus scheduler. In theory they should rock, but there are some major problems with it. Currently the 2.6 VM rocks and is way to fast for the 2.6 IO schedulers. On top of that they are buggy. Use SUSE 2.4 or RedHat 7.3 2.4.23aa_vm DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Batara Kesuma [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 27, 2004 9:39 PM To: [EMAIL PROTECTED] Subject: Kernel 2.4 and 2.6 Hi, I use MySQL 4.0.20 on my replication (slave) server. I noticed that when I use kernel 2.4.26 SMP, the slave runs very slow (it can't catch up with master). My machine has dual CPUs with HT. But if I change to kernel 2.6.6 SMP, everything just runs fine, the slave can catch up easily with master. Both of the kernels are from Debian. Anyone has same experience? I just feel curious. Regards, Batara -- 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: Inserting records from one table to another respecting a condition
INSERT INTO Suggestion SELECT partnumber,qty from Sales Limit 1,100; DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED] Sent: Thursday, July 29, 2004 2:01 PM To: [EMAIL PROTECTED] Subject: Inserting records from one table to another respecting a condition Hello all, I have two mysql tables. They have a slightly different structure but share comom columns. I need to get the top 100 sold parts ( partnumbers and quantities) from table Sales and insert into table Suggestion if I issue a query : SELECT partnumber,qty from Sales order by qty desc limit 1,100 I would satisfy my need of retrieving the top 100 sold Items from Sales. Then, I need to insert into Suggestion so that I can have an estimate suggestion of purchase. How can I achieve it? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: tests comparing AMD64 to Xeon MySQL performance?
I tested both platforms on a SAN every day for 26 days 18 hours a day straight. I came to this conclusion. The kernel does make a huge difference in the two platforms. Especially on the chipset patches and the disk IO subsystem. If you go with AMD do not use 2.6.7 or ext3. EXT3 has a bug in it and its use with O_DIRECT, and 2.6.6+ IO scheduler is not enterprise ready, although by description it sounds like it is. The VM system is to fast for the scheduler. Use 2.4 SUSE for AMD. If you need to run RedHat DO NOT RUN THE RedHat AS 3.0 ISO, run RedHat with SUSEs kernel in 64 bit mode. RedHat AS back ported some 2.6 features and the disk IO scheduler is NOT an Advance This is the fastest config that our team has come up with. It's nearly 3 times faster then a XEON with 4GB of ram and 5 times faster with 8GB of ram. As many of you know, Friendster was very slow, due to our AMD solution and some mySQL optimizations Friendster is FAST. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Miles Keaton [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 27, 2004 1:48 PM To: [EMAIL PROTECTED] Subject: tests comparing AMD64 to Xeon MySQL performance? We've got a really high-load MySQL server and are planning to get a new server. Has anyone seen tests comparing performance of MySQL on AMD64 versus Xeon CPUs? -- 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: Dual Master Configuration
-Original Message- From: Free Grafton - CCB [mailto:[EMAIL PROTECTED] Sent: Monday, May 24, 2004 11:29 AM To: [EMAIL PROTECTED] Subject: Dual Master Configuration Can someone show me how a configuration file (my.cnf) would look to have dual MySQL masters. Its more then just my.cnf changes-your application needs logic as well to handle dual masters. A---B Use log-slave-updates, logbin, serverid. A replicates from B and B replicates from A, they need to have distinct serverids to avoid cycles. -- 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: Indexes ignored when using SELECT foo FROM a, b?
Use force index to force the index lookup on foo_id
If that doesn't work try analyze table on that table and run the explain
again.
The OR will not allow you to use a compound index but the primary key or 1st
key-foo_id should be used. I just noticed that your table definition foo_id
is not defined as a primary key, so running analyze table will get things
into perspective.
DVP
Dathan Vance Pattishall http://www.friendster.com
-Original Message-
From: Eamon Daly [mailto:[EMAIL PROTECTED]
Sent: Monday, May 17, 2004 7:32 AM
To: [EMAIL PROTECTED]
Subject: Indexes ignored when using SELECT foo FROM a, b?
I have a table structured like so:
CREATE TABLE `foo_equivalency` (
`foo_id` smallint(6) NOT NULL default '0',
`type` enum('a_id','b_id','foo_id') NOT NULL default 'foo_id',
`id` smallint(6) NOT NULL default '0',
KEY `foo_id` (`foo_id`),
KEY `type` (`type`)
) TYPE=MyISAM
I'm using a SELECT to pull all of the equivalent foo_id's
from another table, bar, like so:
SELECT bar.foo_id, foo_equivalency.foo_id FROM bar, foo_equivalency
WHERE
foo_equivalency.foo_id IN (367,365,327,269,197,387,379,361,331) AND
(type = 'a_id' and id = bar.a_id) OR
(type = 'b_id' and id = bar.b_id) OR
(type = 'foo_id' and id = bar.foo_id)
foo_id is a primary key in table bar, and I created test
indexes on a_id and b_id:
PRIMARY KEY (`foo_id`),
KEY `a_id` (`a_id`),
KEY `b_id` (`b_id`)
EXPLAIN reveals that no indexes are being used:
+-+--+---+--+-+--+
--
+-+
| table | type | possible_keys | key | key_len | ref |
rows
| Extra |
+-+--+---+--+-+--+
--
+-+
| bar | ALL | PRIMARY,a_id,b_id | NULL |NULL | NULL |
269
| |
| foo_equivalency | ALL | foo_id,type | NULL |NULL | NULL |
2931
| Using where |
+-+--+---+--+-+--+
--
+-+
I'm unclear as to why none of the indexes apply, not even
the PRIMARY on foo_id. Is it the ORs that blow everything
away? Can I work around it?
It occurs to me that I could split foo_equivalency into 3
tables (foo_equivalency_by_a, foo_equivalency_by_b, and
foo_equivalency_by_foo), rather than using the enum and a
conditional, but that seems really unwieldy and slow (3
selects and then a UNION). Suggestions? I'm on MySQL 4.0.18,
by the way, so subselects aren't an option.
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 773 913-0970
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select distinct year from unix timestamp
R u sure your printing out the correct array (hash) field? Did you connect to the db? Is mysql_error reporting an error? DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of T. H. Grejc Sent: Sunday, May 16, 2004 11:36 AM To: [EMAIL PROTECTED] Subject: Select distinct year from unix timestamp Hello, I'm trying to select all distinct years from a unixtimestamp field in MySQL database (3.23.56). I have a query: SELECT DISTINCT YEAR(date_field) As theYear FROM table but PHP gives me an empty array. What am I doing wrong? TNX -- 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: Counting multiple tables
SHOW TABLE STATUS to get the count of each table, then your application adds the number in the Rows field from each of the tables returned. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Gustavo Andrade [mailto:[EMAIL PROTECTED] Sent: Sunday, May 16, 2004 3:10 PM To: [EMAIL PROTECTED] Subject: Counting multiple tables I want to know if its possible to count the total records of multiple tables: Example: I have 3 tables. I want to know the total records of each table using only 1 query. Is that possible? _ Quer ter um fórum para seu clan de Starcraft/BroodWar, Counter-Strike, Warcraft ou outros. entre em http://www.arena-star.com.br/forum/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: avoiding Locked threads
-Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Jon Drukman Sent: Thursday, May 13, 2004 3:21 PM To: [EMAIL PROTECTED] Subject: avoiding Locked threads I've got a very high traffic discussion forum database that is constantly running into a problem with lots and lots of threads in the Locked state. i was under the impression that MySQL could update/insert and select from the same table at the same time, but it doesn't seem to be the case. all tables are MyISAM. the machine is a dual xeon 3.2G with 2G of RAM now but i am upgrading it to 4G. NOTE: 4GB only helps because of system cache, mySQL in 32 bit cannot go beyond 2GB with out some experimental tweak that are separate from mySQL. the database is on a 15K RPM SCSI RAID0+1. the cpu load and io load all looks pretty good. here's my my.cnf: [mysqld] set-variable= query_cache_size=384M set-variable= key_buffer=384M set-variable= max_allowed_packet=4M set-variable= table_cache=64 set-variable= sort_buffer=8M set-variable= record_buffer=8M set-variable= thread_cache=8 set-variable= tmp_table_size=128M set-variable= thread_concurrency=4 the above is for solaris only set-variable= myisam_sort_buffer_size=128M set-variable= max_connections=1800 set-variable= max_connect_errors=10 set-variable= wait_timeout=120 set-variable= max_binlog_size=5 set-variable= long_query_time=5 server-id= 1 log-bin=/var/opt/mysql/db2-binlog skip-innodb log-error=/var/opt/mysql/db2-errlog This is on a separate drive? here's a sample of some of the locked threads at the moment: INSERT INTO MSGPOST VALUES (NULL, '931046', 'leon is...br/b(SPOILERS)/bbr/-br/-br/-br/ | INSERT INTO MSGPOST VALUES (NULL, '943738', 'All I got is from the British. It should be the same th | INSERT INTO MSGPOST VALUES (NULL, '951538', '(i)Don\'t forgot that Florina can BARELY hold a slim la | INSERT INTO MSGPOST VALUES (NULL, '955788', 'it stands for cyberathletic amateur leaguebr/---br/ | INSERT INTO MSGPOST VALUES (NULL, '971210', 'I\'m getting a job at a place that sells DnD books and | SELECT * FROM MSGPOST WHERE MsgPostId = 4466518 SELECT * FROM MSGPOST WHERE MsgPostId = 4466519 SELECT * FROM MSGPOST WHERE MsgPostId = 4466523 SELECT * FROM MSGPOST WHERE MsgPostId = 4466524 SELECT * FROM MSGPOST WHERE MsgPostId = 4466526 SELECT * FROM MSGPOST WHERE MsgPostId = 4466527 SELECT * FROM MSGPOST WHERE MsgPostId = 4466532 SELECT * FROM MSGPOST WHERE MsgPostId = 4466534 we've tried using MERGE tables on some of the real busy ones to break up the updates but we're still seeing locks doing SELECT on the merged table. I assume that your msgPostId is a primary key? any ideas appreciated! Try setting low-priority-updates and delay-key-write=ALL Your running into a concurrency issue, the only other quick fix is to use innodb, but your blobs will kill you in disk space. -jsd- -- 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: InnoDB Questions
-Original Message- From: Lou Olsten [mailto:[EMAIL PROTECTED] Sent: Thursday, May 13, 2004 6:45 AM To: [EMAIL PROTECTED] Subject: InnoDB Questions Need someone with some insight or experience with InnoDB (Heikki?? :-) a) Where does InnoDB store all of this information (such as the deleted rows)? In the InnoDB tablespace? In the innodb transaction logs, usually 2 of them are created with an archive log b) How long is it stored? It's flushed on commit and/or every few seconds c) Can we (users) control any of this? Sort of but not as fine grain as it seem your indicating. 2) Another question I have is surrounding fragmentation. Is there a way I can monitor my InnoDB tables to see the level of fragmentation on the table and/or indexes? No, other then looking at show table status and looking at pages free. Look up innodb start up options on mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB filesystem
-Original Message- From: Tim Cutts [mailto:[EMAIL PROTECTED] Sent: Thursday, May 13, 2004 7:11 AM To: MySQL List Subject: Re: InnoDB filesystem On 13 May 2004, at 3:34 pm, Dan Nelson wrote: Pros: performance and bypassing the filesystem cache. MySQL can't use all that memory itself, so it makes sense to allow the OS to cache as much disk space as possible in the memory that MySQL can't use directly? It depends, if your datafile is less then 16 GB then the system cache can help, but fill up the innodb_buffer_pool you'll get better performance. Think of innodb as being its own virtual filesystem. If you have 16GB it's probably a 64 bit OS, and mysql is available in 64 bit. Tim -- 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: InnoDB filesystem
-Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Thursday, May 13, 2004 4:03 PM To: Dathan Vance Pattishall Cc: 'Tim Cutts'; 'MySQL List' Subject: Re: InnoDB filesystem On Thu, May 13, 2004 at 04:51:27PM -0700, Dathan Vance Pattishall wrote: I think that the problem is that it's *not* a 64 bit OS. It's just an Intel 32bit box with 4GB of memory. And sine MySQL doesn't do PAE, it'll never see that extra memory. Intel box with 4GB? It is possible with a patch like hugemem in Linux but 4GB should only be used 2^32 = 4GB. - The hugemem patch for instance allows you to use all 16 GB but at a performance penalty. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ESRI GIS products support lacking in MySQL???
Look at some of the features of 4.1.1 http://dev.mysql.com/doc/mysql/en/Spatial_extensions_in_MySQL.html -Original Message- From: Douglas Phillipson [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 1:41 PM To: [EMAIL PROTECTED] Subject: ESRI GIS products support lacking in MySQL??? I hope this isn't an inappropriate list for this... I have been trying to get ESRI (ww.esri.com) to consider supporting MySQL as a backend Database. They informed me that MySQL doesn't have all the features required to support ESRI's products. Since the alternatives are Oracle and SQL Server, and Oracle is prohibitively expensive, I'm wondering if your MySQL AB has considered contacting ESRI to see what their requirements are. ESRI is the standard for GIS systems. It would seem to me that if your database could be used by ESRI that you might get tens of thousands of new customers all over the world. I'm wondering if anyone else has probed exactly what is missing in MySQL that makes it inappropriate for use by ESRI. Regards Doug P -- 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: check for certain characters
Off of the top of my head you can basically do a combination of all letters in big or (use IN) list. It should be pretty fast. I'm personally leaning to using REGEXP in mySQL yet, that would match the letters in a string and not exclude others, unless explicitly told to. Using a REGEXP is slow. -- DVP -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 5:42 PM To: [EMAIL PROTECTED] Subject: check for certain characters hi, I have a field which is a genome sequence and I need to check if each of the entries made for the sequence field contains only a,t,c or g in the string and no other characters. how will i give the query??? thanks, liz -- 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: my.cnf setup
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 7:42 AM To: Dathan Vance Pattishall Subject: RE: my.cnf setup Thanks for the feedback! I have made the changes you suggested. I do have a question about the slow query log though. I added it to my my.cnf file as = [mysqld] port= 3306 log-slow-queries = /usr/local/mysql/slowlog socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 Increase your tmp_table_size to 32M Additionally ensure that your indexes on your tables are correct. If you need some help with that send the query + table structure to the list. = Is this correct? Will it just make the file called slowlog? So far it hasnt done anything. And i did restart the server fyi. Anything you can clear up? Conner -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, May 10, 2004 2:23 PM To: [EMAIL PROTECTED] Subject: my.cnf setup I am running a mysql server off a Dell 2650. Dual 2.8Ghz Intel Xeon processors 1 Gig of RAM The MySQL data comes up rather slowly. Would like some suggestions on my my.cnf file if you had a minute. here is what I have | Handler_read_first | 2| | Handler_read_key | 9080 | | Handler_read_next | 35 | | Handler_read_prev | 1764 | | Handler_read_rnd | 723 | | Handler_read_rnd_next | 63575| You are not indexing your table right. The read random next is to high. Turn on the slow query log to find out what query is the problem. | Handler_rollback | | | Handler_update | | | Handler_write | 154 | | Key_blocks_used| 51 | | Key_read_requests | 13370| | Key_reads | 41 | | Key_write_requests | 67 | | Key_writes | | | Max_used_connections | 4| | Not_flushed_key_blocks | | | Not_flushed_delayed_rows | | | Open_tables| 18 | | Open_files | 38 | | Open_streams | | | Opened_tables | 24 | | Questions | 9971 | | Qcache_queries_in_cache| 173 | | Qcache_inserts | 173 | | Qcache_hits| 9665 | | Qcache_lowmem_prunes | | | Qcache_not_cached | 8| | Qcache_free_memory | 32908680 | | Qcache_free_blocks | 1| | Qcache_total_blocks| 365 | | Rpl_status | NULL | | Select_full_join | 8| | Select_full_range_join | | | Select_range | 66 | | Select_range_check | | | Select_scan| 84 | | Slave_open_temp_tables | | | Slave_running | OFF | | Slow_launch_threads| | | Slow_queries | | | Sort_merge_passes | | | Sort_range | | | Sort_rows | 723 | | Sort_scan | 16 | | Ssl_accepts| | | Ssl_finished_accepts | | | Ssl_finished_connects | | | Ssl_accept_renegotiates| | | Ssl_connect_renegotiates | | | Ssl_callback_cache_hits| | | Ssl_session_cache_hits | | | Ssl_session_cache_misses | | | Ssl_session_cache_timeouts | | | Ssl_used_session_cache_entries | | | Ssl_client_connects| | | Ssl_session_cache_overflows| | | Ssl_session_cache_size | | | Ssl_session_cache_mode | NONE | | Ssl_sessions_reused| | | Ssl_ctx_verify_mode| | | Ssl_ctx_verify_depth | | | Ssl_verify_mode| | | Ssl_verify_depth | | | Ssl_version| | | Ssl_cipher | | | Ssl_cipher_list| | | Ssl_default_timeout| | | Table_locks_immediate | 239 | | Table_locks_waited
RE: my.cnf setup
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, May 10, 2004 2:23 PM To: [EMAIL PROTECTED] Subject: my.cnf setup I am running a mysql server off a Dell 2650. Dual 2.8Ghz Intel Xeon processors 1 Gig of RAM The MySQL data comes up rather slowly. Would like some suggestions on my my.cnf file if you had a minute. here is what I have | Handler_read_first | 2| | Handler_read_key | 9080 | | Handler_read_next | 35 | | Handler_read_prev | 1764 | | Handler_read_rnd | 723 | | Handler_read_rnd_next | 63575| You are not indexing your table right. The read random next is to high. Turn on the slow query log to find out what query is the problem. | Handler_rollback | 0| | Handler_update | 0| | Handler_write | 154 | | Key_blocks_used| 51 | | Key_read_requests | 13370| | Key_reads | 41 | | Key_write_requests | 67 | | Key_writes | 0| | Max_used_connections | 4| | Not_flushed_key_blocks | 0| | Not_flushed_delayed_rows | 0| | Open_tables| 18 | | Open_files | 38 | | Open_streams | 0| | Opened_tables | 24 | | Questions | 9971 | | Qcache_queries_in_cache| 173 | | Qcache_inserts | 173 | | Qcache_hits| 9665 | | Qcache_lowmem_prunes | 0| | Qcache_not_cached | 8| | Qcache_free_memory | 32908680 | | Qcache_free_blocks | 1| | Qcache_total_blocks| 365 | | Rpl_status | NULL | | Select_full_join | 8| | Select_full_range_join | 0| | Select_range | 66 | | Select_range_check | 0| | Select_scan| 84 | | Slave_open_temp_tables | 0| | Slave_running | OFF | | Slow_launch_threads| 0| | Slow_queries | 0| | Sort_merge_passes | 0| | Sort_range | 0| | Sort_rows | 723 | | Sort_scan | 16 | | Ssl_accepts| 0| | Ssl_finished_accepts | 0| | Ssl_finished_connects | 0| | Ssl_accept_renegotiates| 0| | Ssl_connect_renegotiates | 0| | Ssl_callback_cache_hits| 0| | Ssl_session_cache_hits | 0| | Ssl_session_cache_misses | 0| | Ssl_session_cache_timeouts | 0| | Ssl_used_session_cache_entries | 0| | Ssl_client_connects| 0| | Ssl_session_cache_overflows| 0| | Ssl_session_cache_size | 0| | Ssl_session_cache_mode | NONE | | Ssl_sessions_reused| 0| | Ssl_ctx_verify_mode| 0| | Ssl_ctx_verify_depth | 0| | Ssl_verify_mode| 0| | Ssl_verify_depth | 0| | Ssl_version| | | Ssl_cipher | | | Ssl_cipher_list| | | Ssl_default_timeout| 0| | Table_locks_immediate | 239 | | Table_locks_waited | 0| | Threads_cached | 1| | Threads_created| 5| | Threads_connected | 4| | Threads_running| 1| | Uptime | 1444 | ++--+ my.cnf as follows: [client] password= password port= 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs [EMAIL PROTECTED] bin]# cat /etc/my.cnf [client] password= guest port= 3306 socket = /tmp/mysql.sock # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M Raise higher if your replicating blobs. table_cache = 512 sort_buffer_size = 8M This is too high, you might run out of memory. This is a per thread allocation. Set it to 2M. read_buffer_size = 2M add read_rnd_buffer_size = 2M since your indexes are bad. myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 This is not needed. It only works for Solaris. There
RE: use LIMIT and get total count
Yes Use SQL_CALC_ROWS And then SELECT FOUND_ROWS() -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 2:12 PM To: [EMAIL PROTECTED] Subject: use LIMIT and get total count Hi, In MySQL, I do: mysql SELECT * FROM my_table WHERE myconditions LIMIT 10; It retrieves 10 rows of data that meets 'myconditions' from my_table. Is it possible to get the total row count that meets 'myconditions' without another query? Thanks. Dexin __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- 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: Fulltext searching
Check to see if beyond is in your stopword file. -Original Message- From: Richard Baskett [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 2:12 PM To: MySQL Subject: Fulltext searching Ok having some problems with MySQL's fulltext search. I have the fields that I need fulltext indexed, everything seems to be working correctly, but for some reason when I search for beyond looking for an item called: Beyond Heaven yoga Day Spa It doesn¹t find it.. I am searching in boolean mode by the way. but if I search for heaven it does find it. I have no idea why it will not find that first word... Actually it finds it when using all the words, except when I use beyond Now it just occurred to me.. if there is only one result.. will it not show that there was a result? Or is it something completely different? Thanks! Rick What lies behind us and what lies before us are tiny matters compared to what lies within us. - Oliver Wendell Holmes -- 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: Need correct 'order by' syntax where field does not contain NULL
WHERE id = id = '$category.' AND active ='y' AND order is not NULL ORDER by order,title -Original Message- From: Eve Atley [mailto:[EMAIL PROTECTED] Sent: Monday, May 03, 2004 2:24 PM To: [EMAIL PROTECTED] Subject: Need correct 'order by' syntax where field does not contain NULL Hi. I had a MySQL DB set up and recently added a field 'order' to allow for exceptions in a web site menu heirarchy. Fields should be ordered by 'order' field first where it does not contain 'NULL', and then by field 'title'. I had this previously: select * from navigation WHERE id = '.$category.' AND active='y' ORDER BY title ...and now, when I put in: select * from navigation WHERE id = '.$category.' AND active='y' ORDER BY order, title ...my menu shows nothing. How can I write my statement to allow for ORDER by order where 'order' does not contain 'NULL', and then title? Thanks, Eve -- 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: InnoDB Load Problem
Run Show INNODB status. Look at -- BUFFER POOL AND MEMORY -- Total memory allocated 1299859045; in additional pool allocated 6113152 Buffer pool size 71936 Free buffers 59 Database pages 70898 Modified db pages 57113 Pending reads 1 Pending writes: LRU 0, flush list 0, single page 0 Pages read 379011342, created 2581822, written 233133461 58.62 reads/s, 0.12 creates/s, 61.24 writes/s == Buffer pool hit rate 981 / 1000 -Original Message- From: Emmett Bishop [mailto:[EMAIL PROTECTED] Sent: Friday, April 23, 2004 8:01 AM To: Dathan Vance Pattishall Subject: RE: InnoDB Load Problem I've been keeping tabs on this thread and would just like to know how to tell what the buffer pool ratio is. What is it a ratio of? What command do I run to take a look at it? Thanks, Tripp --- Dathan Vance Pattishall [EMAIL PROTECTED] wrote: Look at your fsync stat and your buffer pool ratio. You may get better performance out of use O_DIRECT since it does not double buffer your log writes. Next make sure your buffer pool ratio is close to 1 (100%), if not raise your bugger pool if you can. Additionally make sure you transaction logs are large like 1/2 your buffer pool. Also note if your doing many fast small queries set innodb_thread_conncurency high (cpu+ number of disk)*2 For the hardware portion, you might need to use elvtune to get better throughput for your hard drive or update the kernel to a kernel that supports better interaction with your hardware makeup. This all assumes that your queries are already optimized. -- DVP -Original Message- From: Marvin Wright [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 5:13 AM To: Mechain Marc; Marvin Wright; Dathan Vance Pattishall; [EMAIL PROTECTED] Subject: RE: InnoDB Load Problem Hi, To put the unique index on like you suggest is fine for this table but this table is just the top level of a hierarchy. table a has 1 record table b has 100's of records linked to 1 table a record table c has 100's of records linked to 1 table b record All the records in table b and c would need to be updated/deleted for a new record. It think this would be very time consuming, and the clients that are inserting are public internet users therefore I'd rather not slow these down. under load iostat -x 1 gives me this avg-cpu: %user %nice%sys %idle 38.500.00 18.00 43.50 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 104.00 552.00 31.00 39.00 1088.00 4728.00 544.00 2364.00 83.0962.20 1174.29 141.43 99.00 /dev/hda10.00 0.00 0.00 0.000.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda2 104.00 552.00 31.00 39.00 1088.00 4728.00 544.00 2364.00 83.0982.20 1174.29 75.71 53.00 /dev/hda30.00 0.00 0.00 0.000.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 44.500.00 16.50 39.00 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 6.00 838.00 1.00 58.00 64.00 7168.0032.00 3584.00 122.58 3.30 393.22 169.49 100.00 /dev/hda10.00 0.00 0.00 0.000.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda26.00 838.00 1.00 58.00 64.00 7168.0032.00 3584.00 122.5823.30 393.22 23.73 14.00 /dev/hda30.00 0.00 0.00 0.000.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 2.000.000.00 98.00 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 195.00 162.00 58.00 8.00 2080.00 1392.00 1040.00 696.00 52.6144.40 740.91 128.79 85.00 /dev/hda10.00 0.00 0.00 0.000.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda2 195.00 162.00 58.00 8.00 2080.00 1392.00 1040.00 696.00 52.6164.40 740.91 151.52 100.00 /dev/hda30.00 0.00 0.00 0.000.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 8.000.003.00 89.00 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/s
RE: Executing Pre-Written Commands
Paul book is killer, I have the 1st and second addition - but to answer your question the mySQL client can save commands, but if your looking for something in mySQL itself to do that it can't. To create tables you can do something like this. %mysql -hyourdb -uroot -pyourpass -f yourdatabase your ddl file The ddl file (text file) can contain any mysql SQL command including a create table statement. -- DVP -Original Message- From: Chris Stevenson [mailto:[EMAIL PROTECTED] Sent: Friday, April 23, 2004 6:48 AM To: [EMAIL PROTECTED] Subject: Executing Pre-Written Commands Anyone out there have any experience with the book MySQL by Paul DuBois? I'm having trouble figuring out how to save/resuse executed commands (primarily creating tables at this point). Perhaps I could bounce a few questions offline if you've got used this book before or think you can assist me regardless. Thank you and have a great day! Chris Stevenson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: update if insert fails
You can use replace into, but note it cannot do Replace into table VALUES (1,2,3,col4+1); -Original Message- From: Andy Ford [mailto:[EMAIL PROTECTED] Sent: Friday, April 23, 2004 3:59 AM To: [EMAIL PROTECTED] Subject: update if insert fails Hi Is there such a statement where, if the insert fails (due to a duplicate record) an update will happen Thanks Andy -- 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: query mysql data dictionary
SHOW KEYS FROM TABLE -Original Message- From: sbv chris [mailto:[EMAIL PROTECTED] Sent: Thursday, April 22, 2004 7:26 AM To: [EMAIL PROTECTED] Subject: query mysql data dictionary Hi, I'm trying to find a way to find the primary keys in a table and find constraints on a table by sql. I would like to later issue these sql statements through jdbc. hows this done in mysql? Regards, Sunil. _ Lose those love handles! MSN Fitness shows you two moves to slim your waist. http://fitness.msn.com/articles/feeds/article.aspx?dept=exercisearticle=e t_pv_030104_lovehandles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mySQL: Table locking problems when non-index keys used
Use indexes. Make sure your indexes are on the right side of the where clause ie. SELECT col1,col2 from tablewithproperindexes where col3=const_index_lookup. Or you can use a dirty read as your transaction model to help out a TINY bit. -- DVP -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, April 22, 2004 8:09 PM To: [EMAIL PROTECTED] Subject: mySQL: Table locking problems when non-index keys used Friends, Sorry to post this question again. I got a message saying that the server couldn't transfer this message to some groups. Also I didn't get any response to this question. We are using mysql 4.0.17 with innodb option. In a query, when a WHERE clause contains a non-indexed columns, it locks the entire table instead of row lock. Is there any solution apart from building index on each query key ? Is there a solution in any of the later versions ? With Best Regards, Ravi Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Patches
Do you want the binary or the src code? http://dev.mysql.com/downloads/index.html If you want the developer repo go here. http://dev.mysql.com/doc/mysql/en/Installing_source_tree.html -Original Message- From: Hassan Shaikh [mailto:[EMAIL PROTECTED] Sent: Friday, April 23, 2004 12:24 PM To: [EMAIL PROTECTED] Subject: Patches Where can I download patches for MySQL 4.0.17 from? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: My Unicode Woes - Plz Help!!!
4.0.18 does not support Unicode. The JDBC driver tries to set the UNICODE charset but can't because this mysql version cannot store Unicode data as a charset. If you used 4.1.1, which is alpha it supports the charset and correlations. My suggestion is this. Store the blobs on NAS as UNICODE text. mySQL is now used a lookups to the blob data in a particular even hashed directory structure on the NAS device that holds the UTF-8 or UCS data. If this is not available to you, then use 4.1.1 and don't use subselects, that is the most unstable portion of this branch. -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Stormblade Sent: Thursday, April 22, 2004 5:33 PM To: [EMAIL PROTECTED] Subject: My Unicode Woes - Plz Help!!! I'm posting this as a last ditch effort to get this working or at least find out what's going on and perhaps get a workaround if one exists. Platform: Windows XP SP1 Database: MySQL 4.0.18 JDBC Drv: 3.0.11 History: There is an existing website which uses ColdFusion/SQLServer. The owner of this website used Microsoft Word to create some text and he pasted this into a form field. This was inserted into the SQLServer database. Now, Here is what I have so far: 1. ColdFusion/SQLServer - Original site. Data entered into a form and inserted into the SQLServer Database. Retrieved with query and displayed on page. Unicode characters are displayed as their proper symbols. 2. JSP/SQLServer - New Site. Retrieved the data from the SQLServer database and displayed it. Same database as in #1. Same data and same query. This also worked fine. Unicode was displayed as their proper symbols. 3. JSP/MySQL - Exported the data to an Access Database. Opened database in Access to verify that the data and unicode were there. Unicode was displayed as proper symbols in Access. Imported into MySQL. Used Navicat, EMS MySQL Manager and SQLyog to view the data in MySQL. Navicat displayed the unicode in the data as blocks. EMS MySQL Manager displayed the unicode as their proper symbols. SQLyog displayed the unicode in the data as blocks. Same as in Navicat. So the data does contain non-Ascii characters and I'm assuming they are unicode. Now some further information of my setup. Default charset on database: latin1 Table DDL: CREATE TABLE `article` ( `ID` bigint(20) NOT NULL auto_increment, `Author` varchar(150) NOT NULL default '', `Title` varchar(150) NOT NULL default '', `Body` longtext NOT NULL, `Date` datetime NOT NULL default '-00-00 00:00:00', `Category` char(2) NOT NULL default '', PRIMARY KEY (`ID`), UNIQUE KEY `ID` (`ID`), KEY `Category` (`Category`) ) TYPE=InnoDB The Body column is the column that contains the unicode data. I am accessing the database from Java through the JDBC driver. My URL looks like this: jdbc:mysql://localhost/thedatabase?useUnicode=truecharacterEncoding=UTF-8 I played around with this. If I leave off the useUnicode and characterEncoding parameters what happens is all of the codes are just not displayed at all. With the above URL they are displayed as ?s. The JSP page encoding setting is set to UTF-8 which worked for when I was accessing SQLServer through their JDBC driver. I have downloaded an ODBC driver for MySQL so that I can try and see if perhaps it's the driver but right now I don't have an application to test it yet and I'll need to research to see how to do it in Java which would really allow me to test it. So I'm hoping someone can help me either figure out why it's not working as I would expect. Also I'm open to suggestions on how to handle this on the database/server side. Any alternate solutions have to be transparent to my client who enters this data from a web form usually copy and paste from Word. Thanks in advance for any help. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- 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: InnoDB Load Problem
Look at your fsync stat and your buffer pool ratio. You may get better performance out of use O_DIRECT since it does not double buffer your log writes. Next make sure your buffer pool ratio is close to 1 (100%), if not raise your bugger pool if you can. Additionally make sure you transaction logs are large like 1/2 your buffer pool. Also note if your doing many fast small queries set innodb_thread_conncurency high (cpu+ number of disk)*2 For the hardware portion, you might need to use elvtune to get better throughput for your hard drive or update the kernel to a kernel that supports better interaction with your hardware makeup. This all assumes that your queries are already optimized. -- DVP -Original Message- From: Marvin Wright [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 5:13 AM To: Mechain Marc; Marvin Wright; Dathan Vance Pattishall; [EMAIL PROTECTED] Subject: RE: InnoDB Load Problem Hi, To put the unique index on like you suggest is fine for this table but this table is just the top level of a hierarchy. table a has 1 record table b has 100's of records linked to 1 table a record table c has 100's of records linked to 1 table b record All the records in table b and c would need to be updated/deleted for a new record. It think this would be very time consuming, and the clients that are inserting are public internet users therefore I'd rather not slow these down. under load iostat -x 1 gives me this avg-cpu: %user %nice%sys %idle 38.500.00 18.00 43.50 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 104.00 552.00 31.00 39.00 1088.00 4728.00 544.00 2364.00 83.0962.20 1174.29 141.43 99.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda2 104.00 552.00 31.00 39.00 1088.00 4728.00 544.00 2364.00 83.0982.20 1174.29 75.71 53.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 44.500.00 16.50 39.00 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 6.00 838.00 1.00 58.00 64.00 7168.0032.00 3584.00 122.58 3.30 393.22 169.49 100.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda26.00 838.00 1.00 58.00 64.00 7168.0032.00 3584.00 122.5823.30 393.22 23.73 14.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 2.000.000.00 98.00 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 195.00 162.00 58.00 8.00 2080.00 1392.00 1040.00 696.00 52.6144.40 740.91 128.79 85.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda2 195.00 162.00 58.00 8.00 2080.00 1392.00 1040.00 696.00 52.6164.40 740.91 151.52 100.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 8.000.003.00 89.00 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 174.00 0.00 60.00 5.00 1856.008.00 928.00 4.00 28.6850.00 1235.38 147.69 96.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda2 174.00 0.00 60.00 5.00 1856.008.00 928.00 4.00 28.6870.00 1235.38 153.85 100.00 /dev/hda30.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda50.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 29.500.00 16.50 54.00 Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/hda 102.00 71.00 40.00 6.00 1088.00 616.00 544.00 308.00 37.04 5.60 671.74 193.48 89.00 /dev/hda10.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 /dev/hda2 102.00 71.00 40.00 6.00 1088.00 616.00 544.00 308.00 37.0425.60 671.74 163.04 75.00
RE: ERROR 1030: Got error 127 from table handler
There is a delete bug where frequent deletes can cause table corruption. Upgrade to 4.0.18 -- I believe its been fixed by now. -Original Message- From: Bhaskar Borthakur [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 1:30 PM To: [EMAIL PROTECTED] Subject: ERROR 1030: Got error 127 from table handler Hi I have been using MySQL server version 4.0.14 for the last six months in our project and it was running just fine. We use Linux AS 3.0 (kernel version 2.4.21-4.0.1.EL #1) However, since last week we started to get the following error : ERROR 1030: Got error 127 from table handler ( please look below). This happens when we are doing some load testing on our application server and there are around 2 records in the table. We are doing only inserts and deletes from the table at the total rate of around 90 operations per second. Its has happened numerous time since the last few days. Could anyone please let me know why this is happening and how do i go about solving this. If I do repair table table name, it says that all the records are corrupted since the pointer is outside data segment. Then it deletes all the rows. However what I am looking for is a prevention of this problem instead of a cure thanks a lot bhaskar [EMAIL PROTECTED] ~]$ mysql -uccd_user -pccd_user Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 63458 to server version: 4.0.14-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use ccd_chkp_db; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select * from USER_CACHE; ERROR 1030: Got error 127 from table handler mysql desc USER_CACHE; +---+--+--+-+-+--- + | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+--- + | info_type | tinyint(3) unsigned | | | 0 | | | user_addr | varchar(64) | | PRI | | | | domain| varchar(255) | | PRI | | | | ip_version| tinyint(3) unsigned | | | 0 | | | ip_addr | tinyblob | | | | | | port | smallint(5) unsigned | | | 0 | | | security_key_k1 | tinyblob | | | | | | security_key_k2 | tinyblob | | | | | | reg_ttl | int(10) unsigned | | | 0 | | | cseq | int(10) unsigned | | | 0 | | | call_id | varchar(255) | | | | | | timestamp | int(10) unsigned | | | 0 | | | vocoder | smallint(5) unsigned | | | 0 | | | protocol_ver | tinyint(3) unsigned | | | 0 | | | sdb_flag | tinyint(3) unsigned | | | 0 | | | qsk_ttl | smallint(5) unsigned | | | 0 | | | time_zone | smallint(5) unsigned | | | 0 | | | home_carrier_id | tinyint(3) unsigned | | | 0 | | | current_carrier_id| tinyint(3) unsigned | | | 0 | | | region_id | tinyint(3) unsigned | | | 0 | | | omd | varchar(38) | | | | | | PNOffset | smallint(5) unsigned | | | 0 | | | sid | smallint(5) unsigned | | | 0 | | | nid | smallint(5) unsigned | | | 0 | | | restriction_size | int(10) unsigned | | | 0 | | | restrictions | mediumblob | YES | | NULL| | | request_uri_user_addr | varchar(16) | YES | | NULL| | | request_uri_domain| varchar(253) | YES | | NULL| | | subscriberId | varchar(64) | | | | | +---+--+--+-+-+--- + 29 rows in set (0.04 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem
You need to flush privileges to get the new ones active or restart mysql. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 12:55 PM To: [EMAIL PROTECTED] Subject: problem hi, I reinstalled mysql again and did what the website said. C:\ C:\mysql\bin\mysql -u root mysql mysql DELETE FROM user WHERE Host='localhost' AND User=''; mysql FLUSH PRIVILEGES; mysql QUIT C:\ C:\mysql\bin\mysqladmin -u root password newpwd C:\ C:\mysql\bin\mysqladmin -u root -h host_name password newpwd instead of newpwd i wrote zilnoy instead of hostname i wrote zilsys then i said grant all privileges on genome_db.* to [EMAIL PROTECTED] identified by 'zilnoy'; i am getting an error like : Access denied to user: '@localhost' to database 'genome_db' what should i do??? -- 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: reuse results set
Store the result set in a array of hashes and loop through that array to
build the rest of the form.
--
DVP
-Original Message-
From: Scott Swaim [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 21, 2004 12:17 PM
To: Mysql
Subject: reuse results set
I have a form that does the following
? while ($speaker = db_fetch_object($qid_speaker)) { ?
option value=? pv($speaker-first_name)
? ? pv($speaker-last_name) ?
? pv($speaker-first_name) ? ?
pv($speaker-last_name) ?
? } ?
/select
$qid_speaker = db_query(SELECT first_name, last_name FROM people WHERE
speaker = 'Y' ORDER BY last_name);
I am using this in a form select drop down box.
What I need to do is reuse this results set in another drop down box. but
I can not figure out how to do this.
If I use the mysql_free-result($qid_speaker) and then try to do the query
again I get a mysql_fetch_object(): 12 is not a valid MYSQL result
resource.
TIA
Scott Swaim
Quality Corps, Inc.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql using big two tables in BSD
Use explain [INSERT YOUR SELECT STATEMENT] Verify your using keys and if you are using keys ensure that the join say eq_ref and the first key is const. If this is the case then this is the fastest possible join you can do for the statement below. -- DVP -Original Message- From: kamlesh pandey [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 1:10 PM To: [EMAIL PROTECTED] Subject: mysql using big two tables in BSD Hi, I'm new to mysql. I have two big tables ,tableA4GB and tableB1GB. both tables around 10 million rows,each. tableA has following two cols. user_id(varchar(255) PRIMARY KEY user_data1(MEDIUMTEXT) entries looks like user1 xyz user2 x1.. tableB has following cols user_data2:varchar(50) user_id(varchar(255)). tableB is indexed on (user_data2,user_id) and on user_id. there is no primary key in tableB since,it can have entries like A user1 B user1 A user2 C user2. I NEED to SELECT data from both tables as follwoing SELECT tableA.userid,tableA.user_data1 from tabelA,tableB where tableB.user_data2=myinput AND tableB.user_id=tableA.user_id It was good while the table size was small,but since the table size is big and growing,the query is becoming slow. I'm using mysql_use_result(). to get the result. Any suggestion either on client query or server tuning will be helpful. thanks = Don't worry about the world coming to an end today. It's already tomorrow in Australia. ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°¤º° Do You Yahoo ! ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°¤º° __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- 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: 64M too much?
Depends on your result set size and your query cache hit rate, which all can be figured out from a show status command. -- DVP -Original Message- From: Mark [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 3:43 PM To: [EMAIL PROTECTED] Subject: 64M too much? Hello, Just a quick question: running MySql 4.0.18, is a 64M query cache too large? Thanks, - Mark -- 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: bajar mysql
If I'm reading my Spanish correctly http://www.mysql.com/products/mysql/ Its not going to get much better then this. -Original Message- From: María de los Angeles Vargas [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 8:48 AM To: [EMAIL PROTECTED] Subject: bajar mysql De donde puedo bajar la ultima versión de mysql. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Uninstall mysql
Left click on the binary and look at the access permissions. -Original Message- From: vasanthsena x [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 2:50 PM To: [EMAIL PROTECTED] Subject: Uninstall mysql Hi, I tried to uninstall mysql from win Xp.however it did not remove everything completely.I tried to remove the files manually but specifically it does not let me delete mysqld.exe.it says access denied. Can anyone help me?I would to reinstall mysql. S. __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- 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: Converting tables to innoDB
No, there is no reason to covert them to INNODB unless you want an ACID compliant table format, and have disk space to spare. MYISAM support a wide variety of mySQL featured, such as full text searches, a key buffer, and a few other nice things such as a being able to get a row count in ms time. INNODB is cool for transactions / ACID compliant features and large amounts of concurrency. -- DVP -Original Message- From: Brad Tilley [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 2:44 PM To: [EMAIL PROTECTED] Subject: Converting tables to innoDB Hello Mysqlers, I have a few small DB's (less than 10,000 entries per table) that track computer inventory in a mid-sized organization. Currently, all of the tables are MyISAM. Is there any compelling reason to convert these to InnoDB? We're not having any performance or size issues... everything works great right now. I'm just hearing how InnoDB is the only way to go today, any tips on whether or not to switch? Thanks, Brad -- 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: ERROR 1030: Got error 127 from table handler
http://dev.mysql.com/doc/mysql/en/News-4.0.x.html -Original Message- From: Bhaskar Borthakur [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 4:45 PM To: Dathan Vance Pattishall; [EMAIL PROTECTED] Subject: RE: ERROR 1030: Got error 127 from table handler At 04:42 PM 4/21/2004 -0700, Dathan Vance Pattishall wrote: There is a delete bug where frequent deletes can cause table corruption. Upgrade to 4.0.18 -- I believe its been fixed by now. Is there a bug fix list that I can lookup on the net to make sure that this error has been fixed? thanks bhaskar -Original Message- From: Bhaskar Borthakur [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 1:30 PM To: [EMAIL PROTECTED] Subject: ERROR 1030: Got error 127 from table handler Hi I have been using MySQL server version 4.0.14 for the last six months in our project and it was running just fine. We use Linux AS 3.0 (kernel version 2.4.21-4.0.1.EL #1) However, since last week we started to get the following error : ERROR 1030: Got error 127 from table handler ( please look below). This happens when we are doing some load testing on our application server and there are around 2 records in the table. We are doing only inserts and deletes from the table at the total rate of around 90 operations per second. Its has happened numerous time since the last few days. Could anyone please let me know why this is happening and how do i go about solving this. If I do repair table table name, it says that all the records are corrupted since the pointer is outside data segment. Then it deletes all the rows. However what I am looking for is a prevention of this problem instead of a cure thanks a lot bhaskar [EMAIL PROTECTED] ~]$ mysql -uccd_user -pccd_user Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 63458 to server version: 4.0.14-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use ccd_chkp_db; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select * from USER_CACHE; ERROR 1030: Got error 127 from table handler mysql desc USER_CACHE; +---+--+--+-+- +--- + | Field | Type | Null | Key | Default | Extra | +---+--+--+-+- +--- + | info_type | tinyint(3) unsigned | | | 0 | | | user_addr | varchar(64) | | PRI | | | | domain| varchar(255) | | PRI | | | | ip_version| tinyint(3) unsigned | | | 0 | | | ip_addr | tinyblob | | | | | | port | smallint(5) unsigned | | | 0 | | | security_key_k1 | tinyblob | | | | | | security_key_k2 | tinyblob | | | | | | reg_ttl | int(10) unsigned | | | 0 | | | cseq | int(10) unsigned | | | 0 | | | call_id | varchar(255) | | | | | | timestamp | int(10) unsigned | | | 0 | | | vocoder | smallint(5) unsigned | | | 0 | | | protocol_ver | tinyint(3) unsigned | | | 0 | | | sdb_flag | tinyint(3) unsigned | | | 0 | | | qsk_ttl | smallint(5) unsigned | | | 0 | | | time_zone | smallint(5) unsigned | | | 0 | | | home_carrier_id | tinyint(3) unsigned | | | 0 | | | current_carrier_id| tinyint(3) unsigned | | | 0 | | | region_id | tinyint(3) unsigned | | | 0 | | | omd | varchar(38) | | | | | | PNOffset | smallint(5) unsigned | | | 0 | | | sid | smallint(5) unsigned | | | 0 | | | nid | smallint(5) unsigned | | | 0 | | | restriction_size | int(10) unsigned | | | 0 | | | restrictions | mediumblob | YES | | NULL | | | request_uri_user_addr | varchar(16) | YES | | NULL | | | request_uri_domain| varchar(253) | YES | | NULL | | | subscriberId | varchar(64) | | | | | +---+--+--+-+- +--- + 29 rows in set (0.04 sec) -- MySQL General Mailing List For list archives: http
RE: unstable mysql connection
This might work for you although I would just fix my connection ;) http://freshmeat.net/projects/mysql_proxy/ -Original Message- From: Alexander Newald [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 5:36 PM To: [EMAIL PROTECTED] Subject: unstable mysql connection Hello, I've an application client that stores it's data in a mysql server. Client and server (Both linux) are on diffrent hosts, I can't change the application but I have root access on both server and client. The problem: Sometimes the lan connection has paket drops (about 1%) and the connection to the mysql server is lost. Unfortunately the client starts his work from the beginning. How can I add some sort of proxy between the client and the server on the client host that accept the mysql query from the client like a mysql server, forwards this query to the real mysql server as often as needed to get the answer over the bad lan connection and than returns the result of the clients query to the client like a normal mysql server would do? Anyway, if someone knows a complete diffrent solution that works without changing the client application (and of course the buggy lan) I will be happy! Thanks, Alexander Newald -- 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: Creating Relationships
Use INNODB and use FK constraints / cascades. -Original Message- From: LastingImages [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 6:33 PM To: [EMAIL PROTECTED] Subject: Creating Relationships OK I am a umber newbie. And I have decided to change that. I was wondering how do you create relationships between tables? If you know where it talks about it in the MySQL reference manual you can direct me there. But I have not been able to find it. So this is my first question. Thank you! Bryan Goodwin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySqld [problem]
Can you start it with strace running and see where it fails? I suspect its an issue with finding the group file. If this is the cause you will need to build mySQL yourself. Btw please send arch / version / system hardware spec. -- DVP -Original Message- From: Mozzi [mailto:[EMAIL PROTECTED] Sent: Monday, April 19, 2004 12:24 PM To: [EMAIL PROTECTED] Subject: MySqld [problem] When trying to start mysqld I get the following error #safe_mysqld Starting mysqld daemon with databases from /var/lib/mysql /usr/bin/safe_mysqld: line 275: 32649 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 040419 22:08:23 mysqld ended I am on debian woody and upgraded with apt-get apgrade about two hours ago. I am @ my wits end, so sorry if someone saw one of my previous posts. Mozzi -- --- No Microsoft software was used, or employees hurt in creating this email. -- 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: XFS or ReiserFS?
Use JFS, unless your database server is going to have many many (on the order of 10K) small tables use Reiser. JFS hands down is faster for various mixed environments of reading / writing / random access etc. -- DVP -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Monday, April 19, 2004 2:21 PM To: Don Dachner; [EMAIL PROTECTED] Subject: Re: XFS or ReiserFS? Don Dachner wrote: Which filesystem is better for a MySQL server? I can't see the difference between them. All I need is a LFS that will handle a MyISAM table above the 4GB size level. Running Linux 2.4 (or can install the latest ver if necessary) on 32 bit Intel platform. My Linux books says XFS is the leader in supporting Access Control Lists...is that a consideration for a MySQL server? Thanks, Don I've always 'felt' ( ie haven't benchmarked, but this has just been my impression ) that ReiserFS was faster than XFS. And no, Access Control Lists is not important for a MySQL server. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
RE: InnoDB Load Problem
-Original Message- From: Marvin Wright [mailto:[EMAIL PROTECTED] Sent: Monday, April 19, 2004 8:55 AM To: [EMAIL PROTECTED] Subject: InnoDB Load Problem Hi, Apologies in advance for the large post but I've tried to explain my problem as much as I can. I've read the InnoDB tuning page ( http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html ), there are a few things I could try but I'd like to make sure that I'm on the right tracks. * Firstly I could put more queries into a single transaction. That will help, increase your innodb_log_file_size and changed your flush method to O_DIRECT - this avoids double writes. You can verify the benefit of the change by looking at SHOW INNODB STATUS in the LOG IO section. Also, look at your buffer pool hit ratio. If its close to 100% then there is no need to raise the allocation of the buffer_pool. Next, make sure you disk layout is okay. Ensure that your not saturating on IO. Next make sure your not doing queries like SELECT count(*) FROM table -- this is bad. Finally look at which queries are slow. Make sure that your indexes are proper. One last note if your doing many many small quiries think about tweaking your innodb_thread_conncurrency setting -- Ignore innodb_thread_io_conncurrency unless you want to change code. -- Dathan Vance Pattishall Sr. Database Engineer / Sr. Programmer Friendster Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Cluster
You can also use pae for any one process to address 4 GB of ram on a 32 bit system. -- DVP -Original Message- From: Tim Cutts [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 14, 2004 2:05 PM To: Adam Erickson Cc: [EMAIL PROTECTED] Subject: Re: MySQL Cluster On 14 Apr 2004, at 10:57 pm, Adam Erickson wrote: (This is probably not the best place for this post, but here goes...) The (soon to be released) MySQL cluster software docs use a sample cluster node configured with Dual Xeons and 16GB of ram. MySQL has never been able to use more than 2 gigs of system memory (on 32 bit platforms.) With MySQL Cluster, will MySQL finally start using the memory paging trick Oracle and others have been using for years? Otherwise, what is the point of having 16 gigs of ram for one MySQL server? Disk cache. Tables which MySQL doesn't have in its own buffers but which nevertheless are frequently accessed will already be in RAM, and therefore faster to access. Even so, you'd probably do better with a 64 bit processor with that amount of memory. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- 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: autoincrement column
Try truncate table. It essentially drops the table and recreates it. -- DVP -Original Message- From: Arthur Radulescu [mailto:[EMAIL PROTECTED] Sent: Thursday, April 08, 2004 10:36 AM To: [EMAIL PROTECTED] Subject: autoincrement column Before switching to version 4.0.18 of MySQL the command delete from table was also reseting the autonincrement column value... It seems that this is not happening anymore after we have installed this version... Any idee about this problem? Is there any other way to reset this? Arthur -- 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: Corrupted relay log
All you had to do what CHANGE MASTER TO MASTER_LOG_POS=Exec_master_log_pos, MASTER_HOST=Master_host, MASTER_USER=Master_user, MASTER_LOG_FILE=Relay_Master_Log_File This essentially forces mySQL to redownload the relay logs and stay in sync. Its call rewinding the replication thread. -- DVP -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: Thursday, April 08, 2004 10:43 AM To: [EMAIL PROTECTED] Subject: RE: Corrupted relay log Here's what I did to fix it... Excert from MySql Manual... When you back up your slave's data, you should back up these two small files as well, along with the relay log files. They are needed to resume replication after you restore the slave's data. If you lose the relay logs but still have the `relay-log.info' file, you can check it to determine how far the SQL thread has executed in the master binary logs. Then you can use CHANGE MASTER TO with the MASTER_RELAY_LOG and MASTER_RELAY_POS options to tell the slave to re-read the binary logs from that point. This requires that the binary logs still exist on the master server. I performed these steps... Read info form relay-log.info file. Mysql: Slave Stop; Mysql: Change master to master_host = '192.168.1.10', Master_User = 'name', Master_Password = 'pass', Master_log_File = 'get this from relay-log.info', Master_log_pos = get this from relay-log.info; Mysql: Slave Start; Does anyone see a problem with this method? Thanks, Jeff -Original Message- From: Jeff McKeon Sent: Thursday, April 08, 2004 8:54 AM To: [EMAIL PROTECTED] Subject: Corrupted relay log Is there any way to fix a corrupted relay log on a slave? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb_buffer_pool_size limit
Try setting it to 4096M -- DVP -Original Message- From: Keith Thompson [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 23, 2004 12:46 PM To: [EMAIL PROTECTED] Subject: innodb_buffer_pool_size limit Is there a limit on what innodb_buffer_pool_size can be set in 4.0.17 (64-bit Solaris 9)? I tried setting it to 4G in my.cnf and SHOW VARIABLES says it's 0. -keith -- 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: mysqlhotcopy -- max number of tables
Can you send the error message? It might be a timeout problem which is a mySQL configuration issue. (Look at wait_timeout in my.cnf) -Original Message- From: Andrew Loughe [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 10:30 AM To: [EMAIL PROTECTED] Subject: mysqlhotcopy -- max number of tables The mysqlhotcopy perl script is failing fail when I try to back up a database containing more than about 600 tables. The script appears to be unable to lock that many tables before making the actual copy. Have others experienced this same problem? Is this a limitation of DBI, or of MySQL 3.23.58? Is there a known work-around? Thanks for any help! [ MySQL 3.23.58 , MyISAM tables, RH linux ] -- Andrew Loughe = NOAA/OAR/FSL/AD R/FS5 | email: [EMAIL PROTECTED] 325 Broadway | wwweb: www-ad.fsl.noaa.gov/users/loughe Boulder, CO 80305-3328 | phone: 303-497-6211 fax: 303-497-6301 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and PHPBB access problem
Your access permissions are not valid. Add user pete with a password to your grant tables as configured with PHPBB Or Add skip-grant-tables in you're my.cnf config file. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 11:09 AM To: [EMAIL PROTECTED] Subject: MySQL and PHPBB access problem Hope you guys can help a n00b, although I just got my MCSA. I have a server at my home network behind a router/switch. It's running Server2000 SP4. IIS is installed from the CD itself. I already have an ftp running for practice. That went well. I found phpbb 2.0.8 and downloaded it. I didn't install it at first. I also got MySQL 4.0.18 and Control Center 0.9.4. Those 2 installed ok. So did the ODBC driver from their website. I made a database named forumdb but didn't add anything to it. I also made sure it was connected. Then I installed Php 4.3.5 and phpMyAdmin 2.5.6. That seemed to go well. I added them to my web folder that IIS points to. I also added index.php to the Documents area under Default Webpage Properties. It is just under the top doc Default.htm. All of these installs I did from what each had recommended on their own website. Now I come to phpbb. Everything went in ok. Got to the Welcome to PHPBB 2 Installation through IE on the server itself. I put in the values I thought were correct but I get the following error: Warning: mysql_connect(): Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in F:\Website Home\phpbb2\db\mysql4.php on line 48 Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in F:\Website Home\phpbb2\db\mysql4.php on line 330 Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource in F:\Website Home\phpbb2\db\mysql4.php on line 331 phpBB : Critical Error Could not connect to the database Not sure what I'm doing wrong. I'm assuming a logon problem. I have tried various users from all 3 programs along with their passwords but still get the same error. Plus I had Dreamweaver MX installed and I couldn't change the phpbb config file to 666. I uninstalled DW and made .php be associated with notepad and the attribute for the file is not read only. But I still can't get the right click menu that displays like on their flash tutorial though. CHMOD is not there. I hope that isn't the problem. What I also need to know is where to find the information to enter in each line of the PHPBB Install page. DSN: I tried localhost and DC-1 and the server's IP. None worked. The database name is the one I made in MySQL. It is connected presently. Should it be turned off? The main problem seems to be that I don't know where to find the database user and password listed to add it to the form. Any other info you need to help find an answer I would be glad to post. Hope you guys can help. Thanks in advance. Pete Hinkley -- 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: group by order by rand() problem
Try seeding your rand. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 12:57 PM To: [EMAIL PROTECTED] Subject: group by order by rand() problem I have this table: mysql select * from banners; ++-+---+ | id | user_id | title | ++-+---+ | 1 | 1 | first banner | | 2 | 1 | second banner | | 3 | 2 | third banner | | 4 | 2 | forth banner | | 5 | 2 | fifth banner | ++-+---+ I would like to show a random banner for each user, something like this: first call ++-+---+ | id | user_id | title | ++-+---+ | 1 | 1 | first banner | | 3 | 2 | third banner | ++-+---+ second call ++-+---+ | id | user_id | title | ++-+---+ | 2 | 1 | second banner | | 4 | 2 | forth banner | ++-+---+ etc... I have tried with following query but the banner doesn't change while multiple calls: SELECT * FROM banners GROUP BY user_id ORDER BY RAND(); Can anyone help me? Thanks in advance, Alex -- 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: Questions about MySQL capability
This is all dependent on your OS's unlimit sizes, FS INODE limit, and mySQL table type. Generally is about 64K for databases and tables if you use myISAM / LINUX / EXT3 -- DVP -Original Message- From: Mark.he [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 9:42 PM To: [EMAIL PROTECTED] Subject: Qustions about MySQL capability Hi All, I have some questions about MySQL capability. 1. What's the max number of databases can be created in one MySQL server? 2. What's the max number of tables can be created in one database? 3. What's the best number of databases in one MySQL server? 4. What's the best number of tables in one database? All questions are based on RedHat 8 OS. If you are not sure of your answers, could you please tell me where I can get the answers? Thanks a lot for your concern. Mark.Ho -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: urgent help needed on replication on windows 2000
What version of mySQL are you using, 4.0.19 (not yet released) fixes something similar to what you have described. -- DVP -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 7:49 PM To: [EMAIL PROTECTED] Subject: urgent help needed on replication on windows 2000 Hi, I am working on mysql repliction on windows 2000. I am trying to make the slave as master when the master goes down. When I execute the io thread shutdown on slave after master goes down, it hangs. Even the slave server does not stop on executing the shutdown command using mysqladmin and root user. Regards, Anup Mahansaria -- 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: InnoDB Deadlock cannot find free slot for undo
Did you run out of disk space? If not what transaction model are you using? Is it repeatable read? If so, are you allowing the transaction to finish with a commit? If not your filling your transaction log and that's how your getting this message. Call commit on the session OR SET AUTOCOMMIT=1 on connection. Additionally set your innodb_deadlock_timeout var. -- DVP -Original Message- From: Bryan Heitman [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 2:53 PM To: [EMAIL PROTECTED] Subject: InnoDB Deadlock cannot find free slot for undo Looking for some help on tuning my InnoDB settings. I received a InnoDB deadlock. From analyzing show innodb status -- * There were a ton of processes in the active status not doing anything. * The log flushed up to was moving but the last checkpoint at was extremely old. Error in mysql error log scrolling repeatedly: 16:21:37InnoDB: Warning: cannot find a free slot for an undo log. Do you have too many active transactions running concurrently? version | 4.0.17-standard-log current innodb settings set-variable = innodb_buffer_pool_size=768M set-variable = innodb_additional_mem_pool_size=20M set-variable= innodb_log_file_size=256M set-variable= innodb_log_files_in_group=3 Any help would be appreciated. Best, Bryan Heitman [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: Managing Very Large Tables
Tips on managing very large tables for myISAM: 1) Ensure that the table type is not DYNAMIC but Fixed. = Issue the show table status command. = Look at Row Format = if Row Format != Dynamic the your ok else get rid of varchar type columns = Reason: Your myISAM table can grow only to 4GB then it will run out of space even if your file system allows files to grow past 4GB. 2) For selects avoid ranges i.e. SELECT * FROM BLAH WHERE column NOW() - INTERVAL 30 DAY == or increase range_alloc field in my.cnf 3) For pruning as described below, in a maintenance window run optimize table or myisamchk -r -S -a yourtable.MYI to get rid of deleted blocks. This will help keep your query speed consistent and disk utilization lower. 4) Ensure that mysql_safe is off so you can get the benefits of simulatenous reads or simulatenous writes. 5) add --low-priority-update to allow writes to happen in batches after reads have finished. -Original Message- From: Chad Attermann [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 30, 2004 9:42 AM To: [EMAIL PROTECTED] Subject: Managing Very Large Tables Hello, I am trying to determine the best way to manage very large (MyISAM) tables, ensuring that they can be queried in reasonable amounts of time. One table in particular has over 18 million records (8GB data) and is growing by more than 150K records per day, and that rate is increasing. Besides the obvious things like better hardware and load-balancing across multiple replicating databases, I am trying to determine how to keep these data sets optimized for fastest queries. In my particular situation, the most recent data is queried most often, and data over 30-45 days old is not queried much at all but still must remain accessible. Each record has an integer time column that is indexed for querying over periods of time. Currently I run a script regularly that moves records older than 45 days from tables in the main database into identical tables in another (archive) database running in the same server process. This seems to speed up the tables in the main database, but I realize that deleting records leaves holes in the tables, and that this slows inserts as well as makes it impossible to read and write concurrently from these tables. My question is, is it better to keep all of the data in the original tables to avoid holes, or is 'archiving' records to another database a wise approach? How much does the size of a table really affect performance when querying the more recent data? If archiving is reasonable, is there a way to optimize the tables to get rid of the holes without siginificantly impacting ongoing activity on these tables? Thanks for your time! Chad Attermann [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ibdata1 size
-Original Message- From: joe collins [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 30, 2004 1:26 AM To: MySQL Subject: ibdata1 size HI, the ibdata1file in mysql\data has reached a size of 10Mb in just over a week, the database I have set up is quite limited, only 7 tables with no more than 12 records in any one table (only evaluating database models on MySQL at the moment). I have done 4 MySQL dumps and some inserts / updates etc but all low volume. Is there some setting I need to have to keep the size of this file down, or is there a maintenance task to clear it out? You can turn autoextend off. Add another disk and extend another data file onto it as autoextend. Remember INNODB is a virtual file system, it also stores the index file (3.X - 4.0.X) in a single file. It also uses a page system (16KB by default), this page system does not map to the block disk device per say. Additionally as it rebalances the Btree it will expand and contract from time to time. In most cases it will expand unless you delete rows. Blobs are particularly affected from innodb. Use myISAM tables for blobs. What is the significance of this file? Any pointers would be greatly appreciated, as my database will be vastly increased in size and disk space may become an issue. Many thanks for your patience Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error 127 = Record-file is crashed
Yes let it finish. Never stop a repair in progress. Now myisamchk is rebuilding the index file since youre the table is extremely corrupt. Myisamchk can run faster if you set some buffer properties. Put this in youre my.cnf file for next time. [myisamchk] key_buffer=256M sort_buffer=256M read_buffer=2M write_buffer=2M - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: rmck [mailto:[EMAIL PROTECTED] --Sent: Tuesday, November 11, 2003 5:27 PM --To: Dathan Vance Pattishall --Subject: RE: Error 127 = Record-file is crashed -- --Thank you for the reply -- --Before you replied I killed the first myisamchk thinking I should do --REPAIR TABLE --Then your email came and I killed the REPAIR TABLE and --reran --myisamchk -rf Nov03. --Thats whats running now myisamchk -rf Nov03 -- --NOW I noticed my .MYI file is at 1024K: -- --[root]# ls -al Nov* ---rw-rw1 mysqlmysql8802 Nov 6 07:04 Nov03.frm ---rw-rw1 mysqlmysql8812359152 Nov 11 01:25 Nov03.MYD ---rw-rw1 mysqlmysql1024 Nov 11 16:33 Nov03.MYI ---rw-r--r--1 root root 67108864 Nov 11 17:05 Nov03.TMD -- --Man I really messed this up? Should I let this keep on running?? -- --Thank You --Rob -- ---Original Message- --From: Dathan Vance Pattishall [EMAIL PROTECTED] --Sent: Nov 11, 2003 4:19 PM --To: 'rmck' [EMAIL PROTECTED], [EMAIL PROTECTED] --Subject: RE: Error 127 = Record-file is crashed -- -- -- --- Dathan Vance Pattishall -- - Sr. Programmer and mySQL DBA for FriendFinder Inc. -- - http://friendfinder.com/go/p40688 -- -- -Original Message- From: rmck [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 3:17 PM To: [EMAIL PROTECTED] Subject: Error 127 = Record-file is crashed Hello, I have mysql-standard-4.0.16-pc-linux-i686 installed on a 4gig mem, 2cpu system, RH 9. I have a large table (Data records: 72426930) that is now giving me --this error Error 127 when doing selects... This is my first crash of any kind with Mysql. So I'm a liitle --confused on what I should do. The manual states use myisamchk, then you read further and it says use REPAIR TABLE... I'm confused can someone give me a 1 to end step on repairing this table?? At this point I brought my mysqld down. Tried running this: [root]# myisamchk -r Nov03 - recovering (with keycache) MyISAM-table 'Nov03' Data records: 72426930 myisamchk: error: Can't create new tempfile: '/opt/logs/Nov03.TMD' MyISAM-table 'Nov03' is not fixed because of errors Try fixing it by using the --safe-recover (-o) or the --force (-f) --option So I tried this: [root]# myisamchk -rf Nov03 [EMAIL PROTECTED] ip_logs]# myisamchk -rf Nov03 - recovering (with keycache) MyISAM-table 'Nov03' Data records: 72426930 5939000 It looks like its doing something my data dir and the table in question: -rw-rw1 mysqlmysql8812359152 Nov 11 01:25 Nov03.MYD -rw-rw1 mysqlmysql5346325504 Nov 11 14:52 Nov03.MYI -rw-rw1 mysqlmysql631242752 Nov 11 14:52 Nov03.TMD Please advise... -- --Good you turned off mysql and ran this command. If you didn't you would --just cause further corruption. Next the TMD file means that as myisamchk --repairs, this temp file above is appended as the datafile is compared to --the index file. This is to ensure that the source data file is not --modified (yet) in case of row loss. If you need to recover the lost rows --that occur during this repair (source data file will be stored in a .BAK --file) then it's possible. -- --So, what you need to do right now is wait till that 631242752 file size --equals the 8812359152 file size. -- --Myisamchk will report how many rows have been lost if any during the end --of the repair. -- -- Thanks Rob -- 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: newbie question on data accumulation
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: joffrey leevy [mailto:[EMAIL PROTECTED] --Sent: Wednesday, November 12, 2003 9:58 AM --To: [EMAIL PROTECTED] --Subject: newbie question on data accumulation -- --Hi all -- --Curious as to what happens after data is repeatedly --selected from a mysql table overtime. If the data is not fragmented from constant deletes, it will continue to stay at a constant fast rate. The table is put into a table cache to reduce the amount of opens, the data is stored in shared memory in a key buffer, thus the speed of mysql. If your table does not change, and the query is the same turn on the query cache, you'll see a 256 times speed increase. Does it --accumulate as junk data, stored at some location and --eventually slow down the database/program/server? The queries do eventually slow down if there are a lot of deletes that fragments your table. This can be repaired with an optimize table. --Does any purging have to take place? Don't know what you mean with this question. -- -- --Thanks --J -- -- --__ --Do you Yahoo!? --Protect your identity with Yahoo! Mail AddressGuard --http://antispam.yahoo.com/whatsnewfree -- --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]
bug in replication?
ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1597257529, event_type: 49 ERROR: Could not read entry at offset 240378281 : Error in log format or read error On the master it's reporting this error. The data_len is 1.48 GB which is an error. I know that this is not the case since I don't insert 1.4GB of data in one sql statement. At this offset, the binary log is corrupt. Also the size of the binary file is: (Note: size and position are related) 240378300 Nov 12 00:04 ef112-bin.060 I read someplace that this is due to the binlog pointer becoming displaced. I don't believe it's a big problem for me since only 19 bytes of data is missing and queries to this box are never = 19 bytes. I assume its some padding of data/or bad data. Unfortunately the only way that I can recover from a possible missed event on the master-is to read every row of the master and compare it with every row of the slave once the master and slave are in-sync. Anyone have better solution? - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688
RE: A question on INTERVAL
The parser does not execute the query before it parses it. Thus you cannot do that. Use Code, and or have an idea what the INTERVAL UNIT is prior to executing the query. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Erik Osterman [mailto:[EMAIL PROTECTED] --Sent: Wednesday, November 12, 2003 4:05 AM --To: [EMAIL PROTECTED] --Subject: A question on INTERVAL -- -- --I would like to do something like this -- --SELECT * FROM orders,subscriptions -- WHERE orders.subscription_id = subscriptions.id -- created created - INTERVAL subscriptions.term subscriptions.unit; -- --But I get ERROR 1064: You have an error in your SQL syntax near --'subscriptions.unit)' -- --If I replace subscriptions.unit to 'YEAR' it works fine. -- --The 'subscriptions' table has 2 columns: term (int) and unit (enum). --The enum types for unit are 'YEAR', 'MONTH', etc... -- --Anyone have any ideas on how to use MySQL's wonderful date time --functions --to accomplish what I am trying to do? I'd like to avoid using approximate --techniques such as using 86400 seconds for a day, since calculating --months --and years _accurately_ is very involved. -- -- --P.S. DATE_SUB(created, INTERVAL subscriptions.term subscriptions.unit) --had --the same parse error. -- -- --Regards, -- --Erik Osterman -- -- -- -- --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: After installing MySQL 4.0.16 (rpm), it did not create /etc/my.cnf ?
Create one. Look in your base directory under support-files Look for my-small.cnf - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Admin-Stress [mailto:[EMAIL PROTECTED] --Sent: Tuesday, November 11, 2003 11:44 AM --To: [EMAIL PROTECTED] --Subject: After installing MySQL 4.0.16 (rpm), it did not create --/etc/my.cnf ? -- --Hi, -- --My server is using RedHat 9.0. -- --I just CHANGED the mysql from 3.23.58 (rpm) to 4.0.16 (rpm). -- --What I did : -- -- # rpm -e all mysql 3.23.58 rpm -- # rm -rf /var/lib/mysql -- -- then install the 4.0.16 (rpm) -- -- # rpm -i MySQL-server MySQL-client ... MySQL-devel ... MySQL- --shared-compat -- --After this, -- --I can start the server. --I can change the root password using mysqladmin. --I can create database. -- --But I cant find /etc/my.cnf -- --is this normal ? -- --I verified by issuing: rpm -ql MySQL-server ... --But again, I did not see /etc/my.cnf -- --Anyone know what is the 'standard' my.cnf for MySQL 4.0.16 ? -- --Thanks, -- --__ --Do you Yahoo!? --Protect your identity with Yahoo! Mail AddressGuard --http://antispam.yahoo.com/whatsnewfree -- --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: Unix Socket Daemon
mySQL already does that look at /var/lib/mysql/mysql.sock=. You can add skip-networking to my.cnf not to allow tcp connections at all. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Arnoldus Th.J. Koeleman [mailto:[EMAIL PROTECTED] --Sent: Tuesday, November 11, 2003 12:29 PM --To: [EMAIL PROTECTED] --Subject: Unix Socket Daemon -- --I wanna bring up the mysql daemon on a unix domain socket. -- -- -- --Any Idea how?? -- -- -- --I don't wanna use the localhost TCP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Upgraded to 4.0.16 from 3.2.x --- why did service file change?
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Brian Snyder [mailto:[EMAIL PROTECTED] --Sent: Tuesday, November 11, 2003 12:51 PM --To: [EMAIL PROTECTED] --Subject: Upgraded to 4.0.16 from 3.2.x --- why did service file change? -- --I upgraded from RPM and I noticed that the /etc/rc.d/init.d script was --changed from mysqld to mysql. It also appears as if the 'status' option --that you can pass into service was removed. -- --I'm just curious why the owners would do that? Maybe because your actually not starting the mysqld daemon, your starting a wrapper script. --What is the rationale to changing a script name and removing --funtionality? No functionality has been removed. What are you noticing? -- --Thanx, -- 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: Upgraded to 4.0.16 from 3.2.x --- why did service file change?
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Brian Snyder [mailto:[EMAIL PROTECTED] --Sent: Tuesday, November 11, 2003 1:12 PM --To: [EMAIL PROTECTED] --Subject: RE: Upgraded to 4.0.16 from 3.2.x --- why did service file --change? -- --On Tue, 2003-11-11 at 16:04, Dathan Vance Pattishall wrote: -- - Dathan Vance Pattishall -- - Sr. Programmer and mySQL DBA for FriendFinder Inc. -- - http://friendfinder.com/go/p40688 -- -- -- ---Original Message- -- --From: Brian Snyder [mailto:[EMAIL PROTECTED] -- --Sent: Tuesday, November 11, 2003 12:51 PM -- --To: [EMAIL PROTECTED] -- --Subject: Upgraded to 4.0.16 from 3.2.x --- why did service file -- change? -- -- -- --I upgraded from RPM and I noticed that the /etc/rc.d/init.d script -- was -- --changed from mysqld to mysql. It also appears as if the 'status' -- option -- --that you can pass into service was removed. -- -- -- --I'm just curious why the owners would do that? -- -- Maybe because your actually not starting the mysqld daemon, your -- starting a wrapper script. -- -- --What is the rationale to changing a script name and removing -- --funtionality? -- -- No functionality has been removed. What are you noticing? -- --The old mysql service script was called mysqld , the new one is called --mysql. -- --You used to be able to say 'service mysqld status' and get an OK if --running. Now support for status was removed, and its just start, stop, --and restart. -- --BTW, This is on redhat. chkconfig --add mysql -- --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: Backing up all databases
Look at GRANT on mysql.com GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFITED BY ''; - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Randall Perry [mailto:[EMAIL PROTECTED] --Sent: Tuesday, November 11, 2003 3:05 PM --To: [EMAIL PROTECTED] --Subject: Backing up all databases -- --Am testing this command to backup databases: -- --/usr/local/mysql/bin/mysqldump --opt --all-databases --/usr/local/mysql/data/mysqldump -- --Am running as root, but it only backs up databases root has access too. -- --What's the best way to handle this -- give root full perms on all dbs? -- -- --Randall Perry --sysTame -- --Xserve Web Hosting/Co-location --Website Development/Promotion --Mac Consulting/Sales -- --http://www.systame.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]
RE: Error 127 = Record-file is crashed
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: rmck [mailto:[EMAIL PROTECTED] --Sent: Tuesday, November 11, 2003 3:17 PM --To: [EMAIL PROTECTED] --Subject: Error 127 = Record-file is crashed -- --Hello, -- --I have mysql-standard-4.0.16-pc-linux-i686 installed on a 4gig mem, --2cpu system, RH 9. --I have a large table (Data records: 72426930) that is now giving me this --error Error 127 --when doing selects... -- --This is my first crash of any kind with Mysql. So I'm a liitle confused --on what I should do. --The manual states --use myisamchk, then you read further and it says use REPAIR TABLE... -- --I'm confused can someone give me a 1 to end step on repairing this --table?? -- --At this point I brought my mysqld down. -- --Tried running this: --[root]# myisamchk -r Nov03 --- recovering (with keycache) MyISAM-table 'Nov03' --Data records: 72426930 --myisamchk: error: Can't create new tempfile: '/opt/logs/Nov03.TMD' --MyISAM-table 'Nov03' is not fixed because of errors --Try fixing it by using the --safe-recover (-o) or the --force (-f) option -- --So I tried this: --[root]# myisamchk -rf Nov03 --[EMAIL PROTECTED] ip_logs]# myisamchk -rf Nov03 --- recovering (with keycache) MyISAM-table 'Nov03' --Data records: 72426930 --5939000 -- --It looks like its doing something -- --my data dir and the table in question: ---rw-rw1 mysqlmysql8812359152 Nov 11 01:25 Nov03.MYD ---rw-rw1 mysqlmysql5346325504 Nov 11 14:52 Nov03.MYI ---rw-rw1 mysqlmysql631242752 Nov 11 14:52 Nov03.TMD -- --Please advise... Good you turned off mysql and ran this command. If you didn't you would just cause further corruption. Next the TMD file means that as myisamchk repairs, this temp file above is appended as the datafile is compared to the index file. This is to ensure that the source data file is not modified (yet) in case of row loss. If you need to recover the lost rows that occur during this repair (source data file will be stored in a .BAK file) then it's possible. So, what you need to do right now is wait till that 631242752 file size equals the 8812359152 file size. Myisamchk will report how many rows have been lost if any during the end of the repair. -- --Thanks --Rob -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and Oracle
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Chen, Mao [mailto:[EMAIL PROTECTED] --Sent: Monday, November 10, 2003 2:25 PM --To: [EMAIL PROTECTED]; [EMAIL PROTECTED] --Subject: MySQL and Oracle -- --Hi Everyone, -- -- -- --I'm wondering is there any way we can make MySQL database directly --'talk' with Oracle database? For example, directly transfer data --between each other. Directly no. Indirectly yes. -- -- -- --Thanks for any input. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
anyone use Veritas Flash Snap?
Anyone use Veritas FlashSnap to back up data or even better yet mySQL databases over NFS? - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688
RE: This is confusing..?
Can you send show keys from campaign_t. I bet you can solve your problem by running analyze table. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Eric Anderson [mailto:[EMAIL PROTECTED] --Sent: Thursday, November 06, 2003 11:08 AM --To: [EMAIL PROTECTED] --Subject: This is confusing..? -- -- --Given the following table: -- --CREATE TABLE campaign_t ( -- acct_id int(11) unsigned NOT NULL default '0', -- site_id tinyint(3) unsigned NOT NULL default '0', -- ref_id int(11) unsigned NOT NULL default '0', -- datestamp char(10) NOT NULL default '', -- raws int(11) unsigned NOT NULL default '0', -- uniques int(11) unsigned NOT NULL default '0', -- trial_signups int(11) NOT NULL default '0', -- full_signups int(11) NOT NULL default '0', -- annual_signups int(11) unsigned NOT NULL default '0', -- PRIMARY KEY (acct_id,site_id,ref_id,datestamp), -- KEY acct_id (acct_id), -- KEY site_id (site_id), -- KEY ref_id (ref_id), -- KEY datestamp (datestamp) --) TYPE=MyISAM; -- --How come it doesn't use the 'datestamp' index on this query: -- --mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g --++--+---+--+-+--+ +--- ---+ --| table | type | possible_keys | key | key_len | ref | rows | --Extra | --++--+---+--+-+--+ +--- ---+ --| campaign_t | ALL | datestamp | NULL |NULL | NULL | 438166 | --where used | --++--+---+--+-+--+ +--- ---+ --1 row in set (0.00 sec) -- --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: relationships
What do you mean by relations? Like table relations such as with FK in innodb? - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Robert Morgan [mailto:[EMAIL PROTECTED] --Sent: Thursday, November 06, 2003 11:18 AM --To: mysqllist --Subject: relationships -- --Hi, is there a way of showing relationships in mysql? -- --such as. mysql SHOW relationships -- --Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: so long I keep longing
On the list I thought I saw that they where expecting it to come out this month to mid December - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Jon Hancock [mailto:[EMAIL PROTECTED] --Sent: Thursday, November 06, 2003 11:26 AM --To: [EMAIL PROTECTED] --Subject: so long I keep longing -- -- for 4.1.1... --.anyone have a good guess for getting a build of 4.1.1 ??? -- --thanks, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ROWID
U could always use a PRIMARY key on an auto_increment column. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Mike Brando [mailto:[EMAIL PROTECTED] --Sent: Thursday, November 06, 2003 1:03 PM --To: [EMAIL PROTECTED] --Subject: RE: ROWID -- -- -- -Original Message- -- On Thu, Nov 06, 2003 at 09:59:41AM -0800, Wan, Wenhua wrote: -- Hi there, -- -- Both Oracle and Informix use ROWID to uniquely represent the location --of -- each row of data in a table. ROWID is basically a hidden column or -- pseudocolumn for each table, and it is the fastest way to retrive a --row -- from -- a table. Does MySql have similar field? If is, what's the name and --how -- to -- access it? -- -- Thank you very much in advance for your advice. -- -- http://www.mysql.com/doc/search.php?q=rowid -- -- -- --Ok, so that search produces this: -- -- If the PRIMARY or UNIQUE key consists of only one column and this is of --type --integer, you can also refer to it as _rowid (new in Version 3.23.11). -- --But that's not what a ROWID is compared to what I think the original --poster --was looking for. In Oracle for example, a ROWID is the unique address of --a row --in the database. Every row, unique key or not has a unique address. Is --there --such a thing in MySQL? ROWIDs are extremely useful for guaranteeing that --you --are manipulating the exact row that you think you are. -- --Mike -- --- - --Michael Brando --Senior Manager of Engineering --Applied Biosystems --3833 North First Street --San Jose, CA 95134-1701 -- -- --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: Transaction
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Arnoldus Th.J. Koeleman [mailto:[EMAIL PROTECTED] --Sent: Thursday, November 06, 2003 1:24 PM --To: [EMAIL PROTECTED] --Subject: Transaction -- --I am new in MySQL and am used to work with Oracle. -- -- -- --If you work with MyISAM tables is it possible to rollback transaction. No not at the db level with code yes. -- -- -- --And what is a better way to install a production application with MyISAM --tables or InnoDB? Depends on what your system needs. If you need transactions use innodb. If you need full text searches use myISAM etc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select count(*) / select count(column) in innodb
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Hsiu-Hui Tseng [mailto:[EMAIL PROTECTED] --Sent: Thursday, November 06, 2003 3:41 PM --To: [EMAIL PROTECTED] --Subject: select count(*) / select count(column) in innodb -- --Hi, -- --select count(*) is very slow in innodb (because it is a table scan). Is --there any difference if I change it to select count(column). -- --I did some test and some times select count(*) is really slow and some --time --select count(column) is slow. Could anyone help me? INNODB is slow because its a row level locking schema and the nature of transactions (e.g. what committed what's not etc). Heikki Tuuri will give a much more solid explanation then I could. At the conference he did give a prelude that this functionality taken for granted will improve in later versions of innodb. In the interim you can use Show table status like 'YOUR TABLE NAME%'; But it's not very accurate with INNODB. -- --If I need do a select(*) in innodb, is there any way to get away from the --slowness? No, use MYISAM; -- --In the sql, should we avoid doing select * or select count(*)? What is --the --reason? Well why transfer unneeded column values over your network? It makes a difference when these columns are big blobs / texts and the intention is not to do so. -- --Thank you for your help! -- --Hsiu-Hui -- -- --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: cardinality in SHOW INDEX
Use analyze table to calculate cardinality. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Hsiu-Hui Tseng [mailto:[EMAIL PROTECTED] --Sent: Thursday, November 06, 2003 4:46 PM --To: [EMAIL PROTECTED] --Subject: cardinality in SHOW INDEX -- --Hi, -- --Some time I saw cardinality in SHOW INDEX is null. Why this happening? --Then, --I did a OPTIMIZE TABLE on the table and the number was getting back. -- --We are going to switch all of our table to innodb. Will innodb has this --problem? If it happend, how to fix it in innodb. Using OPTIMIZE TABLE? Is --innodb support OPTIMIZE TABLE? -- --Thanks! -- --Hsiu-Hui -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replication setup
You need to GRANT the appropriate privs-for the slave in question to replicate from the master on the master. Look up Grant on mysql.com - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Ilia Kantor [mailto:[EMAIL PROTECTED] --Sent: Sunday, November 02, 2003 1:11 PM --To: [EMAIL PROTECTED] --Subject: replication setup -- --Hello, -- --I'm not so newbie to mysql yet have troubles setting up the --replication.. Could you advise a way out ? -- --I set up slave, master and rerun them. -- --But when I type -- --LOAD DATA FROM MASTER --on slave server - I see -- --ERROR 1218: Error connecting to master: Access denied for user: --'[EMAIL PROTECTED]' (Using password: YES) -- --How can that happen? -- --mysql --host=.. --port=.. -u.. -p --connects quite well. -- -- -- -- -- --Best regards, -- Ilia mailto:[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: table gets corrupted often
I think I have tracked the problem down to deletes. In an environment with many deletes I've noticed that the rate of table corruption across my cluster of db server increases for the boxes with many deletes. I've been able to get around this issue by periodically checking tables on off hours and repairing them as the issue develops. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Jon Wagoner [mailto:[EMAIL PROTECTED] --Sent: Monday, November 03, 2003 11:48 AM --To: [EMAIL PROTECTED] --Subject: table gets corrupted often -- --I have a table that tends to get corrupted and needs to be repaired every --few days. It is used for logging various messages in my web app. The --structure is: -- --CREATE TABLE log ( -- log_id int(11) unsigned NOT NULL auto_increment, -- log_type tinyint(1) unsigned NOT NULL default '0', -- when datetime NOT NULL default '-00-00 00:00:00', -- msg text NOT NULL, -- PRIMARY KEY (log_id) --) TYPE=MyISAM; -- --I am running MySQL 4.0.16 on a Red Hat 7.3 server. There are about 200 --other tables in the database, and none of the rest have a problem. -- --The table has about 200,000-300,000 records added daily, and is cleaned --out --every week or so. I have tried dropping and recreating the table, but --that --does not seem to help. The table is almost exclusively accessed by --INSERTs. -- --Any ideas why this particular table would be so unstable? -- -- --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: Hanging processes in MySQL 3.23.53
Can you check you keys_used status var. You may have run out of your key_buffer cache so things might take a much longer amount of time. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Dan Goodes [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 7:03 PM --To: Chris Nolan --Cc: [EMAIL PROTECTED] --Subject: Re: Hanging processes in MySQL 3.23.53 -- --Using Redhat linux 7.3 with an ext3 FS. -- --Incidentally, I've just manually restarted mysql (which drops all --in-progress processes), and it seems that the problem takes a while to --show (i.e. there's a period after a restart that things seem to go along --fine, then it all comes undone). I also should note that the database is --being written to almost-constantly (it's being used as an apache logger --process via mod_log_sql). -- ---dan -- -- --On Tue, 28 Oct 2003, Chris Nolan wrote: -- -- Which platform are you using? Which FS? -- -- Regards, -- -- Chris -- -- On Tue, 28 Oct 2003 01:14 pm, Dan Goodes wrote: -- Hi folks, -- -- I have a bit of a problem. I'm running 3.23.53 which I've compiled up --from -- source (because the RPMs are not an option for me). -- -- I have a process that does a fairly large select statement every 10 -- minutes - up until a few days ago it was all find and dandy. -- -- A few days ago I did a massive delete from one of the tables (getting --rid -- of a lot of old records), and since then things have gone awry. The --select -- statement seems to get stuck in the COPY TO tmp table stage, and -- starts to back up fairly heavily. Each of the cron-run processes gets --to -- this COPY TO TMP TABLE stage and locks up, which consumes all --available -- slots on the server and the whole things comes to a grinding halt. -- -- I've already run an optimize table on the table, and that got rid of --all -- the empty space freed up by the delete. -- -- Any ideas why, after the massive delete, things have started slowing --right -- down (or locking up entirely)? -- -- THanks for help. -- -- -Dan -- -- --Regards, -- --Dan Goodes : Systems Programmer : [EMAIL PROTECTED] -- --Help support PlanetMirror - Australia's largest Internet archive --by signing up for PlanetMirror Premium : http://planetmirror.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]
RE: Ancestry program
Think of a binary tree. Parent_id auto increment Child_id Details of the famly The head of the family has a child_id == parent_id All members of the family have different child_ids but the same parent_id Then you can do some really cool recursive fast searches. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Nitin [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 10:46 PM --To: [EMAIL PROTECTED] --Subject: Ancestry program -- --Hi all, -- --I'm developing a web based ancestry program. The user wants it to be --static, that means, it isn't for all to use, but his family. Better to --say, it'll contain only his family tree. -- --Now, I cant think of the proper db design, which will help any user to --find his or her relationship with any other person in the tree. Though, I --can design a simple database, where everything will have to be done --through queries and scripts, but I want those queries to keep as simple --as possible. -- --Any help will be appreciated, as I'm new to such a problem. -- --Thanx in advance --Nitin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Change of root password
As ROOT on linux goto /etc Edit my.cnf Enter skip-grant-tables Restart the mysql server Change the password. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Parminder Singh Chauhan [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 10:34 AM --To: [EMAIL PROTECTED] --Subject: Change of root password -- --After long struggle I (A newbee to linux) was able to setup mysql4.0.16 --and I don't know what I have done, I set the root password which I don't --know. Now I can't access the mysql. --Is there anyway of changing root password.? and how? --I tried to delete the content of /usr/local/mysql/var/mysql and run --mysql_install_db again thinking it will remove the password but no --effect. --Parminder Singh Chauhan [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Howto reduce size of MYISAM files after deleting records?
Shutdown the mysql server Goto the datadir (/var/lib/mysql) Goto the db directory Type myisamchk -r -S -a *.MYI this will reclaim the bad blocks caused by the delete. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Iago Sineiro [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 9:52 AM --To: MySql Mail List --Subject: Howto reduce size of MYISAM files after deleting records? -- --Hi. -- --I delete a lot of records of one MyISAM table in MySQL 4.0.16. After that --I --execute mysqladmin refresh and now the size of the files of the table is --the --same than before. -- --Is something wrong or is necessary doing something more to reduce the --size --of the files? -- --Thanks in advance. -- --Iago. -- -- -- --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]
