dump time progressively increasing with Innodb
Hi, I noticed that over the months the dump of my databases (very subject to modifications, but not subject to increase significantly in size) gets progressively slower: from ~8 minutes to almost 15 in 6 months. How can I avoid this degeneration? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Extremely slow access to information_schema
Hi, in Mysql 5.0 accessing information_schema.tables means almost certainly dropping down the DBMS. With my configuration 412 databases 357417 grants every query to information_schema.tables takes minutes, while the equivalent show tables from... (that I can't absolutely use) is immediate. Is there anything that I can do to speed it up? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Extremely slow access to information_schema
Il giorno mar, 28/04/2009 alle 17.11 -0400, Baron Schwartz ha scritto: Hi, On Tue, Apr 28, 2009 at 1:04 PM, Rolando Edwards redwa...@logicworks.net wrote: Make sure the tables that the information_schema are not locked. This is because MyISAM tables, that are constantly being inserted into (Each insert, update, delete on a MyISAM table does a table lock), must update the TABLE_ROWS column in information_schema.tables to have the latest count. That's not how INFORMATION_SCHEMA works. Those aren't real tables. Behind the scenes it is just doing SHOW TABLE STATUS on each table and populating a temporary structure with the results. Nico, it is my opinion that I_S is not suitable for heavy production use :-) I have seen several cases of severe performance problems caused by it. Baron after what I'm seeing I can't avoid to agree completely. I also read of extremely serious DOS problems caused by this crazy (and severely castrated) implementation of the I_S. How did it end up in mainline? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Shutdown time
Hi, after many years that I've been using mysql (with almost all Innodb tables) I still can't make myself a reason of the unbearably long shutdown times: almost everytime it takes at least 4 minutes to stop completely and to kill the process; sometimes I even had to kill -9 mysqld. Currently I'm running 150 databases, 12415 tables 1694 users and 173682 grants. The servers are configured to use 1GB of innodb_buffer_pool_size, innodb_log_buffer_size =8M innodb_log_file_size =5M out of 4 GB available. Both run on hardware scsi raid. What does the shutdown times depend on, and how can I reduce it? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Shutdown time
On Wednesday 15 April 2009 17:24:21 Baron Schwartz wrote: Hi! I just blogged about this: http://www.mysqlperformanceblog.com/2009/04/15/how-to-decrease-inno db-shutdown-times/ Short version: mysql set global innodb_max_dirty_pages_pct = 0; and wait until Innodb_buffer_pool_pages_dirty is smaller. Then shut down. thanks a lot, it's very useful ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Repeatedly got signal 10 in Solaris
Hi, for 2 consecutive nights I got the following message in the log, followed by a restart: 090323 2:00:14 - mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=81 max_connections=800 threads_connected=13 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1748985 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 090323 02:00:37 mysqld restarted 090323 2:00:43 InnoDB: Database was not shut down normally! ... The package I'm using is the 5.0.45-log bundled by Mysql for Solaris 10 - 64bit. If I'm not mistaken signal 10 is SIGBUS, something that in solaris happens as frequently as SIGSEGV. There are no coredumps to analyze. The number of active connections was average (81), so I don't expect that crash to have been caused by a lot of activity. Can anyone advise me what else to search? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Usefulness of mysql logs when using innodb?
Hi, I guess that when I'm using only Innodb and no replication I can safely disable mysql's (bin-) log files (that grow to no end) because Innodb has its own log files. Is it correct? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confirm subscribe to mysql@lists.mysql.com
On Wednesday 09 April 2008 13:02:57 [EMAIL PROTECTED] wrote: To confirm that you would like [EMAIL PROTECTED] added to the mysql mailing list, please click on the following link: http://lists.mysql.com/s/mysql/47fca2617dc97f36/nsabbi=tiscali.it This confirmation serves two purposes. First, it verifies that we are able to get mail through to you. Second, it protects you in case someone forges a subscription request in your name. --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, just send a message to the address in the ``List-Unsubscribe'' header of any list message. If you haven't changed addresses since subscribing, you can also send a message to: [EMAIL PROTECTED] or for the digest to: [EMAIL PROTECTED] For addition or removal of addresses, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete the transaction. If you need to get in touch with the human owner of this list, please send a message to: [EMAIL PROTECTED] Please include a FORWARDED list message with ALL HEADERS intact to make it easier to help you. --- Enclosed is a copy of the request I received. Received: (qmail 6852 invoked by uid 48); 9 Apr 2008 11:02:57 - Date: 9 Apr 2008 11:02:57 - Message-ID: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unsubscribe request From: [EMAIL PROTECTED] This message was generated because of a request from 89.97.249.170. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confirm subscribe to mysql@lists.mysql.com
On Wednesday 09 April 2008 13:03:39 [EMAIL PROTECTED] wrote: To confirm that you would like [EMAIL PROTECTED] added to the mysql mailing list, please click on the following link: http://lists.mysql.com/s/mysql/47fca28b736fbf40/Nicola.Sabbi=poste. it This confirmation serves two purposes. First, it verifies that we are able to get mail through to you. Second, it protects you in case someone forges a subscription request in your name. --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, just send a message to the address in the ``List-Unsubscribe'' header of any list message. If you haven't changed addresses since subscribing, you can also send a message to: [EMAIL PROTECTED] or for the digest to: [EMAIL PROTECTED] For addition or removal of addresses, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete the transaction. If you need to get in touch with the human owner of this list, please send a message to: [EMAIL PROTECTED] Please include a FORWARDED list message with ALL HEADERS intact to make it easier to help you. --- Enclosed is a copy of the request I received. Received: (qmail 7272 invoked by uid 48); 9 Apr 2008 11:03:39 - Date: 9 Apr 2008 11:03:39 - Message-ID: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unsubscribe request From: [EMAIL PROTECTED] This message was generated because of a request from 89.97.249.170. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help needed to tune Innodb on ZFS (on Solaris)
Hi, I installed Mysql 5.0.45 on Solaris 10 01/08 configured to run the datadir on a ZFS filesystem on a Raid1 pool. Both myisam and innodb tables are on the same filesystem. Innodb is configured to run with a buffer_pool_size=256M, with doublewrite set to OFF and with file_per_table to Off. The log files are 2 x 48 MB each. The problem I have is that importing the same 7 MB sql dump takes 9 seconds if engine=Myisam and 98 when engine is Innodb. Following some advice found in various bloggers I created the zfs filesystem I created with a recordsize of 16K, and set flush_log_at_trx_commit to 0. In some way the benchmark have improved, but I still find Innodb much slower than Myisam. Does anyone have any experience on this particular configuration? Is there any other trick to follow to improve Innodb's performance on ZFS? Three more things that I noticed: - setting innodb_flush_method=O_DIRECT causes mysql to complain that the directio() is not implemented. - dropping the db and reimporting it from the dump is slower at every iteration. Is there any way to keep the next reimports as fast as the first one without recreating from scratch the ibdata files? - why using innodb_file_per_tables is so much slower than running innodb with a single table space? Thanks in advance for any help, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed to tune Innodb on ZFS (on Solaris)
Il Wednesday 09 April 2008 19:40:32 Rob Wultsch ha scritto: On Wed, Apr 9, 2008 at 5:51 AM, Nico Sabbi [EMAIL PROTECTED] wrote: The problem I have is that importing the same 7 MB sql dump takes 9 seconds if engine=Myisam and 98 when engine is Innodb. Is autocommit turned off? http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET AUTOCOMMIT and COMMIT statements: SET AUTOCOMMIT=0; ... SQL import statements ... COMMIT; yes, autocommit is off There is also a note in there about forcedirectio and certain solaris setups. I read it, but directio seems to be unavailable on ZFS (as fas as I can tell) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
converting TEXT fields between charsets doesn't work
Hi, I followed the instructions to change columns values from an encoding another (in my case from latin1 to utf8), but the operation simply failed. The manual reads http://dev.mysql.com/doc/refman/5.0/en/alter-table.html : If you want to change the table default character set and all character columns (|CHAR|, |VARCHAR|, |TEXT|) to a new character set, use a statement like this: ALTER TABLE /|tbl_name|/ CONVERT TO CHARACTER SET /|charset_name|/; *Warning:* The preceding operation converts column values between the character sets. fact is that the columns values are completely unaffected. If in konsole with encoding set to latin1 I select one particular TEXT field from the table before the conversion I can see correct accented letters; after the conversion to utf8 (but with the konsole encoding still set to latin1) the select of the same field still shows correct accented letters, that would have been impossible if the encoding conversion had actually been done. Changing the table handler from Innodb to Myisam didn't help. The version of Mysql I'm using is 5.0.27-max. Am I doing something wrong? -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Refill s.r.l. - Il risparmio è fai da te! Inchiostri, cartucce, toner, carta speciale: ogni ricarica per la tua stampante ti costa solo 1 EURO! Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5193d=4-4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb_file_per_table set but ibdata still updated
Hi, in /etc/my.cnf I set innodb_file_per_table in order to avoid a centralized set of ibdata* files, but that file (8 MB) is still present and continually updated. What can I do to make it vanish for good? I'd like to have only per-table ibd files. The version of Mysql that I'm using is mysql-max 5.0.24a. Thanks, Nico -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Fai crescere i tuoi sogni. Scegli il prestito fino a 5.000 euro. * Rate da 20 euro Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6331d=28-3 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to log on the server refused connections?
Hi, I'm experiencing some sporadic connection refused from mysql-max server. I'd like to keep track of these events, so is there a way to log on the server these 3 items? - date and time - ip of the client - reason of the refusal Thanks, Nico -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Crea il tuo sito web dinamico con ASP e ACCESS - VideoCorso professionale direttamente nel tuo computer. Trucchi e segreti Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5143d=26-1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.1.14 Beta has been released
Mads Martin Joergensen wrote: Dear MySQL users, We are proud to present to you the MySQL Server 5.1.14 Beta release, a new Beta version of the popular open source database. Bear in mind that this is a beta release, and as any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. For production level systems using 5.0, pay attention to the product description of MySQL Enterprise at: http://mysql.com/products/enterprise/ The MySQL 5.1.14 Beta release is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ Hi, in any release that I tried up to 5.1.12 inclusive, almost any access to the information_schema paralyzed the server (bringing it to its knees). This bug was reported long ago and confirmed by many users in bugzilla, but in no changelog that I've read so far it seems to have been fixed. Is there any update on this issue? Any plan to fix it if it's not already resolved? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Transaction and LAST_INSERT_ID()
Mike Kruckenberg wrote: mysql SET @staff_id = LAST_INSERT_ID(); Query OK, 0 rows affected (0.01 sec) I don't know if this behaviour has changed in later versions of mysql, but using session variables, although lovely, was the quickest way to break replication (at least up to and including 4.0.27) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More than one MyQSL in a server
Mário Gamito wrote: Hi, I have a 3.23 MySQL running in a server and i want to install 5.0.27 I made # ./configure --prefix=/usr/local/mysql-5.0.27 --with-tcp-port=3307 # make # make install and then # scripts/mysql_install_db --datadir=/usr/local/mysql-5.0.27/var # ./mysqld_safe --datadir=/usr/local/mysql-5.0.27/var/ But here, i get the error A mysqld process already exists How can i have the two MySQL running in the same machine ? Any help would be appreciated. Warm Regards, MG mysqld_multi works pretty well. It's documented in www.mysql.com/doc. mysqld_multi --example shows a sample config file -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving database to another machine
Ratheesh K J wrote: Hlo all, I wanted to move around 50 GB of data on Machine A to Machine B. Both the machines are of same architecture ( LAMP ) Dumping and restoring takes a lot of time. Is there a faster method to accomplish the same? Is there a way to tar the whole thing and untar on Machine B? or any other way? Thanks, Ratheesh Bhat K J if you have exactly the same version of mysql keep the same configuration you can copy on the second machine the whole data directory (e.g. /var/lib/mysql), but obviously in this manner you will overwrite what you have in the second server. Don't forget to copy innodb's files, too, if you use it. -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Refill srl il paradiso della tua stampante - cartucce e toner compatibili, inchiostri e accessori per la ricarica, carta speciale. Tutto a prezzi scontatissimi! Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5187d=12-9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving database to another machine
Chris wrote: An additional note - you can only do this while mysql is completely shut down. You cannot do this while mysql is running on either server. why is it that I received this mail 3 times? I can understand 2 (one to my and one to the list), but 3 ... -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Ascolta tutta la musica che vuoi gratis! * Clicca su www.radiosnj.com Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5176d=12-9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HUGE load when user with few privileges execs show databases
Nico, as a first stab, I would try optimizing the tables in question. OPTIMIZE TABLE mysql.user etc. The one with 194,177 entries would be a good candidate for this especially. done, but with no improvement I wonder also if you would see something logged in the slow query log as this happens? done, but as you can see there's nothing in the log we didnt already know: cat *slow* /usr/sbin/mysqld-max, Version: 4.0.26-Max-log, started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument # Time: 060824 15:08:14 # [EMAIL PROTECTED]: X[X] @ nico.abc.loc [192.168.0.34] # Query_time: 20 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 show databases; Perhaps the tables_priv table could use an additional index, if OPTIMIZE doesn't do the trick. Hope this helps. Dan done, but still no improvement :( Thanks for your help -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Conquista e fatti conquistare aderendo al Club PER DUE di Blinko Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5490d=24-8 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HUGE load when user with few privileges execs show databases
Hello, as the title reads, when a user X with access to few tables runs show databases the query executes _very_ slowly and there's a sudden HUGE load (mysqld takes 99% cpu for the whole duration of the query): show databases; ++ | Database | ++ | A | | B | | C | | D | | E | | F | ++ 6 rows in set (18.35 sec) Now if I connect as super_user: mysql show databases; ... 286 rows in set (0.00 sec) There are a lot of users defined with very fine grained grants: mysql select count(*) from mysql.user; +--+ | count(*) | +--+ | 1025 | +--+ 1 row in set (0.01 sec) mysql select count(*) from mysql.db; +--+ | count(*) | +--+ | 1975 | +--+ 1 row in set (0.00 sec) mysql select count(*) from mysql.tables_priv; +--+ | count(*) | +--+ | 194177 | +--+ 1 row in set (0.00 sec) This anomaly is a very big problem for me. What can I do to solve it? Thanks, Nico -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Ascolta tutta la musica che vuoi gratis! * Clicca su www.radiosnj.com Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5176d=23-8 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What's wrong in this Innodb status log?
Hi, after having noticed occasional load spikes I created an Innodb monitor; follows an excerpt of the output where I can read that certain transactions don't start and that accessing a table (H.albero) with a very low amount of records (185) seems to take a lot of time. What's wrong with these logs? why those transactions don't start? Thanks, Nico = 060529 16:27:40 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 44025032, signal count 43473647 Mutex spin waits 794955636, rounds 1522695948, OS waits 9162350 RW-shared spins 66265323, OS waits 32297148; RW-excl spins 1644124, OS waits 379573 TRANSACTIONS Trx id counter 0 678088914 Purge done for trx's n:o 0 678083128 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 678088912, not started, process no 30735, OS thread id 1210591600 MySQL thread id 6460882, query id 173503655 192.168.65.105 pat_trento0844 ---TRANSACTION 0 678088911, not started, process no 30735, OS thread id 1207380336 MySQL thread id 6460881, query id 173503653 192.168.65.105 pat_trento0844 ---TRANSACTION 0 678088913, not started, process no 30735, OS thread id 1193732464 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 0 MySQL thread id 6460880, query id 173503656 192.168.65.107 h3r4user Sorting result SELECT H.albero.ordinamento, H.albero.REF, H.albero.titolo, H.albero.categoria, H.albero.tabella, H.albero.url, ---TRANSACTION 0 678087842, not started, process no 30735, OS thread id 1175267696 MySQL thread id 6460844, query id 173502395 192.168.65.107 r1minse4 ---TRANSACTION 0 678087713, not started, process no 30735, OS thread id 1164028272 MySQL thread id 6460837, query id 173502245 192.168.65.105 pat_trento0844 ---TRANSACTION 0 678082967, not started, process no 30735, OS thread id 1204169072 MySQL thread id 6460594, query id 173496341 192.168.65.107 h3r4user ---TRANSACTION 0 678081965, not started, process no 30735, OS thread id 1179281776 MySQL thread id 6460592, query id 173495169 192.168.65.105 pat_trento0844 ---TRANSACTION 0 678081879, not started, process no 30735, OS thread id 1150380400 MySQL thread id 6460570, query id 173495043 192.168.65.108 cntdb3 ---TRANSACTION 0 678057066, not started, process no 30735, OS thread id 1192126832 MySQL thread id 6459537, query id 173464972 192.168.65.105 pat_trento0844 ---TRANSACTION 0 678051381, not started, process no 30735, OS thread id 1195137392 MySQL thread id 6459290, query id 173458071 192.168.65.108 cntdb3 ---TRANSACTION 0 678045374, not started, process no 30735, OS thread id 1182894448 MySQL thread id 6459003, query id 173450765 192.168.65.105 prgdb_1 FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 99506233 OS file reads, 1810896 OS file writes, 808231 OS fsyncs 266.30 reads/s, 33214 avg bytes/read, 1.75 writes/s, 1.12 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 5, seg size 7, 195942 inserts, 195942 merged recs, 7446 merges Hash table size 34679, used cells 6972, node heap has 13 buffer(s) 4850.07 hash searches/s, 1614.59 non-hash searches/s --- LOG --- Log sequence number 4 1251143669 Log flushed up to 4 1251143659 Last checkpoint at 4 1251140386 0 pending log writes, 0 pending chkp writes 1203908 log i/o's done, 1.12 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 364710794; in additional pool allocated 25165824 Buffer pool size 1024 Free buffers 0 Database pages 1011 Modified db pages 11 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 234480945, created 286926, written 1159843 539.84 reads/s, 0.00 creates/s, 0.62 writes/s Buffer pool hit rate 995 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 30735, id 1147169136, state: sleeping Number of rows inserted 16849245, updated 39287, deleted 16105063, read 32922121960 0.06 inserts/s, 0.06 updates/s, 0.00 deletes/s, 135719.89 reads/s END
Execution time of show databases increases when fewer databasesare visible
Hi, I have a strange and annoying problem with Mysql 4.0.26 that I hope someone will help me to fix: the more databases a user has the less time it takes to execute show databases and vice versa: show databases; +--+ 5 rows in set (7.97 sec) --- show databases; ++ 74 rows in set (5.87 sec) --- show databases; ++ 141 rows in set (3.66 sec) --- show databases; ++ 210 rows in set (1.45 sec) Is this behaviour due to a bug or maybe my mysql tables are messed up? Here are some stats: select count(*) from mysql.user; +--+ | count(*) | +--+ | 998 | +--+ 1 row in set (0.02 sec) select count(*) from mysql.host; +--+ | count(*) | +--+ | 0| +--+ 1 row in set (0.03 sec) select count(*) from mysql.db; +--+ | count(*) | +--+ | 1402 | +--+ 1 row in set (0.03 sec) select count(*) from mysql.columns_priv; +--+ | count(*) | +--+ | 0| +--+ 1 row in set (0.03 sec) mysql select count(*) from mysql.tables_priv; +--+ | count(*) | +--+ | 145894 | +--+ 1 row in set (0.01 sec) Nico -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Sei single e stai cercando lamore? Entra subito in Meetic, iscriviti gratis, consulta i profili di milioni di single e chatta con loro Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=4051d=21-4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication of GRANTs
Hi, I don't understand how GRANTS are replicated in Mysql 4.0.26 (I can't upgrade to a newer version). The servers were all cleanly installed from scratch (not upgraded) using Mysql's 4.0.26 official rpm. According to the manual GRANT statements should be replicated and after executing 'flush privileges' on the slave I should see the new access rights in effect, yet: on the master: mysql show grants for d6882@'%'; +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT USAGE ON *.* TO 'd6882'@'%' IDENTIFIED BY PASSWORD '077c47e54922b29b' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `sessions`.`sessions` TO 'd6882'@'%' | +--+ 2 rows in set (0.10 sec) on the slave: mysql show grants for d6882@'%'; ERROR 1141: GRANT non definita per l'utente 'd6882' dalla macchina '%' flush privileges; Query OK, 0 rows affected (19 min 54.15 sec)## --- THIS IS NOT NORMAL mysql show grants for d6882@'%'; ERROR 1141: GRANT non definita per l'utente 'd6882' dalla macchina '%' Sane thing after having flushed privileges on the master. mysql show slave status \G *** 1. row *** Master_Host: 192.168.65.11 Master_Port: 3306 Connect_retry: 60 Master_Log_File: nodo2-bin.004 Read_Master_Log_Pos: 189127893 Relay_Log_File: sviluppo-relay-bin.004 Relay_Log_Pos: 189127973 Relay_Master_Log_File: nodo2-bin.004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 189127893 Relay_log_space: 189127973 1 row in set (0.01 sec) On the slave these are the replication settings: server-id=150 master-host=192.168.65.11 master-connect-retry=60 replicate-wild-do-table=%.% replicate-wild-ignore-table=sessions.sessions BTW, the statements in tables sessions.sessions seem to be replicated: I can see them with show processlist. why? delete from sessions.sessions; Query OK, 99458 rows affected (0.16 sec) mysql select count(*) from sessions.sessions; +--+ | count(*) | +--+ | 38 | +--+ 1 row in set (0.00 sec) mysql select count(*) from sessions.sessions; +--+ | count(*) | +--+ | 39 | +--+ 1 row in set (0.00 sec) Thanks, Nico -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Biglietti da visita premium GRATUITI. Offerta in scadenza * Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=4834d=10-3 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.0 always executes case insensitive queries
Hi, my mysql always executes case insensitive queries: SELECT username FROM workflow.user WHERE username = 'NicO' LIMIT 1; +--+ | username | +--+ | nico | +--+ 1 row in set (0.01 sec) that field is of varchar(255) type. I don't understand the reason for this behavior. What should I check? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb dealing with blobs in 4.1: Error 139 from storage engine
Hi, with mysql 4.1[234], importing a dump of Innodb tables containing at least a blob field I invariably get_ ERROR 1030 (HY000) at line 21027: Got error 139 from storage engine I read in the bugzilla that this problem is due to low memory conditions, but surely it's not my case: the server has 2 GB ram, and it doesn't have anything else running than mysql. I also tried to raise set-variable = innodb_buffer_pool_size=120M set-variable = innodb_additional_mem_pool_size=120M but with no improvent. The content of the err file is: 050920 11:46:31 mysqld started 050920 11:46:31 InnoDB: Started; log sequence number 0 18025704 /usr/sbin/mysqld-max: ready for connections. Version: '4.1.14-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Max (GPL) Is there a way to fix this behaviour or should I revert to 4.0? Thanks, -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb dealing with blobs in 4.1: Error 139 from storage engine
Nico Sabbi wrote: Hi, with mysql 4.1[234], importing a dump of Innodb tables containing at least a blob field I invariably get_ ERROR 1030 (HY000) at line 21027: Got error 139 from storage engine I read in the bugzilla that this problem is due to low memory conditions, but surely it's not my case: the server has 2 GB ram, and it doesn't have anything else running than mysql. I also tried to raise set-variable = innodb_buffer_pool_size=120M set-variable = innodb_additional_mem_pool_size=120M but with no improvent. The content of the err file is: 050920 11:46:31 mysqld started 050920 11:46:31 InnoDB: Started; log sequence number 0 18025704 /usr/sbin/mysqld-max: ready for connections. Version: '4.1.14-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Max (GPL) Is there a way to fix this behaviour or should I revert to 4.0? Thanks, If it can help, seems that the largest value I can store in a blob field without triggering that error is 192 characters long. -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb dealing with blobs in 4.1: Error 139 from storage engine
sorry for this monologue-thread, but... Nico Sabbi wrote: [snip] If it can help, seems that the largest value I can store in a blob field without triggering that error is 192 characters long. I just read the restrictions on Innodb tables, and I'm not convinced that what is going on is expected. Quoting from here http://dev.mysql.com/doc/mysql/en/innodb-restrictions.html # The maximum row length, except for |VARCHAR|, |BLOB| and |TEXT| columns, is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. |LONGBLOB| and |LONGTEXT| columns must be less than 4GB, and the total row length, including also |BLOB| and |TEXT| columns, must be less than 4GB. |InnoDB| stores the first 768 bytes of a |VARCHAR|, |BLOB|, or |TEXT| column in the row, and the rest into separate pages. # that clearly states that varchar(255) fields should be excluded from the restrictions, shouldn't they? Yet, my tables is made of 104 varchar(255) fields (I know it's bad, but unfortunately it's a structure I can't change), a dozen blobs and 8 other fields (date and int), so it seems that Innodb _is_ including varchar() fields in the restriction. If needed I can provide the whole table structure. BTW, why introducing a restriction that wasn't present in 4.0 ? Thanks, Nico -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grants not entirely propagated to slaves?
Atle Veka wrote: What version of MySQL are you using? Also, are you issuing only GRANT .. statements or modifying the privilege tables manually as well? Search for 'GRANT': http://dev.mysql.com/doc/mysql/en/replication-features.html Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 1 Apr 2005, Nico Sabbi wrote: hi, my mysql is a 4.0.21. After a flush privileges I can see all granted accesses. Thanks for you help! Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Grants not entirely propagated to slaves?
Hi, it seems my Grants are not entirely propagated from the master to the slave (some are active, some are not). The slave is configured to replicate all databases, and the replication client has all privileges on the master. What is necessary to propagate every single grant? Thanks, -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Revoke all privileges on *.* doesn't work
Hi,
the documentation reads:
REVOKE /|priv_type|/ [(/|column_list|/)] [, /|priv_type|/ [(/|column_list|/)]]
...
ON {/|tbl_name|/ | * | *.* | /|db_name|/.*}
FROM /|user|/ [, /|user|/] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM /|user|/ [, /|user|/] ...
but it doesn't work, as you can see below:
grant all privileges on *.* to user3;
Query OK, 0 rows affected (0.00 sec)
mysql show grants for user3;
++
| Grants for [EMAIL PROTECTED] |
++
| GRANT ALL PRIVILEGES ON *.* TO 'user3'@'%' |
++
1 row in set (0.00 sec)mysql show variables like 'version';
+---++
| Variable_name | Value |
+---++
| version | 4.0.21-Max-log |
+---++
1 row in set (0.00 sec)
mysql revoke all privileges on *.* from user3;
Query OK, 0 rows affected (0.00 sec)
mysql show grants for user3;
+---+
| Grants for [EMAIL PROTECTED]|
+---+
| GRANT USAGE ON *.* TO 'user3'@'%' |
+---+
1 row in set (0.00 sec)
mysql flush privileges;
Query OK, 0 rows affected (0.09 sec)
mysql show grants for user3;
+---+
| Grants for [EMAIL PROTECTED]|
+---+
| GRANT USAGE ON *.* TO 'user3'@'%' |
+---+
1 row in set (0.00 sec)
mysql show variables like 'version';
+---++
| Variable_name | Value |
+---++
| version | 4.0.21-Max-log |
+---++
1 row in set (0.00 sec)
what am I doing wrong?
Thanks,
--
Nico Sabbi - Officine Digitali - Bologna
Tel. 051 - 4187565
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoke all privileges on *.* doesn't work
Caron, Christian wrote: mysql show grants for user3; +---+ | Grants for [EMAIL PROTECTED]| +---+ | GRANT USAGE ON *.* TO 'user3'@'%' | +---+ 1 row in set (0.00 sec) what am I doing wrong? Near the bottom it says: USAGE ||Synonym for ``no privileges'' So, you succesfully removed all privileges! good to know :) , but ... That's something that always bugged me... If you really want to remove a user from your interface, you'll have to do it manually in the table. But why has it been implemented this way? If I want to remove a user, I don't want him/her to have no privileges, I want him/her to be completely out of the database... Anyone knows why they chose this route? Christian I totally agree: I would like mysql to kill or forget the existence of that user. Besides, I'm not supposed to mess with a dbms' internal tables, nor to know how/where grants are stored. -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to limit log space usage?
Hi, I have a configuration with 1 master and 2 slaves; all servers are correctly running mysql-max-4.0.20. All is fine, but I'm observing a strange usage of logs. On one of the slaves I have: mysql show slave status \G *** 1. row *** Master_Host: idb Master_User: root Master_Port: 3306 Connect_retry: 60 Master_Log_File: db-bin.3322 Read_Master_Log_Pos: 1013397930 Relay_Log_File: www4-relay-bin.009 Relay_Log_Pos: 174693397 Relay_Master_Log_File: db-bin.3322 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: mysql Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 1013397930 Relay_log_space: 174693397 1 row in set (0.00 sec) and the relay log is 170MB. I don't want relay logs to grow so big; what I'd like Mysql to do is to destroy the relay-log file as soon as the SQL thread is finished. (usually relay logs grow up to 4GB in 1GB chunks, so this example doesn't show my problem in all its extent). Similar problem on the master (db): -rw-rw1 mysqlmysql 196122 Jan 23 2004 db-bin.3319 -rw-rw1 mysqlmysql1073772277 Sep 13 14:55 db-bin.3320 -rw-rw1 mysqlmysql1073773581 Sep 15 10:18 db-bin.3321 -rw-rw1 mysqlmysql1019108019 Sep 17 09:08 db-bin.3322 -rw-rw1 mysqlmysql 42 Sep 15 10:18 db-bin.index There are 3 GB of logs that no one needs anymore. Since the master knows that all the slaves are correctly aligned up to a certain MASTER_LOG_POS, can't it automatically remove the logs up to that position? On another slave where I set set-variable = max_relay_log_size=2500 set-variable = relay_log_space_limit=5000 max_relay_log_size seems to be respected, but the problem remains: after having the data inserted in the DB those logs are useless. Can't Mysql automatically purge logs when they are not needed anymore? Thanks, -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.1 and MyODBC don't like each other
Hi, I tried to compile Myodbc 3.51.0[67] against mysql4.1, but while 0.7 doesn't even configure correctly because of undefined automake macros, .06 fails because it calls int2str() with three parameters instead of 4 (defined in m_string.h). What does the 4th parameter mean? Is MyODBC a dead/unmaintained project? Another problem: building the sources of Mysql*.src.rpm never builds Mysql-shared-compat, that is often indispensable. Can someone please update the specfile? Thanks, -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication stopping
Il lun, 2004-07-12 alle 13:45, Cemal Dalar ha scritto: To debug the problem. Make SHOW SLAVE STATUS in the slave and check for the error number.. Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.gittigidiyor.com http://www.dalar.net - Original Message - From: Nico Sabbi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 12, 2004 6:08 PM Subject: Replication stopping Hi, it happened again just now, this is the status: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 55463 to server version: 4.0.18-Max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show slave status \G *** 1. row *** Master_Host: master Master_User: replica Master_Port: 3306 Connect_retry: 60 Master_Log_File: db-bin.3323 Read_Master_Log_Pos: 437183883 Relay_Log_File: server-relay-bin.005 Relay_Log_Pos: 228252575 Relay_Master_Log_File: db-bin.3323 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: mysql Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 437183883 Relay_log_space: 228252575 1 row in set (0.00 sec) Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication stopping
Hi, often my slave suddenly stops, reporting these the logs: 040712 12:19:00 Slave I/O thread exiting, read up to log 'db-bin.3323', position 197564621 040712 12:19:10 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'db-bin.3323' at position 197564621 040712 13:48:22 Slave I/O thread exiting, read up to log 'db-bin.3323', position 208931388 040712 13:48:25 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'db-bin.3323' at position 208931388 this is the configuration of the slave: [mysqld] log-bin server-id=20 master-host=master master-port=3306 master-user=replica master-password=*** replicate-ignore-db=mysql replicate-wild-do-table=db1.prc replicate-wild-do-table=db2.provincia_rc replicate-wild-do-table=db3.tc replicate-wild-do-table=prc.% master-connect-retry=60 slave-skip-errors=all How can I understand exactly what is stopping the replication? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
meaning of master_log_pos setting?
Hi, I have this entry in my logfile: 040524 12:30:06 Got fatal error 1236: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' from master wh en reading data from binary log 040524 12:30:06 Slave I/O thread exiting, read up to log 'db-bin.3320', position 2494387 My master had that variables set to 4M, so I increased it to 64M and restarted both master and slave, but it still wasn't enough. How can I check what value it needs at the moment? Is it the size of a binlog file or of a single stamenent? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Alle Wednesday 12 May 2004 21:51, hai scritto: Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 14:12, hai scritto: Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 11:19, Sergei Golubchik ha scritto: Hi! sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Yes, but the second syntax (with use db) should replicate if you use replicate-do-db=db. I understood that you tried it, and it didn't work ? Exactly. In my.cnf I have both: replicate-do-db=db replicate-wild-do-table=db.% but only insert into db.table values() First - sorry for confusion, according to http://dev.mysql.com/doc/mysql/en/Replication_Options.html replicate-do-db is not expected to do anything if you have replicate-wild-do-table. So you are right - it does not work as you expected. But replicate-wild-do-table should work, no matter whether you use insert into db.table values() or use db; insert into table values() Could you provide a repeatable test case to show that replicate-wild-do-table does not work ? If yes - please submit it at http://bugs.mysql.com/ Regards, Sergei I can't reproduce it anymore, I must have made something wrong that messed up the replication sooner. Sorry and thanks for your help, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Alle Wednesday 12 May 2004 11:19, Sergei Golubchik ha scritto: Hi! On May 11, Nico Sabbi wrote: Alle Tuesday 11 May 2004 15:33, Nico Sabbi ha scritto: Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto: Nico Sabbi [EMAIL PROTECTED] wrote: Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? No, but if you use -B option of mysqldump USE db_name statements will be included in the output. I see, but this creates me a serious problem: I usually replicate my databases between a number of mysqld servers; when I want to copy a database db plus some additional metadata from the local server to the master I usually run mysqldump -B db -h local | mysql -h master that works correctly on the new_server, but totally messes up the current slaves that are configured to replicate-wild-do-table=db.% because there's no db.table syntax, so the slaves discard the insert. Maybe adding replicate-do-db=db (for all of my dbs) will do the trick? sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) Regards, Sergei I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Regards, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Alle Wednesday 12 May 2004 14:12, hai scritto: Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 11:19, Sergei Golubchik ha scritto: Hi! sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Yes, but the second syntax (with use db) should replicate if you use replicate-do-db=db. I understood that you tried it, and it didn't work ? Exactly. In my.cnf I have both: replicate-do-db=db replicate-wild-do-table=db.% but only insert into db.table values() are executed. That's why yesterday I posted my patch to mysqldump Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Alle Wednesday 12 May 2004 16:14, Egor Egorov ha scritto: Nico Sabbi [EMAIL PROTECTED] wrote: Alle Wednesday 12 May 2004 14:12, hai scritto: sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Yes, but the second syntax (with use db) should replicate if you use replicate-do-db=db. I understood that you tried it, and it didn't work ? Exactly. In my.cnf I have both: replicate-do-db=db replicate-wild-do-table=db.% but only insert into db.table values() are executed. That's why yesterday I posted my patch to mysqldump Works fine for me. Do you use any other replication-* options? Which version of MySQL server do you use? mysql-4.0.18-max on both sides. this is the configuration of the slave: [mysqld] log-bin server-id=2001 master-host=master master-port=3306 master-user=replica master-password= replicate-ignore-db=mysql master-connect-retry=60 slave-skip-errors=all replicate-do-db=db replicate-wild-do-table=db.% replicate-wild-do-table=dbsetting.db% replicate-wild-do-table=image_repository.db% replicate-wild-do-table=workflow.table_categories Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to dump data in db.table syntax?
Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? Another question: is there a way to dump all dbs that DON'T match a pattern without resorting to pipes / grep -v / xargs? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto: Nico Sabbi [EMAIL PROTECTED] wrote: Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? No, but if you use -B option of mysqldump USE db_name statements will be included in the output. I see, but this creates me a serious problem: I usually replicate my databases between a number of mysqld servers; when I want to copy a database db plus some additional metadata from the local server to the master I usually run mysqldump -B db -h local | mysql -h master that works correctly on the new_server, but totally messes up the current slaves that are configured to replicate-wild-do-table=db.% because there's no db.table syntax, so the slaves discard the insert. Maybe adding replicate-do-db=db (for all of my dbs) will do the trick? Another question: is there a way to dump all dbs that DON'T match a pattern without resorting to pipes / grep -v / xargs? No, mysqldump doesn't have such option. it's a pity :( Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Alle Tuesday 11 May 2004 15:33, Nico Sabbi ha scritto: Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto: Nico Sabbi [EMAIL PROTECTED] wrote: Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? No, but if you use -B option of mysqldump USE db_name statements will be included in the output. I see, but this creates me a serious problem: I usually replicate my databases between a number of mysqld servers; when I want to copy a database db plus some additional metadata from the local server to the master I usually run mysqldump -B db -h local | mysql -h master that works correctly on the new_server, but totally messes up the current slaves that are configured to replicate-wild-do-table=db.% because there's no db.table syntax, so the slaves discard the insert. Maybe adding replicate-do-db=db (for all of my dbs) will do the trick? sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[PATCH] for mysqldump: full insert statements (db.table) with -c
Hi,
with this patch if you use mysqldump with -c the db name will precede the
table name.
Hopefully this will solve the replication problem I described earlier.
ico
--- mysqldump.c.orig 2004-02-10 19:15:59.0 +0100
+++ mysqldump.c 2004-05-11 17:33:16.407884792 +0200
@@ -670,7 +670,7 @@
}
if (cFlag)
- sprintf(insert_pat, INSERT %sINTO %s (, delayed, opt_quoted_table);
+ sprintf(insert_pat, INSERT %sINTO %s.%s (, delayed, db, opt_quoted_table);
else
{
sprintf(insert_pat, INSERT %sINTO %s VALUES , delayed,
@@ -732,7 +732,7 @@
fprintf(sql_file, CREATE TABLE %s (\n, result_table);
}
if (cFlag)
- sprintf(insert_pat, INSERT %sINTO %s (, delayed, result_table);
+ sprintf(insert_pat, INSERT %sINTO %s.%s (, delayed, db, result_table);
else
{
sprintf(insert_pat, INSERT %sINTO %s VALUES , delayed, result_table);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication and Grants nightmare
Hi, I'm running mysql-max 4.0.18 on two servers on separate networks, master is A and slave is B. The problem is that if I don't grant on the master the full range of privileges to the slave the replication doesn't even start. Even worse is the fact that grants seems to be totally managed at random, as you can see: (A and B obviously are fake names). mysql A GRANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER, RELOAD on *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc'; OK mysql A show grants for replica@'B'; ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT RELOAD ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc' WITH GRANT OPTION | ++ 1 row in set (0.00 sec) - I didn't grant (yet) any option, so why does it say 'WITH GRANT OPTION' ? - where have all the other privileges gone? they vanished mysql A revoke ALL PRIVILEGES on *.* from replica@'B'; # identified by 'RC_rpl!'; Query OK, 0 rows affected (0.00 sec) mysql A flush privileges; Query OK, 0 rows affected (0.00 sec) mysql A show grants for replica@'B'; +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc' WITH GRANT OPTION | +--+ 1 row in set (0.00 sec) where did it take these rights from? I revoked them all Now the replication part: mysql A GRANT super, reload, replication client, replication slave ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql A flush privileges; Query OK, 0 rows affected (0.00 sec) these are the logs on B: 040430 11:10:34 InnoDB: Started /usr/sbin/mysqld-max: ready for connections. Version: '4.0.18-Max-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 040430 11:10:34 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'db-bin.3321' at position 34997604 040430 11:10:34 While trying to obtain the list of slaves from the master 'A:3306', user 'replica' got the following error: 'Access denied. You need the REPLICATION SLAVE privilege for this operation' 040430 11:10:34 Slave I/O thread exiting, read up to log 'db-bin.3321', position 34997604 What is going wrong? P.S. the manual doesn't say that grants super, reload, replication client and replication slave can be used only on *.* and not on DB.*. Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
Alle Friday 30 April 2004 15:22, Victoria Reznichenko ha scritto: Hmm.. Your queries worked fine for me: ANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER, RELOAD ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc'; Query OK, 0 rows affected (0.00 sec) mysql show grants for replica@'B'; +-- + | Grants for [EMAIL PROTECTED] || +-- + | GRANT RELOAD, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO | 'replica'@'B' IDENTIFIED BY PASSWORD '715a443962d324cc' | +-- + 1 row in set (0.01 sec) mysql revoke ALL PRIVILEGES ON *.* FROM replica@'B'; Query OK, 0 rows affected (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) mysql show grants for replica@'B'; +-- -+ | Grants for [EMAIL PROTECTED] | | +-- -+ | GRANT USAGE ON *.* TO 'replica'@'B' IDENTIFIED BY PASSWORD | '715a443962d324cc' | +-- -+ 1 row in set (0.00 sec) Did you have account for 'replica'@'B' before? neither other users with the same name nor other entries for the same host Are the above queries exact that you used? yes, except the host names Which OS do you use? the master is a Redhat 7.3 with Mysql-max 4.0.18 (binary rpms from www.mysql.com), the slave is a very old Cobalt 6.0 with Mysql-max 4.0.18 compiled from the .src.rpm. Thanks for your help -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
Alle Friday 30 April 2004 15:51, hai scritto:
P.S. the manual doesn't say that grants super, reload, replication client
and replication slave can be used only on *.* and not on DB.*.
Each of those privileges is listed as an administrative privilege here:
http://dev.mysql.com/doc/mysql/en/Privileges_provided.html
There are not listed as database or table privileges.
I'm not sure what it could mean for them to be database-specific, actually.
so I should have columns Repl_client_priv and Repl_slave_priv ?
my mysql db doesn't have them:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ mysql;
USE mysql;
--
-- Table structure for table `columns_priv`
--
CREATE TABLE columns_priv (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Table_name char(64) binary NOT NULL default '',
Column_name char(64) binary NOT NULL default '',
Timestamp timestamp(14) NOT NULL,
Column_priv set('Select','Insert','Update','References') NOT NULL default
'',
PRIMARY KEY (Host,Db,User,Table_name,Column_name)
) TYPE=MyISAM COMMENT='Column privileges';
--
-- Table structure for table `db`
--
CREATE TABLE db (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,Db,User),
KEY User (User)
) TYPE=MyISAM COMMENT='Database privileges';
--
-- Table structure for table `func`
--
CREATE TABLE func (
name char(64) binary NOT NULL default '',
ret tinyint(1) NOT NULL default '0',
dl char(128) NOT NULL default '',
type enum('function','aggregate') NOT NULL default 'function',
PRIMARY KEY (name)
) TYPE=MyISAM COMMENT='User defined functions';
--
-- Table structure for table `host`
--
CREATE TABLE host (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,Db)
) TYPE=MyISAM COMMENT='Host privileges; Merged with database privileges';
--
-- Table structure for table `tables_priv`
--
CREATE TABLE tables_priv (
Host char(60) binary NOT NULL default '',
Db char(64) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Table_name char(60) binary NOT NULL default '',
Grantor char(77) NOT NULL default '',
Timestamp timestamp(14) NOT NULL,
Table_priv
set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter')
NOT NULL default '',
Column_priv set('Select','Insert','Update','References') NOT NULL default
'',
PRIMARY KEY (Host,Db,User,Table_name),
KEY Grantor (Grantor)
) TYPE=MyISAM COMMENT='Table privileges';
--
-- Table structure for table `user`
--
CREATE TABLE user (
Host char(60) binary NOT NULL default '',
User char(16) binary NOT NULL default '',
Password char(16) binary NOT NULL default '',
Select_priv enum('N','Y') NOT NULL default 'N',
Insert_priv enum('N','Y') NOT NULL default 'N',
Update_priv enum('N','Y') NOT NULL default 'N',
Delete_priv enum('N','Y') NOT NULL default 'N',
Create_priv enum('N','Y') NOT NULL default 'N',
Drop_priv enum('N','Y') NOT NULL default 'N',
Reload_priv enum('N','Y') NOT NULL default 'N',
Shutdown_priv enum('N','Y') NOT NULL default 'N',
Process_priv enum('N','Y') NOT NULL default 'N',
File_priv enum('N','Y') NOT NULL default 'N',
Grant_priv enum('N','Y') NOT NULL default 'N',
References_priv enum('N','Y') NOT NULL default 'N',
Index_priv enum('N','Y') NOT NULL default 'N',
Alter_priv enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (Host,User)
) TYPE=MyISAM COMMENT='Users and global privileges';
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and Grants nightmare
Alle Friday 30 April 2004 16:44, hai scritto: At 16:22 +0200 4/30/04, Nico Sabbi wrote: Alle Friday 30 April 2004 15:51, hai scritto: P.S. the manual doesn't say that grants super, reload, replication client and replication slave can be used only on *.* and not on DB.*. Each of those privileges is listed as an administrative privilege here: http://dev.mysql.com/doc/mysql/en/Privileges_provided.html There are not listed as database or table privileges. I'm not sure what it could mean for them to be database-specific, actually. so I should have columns Repl_client_priv and Repl_slave_priv ? my mysql db doesn't have them: Indeed you should (in the user table only), and several other columns as well. Perhaps that is a clue to what is happening. Did you upgrade from a release older than 4.0.2 to a release 4.0.2 or newer at some point, without running the mysql_fix_privilege_tables script? If so, then please read this: http://dev.mysql.com/doc/mysql/en/Upgrading-grant-tables.html done, it works correctly now. Thanks very much, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
autoextend in 3.23.51 rpm not accepted
Hi, I just installed mysql-max 3.23.51, hoping to use innodb autoextend feature, but mysql dies when run with the following row in my.cnf: innodb_data_file_path = ibdata1:512M:autoextend;ibdata2:256M:autoextend; complaining of an unrecognized option. Thanks, Nico - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication and transaction questions
HI, I have two questions: 1) is there a way to tell my mysql master not to log failed executions, such as creation of existing tables, drop of inexistant databases etc? Each of these operations lock the slave, and I believe that it' conceptually wrong logging them, since they never took place on the master. I didn't find any mention of similar options in the manual, except some options to tell the slave to go on in case of errors (that I don't like). 2) why are creations of tables and databases non transactional when using InnoDB? Is there an options to change this behavior? Thanks Nico - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Serious performance problems when using InnoDB
Hi, I'm using a db with few tables, one of which is reported below: CREATE TABLE `keywords` ( `keyword` varchar(128) NOT NULL default '', `codice` varchar(16) NOT NULL default '', `timestamp` int(11) NOT NULL default '0', `soundekw` varchar(32) NOT NULL default '', PRIMARY KEY (`keyword`,`codice`), KEY `codice` (`codice`) ) TYPE=InnoDB | My problem is that after having inserted more than 2-3 times roughly 70 records in it, every operation such as DELETE from keywords; or SELECT count(*) from keywords; slows to a crawl: it takes 15 minutes or so to complete. Sometimes I even have to dump, zap, recreate and reimport the db. My system is redhat linux 7.2, Mysql-Max 3.23.46 (official rpm) on a Dual PIII 550, 256 MB ram, Raid 1 on a Mylex Raid card and the following configuration for Innodb: innodb_data_home_dir = /home/share/innodb/ innodb_data_file_path = ibdata1:512M set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = /home/share/innodb/log/ set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_arch_dir = /home/share/innodb/log/ innodb_log_archive=1 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 I know this configurations doesn't look aggressive at all, but it shouldn't even lead to such a slow-down. Can anyone help me please? Thanks, Nico - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Innodb replication and Database renaming question
Hi, I'm still using Mysql 3.23.46 because, as far as I remember, in .47 was reintroduced the limit of 500 chars max in Innodb primary keys (limit that would break my application). Is it still present ? Is it present in Mysql 4 ? I also need to know if Innodb is reliable in replication mode. I remember that with past versions, if the master broke the slave would lose synchrony, or something of this kind that would make a self-recovery impractical. I need to implement a read-only fall-back DB, and I'm planning to use a combination of cron script to dump the live db to the slave after having renamed the good (old) versions, and after having verified that all the data in the new dump is fine, renaming the new db to the right name. The problem is that I can't find in the manual a command to rename a database. I will appreciate any suggestion, Thanks, Nico - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
