#maria Log v0.1

logs Catalogue

This page loads from the db, refresh as and when. It displays 24 hrs
For older data http://marialog.archivist.info/previous.php, logs go back to 2009-01-18
Some user stats
Table logs
date_time
user
message
2014-10-01 08:00:39
nno0
Thanks, that would be a solution . £º£©
2014-10-01 08:46:08
AL13N_work
hmm, i get deadlock in queries... no, running queues...
2014-10-01 08:46:24
AL13N_work
no errors in logfile
2014-10-01 08:46:48
AL13N_work
can i find out what is locked at what time?
2014-10-01 08:47:02
AL13N_work
i'm starting to suspect that someone locked the database or something
2014-10-01 08:49:43
danblack
show engine innodb status (assuming innodb tables)
2014-10-01 08:49:52
serg
there is no LOCK DATABASE statement
2014-10-01 08:50:39
serg
knielsen? about MDEV-6581
2014-10-01 08:51:08
knielsen
knielsen looking
2014-10-01 08:51:42
danblack
AL13N_work: what queries and table structure would help with this. Though a deadlock indicates that you're trying to do potentially two incompatible row statements at the same time.
2014-10-01 08:52:30
serg
knielsen: the reasons for this - binlog starts a transaction and marks it read-write. even when only myisam tables are involved. then ha_commit_trans refuses to commit a read-write transaction when read-only is enabled
2014-10-01 08:53:10
serg
knielsen: I see two fixes - don't let binlog start a transaction (that is, don't write statements to the cache unless a transaction is already started)
2014-10-01 08:53:23
serg
knielsen: or don't let binlog mark transaction as read-write.
2014-10-01 08:54:44
serg
knielsen: both changes change results of lots of tests. in the first case, for example, BEGIN/END goes away in many cases, sometimes events are rearranged. All result changes look pretty safe for me (and some are even "more correct" than old results)
2014-10-01 08:55:26
serg
still, I'm looking for a second opinion. these changes do affect lots of tests after all
2014-10-01 08:55:43
knielsen
serg: But isn't it necessary to always log all temporary table changes to the binlog? At least in statement-based mode?
2014-10-01 08:56:17
knielsen
serg: what if later, read-only mode is switched off, and user does INSERT INTO nontemp_table SELECT * FROM temptable ?
2014-10-01 08:56:19
serg
nether fix changes that. In the first fix myisam-only changes won't be wrapped in BEGIN/COMMIT
2014-10-01 08:56:38
serg
in the second change it'll be BEGIN/COMMIT, not BEGIN/Xid
2014-10-01 08:56:58
serg
but the change itself will be in binlog either way
2014-10-01 08:57:11
serg
the decoration will change
2014-10-01 08:57:40
knielsen
serg: Changing this stuff is _really_ dangerous without spending a _lot_ of time to understand what the consequences will be
2014-10-01 08:57:53
knielsen
serg: but first, I'm trying to understand if it's really a bug?
2014-10-01 08:57:58
serg
that's why I'm asking :)
2014-10-01 08:58:38
serg
writing to tem tables used to be allowed
2014-10-01 08:58:44
serg
even under read-only
2014-10-01 08:58:47
knielsen
serg: It seems to me, if the server is in read-only mode, then any statement that would cause write to the binary log should be disallowed, right?
2014-10-01 08:59:24
knielsen
serg: I mean, the whole point of read-only in many cases is precisely that this is a slave, and it must not take changes from anywhere except from the master
2014-10-01 08:59:44
serg
yes (to the latter)
2014-10-01 08:59:46
knielsen
serg: if you're running in GTID mode, for example, and then some user binlogs something on the slave, the GTID failover becomes broken
2014-10-01 09:00:03
serg
hmm
2014-10-01 09:00:03
knielsen
so we absolutely should not allow any binlog writes in read-only mode, as far as I can see
2014-10-01 09:00:20
knielsen
serg: what if you run in row-based binlogging mode, are temporary tables allowed then?
2014-10-01 09:00:41
serg
in read-only, you mean?
2014-10-01 09:00:44
knielsen
yes
2014-10-01 09:01:34
serg
I doubt it, but let me check
2014-10-01 09:02:12
arjenAU
serg I see you're having fun with that bug... didn't realise it was such a can of worms ;-)
2014-10-01 09:02:54
knielsen
well, temporary tables in statement-based replication is nothing but one big can of worms :-(
2014-10-01 09:04:32
serg
knielsen: nope, rbr does not help
2014-10-01 09:05:38
knielsen
serg: In any case, if I understand correctly, we cannot allow any binlogging in read-only mode, also not temporary tables
2014-10-01 09:06:16
knielsen
serg: Have you checked what MySQL 5.6 does? Probably we should have the same behaviour, as we have similar issues if stray binlogging happens on the slave, eg. GTID for example
2014-10-01 09:06:38
serg
no, I didn't. it's a 5.5 regression bug, not 10.0
2014-10-01 09:07:03
knielsen
serg: so you mean, this bug does not exist in 10.0?
2014-10-01 09:09:29
serg
no, I mean, it was reported for 5.5. I'm pretty sure it exists in 10.0, but I can check
2014-10-01 09:09:31
knielsen
I assume the CREATE TEMPORARY TABLE is also binlogged in read-only mode? That would seem to be a bug also...
2014-10-01 09:10:38
knielsen
serg: Doesn't it sound right, that in statement-based mode we need to disallow any temp table operations when binlog is enabled? Because otherwise we might have to write those statements to the binlog, we cannot know this in advance?
2014-10-01 09:10:55
serg
same in 10.0
2014-10-01 09:11:35
knielsen
serg: Maybe in row-based mode it would be possible to allow temp table operations in read-only mode, as I think such operations are not binlogged in row-based mode
2014-10-01 09:12:09
knielsen
serg: (But this part of the code is all a mystery to me, not sure what would be involved in making it allowed in row-based)
2014-10-01 09:12:39
AL13N_work
danblack: looking at innodb status, it talks about that specific deadlock and says it rolled back statement 2, so i'm assuming it got resolved on it's own
2014-10-01 09:12:51
AL13N_work
this happened more than 2h ago
2014-10-01 09:13:02
AL13N_work
and no other deadlocks appear, so i'm ok for now :-)
2014-10-01 09:13:03
AL13N_work
thx
2014-10-01 09:13:03
serg
knielsen: mysql bug report (from 2011, still "verified") says that in rbr it works. either the reporter is wrong or we've broken it. or it worked in 2011 but not anymore
2014-10-01 09:13:05
danblack
yes, one of the updates didn't happen
2014-10-01 09:13:56
danblack
AL13N its up to you to deside if that's important or not.
2014-10-01 09:14:41
serg
knielsen: I'm also not sure about no binlog writes in read-only. temporary tables don't really affect anything. Not even GTID if there's no transaction.
2014-10-01 09:15:14
serg
on the other hand, I don't really understand how GTID works for MyISAM tables at all
2014-10-01 09:16:17
knielsen
serg: I'm pretty sure they affect it. Whenever you binlog something on the slave, you allocate a new GTID. This GTID does not exist on the master. So now you cannot switch another slave with this GTID in its position to the original master
2014-10-01 09:17:09
knielsen
serg: but even without GTID the same problem exists, your binlogs are not compatible between servers in your replication hierarchy, which causes problems when switching a slave from one master to another
2014-10-01 09:17:26
serg
what do you mean?
2014-10-01 09:18:02
knielsen
serg: The point is, the sequence on GTIDs in all binlogs on all servers must be identical.
2014-10-01 09:18:14
knielsen
serg: Suppose the master has: GTID 0-1-1, 0-1-2, 0-1-3
2014-10-01 09:18:25
serg
you said "even without GTID"
2014-10-01 09:18:52
knielsen
serg: well, suppose your replication is S1->S2->S3
2014-10-01 09:18:59
serg
FYI, the test also fails in 5.6
2014-10-01 09:19:23
knielsen
serg: now on S2, you binlog a CREATE TEMPORARY TABLE. This gets binlogged on S3 also (I think?)
2014-10-01 09:19:46
knielsen
serg: now you want to take S2 out and have S1->S3. S3 is replicated up to your CREATE TEMPORARY TABLE
2014-10-01 09:20:06
knielsen
serg: so now you will try to find that statement on S1, but you cannot - because it is not there
2014-10-01 09:33:22
serg
hmm
2014-10-01 09:33:28
serg
this is all pretty ugly
2014-10-01 09:33:52
serg
knielsen: in fact, INSERT into temp table currently succeeds. the error is issued at commit time
2014-10-01 09:34:31
serg
knielsen: so the temp table gets the new row all right, one can simply ignore errors and be fine
2014-10-01 09:35:39
serg
knielsen: so, I still think that one of my first bugfix ideas is valid, and needs to be done. preventing binlog writes in read-only mode is a separate issue
2014-10-01 09:36:03
serg
that needs to be done *before* operation, not at commit, and catch CREATE TEMP TABLE too
2014-10-01 09:38:51
knielsen
serg: Unfortunately, I don't know what the consequence will be of either of your original suggestions. They both sound extremely scary though
2014-10-01 09:39:42
knielsen
serg: I've found it impossible to touch any of this binlog code with the transactional/nontransactional binlog cache and all that stuff, except in a way that "it works the same after my changes as before"
2014-10-01 09:40:46
knielsen
there are just so many wierd cases that can break.
2014-10-01 09:46:59
serg
okay, okay
2014-10-01 09:51:39
knielsen
serg: sorry if I'm not of much help...
2014-10-01 17:13:33
catphish_
i'm really sorry, this is the third time i've been hit with the same optimizer problem, and i really can't remember what was said on previous occasions so i'm going to ask again. essentially when running "select * from table where indexed_column = n order by primary_key limit 10", the index on indexed_column is rarely used, with the primary key being used instead, despite the fact that this is usually hugely inefficient, the problem doesn't happ
2014-10-01 17:13:33
catphish_
en if the order or limit are removed
2014-10-01 20:24:10
anomalyst
trying to restore akonadi and install/use PAM plugin
2014-10-01 20:24:39
anomalyst
INSTALL SONAME 'auth_pam';
2014-10-01 20:24:40
anomalyst
ERROR 1127 (HY000): Can't find symbol '(null)' in library
2014-10-01 20:25:08
anomalyst
[mysqld]
2014-10-01 20:25:10
anomalyst
plugin-load=auth_pam.so
2014-10-01 20:26:01
serg
you need either INSTALL or plugin-load, but not both
2014-10-01 20:26:16
anomalyst
serg: neither seems to work
2014-10-01 20:26:35
anomalyst
i dont see it under 'show plugins'
2014-10-01 20:26:42
serg
ok, do you mean you run that INSTALL SONAME when you didn't have plugin-load?
2014-10-01 20:26:55
serg
or was it with run with plugin-load?
2014-10-01 20:27:25
anomalyst
serg: tried that same 'null' error
2014-10-01 20:27:57
serg
what mariadb version is it?
2014-10-01 20:28:12
anomalyst
serg: status after restart seems clean after adding mysqld option
2014-10-01 20:29:15
montywi
serg: it would be nice to provide a little better error message...
2014-10-01 20:30:08
anomalyst
serg: Fedora 20 v5.5.39
2014-10-01 20:31:04
anomalyst
montywi: i'm guessing its because there might be a problem with the plugin
2014-10-01 20:31:51
anomalyst
how can I list the symbols it is searching?
2014-10-01 20:33:01
serg
anomalyst: ok, this error message is a bug (I vaguely remember fixing it) but it basically happens only when you have loaded the plugin with plugin-load, and *then* tried to load it again with INSTALL SONAME
2014-10-01 20:33:58
serg
the bug results in a confusing error message on the attempt to load an already installed plugin with INSTALL SONAME
2014-10-01 20:34:02
montywi
serg: what is it that causes digest hash to change in 10.1 ?
2014-10-01 20:34:03
serg
so it's pretty harmless
2014-10-01 20:34:09
montywi
is it any change in yacc ?
2014-10-01 20:34:20
anomalyst
serg: yes I did that, how can I reassure myself PAM is loaded?
2014-10-01 20:34:29
serg
not any, but yes. changes in yacc
2014-10-01 20:34:41
serg
show plugins should show it
2014-10-01 20:34:54
montywi
ok that i replace digest number with '#' ?
2014-10-01 20:35:15
montywi
it's not very useful to have a not stable number in the test system...
2014-10-01 20:35:37
serg
yeah, go ahead. thanks
2014-10-01 20:36:00
anomalyst
serg: I see "mysql_native_password"
2014-10-01 20:36:16
serg
anomalyst: and that's all?
2014-10-01 20:37:08
anomalyst
serg: mysql_old_password, buncha innodb stuff + Aria
2014-10-01 20:38:27
serg
anomalyst: then check the error log, if a plugin fails to load at startup, the error should be there
2014-10-01 20:48:43
anomalyst
serg:
2014-10-01 20:48:44
anomalyst
141001 12:46:37 [ERROR] Function 'pam' already exists
2014-10-01 20:48:46
anomalyst
141001 12:46:37 [Warning] Couldn't load plugin named 'pam' with soname 'auth_pam.so'.
2014-10-01 20:49:11
serg
that's when you loaded it for the second time
2014-10-01 20:49:31
serg
check earlier entries
2014-10-01 20:51:22
anomalyst
serg: N, I delete the log and systemctl restart mariadb, just tried again, same
2014-10-01 20:52:41
anomalyst
serg: does it remember "INSTALL" from invocation to invocation?
2014-10-01 20:52:55
serg
yes
2014-10-01 20:57:30
anomalyst
serg: how xan I make it go away so the mysqld works "UNINSTALL PLUGIN 'auth_pam';" complains about syntax errror
2014-10-01 20:58:15
serg
the plugin is called 'pam'. you need to do either UNINSTALL PLUGIN 'pam' or UNINSTALL SONAME 'auth_pam'
2014-10-01 20:58:30
serg
eh, sorry
2014-10-01 20:58:42
serg
UNINSTALL PLUGIN pam -- no quotes
2014-10-01 20:58:46
serg
or
2014-10-01 20:58:51
serg
UNINSTALL SONAME 'auth_pam'
2014-10-01 21:01:42
anomalyst
serg: kk that worked, restarting & show plugins gives me a pam entry tyvm
2014-10-01 21:01:51
serg
welcome
2014-10-01 21:41:58
anomalyst
mysql -u lll
2014-10-01 21:42:20
anomalyst
Access denied for user 'lll'@'localhost'
2014-10-01 21:42:55
anomalyst
im trying to create a root level user
2014-10-01 21:43:14
anomalyst
PAN authenticated
2014-10-01 21:43:37
anomalyst
s/PAN/PAM/
2014-10-01 21:48:17
anomalyst
GRANT ALL PRIVILEGES ON *.* TO 'lll'@'localhost' IDENTIFIED BY PASSWORD '*[redacted]' WITH GRANT OPTION
2014-10-01 21:54:05
anomalyst
SELECT user,host FROM user where user like 'lll%'; gives ' lllrjs | localhost '
2014-10-02 00:35:22
jenia_
hello.
2014-10-02 00:36:09
jenia_
when i do: SET PASSWORD FOR 'jeffrey'@'jenia' = PASSWORD('12341234);
2014-10-02 00:36:22
jenia_
it tells me: ERROR 1133 (28000): Can't find any matching row in the user table
2014-10-02 00:36:39
jenia_
i mean i do this: set password for 'jenia'@'localhost' = password("12341234")
2014-10-02 00:36:54
jenia_
however its clearly there!! what am i doing wrong?
2014-10-02 00:40:06
kolbe
jenia_: "clearly there" huh?
2014-10-02 00:40:28
jenia_
clearly its there* lol. but i noticed that the host for "jenia" is %
2014-10-02 00:40:35
kolbe
SELECT user, host, password FROM mysql.user where user='jenia';
2014-10-02 00:40:37
jenia_
what does that mean? can i change that to localhost?
2014-10-02 00:40:49
kolbe
jenia_: right. users in MySQL are the combination of usernames+hostname
2014-10-02 00:41:01
kolbe
'jenia'@'localhost' is a totally different user than 'jenia'@'%'
2014-10-02 00:41:02
jenia_
so i should just change it to localhost?
2014-10-02 00:41:13
kolbe
well, what are you trying to do?
2014-10-02 00:41:23
kolbe
if you want to change the password for an existing user, specify the correct username + host for the user
2014-10-02 00:41:25
jenia_
i need to allows wordpress to connect there
2014-10-02 00:41:34
kolbe
if you want to do something else, you shouldn't be using SET PASSWORD
2014-10-02 00:41:46
kolbe
is wordpress running on the same host as mysql?
2014-10-02 00:41:51
jenia_
yes
2014-10-02 00:41:53
kolbe
what error does wordpress give now when it tries to connect?
2014-10-02 00:42:06
jenia_
one second
2014-10-02 00:43:25
jenia_
it tells me there an error:
2014-10-02 00:43:33
jenia_
Are you sure you have the correct username and password? Are you sure that you have typed the correct hostname?....
2014-10-02 00:43:41
kolbe
that's not a MySQL error
2014-10-02 00:43:49
kolbe
there's a MySQL error buried somewhere... may an error log... i don't know
2014-10-02 00:43:56
kolbe
if you can't find it, see if someone in #wordpress can help you locate it
2014-10-02 00:44:09
jenia_
humm. okay thanks a lot ;)