dbclkidk what the fuck is happening
dbclkmysql wont allow me to login without sudo
dbclkis this the expected behavior?
ayogiguys, i was trying to understand normalization, but i am not able to understand 4NF
ayogiis there a way i can understand better
nohitallhi i have an issue with root password, I tried to reset it but now I get: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
nohitallbut I can't because it complains my pw isent fitting policy requirements, if I try to turn off policy it tells me again I have to reset using ALTER user again
nohitallits a frikkin loop
Reception123 Hello. We're having memory issues on our database on our site and it's causing issues
flyingany help on galera mysql?
Reception123If anyone could please answer http://dba.stackexchange.com/questions/159096/how-can-i-find-out-why-mariadb-runs-out-of-memory
flyingI have a node not in cluster
flyinghow can I check what happenede?
Reception123That would be a great help for our site (non profit that hosts Mediawiki wikis for free)
thumbsflying: #maria might be a better place to ask
thumbsyes, MariaDB
Reception123its for MariaDB
Reception123I asked there too but no one is answering anything
Reception123that's why I thought maybe someone would know something here
thumbsReception123: what else do you run on that box?
Reception123thumbs: as Southparkfan mentioned our database runs Mediawiki databases (about 1800 now), Phabricator and Piwik
thumbsReception123: what other processes?
thumbsisn't mediawiki php based?
Reception123It is
Reception123It's weird why MariaDB just runs out of memory
thumbsReception123: so it's not really dedicated to MariaDB, isn't it?
Reception123Not really, but it still uses MariaDB
thumbsReception123: php can trivially use 5+ GB of memory and leave nothing for MariaDB
Reception123Also, I'm a sysadmin at the site but I only deal with the MediaWiki side so I don't know too much about our databases nor do I have access
Reception123thumbs: the main question that my colleague, Southparkfan needs to know is "What could be a good way to find the offending trigger or avoid high memory usage?"
thumbsReception123: also, limiting mysqld to 1GB won't work as such, because of all the other per-connection buffers
thumbsReception123: per connection buffers.
Reception123thumbs: thanks for your help. I will pass that along to Southparkfan
thumbsReception123: he should monitor how much memory php is consuming.
Reception123Thanks again for your help
thumbsReception123: you have good things in that config file - you disabled the query cache.
thumbsReception123: I've also seen cases where disabling the performance schema helped reduce memory consumption.
Reception123Ok. I'll tell Southparkfan about that too.
Reception123We're tracking the issue here: https://phabricator.miraheze.org/T1239#22634
thumbsReception123: have you read http://www.tocker.ca/2014/03/10/configuring-mysql-to-use-minimal-memory.html ?
thumbsReception123: it has good pointers on variables that you might have overlooked.
Reception123I surely haven't, not sure if Southparkfan did
thumbswhy didn't he join #mysql or #maria first?
thumbshe was online 10 hours ago or so.
Reception123He probably wasn't aware of the channels or didn't have the time
Reception123When he comes online I will also mention these channels to him so he knows to check them from now on
thumbswell, I can tell from the config file that he has a clue - most of the mistakes new users make are not present.
Reception123Well there's one more person that helps around with databases
Reception123but yes Southparkfan does know stuff about databases
thumbswith the exception of innodb-log-file-size - 64M is quite small, given the amount of transaction the site must have
thumbsa smaller log file is *not* faster!
Reception123well yes.. with 1800+ databases I believe we do
Reception123but as I said I have no power/access to our databases as I only deal with MediaWiki
thumbswith a decent load, I usually start anywhere between 300 and 500MB for that variable.
Reception123thumbs: thank you very much again. I wasn't expecting such good and quick answers really
Reception123It would have been easier if Southparkfan were here so you could actually discuss the options
thumbsReception123: I just skimmed the surface, really.
salleReception123: Don't forget it is 1st of Jan :)
salleReception123: Usually there are lot more active people both in #mysql and #maria
thumbsinstead all you have is salle and myself.
Naktibaldathat's 50% of a usual crowd
sallethumbs: Not even all of that :)
Naktibaldahi, happy new year
salleHappy New Year
Reception123another question from me.. not sure how mysql related it is but people in other channels said it is
Reception123 Hello. I have a logging issue with extension that creates wikis https://phabricator.miraheze.org/T1239
Reception123Sorry the link is https://phabricator.miraheze.org/T1104
thumbsReception123: doesn't sound like a MariaDB/MySQL issue, no.
Reception123thumbs: Ok, I thought so too
Reception123thumbs: Southparkfan replied .https://phabricator.miraheze.org/T1239
thumbsReception123: heh. It's not that large. Domas would tell you to use 1GB :)
Reception123:) Any other ideas?
thumbsReception123: tweak the per-connection/thread buffers.
thumbsnow, identifying a true memory leak might be more complicated.
thumbsReception123: as for my suggestion for the log file size, I probably have a bigger workload than most. 64M would be far too small and would fill up much faster than other less busy servers.
Reception123SPF|Cloud (Southparkfan) has connected to this channel so you can talk directly with him now.
SPF|Cloudthumbs: hi, colleague of Reception123 here. Thanks for your suggestions
SPF|CloudI/O is not very fast here, does that matter much for the log file size?
thumbsSPF|Cloud: with a slow I/O subsystem, you want to minimize the number of checkpoints.
SPF|CloudIndeed, you're right
thumbsSPF|Cloud: now, on the other hand, a larger log would be more cumbersome when you have to do recovery, of course.
SPF|CloudRecovery takes a few hours
thumbsSPF|Cloud: this is all sidestepping the main problem, which is the growing RAM usage.
thumbsso addressing the current values of those per-connection buffers is important. Only then should you make adjustments.
thumbsin short: *never* make blind adjustments. *always* measure first, then tweak, then measure/benchmark after.
SPF|CloudIs the high amount of (InnoDB) tables a factor?
thumbsSPF|Cloud: my note about the small log was because many folks minimize all values without thinking or hoping that it'll help performance, or lower memory consumption.
thumbsSPF|Cloud: not especially. The buffer pool can only hold so much (data and indexes) at a given time, and you have LRU
salleSPF|Cloud: Yes it is
thumbssalle: why?
sallethumbs: If there are several thousands of tables the data dictionary can use noticeable amount of RAM
salleSPF|Cloud: What do you get for Dictionary memory allocated in SHOW ENGINE INNODB STATUS ?
thumbslet me check my innodb status then
SPF|CloudI'll run that command asap, one minute
thumbsI've got about... 1500 tables on this particular server.
thumbsit seems to hover between 30 and 40% of the buffer pool size. You're right, it can be significant.
thumbsbut in their case it would be around 400MB, plus the 1GB pool. That would leave 1.5GB for per-connection buffers, which seems a bit high.
sallethumbs: Depends on how many are the many tables they have :)
thumbs(the OOM clocked it at 3GB)
thumbsall right, let's wait for SPF|Cloud
SPF|Cloudsalle: Dictionary memory allocated 6516934
thumbsSPF|Cloud: and the buffer pool is 10485760 ?
thumbssorry, make that 1G.
SPF|CloudIs 6516934 really big?
salleSPF|Cloud: Depends on how big your buffer pool is
SPF|CloudIt is 1G
thumbsthen no, it's small enough not to matter.
thumbslook in the engine status for the current buffer pool size, in bytes.
SPF|CloudBuffer pool size, bytes 1073725440
thumbs6.2M versus 1G is really small. It would interesting if increasing the number of opened tables affects this.
SPF|CloudTable cache is set to 350, which in turn is probably increased to 400? Because that is the minimun
salleSPF|Cloud: Is that buffer pool size reported by SHOW ENGINE INNODB STATUS or it is the value of @@innodb_buffer_pool_size?
SPF|CloudFirst one
salleSPF|Cloud: The you have huuuuge buffer pool
salleSPF|Cloud: SELECT @@innodb_buffer_pool_size; ?
salleSPF|Cloud: The value reported by engine status is in pages not bytes
SPF|CloudBut I chose the one with ", bytes"
SPF|CloudThe other one is 65535, which is the open files limit
thumbsforget about the open files limit for a moment
thumbsI think the workload on your server is far less than I first estimated it.
SPF|CloudProbably. It is not write-intensive
thumbsSPF|Cloud: can you show the complete BUFFER POOL AND MEMORY section?
SPF|CloudIf you want hastebin or pastie.org feel free to ask me
thumbsSPF|Cloud: that's fine.
thumbsSPF|Cloud: really, that's not a busy server. I would leave those variables alone for now, and focus on the per-connection buffers (many were unset and left to default values)
thumbsSPF|Cloud: also, I concur with salle as far as the buffer pool size is concerned: it seems far too high for your actual needs.
SPF|CloudI used to read 'buffer pool should be >50% of total memory' a lot of times :) obviously every site is different
thumbsSPF|Cloud: of course every site is different
thumbsSPF|Cloud: the elephant in the room is still where the other 2GB went.
thumbsI overallocate the pool in many servers, too. None show signs of OOM, granted.
SPF|CloudThe buffer pool hit rate is 99,9% (which is actually higher than I thought), so lowering the buffer pool to 512M or 768M won't hurt much (I need to test that, though..)
thumbsSPF|Cloud: you're likely to still OOM at 3G, but now you'll need to chase the 2.25 or 2.5G of consumption.
SPF|CloudYes, that's a tough part here.
thumbsSPF|Cloud: read the http://www.tocker.ca/2014/03/10/configuring-mysql-to-use-minimal-memory.html page and find out the *current* values of all those variables.
thumbssalle: what percentage of the buffer pool is your dictionary on your servers?
Onepamopaguys, can someone tell me why "SSL connection error: SSL is required but the server doesn't support it", my.cnf [mysqld] has the appropriate ssl-cert ssl-key ssl-ca, mysql server is restarted but: have_ssl | DISABLED
Onepamoparunning freebsd 10.3, mysql server 5.7.15-log
thumbsOnepamopa: check the error log - it'll show you why ssl support is absent.
OnepamopaFailed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed
SPF|Cloudhere you are thumbs https://www.irccloud.com/pastebin/aURcADJh
SPF|CloudIt takes a while before I have the others, but you might already see something that shohld be adjusted
SPF|Cloud(used select @@<variable_name>; here)
NaktibaldaSPF|Cloud: how about using SHOW VARIABLES ?
Onepamopacan someone tell me why do I get SSL_CTX_set_default_verify_paths failed ?
Onepamopacerts & keys are in /usr/local/etc/mysql-ssl/mysql-ca.pem mysql-server.pem etc
thumbsOnepamopa: either permissions, or selinux/apparmor interfering
Onepamopathumbs, freebsd doesnt have such things
Onepamopafolder + files are owned by mysql
Onepamopachmod 777 folder + files = same result ..
thumbsnever use 777.
Onepamopait was just for a test
thumbsOnepamopa: not even for a test.
thumbsuse namei -mo /path/to/files or http://people.apache.org/~igalic/hacks/parsepath if you don't have namei
Onepamopabash: namei: command not found
thumbsOnepamopa: yes, see my comment.
thumbsSPF|Cloud: they could be lowered a bit. I would refer to the docs for each variable first.
Onepamopawell folder is already 755 and files 644
thumbs!man innodb_ft_cache_size
ubiquity_botSee http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_ft_cache_size
thumbsOnepamopa: show the full path from /
Onepamopa-> /usr/local/etc/mysql-ssl
thumbsOnepamopa: use parsepath to show the permissions from each segment.
SPF|CloudThank you for the suggestion thumbs. ^ there you have all vars
Onepamopadrwxr-xr-x 4 root wheel 512B Jan 1 15:46 mysql
Onepamopadrwxr-xr-x 2 mysql mysql 1.0K Jan 1 17:10 mysql-ssl
Onepamopaeverything else is root:wheel
thumbsOnepamopa: you still have not shown the output from /
Onepamopa% /usr/local/etc/mysql-ssl
Onepamopad 0755 root:wheel /
Onepamopad 0755 root:wheel /usr
Onepamopad 0755 root:wheel /usr/local
Onepamopad 0755 root:wheel /usr/local/etc
Onepamopad 0755 mysql:mysql /usr/local/etc/mysql-ssl
OnepamopaI'll try to move the ssl certs/keys @ /var/db/mysql (the homedir/basedir)
thumbsis there a passphrase set?
thumbs!man innodb_ft_total_cache_size
ubiquity_botSee http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_ft_total_cache_size
thumbsSPF|Cloud: are you using FTS at all?
Onepamopathumbs, dude sorry, ca file was empty
thumbsOnepamopa: hah
Onepamopanow no error @ restart
SPF|CloudYes, for the search function of the site
thumbsSPF|Cloud: why did you pick InnoDB FTS over more preformant options?
SPF|CloudWell, the software that uses it actually uses MyISAM for it, but the tables had to be repaired many, many times
thumbsso you're still using MyISAM for this?
SPF|CloudWith InnoDB we didn't have that problem, so we chose to use InnoDB for.it
thumbsso you converted those tables to InnoDB?
thumbswell, there's 650MB allocated to it.
thumbsalso, myisam_sort_buffer_size 128M ? Really?
thumbs!man myisam_sort_buffer_size
ubiquity_botSee http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_myisam_sort_buffer_size
SPF|Cloud650MB is too much. I have no objections to lower that to 200MB
SPF|Cloud(elasticsearch is better for that...)
thumbsyes, far better, and will use less RAM
SPF|CloudEh 128M? Wow
thumbsSPF|Cloud: if you converted all tables to InnoDB (save the mysql schema), then key_buffer can be set to the minimum too
thumbs!man thread_stack
ubiquity_botSee http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_thread_stack
thumbsI think your thread stack might be too large too.
thumbsas with any changes, please read the aformentioned doc page to make sure the new value is sane.
thumbsI was playing with the thread stack size on a HTTP server recently with great results. A lower value nearly tripled the number of concurrent users I could support.
thumbs!man thread_stack
ubiquity_botSee http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_thread_stack
Onepamopathumbs, http://pastebin.com/896EVJ9k ?
Onepamopacustom user certificate and key generated, signed with server's ca
thumbsOnepamopa: sorry, pastebin.com seems to time out for me. I'll just let someone else look at it.
Onepamopauser is with REQUIRE SSL
Onepamopathumbs, preferred paste site?
thumbsOnepamopa: any other than pastebin.com
Onepamopaah I noticed @ topic
Onepamopawhat the ...
SPF|Cloudthumbs: do you really mean 650M?
Onepamopahow does that fscking hastebin works ..
SPF|CloudOr 640? (not a significant difference, but I want to be sure I look at the right variable)
thumbsOnepamopa: just use another if you can't find the save button
thumbsSPF|Cloud: I added the 8M to it, and rounded it.
Onepamopawell I can click save, but no link is generated ...
SPF|CloudErr why the 8M?
thumbsjust use another paste site.
thumbs!man innodb_ft_cache_size
ubiquity_botSee http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_ft_cache_size
thumbs!man innodb_ft_total_cache_size
ubiquity_botSee http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_ft_total_cache_size
SPF|CloudAha, sorry.. :)
Onepamopamysql -h remote-IP -P 3100 -u theusername -pthepassword --ssl-mode=REQUIRED --ssl-cert=/home/user/user-cert.pem --ssl-key=/home/user/user-key.pem ---> results in: ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)
thumbsSPF|Cloud: those explain the purpose.
SPF|CloudThank you.
thumbsSPF|Cloud: you can use !man here with any of those variables.
thumbsOnepamopa: SSL with mysqld is extremly finicky. I would look into a ssh tunnel instead.
Onepamopassh tunnels won't be possible..
domasthumbs: ssl with mysqld is awesome
thumbsdomas: happy new year!
thumbsdomas: then answer his question!
domasOnepamopa: don't listen to them, SSL with ssl works great
domaswith mysql
domashappy new year
domashow was the leap second?
domasI slept like a baby
Onepamopadomas, so ssl should be added in [mysql] not [mysqld] ?
domasOnepamopa: [mysqld] for server settings, [mysq] for client settings
Onepamopadomas, well Im connecting from a remote server, there is only mysql57-client installed, no my.cnf present anywhere
domasOnepamopa: then [client] or [mysql]
domasI'd put into [client]
domasthen it applies to mysqladmin/mysqldump/et al
Onepamopaso its not needed in [mysql/d] ?
domasOnepamopa: there's one part that is important on the client side, it is ssl-ca
thumbsdomas: actually, what is the percentage of Dictionary memory allocated / Buffer pool size, bytes on your server?
domasthumbs: hmmm, need to get onto vpn for that
domasI'd say less than 1% is dictionary
domasmaybe 0.1%
thumbsdomas: I'm just curious. Mine seems high a bit.
domasdepends on a system, I suppose
domasyou can do the math
domas10k-20k tables per instance
thumbsoh, I misread initially. I'm actually at 2.9%
domasand... say.... 50G buffer pool
domasless tables in other cases
domasmeh, worth hopping onto vpn anyway
Onepamopadomas, strangely, even when I have ssl-cert/ssl-key/ssl-ca @ my.cnf, it "auto-generates" such in /var/db/mysql
Onepamopawhy the hell does it do that
domasit is stupid
Onepamopain any case, Im still getting the same error with the client certs added @ server's my.cnf in [client]
Onepamopano certs/keys in [mysql/d]
domasthumbs: random machine:
domasTotal memory allocated 50844008448; in additional pool allocated 0
domasDictionary memory allocated 52266954
domas*random instance
thumbsmuch higher, then.
domaswhat is?
thumbsthe percentage.
domasmuch higher in your case, yes
domasOnepamopa: stuff that auto-generates is stupid
domasOnepamopa: SSL's idea is to have trust, not just crypto
domasI guess you do get crypto by auto-generating stuff ;-)
OnepamopaI need the crypto
Onepamopaso there's no need to add ssl cert/key on the remote side?
Onepamopathen how do I enforce user to connect via SSL ?
domasthumbs: anyway, did not find more than 1% anywhere :)
thumbsdomas: ok, thanks for checking.
domasOnepamopa: --ssl-ca is most important for the client
Onepamopadomas, I added server's ca to that server, to the command line
Onepamopasame error
thumbsdomas: salle alluded that the user asking here could have a huge dictionary
thumbswhich I never heard about, so I wanted to confirm
domasthumbs: some people do
domashuge would be 10% ;-)
domasfor some people mere existence of a table is "data"
domasOnepamopa: does the user have 'require x509' ?
domaswhy are you passing user cert
Onepamopadomas, it has REQUIRE SSL
domasyou don't need user's cert then
Onepamopaand IM providing the password via the commandline
domas--ssl-ca should be enough
domasI might be spoiled though, we did some work to make ssl much more usable (like, better errors and what not)
thumbsis that why you say it's awesome?
domasI don't know how much of our work went upstream
Onepamopadomas, so I need require x509 to be able to use the user's cert/key+ca ?
Onepamopacause every user will have its own certs/keys
domasOnepamopa: 'require x509' asks for client cert
Onepamopathanks, will try now
domas'require ssl' just needs crypto
domaswell, first get it working without user certs
OnepamopaI managed to connect
domasalso, you may want to link against boringssl if you're going to have more than 10k ssl connects/sec on your instance :-)
domasOnepamopa: 'status' command in mysql cli will tell you if you're ssl connected
domasthumbs: https://www.facebook.com/MySQLatFacebook/posts/10153074619236696?match=c3Ns ;-)
OnepamopaSSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256
domasOnepamopa: read above link
thumbsdomas: ooo
domascrap, that post was mid-2015
domaswhat did I do in 2016? :(
thumbsdomas: you slacked off, naturally.
domasseems like
Onepamopadomas, thanks, that'll certainly help
domasOnepamopa: things like ECDHE are nice
domasperformance stuff may not be as necessary
domasespecially if you don't run a large fleet
domasthumbs: I'm afraid of my 2016H2 review!
thumbsdomas: you still have reviews? Sucks to be you!
domaswe all do!
thumbsdomas: not I
Onepamopadomas, now with require x509 on the user, --ssl-cert & --ssl-key paths added @ command line - SSL connection error: error:00000001:lib(0):func(0):reason(1)
domasOnepamopa: well, client's cert has to be trusted by the server
domasOnepamopa: is it issued by the same CA?
domasis client CA known by the server?
Onepamopaits signed by the same ca
Onepamopait's server CA
domasnow... is it... ;-)
domasemily@percona sent me an email, "can you afford NOT to upgrade your database?"
domasshould I send her an answer?
domascc peterz
domascc laurynas
Onepamopaopenssl req -newkey rsa:2048 -days 3600 -nodes -keyout user-key.pem -out user-req.pem
Onepamopaopenssl rsa -in user-key.pem -out user-key.pem
Onepamopaopenssl x509 -req -in user-req.pem -days 3600 -CA mysql-ca.pem -CAkey mysql-ca-key.pem -set_serial 01 -out user-cert.pem
thumbspoor emily
Onepamopamysql-ca.pem is @ [mysqld]
thumbsshe'll have to endure domas' sarcastic response.
OnepamopaI ain't she
thumbsI was referring to emily@percona
OnepamopaAh, sorry
domasOnepamopa: well, are you sure mysql-ca.pem is properly in [mysqld] ;-)
domasbecause that is the only reason cert would not work
domas(assuming you can verify it manually, etc)
Onepamopaerror 18 at 0 depth lookup:self signed certificate
Onepamopafor both the server and client certs
domas"self signed certificate"
domassays there's an errorĄ
Onepamopaso what if I use self-signed certificates?
domaswell, it is not a self-signed if you have a CA
thumbswith a self-serve certificate, it is both the certificate and the CA.
Onepamopaso I have to add the CA to the client's certificate file
domasin your verification you provide a "CA root" which may be your own CA
domastechnically, user-cert should be signed
domasbased on your command
Onepamopayes, it is ..
domascan you show 'openssl x509 -text' output on it?
Onepamopayes, no errors
domasthere're no errors there, because you don't verify anything much except for x509
Onepamopathats what you asked me to do ;)
domasI asked you to show the output!
domasanyway, figure out if server can verify certificates properly
domasand then pass a certificate
domasonce you're ready for that, you can require specific subjects
SPF|Clouddomas is alive? :o
thumbsSPF|Cloud: sometimes, when he's not trolling the facebook employees
Woetdomas: whatever you posted on facebook is hidden now
Woetits only visible to you/friends
thumbsit's a good thing I'm his friend
domasWoet: what?!
Woetdomas: try incognito, you'll see
Woetif you click it it wont show up
domasWoet: the post itself
Woetoh, sorry
Woeti thought you made a comment
Onepamopadomas, I put ca.key instead of server key in [mysqld], now Im getting access denied, even tho the password is correct
domasWoet: lots of people write spammy/shitty comments there :)
domasSPF|Cloud: whatsup!
domasOnepamopa: what do you mean by 'instead'
Onepamopawell I thought I was making progress, but no ;)
Onepamopathe server still doesn't validate client certs
OnepamopaI re-generated everything, still the same error ..
domasyou may be doing something wrong, then!
Onepamopawell, from the same server if I try to connect, I get Access denied for user 'platformams9'@'localhost' (using password: YES)
Onepamopathe user is added with host %
thumbslocalhost? I thought this was a remote connection.
Onepamopafrom the remote server I get SSL connection error: error:00000001:lib(0):func(0):reason(1)
Onepamopaso Im testing on the same server now, to see if it can verify the client's certificate
Onepamopaoh, I forgot to add --ssl-mode=REQUIRED
Onepamopaso, same error @ the same server
domaswhen you know that fixing for it yourself would take few seconds
domasand yet this chat is ongoing forever!
domasACTION sips some coffee
Onepamopadomas, something to do with the CRL ?
Onepamopaand you won't tell me where the problem is ;)
domaswell, your grants have to be correct, your settings have to be correct, and then things will work
Onepamopadomas, well the grants are how you said - require x509
domaspermission denied would be you connecting without a cert or with bad password or hitting wrong grant record
domasssl error would be if ssl is not negotiated
Onepamopaso something either @ [client] or [mysqld] is wrong
Onepamopa@ client there are only the client ssl cert/key for the server itself
domasjust pass that shit in command line
domasat client you need SSL CA, client cert, client key
domasat server you need SSL CA, server cert, server key
Onepamopawell, I added the ca @ [client], now I can't connect locally at all - error:00000001:lib(0):func(0):reason(1)
SPF|Cloud!man myisam_sort_buffer_size
ubiquity_botSee http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_myisam_sort_buffer_size
Onepamopadomas, I think I figured it out, has to do with the common name of the certificates...
Onepamopadoesn't it ;)
domasSN is important for clients only if you require subject, for servers depends on verification type
SPF|Cloudthumbs: well, I just tried the config changes
thumbsSPF|Cloud: ok
SPF|Cloudlower innodb buffer pool to 768MB, lower ft cache size to 150MB, lower myisam sort cache size to 8MB
Onepamopadomas, I was just doing the same on all, openssl default values
SPF|Cloudbut actually it just crashed again
thumbsSPF|Cloud: we talking about the thread stack too
SPF|Cloudnow it's reading tablespace information from the .ibd files, which will take at least 2 hours
SPF|Cloudyou know, it frustrates me really much
SPF|Cloudwe're a non-profit project, and with our funds we can't just buy more RAM for our VPSs or hire a DBA...
SPF|Cloudfinding out what's causing the OOM is also hard. well let's wait 2 hours
thumbsreading the information from the .ibd files shouldn't take 2 hours
SPF|Cloudit has in the past
SPF|Cloudwe had another oom earlier today where the recovery took 1 hour, I hope it won't take longer now.
thumbsSPF|Cloud: what is this, tape drive storage?
SPF|CloudSSD-cached storage
SPF|Cloudbut it's not fast.
SPF|Cloudthumbs: there is a way to decrease recovery time, right?
thumbsSPF|Cloud: you are recovering from a crash, but you can speed that up a bit
SPF|Cloudcan you tell me what I can do?
thumbsSPF|Cloud: one sec
dbclkfolks...having an issue with mysql
dbclkrunning this query but, it doesn't seem to work select min(created_at) as min_created_at, max(created_at) as max_created_at from `mos` order by `id` desc limit 50
DjKadgroup by id
dbclkit seems to be getting the Min in the entire DB as oppose from 50 records
dbclkwhy would i group by id when the ID is unique?
dbclkany ideas? im stumped to what's happening here
dbclkso..i see what's happening here
DjKadsorry i've read in a wrong way
dbclkI actually got this query from someone else
dbclkessentially they're saying find the record with THE record with the min and max created_at date
DjKadwell that query gives ya just due values
dbclkthis is rather wrong
DjKad2 values
dbclkwell DjKad it always gives me the same minmum value
dbclkdespite me changing the limit to 5
pudymodymaybe instead of from mos, from (SELECT created_at FROM .....)
DjKadsuppose you have a table with only 20 rows
pudymodylike, select the max and min from this set
DjKadand suppose that table have only the field created_at
DjKadif you send a SELECT min(created_at) from tablename it give you just a value that rappresent the minimun value between all that 20 rows
DjKadfor example if created_at values are 1,2,3,4,5,6,7,8,9,10 the query will return you 1
dbclkok but, what if i said select created_at from blah order by created_at DESC limit 6
dbclkwould this return 4?
dbclksorry I meant 5
dbclk@ DjKad
dbclkbecause, what i'm seeing here for that query is 1
dbclkthat's not the case on my queston
DjKadwait wait
DjKadif you select min(created_at) it will return always 1
DjKadbecause the minimun between 1,2,3,4,5 is 1
DjKadit's better that you tell me what you want to obtain and i can help you
dbclkhere's what i'm seeing
dbclkbecause i said order by created_at DESC limit 6 ..so it should've been 10,9,8,7,6,5
dbclkand in this case..it should be 5
DjKadas you said, you can remove the group by
DjKadbut i still don't understand which information you want obtain
DjKadfrom that table
dbclkDjKad: I got this query from someone...I'm working redoing their stuff
dbclkbut, it doesn't make sense to me
dbclkcuz..i think what they're looking for is the min and max created_date in the last 50 records that was inserted
dbclkbut even from the gist i pasted
dbclkwhere the groupby was use
dbclkand i also used limit 5
DjKadorder by id desc because they probably use an autoincrement id
dbclkthe bottom record had '2017-01-02 01:25:31'
dbclkbut, when i remove the groupby
dbclkit shows 2010-01-01 00:00:00
dbclkwhich wasn't in the first query with the groupby
dbclkthis doesn't make sense to me
dbclkbecause, i have over 10k records in the table
dbclkand it seems to be selecting the minimum record in the ENTIRE table rather than selecting the minimum record in the limit of 10
dbclkwell could you explain why mysql is doing this?
dbclkspecificaly this -> https://gist.github.com/dbclkclk/c86eb00c553060326991f84547af57aa
dbclkI dont understand what's happening here
DjKadhave you see the access plan of that query?
dbclki updated the gist
dbclksee here
dbclkthis doesn't make sense
dbclkno..I have
dbclkhow could i see this?
DjKadlimit is applied to result row
dbclkso what if I wanted to select the min and max from a resultset
dbclkrather than the entire DB
DjKadyou should try to select limit 50 and then apply the minimun just to those rows
dbclk@ DjKad
DjKadselect min(a.created) from table as a join (select id from table order by id desc limit 5) as b on (b.id=a.id)
dbclkI understand what you mean DjKad but, my understanding was that in that query....it would have executed the GROUPBY first, then the LIMIT 50 and lasty: MIN and MAX
dbclkbut, what you're saying is that's not the case
dbclkthe query actually finds the MIN and MAX first....then it applies the groupby ...then the limit
dbclkis that correct?
dbclk@ DjKad I'm not trying to find a solution..I'm just trying to understand what's happening here
DjKadhave you seen the execution plan?
dbclkhow could I get this?
dbclkis this a command i could run from the terminal?
DjKaddo you have mysql workbench?
dbclki do
passagedbclk: select min(dt.created_at) from (select created_at from mos order by id limit 10) as dt; <-- sort of thing
dbclkI know passage ...the problem is that there's a misunderstanding regarding execution precedence with queries
passagedbclk: that subquery in the FROM clause is called a derived table. you give it an alias, and address the resultset using the alias
dbclkpassage: I know...as I said..i'm not looking for a solution..I'm just trying to understand from a high level ..how execution precedence takes place
dbclkthis is the problem here
dbclkand it's my lack of understanding too
passagedbclk: you're selecting the min value of that column from the table, which is one row without a group by. then you are uselessly ordering that one row, and then uselessly limiting that one row resultset to 10 rows
passagedbclk: group by, order by, limit are all filters on the resultset. the resultset in that case is a single tow
passagedbclk: in other words, in your example, the order by and limit are irrelevant.
DjKadand so the answer is yes..limit is applied to resultset, while aggregate functions are performed before
DjKadpassage: with my version of mariadb limiting inside a derived table is not available
DjKadError Code: 1235. This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
passagedbclk: the order is spelled out in the manual entry for SELECT http://dev.mysql.com/doc/refman/5.7/en/select.html
passagedbclk: you also need to be careful using aggregate functions without GROUP BY. it will work for a single column in select clause, but will cause troubles otherwise
passagemay cause troubles*
dsc_can someone explain to me why this works: https://www.notsosecure.com/sql-column-truncation-vulnerabilities/
dsc_'admin a' != 'admin' last time I checked
dsc_or 'admin ' for that matter
preactionthey don't have a primary key on that table...
preactionthat... is like 4 terrible practices they're doing there
dsc_i'm sure there is, i'm just wondering how it works technically
dsc_what does primary key have to do with this?
preactionif there was a primary key, the value would be rejected as "not unique"
preactionany unique constraint really
preactionbut since there is no primary key, you can have any number of users named the same
preactionso the truncation bit doesn't really matter here, though yes that is unfortunately a thing
preactionif they're doing the "does the user already exist" check in their application code, then frankly they deserve this problem... :p
preactionalso, technically, those are two different users: "admin" and "admin "
preactionoh. right. because mysql...
dsc_yeah im just wondering, does it trim?
dsc_if so, 'huh?
dsc_so I cant save a bunch of trailing spaces in values that I want to save in the db?
preactioni mean, that'd you have to look up. i forget the rules around that
dsc_your first comment about primary key confused me since that's usually used for int. 'id' and user columns have the NOT NULL constraint
dsc_that still doesnt explain how the last select yields 2 usernames
dsc_yields 2 rows*
preactionprimary key can be used on basically any column type
preactionand i don't see any not null constraints in this page
preactionthis page describing the problem is describing it very poorly, is the point i'm trying to make
dsc_`insert into users values('admin a','test')` has some spaces that dont seem to make it into the table
dsc_am I missing something obvious?
dsc_that 'a' dissapears, that I understand
preactionhttp://stackoverflow.com/questions/3668996/trailing-whitespace-in-varchar-needs-to-be-considered-in-comparison <- it's saved, but = doesn't care
preactionthe docs https://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html
DjKadinsert into apply a kind of trim when you insert values with spaces in a row where UNIQUE is set :O
dsc_it does trim.
preactionthere is no unique set though
dsc_I know I know, my question is about the trim. mysql 4 seems to do that
preactioni just said
DjKadfor example if i enter 'marcello' and then i try to enter 'marcello a' it alert me that 'marcello ' is a duplicate
preactionright. that's the solution to this
preactionand the mysql workbench is saying that they're truncated as well. so both = ignores trailing whitespace, and trailing whitespace is truncated
dsc_honestly did not know that
DjKadwell..truncated because i've set varchar(10)
DjKadbut 'marcello' should be != 'marcello '
DjKadunique constraint apply a check leaving the spaces
dsc_ofc any decent programmer would implement both primary keys and unique constraints
dsc_alright, thanks.
enlethHi there. Is there an 'official' way of running a foreign key consistency check on an InnoDB database? Say, after doing a multi-table LOAD DATA with SET FOREIGN_KEY_CHECKS=0 and re-enabling the checks, I'd like to make sure that the data is in fact consistent. I found a stackoverflow question from 2010, with the most recent answers from 2012, providing a stored procedure that does the check, but I wanted to
enlethask if there's something available OOTB nowadays.
enleth(The use case of all that being a LOAD DATA of many tables with complex FK dependencies where the personnel doing the load can't be expected to reorder the statements properly for whatever reason)
enleth(I *know*, this *is* a little bit of an X-Y problem. I really do.)
GettyOh humour mandatory.... and i was already thinking about how i describe my situation without getting ripped into pieces, puh, now i feel safer to just ask ;)
Gettyi have a high-write mysql server and given that i normally dont use mysql i didnt actually cared about the optimization that much
Gettyit was also a big run of other unrelated problems that hunted me anyway all the time. But now we got one "awkward" problem left, that leads to specific fastcgi processes getting tons of "Mysql server gone away" errors, at first i thought its the app, but since i now realized that restarting app doesnt help often but restarting mysql always helps, i think its like the server that just bails
Gettygiven that its superhard to replicate that problem (but if it comes everything explodes), can it be that a very bad configured mysql server in combination with high load leads to this situatoin (without actually throwing any error or anything that gives deeper inside)?
Gettyits like, when i read the "mysql server gone away" documentation page there is no indicator that "bad optimization" is a reason for this to happen