Re: MySQL Replication - promote slave
Hi, 1. flush logs on the master (only if it's accessible, of course). Not really necessary if you block clients (firewall rule for new connections to port 3306?). Anyway, why don't you use a dual-master setup? I find that this is a whole lot easier to administer than a master/ fallback-slave situation. In particular, restoring the master after it comes back happens automatically, or (if you need to re-install the master from scratch) the command slave# mysqldump --single-transaction --master-data=1 --all-databases \ | ssh master mysql ensures that you can continue to use the slave while restoring the master. Assuming you use only transaction-safe tables, of course. (You should.) -- -- Matthias Urlichs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Deadlock question
Hi, I have the following table and two concurrent jobs, trying to insert data into it. They deadlock on accessing an index which shouldn't collide (the date is part of the index, and the jobs process data for different dates). This is mysql 5.0.51a-24+lenny1~bpo40+1-log. Any ideas? Is this likely to not happen with 5.1? CREATE TABLE `test` ( `kunde` int(11) NOT NULL default '0', `quelle` int(11) NOT NULL default '0', `datum` int(10) unsigned NOT NULL default '0', `ziel` tinyint(4) default NULL, `pakete` bigint(20) default NULL, `bytes` bigint(20) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, KEY `qkd` (`kunde`,`datum`), KEY `qqd` (`quelle`,`datum`), KEY `timestamp` (`timestamp`), KEY `datum` (`datum`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1 *** 1. row *** Status: = 090612 13:52:17 INNODB MONITOR OUTPUT = Per second averages calculated from the last 14 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 2911205, signal count 2566010 Mutex spin waits 0, rounds 827176046, OS waits 1619184 RW-shared spins 8458591, OS waits 749052; RW-excl spins 15010669, OS waits 188067 LATEST DETECTED DEADLOCK 090612 8:35:15 *** (1) TRANSACTION: TRANSACTION 0 534404698, ACTIVE 44 sec, process no 4871, OS thread id 1229306208 inserting mysql tables in use 1, locked 1 LOCK WAIT 7 lock struct(s), heap size 3024, undo log entries 2 MySQL thread id 2773037, query id 633323918 acct1.backup.noris.net 10.1.1.95 kunde update insert low_priority into test set kunde=9, quelle=-715188982, datum=20090602, ziel=119, pakete=192, bytes=10752 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 692344 n bits 456 index `qkd` of table `einzel/test` trx id 0 534404698 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 800c; asc ;; 1: len 4; hex 013217cc; asc 2 ;; 2: len 6; hex 0337b2b8; asc7 ;; *** (2) TRANSACTION: TRANSACTION 0 534294520, ACTIVE 4074 sec, process no 4871, OS thread id 1249306976 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 35927 lock struct(s), heap size 6256624, undo log entries 411437 MySQL thread id 2763571, query id 633351909 acct1.backup.noris.net 10.1.1.95 kunde update insert low_priority into test set kunde=9, quelle=-1066572043, datum=20090601, ziel=100, pakete=64, bytes=4096 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 692344 n bits 416 index `qkd` of table `einzel/test` trx id 0 534294520 lock_mode X locks gap before rec Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 800c; asc ;; 1: len 4; hex 013217cc; asc 2 ;; 2: len 6; hex 0337b2b8; asc7 ;; Record lock, heap no 347 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd1af4; asc ;; Record lock, heap no 348 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd25bd; asc % ;; Record lock, heap no 349 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd25be; asc % ;; Record lock, heap no 350 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd25c3; asc % ;; Record lock, heap no 351 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd25c4; asc % ;; Record lock, heap no 352 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd25c5; asc % ;; Record lock, heap no 353 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd25cb; asc % ;; Record lock, heap no 354 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd2633; asc 3;; Record lock, heap no 355 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd2634; asc 4;; Record lock, heap no 356 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd2635; asc 5;;
Re: MYSQL PROCESS
On Thu, 14 Aug 2008 10:27:29 +0530, Krishna Chandra Prajapati wrote: Please help me that what should i do. Nothing. It's just multi-threaded. Everything's fine. -- Matthias Urlichs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data Truncated warning about join, not insert?
Hi, Tom Cunningham wrote:
I have a funny problem: I'm getting thousands of Data Truncated
warnings when I insert a single row of data into a table, using an
INSERT...SELECT.
I bet that the columns you're joining on have different sizes.
--
Matthias Urlichs | {M:U} IT Design @ m-u-it.de | [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index not used for select distinct?
Hi, Bill Easton wrote:
I think that MySQL is doing what would be expected, namely an index scan
which reads entire index on IP and selects distinct values of IP.
What I expect it to do is to give me all the distinct values in that
row. It should be able to notice that there are only a few distinct
values in there, and a select distinct over an indexed column doesn`t
need to do a full scan.
An open-coded loop
val = db.Do(select min(IP) from test)
while (val) {
process(val)
val = db.Do(select min(IP) from test where IP '$val')
}
runs almost instantly. MySQL should be able to do that optimization, it's
rather obvious (ten million rows, and IP has less than 100 different
values).
--
Matthias Urlichs | {M:U} IT Design @ m-u-it.de | [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change master on replication
Hi, Sean Leach wrote:
my question is in the best way to sync Slave 1 and Slave 2 with Master B
since Master A and Master B weren't completely in sync.
Well, obviously you need to sync the slaves to B. ;-)
I've written a Python script that does this. Holler if you want it.
NB, the script needs unique indices (duh ;-) and update timestamps on all
tables. (It works without a timestamp, but obviously *way* slower.)
--
Matthias Urlichs | {M:U} IT Design @ m-u-it.de | [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index not used for select distinct?
We have a slight opimization problem here.
Given this table:
CREATE TABLE `test` (
`IP` varchar(15) collate latin1_german1_ci NOT NULL default '',
`Type` enum('WARN','ERROR','FATAL','UNKNOWN') collate latin1_german1_ci NOT NULL
default 'WARN',
`epoch` int(10) unsigned NOT NULL default '0',
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
)
... containing ten million records; the IP column holds only a handful
of distinct values. Given this, I would expect a select distinct ip
to return immediately.
However,
explain select distinct ip from test;
++-+---+---+---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
++-+---+---+---+--+-+--+--+-+
| 1 | SIMPLE | test | index | NULL | IP | 15 | NULL | 10991123
| Using index |
++-+---+---+---+--+-+--+--+-+
takes a *long* time and obviously scans the whole table.
Ideas, anybody?
MyISAM vs. InnoDB behave identically.
4.0 or 4.1.5 also didn't make a difference; I didn't test 5.0, as this
is supposed to be a production system.
--
Matthias Urlichs | {M:U} IT Design @ m-u-it.de | [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index not used for select distinct?
Hi,
gerald_clark:
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
Your second key is redundant.
True (there's a UNIQUE missing in there).
Deleting the first key, however, doesn't change anything.
--
Matthias Urlichs | {M:U} IT Design @ m-u-it.de | [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump doesn't quote table names
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Friday 07 March 2003 18:38, Mark Matthews wrote: Why not mysqldump --quote-names test Because I might not actually _want_ quoted names in my output. This is not about the mysqldump output. This is about the correctness of the statements mysqldump sends to the server, which is an entirely different thing and not something which should be controllable by any arguments to mysqldump. Anyway, the fix is reasonably trivial. I've already mailed a patch to [EMAIL PROTECTED]; I can resend it to someplace else if that might be more appropriate. - -- Matthias Urlichs|noris network AG|http://smurf.noris.de/ - -- Mercedes vermietet jetzt den Unterboden der A-Klasse als Werbeflche. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+adRR8+hUANcKr/kRAnXNAKCnyhJ5E/T9/wdLEDg8vQp33nM/hgCeLsxi vS2k3eYPQM5yH6PSq2L/1O0= =9x9C -END PGP SIGNATURE- - 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
Default-less columns ?
Hi, is it possible to have a column which does NOT have a default value, i.e. an INSERT query should fail if no value is given..? The obvious idea, i.e. CREATE TABLE foo (bar integer not null default null); doesn't work. -- Matthias Urlichs | noris network AG | http://smurf.noris.de/ - 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
Re: Somebody kindly get me off this list
At 15:17 -0700 2010-05-02, Angela Neff wrote: I have tried numerous methods as instructed in the mySQL listerserv e-mail to get off this list but am still on it. Can somebody help me out? I got 779 e-mails today. Set your date correctly and try again..! -- Matthias Urlichs - 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
Re: MySQL.org
Hi, The article mentions that the Web site will confuse new users, but I think this one will confuse them even more: http://mysql.net/ (for non-Japanese users that is). Korean, actually. -- Matthias Urlichs (being exceedingly helpful today) - 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
Alternate database handlers / tests
Hi, did anybody ever try to run the standard test suite with another table handler? We think about switching to InnoDB, but we see problems..: - The maximum blob/text length. We use MySQL for our support emails. (Restructuring the database to use a mix of InnoDB and myISAM would work, but not in the short term.) The docs say this should be fixed by now, but is it?? - No fulltext index on non-MyISAM tables. (Why not? You could keep the fulltext index in a separate file.) - The worst, though, is that the standard test suite fails when using InnoDB as the default handler. IMHO it shouldn't -- all tests which are not table-specific should work with all tables, and those that are should explicitly specify the table type they need. -- Matthias Urlichs - 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
RE: Full text search gotchas
At 13:53 +0200 2001-06-26, Bruce Stewart wrote: If you searched for '% john %' - note the spaces, you would not find 'johnson' etc.. *Sigh* Since when do wildcards work with the fulltext index? Besides, there are characters beside a space wich can delimit words. Three examples: Commas. Line breaks. The start of the actual data. If you really want words, use rlike and \...\ (assuming that MySQL can do egrep-like escapes). But it'll be dirt slow compared to the fulltext index. -- Matthias Urlichs - 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
Re: auto-increment sequence question
At 13:49 +0200 2001-06-26, Gunnar von Boehn wrote: Lets take a single row, integer value for holding the sequence: CREATE TABLE sequence( id int4; ); That works, though I would use a single table for all sequences, not a new table per sequence. The other solution would be to do check the table itself what the maximum is: LOCK tables customer WRITE SELECT MAX(id) FORM customer WHERE id 1; ## your first normal customer INSERT INTO customer SET id=#new_id#, ... UNLOCK TABLES though MySQL's optimizer doesn't understand how to do find a ranged maximum by checking the index on the ID, so it's not a fast solution at present. = Hello MySQL developers: Please add that. Same for the equivalent ... ORDER BY id DESC LIMIT 1 -- Matthias Urlichs - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: $sth-{mysql_insertid} not working in 3.23.38?
At 0:36 -0600 2001-06-23, Colin Faber wrote:
heh, my ($id) = $dbh-selectrow_array(SELECT MAX(id) FROM tbl;);
You're in for a VERY nasty surprise as soon as two of these happen to
run in parallel.
Our code uses $sth-{mysql_insertid}. We don't have any 3.22 servers,
though -- they can't do (some? I forget) binary operations in a
SELECT right, and our customers _do_ have some reasonable
expectations about SQL compatibility.
--
Matthias Urlichs
-
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
Re: Sending data, but no data sent?
Hi, Sinisa Milivojevic: First of all, where have you got 3.23.34 when it is not out ?? It's the version in your current BitKeeper archive. Second what type of column is id and what does PRIMARY KEY consist of. explain ticketid; Field Type Null Key Default Extra id varchar(100) PRI ticket int(11) MUL 0 sender int(11) MUL 0 added int(11) YES NULL seqint(11) 0 extern char(1) YES NULL inhalt mediumtext YES NULL typsmallint(6) YES NULL d_data int(11) YES NULL show index from ticketid; TableNon_unique Key_name Seq_in_index Column_name Collation ticketid 0 PRIMARY 1 id A ticketid 1 ticket 1 ticket A ticketid 1 ticket 2 seq A ticketid 1 sender_i1 sender A Third --with-debug is slower then without. But not that much slower... -- Matthias Urlichs | noris network AG | http://smurf.noris.de/ - 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
Re: Sending data, but no data sent?
Hi, Sinisa Milivojevic: And what happens if you put quotes ??? ... id = '123'... Then it works, of course. But the nice thing about the numeric select is that it also accepts values like 123x45 or 123-45-67 (but not 1234), which is why I wanted to use it. -- Matthias Urlichs | noris network AG | http://smurf.noris.de/ -- New York is the biggest boob town in America. All any of those hokum peddlers need to do in selling New Yorkers their phony` goods is to ask what they want, and they'll be sure to get it. -- Bat Masterson - 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
RedHat 7.0 and threading
Hi, we had a rather ... interesting ... problem with mysqld, compiled on a system with glibc-2.1.3. We tried to run the server on a RedHat 7.0 machine and had inexplicable pthread errors. The solutions turned out to be either to downgrade /lib/libpthreads.so.0 and /lib/librt.so.1 to the versions from a RH 6.4 system, which worked temporarily but is IMHO somewhat dangerous, or to upgrade glibc to the version from redhat's Rawhide (2.2.1-3 at the moment). We didn't try the 2.2 from redhat's 7.0 updates, because I follow the glibc mailing list. :-/ It's probably a compiler problem; Rh 7.0 used a rather buggy gcc :-( and the version -- Matthias Urlichs | noris network AG | http://smurf.noris.de/ -- If wishes were horses, then beggars would be thieves. - 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
Re: Large text searches
Hi, Sander Pilon: FULLTEXT indexes are about 95% of the size of the table, and they can perform quite well or very slow, depending on what you do. For my use (a trouble-ticket database), they behaved extremely poorly, unfortunately. The tickets frequently are over a MByte in size, which results in _really_ long insert times (five minutes), which (the way MySQL currently is designed) slows down the queries in an unacceptable way. Fixing that would probably require a new thread for index maintainance, an extra file for the fulltext index, and a sensible file format that can be searched while being updated. I rather like the relevancy algorithm. The insistence on only indexing full words is more problematic, however. -- Matthias Urlichs | noris network AG | http://smurf.noris.de/ -- Most people ignore most poetry because most poetry ignores most people. -- Adrian Mitchell - 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
Re: Linux limited to 256 connections by default?
Hi, There's one other problem to be aware of. Currently, MySQL uses "fd_set" data structures in a few places. GLIBC 2.1 has __FD_SETSIZE hardwired to 1024. This means that any use of a file descriptor 1023 WILL cause data corruption or crashes. Somebody please change MySQL to only use malloc'ed fd_sets. Until then, setting the fd limit to 1024 is a Bad Idea. -- Matthias Urlichs | noris network AG | http://smurf.noris.de/ -- Californians do it laid back. - 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
