#mysql
30 December 2007
Total 14 pages. You are browsing page 5/14.
First :: Prev :: [...] [3] [4] [5] [6] [7] [...] :: Next :: Last
03:44 <****> heres what i typed last
03:44 <****> 'delete from user where User like "postfix%';
03:44 <****> ERROR 1064 (42000): 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 ''delete from user where User like "postfix%'' at line 1
03:44 <****> 'select * from user where User like 'postfix%'Connection to 192.168.0.172 closed by remote host.
03:44 <****> Connection to 192.168.0.172 closed.
03:44 <****> Lazarus delete is a reserved word you could try `delete`
03:45 <****> and then the connection died
03:45 <****> Lazarus: too many info sources can be confusing. I will leave you to it. Good luck.
03:45 <****> threnody i didn't mean to interrupt you.
03:45 <****> just pointing out that he is blindly following every character you type
03:46 <****> and not using the brain too much
03:47 <****> Lazarus basically it couldn't die because of what you just typed in
03:47 <****> oh
03:48 <****> okay im back in again
03:48 <****> and im at the tables point showing the two users I want to delete
03:48 <****> so what came after that
03:55 <****> I'm having performance issue. I used to have a huge table with redundant strings (varchar) in the rows. I replaced those strings with integer code (one word = one integer code, no joins needed, I know those from the app)
03:55 <****> and I replaced varchar with smallint, now the same queries I had, take much longer
03:56 <****> NoiseFree: did you forget to add back the key?
03:56 <****> I have indexes on the rows and typically do a query like: SELECT rowX FROM Table WHERE row1 = '..' AND row2 = '..' AND row3 = '..' WHERE rowX IS NOT NULL GROUP BY rowX
03:56 <****> ORDER BY rowX
03:56 <****> I have the indexes still
03:57 <****> key?
03:57 <****> primary?
03:57 <****> same thing, an index on the column
03:57 <****> how many rows does it show to be scanning when you do an EXPLAIN on the query
03:58 <****> are you using a multi col index?
04:00 <****> my indexes are col-by-col since the queries vary a bit
04:00 <****> 1 sec I'll do EXPLAIN
04:00 <****> 1351 rows
04:01 <****> I have 1.7 million rows, so I guess it should be working
04:01 <****> it should be noted, that if I try the same query second time, it's fast, but i do NOT have query cache enabled
04:01 <****> is the index created " on demand " ?
04:01 <****> NoiseFree, query cache isn't the only cache. Could be disk cache (at least for MyISAM), or whatever.
04:02 <****> no but it has to be updated when you update
04:02 <****> Indexes are updated with every update to the table, and stored in files.
04:02 <****> hmm wait
04:02 <****> what on earth
04:02 <****> it's myisam
04:02 <****> those used to be unnodb
04:02 <****> innno
04:02 <****> I'm getting some odd behaviour with PHP4 and MySQL on Ubuntu Linux. I can mysql_connect('127.0.0.1:3306',...) but not mysql_connect('localhost:3306',...). I get errors saying it can
04:02 <****> ...'t connect to a socket
04:02 <****> Any ideas?
04:03 <****> oh fuck
04:03 <****> i deleted a user
04:04 <****> and it was the wrong one
04:04 <****> ...
04:04 <****> how could this've happened?
04:04 <****> innodb tables magically become myisam
04:04 <****> NoiseFree, ALTER TABLE?
04:04 <****> Bad memory?
04:04 <****> I altered it all right, but
04:04 <****> etfb: localhost assumes socket
04:04 <****> I don't remember altering that. But Navicat is wonky like this
04:05 <****> ok I'll try again with inno thanks for your help everyone.
04:05 <****> InnoDB would probably make no big difference to your query, I think?
04:05 <****> hi, how to query my db to find all the foreign keys associated to this table
04:05 <****> i mean to a table
04:05 <****> mikegriffin: Without the :3306 it connects happily to the socket using 127.0.0.1
04:05 <****> mikegriffin: Does the IP address default to the port?
04:06 <****> etfb: 127 uses a tcp sock, localhost uses a unix socket
04:06 <****> Simetrical, possibly, but at least it's one more variable back to how it was originally
04:06 <****> NoiseFree: if there are updates, it could be locked
04:06 <****> or example, i have a table called projects (which has client_id) and another table clients with primary key id
04:06 <****> mikegriffin: OK, that's new to me. I don't know the difference. TCP socket is specified by port number? So "TCP socket" == "port", in effect?
04:06 <****> close enough
04:07 <****> unix socket is a file
04:07 <****> mikegriffin: Shouldn't I be able to override the default by specifying a port in the server connection name?
04:08 <****> hello, can anyone help me?
04:08 <****> please
04:08 <****> arpan: use a gui
04:09 <****> mysql-admin for example
04:09 <****> erm
04:09 <****> i can't because i am writing a scrpit that does a different thing when it sees foreign keys
04:09 <****> isn't there a command for it?
04:10 <****> mikegriffin: i am writing a script that generates code for me
04:11 <****> so i want to do different things if it has a foreign key
04:11 <****> looking in information_schema
04:13 <****> ok
04:14 <****> grr im wipeing the box
04:14 <****> its eaiser that way
04:15 <****> ill bbl
04:15 <****> its gunna take a while'
04:15 <****>'mysql';
04:16 <****> or maybe select * from information_schema.KEY_COLUMN_USAGE where information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA IS NOT NULL;
04:17 <****> thanks mikegriffin i should be able to extract the info from these 2 queries
04:18 <****> i didnt think it would be so complex
04:18 <****> Anybody here fairly knowledgeable in regards to the python bindings for MySQL?
04:18 <****> just ask
04:18 <****> I'm trying to find a way to have the table name in python as a variable but so far can't seem to find a way.
04:19 <****> Using %s isn't supported and having the entire MySQL query be a string doesn't seem to work either.
04:19 <****> huh
04:19 <****> show your code
04:19 <****> just use a normal format strnig
04:20 <****> I tried that. Didn't work.
04:20 <****> hwat happened
04:22 <****> It said to check my syntax but I know that the syntax is correct as I've manually entered the query in the query browser.
04:23 <****> Hm, I may have found a solution...
04:24 <****> arpan: just look at the table structure for information_schema.KEY_COLUMN_USAGE
Total 14 pages. You are browsing page 5/14.
First :: Prev :: [...] [3] [4] [5] [6] [7] [...] :: Next :: Last