logs archiveChat DB / Freenode / #mysql / 2015 / November / 20 / 1
BlaDe^
can I get a set of example parameters from a query fingerprint (obtained by pt-query-digest) ?
mgriffin
BlaDe^: grep the slow log for something rare
like an alias in the query
BlaDe^
mgriffin: yeah thought as much, wondered if there was a way the toolkit could help
aradapilot
pt-query-digest provides one set of sample parameters in its output
events_..._digest does not
if you need more than one, grep, but one is usually enough to check the basics
Kieran
Hi, how would I go about resetting AUTO_INCREMENT (or truncating the table to achieve the same) when a table is empty? That is, how can I do it atomically so as to make sure no rows are inserted between the check and the reset/truncate?
danblack_
!m KeithGS truncate table
ubiquity_bot
KeithGS: See http://dev.mysql.com/doc/refman/5.6/en/truncate-table.html
danblack_
will reset auto_increment.
Kieran
I know
I want to make sure that the table is actually empty before doing it, though
danblack_
rests to 0; otherwise alter table can set it to a value. you probably can explictly lock the table (LOCK TABLE) before doing the alter table
Kieran
Hm
http://stackoverflow.com/a/5972404/5583112 This seems to accomplish what I want in a better way
Since it looks like RENAME is atomic
Thanks anyway
BlaDe^
a covering index can use two equality and a range right?
but the range must be the most rightmost of the index?
danblack_
BlaDe^: constants(yes more than one is possible) come before range.
!t BlaDe^ query optimization
ubiquity_bot
BlaDe^: https://github.com/jynus/query-optimization
danblack_
Kieran: yeh, good idea. depends a bit on use case but might work for you.
BlaDe^: yes, range is rightmost
BlaDe^
danblack_: damn in this case the range is a unix timestamp field... so it uses ts > UNIXTIMESTAMP(DATE(..))
I will move the calc to the code, I guess changing the datatype would be tough at this point
danblack_
BlaDe^: as long as ts is the field it should be a problem. you can wrap as many functions around other constants in sql as you like.
virtual computed colums and index are one way to do migration if you can isolate all your table update statements and change them a the same time
BlaDe^
that would be possible
Kieran
With the C API, can work with multiple result sets (from mysql_store_result()) at the same time, or must you free one before you can store another?
BlaDe^
danblack_: but you're saying: last_ts >= UNIX_TIMESTAMP(DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))) --- is okay?
it'd still use a range index?
danblack_
yes. tias.
bbl
vhuren
hey guys I have a table with category_id inside it and I want to write a query to select 100 rows but each category could only have upto a max of 5 rows. How would I go about doing this?
thumbs
!t vhuren group top n
ubiquity_bot
vhuren: To find the top N per group check out -> http://thenoyes.com/littlenoise/?p=36
vhuren
thank you thumbs
oneirosFade
Hey SQL wizards. How can I create a trigger that only modifies the inserted/updated row? I have a TIMESTAMP and a DATETIME, and I want the DATETIME to be set to the TIMESTAMP on insert. This is a trigger, right?
Or should I just have the interfacing software set the DATETIME to the right timestamp on its own?
thumbs
!t oneirosFade timestamp
ubiquity_bot
oneirosFade: The DEFAULT clause for the TIMESTAMP data type in MySQL 5.5 and earlier only allows a constant, CURRENT_TIMESTAMP. Starting with MySQL 5.6, you can now use NOW() with the DATETIME or TIMESTAMP data types. Please see http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html and http://dev.mysql.com/doc/refman/5.6/en/datetime.html for references.
oneirosFade
Thanks
DaddyEric
question, how do i get mysql fields to quit removing the leading 0's on a number
its freakin annoying
kolbe
DaddyEric: what sort of number is it?
DaddyEric
ist stuff like this 006-Eastern Hills and the field value is 006 but for some reason mysql removes the 0's on the leading
kolbe
DaddyEric: "numbers" don't have significant leading zeros. are you sure you're storing a "number", or are you storing a string that just happens to be composed of characters?
DaddyEric
charset utf8 data type char
thumbs
pray tell, how are you inserting data into that column?
DaddyEric
you talking to me?
thumbs
yes.
DaddyEric
storing it through PHP and the fieldvalue is only 1 to 3 chars
kolbe
DaddyEric: leading zeros aren't removed from character columns. so, you're doing something wrong on the application side if you are seeing that behavior.
DaddyEric
ok
what does zero fill do?
thumbs
!t DaddyEric zerofill
ubiquity_bot
DaddyEric: The optionally defined number after an INT data type represents the display width when used in conjunction with ZEROFILL. Please refer to: http://hashmysql.org/wiki/Zerofill
thumbs
DaddyEric: it's only for integral column types, too.
DaddyEric
ok
well i am still learning about everything
at least I can write some queries now as i have learned that
thumbs
oooo a binary secret message
DaddyEric
i hate messages period
they are annoying and are a distraction
|_[O_O]_|
i've installed MySQL 5.6 from the apt repo (https://dev.mysql.com/downloads/repo/apt/), and am looking at the lack of a /etc/mysql/debian.cnf. Has this configuration been moved elsewhere - or does it no longer exist?
threnody
|_[O_O]_|: that's not a debian repo :-)
|_[O_O]_|: the debian package managers add that
|_[O_O]_|
reading over https://bugs.mysql.com/bug.php?id=73711 now, is it not actually required for safe operation (updates, etc)?
or does this imply a pinned release that will receive no updates
jenni_lib
hello, Im having problems with LOCK IN SHARE MODE in a PHP loop. I end up with a number that is the same for all the objects updated in the loop but when I call it one at a time, it updates correctly, can anyone see what Im doing wrong?
aradapilot
jenni_lib: an example would be nice, but if it's a problem in the php code, ##php would have more help for you. we're mostly just database folks in here.
pastie that part of the code and the results you're seeing
jenni_lib
this is what I am doing http://pastie.org/private/imbywuotberd9ctpxa4a7a aradapilot
reading up some more, I am learning that it allows other transactions to read but not update, this is probably why it updates once for and then reads the new value over and over for all other objects in the loop
however, I cant see where in this the transaction begins or where I can end it within each loop?
sorry, the FOR UPDATE part is a recent change, it was LOCK IN SHARE MODE
line 6
danblack_
jenni_lib: what loop? if you've just got an UPDATE statement SELECT for UPDATE isn't required.
if you have an outer loop, try to factor this into the same sql statement. otherwise...
!t jenni_lib start transaction
!m jenni_lib start transaction
ubiquity_bot
danblack_: Sorry, I have no idea about that manual entry.
thumbs
!m jenni_lib being transact
ubiquity_bot
thumbs: Sorry, I have no idea about that manual entry.
thumbs
blergh
danblack_
!m jenni_lib begin
ubiquity_bot
jenni_lib: See http://dev.mysql.com/doc/refman/5.6/en/begin-end.html
danblack_
!m jenni_lib commit
ubiquity_bot
jenni_lib: See http://dev.mysql.com/doc/refman/5.6/en/commit.html
thumbs
stupid Oracle pages
danblack_
ignore begin and other guff :-)
jenni_lib
thumbs I have an array of $Models and $fields and call the function where this query is in for each $Model and $field
thumbs
jenni_lib: sounds like a terrible idea.
jenni_lib
I think so, but Im stumped on how to deal with this
thumbs
jenni_lib: populate a temp table, and use an UPDATE with a JOIN
jenni_lib: UPDATE t1 JOIN t2 ON ... SET t1.col = t2.col WHERE ...
jenni_lib
it worked ok before now, I tried adding the lock because somehow 2 users performed this function at exactly the same time and ended up with the same $update[0][$this->table]['value']
danblack_
or start at the problem, why are you incementing values?
« prev 1 2 3 4 5 6 7 8 next »