ignasrhi, is there a relay log equivalent to the command "show binary logs"?
ackpacketI have two tables: popsical_stand(stand_id, total_revenue) and administration(stand_id, owner_id). Stands and owners are many to many. I'm trying to find out how much money each owner has made selling popsicals (double counting is ok, two owners owning the same stand that sold $100 should both count $100 toward their total). I'm using this query and want to know if it could be more efficient:
ackpacketSELECT SUM(total_revenue) FROM popsical_stands AS A, administration AS B WHERE A.stand_id = B.stand_id GROUP BY owner_id
ackpacketMay crosspost in ##programming
dragonheartackpacket: use a JOIN.
dragonheartSELECT SUM(total_revenue) FROM popsical_stands AS A JOIN administration AS B ON A.stand_id = B.stand_id GROUP BY owner_id
ackpacketdragonheart: Why's that?
dragonheart, is a cross product join. Recommend never using it
ackpacketdragonheart: Is SELECT ... foo, bar not a join?
dragonheartits an outer join, you need an inner join
dragonheartyou probably want SELECT owner_id,SUM(total_revenue) FROM ...
ackpacketdragonheart: correct on that count
ackpacketdragonheart: Considering there will only be stands with existing owners, and only be owners listed who have stands, how does an inner join produce a different result?
jkavalik"from a, b" is not an outer join
dragonheartremove the group by and show the A.*,B.* results and look
ackpacketOk, will experiment further, ty for the advice. I'm off.
swenssonHey guys "mysql>CREATE USER magentouser@localhost IDENTIFIED BY 'user_password';" will user_password be by password?
dragonheart!t swensson tias
ubiquity_botswensson: Try it and see, its quicker to type it on your system and try it than wait for one of us to tell you its ok
swenssonWell I don't know how to change the password so then I would have to look for that aswell, easier comming here asking some pros ;D
thomedy_hello
thomedy_im trying to run a mysql dump and
thomedy_im getting Got error: 1146: Table 'thomedy_newFile.field_revision_balance' doesn't exist when using LOCK TABLES
thomedy_what do i do with that
thomedy_im exhausted and need a bck up please help
thomedy_im running mysqldump -u [userName] -p newFile2 > newFile2.sql
thomedy_but its dsending me this
leeyaahi
leeyaado you guys know if it is possible to downgrade mysql to 5.5 on ubuntu 16.04 ?
leeyaai prefer without installing from source
lordjancsohi, i'm developing a php website and i have to connect to a remote mysql db via vpn. i'm using a docker container with nginx. all my websites for development are working, but this one with vpn database giving 504 gateway timeout if i call it via browser. if i call with "wget -O - http://mysite.local" it gives back the content. what would be the problem?
bonhoefferis there a way to test a password from within mysql?
bonhoefferwhen i try to login with the password i set, i get: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES)
bonhoefferbut i'm pretty sure i have the password set correctly
uid1!t bonhoeffer about root as root
ubiquity_botbonhoeffer: If "mysql -u root" only works as the system root user, you might be using the auth_socket plugin. SELECT user, host, plugin FROM mysql.user; and see https://dev.mysql.com/doc/mysql-security-excerpt/en/socket-authentication-plugin.html
bonhoefferuid1: got it -- i need to try to specify the db
leeyaahi
leeyaaif i want to setup replication for single myisam db is it ok if i use rsync for this one db (from lvm snapshot) to setup the replication?
bonhoefferhm. . . trying to give a user permissions and i'm blocked by: ERROR 1133 (42000): Can't find any matching row in the user table
leeyaai already have users table configured
leeyaaand other system stuff
leeyaabonhoeffer: try adding the user first.
bonhoefferi can confirm the user is in the mysql.user table
uid1bonhoeffer: In the user table, 127.0.0.1 ≠ localhost
archivistbonhoeffer, user@host
bonhoefferif i select Host, User from mysql.user, i get: | localhost | not_linsey |
bonhoefferGRANT ALL PRIVILEGES ON foo.* TO 'not_linsey'@'localhost';
bonhoefferthat can't find the user in the table -- but if i take off 'localhost', it works
uid1bonhoeffer: re-select user, host from mysql.user -- the grant may have created another user.
bonhoefferah
bonhoefferyep!
bonhoefferwith a host %
bonhoefferso how can i delete that without deleting the original user?
bonhoefferi'm obviously missing how to refer to hostnames
bonhoefferdroped the user -- by using 'foo'@'%' -- so there is '%', 'localhost' and '127.0.0.1'
bonhoefferwhich all compete
bonhoefferso why does this not work: GRANT ALL PRIVILEGES ON cf_falls_church2.* TO 'not_linsey'@'localhost'; if i select host, user from mysql.user i get | not_linsey | localhost |
uid1It looks okay to me bonhoeffer
bonhoefferso odd
bonhoefferi can't get away from: ERROR 1133 (42000): Can't find any matching row in the user table
uid1Try FLUSH PRIVILEGES
bonhoefferok
bonhoefferno luck -- let me look at priv for the user now
bonhoefferSHOW GRANTS FOR 'not_linsey'@'localhost'; -> ERROR 1141 (42000): There is no such grant defined for user 'not_linsey' on host 'localhost'
uid1SELECT user, host FROM mysql.user WHERE user='not_linsey'
uid1I'm wondering if there's a homophone in there or something?
bonhoefferok
salleuid1: Stop telling people to FLUSH PRIVILEGES. It is not necessary ever since GRANT was introduced more than 15 years ago
bonhoefferworks: +------------+-----------+ | user | host | +------------+-----------+ | not_linsey | localhost | +------------+-----------+
bonhoeffersalle: doesn't hurt!
sallebonhoeffer: SHOW GRANTS FOR 'not_linsey'@'localhost'; will tell you all about this user
bonhoeffermakes me feel like i'm doing something
bonhoeffersalle: ERROR 1141 (42000): There is no such grant defined for user 'not_linsey' on host 'localhost'
sallebonhoeffer: So create it first. CREATE USER ...;
bonhoefferthe user exists:
uid1bonhoeffer: I thought there'd be 3 records: @localhost, @%, and @127.0.0.1 ?
bonhoefferSELECT user, host FROM mysql.user WHERE user='not_linsey' -> | not_linsey | localhost |
salleuid1: The infamous anonymous user? It is always good idea to remove it
uid1salle: I thought he'd set up not_linsey@ 3 different hosts
bonhoefferi only have localhost
bonhoefferas far as i can tell
uid1Got ya - re-read above and it didn't mean what I thought: <bonhoeffer> droped the user -- by using 'foo'@'%' -- so there is '%', 'localhost' and '127.0.0.1'
sallebonhoeffer: "user" in MySQL means user@host pair. foo@localhost, foo@%, foo@some.other.host are different users who can have different passwords and different privileges
mrbubbleshello! i'm having a bit of a strange one and wondered if anyone can help. have just installed mysql on OSX using homebrew. server is running and Navicat is connected via socket. However when I create a new database it never appears in the list (just "information_schema") but if I look in sequel pro it is there. Do you think this is a bug with navicat or something to do with permissions? thanks :)
smerzsounds more like permissions to me ;-) if you connect with unpriviledges user you will only see db's you have access to
mrbubblesyep but I'm connecting as root with both pieces of software
mrbubbleswhich is strange
thumbsmrbubbles: select user(), current_user();
mrbubblesthumbs but can't the root user usually see everything?
jkavalikmrbubbles, if it is authenticated properly, the select thumbs gave you can tell you
mrbubblesgot you, thanks jkavalik and thumbs :)
mrbubblesso it just shows root@localhost in both columns
thumbsmrbubbles: that's good. SHOW GRANTS;
mrbubbles| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | and | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
thumbsmrbubbles: that's sane. SHOW DATABASES;
mrbubblesinformation_schema, ck-2016, mysql, performance_schema
mrbubblesSo that all seems fine
thumbsmrbubbles: so what is the problem again?
mrbubblesJust when I create a new database in navicat it then disappears from the list. can see it in sequel pro and mysql but not navicat
mrbubblesSounds like a navicat bug though, will ping them. thanks
thumbsmrbubbles: or you're not running as root for navicat.
thumbs*the root user
mrbubblesShould be, using the root login details
thumbsbah, connecting.
mrbubblesusing the same details as I am for the sequel pro connection
thumbsok. Bug the navicat folks then.
mrbubbleshmmmm
mrbubblesso I uninstalled mysql completely using homebrw
mrbubblesbut I still seem able to conncet
mrbubblesso maybe I have two versions kicking around...
GreggoryMorning, all. What is a good guide to using Excel with MySQL for reporting purposes, specifically to give information to management in graphical format and not just plain cells?
salleGreggory: Wrong channel I am afraid. MySQL provides Connector/ODBC which is enough to allow Excel connect to MySQL
Greggorysalle: Thanks
thomedyand i cant get my mysql-dump
thomedyhello i have lock tables
thomedyi need a mysql dump with a moderately large db
thomedyand it says i have lock tables
thomedyi tried skip lock tables and single transaction
thomedynothing
archivistreal error message
thomedyi did a mysql check and one tqble is lost
thomedybut i couldnt find a solution for that
thomedymysqlfrm isn't on my system
thomedyi can get it
thomedythats what i read is the solution but it ws 4 am
thomedyand i needed sleep
thomedyim just looking for a back up rigt now
thomedyhow doo i correct the lost table in this
sallethomedy: Get some sleep first
thomedyi already did that now i need the back up
thomedyheres my command
thomedymysqldump --skip-lock-tables -u [user nme] -p [db name] > [db nme.sql]
thomedyCouldn't read status information for table field_revision_balance () mysqldump: Couldn't execute 'show create table `field_revision_balance`': Table 'thomedy_newFile.field_revision_balance' doesn't exist (1146
thomedythats the error i get
thomedybut im concerned i think i need that table
thomedywell not too bad i guess
thomedyif it is the revisision i should be fine
thomedyi just went in and said show tables
thomedyits def not in there
thomedyso its in the schema and not the tables
thomedyi think
thomedyim begging for help
thomedyand researching before i have to go to work
thomedyim so confused
thumbsthomedy: just run mysqldump with -u root
thomedyi must have fixed it
thomedybecause i did that
thomedybut it worked that time
thomedyi basically dropped and recreated the table though
thomedyso that prob fixed it
thumbsthomedy: also, the enter key is not a substitute for proper punctuation.
smerz<thomedy> i basically dropped and recreated the table though <thomedy> so that prob fixed it correct! but the content of the table will be gone. if that is needed you may want to restore that table's content from backup(only that table naturally ;-) )
degvaHello all, I'm using mysql 5.5.40 on a debian machine and executing this query: insert into tableA select id, (sum(columnA) - max(columnA))/(count(columnA)-1) as valueX from tableB group by id; returns NULL on "valueX".
degvaBut in mysql 5.5.50 in CentOS I do get the correct results.
degvaI'm not sure why this happens on that version. What could it be? how could I fix it? thanks!
thumbsdegva: can you make the test case on sqlfiddle.com ?
thumbsdegva: any warnings? SHOW WARNINGS;
degvathumbs, I'm very far from the server. But once I get there, I'll check out that. I'll do the sqlfiddle.com
degvathumbs, seems that sqlfiddle.com doesn't work...
thumbshrm, sometimes it's down
thumbsdegva: works for me.
degvaI get Create script error when using mysql 5.5
degvapostgresql works okay, though
thumbsdegva: MySQL works for me
thumbsdegva: 5.5 and 5.6
degvaweird... also, running a query is being quite slow...
degvathumbs, see this: http://sqlfiddle.com/#!9/f2c61
thumbsone sec
thumbsdegva: http://sqlfiddle.com/#!9/f2c61/2
thumbsdegva: it does take 30 seconds or so to run the select, but that's fine for a shared server like that
degvathumbs, does it work with mysql 5.5?
thumbsdegva: no, the create table fails. Try it yourself.
degvathumbs, I've tried already and it doesn't work for me either.
thumbsdegva: umm, maybe they are having a problem with the 5.5 server
degvaI had similar problems with mysql before. Like executing this: 0.3*1 = -0
degvaso, I'm assuming it might be the engine it's giving me NULL
waqstarSet up some new mysql machines today - what do you guys think I should be monitoring. They will be used as a backend for web interfaces with innodb as the engine. Obviously RAM usage, cpu usage. Im thinking about adding processlist output to see if commands have been running for more than 10 seconds.. What else is important?
passagewaqstar: slow query log, including queries not using indexes
passagewaqstar: till you get those ironed out, at least
passagewaqstar: processlist is not ideal for that, use the slow query log instead
waqstarpassage, Ok - how do I check if queries are not using indexes? For processlist Ill write a small bash script to execute show processlist I can add it in here
passagewaqstar: slow query log has an option to include queries not using index
waqstarah, kewl
thumbsdegva: I might try on my local 5.5 instance. I don't have time yet.
passagewaqstar: the default innodb buffer pool and log file sizes are too small. adjust those to the setting recommended in the manual
passagewaqstar: use the page for your version - http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
waqstarpassage, Ive changed the innodb buffer pool to be 3GB
waqstarpassage, ok thanks ill have a read
passagewaqstar: the manual recommends 70-80% of system ram for dedicated mysql servers
waqstarIm using nagios
waqstarpassage, kewl. Got 4GB ram each so perfect
passagewaqstar: buffer pool first, the log file size, which is a function of buffer pool size
passagewaqstar: whenever you adjust caches and buffers, monitor swap usage for a few days to make sure you haven't used too much
waqstarThese databases are about 90% reads i.e. selects. what else should I be increasing/decreasing
passagewaqstar: This is loosely true: For planning - competent hardware, competent mysql server config as to buffers and caches and log file sizes, normalized schema design, correct table storage engine choice, proper data types, adequate indexing, competent queries. For performance troubleshooting - reverse the order. :-)
passagewaqstar: indexing will most likely impact select queries. learn to use the EXPLAIN capability
degvathumbs, it's okay. I have 5.5.50, but I'd like to know if it's 5.5.40 fault because of the NULL. Thanks!
passage!t waqstar about use explain
ubiquity_botwaqstar: http://dev.mysql.com/doc/refman/5.7/en/execution-plan-information.html and http://dev.mysql.com/doc/refman/5.7/en/explain.html
passagewaqstar: it will tell you if you are correctly indexed
waqstarlol nice. I can do something except for "compentent queries" I have no control over what the developers write. Some queries Ive seen are horrible
passagewaqstar: sloq query log -> explain is a good work flow
sneepwaqstar: Load average
passagewaqstar: whoever's in charge of writing sql should be closely working with whoever's in charge of indexing
sneepSwap usage
waqstarsneep, Ah yes. true
sneepYou can also check if mysql is actually running
sneepDisk usage
sneepPing time
waqstarsneep, lol. got disk usage and is mysqld running
sneep90% of our Nagios messages are about disk space :p
waqstarFound this online, is it worth adding in: https://www.percona.com/doc/percona-monitoring-plugins/1.0/nagios/pmp-check-mysql-innodb.html
sneepSounds interesting
passagewaqstar: have a useful backup and disaster recovery plan
waqstarsneep, same. Its annoyed me a lot so going in and adding logrotate and shipping logs via rsyslog from now. Made nagios much happier
waqstarpassage, I will do mysql replication and then use bacula to dump databases every night
waqstarbacula is quite good
smerzit is. but very powerufl / complex ;-)
smerz*powerfull
waqstarI absently love it. bacula is the best for me.
waqstarabsolute
waqstarargh
waqstarok thanks for all your help. time to get cracking
Some_PersonAre there implications of having a big ass text column in MySQL? The TEXT datatype is not anywhere near big enough in this case
jgehey all good morning, how would I find mtype for all columns in a db?
sallejge: mtype?
jgetrying to see which tables contain the TIME, DATETIME and TIMESTAMP columns
jgeif any
sallejge: SELECT .. FROM information_schema.columns WHERE ...
jgeyeah I got that salle, thanks but i'm having trouble constructing a full query
sallejge: Learn SQL then
jgetoo lazy right now
jge:D
jgeim upgrading to 5.6 from 5.5 but im reading there's an incompatible change for TIME, datatime and timestamp columns which will need to be converted to the right types
sallejge: You want to select some rows from some table. It will take you less time to type this query than it took you to type your question here in #mysql
jgeso I'm trying to figure out if we even use any
jgesalle: well, not exactly dont I have to learn it first before I type it?
thumbsjge: you should learn sql first.
sallejge: "Hello everyone. I have this table and I want to select some rows from it. Can someone write that select for me please?"
jgesorry if I made it sound like that, not my intention to a query .. just not sure how to get the mtype of a column
jgeto ask for *
thumbsjge: query the information_schema
jgehmm ok, but which table?
jgethe columns table?
thumbsjge: salle already told you!
sallejge: What the heck is mtype?
jgeok I see, let me try that
jgesalle: https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-columns-table.html
sallejge: MTYPE is column in this table not some widely used term
thumbssalle: FWIW, the MSSQL folks use that term all the time.
sallethumbs: Maybe
jgeI've heard it before and I don't even know mysql ;)
thumbssalle: and you know what I think of MSSQL admins
sallejge: MySQL information schema follows the ANSI standard so there are data_type and column_type in COLUMNS table
mysqlhi
mysqlI have a SELECT * from table where user="somth"
mysqlis there any way I can do something like if this query (SELECT * from table where user="somth" ) is empty do nothing but if it is non-empty do SLEEP(5)
mysqlor something
sallemysql: Change your nick please
TommckI've got a new ubuntu 16.04 installation. I installed mysql on it, set the root password and can't login to the DB to save my life.
Tommcki've tried resetting the password a few different ways
salle!t Tommck reset root
ubiquity_botTommck: Stop the mysqld server. Edit my.cnf or my.ini, add skip-grant-tables under [mysqld]. Restart mysqld. Connect with mysql -u root and do FLUSH PRIVILEGES. If 5.7+, do: ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPassword'; If 5.6 or earlier, do: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newPassword'); Remove the added line from my.cnf and restart mysqld.
Tommckbeen a while.. where is my.cnf located?
bitchez_b_crazydone
salleTommck: /etc/
Tommckis my.cnf actually mysqld.cnf in ubuntu?
aradapiloti think in debian/ubuntu it's /etc/mysql/my.cnf
Tommckaradapilot: the only think I can see that has a [mysqld] section in it is /etc/mysql/mysql.conf.d/mysqld.conf
Tommckno my.cnf anywhere in /etc/mysql
TommckACTION thinks he hosed something up.. 
Tommck"Failed to start mysql.service: Unit mysql.service is masked."
salleTommck: That's more systemd question
aradapilotyeah i just checked, it's /etc/mysql/my.cnf in ubuntu
Tommckaradapilot: I don't have one there
salleTommck: How did you install MySQL and what exactly did you install?
Tommcksudo apt-get install mysql-server
TommckI noticed during reinstall that this notice came up: "update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode"
TommckI guess that's why I don't have a my.cnf?
pissfroganyone aware on how to start mysql with --skip-grant-tables in systemd linuxes?
pissfrogi'm almost ready to go back to linux sans systemd
litheumpissfrog: stick it in a .cnf file temporarily?
Tommckassuming you just want to do it temporarily, can't you just kill mysql and then run mysqld_safe --skip-grant-tables &
litheumyeah i wouldn't recommend you do that... mysqld_safe may not behave the same as mysqld executed by systemd
Tommckwell, I assumed you were doing something simple like resetting a password or something
pissfrogyes
pissfrogthat's what i'm trying to do, reset the root pw
pissfrogi can't connect to the newly installed server
pissfrogeven as root
bitchez_b_crazyguys
bitchez_b_crazyisn't this a valid query
bitchez_b_crazyselect * from table where user="user" and sleep(5)=1;#stuff
bitchez_b_crazy(mysql)
bitchez_b_crazyshouldn't this query take 5 seconds at least
Tommckseems weird that a DB would have a sleep functoin
Tommckbut I guess it does.. strange
bitchez_b_crazysqlite has this functionality
thumbsbitchez_b_crazy: why do you want to sleep?
bitchez_b_crazycoz I am tired
pissfroglol
pissfroggood answer
TommckI've never wanted to sleep in a database. so curious why anyone would want to
thumbsbitchez_b_crazy: you're probably solving the wrong problem.
bitchez_b_crazy?
thumbsbitchez_b_crazy: why do you want to sleep in sql?
bitchez_b_crazybtw mysql documentation is a complete bs
bitchez_b_crazywhats the time units for this?
pissfrogbitchez_b_crazy, tell us something we don't know
Tommckbitchez_b_crazy: seconds
Tommckhttps://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_sleep
thumbsbitchez_b_crazy: seconds. SELECT sleep(5);
pissfrogg/l and h/f with postgres
Tommck" SLEEP(duration)
TommckSleeps (pauses) for the number of seconds given by the duration argument, then returns 0."
thumbsI think the docs are fine.
pissfrogthumbs, they're better than postgres'
pissfrogthat's for sure
thumbsif we're to compare, I think MariaDB docs are better than MySQL
flyinghi
flyingthis is the official channel of MySQL?
litheumflying: good guess!
flying:)
thumbsno, no, we play crocket here.
flyingmy guess was if it's the official channel
flyingI don't want to bore you :)
thumbsflying: are you asking if Oracle officially endorses this channel?
flyingif it's the primary
thumbsflying: generally, questions for Oracle MySQL are better asked here. If you use Percona, then you can ask #percona, and #maria for MariaDB
thumbsACTION shrugs
thumbs< thumbs> flying: generally, questions for Oracle MySQL are better asked here. If you use Percona, then you can ask #percona, and #maria for MariaDB
desikhello eveybody
flyingthere is some channel also for galera?
thumbsflying: #maria is good for those questions.
thumbsflying: and #mysql-ndb for NDB
flyingndb??
thumbsflying: yes, a cluster implementation
flyingok thanks :)
desikplease bear with me I'm having a hard time trying to reduce the number of queries of one user, as seems he has a fast internet connection or something and he is generating > 3 queries per second any advise how to reduce that to 1 at db level
Drizzt321I'm using REPLACE INTO with a batch and Connector/J and I'm getting back a -2 as the number of rows affected, and I can't find any documentation as to what that actually means.
Drizzt321Hm...I think that is coming from java.sql.Statement.SUCCESS_NO_INFO constant.
passageDrizzt321: The REPLACE statement returns a count to indicate the number of rows affected. This is the sum of the rows deleted and inserted. If the count is 1 for a single-row REPLACE, a row was inserted and no rows were deleted. If the count is greater than 1, one or more old rows were deleted before the new row was inserted.
passageDrizzt321: http://dev.mysql.com/doc/refman/5.7/en/replace.html
passageDrizzt321: I dk anything about java, so...
Drizzt321passage: yes, but I'm actually getting back a -2
Drizzt321so, I guess it's in the Connector/J lib, somewhere
HatamotoThere.
HatamotoEntertaining nickserv issues. Whee.
HatamotoAnyways, I'm looking to create a trigger on a table that tracks updates, but only to some of the columns. I could do it with a series of IF statements, but I was wondering if there was something like an sql equivalent of foreach so I could iterate through the desired columns?
Hatamotoe.g. if I have a table with something like id, name, address, city, state, zip, comments, last_contacted and I only want to do something when there's changes made to the name or address-related fields.
HatamotoI know I could do IF NEW.name <> OLD.name THEN ...whatever for every field, I'm just wondering if there's a cleaner way to do it.
thumbsHatamoto: making a script that creates multiple triggers.
HatamotoOne trigger per column?
jwhhttps://dev.mysql.com/doc/refman/5.7/en/case.html maybe?
jwhor similar
thumbsdepending on the number of column, one approach may be slower.
HatamotoYeah, I was looking at case, but it seemed more like a slightly cleaner if collection than iteration through a list
thumbs*columns
HatamotoWhat I'd like to do would be something like (in pcode) foreach("name", "address", "city") as col; if OLD.col <> NEW.col then ... whatever...
thumbswell, sql is not really procedural as such
HatamotoYeah
thumbsI honestly don't know if a single trigger versus multiple triggers will be faster, or slower.
HatamotoI'm not super worried about execution time, the updates would be coming in meatspace timeframes so either way shouldn't be overly taxing.
HatamotoArite, thanks for the thoughts. I'm sure I can bash something out.
thumbsHatamoto: a single trigger is probably simpler to maintain
HatamotoThat was my thought as well
HatamotoIt'll be a bit longish and uglyish but it'll do.
ackpacketLooking for recommendations here, but, does anyone know of a good way to create some histogram like functionality for a table? Let's say I have a large table, updated daily, with only two values: user_id, bank_account_balance. What I'd like to do is look up a user and say: this user has more money than X% of other users.
ackpacketCross posting, will notify channel if I'm answered
thumbsah, I make a point of not answering folks that cross-post.
ackpacketACTION shrugs
ackpacketTo each his own.
jscatalahey guys, i got a backup from a db with all the tables as with frm, the ib_logfile* and the ibdata1 file. I create a new datadir, change the path on mycnf, but now mysql does not start. Which one is the proper way to do this?
dragonheartfor a start, read the error log.
dragonheartif using the file method you need to treat the entire datadir as something that needs to be replaced.
dragonheart!t jscatala backup
ubiquity_botjscatala: Choices for backing up MySQL data include: stopping the server and copying the files :: mysqldump (-F|--single-transaction) :: LVM snapshot :: innobackup/xtrabackup :: replication AND one of the previous.
jscataladragonheart: as always thanks! ;)
dragonheartyou also need to have made sure that there was a consistent snapshot when the datadir was copied.
jscataladragonheart: it keeps saying that ibdata1 file must be writtable. That file belongs to mysql user
dragonheartjscatala: namei {path to ibdata1} && ls -la {path to ibdata1}
dragonheartwhat os?
jscataladragonheart: ubuntu 16 lts with mysql 5.7
jscataladragonheart: chown -R mysql:mysql *
dragonheartuse {datadir} rather than *
jscataladragonheart: yup i did with data dir
dragonheart!t jscatala apparmor
ubiquity_botjscatala: https://blogs.oracle.com/jsmyth/entry/apparmor_and_mysql
kairozIs there a recommended way to use the mysql client to connect as "'user'@'%'" instead of "'user'@'localhost'" when connecting from localhost?
dragonheart-h 127.0.0.1
kairozdragonheart: it's too clever for me, mysql interprets 127.0.0.1 as localhost. Maybe I should remove that line from my.cnf?
dragonheartis that what select current_user() shows?
kairozdragonheart: I'm not able to log in, so I don't know, but mysql-error.log shows Access is denied for user 'myusername'@'127.17.0.19' ... so maybe the issue is that the user hasn't been set up? I'll try to overwrite the root password for this instance to test.
Sammitchis it possible to split write/read queries to master/slave nodes using only Mysql Router?
lucyfxI have to store some json files, should I use JSON or VARCHAR or TEXT ?
lucyfxmy json can be up to 100 kb, less than 1 mb. maybe I should just use file system?
lucyfxdoes anyone have some experience at these sizes?
Sammitch>65k rules out VARCHAR
Sammitchif you want to natively access the document within mysql then the JSON type might be what you want
lucyfxI will be using it as same as I would use fopen(), basically.
lucyfxwhats the size for JSON ?
Sammitchhttps://dev.mysql.com/doc/refman/5.7/en/json.html
lucyfx18446744073709551615 bytes
lucyfxwait nvm
Sammitchif you're not going to be accessing the data in a structured manner within mysql itself you're best off using one of the TEXT types
lucyfx1073741824 bytes
lucyfxshould I prefer file system, or is the mysql better choice even if the job would normally belong to file system?
Sammitchwith the caveat that when you get up are your max_packet you might encounter issues with your application and/or driver
SammitchIMHO: files in the filesystem.
dragonheartAfter you fopen the file are you just sending it as a http response?
Sammitchanyhow regarding those issues, you'll need to look at your driver's LOB support
lucyfxdragonheart, websocket or stream. Are you suggesting a GET?
dragonheartlucyfx: more thinking filesystem and using X-Sendfile or other webserver acceleration to make the script finish faster.
Sammitch^
lucyfxI am reading on it atm, thanks
lucyfxso x-sendfile is just some optimization, and there is no difference in either ends ?
dragonheartno difference to client end anyway. for server side you'll need to implement/configure bits as you'll find out.
lucyfxAlright, that seems like the most sane option.
lucyfxWhere do you guys draw the line, for example what if my json files were about 4 kb ?
SammitchIMHO: files in the filesystem.
dragonheartif you started to need complex searching within the json object I'd start looking at the database and indexing it. otherwise filesystem.
dragonheart!t lucyfx images
ubiquity_botlucyfx: http://mysqldump.azundris.com/archives/36-Serving-Images-From-A-Database.html and http://hashmysql.org/index.php?title=Storing_files_in_the_database
dragonheartsame principles
lucyfxdo these still hold true even if I would be accesing the file and the rest of the columns at the same time, and never alone?
dragonheartyes. storing on filesystem makes the row smaller and there doesn't need to be a lot of copying of that json document to get it out to the user.
lucyfxalright, thanks once again
lucyfxas a last request, can you link me or name the terms for any other important optimizations or patterns if there are any, like the xsendfile, what else is there?
dragonheartthat's quite open ended and it becomes very specific to the problem you are solving.
lucyfxWell, if you could just name the common terms or techniques, I'd love to spend a night or week learning those. I understand if this is a more complicated request than I initially thought
lucyfxI am after caching, for the most part
dragonheartlook up the features of your webserver and understand them. you of course know the meaning of premature optimization too right?
lucyfxkind of :p
lucyfxOnce again, thanks for the help. I will be on my way now ^_^
JavaLoverI know that you link users to comments based on the id of the user right
JavaLoverbut how do you protect the data of a users id?
JavaLoveris it strictly based on backups?
JavaLoverlike if I have id 7 and something were to corrupt the users table...am I just @)*#($ without backups?
JavaLoveram I supposed to just trust that because I have backups of the users table, in the future if anything bad were to happen I can just fix the broken user table?
thumbsJavaLover: how else do you expect to restore data?
JavaLoverright...
JavaLoveridk why I just, it scares me that i'm basing it on a primary collumn
JavaLoveratleast with basing it off a username I can see like "oh this data belongs to thumbs" for example
JavaLoverinstead of this data belongs to user 7
thumbsJavaLover: referring to a unique identifier would be best.
JavaLoverwhen you say referring
JavaLoverwhat do you mean?
thumbsJavaLover: using a column that can uniquely identify a specific row.
JavaLoverright
JavaLoverso true even if accountid is unique in that case
thumbsJavaLover: and the goal is to never re-use those values.
thumbsJavaLover: if
JavaLoverohhh
thumbsJavaLover: if 'thumbs' is id 7, then no one can be id 7 again.
JavaLoverso then I could change my username, but keep my id?
JavaLoverok I see the logic
JavaLoverso then it's my job to always backup the database
thumbsJavaLover: always.
Sammitchbut the trouble with assigning IDs from a sequence [eg: auto-incrementing integer] is that it becomes untenable if you have more than one system issuing IDs
JavaLoversooo what should I do then Sammitch?
Sammitchwhich is why I'm an advocate for UUIDs
JavaLoverhow do I get one of those?
passageSammitch: there are solutions to that. uuid, odd/even auto_increment_increment, etc
passageSammitch: more than one system issuing ids is pretty rare for most use cases
dragonheartwhy not to use uuids https://www.percona.com/blog/2015/04/03/illustrating-primary-key-models-in-innodb-and-their-impact-on-disk-usage/
JavaLoverdragonheart what would you recommend then?
dragonheartthere is a ordered uuid if you read it.otherwise auto_increments
JavaLovermy current option is the default, assign data based on the primary id collumn
dragonheartor a PK based on unique data is just as good
JavaLoverwhat's ordered uuid?
JavaLoverhow do I use it?
dragonheartffs - read!
JavaLoverI like you
JavaLoveryou're a rtfm guy I trust your answer
JavaLovergonna goo rtfm hold on
Sammitchpassage: "pretty rare" in that no one thinks of it until it's too late, but with the rise in distributed systems this is only going to become more of a problem
Sammitchdragonheart: "PK based on unique data" has been true since forever, but that still doesn't stop people from using surrogate keys everywhere :P
Sammitchplus there's the inevitable "but we need to change the username!"
JavaLoverhttps://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/
JavaLoveris that the correct way?
JavaLoveryou have like tables with uuid?
Sammitchyeah that ordered-uuid jazz is the way to go
Sammitchdragonheart's link is really just an explanation on why not to use *text* UUIDs
JavaLoverbut i'm confused...having a uuid isn't that the same as the primary id?
JavaLoverwhat happens when i have 2 tables?
JavaLoverhow does it know what the last uuid was?
dragonheartright, let me clarify for once only. if you need to create your one pk, use a auto_increment. if for some reason you need to use uuids, consider order uuids. /EOM my opinion only. have fun. got my own work to do so i'm not going to discuss this
SammitchUUIDs are random, if you need to sort records chronologically then you need to store that data elsewhere in the row
JavaLoverso thennnn we're back to square one, primary id's are fine.
JavaLoverand then a uuid would be used as reference to the existing primary row it's a part of
salleJavaLover: Say primary key please not id's
JavaLoverokay primary key
salleJavaLover: id is just common convention for single column primary key
JavaLoverso you have your primary key and then you generate a uuid that you use
passageJavaLover: no such thing as primary id. are you reading that from some gui?
JavaLoverno i'm just not a big vocab XD
JavaLoverso then, when you say insert(new user with uuid)
salleJavaLover: Primary key is unique identifier.
JavaLoverdoes mysql understand like "oh uuid has to be unique to this unique collumn you have
JavaLoverand it will always work?
salleJavaLover: First thing you need is to understand what PK is
JavaLoverwell its starting to make sense though
JavaLoverlike even if i reinsert all the data out of order, with proper uuid
JavaLoverthan the data will still be fine.
salleJavaLover: Step back and learn what Primary Key means
JavaLoverI get that a primary key would do that
JavaLoveri'm just saying if for some random reason, the primary key gets effected
JavaLoveras long as the data had the right uuid it would be fine
salleJavaLover: Step back please
JavaLoversalle i understand that a pk would uniquely identify all table records
salleJavaLover: Correct.
JavaLoverbut what happens if for some reason that PK gets corrupted
JavaLoveror we run out of room for having that PK?
salleJavaLover: What do you mean by corrupted?
JavaLoversay we go up to the limit and a table is full
JavaLoverthen what?
salleJavaLover: You get error when you try INSERTing more rows
JavaLoverso how do I branch of and continue this adding of users?
JavaLoveroff*
passageJavaLover: wat is the range of an int unsigned auto_increment primary key?
passagewhat is*
JavaLoveridk...
JavaLoverbut i imagine a table can get full
salleJavaLover: Use proper type for your needs
salleJavaLover: In theory or in practice?
JavaLovertheory
JavaLoverhonestly if my table was full i'd be rich & higher people to deal with this for me xd
passageJavaLover: why are you using a data type without understanding its properties?
salleJavaLover: Answer passage and then answer same question about bigint unsigned
JavaLoverpassage because this was how I was taught to do it
JavaLoverpassage but right now i'm questioning if what i was taught is the right thing to do or not.
passageJavaLover: perhaps someone else
JavaLover 0 to 18446744073709551615.
salleJavaLover: How soon you will get that many users in your users table?
JavaLoverprobably never XD
SammitchI hit th 16M mark thanks to INSERT IGNORE and a poorly-written script :P
Sammitch*on a MEDIUMINT columne
salleJavaLover: Do you realize how big this number is? :)
JavaLoverI get how big the number is
Sammitchand unsigned int is ~4billion, not that number you wrote
JavaLoverwell that's from the docs
JavaLoverokay I guess I just base everything off primary id then
JavaLoverprimary key*
salleSammitch: That's the range of BIGINT UNSIGNED
Sammitchsalle: ah, I don't know what I read wrong. I think I'm getting fuzzy from this migraine -_-
salleJavaLover: So in theory you can hit that limit, but in practice you will need thousands of years even if you insert millions of rows per second
salle!m Sammitch integer types
ubiquity_botSammitch: See http://dev.mysql.com/doc/refman/5.7/en/integer-types.html
Sammitchsalle: I know the types, I misread your question to JavaLover :P
JavaLoverbut salle an unsigned big int is the pk?
salleJavaLover: nope. INT UNSIGNED is column type
Azundrismorning salle
JavaLoverright so I auto increment
JavaLoverand i'll never auto increment to that amount
JavaLoverso then why do people say to do this order uuid stuff?
salleJavaLover: So is BIGINT UNSIGNED. That's column type *not* primary key. You can make such column primary key indeed
thumbsJavaLover: some systems benefit from non-predictability of user ids.
salleAzundris: Hi! I'd say it is night :)
thumbsAzundris: no hi???
JavaLoverI also enjoy that type of a system idea
JavaLoveras a user signing up i'd like to have a random generated id...but is it practical?
salleJavaLover: "Why do people say ..." is wrong question
jwhthe answer to everything is oauth?
Azundristhumbs: only just tuned in, haven't seen you talk yet :) hi!
thumbsAzundris: LIES
thumbsACTION tangos with Azundris 
JavaLoversalle I understand your logic and I agree with it
jwhbromance
JavaLoverbut i'm just curious, is there anything that can go wrong with using uuid()
salleJavaLover: Another wrong question
salleJavaLover: Is there anything that can go wrong with using computers? Absolutely yes
Azundrisyou'd have to have an extremely old server. uuid() was broken as @#$@ at one point.
JavaLoverokay so then generating a uuid() is a normal thing to do
JavaLoverhow does mysql generate this uuid?
Azundris"I'm glad you asked this question."
salleJavaLover: Look at the code
JavaLovernot the entire process but like...how does it determine it's unique to everything else?
salleJavaLover: UUID() is function. Is it normal thing to use functions?
salleJavaLover: uuids are generated according to RFC 4122
JavaLoveroh so it's based on time?
guzzlefryI think it's based on several things.
JavaLoverokay
JavaLoverI think based on my reading that, a pk along with a generated uuid is best
AzundrisSeveral, yes. I vaguely remember also putting in some magic that prevents two UUIDs being the same if generated at the "same time", outside that whole random thing
JavaLoverthe pk determines what row you belong to and the uuid determines how to access it
guzzlefryI'd honestly just use auto incremented integers.
JavaLoverobviously the uuid would be unique, so if you fail to signup you'll just have to signup again
JavaLoveri'm technically still using auto increment integers guzzlefry
guzzlefryoh, I didn't read the backlog. Is the UUID for some sort of referral code?
JavaLoverthis is just a way to reference where a users id is unique from it's row
salleJavaLover: Already wrong
JavaLoveri'm wrong?
JavaLoverhows that wrong?
JavaLoverit seems pretty logical to me...
guzzlefryJavaLover: what are you trying to do?
salleJavaLover: "uuid determines how to access" is plain wrong
JavaLoveri want to have data linked to my users, and the current setup is to attach the data to the pk id that you have
JavaLoverso if your id is say 7, then the data in table 2 is referenced by id 7
salleJavaLover: You refer to rows in another table by their PRIMARY KEY
salleJavaLover: Always. That's what PRIMARY KEY is for
JavaLoverthat's how i'm currently doing it...but I think uuid() might be a better way
salleJavaLover: What makes you think so?
JavaLoverbecause it seems very unique
guzzlefryJavaLover: auto-incremented integers are also guaranteed to be unique. Maybe more so than UUID.
salleJavaLover: Relevance?
salleJavaLover: Water seems wet. So what?
JavaLoverbut could you identify it?
JavaLoverlike say the pk messes up
salleJavaLover: What is "it"?
salleJavaLover: What do you mean by "messes up"?
JavaLoveridk somehow the auto increment value radically messes up
salleJavaLover: What do you mean by that?
JavaLoversay that someone goes in & sets it to 5
JavaLoverwhen it was supposed to be 500
JavaLoverwhat will happen to the data?
Azundrisnothing? :)
JavaLoverugh fine, maybe i'm being stupid and i'll just keep using the pk
JavaLoverso then why even have uuid() to begin with?
salleJavaLover: Why not?
guzzlefryJavaLover: Primary keys are unique. So if you try to re-insert a row when another row in the table has that primary key, it throws an error.
AzundrisIt's more likely to be unique across servers.
JavaLoverAzundris what's that even mean?
JavaLoveroh right
JavaLoverno Azundris makes a point, what if I have 2 databases
JavaLoverno same stupid thing nevermind
salleJavaLover: If you allow someone to put wrong data in your database there is no difference what kind of primary keys you use. He can always set wrong value
JavaLovertrue
JavaLoveranyways thx for the help
JavaLoveri'll just stick to using the PK like salle suggested
salleJavaLover: Wait a second
salleJavaLover: If you use relational database such as MySQL you don't have any other choice. You need PKs
salleJavaLover: Using relational database without primary keys is possible, but pointless
JavaLoverwell I do, i'm using innodb with mysql
JavaLoveryeah I didn't want to do that either
JavaLoverI just want to make sure my data is safe
JavaLoverbut I guess the only way is to back it up constantly
salleJavaLover: Relational database theory dictates that each and every table must have primary key. No exceptions
JavaLoverokay you make a valid point
JavaLoveri'll just follow the format & make sure i back everything up
salleJavaLover: In practice there are some very rare cases when it is useful to have PK-less tables, but these are really rare cases
salleJavaLover: With InnoDB engine it is very bad idea to create table without PK for several reasons
guzzlefryJavaLover: A primary key can be an auto-incremented integer, string, uuid, whatever. It just has to be unique across the entire table.
JavaLoveryeah I know what you mean
salleJavaLover: Primary Key has nothing to do with "safer" you know
JavaLoverright
JavaLoverokay i'll just do that
JavaLoverbut salle I have plans for the evening
JavaLoverso i must be going but thank you so much for clarifying :D
JavaLoveri'll stick to pk and just make backups :D
salleJavaLover: If you want to drive a car you need car on streets your car must have steering wheel and brakes. They don't make it safer or less safe. They are must-have attributes.
JavaLoverI get your point
JavaLoverI gotta go Salle but ty :D
JavaLoverlaters
salle:)
salleProbably the most confused person I saw here within last year or so
guzzlefryRelated, does the bot have some beginners' database design/relational theory link? :P
salle!t guzzlefry db
ubiquity_botguzzlefry: http://bot.hashmysql.org/ubiquity/
guzzlefryoh, nice
passage!t us qbout normalization
passage!t us about normalization
ubiquity_bot#mysql: http://mysqldump.azundris.com/archives/20-Nermalisation.html and http://goo.gl/2X5B4 and some here http://www.keithjbrown.co.uk/vworks/mysql/
Azundriswith kittehs! :)
jeffrey_fUsing mysql-connector in Python3 - I want to process a cursor fetchone(), is that cursor updatable, as in, can I update the record?
lawwith mysql 5.7 on debian, if the daemon (on a fresh install) isn't starting and isn't writing to /var/log/mysql/error.log or throwing anything to STDOUT/STDERR, how can I figure out what it's complaining about?
lawthe default my.cnf started right up, but this is a slave so as a first start I copied over the master's my.cnf (where it's working happily, same mysql 5.7) and now it's cranky
dragonheartjeffrey_f: i don't know a large amount about the mysql-connector but it doesn't seem possible. If you want to update a record based on a criteria use and UPDATE query.
lawoh, I know why
jeffrey_fdragonheart: I was going to try it anyway, but if there is a "right" way of doing it, I'd like to do it that way. I am not new to programming, but new to Python. In the language I used for work, the cursor is updatable
lawbind-address is different. derp.
lawI wish there was a 'check-config' option for this thing
dragonheartlaw: it goes by the name of "bothering to read the logs"
Azundriswell, they messed up their logs, so that would be hard :)
Azundrislaw: if the server actually comes up, you can check the global variable to see where the error log goes. Of course if you're unlucky, that may just be stderr, and redirected in some wrapper. Hey, that gives me an idea --
dragonheartusing pt-config-diff may be one way to check before doing a restart
bonhoefferhey -- i'm not seeing grants: ERROR 1141 (42000): There is no such grant defined for user 'chad' on host 'localhost'
bonhoefferSELECT user, host FROM mysql.user WHERE user='chad' shows the user is there
passagebonhoeffer: show grants for 'chad'@'localhost'; returns what?
bonhoefferpassage: ERROR 1141 (42000): There is no such grant defined for user 'not_linsey' on host 'localhost'
bonhoefferthe user is actually 'not_linsey'
bonhoefferGRANT ALL PRIVILEGES ON cf_falls_church2.* TO 'not_linsey'@'localhost'; produces "ERROR 1133 (42000): Can't find any matching row in the user table"
passagebonhoeffer: select user, host from mysql.user; <-- pastie.org that output
bonhoefferhttp://pastie.org/10913732
passagebonhoeffer: you have two anonymous user accounts, which are doing you no good. those are the ones with an empty string for user. you should drop them, they complicate things
bonhoefferok
passagebonhoeffer: are you connected as the mysql root user?
bonhoefferyes
bonhoefferhow do i delete the empty accounts since i can't find a pk or key off the user
passagebonhoeffer: don't manually manipulate the mysql admin tables, it can cause problems
passagebonhoeffer: drop user ''@'localhost; drop user ''@;christines-mackbook-air.local;
passagebonhoeffer: drop user ''@'localhost; drop user ''@'christines-mackbook-air.local';
bonhoeffergot it -- makes sense
passagebonhoeffer: you added that 'not_linsey' account manually, did you?
passagebonhoeffer: with an insert into query?
bonhoeffernot sure -- but i def did via the mysql command line
bonhoefferis there a way to see command history
passagebonhoeffer: is this version 5.7?
bonhoeffermysql Ver 14.14 Distrib 5.7.13, for osx10.10 (x86_64) using EditLine wrapper
passagebonhoeffer: yeah, mysql 5.7
bonhoefferi could just drop and recreate the user
passagebonhoeffer: different syntax for user management
passagebonhoeffer: if you inserted that not_linsey account, go ahead and delete it. delete from mysql.user where user = 'yadda' and host = 'yadda';
passagebonhoeffer: then follow this syntax - http://dev.mysql.com/doc/refman/5.7/en/adding-users.html
bonhoefferpassage: i see what happens
bonhoefferi try to change the password, then plugin gets loaded
bonhoefferso frustrating -- the plugin field for the user keeps loading
bonhoefferand then i don't have any capabilities
bonhoefferevery other user has mysql_native_password, but the user i'm working with has *4460F9E52AB8B4D3B7EFEAEE4D3085BE4DBD4AC6
bonhoefferi can't make a basic user that i can then login as
dragonheartshow details of your creation and your login attempt.
bonhoefferdragonheart: https://gist.github.com/tbbooher/15f00777b580ced2026932a9e4e80e58
dragonheartbonhoeffer: looks like your table structure isn't right. Try running mysql_upgrade
bonhoefferok
bonhoefferi think the password was too long