logs archiveChat DB / Freenode / #mysql / 2015 / September / 30 / 1
kervan
hello, I'm trying to connect to a remote host by using mysql -u myusername -p mypassword --host=111.111.111.111 but getting an error like this : Access denied for user 'root'@'222.222.222.222'
so, 222.222.222.222 is my local ip, it doesn't care what I am writing to --host
I mean it doesn't try to connect to 111.111.111.111
mgriffin
kervan: mysql auth is a combination of username, password, and source ip of the connection
kervan: your error message says this username and password combo is not valid from 222.222.222.222
kervan
But I'm trying to connect to 111.111.111.111 why it gives an error about 222.222.222.222
mgriffin
kervan: because your source ip is 222.222.222.222
(the client runs on the machine at this address)
PatBoy
u need to allow 222.222.2222.222 for this username/password combination
mgriffin
PatBoy: maybe. could be the password is wrong, or there is a more specific grant that is being used. lots of things can be wrong here.
PatBoy
i bet 2$ is because i dont allow this ip to connect :P
mgriffin
PatBoy: for example a root@'%' account password is used but root@'222.222.222.222' grant also exists, with a different password
PatBoy
he dont*
yeah maybe :P
mgriffin
PatBoy: it could also be that 111.111.111.111 is the external ip and the connection should go to 192.168.0.111 so that the source address is inside (nat ip)
in that case, the source ip looks more like 192.168.0.222
kervan
PatBoy: ip is allready allowed. It is not about that.
I'm able to connect via php, but on CLI it doesn't connect.
so, I think it doesn't care what I am writng to --host=
mgriffin
So we need to know, which grants exist for accounts called root, first:
select concat('"',user,'"@"', host, '"') as root_accounts from mysql.user where user='root' and host not in ('localhost',@@hostname,'127.0.0.1','::1');
kervan: php might connect as "webapp" and you are trying "root"
kervan: are you *actually* doing: -p mypassword
kervan: take out the space: -pmypassword
kervan: or leave out "mypassword" and let it prompt for password interactively
kervan
sorry all, it is my mistake.
I was trying to connect with an non allowed username for the ip.
Sorry again, and thanks for all helps.
PatBoy
<kervan> I was trying to connect with an non allowed username for the ip. ... i win 2 $ :D
kervan
PatBoy : I can neither confirm nor deny that you won 2$
mgriffin
PatBoy: that's because you rock and everyone else sucks
crisumi
(Action) woo hoo!
mgriffin
heh percona is building against readline again
welcome home!
adv_
how can i increase the memory of a db?
at some point i went over 90k rows and it complained
i will populate a new table now over some hours and i want to make sure that it can hold the data
salle
adv_: 90 000 rows is tiny by nowadays standards
adv_
i don't know why it complained there
it was something about memory not enough , and it was a php pdo error
is there any implicit limit on db or table size ? on default mysql
aradapilot
not really
for rows, i guess the largest pk is 3072 bytes, so 256^3072 records, but that's a barely comprehensible number. tablespace addressing issues for pages would come up first
lemme grab the man page with those numbers
here
http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html
adv_: ^
adv_
issues for pages?
aradapilot
oh, 90 thousand? yeah, no possible way that'd hit any limit
maybe 90 billion
if you had the disk space
my assumption would be there's an issue with memory on the client server
adv_
aradapilot: what is the client server?
aradapilot
the one with your php on it
adv_
ah so like i'm out of physical memory
quite possible yeh
yeah
aradapilot
that, or out of the memory that php is allocated
felixn
https://gist.github.com/munro/aa4c404455e055d07d88 <-- hey is there an efficient way to exclude rows if a related table has joined data within a certain time period?
that's just one of many permutations I've tried @_@
salle
felixn: First get rid of that nonsense with the subquery
felixn: SELECT COUNT(*) FROM (SELECT * ...)
felixn: Seriously?
felixn
it's efficient and makes composition easy for playing in the repl
jexmex
doing a basic index on a column in a table with 175 million rows or so, should that take hours?
salle
felixn: Not only it is not efficient, but it also truly idiotic
jexmex: What do you mean by basic index?
jexmex: And how are you "doing an index"?
jexmex
its a timestamp column, the index is for a single column ASC
ALTER TABLE `domains` ADD INDEX `LAST_UPDATED_ASC` (`last_updated` ASC);
salle
jexmex: If you do ALTER TABLE on huge table it normal that it will take long time
jexmex
so your saying I am in for a long night?
lol
salle
jexmex: It can take hours, days or even weeks depending on hardware
jexmex
I hate big data
ebergen
even better when it hits replication!
heh
jexmex
not sure at what level you consider data "big data", but this is the biggest table I have had to work with
fights me the whole way
ebergen
on decent hardware that should only take an hour or two
unless the rows are massive
salle
jexmex: Let's say "too big" = "does not fit into the RAM" :)
ebergen
even faster if you're using fast index creation in 5.6
felixn
salle: that's incorrect o_O good to know you don't know what you're talking about though
jexmex
8gb box with 8 cores. The rows are very basic, 5 columns.
salle
felixn: Ask whoever you want. SELECT COUNT(*) FROM (SELECT * ...) is truly idiotic
jexmex: 5 int columns = 20bytes
ebergen
that was decent hardware in 2007
maybe 2006
felixn
http://stackoverflow.com/questions/16584549/counting-number-of-grouped-rows-in-mysql?answertab=votes#tab-top <-- salle I recommend you try it before you open your mouth as well, same execution time
salle
felixn: I recommend you to not trust stackoverflow for anything.
felixn
salle: please don't bring your toxic attitude here as well
salle
felixn: How long do you use databases and SQL?
felixn
^
salle
felixn: I am serious about stackoverflow. It is wrong about MySQL about 90% of the time and probably about other things too
jexmex
salle: out of curiosity, is count(id) better?
salle
jexmex: Better than what?
felixn
salle: you're seriously wrong on performance XD
jexmex
count(*), RE: the other conversation going on
salle
felixn: Let me clarify: SELECT COUNT(*) FROM (SELECT * FROM <some tables>) produces exactly the same result as SELECT COUNT(*) FROM <some tables> so what is the point to do it with subquery?
« prev 1 2 3 4 5 6 7 8 9 10 11 next »