logs archiveChat DB / Freenode / #mysql / 2015 / October / 12 / 1
Xgc
Twirl: Optimizing queries often involves fixing the design, whether that was your original intent or not.
Twirl
the results for the harshest query are taking around 0.4ms, not that bad
thms
So i mysqldumped all databases, reimported (same version), on old sql I can login as users, on new I cant
I done select user,host from mysql.user and all users are there but their password are no longer working
any idea on that ?
threnody
thms: did you do FLUSH PRIVILEGES; after the import, or restart mysql server?
thms: the privilege tables are loaded into memory when the server starts. if you imported new privilege tables, you need to do FLUSH PRIVILEGES; or restart the server for them to take effect
thms
threnody: didn't restart, will try.
threnody: that was it ! thanks!
threnody
yw
buzzkill
Using sqlfiddle to try and show results... http://sqlfiddle.com/#!9/b467be/1
I am having issues with the "Top N" query not reliably showing results. When fully poplated with data, some users that have played 7 tournaments only show results of 4 summed, others 6.
I cannot load full data, because I get an error w/r/t being over 8k
Any assistance in trying to get the query right would be appreciated.
QuantumAtom
I know this is vague, but I ran a query with correlated subquery, but I tried to filter out null values with is not null after an inner join, yet it doesn't filter null values
I don't know if typing in the query will help, but I didn't want to flood the chat
RusAlex
q: what statement grant execute on procedure beta_beta TO 'beta'@'%'
does ?
suppose beta_beta must be either function or procedure
seems like after update, restoring binary logs fails on this query
with error: ERROR 1305 (42000) at line 536147: FUNCTION or PROCEDURE beta_beta does not exist
before update everything was working well
mysql grant syntax manual shows: beta_beta is priv_level token
salle_
RusAlex: a: There is manual: http://dev.mysql.com/doc/refman/5.6/en/grant.html#grant-routine-privileges
RusAlex
salle this statement in my binary logs. And they had been restoring every day very well
just today something became broken
statement was correct during half a year
hartmut
didn't reporters still have access to their own bug reports if a bug was marked private on bugs.mysql.com?
I'm now staring at https://bugs.mysql.com/bug.php?id=74108 which i definitely filed myself and it's just telling me "you don't have access"
serg
hartmut: yes, they do (or they used to, last time I've reported such a bug :)
might be a recent change
motaka2
hello what is the english title for WHERE or ORDER BY or LIMIT ?
CuriousOne
Hello. I'm curious about one thing: what happens if process ID in mysql gets very high?
jkavalik
motaka2, "clause" (not sure if applicable to LIMIT) or a "keyword" ?
motaka2
jkavalik: thank you I chose keyword
jkavalik
motaka2, depends on what you need it for, but if describing the structure of a SQL query, "clause" is the right one - https://en.wikipedia.org/wiki/SQL
motaka2
jkavalik: thank you
salle_
motaka2: Clause is the correct one. Keyword is more broad
motaka2
salle_: thank you.
CuriousOne
I noticed process id's just get incremented and I'm just curious if they can overflow in any way
salle_
CuriousOne: Every number can overflow in computing :)
Naktibalda
I haven't heard about anyone having issues because of it
CuriousOne
What happens if it gets very, very high? Will it reset back to 0?
jkavalik
CuriousOne, it is defined as unsigned long, you would have to increment it a lot - https://github.com/mysql/mysql-server/blob/5.6/include/mysql.h#L275
(at least I think that thread_id is the one which is shown as connection id
salle_
jkavalik ++
Naktibalda
Survey: what's the highest process id that you see in your servers?
CuriousOne
I now see over 1000000 and it doesn't run for long time
jkavalik
salle_, unfortunately I was not able to verify it 100%, there are some places in the code where it looks like uint32 only is being used in some case for my_thread_id and such..
sachinaddy
Hi All. I want to really encrypt the password and data which I'm passing from my application to my mysql db. What kind of encryption for password shall I use? Because Md5, sha1 all can be decrypted easily
jkavalik
CuriousOne, "Uptime: 117 days", average qps 394, connection id 288188315 - that is 20 times smaller than even unsigned int
hrust
I want to update existing dataset to utf8 encoding. I have changed table encoding to utf8 but I need to convert existing rows to utf8 now. what is the easiest way to convert it?
Naktibalda
sachinaddy: bcrypt
!m hrust alter table
ubiquity_bot
hrust: See http://dev.mysql.com/doc/refman/5.6/en/alter-table.html
jkavalik
CuriousOne, and imho, even if it wraps, there should be no problem, as "used" ids should not be reused anyway so just getting low IDs again does not change anything
Naktibalda
hrust: ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
sachinaddy
Naktibalda: Is MD5 a hash or encryption tool? I check bcrypt just now.. Its one-way right?
Naktibalda
yes
md5 is a hash
CuriousOne
jkavalik: well, that calms me down, though I'm still curious about this. Thanks for help though
Naktibalda
sachinaddy: if you use PHP, use password_hash function
sachinaddy
Naktibalda: yes, I'm using PHP.
Naktibalda
sachinaddy: you don't want to encrypt password
hrust
Naktibalda: I tried ALTER TABLE mytable CONVERT TO CHARACTER SET utf8; but nothing changed?
Naktibalda
hrust: how do you know?
sachinaddy
Naktibalda: So if I use password_hash function, some hash data will be stored in the db. No one can figure out the password who will have access to the raw data ?
Naktibalda
yep
hrust
Naktibalda:what do you mean? I checked dataset and it is not encoded in utf8
sachinaddy
Naktibalda: Thanks.
jkavalik
CuriousOne, for usual implementation of threads the identifier should just be unique at any given instant (and only within current application), so reuse should be OK anyway (pthreads have it specified that way for example), I do not know details mysql threads, but imho there should be no need for it to be very different
hrust
Naktibalda: collation is set to utf8_general_ci but row values are not encoded in utf8
Naktibalda
how do you check?
jkavalik
sachinaddy, for password hashing is used because there is no way someone might get hands on you encryption keys and get everything "for free", but any hash can be "broken" by using predictable or weak passwords, it just takes much more time for bcrypt to "make a guess" than for md5
hrust
Naktibalda:with phpmyadmin?
__gilles
hi
with innodb is there a particular threshold for the number of rows in a table before it starts to degrade in lookups ?
Naktibalda
1
__gilles
if i know in advance a table may end up having > 40 millions rows and i will mostly do primary-key based searches, is it worth sharding the table into bucket tables ?
Naktibalda :-)
xupicor
hi there. ;) a bit of a db design question. I want to store gps positions in database, the idea is to get and save gps coordinates that the clientside will retrieve and display, however, there are two "special" sets of coordinates that will be given before any other, lets call then "from" and "to". So naturally I want to have a "gps_coordinates" table, however, the "special" but irks me. These special locations should be handled differently than others, a
Naktibalda
xupicor: your question ended at "be handled differently than others, a"
xupicor
I'm thinking reference table is probably the best way to go here? Thing is, I can't be sure I won't add special types and adding special tables seems like a worse idea than just adding rows
Naktibalda: http://pastie.org/10476349
cheers, by the way, if I could remember any handle on freenode it's yours ;)
jkavalik
__gilles, innodb table is stored IN the primary key, so as a b-tree, not sure how many levels the b-tree has, but it is "not many", there is and estimate of 5 levels for "a billion of rows" https://www.percona.com/blog/2009/04/28/the_depth_of_a_b_tree/
__gilles, in the end it might take you more time to decide which shard to query than what speedup you get by sharding
__gilles
thanks jkavalik
marchelly
Hi, is that spoossible to use REPLACE in WHERE like SELECT id from table_name where REPLACE('name','.',''); ? Why do I need this. I have local_name part of email stored in table, like field `localname` 'john.doe' and I want to select id where `localname` = 'johndoe' and I want it match john.doe
I want stored value 'john.doe' match all dop positions in the string like 'j..ohn.doe', 'johndoe', 'johndoe...' and so on
how can I select id matching this criteria?
Naktibalda
marchelly: yes, you can use REPLACE to remove dots
but it would be more efficient to store dotless usernames in the table
marchelly
Naktibalda, So the best solution is to store 2 values, one dotless, and other one, what we show to the user?
Naktibalda
yes
marchelly
ok. thanks
jkavalik
it might be possible to use some custom collation to "remove" the dot from all checks, but that seemed like a very messy solution last time I saw it
Danielss89
Hi. I have a table with some data. id, value, correlated_value
Now i want to do a SUM of 'value' BUT, if the row has data in correlated_value, i want to use that instead of that from `value`
is that possible
« prev 1 2 3 4 next »