Re: UPDATE and INSERT in one
[EMAIL PROTECTED] wrote: I want to update a table if it meets some conditions (already exist) and INSERT otherwise? Right now I am doing it this way: 1. SELECT the record 2. If it exist, I UPDATE it 3. If it does not exit, I INSERT a new record Could this be done in one query? I mean like conditional update/insert? Take a look at REPLACE: http://dev.mysql.com/doc/refman/5.0/en/replace.html -- David Precious http://blog.preshweb.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help please: SELECT in binlog?
Fionn Behrens wrote: On Do, 2007-04-19 at 13:57 -0400, Jay Pipes wrote: You can have both, AFAIK. The general query log keeps all queries, including SELECTs. Binlog only has data-modifying queries. Thanks very much for your answer. Maybe the fact that binlogs apparently are quite different from normal text logs should be clearly mentioned somewhere in the docs. Especially the mysqlbinlog manpage might be a good place to mention that SELECT statements can not be restored with it. I think it's already fairly clearly stated that the binlog is only for replaying queries which would have modified the database. After all, why would you want to re-run a select query? From the first paragraph of the manual page describing the binlog: http://dev.mysql.com/doc/refman/5.0/en/binary-log.html The binary log contains all statements that update data or potentially could have updated it (for example, a DELETE which matched no rows). Statements are stored in the form of events that describe the modifications. I agree that the mysqlbinlog manpage doesn't clearly state it, but it does refer you to the section of the documentation about the binlog which does. Cheers Dave P -- David Precious http://blog.preshweb.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to present query time through perl
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 04 March 2004 08:58, Jonas Lindén wrote: Hello list, I am trying to find a way to retrive the time a certain select statement took for debug purposes. If I execute a select statement in the mysql console I get the time that select statement took to execute. I am searching for a way to retrive that value. Right now I am using the perl:dbi to connect to my mysql db. Has anyone managed to do this? I'm not sure whether you can retrieve the query time from the server, but you could always time it your end using the Benchmark module. For an example: use Benchmark; #.. set up DB connection etc ... $time0 = new Benchmark; # do the query $time1 = new Benchmark; print Done in . timestr(timediff($time1,$time0)); You could also investigate the Time::HiRes module to do an accurate time yourself. Cheers David P -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFARvUqrz7S5k65yyYRAm0JAKDHSh/pYQoH7IUdA4dbw1RurOwy8ACgsqSs 8avkvE/DXPwSmRa8O678xuw= =iCji -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert into x select * from x doesn't work
Hi In mysql 4.1 (and since mysql 4.0.14 or something like that) I believe it's documented that an insert into X select * from X should work. IIRC, the table you're INSERTing into cannot be the same table you SELECT from - you're trying to take records from the table 'foo' and insert them into the same table. mysql insert into foo.bar select id from foo.bar; ERROR 1066: Not unique table/alias: 'bar' mysql This error seems to support that idea - although its not a particularly friendly way of saying it. Try creating another table, and do the select from that. Hope this helps! David P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: the logic of full text search
No, there are just a few rows in my table, as I am still developing the program. Will it be better when the table is in regular use (and the number of rows will increase)? I believe so. This is what you're seeing - quoted from MySQL manual: -- start quote -- The search for the word MySQL produces no results in the above example, because that word is present in more than half the rows. As such, it is effectively treated as a stopword (that is, a word with zero semantic value). This is the most desirable behaviour -- a natural language query should not return every second row from a 1 GB table. A word that matches half of rows in a table is less likely to locate relevant documents. In fact, it will most likely find plenty of irrelevant documents. We all know this happens far too often when we are trying to find something on the Internet with a search engine. It is with this reasoning that such rows have been assigned a low semantic value in this particular dataset. -- end quote -- Hope that helps! David P -- David Precious http://www.preshweb.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: the logic of full text search
Hi, I am trying to understand the logic of full text search in mysql. I'm not using mysql 4. The search work OK, be it thast I get hits on certain words, whilst other words are discarded for some reason or other. Why is that. An example: I search in a text field for the word organisation. I get hits. When I search for the word scenario nothing is found. But I can see the word in the paragraphs by myself? Is there an explanation for this? AIUI, if a word occurs too many times (in more than x% of rows, I can't remember the logic used) then it's treated as a stop word. This means that words that appear in almost every row (like the, you etc) which would have no value to a search are ignored. I believe this is what's causing your problem. Do you have many records in the table you're doing a fulltext search on? IME it tends to work better with plenty of rows to work with. HTH! David P -- David Precious http://www.preshweb.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PhpMyadmin
Hi, I want to install phpMyAdmin on my windows 2000 box. Whare can I find the PhpMyAdmin software to download. http://www.phpmyadmin.net/ Google would have found that for you in seconds, it's the first result for a search for phpmyadmin. With all due respect, you really should try a little harder to find the answer yourself first before posting to a list about it. HTH David P -- David Precious http://www.preshweb.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3.23.51 Reference Manual needed
I am looking for the Reference Manual for MySQL 3.23.51 (or the latest of the prior ones). Can anyone help me? You should find the manuals etc at: http://www.mysql.com/documentation/index.html HTH! David P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL error code 1064
On Friday 25 July 2003 1:05 pm, Kenneth Illingsworth wrote: I cannot seem to find this in the MYSQL Reference. Are there other places I should check? It's a syntax error. A quick Google (http://www.google.com/search?q=mysql+error+1064) would have found it for you. The message text in my log started out with 'You have...'. But, it was truncated and I could not see the rest. It would have been saying You have an error in your SQL syntax near It occurred on a submitted insert statement originating in an application. The insert statement works ok directly on the MySQL server. It would appear that the application is generating the INSERT statement incorrectly. HTH! David Precious http://www.preshweb.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: varchar restriction
Andrew, ok ok I get it what you are saying if I change a tables attributes from varchar on one table it wont chnage another tables varchars attributes AND WHY THE F**K WOULD I EXPECT IT TO!, what I am asking is it necessary to make chnages to the other table. The answer to your question is: If you want the change to table A to also happen to table B, then YES, you'll need to make the changes to *both* tables - changing the field in table A will have *no* effect on table B. BTW please do mind your language - this is a public forum and there's no need for expletives like that, surely you can be a little more eloquent? Regards David P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump suddenly returns error 1064 [sorted]
snip mysqldump: Got error: 1064: You have an error in your SQL syntax near '404 READ /*!32311 LOCAL */' at line 1 when using LOCK TABLES Anyone got any ideas what could be causing this to happen? snip Table name :) It consists only of digits. Use -Q option of mysqldump. Aaahh! I can't believe I didn't get that one. I've been trying to figure it out for ages! Of course, it all makes sense now. And, looking back through the cron job output, it started doing it right around the time that table got created. Many thanks for your help Victoria, you've saved me from tearing out a considerable amount of hair! Cheers David P -- David Precious [EMAIL PROTECTED] http://www.preshweb.co.uk A mind is a terrible thing to ... er ... h Now playing: Oasis - Whatever (Live, Knebworth) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump suddenly returns error 1064
Hi all, I hope this isn't an obvious question, but I'm having a hard time figuring this one out. I have a cron job set up to run mysqldump regularly to dump my databases out to a flat file, which is then compressed and passed to our backup server by another script. The cron job runs the command: mysqldump -Aac --add-drop-table --all-databases --opt /tmp/mysqldump This had been working fine for ages. However, now I'm getting an error every time it runs, indicating that there's a problem with the SQL syntax - however, this is the SQL being written out by mysqldump, and nothing on the server has changed which I would expect to cause this - MySQL hasn't been reconfigured or upgraded or anything. The error I'm seeing is: mysqldump: Got error: 1064: You have an error in your SQL syntax near '404 READ /*!32311 LOCAL */' at line 1 when using LOCK TABLES Just for info: MySQL v3.23.51 on Slackware Linux 9.0 (on a high-performance dual-Xeon server). Anyone got any ideas what could be causing this to happen? Many thanks David P -- David Precious [EMAIL PROTECTED] http://www.preshweb.co.uk A clear conscience is usually the sign of a bad memory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
