hey all Ive just installed a mysql server on a fedora box and am trying to set the password for the root account
you must know what binlog position the master was at when you created that copy that you want to turn into a slave
taking 1.1s to query
that seems slightly complicated..
it is not
it is precisely the procedure used and needed for a point in time recovery
should one index a primary key, or is it indexed by default? (probably a dumb question)
you explain does not show the query
hmms..
the problem is apparently that the first table is scanned com0pletely, type ALL
Isotopp, i'm using explain query here
is not visible in the pastebin
Is there any problems with mysqlclient crashing applications when mysqld goes down?
updated Isotopp http://www.pastebin.ca/662028
is there an oppisite to `SELECT TOP 10` ? somthing like `SELECT BOTTOM 10` ?
couldn't find anything on mysql's refrence even for TOP
your query is being processed in the order evidence, spamming_ips, and there is no constraint on evidence, so it is a full scan
teh join condition is "evidence.ID = spamming_ips.EvidenceID"
ah, other way round?
apparently there is an index on evidence.ID, and one on spamming_ips.EvidenceID so it could be processed either way
you could try to force it the other way around with straight_join
but the optimizer thinks this is worse
it might be useful to add a coluimn and
precalculate spamming_ips.Address + (pow(2, (32 - spamming_ips.Mask))
in that col
ok … so i'm basically screwed?
EvidenceID is a foreign key btw
the optimizer chooses this join order presumeably because the condition spamming_ips.Address = '1414114284' appears to be not particulary selective.
the other condition (spamming_ips.Address + (pow(2, (32 - spamming_ips.Mask)) - 1)) = '1414114284' appears not to be useful for the optimizer, because the index on
spamming_ips.Address cannot be used (part of an expression)
ah
you could also reformulate the query in a way that spamming_ips.Address is freestanding on the lhs of the inequality
so if i precalculate the number as u said …
finally, the '' around the numbers aren't useful or helpful as well.
i use $db-quote in perl
spamming_ips.Address = 1414114284 - pow(2, (32 -
spamming_ips.Mask)
hi, is there a way to force a table to have only one record?
with a trigger
something like this ….
- AND (spamming_ips.Address + (pow(2, (32 - spamming_ips.Mask)) - 1)) = ?
+ AND spamming_ips.Address = ? + pow(2, (32 - spamming_ips.Mask)) - 1
is there an oppisite to `SELECT TOP 10` ? somthing like `SELECT BOTTOM 10` ?
Isotopp, yea … precalculation queries in under 0.00s
SELECT TOP 10 …… ORDER BY ID DESC
just order it oppisite to whatever you're ordering it now.
hrmmmmm
how would I remove key from a table? drop key xxx?
hey all, I've just installed a mysql server on a fedora box and am trying to set the password for the root account for which you log in using your hostname. But when I try this the connection fails. If do telnet localhost.localdomain 3306 it connects for about 3 seconds and then the
connection is closed, any ideas why?
nmap says port 3306 is open and mysql is running on it
erm, why are you using telnet?
drop index indexname
Auris-: Telnet has its uses, specifically for private trusted networks.
Auris-: Telnet has its uses, specifically for private hosting trusted networks.
if the key is on Address , I drop index Address?
Consty, yeah, but if I wanted to contact my server, I'd use mysql client.
"alter table foo drop primary key;" ?
nope, a normal key (column,column2)
nope, a normal KEY (column,column2)
(timestamp, id, key, value). the primary key is (timestamp,id,key). I am trying to select all the rows of a particular key where the timestamp is the maximum.
(maximum for the id,key pair)
select max(time),id,value from t where and `key` = 'some_specific_key' group by id;
but it mixed data from different rows. (got the max time, but the wrong value)
(time,id,key,value)1/1/2007,1,k1,v1)(2/1/2007,1,k1,v2),(9/1/2007,1,k2,v1) , I want to select (2/1/2007,1,k1,v2),(9/1/2007,1,k2,v1)
this seems to work, but I think I can do it in a better way:
select * from (select time,id,value from table where `key` = 'some_key' order by time desc) u group by id;
where are the MySQL logs on Ubuntu?
omry, you can try "select time,id,key,value from (select * from table order by time desc) as table group by id,key;" ,but the correct sql is : select t.time,t.id,t.key,t.value from (select id,key,max(time) as mtime from table) as maxes inner join table as t on
t.id=maxes.id and t.key=maxes.key and t.time=maxes.mtime
gnari, nice.
is that much better than what I do now?
select * from (select time,firestats_id,value from sysinfo where `key` = '$key' order by time desc) u group by firestats_id
how do i set a mysql pass?
njnddd~?a
njn~?a
~-
sorry
hi, Im trying to use mysqlhotcopy to dump all my databases. Can anyone give me some pointers on syntax
man mysqlhotcopy
I did
can you use wildcards to do everything or do I have to specify each database?
Never used it, sorry. *shuttingup*
i have some syntax error and i don't have any clue …. http://pastebin.com/d2d190ea0
moin
moin moin
pleeeeaseee heeeelp
Hi
I've got a table with a LONGTEXT and I'd like to search this LONGTEXT for a specific string, how do I do that?
Morning all! Can someone please tell me why, when I run this CREATE TABLE script, the CHAR(15) actually creates a column of data type VARCHAR(15)? Please see http://pastebin.ca/662122
dazjorz, what about using "=" or "LIKE" ?
ah with LIKE and %string%
lucox, your insterting two bits of data into three feilds
lucox, is it because you must have single quotes around the 0
status is integer
can you try it anyway?
two bits ? 'blabla','blabla',NOW()
i have tried it
hmm ?
lucox, do you need a semi colon after begin?
Hey guys
where do you see semicolon after begin ??
I have a DATE field, how can I make the default the current date?
could someone run the create table code at http://pastebin.ca/662122 and please tell me why the CHAR(15) directive results in a VARCHAR(15) column?
CHAR means one character doesn't it?
So it's converting it for you.
lucox, I mean, try adding it
after begin
please give me an advice, i'm worrying with it for hours
see if you get a different error
hal, read what I said
lucox, are you listening to me?
Hexxeh, thank you, I will look at that….brb
Heh, okay.
Makes sense, in C++ char means a single character.
I assume it does in php mysql web hosting too.
Anyone know about my date question?
after BEGIN ??
yes BEGIN;
I'd like MySQL to set my row(character) creation date for me.
i don't see a semicolon after begin in any example … http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
i tried but it's not right …
"The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. (Before MySQL 3.23, the length of CHAR may be from 1 to 255.) "
what is the error? Does it say that there is an error near BEGIN; ?
Yeah I was wrong
Nevermind, just trying to help =]
Hexxeh, thank you
If anyone else can try creating the table, I would be grateful, especially if they know why it doesn't work?
people please help me, it must be simple for you …
I'll try it
Works fine here.
lucox, you need FOR EACH ROW before the BEGIN. You don't need any semi colon after the BEGIN
Results in a CHAR(15) column
I'm using SQLYog though.
Easier for dev stuff 
I'd reccomend it to anyone, it's excellent.
Hexxeh, no way! Could you try directly in the MySQL CLI?
phpMyAdmin on speed.
Don't have it here sorry.
always syntax error
Windoze :/
lucox, but is it the SAME syntax error?
I'm working on a windows job :/
Hexxeh, you can still do it. Go to the Command Line, then type mysql and return
Server isn't on this machine, so I don't have the cli.
I'm working with a cluster atm.
Sorry :/
Hexxeh, ah, I see. Do you have telnet or SSH access?
No sorry.
I'm not that priviledged
I only get basic stuff.
They're security tight.
Hexxeh, you know, if you need the CLI in the future, just install mysql locally, and then type mysql hosting -h yourserver
Yeah, I know.
SQLYog is faster.
I put your query into it and it worked fine :/
luxthat is an improvement
your error is on line 4 now
but i don't see error …
lucox, but you had another error before that you have just solved!
lucox, you must tackle it step by step
i was in that state some minutes ago …
line 4 is insert
if i run this insert separate - there is no problem
lucox, no, it wasn't - your error before was to leave out FOR EACH ROW. Now you need to resolve line 4
but i don't know now … with or without FOR EACH ROW i was experimenting several times … but i don't know detect the main error
lucox, delete the INSERT INTO line. Does it complete without errors?
no
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 5
lucox, how are you creating the trigger? from mysql command line?
phpmyadmin
lucox, replace the INSERT INTO with RETURN FALSE;
#1313 - RETURN is only allowed in a FUNCTION
hello all!
hmm?
lucox, see http://pastebin.com/d78a771a
lucox, any luck?
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
lucox, that was http://pastebin.com/d78a771a was it?
yes
lucox, try http://pastebin.com/d70c46654
I took out the spaces in the field part
hi, i get an error 'cant connect to local mysql server through socket /var/run/mysqld/mysqsld.sock'
when doing mysqldump –all-databases
im a noob
i see, always the same
what am i doing wrong?
lucox, can you get to the CLI rather than enter this via phpmyadmin?
no i haven't access
are you using a shared host?
The error (2002) Can't connect to … normally means that there is no MySQL server running on the system or that you are using an incorrect Unix socket filename or TCP/IP port number when trying to connect to the server.
what does that mean
lucox, I know, instead of using NOW() try using a value
Means it's firewalled probably.
Wait no.
it's local and running
Yeah, didn't read you bit before.
i dont know what a .sock file does
ttt- then you're trying to connect through the wrong socket file
It's a socket file.
how do i find the correct one?
Like a file based socket way of accessing MySQL.
Look at your MySQL config
And make sure file based sockets are enabled if that's what you want to use.
ttt-, have you changed your configuration at all from the default?
no but it came in linrails
ttt-, try running mysql
linrails did all the installing
please give me example of datetime value to put into …
lucox, '0000-00-00 00:00:00'
lucox, that will work, just use those zeros
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server hosting version for the right syntax to use near '' at line 3
runnign mysql gives the same error (cant connect to socket)
always the same
hey, when i go i the directory of mysql, it works
in*
Is there a way to set a dual PK? Like you can't have the same owner and template id's together twice?
Is there a way to set a dual PK? Like you can't have the same owner and template id's together twice?
