logs archiveChat DB / Freenode / #mysql / 2015 / October / 11 / 1
Mr_Pancake
Hey. How could I tell if long mysql shutdown is doing anything? It wouldn't be very safe to kill the process, right?
danblack
let it go - probably won't be more than a few mins. read the error log. try to avoid killing the process
Mr_Pancake
how could I check if it's doing anything at all? Would it be possible it just stopped at some point?
there is pwait on mysql proccess id for some time already
danblack
unlikely. strace the process.
is there cpu activity?
Mr_Pancake
not on sql
lot of ERR#60 'Operation timed out'
kill -15 on sql process gives nothing too
Xgc
Mr_Pancake: Usually, there's more to the story you forgot to tell. For instance, why were you shutting down? Was there another problem prior to the shutdown attempt?
People love to leave out all the detail about manually copying and deleting files from the file system.
Mr_Pancake
I didn't touch mysql directory if you're asking about this
Xgc
or that they had killed the mysql server prior to this and it never really recovered.
Mr_Pancake: I'm asking about the entire system. What drove you to shutdown mysqld?
Mr_Pancake: It's very rare that the problem you are asking about is the beginning of the story. It could be. But that's rare.
glowdemon1
Is it a thing to set varchar to unsigned, or does this have no effect?
Mr_Pancake
It stopped responding. I can't really tell now what happened on system yet
Xgc
glowdemon1: It's not a thing. That applies to numeric types.
Mr_Pancake: So this is the first sign of a problem and you had no specific reason to shutdown mysqld, other than you felt like shutting it down?
Mr_Pancake: or did it stop responding before you decided to shut it down?
Mr_Pancake
It stopped responding before I tried to shut it down... I'm digging up to logs now to check if there's something unusual, I'll ask about anything I find odd
Xgc
That makes more sense.
That's getting into the part of the story you forgot to mention.
The original problem is rarely serious. It's the coverup that gets most people sent to jail.
glowdemon1
I'm having two tables: Accounts and websites. An account can have multiple websites attached to it and I'd like to keep track of howmany times a website has been attached to an account: Do I need a field to the websites table (times_used) and keep track of the count there, or do I just do a query on the table Accounts each time I want the count?
do I need to add a field*
Xgc
glowdemon1: What are the keys involved? "How many times" suggests you can assign a site to an account multiple times and there's some other attribute like assigned_timestamp.
glowdemon1: Explain the relationship more clearly.
glowdemon1
Alright, one sec
Xgc
Maybe you have an active bit / attribute that indicates a site / account entry is no longer applicable.
glowdemon1
http://puu.sh/kFybc.png <-Here I can link multiple links from my other table (http://puu.sh/kFy5S.png) Now, in the first table, people can "group" multiple urls to a group_key -> I want to know howmany times one link from the second table has been grouped in the first table. Do I add another column that keeps track of the count or do I just do a query on the first table each time I want to know
the count?
Whats the most efficient?
Xgc
glowdemon1: I don't understand the purpose of any of that.
glowdemon1: You should tell the complete story.
glowdemon1: Add real table names. Sometimes the table names help to clarify your intent.
glowdemon1: But the general answer is, just use GROUP BY. I don't need to know why you asked or what your schema means.
glowdemon1
Alright, thanks. Think I found an article on that
Xgc
glowdemon1: It's often better to not store values that can be calculated from the base data. But that's your call. It's generally done with materialized views.
glowdemon1
I see
Thanks
Xgc
glowdemon1: The chance of getting it wrong high with triggers or other methods of updating the calculated values.
The s/high/is high/
xor1
In absense of exception handler will `signal` cause the procedure to immediately exit?
In particular, for class '02'
glowdemon1
ALTER TABLE `primary_urls` DROP PRIMARY KEY , ADD PRIMARY KEY ( `url_id` ) I'm trying to change my primary key but it keeps setting the primary key to visitor_count ( http://puu.sh/kFEYw.png )
klue
Which version of mysql addresses the buffer overflows in 5.6.24 (http://0day.today/exploit/24379)
dw1
should i avoid ORDER BY RAND() ?
for large tables*
resultsets*
nm the guy who had troubles didnt have proper indexes
with good traffic tho i guess i'll just have to randomize once and store in db
for efficiency
do that every X mins
i doubt mysql optimizes rand on every page load with high traffic.. but maybe it could
like a timed rand query or something idk
i.e. only randomize every X mins
cache the rest
elisa87
http://stackoverflow.com/questions/33061505/benchmarks-similar-to-tpc-h-and-tpc-ds
ss23
If you create a UNIQUE INDEX over two fields, lets say first name and lastname, does that mean that the person "Brendan Elks" isn't different from "Brenda Nelks"?
Vacuity
ss23: a) just try it. b) the UNIQUE over two columns (a,b) is sensitive to the order of the columns. so you can have both tuples (1,2) and (2,1) in it
ss23
I know it's sensitive to order, I was wondering about content
I guess I can test it, if I have to, Vacuity :(
Vacuity
ss23: ah, I see your real question now :) your example will be two different entries. they are not "glued" together for comparision
ss23
Thanks, Vacuity :D
niemal
Hello! How can I check if a SELECT query returns an empty set, so I can jump to an INSERT? I want to achieve this with a single query in total.
Vacuity
niemal: thats not possible. there are two options: 1) if with your SELECT you want to check the existence of a value that has a UNIQUE index, just run the INSERT and catch the error to know it already existed. 2) use a transaction with SELECT ... FOR UPDATE , then INSERT if needed
niemal
Vacuity: Thank you.
philm88
Is there a way to use mysql_config_editor to set the password non interactively? mysql_config_editor set --user=me --password=something doesnt work; mysql_config_editor: option '--password' cannot take an argument
I'd also like it to skip the 'are you sure you want to overwrite the existing mylogin.cnf file' message and just do it too
Just found the --skip-warn option that fixes issue #2 - I'd still like to be able to set the password non-interatively though
For some reason I cant just do; echo 'something' | mysql_config_editor set --user=me --password
extor
Instead of using PHP how would I use bash to query a db, are there any bash bindings for mysql or does one have to use perl or python?
danblack
if using bash you'd leverage the mysql client utility. what is your goal? if its too complicated a scripting language that supports mysql db connections would be recommended.
dunnock
extor, You can write bash scripts with php if you want
ivan
echo "USE mydb; SELECT * FROM table;" | mysql
extor
yes but how do I keep a persistant connection to a specific database alive, is that even possible
danblack
extor: look at coproc in bash.
extor
oh coprocesses
I don't know that technique, I just know bash scripts unfortunately
danblack
extor: https://github.com/openquery/mariadb-server/blob/MDEV-5536-systemd-socket-activation-notification-10.1_clean/scripts/mariadb-socket-convert example that uses my_print_defaults. both read and write file descriptors are available
it is bash :-)
(though i've only read from the output there).
niemal
Can anyone enlighten me why this raises a syntax error? SET @wat = 1; IF (@wat IS NOT NULL) THEN SET @yay = 5; END IF;
danblack
niemal: what is the exact error?
niemal
danblack: http://hastebin.com/iyawidibov.vbs
^ @yay is NULL after that.
danblack
!m niemal if
ubiquity_bot
niemal: See http://dev.mysql.com/doc/refman/5.6/en/if.html
danblack
note it says for procedures
!m niemal function-if
ubiquity_bot
danblack: Sorry, I have no idea about that manual entry.
danblack
http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html#function_if is probably what you want.
niemal
danblack: I still don't understand how I can do this.
The docs say IF condition THEN code; END IF;
But that's not possible AFAIK (??).
Your instruction doesn't include statement execution but returning a value which is kinda irrelevant IMHO.
danblack
niemal: set @yay = IF(@wat, 5, NULL);
niemal
danblack: What if I want to execute an INSERT statement instead?
e.g. INSERT INTO test (id) VALUES (1);
« prev 1 2 next »