logs archiveChat DB / Freenode / #mysql / 2015 / November / 21 / 1
clayjar
Hmm, bdb looks like a predecessor for redis.
ExtraSteve, just because there is a small base of users doesn't mean it should go defunct. Just look at Hurd OS.
I know you weren't implying that directly, but it probably has some aspects of novelty still attached to it.
z1haze2
will and int type cover all port numbers?
Naktibalda
z1haze2: tcp port numbers?
salle
z1haze2: INT is little too big for tcp ports you know. Even INS SIGNED
z1haze2
what would you recommend?
thumbs
mediumint unsigned
salle
!t z1haze2 integer types
!m z1haze2 integer types
ubiquity_bot
z1haze2: See http://dev.mysql.com/doc/refman/5.6/en/integer-types.html
salle
z1haze2: Don't you trust the manual?
thumbs: Wrong answer unless something has changed regarding possible port numbers since last time I checked them
z1haze2
ok makes sense. No I do, i just have a hard time finding what im looking for sometimes
thumbs
(Action) raises an eyebrow
salle
thumbs: My memories are about 65535 as max value
thumbs
oh, mediumint unsigned is still overkill
salle
yup
z1haze2
theres no closer choice though
salle
z1haze2: Look again please :)
z1haze2
like he said the smallint has a max that doesnt cover all ports
salle
z1haze2: 3-bytes long SMALLINT SIGNED covers 0..65535
thumbs
salle: unsigned.
salle
z1haze2: UNSIGNED
thumbs: Yes typing too fast
z1haze2
oh whoops
its like it was designed that was or something xD
and just looking at the table, its a pretty poor display
salle
z1haze2: Yes it could be made better
chucky_z
is there a simple way to put a where clause on an aggregate column?
e.g.: select id,count(*) as c from tbl where c = 5 group by id;
just kidding, `HAVING` works fine
sim590
I want to rename my database. I've red that RENAME does bad things (http://dev.mysql.com/doc/refman/5.1/en/rename-database.html), is it still true (my version of mysql is: mysql Ver 15.1 Distrib 10.0.22-MariaDB, for Linux (x86_64) using readline 5.1) ?
Ugly-051
Please can I be advised how to set a root password without using mysql_secure_installation?
New DB so not set root password yet
jericon
Ugly-051: login without a password. UPDATE mysql.user SET password = PASSWORD('goes_here') WHERE user = "root"; flush privileges;
Naktibalda
sim590: you can't rename a database
Ugly-051
Thanks @jericon that worked
salle
sim590: RENAME DATABASE was once implemented, but then abandoned
aradapilot
yeah, you can rename all tables in one statement, though, to move them to a new database
same effect
obserious
the easiest way would be to dump your database and then import that data into a new database
sim590
thank you guys. I'll do that instead.
knarfly
I can't seem to get the substring syntax right. I'm using SELECT * FROM myTable WHERE SUBSTRING(myColumn,2,1)='D' but it gets nothing even though there are thousands of records where the 2nd character in the myColumn is a 'D'
thumbs
knarfly: you're probably confused about the contents of your column then
knarfly: SELECT substring(col, 2, 1) from tblname limit 10 where col like '%D%';
knarfly
thumbs: thanks...but that would find APD and also 'ADP' and I'm only after 'APD'
excuse me...ADP is what I want to find, not APD...the 2nd character is D then I want to account for it
thumbs
knarfly: I want you to look at what the substring() call returns.
knarfly
OK, this seems to work SELECT * FROM myTable WHERE SUBSTR(myColumn,2,1)='D'
thumbs
ok
salle
knarfly: You are doomed if you need to do this :)
knarfly
salle: I'm doomed anyway, but could you elaborate please? I want to learn more.
Brklyn
hi, I'm trying to check SHOW SLAVE STATUS\G, but it comes up empty... any tips? I don't have any replication going on at the time, but I was expecting some stats...
obserious
if it never had any repl data, then there is nothing going to come back
knarfly
the query returned the results I was looking for
Brklyn
obserious: so it'll report an empty set until replication actually starts, is that correct?
thumbs
Brklyn: better
obserious
Brklyn, until you setup replication, it cant tell you about any replication
a master will not return any slave info (shouldn't, if done correctly)
chucky_z
is there a good way to get a count of all appearances of, say, a user id by date? someone provided this the other day: http://sqlfiddle.com/#!9/da9e84/2
salle
knarfly: SUBSTR(..column..) or any other function or expression using column in WHERE clause can't use index so it forces full table scan which means you forget about performance
chucky_z
I can't seem to replicate that with my data though
salle
knarfly: In your case the only way to improve is to redesign the table so that you don't need that WHERE SUBSTR(..)
knarfly
thanks
salle
knarfly: There are other cases when it is as simple as tweaking the expression like in: WHERE col + 1 = 2; which can't use index whereas WHERE col = 2 - 1; can use index
Brklyn
Thanks obserious, I'm writing a stats collector and wanted to make sure about that point.
salle
chucky_z: What is the problem with the result in your sqlfiddle?
chucky_z
here's an example of my data... http://sqlfiddle.com/#!9/22334/1 -- it should return 2 rows, one with c=3 for groups_id=10, and c=4 for groups_id=11...
obserious
Brklyn, if you run more advanced setups such as master/master then what I said is not true but a basic binary pari is as I stated
s/pari/pair/g
doh, no sed
chucky_z
salle: sorry that query in there currently is a touch wrong, i'm trying to simply replicate it with my data and walking through it step-by-step to better understand how it's working
salle
obserious: There is no such thing as master/master. It is not coincidence MySQL Manual does never use such term
thumbs
obserious: surely, you mean circular replication instead.
obserious
is if you use heartbeat
really its master / standby so that may be where the wording is off
either box can be the master and each replicates from the other but only one takes writes
salle
chucky_z: Without looking carefully enough at your fiddles perhaps you are asking for "x and y" case
thumbs
no, it's not master / standby either.
salle
!t chucky_z a = x and y
ubiquity_bot
chucky_z: SELECT b FROM table WHERE a IN(x,y) GROUP BY b HAVING COUNT(DISTINCT a) = 2
chucky_z
salle: basically i want to find the 10th appearance of each groups_id
sorted by date
obserious
ok, please correct me
thumbs
obserious: master / standby is a simple master and slave, and the latter can be promoted to be the new master, if needed.
chucky_z
so if groups_id appears for the 10th time on 2015-11-10, i want to grab that data (i would imagine that could be grabbed via something like HAVING c=10)
obserious
ok, yes, its just the wording
thumbs
obserious: circular replicate is when you write to two servers at once, and each server is a slave of the other.
*circular replication
obserious
using offsets for the circular, yes
thumbs
obserious: master / standby means you only write on the master.
obserious
the naming of these things seems to get a little blurry
thumbs
obserious: the most ambiguous way to describe any replication is probably master-master. It means nothing concrete.
obserious
agreed
thumbs
I run a slave-slave shop. We're all slaves to the job.
obserious
lol
they are always taking dumps on the slaves, I know what you mean
chucky_z
ah yes the ubiquitous slave-slave replication
:P
« prev 1 2 3 4 next »