#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
2017-02-24 02:29:22
rmbeer
why not work? http://pasteall.org/263671
2017-02-24 02:30:15
rmbeer
i use http://www.1keydata.com/es/sql/sql-alter-table.php , https://support.office.com/es-es/article/Crear-o-modificar-tablas-o-índices-mediante-una-consulta-de-definición-de-datos-d935e129-229b-48d8-9f2d-1d4ee87f418e and http://www.tutorialesprogramacionya.com/sqlserverya/temarios/descripcion.php?cod=90&punto=84&inicio=
2017-02-24 02:30:34
rmbeer
all diferent syntax, and nothing work...
2017-02-24 02:30:38
grknight
rmbeer: don't use single quotes on tables and columns.. use the backtick instead. not 'log' but `log`
2017-02-24 02:32:45
grknight
alter table `log` add `host` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL;
2017-02-24 02:33:05
rmbeer
grknight, yes, work. thank :)
2017-02-24 02:39:25
rmbeer
i can't found the documentation for work with libraries of mariadb, need work with headers for C++
2017-02-24 02:40:17
rmbeer
well, i found recent :/ : https://mariadb.com/kb/en/mariadb/c-client-library-mariadb/
2017-02-24 02:41:13
rmbeer
hummm, this is part from mariadb or is a external app? (other comunity)
2017-02-24 02:45:34
rmbeer
well... change question: all library in C or C++ of mariadb, also work with documentation of mysql?
2017-02-24 03:09:38
rmbeer
ok , find only for C and work only with sql commands...
2017-02-24 04:34:58
mrtrump
is there something like search indexing with mariadb?
2017-02-24 04:35:06
mrtrump
instead of spinx or solr?
2017-02-24 08:21:44
romgo
Hello guys. I'm running a 4 nodes galera cluster. Today I just had a crash : all nodes are up and running and suddenly, for an unknown reason I can't run any query on the node.
2017-02-24 08:23:00
romgo
on any node in fact.
2017-02-24 08:23:17
romgo
the solution is to restart one node, and all cluster get up an running again.
2017-02-24 08:23:50
romgo
during the incident show status like 'wsrep%'; doesn't retun any issue. cluster_status is Primary
2017-02-24 08:23:56
romgo
connected at On
2017-02-24 08:24:10
romgo
so do you have any idea on how to troubleshoot and understand what happened ?
2017-02-24 13:15:52
bumbar
how can i enable slow query log? i've added these https://dpaste.de/7hs0 settings to /etc/mysql/mariadb.conf.d/50-server.cnf, restarted service and query log file isn't there
2017-02-24 13:20:51
jkavalik
bumbar, "show global variables like 'slow_query%';"
2017-02-24 13:21:54
bumbar
slow_query_log | OFF
2017-02-24 13:28:44
jkavalik
bumbar, then it looks like your config is not used
2017-02-24 13:29:43
jkavalik
bumbar, is there any section in the 50-server.cnf file? [mysqld] or [maria] kind of stuff?
2017-02-24 13:37:16
bumbar
jkavalik, there's [server], [mysqld] rest are empty ( [mariadb], [mariadb-10.0] )
2017-02-24 13:37:32
bumbar
i've put them under [mysqld]
2017-02-24 13:38:13
jkavalik
bumbar, sounds ok, are other config values being picked from that file? usually only my.cnf is loaded by default and it then may contain an include directive
2017-02-24 13:38:50
bumbar
my.cnf includes /etc/mysql/mariadb.conf.d/ and /etc/mysql/conf.d/
2017-02-24 13:41:10
jkavalik
bumbar, as a side note, you can modify these variables while the server is running, no restart needed (only after changing global variables, they will be used by new connections, but the old ones will retain the old values)
2017-02-24 13:41:51
jkavalik
but this should work.. try putting it directly in my.cnf to see if it is the right one
2017-02-24 13:44:16
bumbar
and create a new section or under [client-server] ?
2017-02-24 13:44:49
mdk
Hi, did anyone know why maria don't optimize "SELECT MAX(a) FROM t1 LEFT JOIN t2 ON b" like it optimizes "SELECT MAX(a) FROM t1" ? With an index on a. First is fast, second is slow, they (obviously?) give the same result, so the 2nd one may be optimized to the first form?
2017-02-24 13:45:44
mdk
(Sry for "ON b" I mean "USING(b)" but it does no change the idea, it's just a LEFT JOIN.)
2017-02-24 13:46:49
grknight
mdk: because they are not the same
2017-02-24 13:47:07
bumbar
jkavalik, i've tried under [client-server] section and created [mysqld] in my.cnf and there's no log file...
2017-02-24 13:47:21
mdk
grknight: enlighten me, what is the difference between both? When I compute the 2nd in my brain, it optimizes out the LEFT JOIN, is there a bug in my brain optimizer?
2017-02-24 13:48:02
jkavalik
bumbar, [mysqld] would be the right one..
2017-02-24 13:48:04
mdk
grknight: My brain does not optimizes out a JOIN because it can "filter out" some rows and change the MAX value, but it does for LEFT JOIN.
2017-02-24 13:48:36
mdk
grknight: what did I missed?
2017-02-24 13:48:41
jkavalik
bumbar, check "mysqld --help --verbose | grep -C2 my.cnf"
2017-02-24 13:48:43
grknight
mdk: think about your join query if it was rewritten as "SELECT MAX(a) FROM t1, t2 WHERE t1.b = t2.b" you cannot look at the a index because b has to be sorted first
2017-02-24 13:50:30
bumbar
jkavalik, "Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf", i only have /etc/mysql/my.cnf file
2017-02-24 13:50:33
grknight
it's not exactly the same, but a join clause is similar to a where
2017-02-24 13:51:42
mdk
grknight: your statement is a equivalent to a JOIN, not a LEFT JOIN
2017-02-24 13:52:02
mdk
grknight: that's why I don't optimize out for a JOIN, but I do for a LEFT JOIN
2017-02-24 13:52:39
grknight
mdk: like i said, it's not exactly the same, but all b rows must be considered first so that is generally the primary index consideration
2017-02-24 13:53:27
mdk
grknight: Why should they be considered first as they can't change the result of the query ?
2017-02-24 13:53:32
jkavalik
mdk, what version?
2017-02-24 13:53:42
grknight
mdk: sure it can
2017-02-24 13:54:26
mdk
jkavalik: 10.1.12+maria-1~jessie
2017-02-24 13:54:37
jkavalik
with left join and no limiting condition it actually can't, it can only return some rows multiple time, which has no effect on max() results
2017-02-24 13:54:53
mdk
grknight: I don't see how the LEFT JOIN can change the result, can you help me see it?
2017-02-24 13:55:08
mdk
jkavalik: I agree
2017-02-24 13:55:25
jkavalik
mdk, use EXPLAIN EXTENDED <query> and SHOW WARNINGS (maybe the EXTENDED is not needed for 10.1 anymore, not sure, 10.0 here)
2017-02-24 13:55:48
jkavalik
mine shows "select tables optimized away" on similar query
2017-02-24 13:56:55
mdk
jkavalik: oh wait for it ... looks like the 10.1.12 actually does the optimization ... I tried to reproduce on my dev server which is a very old one
2017-02-24 13:57:33
mdk
OK so I should dig deeper
2017-02-24 13:58:23
jkavalik
yep, check the older server, maybe the needed optimization is not done there
2017-02-24 13:58:24
mdk
(I over simplified the issue to isolate it and reproduced it on a dev server, which has this specific issue, that my prod sever don't have, but I still have an optimization problem on my production server, have to retry to isolate it)
2017-02-24 13:59:27
mdk
My case is a view doing some left joins, having a subquery in the select, which itself is a view, ... nothing should be able to change the MAX but the query is slow. I'm trying to isolate the point which break the optimization
2017-02-24 14:00:15
jkavalik
mdk, a view is an entirely different can of worms
2017-02-24 14:00:50
mdk
jkavalik: It's a merged view, so it "not that different" I think
2017-02-24 14:00:55
jkavalik
mdk, some optimizations which could be one on a full (equivalent) query are not done on a view
2017-02-24 14:01:02
jkavalik
*be done
2017-02-24 14:01:19
mdk
jkavalik: even on merged views that (should ?) look like full queries /
2017-02-24 14:03:46
jkavalik
yep, iirc there are some limitations, not sure what exactly though - most of them are if the view cannot be merged (contains grouping etc)
2017-02-24 14:04:16
jkavalik
mdk, but simple enough - try getting the full query and executing it manually, check the explain if it is any different
2017-02-24 14:05:22
mdk
Looks like having a subquery in the SELECT blocks this optimization (the optimization still work in the view, without subqueries in the select)
2017-02-24 14:05:54
mdk
But a subquery is like a left join here : it will not be able to change the MAX value...
2017-02-24 14:06:51
jkavalik
but possibly a dependent (correlated) subquery may be only evaluated if there is an actual row? (which with "tables optimized away" there is not)
2017-02-24 14:08:53
mdk
mdk still narrowing down, tring without views
2017-02-24 14:12:52
mdk
Got something: A plain query (not a view), optimizes correctly (with 7 left joins, no subquery) but failes to optimize if one of the left join is a view with ALGORITHM=UNDEFINED
2017-02-24 14:14:17
mdk
As the sub-view has a group by, the picked algorithm will be temptable, so for my query point of view, it's an almost normal table
2017-02-24 14:14:21
mdk
should not break the optimization/
2017-02-24 14:14:23
mdk
?
2017-02-24 15:55:20
yeep
Has anyone here successfully installed the perl-DBD-MySQL package along side the Galera cluster?
2017-02-24 16:15:06
webly
hi
2017-02-24 16:15:28
webly
is it possible setting innodb_flush_method = NULL as argument to mysql (mariadb)? :)
2017-02-24 16:21:40
webly
anyone here? :O
2017-02-24 16:27:38
yeep
I am
2017-02-24 16:27:43
yeep
but I dont know the nswer
2017-02-24 16:27:46
webly
yeep: hi!
2017-02-24 16:27:48
yeep
been asking my own question
2017-02-24 16:27:54
webly
so we are two then :D
2017-02-24 16:28:06
yeep
this is true
2017-02-24 16:28:25
yeep
I dont see any MariaDB forum so I am guessing this is the only place
2017-02-24 16:29:02
webly
oh no :(
2017-02-24 16:29:02
grknight
i have DBD-MySQL installed, but Gentoo always does :)
2017-02-24 16:29:05
webly
maybe we ask in mysql
2017-02-24 16:29:34
webly
damn, mysql channel needs registering
2017-02-24 16:29:36
webly
I don't want that
2017-02-24 16:29:54
chris-p
what's wrong with registering with nickserv?
2017-02-24 16:30:05
webly
I don't like registering :(
2017-02-24 16:30:11
webly
too much monitoring, logging and tracking
2017-02-24 16:30:31
webly
basically all freenode channels are logged down for eternity with lots of info
2017-02-24 16:31:23
chris-p
ah
2017-02-24 16:32:20
yeep
grknight: Is that the Perl package you have installed?
2017-02-24 16:32:55
chris-p
what was the question?
2017-02-24 16:32:56
grknight
yeep: yes, it's required to have with MariaDB on Gentoo
2017-02-24 16:33:53
webly
chris-p: I need to set innodb_flush_method = NULL for mariadb - what is an easy way?
2017-02-24 16:34:21
chris-p
can you join #mysql just not speak?
2017-02-24 16:34:56
webly
yes
2017-02-24 16:34:58
yeep
I am trying to change our cluster over to xtrabackup, I have the Galera-server as well as the Mariadb-common packages running the db right now. With xtrabackup it has a dependency on that perl-DBD-MySQL package, but that package requires mysql-community-common.. which is a problem
2017-02-24 16:35:11
yeep
grknight: I assume you got around that?
2017-02-24 16:35:18
chris-p
would you please be able to?
2017-02-24 16:35:25
chris-p
I've asked for you
2017-02-24 16:35:36
chris-p
as I don't know the answer
2017-02-24 16:40:08
chris-p
17:41 < aradapilot> @chris-p set it in my.cnf (or just delete the row, null means default) and restart the db
2017-02-24 16:40:11
chris-p
17:41 -!- db` [DON@gateway/shell/elitebnc/x-aayadcmwytdndayq] has quit [Ping timeout: 240 seconds]
2017-02-24 16:40:14
chris-p
17:41 < aradapilot> though you might have performance issues, direct IO is better in most cases
2017-02-24 16:40:17
chris-p
17:42 < aradapilot> assuming local storage
2017-02-24 16:40:21
chris-p
webly: ^^
2017-02-24 16:41:36
webly
webly: were these posts for me?
2017-02-24 16:41:40
chris-p
yup
2017-02-24 16:42:22
webly
chris-p: can I also set it without my.cnf, passing an argument to mysql itself?
2017-02-24 16:42:23
webly
I use it in docker
2017-02-24 16:42:27
webly
mariadb official docker image
2017-02-24 16:44:15
chris-p
webly: docker run -it --rm mariadb:tag --verbose --help
2017-02-24 16:44:18
chris-p
see if the option is there
2017-02-24 16:44:23
webly
thanks
2017-02-24 16:45:30
chris-p
otherwise you can create a directory and mount it as /etc/mysql/conf.d in the docker image and create a fil innodb_flush.cnf and then include it in that
2017-02-24 16:49:46
yeep
Has anyone installed xtrabackup on a Galera Cluster?
2017-02-24 17:28:36
grknight
yeep: our packages don't work like that
2017-02-24 18:05:33
yeep
grknight, not sure I understand what you mean
2017-02-24 18:06:17
grknight
yeep: Gentoo has it's own package system is what i mean
2017-02-24 18:06:34
yeep
oh, I thought you were talking mariadb
2017-02-24 18:07:45
yeep
grknight: "our" meaning you work for Gentoo then?
2017-02-24 18:08:59
grknight
i am a volunteer for them yes
2017-02-24 18:09:25
yeep
Very cool
2017-02-24 18:15:04
igor
hi galya!
2017-02-24 18:40:42
suprovsky
hey there, i've got a problem with starting mariadb daemon on QNAP TS-453A, there's a logs: http://pastebin.com/e6QmbpGF
2017-02-24 18:41:07
suprovsky
could you tell me what should I check to see what am I doing wrong?
2017-02-24 18:48:35
grknight
suprovsky: is this a new install?
2017-02-24 18:49:03
suprovsky
oh, hey grknight, i thought you're only on #gentoo
2017-02-24 18:49:26
grknight
well this install doesn't look like gentoo
2017-02-24 18:49:49
suprovsky
no, i've modified a bit configs, but after crashing I went back to default, but I still get crashes on it
2017-02-24 18:50:06
grknight
did you install the system tables? because they are missing
2017-02-24 18:50:27
suprovsky
nope
2017-02-24 18:50:57
grknight
this needs to have mysql_install_db on it
2017-02-24 18:51:06
grknight
run on it
2017-02-24 18:57:33
suprovsky
grknight, is mysql_install_db should be in bin folder?
2017-02-24 18:59:36
grknight
could be, but not on Gentoo, which it doesn't seem like you are running since you used mysqld_safe
2017-02-24 19:00:21
grknight
on Gentoo it goes to /usr/share/mysql/scripts
2017-02-24 19:01:11
suprovsky
actually we're not talking about gentoo, i just remember your nickname from that IRC room
2017-02-24 19:01:24
grknight
ok :) but the script could be anywhere
2017-02-24 19:01:50
python476
hi there
2017-02-24 19:02:22
python476
I have a 200MB sql dump I plan to load on a small vm to check a few things, how much memory could that require (grossly)
2017-02-24 19:03:46
grknight
python476: depends on how you configure the buffers... the default buffers vary by version
2017-02-24 19:03:55
MadMerlin|work_
the memory required isn't really related to the data size, it's related to how you tune the innodb/myisam buffers
2017-02-24 19:04:16
python476
and what about disk space ?
2017-02-24 19:04:18
MadMerlin|work_
however, the performance you get is directly related to how the buffers are tuned, and those are sensitive to the data size
2017-02-24 19:04:20
python476
(thanks btw)
2017-02-24 19:04:43
python476
if that may help, It's just an investigation of that dump, it's not for production
2017-02-24 19:05:24
python476
hmm and I forgot to change the config to allow large sql input too... and it crashed :)
2017-02-24 19:06:15
MadMerlin|work_
should be similar-ish to the dump size, although indexes will add to that depending on how many you have
2017-02-24 19:06:32
python476
ok
2017-02-24 19:06:49
python476
so far I believe it's a very linear aggregation of booktitles
2017-02-24 19:07:14
python476
I expect the structure to be as simple as possible, but maybe indexes are embedded
2017-02-24 19:07:57
python476
also, can I instruct mariadb to use a custom datadir to store that db (instead of the global default /var/lib/mysql) ?
2017-02-24 19:08:29
grknight
python476: yes, the datadir is a variable you can customize (before startup)
2017-02-24 19:08:40
python476
alright then
2017-02-24 19:10:27
python476
you're a very welcoming and reactive channel
2017-02-24 19:10:29
python476
refreshing
2017-02-24 19:19:21
suprovsky
grknight: i've found script and my base dir for mariadb is /mnt/ext/opt/mariadb/, but even if i define it in --basedir, i always get the same error
2017-02-24 19:20:15
grknight
suprovsky: that's where the bin and sbin files are?
2017-02-24 19:21:16
webly
ok, thanks for your help :D
2017-02-24 19:21:18
webly
bye
2017-02-24 19:29:16
suprovsky
grknight okay, I've run mysql_install_db, but I still get the same error when starting
2017-02-24 19:29:37
suprovsky
i didn't get any error during install
2017-02-24 19:30:09
grknight
suprovsky: so there is a host.frm in $datadir/mysql now?
2017-02-24 19:34:32
python476
aight, retrying with a 4GB ram VM, and a 8GB virtual disk, hopefully that will do
2017-02-24 19:47:55
python476
I need to read about storing subsystem, the loaded struct is above 1GB right now
2017-02-24 19:55:40
python476
yay, source finished
2017-02-24 20:31:49
rmbeer
hello
2017-02-24 20:32:36
rmbeer
where found examples for mariadb about of C/C++?
2017-02-24 20:34:53
rmbeer
if is C++ is best...
2017-02-24 20:35:11
rmbeer
or maybe not...
2017-02-24 20:45:01
rmbeer
then i must thinking that mariadb not have nothing documentation for C or C++?
2017-02-24 20:50:38
grknight
rmbeer: http://tangentsoft.net/mysql++/ is a C++ library over the top of the libmysqlclient library which mariadb and mysql provide
2017-02-24 20:50:43
grknight
libmysqlclient is the main C library
2017-02-24 20:53:33
rmbeer
grknight, thank
2017-02-24 21:01:37
python476
bbl
2017-02-24 21:01:39
python476
thanks again
2017-02-24 22:34:05
rmbeer
i read the 5.4 of user manual, i have like this: stock row("Hot Dogs", 100, 1.5,) and query.insert(row);
2017-02-24 22:34:38
rmbeer
how affect for the automatically columns like autocount and timestamp? how to omit this parameters?
2017-02-24 22:52:58
rmbeer
with include <mysql++.h> not work, i need replace by <mysql++/mysql++.h> and also take error with mysql_version.h and more...
2017-02-24 23:00:29
rmbeer
fix...
2017-02-24 23:14:08
rmbeer
error: expected constructor, destructor, or type conversion before â(â token sql_create_5(
2017-02-24 23:28:21
rmbeer
well, this bug not fix... thinking that mariadb not support this feature, also, why user manual not say about of 'insert row' from sql in C++?
2017-02-24 23:30:32
rmbeer
it's bad... have a hard bug where i can't patch the \' symbols...
2017-02-24 23:30:59
rmbeer
must find all characters and convert ' to \' ?
2017-02-24 23:31:10
rmbeer
Dx
2017-02-24 23:31:21
rmbeer
really is hard the use mariadb...