mrpacketheadms access was so easy :-(
mrpacketheadit just now doe'snt keep up with what we are doing
mrpacketheadso i'm teaching myself this
thumbsmrpackethead: read about relational databases
mrpacketheadI get the relational bit
thumbsmrpackethead: stop relying on a GUI to do all the work for you, then.
mrpacketheadits just building a front end interface to it that is really the hard bit. I've been using mysql workbench as a way of entering data
thumbsmrpackethead: right. See my comments about GUIs
mrpacketheadso.. what do you do to enter data in
thumbsmrpackethead: I use the mysql client
thumbsmrpackethead: see the INSERT and UPDATE manuals
mrpacketheadyeah i can send Insert/updates all day
mrpacketheadbut its hardly a frindly way of processing anything
mrpacketheadand not something i cna give a non techy person
thumbsmrpackethead: it's friendly enough for me.
thumbsmrpackethead: build interfaces for non-techy folks.
mrpacketheadok, so.. some kind of GUI
mrpacketheador WUI
mrpackethead( think we just have been round in circles.. but anyway
thumbsmrpackethead: yes, you're mostly wasting time.
mrpacketheadno, i'm not wasting time at all. Because i've learned somethign
gpsingh159i m installing msyql
gpsingh159in linux mint
gpsingh159how to install mysql
thumbsgpsingh159: use your package manager.
gpsingh159thumbs: package manager means
gpsingh159i installed apache then php 7.1.2 now i need to install mysql
thumbsgpsingh159: the channel for your distro will tell you.
gpsingh159#linux mint
thumbsyou're very welcome, gpsingh159
mrpackethead_is it possible to change a feild from being an INT to being a foreign key
archivisttype can (should) remain an int even when used as an fk
Xgcmrpackethead_: A constraint doesn't touch the type of the column. It's a separate construct / object in the database that is related to that data, however.
Xgcmrpackethead_: Just as when you create / drop an index which refers to a column or columns, creating or dropping a foreign key constraint has no impact on the type of the related columns.
IonutVan_any idea what's happening here:
ss23IonutVan_: Add quotes
ss23DROP DATABASE `foo`;
StoBrendoadding a backslash wouldn't work?
IonutVan_great, thanks
IonutVan_but was strange :)
iskorptixcan someone please tell me what is wrong with following query? I'm trying to select two columns from two different tables
iskorptixSELECT CONCAT( AS id ' ,', as name) AS group1 FROM t1, t2 limit 1;
sisvethose aliases within the call to concat looks wrong.
iskorptixsisve: can you give an example pls ?
sisve CONCAT(, ', ',
iskorptixthat works, but I need to use "AS" as well :(
iskorptixbecause I need to rename column on the fly
iskorptixdata from that query will go to solr
sisveWhy? You're concat-ing the value, the name of the column doesn't persist.
iskorptixagain, I need to make sure what I'm sending
sisve SELECT AS id, AS name, CONCAT(, ', ', AS group1 ?
iskorptixok let me try, sec
sisveYou need to explain what you expect to get out of the query.
sisveStart with telling how many columns in the result you expect.
iskorptixtwo columns and
iskorptixmaybe concat is not even need
iskorptixI just want to see result in two different columns
sisve SELECT AS id, AS name FROM ...
iskorptixthanks sisve!
gpsingh159i have mysql 5.5 version and i need to update mysql version into 5.7
Xgcgpsingh159: Read the upgrade documentation for 5.7
gpsingh159Xgc: ok i m doing that but Length: 19202 (19K) [application/x-debian-package]
gpsingh159mysql-apt-config_0.8.0-1_all.deb: Permission denied
PickledEggsI'm in the process of learning MySQL and I'm struggling to get what I feel like should be an easy SELECT to work. I'm trying to sum things from one table, join it to another table to group by and then use a where to filter the results. Here's the code I have:
PickledEggsUsually I can work through syntax errors and whatnot but for some reason the error reason for this is coming up blank.
thumbsPickledEggs: your use of a subquery is not proper. Start over
thumbsPickledEggs: focus on the inner query. Get that one working first.
XgcWhat subquery? :)
XgcACTION counts the ( )s
thumbsSELECT SUM(expression) WHERE ... without a FROM clause
XgcPickledEggs: For MySQL, read about derived tables. If you happen to try MariaDB, read about CTE - Common Table Expressions
XgcHe's missing more, like SELECT for each FROM, etc.
XgcMissing FROM ...
XgcPickledEggs: As thumbs suggests, work on the inner most query first, separately. Don't try to build it all at once.
thumbsPickledEggs: additionally, the HAVING clause can be very useful
PickledEggsThanks everyone, I'll look at those things and keep working
PickledEggsHa! Got it, thanks folks.
vfwERROR 1049 (42000): Unknown database '/usr/share/mythtv/mc.sql'
XgcThat's an odd database name.
Xgcvfw: Sounds more like a mistake in your program / shell script.
vfwHost '' is not allowed to connect to this MariaDB server
thumbsvfw: do you have grants for @ or @% ?
vfwIt's a mythtv server, (hopefully - sume day)
vfwthumbs: Id on't konw
thumbsvfw: log in as root and find out. Select from the mysql.user table
vfwMySQL time zone support is missing. Please install it and try again. See 'mysql_tzinfo_to_sql' for assistance.
thumbsok, so fix that.
vfwthumbs: Ok log in as root?
vfwand do what?
vfwOr... which is my problem?
thumbsare you not seeing that error?
vfwthumbs: Yes, but not sure how to fix it?
thumbsvfw: what did you try to fix it?
vfwWell, I do not know where to begin....
thumbsvfw: the first thing you should do when you get an error is to look it up.
vfwI changed <LocalHostName></LocalHostName> to <LocalHostName>localhost</LocalHostName> in ~/.mythtv/config.xml
vfwthumbs: On google?'
vfwerrors ^^^^^^^^
vfwthumbs: Can you take a look and see?
thumbsvfw: all I see there are mythtv errors.
vfwthumbs: Correct.
Woetin fact, "mysql" is not even mentioned
Woetvfw: so what is your MySQL question?
vfwSo how do we fix it?
Woetvfw: fix what?
thumbsvfw: ask the mythtv folks first.
Woet[21:13:35] <vfw> MySQL time zone support is missing. Please install it and try again. See 'mysql_tzinfo_to_sql' for assistance.
Woetfirst result in google
Woetas for the other errors, they have nothing to do with MySQL.
thumbsright, I told him to look up the error.
vfwMySQL time zone support is missing. Please install it and try again. See 'mysql_tzinfo_to_sql' for assistance.
Woetapparently he's incapable of doing so
Woetfor whatever reason
vfwHow do I fix mysql_tzinfo_to_Sql problem
Woetthumbs: apparently he just has a reading problem
mrpacketheadWith respect to a Foreign key,, what is the reference Table and The referenced column
thumbsWoet: yes, write-only client.
Woetthumbs: ah, fascinating
vfwHow to "install it"?
thumbsmrpackethead: the foreign table name, and foreign table column
mrpacketheadi'm trying to set one up in workbench.
thumbsmrpackethead: ok.
mrpacketheadI can pick a key name and it gives me a list of tables i can chooise from. that seems straight forward
mrpacketheadi get an error.. "Cannot add or update a child row
Woetmrpackethead: what is the query?
thumbsmrpackethead: the full error.
mrpacketheaddid that help?
thumbswell, is timing out for me, so I don't know yet
mrpacketheadoh, its running for me
mrpackethead <-- as per the topic
thumbsmrpackethead: show the SHOW CREATE TABLE tbl output for each table.
thumbsmrpackethead: in short, both tables need to be InnoDB, the data types of both columns must match, and the existing data must support the constraint
mrpacketheadboth are InnoDB, both are int(11)
thumbsmrpackethead: show us
sisvemrpackethead: are both the same signed/unsigned?
thumbsmrpackethead: and the zerofill value doesn't matter
mrpacketheadboth unsigne.d
mrpacketheadjust a sec, i'll get the outputs
mrpacketheaddoh.. sorry
mrpacketheadthats wrong
thumbsmrpackethead: why is your PK not unsigned?
mrpacketheadsorry i'd put the wrong table in.
mrpacketheadPart_Number in OB_Components should refer to ID in Stellalibrary
thumbsmrpackethead: find out if you have existing children (Stellalibrary) without a parent (OB_Components)
thumbs!t mrpackethead a not in b
ubiquity_botmrpackethead: SELECT a.* FROM a LEFT JOIN b ON = WHERE IS NULL;
mrpacketheadthumbs, i dont' know what that means sorry
mrpacketheadshould the primary key be unsigned?
thumbsmrpackethead: yes.
mrpacketheadok.. one is and one is'nt
mrpacketheadlet me fix that
mrpacketheaddifferent error now
mrpacketheadERROR 1215: Cannot add foreign key constraint
thumbsthe other column must be unsigned too.
vfwOk I think I got most of it fixed but how do I verify that I have the password set properly for a particular mysql user?
vfwOr to just change that password to the correct one?
thumbsvfw: sure.
vfwIt appears that some of what I've done is working, some is not....;)
vfwthumbs: So how would I change or set password for mythtv ?
thumbs!m vfw set password
ubiquity_botvfw: See
vfwPASSWORD('my-password') ?
thumbsvfw: did you read the manual?
vfwit needs to have ' at beginning and ' at end?
vfwJust question about puncuation....
vfwthumbs: Looking at manual now...
thumbsquotes are not punctuation, technically.
vfwso the ' and ' needs to be there?
vfwand it won't be part of the password?
thumbsfor literal strings, always.
vfw"Can't find any matching row in the user table"
phelixTrying to figure out how to write a query for this.. Can you not use the where clause when using order ?SELECT * FROM `Listings` ORDER by L_SystemPrice DESC WHERE L_City="Boise" LIMIT 10
passage!m phelix select
ubiquity_botphelix: See
passagephelix: wrong place
passage!t phelix about tutorial
ubiquity_botphelix: MySQL Tutorial: SQL Tutorials: and
thumbsphelix: are you new to sql?
passagephelix: the manual is quite clear. select .. from .. where .. order by ..
phelixgot it! thanks
vfwERROR 1133 (28000): Can't find any matching row in the user table
thumbsvfw: that's a pretty clear error.
passagevfw: you're trying to update a particular row in a table
passagevfw: unfortunately, it doesn't exist. what might your mistake be?
vfwpassage: Just trying to set the password for a mysql user.
vfwor to verify if it is set.
vfwthumbs: So does that mean that mythtv user does not exist?
thumbsvfw: select from the mysql.user table and see.
vfwHow do I select mysql.user?
vfwis it mysql.mythtv?
thumbsvfw: are you new to sql?
vfwthumbs: Yes I am.
thumbsvfw: did I say mysql.mytytv?
thumbs!m vfw select
ubiquity_botvfw: See
vfwthumbs: No
thumbs!t vfw tutorial
ubiquity_botvfw: MySQL Tutorial: SQL Tutorials: and
vfwOk. One can verify a password for particular user with command "mysql -h host -u user -p"
vfwGot it. Thanks.
passagevfw: a mysql user account is the unique combination of username and host. 'joe'@'localhost' and 'joe'@'' and 'joe'@'%' are three separate accounts. can have different passwords and privileges
mrpacketheadso, my PK in both tables are unsigned.
thumbsmrpackethead: `Part_Number` isn't unsigned
mrpacketheadPartnumber in in Stellalibrary is a char
thumbsmrpackethead: further, one column is not even int!
thumbs < thumbs> mrpackethead: in short, both tables need to be InnoDB, the data types of both columns must match, and the existing data must support the
thumbs constraint
thumbsmrpackethead: you missed the part about "the data types must match"
passagemrpackethead: I suspect that your data is not in a state to accept a foreign key constraint
mrpacketheadpart_number.OB_Components is int(11) unsigned.. ID.Library is also int(11) unsigned.
passagemrpackethead: that means that there is a value in ob_components.part_number that isn't in
thumbsoh, I misread.
passagemrpackethead: I'm going to show you how to check
passage!t mrpackethead about a not in b
ubiquity_botmrpackethead: SELECT a.* FROM a LEFT JOIN b ON = WHERE IS NULL;
thumbsI went through this already.
mrpacketheadpassage, i assume i have to swap a and b for my table names
passagemrpackethead: a is ob_components, b is stella-library
passagemrpackethead: the ON condition is the fk constraint columns
passageob_components.part_numer =
staticshockwould someone mind helping me out understand the differences here?
mrpacketheadi've got a bout 30 records that come up
thumbsmrpackethead: delete those rows, yes
mrpacketheaddelete them or fix them?
staticshockthere's some underlying type coersion happening there, but i can't figure out how to investigate it.
passagemrpackethead: until the data is in shape to accept the fk constraint, you will get an error when you try to add it
mrpacketheadnot sure if this matters but is not a 1:1 relationship
thumbsmrpackethead: either or
thumbsmrpackethead: then why do you have two tables?
passagemrpackethead: it's your data. fk constraint is defined in the child table, and references the parent table. referential integrity means no child rows without a corresponding value in the parent row
passagemrpackethead: fk constraint defines a 1 to none, one or many relationshipt between parent table and child table
mrpacketheadthumbs i can have several components of the same type of thing in the lbirary
thumbsmrpackethead: then it isn't a 1:1 relation.
mrpacketheadthats what i said
thumbsoh, indeed.
mrpacketheadbut now i'll go and check my data
mrpacketheadtheres only a small number
mrpacketheadnot sure how they ahve got managled
mrpacketheadmaybe during converrsion
passagemrpackethead: that's what fk constraints are for :-). stop those orphan kids
mrpacketheadyes.. we don't want them
mrpacketheadit would be relaly problematica
thumbspoor orphans
staticshockanyone here know what causes the difference in these queries?
passagemrpackethead: you should rethink you CASCADE statements, too. what do you actually want to happen when a parent value is changed or deleted
passagemrpackethead: to avoid orphans, you need to cascade the updated values and cascade the deletes.
thumbsstaticshock: MySQL or MariaDB? What version?
staticshocki'm seeing the same behavior both on mysql 5.6 and on aurora
mrpacketheadpassage, thanks.. found some rows that did'tn have any partnumber in them
mrpacketheadnot sure how those got there
mrpacketheadbut it will have been a hang over from the old data
passagemrpackethead: it's surprising how much of dba work involves cleaning data before and after imports
mrpacketheadthe good thing is i'll know to check for this with other tables
passagemrpackethead: a not in b is a great tool :-)
mrpacketheadpassage thanks..
mrpacketheadSELECT a.* FROM a LEFT JOIN b ON = WHERE IS NULL; - so question for you on this
mrpacketheadso i've cleaned up my data so every Part_number now has a valid Partnumber
mrpacketheadbut my query still returns some rows
mrpacketheadSELECT OB_Components.* FROM OB_Components LEFT JOIN Stella_library ON = Stella_library.ID WHERE Stella_library.ID IS NULL;
passagemrpackethead: then what you think you did didn't do what you assume you did
passagemrpackethead: wrong column for ob_components, isn't it?
thumbsso you deleted random rows?
passagemrpackethead: the fk constraint was on ob_components.part_number and
mrpacketheadno.. i deleted rows that had errors in them
mrpacketheadi could clearly see them
thumbspoor data
passage"The man who carries a cat by the tail learns something that can be learned in no other way." - Mark Twain
passageACTION hears the 'beer o'clock' whistle blow down in the kitchen
mrpacketheadso I have FK that is ok now
mrpacketheadthumbs there was some rows that were essetially blank in ob_components
mrpacketheadthye just had an ID but no part number
thumbsmrpackethead: yes, I told you that initially
mrpacketheadthankyou for your help
mrpacketheadtheres a lot of concepts that need learning
mrpacketheadpassage... Thanks.. got that sorted out
mrpacketheadyes i was using the wrong Feild
mrpacketheadI went and deliberately broke the data
mrpacketheadto test
mrpacketheadNow my row count for a not in b is zero
mrpacketheadI'm writing this down in my note book
mrpacketheadI'm sure i'll use this again.
thumbsmrpackethead: please go easy on the enter key. It's not a substitute for punctuation.
passagemrpackethead: good news indeed :-)
mrpacketheadalready found same issue in another couple of tables
mrpacketheadpassage, thanks again. i've just made a quantum leap on where i was up to.
mrpacketheadand.. ( apologies for the return ) learned a bunch on the way.
mrpacketheadi know IRC help requests can be really painful
passageIRC help requests are O2 to us
mrpacketheadlol.. well if you come to #openpnp or #openlighting i can return the favor
mrpacketheadthough you just have to sometimes pay forward with help hoping you will get some help from someone else
mrpacketheadit mostly works.
conleyI'm having a hard time deciding between mysql and postgres. Assuming you all are mysql fans in here, why so?
thumbsconley: why would we discuss postgres here?
conleyit's discussing why mysql is better than alternatives, if it is
thumbsconley: every rdbms has strenghts and weaknesses
thumbsconley: pick the one that will satisfy your requirements.
conleyof course, to a certain extent, but most people i know seem to have a clear preference for one over the other, and it's usually for a good reason
conleybut, fair enough
thumbsconley: personal preference is irrelevant.
conleytrue enough
conleyI just read this.
conleywould you agree with it?
thumbsconley: I won't comment.
conleyfair enough
thumbsconley: pick the one that will satisfy your requirements.
passageconley: can't reliably spell postgrse
mrpacketheadi got it all cleaned up!
passagemrpackethead: there you go then :-)
mrpacketheadused the a in b twice to find my issues
mrpacketheadhave put it into my cheat sheet
darwinwhat is the tool that allows you to create test environments of different versions of mysql in your dev environment, trivially?
darwinI am blanking on the name and google is not helping :/
thumbsdarwin: sandbox
darwinexactly it. thank you, without that word I was lost.
mrpacketheadis there a reletively easy "tool" i had can use to build a
thumbsmrpackethead: what is 'a'?
mrpacketheadgrid that lets me use a grid to edit / enter data
mrpacketheadI am trying to replace what Microsoft access let me do
thumbsmrpackethead: pick a language you're comfortable with.
mrpacketheadPython works for me
mrpacketheadand i use it to access the db now
mrpacketheadI was just hoping that there was somethign more 'out of the box'
mrpacketheadi woudl have thought folks would have had this need for a long time
thumbsmrpackethead: it depends on the language you'll be using. Try asking #python
thumbsmrpackethead: you can use perl, php, etc as well. Just pick a language and ask their channel.
thumbsmrpackethead: you're welcome.
mrpacketheadjoin #workbench
Justin_THi all, I'm looking for some advice
mrpacketheadJustin_T, shot
mrpacketheadI can offer advice
mrpacketheadjust not sure on what topic
Justin_TI have a table for a voting system, where I would like to show the count of each vote for every user
Justin_TI have a separate table for the user and a vote table with the user_id and fbid (is using facebook accounts)
Justin_Tright now is doing a select count(id) for each user_id
Justin_Twhat do you think if I do a new table with the total for each user_id and write a trigger that updates that table each time someones vote, and with that I just run one query instead of multiples queries with count(id)
Justin_Tor if you have a better solution, I'm all ears
jeffrey3234Having some performance issues with a Java async web app/mysql backend, < most settings/info, could anyone help with some tuning?
jeffrey323430gb DB size, 127 tables
jeffrey323426gb of that is a table for Documents
jeffrey3234Endusers are experiencing delays upwards of 5 seconds
kegsterif i'm trying to import a dump but i'm getting collation/encoding errors -- is there a way to fix this? i know the database works but can i use the error to tell me what to send in a flag or something when using command line to import a 9gb file?
acro458SELECT * FROM table WHERE dateandtime > DATE_SUB(NOW(), INTERVAL 10 MINUTE) ORDER BY dateandtime;
acro458anyone know why this doesnt work?
acro458returns no results
acro458clearly there are results
acro458dateandtime is of type DATETIME
acro458select now(), DATE_SUB(NOW(), INTERVAL 10 minute);
acro458returns: 2017-03-05 22:13:26 2017-03-05 22:03:26
Justin_Tacro458, are you searching for future dates?
acro458when i add data to dateandtime i use the NOW() function too....
acro458I am looking for records in the last 10 min
acro458records greater than (current time minus 10 minutes)
acro458is how i believe the function works
acro458WHERE dateandtime > '2017-03-05 22:03:26'
acro458still nothing :/
Justin_Ttry WHERE dateandtime > (NOW() - INTERVAL 10 MINUTE)
acro458im retarded