spookahcan I log slow queries into my own database if i'm using a hosted server? without admin privs?
thumbsspookah: ask the admin to enable the slow query log for you.
spookahthumbs: ok, I was afraid of that =[ there is nothing I can do on my own?
thumbsspookah: log in your application.
spookahthumbs: ok, thanks.
dragonheartvi
thumbsdragonheart: vim
dragonheartalready aliased ;-)
thumbsalias vi=`kick dragonheart`
Ocnodyou could try SET GLOBAL slow_query_log = 'ON';
thumbsOcnod: that'll work really well without the root password.
Azundrishmmmmmmmmmmmmm
bizolosHi there. Does someone know how to downgrade mysql server to 5.5 on Ubuntu Xenial?
dragonheartto what?
bizolos5.7 to 5.5
dragonheartACTION jokes sqlite :-)
dragonheartdowngrades really aren't supprted between major versions. do a sql dump and re-import it
Azundris5.5? Is that even still legal?
dragonheartI'd suggest resolving your 5.7 difficulties would be much more productive in the long term
bizolosdragonheart, I don't bother about my data. I can erase all of it
bizolosI just want to install 5.5 on my current machine, for a specific project which does not use Docker
ss23bizolos: If 5.5 isn't in the Xenial repositories, youll have to install from source/scratch or find someone else willing to provide a repo for it
ss23bizolos: it's pretty much manual and up to you, as it's not a "supported" thing
wyoungdragonheart: sqlite had features mysql didnt a few years ago :)
wyoungstill nowhere near as complete as postgresql though
Ocnoddoes anyone find replication is unreliable ?
OcnodI would ideally like a master + master + slave setup
Ocnodthe first master being the main site, the second master being cron jobs and rough calculations and scripts that run in the background, and the third being for storing and copying backups
barmaglothello, i have fast growth table of searches, and i work collect analytics data from it. if table has many rows my queries freezes, how approach can i use?
archivistbarmaglot, analyse, use explain
barmaglothow many rows are consedered a great?
archivistmore than you need worry about
barmagloti need to rotate table probably ?
barmaglotbut if 500 mln ?
raddyHello Everybody
adschi
raddyIs there a documentation on what parameters can be inserted under mysqld_safe and what parameters should be above mysqld_safe in my.cnf ?
raddyI am getting lot of embarrasaments if my parameters turns to be inneffective for this trivial reason.
adscI guess you've read this page: https://dev.mysql.com/doc/refman/5.5/en/mysqld-safe.html ?
password2moin
password2is an INT not smaller than a varchar(50)?
password2ACTION scratches head
password2so using 'show table status from dbname;' the amount of rows differs from when I use 'select count(*) from tblname' Why is this?
jkavalikpassword2, varchar is variable, so there may iirc be 1 byte of length and one byte of data for 1byte charset 'a' etc.. but that would only happen if all the stored values were really short (and not sure what the variability overhead is)
jkavalikengine=innodb ?
password2jkavalik: the average length is 8chars
password2yeah InnoDB
jkavalik8 chars is 8b in ascii/latin etc, and 32b in utf8 + one byte length, should not be smaller than 4 bytes of int
password2i have both tables set to uf8
jkavalikabout the counts - in innodb the precise count is not stored, no easy way to actually get it in transactional system without counting and counting may take time, so what you see in show status etc are estimates based on index statistics (usually index dives), they should be around the same scale, but not exact
password2the avg_row_length in my two table does matter though
password2the one table show about 8000 extra rows with a total of 31000
password2so , eh more ot less :D
password2eh scratch that , the one show 8000 too few :D
jkavalikgetting anywhere between a half and a double the real value is "exact enough" for statistics ;)
password2ok
jkavalikanalyze table will change the numbers (but no guarantee for more precision)
password2I'll just assume my new table is better
password2:D
password2since it aint using a varchar(50) as a primary anymore
password2but an int
JavaLoverI was in here earlier and I was asking about database structure.
salleJavaLover: Not really. You were asking some weird questions
JavaLoversalle you're still here!!
JavaLoveryayyy
jkavalikpassword2, do you store the same data, just in a different format, or is the int a new column?
JavaLoverWell I was going to say that I get now that a pk is a good use for userid
password2yes :D
JavaLoverbut I was curious, if I'm storing images for example, is a PK also a good use for the photo id?
password2the format changed
password2theres no meaningful way to put A01BC1/00 in an int
archiviststore images in the filesystem and store its location in the table
JavaLoverI agree with that archivist
JavaLoverbut say the image belongs to a user
JavaLovernope nevermind
password2I'd say it depends on usage
salleJavaLover: Still same story. Do you have a car?
JavaLoverbecause if the image belongs to a user, then it would be attached based on id
JavaLoverI have a car salle this makes me happy
password2In sqlite i would not mind storing photos in the db as it helps portability
JavaLoverwe can finish our conversation from earlier :D
password2but in 99% of the cases you dont want to store large objects in db
salleJavaLover: Imagine someone tells you "I get now that car engine is a good use when I go shopping with my car!"
JavaLoverpassword2 i'd never store an image in a db
salleJavaLover: What are you going to tell him?
JavaLoverumm why are you shopping with your car?
password2JavaLover: I plan to in the near future
password2when my dynamic UI client is further developed
password2there is cases where its better to do so
JavaLoveroh okay I thought you were talking about what I was doing, suit yourself password2 hope it works out
JavaLoveri'm scared of that stuff but yeah enjoy
password2ofc it will
JavaLoverI thought salle had more to say to me
JavaLoverbut im just gonna ask, so if each photo is treated as an object that can have comments on it. And we have 2 seperate databases being used to store photos, how can you uniquely identify each photo?
x4u3nHello, good morning, i tried this query http://dpaste.com/246A41Z but i have no result, why?
sallex4u3n: =NULL
sallex4u3n: You can't compare NULL with =
x4u3nis null then?
sallex4u3n: Also it is not string so you can't compare it with 'NULL'
x4u3nok then is like oracle :P
x4u3nperfect now is ok, thank u salle.
JavaLoveranyone? using 2 databases to store links to images and treat them as objects that can have comments on them, how to keep the id of each photo unique?
salleJavaLover: What do you mean by "id of each photo"? If you mean primary key of a table it is unique by definition
JavaLoverokay so database A has a PK for photos, and database B also has a PK for photos, are these not overlapping?
btfJavaLover create a compound key including database host
JavaLoverhows that work btf?
password2very simple
password2but why are you working across databases?
password2do you mean tables?
JavaLoveri'm just planning ahead
btf+1 password2
JavaLoverthe databases I currently have are small as it is, and will most likely fill up
password2i have never needed to work across tables , but i guess that can happen
salleJavaLover: Primary key is unique within single table. It is not and does not have to be unique acrosss different tables. Once again you don't understand PK
JavaLoversalle but i'm asking how do I access
JavaLoverif I want to lookup a photo based on it's id
JavaLoverand i have 2 seperate locations where the id is the same...how to identify which one I want?
salleJavaLover: What is "photo id"?
JavaLoverI think btf answered this already but yeah
salleJavaLover: You didn't
password2SELECT file_path FROM table photos WHERE id= yourid ;
JavaLoverso the photo id is currently set to the primary key of the table
password2done
JavaLoverbut each database has the same id...
salleJavaLover: Nope. There is no such thing as "photo id" in databases. There is such thing as Primary Key
JavaLoveragain so on database A the PK is the same as on database B
jkavalikJavaLover, and?
JavaLoverso how to distinguish between them & get the right photo?
salleJavaLover: ?
salleJavaLover: What are you talking about?
JavaLoverhow to know like "oh you mean PK 5 on database B"
jkavalikJavaLover, the id is "namespaced" with the table it is defined in!
JavaLoverhmmmm
password2JavaLover: because you need to slect a db to run a qeury
jkavalikJavaLover, first thing - data are stored in tables, database is a "set of tables" (very simplified)
JavaLoverokay how to know which database to select password2 ?
jkavalikJavaLover, second - when you want to get a photo by its id, you already know that you are searching the photos only, not users or anything
password2JavaLover: by using 'USE dbname';
password2or select * from dbname.tblname
JavaLoverso for example, as a user I get a link that says mysite.com/photo/1234
password2you always have to explicitly specify it
JavaLoverhow do I understand "ohhh 1234 that means database B id 1234"
jkavalikyou dont write a service which you would ask "getMe(5)" and it would return user once and photo other time, you have "getMeUser(5)" and "getMePhoto(5)" and each of them returns a different entity altogether
password2JavaLover: I already gave you the answer
password2also toring the same data more than once is a verybad idea
password2even in different tables , across dbs its just a mess
salleJavaLover: Imagine you are librarian and you have to take care of 2 different librabries within your city. You can have same book in each library. What is the problem with that?
jkavalikJavaLover, you wrote it already - mysite.com/PHOTO/1234 means that you are only searching photos, and your application has to know which database and table are there stored in
salleJavaLover: If someone wants to get a book he first have to go to particular library to get it.
jkavalik*are these*
JavaLoverbut what if i have 2 databases with photos in them jkavalik?
password2you need to define the database before hand
jkavalikJavaLover, and why would you?
password2oh ffs i cant take this
JavaLoversalle so then a_1234 is the id?
salleJavaLover: What do you mean by id?
JavaLoverI just mean like mysite.com/photos/a_1234
JavaLoverwould definately help me to know "oh you mean database A"
salleJavaLover: Why are you talking about different databases?
JavaLoverbecause in my example, there are 2 databases
salleJavaLover: What is the problem if different databases store same information? They are *different* databases
JavaLoverhow do I know which database to check for the photo?
jkavalikbecause you are the one who decides that, if you are the programmer
salleJavaLover: If you have 2 cars how do you know which one you are driving today?
JavaLoveri see so again a_1234 is probably the answer
JavaLoverbecause b_1234 would mean database B
salleJavaLover: If you have 2 cars how do you know which one you are driving today?
JavaLoverI pick it...
jkavalikeither your application has to serve from two databases, then you need to make it so you can know - mysite.com/A/photo/1234 and mysite.com/B/photo/1234 for example
salleJavaLover: Exactly. Same with databases
JavaLoveryes jkavalik
JavaLoverokay makes sense
JavaLoverhave a way to identify which database the photo is on
JavaLoverif this is the case I really don't see the reason for having a uuid
salleJavaLover: Nonsense again
JavaLoverwell why would one need that? and why do sites like facebook use it?
salleJavaLover: Use what?
JavaLoveruuid
jkavalikuuid has nothing to do with selecting the proper database
JavaLoverwhat's it for then?
salleDefinitelty The Most Confused User Of The Year :)
jkavalikuuid is a way to generate unique identifiers - in normal db using autoincrement/generators there is one place where these are created, but sometimes you have services (maybe not even database related) which need to make sure the ID is unique not only locally but across all instances of similar services around the world, without them being directly connected
raddyadsc, Then what for not all parameters added under mysql_safe are not being recognized?
jkavaliksalle, might that be a badge in here? now it would be already worth it :) probably the bot does not support badges though..
adscraddy: I didn't understand that, sorry
JavaLoverjkavalik okay....
jkavalikraddy, my.cnf contains sections ([mysql] for the client, [mysqld] for the daemon etc), the configured variable needs to be under the right section (so after the right section marker and before the next different one)
sallejkavalik: Last night we went in circles when JavaLover was talking about uuid and the topic was like "Why should I prefer primary keys over uuids?" or similar nonsense
raddyjkavalik, Ohh okkk
raddyWhat is the difference between mysqld and mysqld_safe in the configuration file ?
jkavaliksalle, I can imagine that based on what I just saw, and some users accused you of not having enough patience iirc? :)
jkavalikraddy, mysqld is the server process, mysqld_safe is iirc just a wrapper script to configure mysqld on startup and start it again when it dies?
jkavalikso you are configuring different tools
raddyOhhhh
JavaLoverno salle is a good guy
JavaLoverand definately helped me to recognize how good PK are, i'm just worried about what happens when my database gets filled that's all
sallejkavalik: You remind me of people saying "I want my data to be safe so I use mysqld_safe instead of mysqld" :)
jkavaliksalle, good for them :)
salleJavaLover: I never told you how good PK are. They are neighter good nor bad. PK is integral part of each table.
no_gravityHello! When you have a database field x varchar(256) in utf8 you can put in strings that result in LENGTH(x) being bigger then 256.
no_gravitySo I guess the MySql limit is in chars but LENGTH returns the number of bytes?
salleJavaLover: Just like brakes of your car. You *can* drive a car without brakes, but you better don't do it.
JavaLoverI see
salleno_gravity: Correct. That's why there is also CHAR_LENGTH() function
salle!n no_gravity length
ubiquity_botno_gravity: See http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_length
salle!n no_gravity char_length
ubiquity_botno_gravity: See http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_char-length
no_gravitysalle: Nice, thanks!
salleno_gravity: SELECT LENGTH('бира'), CHAR_LENGTH('бира'); -> 8, 4
no_gravitysalle: I get 28,28
salleFor something else :)
no_gravitysalle: for your query
no_gravityMaybe the html interface translates the string into some encoding...
salleno_gravity: Nope. For what you copied from your IRC client which if not set to utf8 will display something else
no_gravityyeah, could be.
salleno_gravity: http://pastie.org/10914002
no_gravitysalle: Yup
no_gravityI'm still confused. How many bytes and chars can be stored in a VARCHAR(100) column?
flyingwhen is unsafe the statement replication guys?
salleno_gravity: Depends on character set
no_gravityutf-8
salleno_gravity: With 1-byte charsets such as latin1 char=byte
salleno_gravity: utf-8 uses 1-3 bytes per character so up to 300 bytes in VARCHAR(100)
salleno_gravity: utf8mb4 uses 1-4 bytes per character so up to 400. And so on
no_gravityhmm... ok... thats tricky
salleno_gravity: Unicode is flexible
no_gravityso you can store 100 "+" signs in a VARCHAR(100) but less "☃" signs.
salleno_gravity: Nope. you can store exactly 100 *chars*
no_gravitySo 100 "+" signs and 100 "☃" signs?
salleno_gravity: You can store 100 "+" signs or 100 "☃" signs. Or you can store 50 of each
salleno_gravity: Test it
no_gravityyeah... still something is strange here...
salleno_gravity: With smaller size column :)
salleno_gravity: VARCHAR(4) for example
no_gravitySo CHAR_LENGTH() on a VARCHAR(4) should never return more then 4, right?
salleno_gravity: Right.
salleno_gravity: You put restriction on column lenght in characters. It is logical this way. If the limit was in bytes it would be much more confusing
salleno_gravity: In VARCHAR(4) I want to store four-letter-words so I can store there word "beer" in various languages using different alphabets.
no_gravitySeems to hold true
_0xbadc0dewhat the hell does SELECT(73) do?
no_gravityIt selects 73.
barmaglothttp://pastebin.com/N3ZuXyn5
barmaglotwhy different datetime intervals may be use and not use index ?
_0xbadc0deyou mean an entry whose key is 73?
barmaglotdifferent day
_0xbadc0de?
_0xbadc0dePLZ HElP!
salle_0xbadc0de: SELECT 73; selects the number 73
sallebarmaglot: Pastebin the whole outpupt of EXPLAIN. It is type column that matters most
barmaglotsalle i lose data. try use INT(10)
barmaglotfor time column
sallebarmaglot: I have no idea what are you talking about
wyoungbarmaglot: unix time?
JavaLoversalle http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
JavaLoverjust wanted to point out...at some point what i'm talking about does apply
barmaglotwyoung yes, put it in int(10) field
sallebarmaglot: Why INT when there is TIMESTAMP?
swenssonIs it possible to see the users that are exist for mysql? did some changes yesterday, I don't remember what and now I can't access it xD
swenssonis timestamp an var?
sallebarmaglot: You can't compare INT directly to datetime values like in your pastebin
ss23select * from mysql.user; salle
ss23swensson*
salleswensson: SELECT user, host FROM mysql.user;
salleswensson: Note that user, host pair matters. This is what MySQL authorises
swensson*Without logging into mysql*
ss23swensson: Nope
swenssonIf Im connected to the server via ssh and try to access root, it's from localhost im trying to access it, right?
ss23Sometimes
ss23Could also be 127.0.0.1
ss23You can force MySQL to use one or the other by using -h127.0.0.1 or not
swenssonIf I call mysql -u root -p within the ssh.... My /etc/hosts have that mapped to localhost so that dosn't matter
ss23MySQL does not think localhost and 127.0.0.1 are the same, swensson
swenssonI did mysql_secure_installation yesterday and Now I don't have access xD...
ss23If your auth table has 127.0.0.1, it must be over TCP/IP, not a unix socket
ss23!t swensson reset root
ubiquity_botswensson: 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.
swenssonss23 Ahh! There it is I think, I set it on socket! (thought it were a better choise) that's maybe why I can't access it?
swenssonto socket*
swensson(in mysql_secure_installation)
swenssonss23 should I just add skip-grant-tables under [mysqid] ?
ss23Yeah, if you want to be able to log in again and reset the accounts
ss23You see how it says "add skip-grant-tables under [mysqld]"
ss23Like you said
ss23Then yeah, do that :P
swenssonI just put it on a new line but it failed unknown option:O
ss23What did you run to get that error, and where did that error come from?
swenssonsudo nano /etc/mysql/conf.d/mysql.cnf, (it only was 1 line and that was the [mysqlid] ) So on line 2 I added skip-grant-tables .No ; or anything behind it. systemctl start mysql, then I got that error in terminal
ss23weird, no idea sorry
swenssonokej, thanks anyways :p
swenssonI think I added an user and did some changes so root weren't allowed, or if I changed the password on root... I really gotta start documenting what I do-.-
swenssonYep, it's becuase of the sock..... argh bad call, sock or tcp/ip I choose socket becuase I thought I could add more security.. Yeah more secuirty, now I can't access it with the correct password /facepalm
swenssonIf I remove mysql package and then install it, will the db files vanish?
swenssonError: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ..... Can I change this in any .cfg file?
swenssonACTION sorry gonna stop spam now.... didn't start the service -.-
swenssonss23 sudo mysqld_safe --skip-grant-tables & worked for me
_0xbadc0deI have a select * from table where field like pattern
_0xbadc0deI want to encapsulate this in an if
_0xbadc0deto check if the outcome of this is not null
salle_0xbadc0de: SELECT * FROM ..; can not return NULL
salle_0xbadc0de: It returns all columns from table
ss23You could do an if count > 0, count, null or something, right
ss23It seems useless, but strictly speaking, you could probably jangle it done
_0xbadc0dewhat if there is no matches for that
_0xbadc0deit returns a table that has no rows?
_0xbadc0dehow can I check that?
jkavalikwhere do you want to check that? are you talking about sql or your application language?
_0xbadc0debtw
_0xbadc0deis this ok
_0xbadc0de... AND IF (1>0) THEN <stuff>; END IF;#
solarsquick question, if I have a query to find duplicates like SELECT property_id, channel_id, bs_key, count(*) as count FROM `ar_bidding_settings` group by property_id, channel_id, bs_key having count > 1 - how can I delete all except 1 entry?
jkavalik!t solars delete dupes
ubiquity_botsolars: If you have a unique ID and the name may contain duplicates then DELETE t1 FROM table1 AS t1 JOIN table1 AS t2 ON t1.id>t2.id AND t1.name=t2.name; If you have other fields that need to be taken into consideration extend the join as needed. If it fails on a myisam table see http://bugs.mysql.com/bug.php?id=28837
jkavaliksolars, take a backup and test it on non-production instance first ;)
solarshave to figure out how to adapt this query
jkavaliksolars, any auto_increment, date_created or similar column to decide which of the duplicated records should go away?
sallesolars: Use proper primary key and you will not have duplicates anymore
solarsDELETE t1 FROM ar_bidding_settings_test AS t1 JOIN ar_bidding_settings_test AS t2 ON t1.id > t2.id AND t1.property_id = t2.property_id AND t1.channel_id = t2.channel_id AND t1.bs_key = t2.bs_key ;
solarsshould do it
sallesolars: If you don't want such duplicates then you should have PRIMARY KEY(property_id, channel_id, bs_key)
solarssalle, we have added a unique index now
solarsit's only a dev database, but I tested it before
solarsseems to work
barmaglothttps://drive.google.com/open?id=0B0wOPQIbMdatc0N3cTBqdVZONDg
barmaglotwhy mysql not use index? and viewed rows = all rows
smerzyoure using a function BETWEEN. try to get rid of that. afaik there is a way to do this and use the index
smerzbut it'll be certainly without a function like BETWEEN. function in where == not using index
jkavalikBETWEEN is not a function, but a construct which can use indexes
smerzah
jkavalikbarmaglot, "show create table stat;"
jkavalikbarmaglot, how many rows are there and how many of them match that day?
jkavalik!t barmaglot 235959
ubiquity_botbarmaglot: http://code.openark.org/blog/mysql/235959-is-not-the-end-of-the-day-no-really
barmaglothttps://drive.google.com/open?id=0B0wOPQIbMdatakxPTGZBZ2VMcVE
barmaglotjkavalik aa.. all rows :)
jkavalikweird.. in the explain the "possible keys" column shows "at" but the index is names "stat_at"..
jkavalikyep, index wont be used unless it filters significantly - not with * anyway
_0xbadc0dewhen I do a SELECT COUNT(*) FROM pet;
_0xbadc0deI get a table with one row wich is the count
_0xbadc0dehow can I get the actual number, so that I can do stuff like if (thisnumber>2, ....
jkavalik_0xbadc0de, where do you want to process that number? stored procedure?
jkavalik!t _0xbadc0de select into
jkavalik!m _0xbadc0de select into
ubiquity_bot_0xbadc0de: See http://dev.mysql.com/doc/refman/5.7/en/select-into.html
barmaglotweird.. in the explain the "possible keys" column shows "at" but the index is names "stat_at". == i rename it
salle_0xbadc0de: What do you mean by actual number?
salle_0xbadc0de: SELECT COUNT(*) FROM pet; gives you table with 1 row and 1 column which contains the actual number.
_0xbadc0decan I use it as a a result for a comparison?
_0xbadc0dei.e.
_0xbadc0decan i do
barmagloti will be use next queries http://pastebin.com/Xvz7bqa0 , i think that only index on field at is necessarily
_0xbadc0deif((SELECT COUNT(*) FROM pet)>0, <...>, formatC);
x4u3ndo a subselect assigned.
barmaglotare u agree ?
salle_0xbadc0de: Answer jkavalik please.
salle_0xbadc0de: He repeatedly asked you where do you want to process that number.
jkavalikbarmaglot, " date(at) between .." date() IS a function so index won't be used there
barmaglotbut how can i select range of dates ?
barmaglotseveral queries ?
jkavalikbarmaglot, "where at >= dayA and at < dayB+1"
barmaglothow can i google it ?
jkavalikbarmaglot, you can compare datetime to date, the date will get extended with 00:00:00 to datetime
barmaglotthanks
barmaglotbut i can`t find chapter about operators working with index
jkavalikbarmaglot, https://en.wikipedia.org/wiki/Sargable
barmaglotSargable operators: =, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL, EXISTS
jkavalikbarmaglot, use it as a base for searches ;)
barmaglotaa date() IS a function so index won't be used there i read incorrect
barmaglotbut i don`t use date() now
barmaglotexplain SELECT SQL_NO_CACHE resource, AVG(response_time) `avg_time` FROM `stat` WHERE source = 3 and ( at >= '2016-07-21' and at <= '2016-07-21 23:59:59' ) GROUP BY resource
barmaglotpossible keys stat_at, but key and key_len is NULL
barmaglotin explain
sallebarmaglot: Can you paste somewhere the full result of EXPLAIN and SHOW CREATE TABLE?
sallebarmaglot: Not as picture. It is plain text
barmaglotaa sorry, explain SELECT SQL_NO_CACHE at FROM `stat` WHERE ( at >= '2016-07-21 00:00:00' and at <= '2016-07-21 23:59:59' )
barmaglotit`s use index
jkavalikbarmaglot, use "at < '2016-07-22'" instead "at <= '2016-07-21 23:59:59'" to not loose the last second of a day with sub-second precision
barmaglotokay!
jkavalikbarmaglot, you previously said that all the rows are from 2016-07-21 so there is no use in the index for these queries, the last one shown uses full index scan only because the index is covering
barmaglotokay, i understatnd
barmaglothttps://drive.google.com/open?id=0B0wOPQIbMdatR2d3YnZCUUxTNWc
barmaglotall rows 482 000
barmaglotit`s good result ?
barmagloti use https://drive.google.com/open?id=0B0wOPQIbMdateE5fZUV2MGRWd0U index
Eburituswhile determining length of vchar, should i put 64 or 63?
Eburitusi mean. does 0 count?
jkavalikbarmaglot, can't see the condition on cheap_price in your screen, try using pastebin/pastie for these
Eburitusif i put 0 to length, can i put anything to variable?
jkavalikEburitus, how doe a string with length 0 look like?
jkavalik*does
Eburitusso i can't write anything to length 0 vchar?
jkavalikprobably only ''
jkavalikit is the length = size, not index
Eburitusroger.
jkavalikEburitus, i is quite easy to actually test - http://pastebin.com/BGLjeWr4
barmaglotjkavalik cheap_price is not null
jkavalikbarmaglot, one important note - indexes are not as useful if your queries are not limiting the data - if you had data for entire year and picked one day then indexes are good, but if you need to read data from the entire table, index cannot help much
jkavalik(but of course there are other ways they can help, ordering for example)
barmaglotjkavalik all my queries are limited by at column (datetime)
salleEburitus: You can actually put "something" in CHAR(0) and that's emtpy string
salleEburitus: If the column is NULLable this allows you distinguish between NULL and NOT NULL with otherwise "empty" column
ImInYourWifeis it better to use built in SQL data parsing with INFILE and FIELDS TERMINATED or to use awk or such other built in unix tools
salleImInYourWife: Define "better"
ImInYourWifeeg: less mem usage, less hdd usage, plays nicer
ImInYourWifeetc
salleImInYourWife: If you want to do complex stuff and you are fluent with awk and other such tools it will be easier to parse the text file
ImInYourWifewell im running a syntax with awk like this cat media.csv | perl -i -pe 's/""/NULL/g' | awk -F',' '{ printf "INSERT INTO `media` VALUES (\x27%s\x27,\x27%s\x27,\x27%s\x27,\x27%s\x27,\x27%s\x27,\x27%s\x27,\x27%s\x27,\x27%s\x27);",$1,$2,$3,$4,$5,$6,$7,$8;print ""}' | perl -i -pe "s/'NULL'/NULL/g" > 17.media\ database.sql
ImInYourWifebut when importing it i will usually get such an error such as ERROR 1064 (42000) at line 1254: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ']','fde0b12d14f8b96a2cc1e04edbc1b06a',NULL,NULL,NULL,NULL,'IOS',NULL); INSERT IN' at line 1
ImInYourWifewhich as annoying it is to edit it manually, i dont really think mysql will make a difference salle
salleImInYourWife: I am illiterate when it comes to awk :)
ImInYourWifesame
ImInYourWifei just searched and found this http://www.harecoded.com/converting-csv-to-sql-using-1-line-in-bash-2275563
ImInYourWifemodifed it as needed
ImInYourWifeused some regex to change some strings
ImInYourWifeand these filses are like several million lines, so it gets annoying manually modifying them
barmaglotjkavalik how can i calculate index keys length ?
barmaglotexlpain return key_len 12, i want compare it with my index length
jkavalik!m barmaglot storage requirements
ubiquity_botbarmaglot: See http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html
jkavalik12 would be probably int (4) + datetime (8) are being used directly in that index
barmaglotand also.. group by very slow my request..
barmaglotindexing gruo by field does not help..
barmaglotit`s normally?
jkavalikgroup by what? each query is different and may need different indexes
jkavalik*different index
barmaglotSELECT SQL_NO_CACHE at FROM `stat` WHERE at >= '2016-07-19' and at < '2016-07-22' and source=3 and cheap_price is not null group by resource LIMIT 60 https://drive.google.com/open?id=0B0wOPQIbMdatakxPTGZBZ2VMcVE
barmaglothttps://drive.google.com/open?id=0B0wOPQIbMdatakxPTGZBZ2VMcVE
jkavalikmysql can use only one index per table in a query so adding another index on the grouping column won't help
barmaglotw/0 group by resource - very fast
ImInYourWifewud
ImInYourWifeadminer says 220k rows, but when i try to view the rows while importing with LOAD DATA, INFILE and FIELDS TERMINATED it shows nothing
jkavaliklimit without order by should not be used
barmaglotjkavalik i think, so i use constraint index
barmaglotcomposite*
jkavalikbarmaglot, well, the screenshot of the create table statement is old, not containing the new indexes at all..
barmaglotjkavalik i write query by parts
jkavalik!t barmaglot index order
ubiquity_botbarmaglot: According to our chadmaynard (don't say his name), indexing order is: join, where, sorts, group by
jkavalikyou may write query by parts, but when optimizing, you need to take the entire final query into account, optimizing the parts usually does not work for the complete query
barmaglotthanks
jkavalikImInYourWife, load data hopefully runs inside a transaction so you won't see the rows until it commits
jkavalik(or you could try setting your select to read uncommited)
ImInYourWifeokay
ImInYourWifeso i just need to wait until the import is done
daumhey guys - if i have a mysql text column, which has differing length texts, and i want to create an index on it so I can do look ups quicker on its value, however i'm not sure what length i should put in
jkavalikdaum, text or (var)char ?
daumjkavalik, text
jkavalikdaum, maybe you want fulltext searches?
daumi'm not looking to do substring searches, just actual matches
jkavalikdaum, what is maximal length of the text?
daumjkavalik, i thought about that, would need to flip the table from innodb to myisam which isn't the end of the world
jkavalik(exact match on few kilobytes would sound weird)
daumjkavalik, that's the issue, the table is really a translations table, so the column is usually fairly short, however sometimes if someone left a long response, could be a decent size
jkavalik5.6 supports fulltext in innodb
daumhm let me see what version of mysql they are on sec
daumah, perfect 5.6.19b
jkavalikdaum, well, you can create a prefix index on text column, it wont always give one exact match, because the longer values will have to be checked explicitly, but it will work
daumjkavalik, ah so optimize for majority of the cases and leave the outliers?
jkavalikyou don't want fulltext for exact matches :)
daumthis is really just for admins when they need to find a translation key
jkavalikyep, use such a length of the indexed prefix that you get less than 2 rows on average for each if possible (or some relatively small number if there are really lot of them/they are not unique)
ImInYourWifenow imported 28,052,322 rows.... all seems... not broken
daumjkavalik, sounds good, thanks for the advice!
jkavalikdaum, do you know the average length?
daumthe index length is that based on bytes or characters?
daumnot yet, but can query and calculate it
jkavalikdaum, characters - if it were bytes, multibyte characters would get spliced and invalid
ImInYourWifethe built in mysql file import with field determined is a lot faster than awk salle
daumpoint well made!
barmaglotSELECT SQL_NO_CACHE at, count(*) count FROM `stat` WHERE at >= '2016-07-19' and at < '2016-07-22' and source=3 and cheap_price is not null group by resource order by count desc limit 60
barmaglothttps://drive.google.com/open?id=0B0wOPQIbMdatXy1lV0E3V1Y4YjQ
jkavalikdaum, I would probably use the prefix length a bit shorter than the average length as it can take most the small ones (maybe find a length which wold cover ~90% of rows) and the longer outliers are really not a problem, there should imho be no noticeable slowdown on them
barmaglot0.8 sec query time on table with 1 mln rows
ImInYourWifenot really looked into it too much, what are the differences between InnoDB and MyISAM?
barmaglotit`s okay and not growth quickly on 10-100 mln ?
daumjkavalik, yep will do that
tjzhello guys
jkavalikbarmaglot, try (source, resource, at, cheap_price) if the date range is not selective, that will allow the group by to be optimized by the index and should get rid of a filesort
salleImInYourWife: Huge :)
jkavalik*no, filesort will stay because of the orderby..
salleImInYourWife: You can write a big book about the difference between InnoDB and MyISAM
ImInYourWifeokay
ImInYourWifewhat are the major differences
ImInYourWifeeg: space, time, speed
ImInYourWifeetc
salleImInYourWife: Transactions
ImInYourWife*not going crazy indepth*
barmaglotjkavalik what is rid ?
salleImInYourWife: For most kind of applications you better stay with InnoDB
jkavalikwell, myisam and innodb store data of db tables, that is probably what they have in common..
sallejkavalik: Same column types too
sallejkavalik: And that's pretty much it ..
jkavaliksalle, not sure about the types, aren't some of them stored differently too?
barmaglotjkavalik it is test data, and dates will be different
ImInYourWifeokay
ImInYourWifeis there a major speed for searching/queries such as select and space used between InnoDB and MyISAM
ImInYourWifethats all i really care about currently
jkavalikbarmaglot, then you need to test on the real (or at least realistically distributed) data to see what really is better
jkavalikbarmaglot, "get rid" - "remove"
salleImInYourWife: Yes, but the difference depends on how exactly are you using them
ImInYourWifeusing SELECT queries a lot and importing large files into it
jkavalik!t ImInYourWife about myisam
ubiquity_botImInYourWife: a malfunctioning form of blackhole engine that sometimes accidentally stores data.
salleImInYourWife: InnoDB usually needs lot more disk space and RAM, but can be much faster in general with highly concurrent load
ImInYourWife"accidentally stores data"
ImInYourWifesalle: how big a space difference
ImInYourWifeQuery OK, 28,052,321 rows affected, 2077 warnings (12 min 27.94 sec) that ended a lot quicker that using awk salle
salleImInYourWife: This is like comparing apples to oranges.
ImInYourWifebut i'd have to format it and fix bugs and then import
tjzi need assistance with calculating sum of unique row
tjzscenario as follow:
tjzhttp://paste.linux.chat/view/36a8d171
ImInYourWifesalle: hmmmm
ImInYourWifenot sure how to really.... hmm
barmaglotfinally query is .. explain SELECT SQL_NO_CACHE resource,from_code,to_code,AVG(cheap_price) avg_price, count(*) count FROM `stat` WHERE source = 3 and ( at >= '2016-07-19' and at < '2016-07-22' ) GROUP BY resource,from_code,to_code ORDER BY count DESC LIMIT 60
jkavaliktjz, do all the rows with the same ip have the same fee? if not which fee you want to take into account?
tjzhey jkavalik
tjzjkavalik, no. fee vary
jkavalikbarmaglot, again a different index is needed for that one :)
tjzeg. id 112, the total fee for these 3 rows should be 0.23+0.23+0.6
jkavaliktjz, and how do you decide it is 0.6 and not 0.23 for 1.2.3.4 ?
tjzit is actually a 'log' table
barmaglotjkavalik what in your opinion:)
tjzbased entry on the 'log' , i will to do statistic
jkavalikbarmaglot, I would try (source, resource, from_code, to_code, .. the rest here or not at all ...), but if the "at" gets more important in your real data then (source, at, ..maybe the rest..) might be better
jkavaliktjz, that does not answer my question :)
tjzjkavalik, just trying to calculate based on the data found in those entries
jkavaliktjz, answer would be "the earliest one", "the biggest fee" or something similar, which is deterministic and can be derived directly from he data
jkavaliktjz, still, there are two rows for 112-1.2.3.4 with fee values 0.23 and 0.6 - why did you decide 0.6 is the right one? what is the reason?
tjzinitially, i tried to use : select DISTINCT remote_addr from table where id="112"
tjzto get unique value of remote_addr
tjzremoving duplicate row
ImInYourWiferemote_addr in php to get a users ip address tjz?
tjzin my case 'remote_addr' is a table field
tjz:)
ImInYourWifewait, nevermind i was thinking of the server variable for ip addresses
tjz select DISTINCT ip from table where id="112"
ImInYourWifei was gonna point out to you that it is pretty easily spoofable
tjzit will return unique row of 'ip'
tjzi like to get the fee of these unique row of 'ip' as well
tjzand do a 'sum' of these fee
jkavaliktjz, does not matter much, there are ways to select unique rows with specific properties, but you need to be able to specify the properties
jkavalik!t tjz groupwise max
ubiquity_bottjz: http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html
tjznot looking for maximum value
tjzbut rather SUM of column'fee'
jkavaliktjz, but WHICH fee when there are multiple?
jkavalikor do you want to sum all the fees of one IP ?
tjzsum all the fees of one IP
tjzwait a minute
jkavaliktjz, then that does not match ">tjz< eg. id 112, the total fee for these 3 rows should be 0.23+0.23+0.6"
tjzno sum all the fees of one IP
tjzno --> sum all the fees of one IP
tjzmy objective is still --> id 112, the total fee for these 3 unique ip rows should be 0.23+0.23+0.6"
tjzreturn $1.06
jkavaliktjz, and my question is still "why 0.23+0.23+0.6 and not 0.23+0.23+0.23" when there is a row with a fee of 0.23 for 1.2.3.4
tjzok
jkavalikyou picked 0.6 instead of 0.23 and you probably have some reason for that, thats what I am asking ;)
thumbsbecause 0.6 > 0.23, maybe?
jkavalikmay be, but tjz has to know to be sure it is right :)
tjzit should be:
tjz0.23+0.23+0.23+0.6
jkavalikthen that is just a sum() and a group by
tjzhow about for id 113 ?
tjzit should be 0.6+0.5 only and not 0.6+0.5+0.5
tjzit should be 0.6+0.5 only and not 0.6+0.5+0.5+0.5
jkavaliktjz, why?
jkavalikdoes not make sense to me..
jkavalikwhen you buy 3 tickets, for $1, $3 and $1, do you only count 4 in your accounting?
tjzmy objective is to calculate fee based on: unique ip per day for particular id
jkavalikso the duplicit fee shows it was not unique?
jkavalikbut if fee differs then the same ip is not duplicate anymore?
jkavalikif that is what you really want..
tjzeasier to visualise:
jkavaliktjz, http://sqlfiddle.com/#!9/55f19/2
tjzhttp://paste.linux.chat/view/b2a44056
jkavaliktjz, or "select distinct fee, id, ip .." in the subquery and sum() group by id in the outer query
ImInYourWifenothing better than importing 41,001,172 rows into mysql
tjzi realise it is more complicated than i initially thought
jkavalikImInYourWife, if you use innodb, the awk script should wrap say 100 inserts in one begin; ... commit; block to make it faster too, but if load data works for you
jkavaliktjz, a bit. did you check the sqlfiddle?
tjzjkavalik, yes. but i will need time to understand the join
ImInYourWifejkavalik: i dont directly pipe into mysql
tjzwhat 41,001,172 rows
tjzare these log?
ImInYourWifei use something like this ""LOAD DATA LOCAL INFILE '/run/media/pm/Media/bla.csv' INTO TABLE `media` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
ImInYourWifetjz: not at all
tjzmy 'log' table might become as huge as that in near future
ImInYourWifea data dump im formating
jkavaliktjz, there is no join in the query I posted
ImInYourWifeeverything im importing so far is data dumps
ImInYourWifefrom some scraping i've been doing
ImInYourWifethat i wanna format
barmaglotjkavalik SELECT SQL_NO_CACHE resource,from_code,to_code,AVG(cheap_price) avg_price, count(*) count FROM `stat` WHERE source = 3 and ( at >= '2016-07-19' and at < '2016-07-22' ) GROUP BY resource,from_code,to_code ORDER BY count DESC LIMIT 60
barmaglothttps://drive.google.com/open?id=0B0wOPQIbMdateWk4NElQcGp2STg
barmaglothttps://drive.google.com/open?id=0B0wOPQIbMdatMzBjMWQ0VG5PSlk
barmaglotindex key_len 1...
barmagloti think that refuse group by
jkavalikbarmaglot, what type is source?
barmaglottinyint
jkavalikbarmaglot, and you would have to put all the columns used in group by before "at" in the index
jkavalik(and in the right order)
barmaglothttps://drive.google.com/open?id=0B0wOPQIbMdatbzdySGRBY1JESVU
barmaglotkey_len also 1
Gorkymanhey guys... is there a way to use lower/higher comparison on string columns? like >= "AB1002" AND < "ABC00"
jkavalikbarmaglot, " GROUP BY resource,from_code,to_code" means these 3 columns have to be together in the index definition
jkavalik(source, resource, from_code, to_code, ...)
barmaglotaa..
barmaglothttps://drive.google.com/open?id=0B0wOPQIbMdatcjFiZXJTV3lMTXc
barmaglotno.. index type ref and key_len 1 also.
tjzjkavalik: i went back and re-read your code and result. it is what i wanted
skywaterhi, if i want to select data with a value containing a ? how to select? SELECT * FROM table where password = ´434?´ gives me no result while there is a password column containing passwords with 434?
tjzis 'group by' the same as DISTINCT ?
Vacuityskywater: a) do you get an error or really an empty result? b) do your _really_ store passwords in plaintext?
tjzjkavalik, thanks you for the help. really appreciate it. hope you have a nice day ahead
ImInYourWifeis it possible to pipe text to mysql to LOAD DATA/FIELD TERMINATED and put in a table, but the input be STDIN?
barmaglotcount rows with group by is slow.. i create INDEX _id and count it but no effect
barmaglotany ideas ?
JediMasterHi guys, I'm trying to run mysqldump but it seems to be producing odd results (not one of our servers). It's producing "INSERT IGNORE INTO `tablename`...." without actually doing the CREATE TABLE `tablename`, but only for certain tables, others it works fine for. Any ideas why it's not creating all tables?
JediMasterThis is with standard mysqldump --opt
JediMasterjust to add to this it's running MySQL 5.5.48 and I'm importing into 5.7.11
JediMasterbut the output from 5.5.48's mysqldump looks broken
barmaglothow can i order by SELECT SQL_NO_CACHE count(id) count, resource,from_code,to_code FROM `stat` WHERE source=3 GROUP BY resource,from_code,to_code
barmaglotorder by calculated field very slow
JediMasterI've verified that SHOW CREATE TABLE `tablename` works with the tables causing the problems, I wonder why mysqldump isn't doing it
jabberwockHi. I am attempting to install MySQL Server 5.5 on Win7 and I am getting an error when it attempts to apply security settings. Access denied for root@localhost
jabberwockHas anyone else experienced this?
jabberwockThis is a fresh Win7 VM and a first attempt install
passagejabberwock: 5.7 uses a random root password and stores it in a local config file for you to use. I dk where that file is in windows. google for it
passageor !t jabberwock about reset root
passage!t jabberwock about reset root
ubiquity_botjabberwock: 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.
jabberwockAt this point there is no my.cnf or my.ini. There are my-small etc.
jabberwockShould I copy one of those to my.ini or cnf?
passagejabberwock: *.cnf is for *nix, *.ini is for windoes
jabberwockIs this info in the install instructions? If so I missed where they are. Will look
passagejabberwock: *.cnf is for *nix, *.ini is for windows
jabberwockThanks. I was thinking the opposite
passagejabberwock: you have to follow the above steps in exactly that order. the FLUSH PRIVILEGES has to happen where it does
jabberwockThe installer also says "C:\ProgramData\MySQL\MySQL Server 5.5" already exists but it doesn't
jabberwockthanks. Seem arcane for a first-time install
passagejabberwock: you have exhausted my windows knowledge. good luck
jabberwockUnderstood and thank you.
jabberwockpassage: This is odd. I changed the name of the install directory (even though the previous one did not exist) and the install worked
passageit's a mystery :-)
jabberwocklol yep
vinkyeh what? this shouldnt happen, right? http://sqlfiddle.com/#!9/51520/9/0
passagejabberwock: methinks this points a guilty finger at the registry, if it stills uses that
jabberwockThat makes sense since the path was definitely not there
jscatalaHello, yesterday i try to restore a db with frm files. now im getting in the logs that native table 'performance'.* has wrong structure. but at the end i get access denied for user 'root'@'localhost'. I've try to start manually with skip grant tables but does not start. I'm clue less because i cannot access to the db in to try to solve either problem
archivistjscatala, you cannot restore a table by directly copying ,frm only
jscatalaarchivist: even if i do have the ib_logfie and the ibdata?
thumbsjscatala: I sense that there is more to this story that you're not telling us.
jscatalathumbs: archivist the whole story is, i got the frm, ibdata and iblog files. create new datadir, change mysqld datadir conf, add the path to apparmour and boom... no more no less
thumbsjscatala: you need to consider the innodb log file size from the old my.cnf too
archivistjscatala, have you also changed mysql versions
jscatalathumbs: yup i consider that too. But beside that, do i need to have the same settings like user or other setting?
jscatalaarchivist: sadly i dont know the previous version.
ImInYourWifeis it possible to move entire MySQL databases to another drive?
ImInYourWifein Linux
archivist'performance'.* has wrong structure coule be a symptom of incorrect mysql version for that data
archivistjscatala, may be fixed running the upgrade script
thumbsImInYourWife: yes.
ImInYourWifethumbs: how could i do that?
jscatalaarchivist: seems like it, but when i try to access i cannot due the block of root user
jscatalaarchivist: so, when i try to run with skip tables, mysql does not start and does not throw any error to error log
thumbsjscatala: what version of MySQL was that from? Was mysqld stopped when the file was copied?
thumbsjscatala: and how large is the ibdata1 file?
jscatalathumbs: i can ask wich version was the previous db but dont know if they will remember if they stop the db. the ibdata1 file is around 60Gb
YeppI imported lists of cities, provinces, etc from an sql I found. They are all in uppercase. How can I update all values to be Ucword() like?
SAiFhey. I need to know if there is a way to get data from mariadb in xml format?
passageSAiF: mysqldump has an --xml option
thumbsSAiF: cross-posting is also terrible.
SAiFoops.. sorry thumbs
SAiFI posted the same question in 3 channels . :|.. sorry
Azundrismalongo, ichi ba
zechariahHey guys, why would this query work: http://hastebin.com/eyocihorur.sql, but adding the addition of: AND DATE(created) <= DATE(NOW() - INTERVAL 62 DAY) cause it to stop working? (there are definitely records in that timespan) .. am i overlooking something silly? thanks!
Ocnodcan you do that?
OcnodI usually do DATE(DATE_ADD(NOW(), INTERVAL +62 DAY))
Ocnodzechariah : You are selecting where DATE <= 2016-05-20 AND DATE >= 2016-06-19
OcnodIt can't be both
Ocnodhttp://hastebin.com/senometete.sql
zechariahd'oh.. long day, so something like... AND DATE(created) between DATE(NOW() - INTERVAL 2 MONTH) and DATE(NOW() - INTERVAL 1 MONTH)
zechariahah, thanks hehe.. i thought maybe i was doing something stupid with the date function i wasn't catching.. thanks for the second pair of eyes/assist m8.
Ocnod:)
SAiFhow can I use >mysql --xml or similar in workbench?
ruben23hi guys anyone can help with this error somehow ------> http://pastebin.com/sfyDfFuD
lamnethruben23: the page has been removed!
ruben23lamneth: what can i do..? you mean the table is deleted..? or just corrupted..?
lamnethruben23: the link you gave (pastebin)
lamnethruben23: there now!
lamnethruben23: are the physical files still there?
hdonhi all :) do i have to worry about adjusting the max fd's available to mysqld if i'm going to have a lot of tables? this is for wordpress multisite
hdonwhich, for whatever reason, uses separate tables for every sub-site, instead of adding another column to each table...
hdoni guess either way is an option with different advantages/disadvantages to each..
domashdon: not if you use innodb_file_per_table=0 :)
jeffrey_fWho here has used mysql-connector with python/?
hdondomas: i think we like having one file per table
thumbshdon: that's fine for many cases. For 30k tables, not so much.
thumbsgranted, I'm not sure how many tables you'll have in the end.
b1tchcakeshi. is this a spot I can ask a question with help on modeling data in mysql?
thumbsb1tchcakes: yes
b1tchcakeswe have a table that contains about 12 million rows, each row is a person. The table has about 30 columns (age, address, phone, income, etc) -- mostly attribute data that really can't be normalized out. the issue is that we need to be able to run queries that include searching ANY of the columns, and unless I indexed all the columns, this slows down dramatically with non-indexed fields included in the search. yet, we do need to be
b1tchcakes able to search across all 25 or so columns. So I'm wondering, do I index ALL the columns in the DB or do I try to move towards a column store db.
dragonheartb1tchcakes: it does look a lot more like a problem column store will solve better. having said that I haven't used those at all yet.
wyoungMornin'
lamnethwyoung: Hi
stacks88i have a site where the website and mysql server are running on the same machine, located in france. im in EST time zone, so france is 6 hours ahead. so when new entries were added into the database, the timestamp was always france timestamp, 6 hours ahead. Now, i've gone ahead and moved the website and mysql to a new machine located in EST timezone, in canada.. now the website is up and running
stacks88again everything is working good. The problem is that when I add a new entry to the database as I have in the past, the timestamp is still france time, in the future.. 6 hours ahead.. but when i run SELECT NOW(),CURDATE(),CURTIME(); i get the current EST time.. which is the same as the time on the machine whe I run the linux command 'date' -- how can I make it so that when new entries are added
stacks88, it doesnt keep setting the time/date_added to 6 hours ahead ?
stacks88How i moved the mysql server was mysqldump --lock-all-tables -u root -p --all-databases > dump.sql -- then on the new machine (in EST timezone) I ran mysql < dump.sql
stacks88There is only one main database, but i was fine with the credentials/users being the same, so i went ahead and did all databases so that i could preserve the users/info/hosts etc
stacks88am i out of luck or is there some way to make it so that it starts utilizing the EST timestamp
stacks88Damn it looks like i should have dumped the database with skip utz or something like that
_August_Can someone take a quick look at my sql? im trying to use databinding and add a like onto my where statement but i keep getting an error back http://pastebin.com/JCDxG3Aw
dragonheartstacks88: what type is the column. look carefully at the manual for what that type means with respect timezones
dragonheart_August_: show the exact error
dragonheartand your WHERE criteria should be part of your JOINs
stacks88dragonheart: when you say what type is the column, do you mean where it says Definition: created_at timestamp
stacks88ive got created_at, updated_at both say timestamp
dragonheartand what does the manual say about timestamps with regard to timezones?
dragonheart!m stacks88 timestamp
ubiquity_botstacks88: See http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
stacks88dragonheart: i guess im just not getting it, based on reading that link. ive gathered that it sounds like its auto updating based on the old timestamp or something like that. this is what ive got http://i.imgur.com/PNCQoEW.png where it looks like its already using CURRENT_TIMESTAMP , so i dont understand 1) what im suppose to do at this point, 2) how to get the created_at value to show the
stacks88current timestamp, not 6 hours ahead.. my system_time_zone is showign as EDT, time_zone is SYSTEM..
stacks88I think the reason why im having this problem is because on the old server in france, I ran: mysqldump --lock-all-tables -u root -p --all-databases > dump.sql -- then on the new server that is in EST timezone, I ran mysql < dump.sql -- so im thinking that somehow i imported the france utc timestamp.. because on the new server in EST timezone, I just ran: SELECT UTC_TIMESTAMP(); and got 2016-07-22
stacks8801:21:01 .. but yet NOW() is 2016-07-21 21:23:23
stacks88so i guess maybe i just need to change the utc timestamp or something? i dont know, im not an expert
stacks88when I run SELECT unix_timestamp(); it shows 1469151424 which is matching to the system time, but the UTC_TIMESTAMP is 4 hours ahead, select unix_timestamp(UTC_TIMESTAMP()); gave me 1469165841 - so it sounds like i just need to somehow force the UTC_TIMESTAMP to be reflective of the actual timezone the server is in, the system time, EST timezone?
passagestacks88: timestamp stores the time internally as UTC. so, when the data was inserted/updated, the local time doing the insert/update is converted to UTC and store. you've imported that UTC time, and when you select, it is converted to your local time. (I'm using local time to reflect the timezone used by the server as system time).
passagestacks88: so it is all doing what it is supposed to do.
passagestacks88: "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval." http://dev.mysql.com/doc/refman/5.7/en/datetime.html
passagestacks88: when that value was inserted/updated in France, what was stored was the UTC conversion from that current time zone. that UTC value is valid, and converted to your current time zone. why would you want to change it? that was when the insert/update occurred
stacks88passage: so when I add a new entry, and the created_at value is 6 hours ahead, what should I do?
passagestacks88: when you insert/update a value, your current timezone will be converted to UTC and stored. it will reflect when the actual insert/update occurred
passagestacks88: show variables like '%zone%';
passagethat is the value that will be converted to UTC
passagestacks88: the time stored is a valid value, and reflects the difference between actual UTC value when the last value was inserted/updated in France.
passagestacks88: there is an actual difference
stacks88system_time_zone = EDT
stacks88time_zone = SYSTEM
stacks88So you're saying that my created_at value showing as 6 hours in the future is valid ?
passagestacks88: your timezone is in the past as compared to the one in France, yes.
passagestacks88: if you have a good reason to try to pretend that those inserts/updates were done in your time zone, and not the one in France, you can use convert_tz to update them
passagestacks88: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz
stacks88passage: my situation is that i just need *new entries* from here on out, to show the the local current time, for created_at value
stacks88there is no dispute about old entries. the server is not in france anymore, so any new entries are still showing the france time, which is what im trying to change.. becuase the system_time_zone is EDT and time_zone is SYSTEM
sallestacks88: Set your desired time_zone then.
stacks88but the time_zone is SYSTEM and when i run 'date' on the system (linux command), its accurate. or if i run select NOW(); it shows the sytem time
passagestacks88: the new entries will be converted from your local time zone to UTC and stored. when retried, they will be converted from UTC to your local time zone
passagewhen retrieved*
stacks88passage: so are you saying that new entries, the created_at value should not show 6 hours in the future? if my local time zone is SYSTEM and EDT ?
passagestacks88: I am saying hte exact opposite of that
stacks88hm ok..
passagestacks88: I am saying exactly that
passagesorry
passagestacks88: the new entries will be converted from your local time zone to UTC and stored. when retrieved, they will be converted from UTC to your local time zone
stacks88passage: so that is my issue, that my time_zone is SYSTEM, my system_time_zone is EDT.. but the new entries are still showing as 6 hours ahead for created_at value
stacks88is it because the mysql.time_zone table is empty ?
stacks88or i need to somehow populate the timezone information from /usr/share/zoneinfo or something like that?
sallestacks88: It will not hurt to populate mysql.time_zone, but it is mostly for using time zone names instead of offsets
passagestacks88: you only need to do that if you are manually converting timezone and using alpha syntax, like EDT, instead of hours, like +5:00
stacks88salle: You said set your desired time_zone -- my system_time_zone is EDT, time_zone is SYSTEM .. would that not be enough?
stacks88pasage: okay, so can we agree that if my system_time_zone is EDT, time_zone is SYSTEM, that I shouldnt be getting 6 hours ahead timestamp for created_at value? that is my dilemma, that i dont konw how to fix this
sallestacks88: If your desired time zone is EDT it is fine. If you want to see France times you can change it
passagestacks88: are you manually inserting a row for testing from the local server, or are you using an application, or somehow inserting from a computer in France?
sallestacks88: 6 hours ahead of what?
stacks88salle: the server is not in france anymore. it is in EDT timezone. when i add new entries on the website, the created_at value is still showing france time, how can i fix this ?
stacks886 hours ahead of EST time, montreal canada
stacks88passage: Not manually inserting a row, im giong on the web page, adding a link
passagestacks88: and where is the webserver? is the application code altering the timestamp data?
sallestacks88: Do you get EDT time or CEST time when you SELECT NOW(); ?
stacks88passage: its in montreal, canada. EDT timezone.. and no the application code is not altering the timestamp data.
sallestacks88: It is 04:24 in France
passagestacks88: if you're going to troubleshoot, simplify. get the web page out of the way. ssh into the server, use the mysql cli client, and insert a row. then select that row
stacks88salle: SELECT NOW(); gives me 2016-07-21 22:25:07 and the 'date' command on the system gives me 2016-07-21 22:25:07
sallestacks88: If you INSERT into table with TIMESTAMP it should get and then SELECT the inserted row you get same value as NOW()
sallestacks88: And UTC_TIMESTAMP() now is 2016-07-22 02:26:10 and it should be the same for you too
stacks88salle: NOW() = 2016-07-21 22:27:19 but UTC_TIMESTAMP() = 2016-07-22 02:27:19
stacks88that is part of the problem i believe, but unsure how to fix it
stacks88that utc timestamp is returning some other value..? or is that correct
sallestacks88: I don't see any problem there.
stacks88hm ok
sallepassage: Do you see any problem with what he says? :)
stacks88so you want me to insert an entry into the table with TIMESTAMP to see what it sets it to ?
stacks88manually*
sallestacks88: NOW() = 2016-07-21 22:27:19 because your server is in EDT
sallestacks88: Which is 4 hours behind UTC
passagesalle: I think he's shelled in from a 2-D world. Flatland. :-)
salle:)
sallestacks88: CREATE TEMPORARY TABLE tmp (ts TIMESTAMP); INSERT INTO tmp () VALUES (); SELECT ts, NOW() FROM tmp;
sallestacks88: You will get same value showing everything is normal
stacks88salle: i ran that and it does return the same timestamp as NOW().. so then does that mean the application is still somehow changing the time then?
sallestacks88: I know nothing about your application
sallenor about your table or tables
stacks88because thats where the issue started. when i went to go add a link on the website, i noticed the created_at value was still france time
passagestacks88: you should examine that
stacks88ok i think i figured it out guys, i was wrong all along, it is the application, im sorry for all this mess
stacks88i see in my application (i didnt code it), it has APP_TIMEZONE=EET .. EET is apparently eastern european timezone
stacks88it must have set that value when i installed it
stacks88since when i installed it, it was when the server was in france
sallestacks88: If the web server always retrieves UNIX_TIMESTAMP() and converts it to CEST that would explain the issue, but that's off-topic here
stacks88bu tim still using the same config so it must be why its diong that, my mistake, i shouldnt have assumed that the application didnt modify the timestamp
passagestacks88: the essence of troubleshooting is simplifying the variables
stacks88yeah, i figured the application just relied on system time, but i guess not, thanks for your time and help
passagestacks88: glad you got it worked out, at least you know what to focus on now
sallestacks88: EET is wrong for France :) Even I am not in EET today because it is summer and that idiotic summer time shift takes place ..
_August_Can someone take a quick look at my sql? im trying to use databinding and add a like onto my where statement but i keep getting an error back http://pastebin.com/JCDxG3Aw
dragonheart_August_: have you included the error message this time?
dragonheart"This page is no longer available. It has either expired, been removed by its creator, or removed by one of the Pastebin staff." - try dpaste.com
_August_nope ill do it now
thumbspastebin.com is horrible anyway
_August_http://dpaste.com/2E8K4BZ
dragonheart(10:12:04) danblack: and your WHERE criteria should be part of your JOINs
dragonheartyou need to quote patterns.
_August_talking to me?
dragonheartyes
_August_i tried that but i was still receiving an error
dragonheart_August_: "that" being what? joins or quoting your pattern? what error then.
_August_http://dpaste.com/302MWQF
thumbs_August_: and don't use commas in the FROM clause.
thumbs_August_: oh, you were already told that. Why didn't you fix the sql already?
dragonheartwas only mentioned 3hrs ago. can't rush these things.
_August_because it works just fine with it
_August_i didnt see the message 3 hours ago
_August_i still cant see it
_August_its gone
thumbsand anyway, the error on top is obvious too.