Can anybody please let me know the counterpart functions for SetSRID(),Makebox2D(),Distance_Sphere() of PostGIS in MySQL
Hello
I want to use functions - SetSRID(),Makebox2D(),Distance_Sphere() of
Post GIS in MySQL. Can please let me know the similar functions in
MySQL.
Environment
===
Operating System : Red Hat Linux 3.4
Database :
MySQL Version 5.0.51
PostgreSQL 8.2.6 with PostGIS 1.2.1
Functions which we are trying to use
a) To retreive the distance between two geometries (co-ordinates).
Basically trying to find and use a function in MySQL similar to
distance_sphere in PostGIS
Query Used
===
SELECT X(s.geom),Y(s.geom),todofuken||shigun||kuchoson AS address,
(distance_sphere(s.geom,GeomFromText('POINT(135 35)',4326))) AS distance
FROM todofuken_tbl t,shikuchoson_tbl s.
Error Occurred
==
ERROR 1305 (42000): FUNCTION .DISTANCE _SPHERE does not exist
b) To find and use a function in MySQL similar to MakeBox2D function of
PostGIS
Query Used
===
select MakeBox2D(GeomFromText('POINT(135.0
34.0)',4326),GeomFromText('POINT(135.5 34.5)',4326));
Error Occurred
==
ERROR 1305 (42000): FUNCTION blog_gisdb.MakeBox2D does not exist
c) To find and use a function in MySQL similar to MakeBox2D function of
PostGIS
Query Used
===
SELECT uri,X(geom),Y(geom),ROUND(dist,2) AS distance FROM (
SELECT
uri,geom,distance(geom,GeomFromText('POINT(135.25 34.25)',4326)) AS dist
FROM geom_tbl g,uri_tbl u WHERE g.id=u.id
AND geom SetSRID(MakeBox2D(GeomFromText('POINT(135.0
34.0)',4326),GeomFromText('POINT(135.5 34.5)',4326)),4326)
) AS d ORDER BY dist;
Error Occurred
==
ERROR 1305 (42000): FUNCTION blog_gisdb.SetSRID does not exist
Can somebody please let me know if similar functions exist and if they
exist, please provide me the names of those functions.
Regards
Ahmad
Please do not print this email unless it is absolutely necessary. Spread
environmental awareness.
The information contained in this electronic message and any attachments to
this message are intended for the exclusive use of the addressee(s) and may
contain proprietary, confidential or privileged information. If you are not the
intended recipient, you should not disseminate, distribute or copy this e-mail.
Please notify the sender immediately and destroy all copies of this message and
any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should
check this email and any attachments for the presence of viruses. The company
accepts no liability for any damage caused by any virus transmitted by this
email.
www.wipro.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update to last 5.0 GA
Hi all, I want to know the main differences between mysql 5.0.41 and MySQL 5.0.51a(last GA release) I read this page : http://dev.mysql.com/doc/refman/5.0/en/mysql-nutshell.html but there is nothing about the developments made by such update... thnaks all...
Re: How to delete duplicates with full row comapring
On Feb 4, 2008 7:17 PM, Baron Schwartz [EMAIL PROTECTED] wrote: On Feb 4, 2008 11:36 AM, Artifex Maximus [EMAIL PROTECTED] wrote: Hello! I am looking for an easy solution for eliminate duplicates but on a row level. I am having 2 tables. 1 destination for all not duplicated info (a) and 1 for input table (b) which might have duplicates related to table a. Now I am using this kind of insert: INSERT INTO a SELECT fields FROM b WHERE ... NOT EXISTS ( SELECT * FROM a WHERE (a.a,a.b,a.c,a.d)=(b.a,b.b,b.c,b.d) ) Looks like it works but is it any solution for row level compare without naming all fields? For example WHERE (a.*) = (b.*) instead of currently used (a.a,a.b,...)=(b.a,b.b,...). You could use this: INSERT .. SELECT .. FROM b NATURAL LEFT OUTER JOIN a WHERE a.primary_key_column IS NULL Thank you very much! Sounds good and I read mysql documentation (it is easier because I know now what I am look for...). Unfortunately I do not have primary_key_column. I am trying to merge phone call logs from six different machines to make a master call log. Individual logs have unique id field, which might use as primary key but unique id is unique only on one machine and other machine may have the same unique id (as it actually happens). So it is only unique within database but not within machines. There is a big mess around and call logs might shared between machines means that only record that contains same values for all fields is unique. Because it is an automatic call-out campaign log I have to include some extra information in master log. Is it problem for 'natural join' that any side have extra fields? I read in the documentation that natural join must have the same fields on both table but I got no error on missing fields now. Is it compare automatically only fields that have the same name? bye, a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update to last 5.0 GA
Thomas Raso wrote: Hi all, I want to know the main differences between mysql 5.0.41 and MySQL 5.0.51a(last GA release) I read this page : http://dev.mysql.com/doc/refman/5.0/en/mysql-nutshell.html but there is nothing about the developments made by such update... thnaks all... Try this: http://dev.mysql.com/doc/refman/5.0/en/releasenotes-cs-5-0.html Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to locate SetSRID(),Makebox2D(),Distance_Sphere()
Rakesh!
The distance_sphere and makebox2d functions are specific to postgis.
MySQL GIS Implementation is based on the Open GIS Simple SQL
Specification (read more on www.opengeospatial.com). The SetSRID is also
not in the specification as far as I know (it should be, but I can't
find it), but many GIS implementations does seem to implement it. Not so
MySQL though, as MySQL currently only supports on SRID, the flat or
euclidean geometry.
Still, there are a few things missing in the current MySQL GIS
implementation compared to the Simple SQL spec. Many of these (not
including SRID support though) is available in special version that you
can read about here: http://forge.mysql.com/wiki/GIS_Functions where you
can also find download links.
Best regards
Anders Karlsson
[EMAIL PROTECTED] wrote:
Hi All
I looking for the stated functions. Earlier I was using postGIS in that
they are present i am wondering if I can get similar kind of method in
MySql.I am Using Mysql 5.0.51a version.
1. I am trying to retrieve the distance between two geometries in the
table using the following syntax:
SELECT X(s.geom),Y(s.geom),todofuken||shigun||kuchoson AS address,
(distance_sphere(s.geom,GeomFromText('POINT(135 35)',4326))) AS
distance
FROM todofuken_tbl t,shikuchoson_tbl s.
ERROR 1305 (42000): FUNCTION .DISTANCE _SPHERE does not exist
2. I am trying to excute this query in mysql :
select MakeBox2D(GeomFromText('POINT(135.0 34.0)',4326),
GeomFromText('POINT(135.5 34.5)',4326));
ERROR 1305 (42000): FUNCTION blog_gisdb.MakeBox2D does not exist
3. I am trying to excute this query in mysql :
SELECT uri,X(geom),Y(geom),ROUND(dist,2) AS distance FROM (SELECT
uri,geom,distance(geom,GeomFromText('POINT(135.25 34.25)',4326)) AS dist
FROM geom_tbl g,uri_tbl u WHERE g.id=u.id AND geom
SetSRID(MakeBox2D(GeomFromText('POINT(135.0 34.0)',4326),
GeomFromText('POINT(135.5 34.5)',4326)),4326)) AS d ORDER BY dist;
ERROR 1305 (42000): FUNCTION blog_gisdb.SetSRID does not exist
How I find above functions in MYSQL and how i use mysql GIS Extension.
Thanking You in inticipation
Rakesh
Please do not print this email unless it is absolutely necessary. Spread
environmental awareness.
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
www.wipro.com
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED])
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/ /_/\_, /___/\___\_\___/ Stockholm
___/ www.mysql.com Cellphone: +46 708 608121
Skype: drdatabase
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lock the row selected by a session and lock those rows for other sessions
Select for update is not working like the way I expected it: FIRST SESSION: mysql start transaction; Query OK, 0 rows affected (0.00 sec) mysql select video_id from videos_innodb where state='QUEUE' limit 5 FOR UPDATE; +--+ | video_id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) So, you notice I didn't COMMIT those rows... Check the second session opened: mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql select video_id from videos_innodb where state='QUEUE' limit 5 FOR UPDATE; +--+ | video_id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ Same thing, I dont want anyway other session to be able to get that selection until I commit. Any other suggestion? Michael Dykman wrote: SELECT FOR UPDATE On Feb 4, 2008 4:58 PM, Frederic Belleudy [EMAIL PROTECTED] wrote: Hi there, I'm new with innodb and I'm not sure it's good to go with innodb for my personnal goals. Ok, let's assume I 've a table and want to select the first 10 rows from that table but I want to be sure that no other scripts will select the same rows I've previously got by the first script. How can I do that? my table contains one primary key. Let's say id is the column name. So my first script is running and select the ids: 1, 2, 3 10 Then that script will play with the returned ids. In the same time, I'm running a second script and do the same select. But I don't want him to get the first 10 ids. The only thing I can think about is to lock WRITE my table. I taught innodb was able to automatically lock the selected rows and not allowed any other script to get the same rows until it's commited... Tks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lock the row selected by a session and lock those rows for other sessions
1) is the table InnoDB? 2) is AUTOCOMMIT on? On Feb 5, 2008 8:44 AM, Frederic Belleudy [EMAIL PROTECTED] wrote: Select for update is not working like the way I expected it: FIRST SESSION: mysql start transaction; Query OK, 0 rows affected (0.00 sec) mysql select video_id from videos_innodb where state='QUEUE' limit 5 FOR UPDATE; +--+ | video_id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) So, you notice I didn't COMMIT those rows... Check the second session opened: mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql select video_id from videos_innodb where state='QUEUE' limit 5 FOR UPDATE; +--+ | video_id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ Same thing, I dont want anyway other session to be able to get that selection until I commit. Any other suggestion? Michael Dykman wrote: SELECT FOR UPDATE On Feb 4, 2008 4:58 PM, Frederic Belleudy [EMAIL PROTECTED] wrote: Hi there, I'm new with innodb and I'm not sure it's good to go with innodb for my personnal goals. Ok, let's assume I 've a table and want to select the first 10 rows from that table but I want to be sure that no other scripts will select the same rows I've previously got by the first script. How can I do that? my table contains one primary key. Let's say id is the column name. So my first script is running and select the ids: 1, 2, 3 10 Then that script will play with the returned ids. In the same time, I'm running a second script and do the same select. But I don't want him to get the first 10 ids. The only thing I can think about is to lock WRITE my table. I taught innodb was able to automatically lock the selected rows and not allowed any other script to get the same rows until it's commited... Tks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld segfault (InnoDB)
Hi, I have a MySQL 5.0.54 instance with a 5.0.22 datadir from a corrupted filesystem, backed up what was readable and am now trying to get it back to life. Unfortunately, mysqld segfaults on startup: --8-- InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 080205 15:43:41 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Page directory corruption: supremum not pointed to 080205 15:43:41 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 00 080205 15:43:41 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Page number (if stored to page already) 0, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 InnoDB: Page directory corruption: supremum not pointed to 080205 15:43:41 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 00 080205 15:43:41 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Page number (if stored to page already) 0, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 080205 15:43:41InnoDB: Error: trying to access a stray pointer 0x3607fff8 InnoDB: buf pool start is at 0xb607, end at 0xb707 InnoDB: Probable reason is database corruption or memory InnoDB: corruption. If this happens in an InnoDB database recovery, see InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: how to force recovery. 080205 15:43:41InnoDB: Assertion failure in thread 3081746096 in file ./../include/buf0buf.ic line 268 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. 080205 15:43:41 - mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=0 read_buffer_size=262144 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 76800 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbff49e58, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x817f84e 0x83f8c8b 0x8379b6b 0x8383f41 0x8333455 0x8334bc4 0x8320bfb 0x831fa8c 0x823b9a8 0x822c674 0x817e8b5 0x8182038 0xb7b1283c 0x80fd131 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. --8-- Here is the resolved stack dump: --8-- 0x817f84e handle_segfault + 772 0x83f8c8b page_cur_search_with_match + 1975 0x8379b6b btr_cur_search_to_nth_level + 1404 0x8383f41 btr_pcur_open_on_user_rec + 98 0x8333455 dict_load_foreigns + 3133 0x8334bc4 dict_load_sys_table + 100 0x8320bfb dict_boot + 2717 0x831fa8c innobase_start_or_create_for_mysql + 5653 0x823b9a8 _Z13innobase_initv + 872 0x822c674 _Z7ha_initv + 582 0x817e8b5 unireg_abort + 559 0x8182038 main + 1162 0xb7b0583c _end + -1354045076 0x80fd131 _start + 33 --8-- I also tried innodb_force_recovery=6 but it didn't help. What should I do next? Submit a bug report? TIA. -- Regards, Wolfram Schlich
Re: lock the row selected by a session and lock those rows for other sessions
(Re-CCing the MySQL list) It is not locking the entire table. It's locking the rows you're selecting. If you don't want the second session to hang and wait, then you need to tell it to lock different rows. It might help if you explain what you're trying to accomplish. It sounds like you're trying to build a message queue or something, which is a problem that has been solved already. On Feb 5, 2008 9:44 AM, Frederic Belleudy [EMAIL PROTECTED] wrote: 1) damn, I had to recompile myssql because the innodb option wasnt enabled 2) ok now it seems to work almost perfectly. I'm doing the same query on the first session but the second one is waiting for the other session to commit after the selection. Is there a way to tell mysql not locking the table entirely, just the row from the selection? Tks a lot for your help!! Baron Schwartz wrote: 1) is the table InnoDB? 2) is AUTOCOMMIT on? On Feb 5, 2008 8:44 AM, Frederic Belleudy [EMAIL PROTECTED] wrote: Select for update is not working like the way I expected it: FIRST SESSION: mysql start transaction; Query OK, 0 rows affected (0.00 sec) mysql select video_id from videos_innodb where state='QUEUE' limit 5 FOR UPDATE; +--+ | video_id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) So, you notice I didn't COMMIT those rows... Check the second session opened: mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql select video_id from videos_innodb where state='QUEUE' limit 5 FOR UPDATE; +--+ | video_id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ Same thing, I dont want anyway other session to be able to get that selection until I commit. Any other suggestion? Michael Dykman wrote: SELECT FOR UPDATE On Feb 4, 2008 4:58 PM, Frederic Belleudy [EMAIL PROTECTED] wrote: Hi there, I'm new with innodb and I'm not sure it's good to go with innodb for my personnal goals. Ok, let's assume I 've a table and want to select the first 10 rows from that table but I want to be sure that no other scripts will select the same rows I've previously got by the first script. How can I do that? my table contains one primary key. Let's say id is the column name. So my first script is running and select the ids: 1, 2, 3 10 Then that script will play with the returned ids. In the same time, I'm running a second script and do the same select. But I don't want him to get the first 10 ids. The only thing I can think about is to lock WRITE my table. I taught innodb was able to automatically lock the selected rows and not allowed any other script to get the same rows until it's commited... Tks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error: You can't specify target table '...' for update in FROM clause
Hi, I've got an error message from MySQL 5.0 that I don't understand. UPDATE message_revision SET HasData = 1, Data = (SELECT Data FROM message_revision WHERE MessageId = 7 AND RevisionNumber = 5) WHERE MessageId = 7 AND RevisionNumber = 6 SQL error: [SQLSTATE:HY000, 1093] You can't specify target table 'message_revision' for update in FROM clause What went wrong? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lock the row selected by a session and lock those rows for other sessions
You said: It is not locking the entire table. It's locking the rows you're selecting. If you don't want the second session to hang and wait, then you need to tell it to lock different rows. Well if you read my message, I dont want another script to select the rows that are selected by another opened session but i want other script to be able to select other rows without hangin on Got me? Yes it's like a pool... Baron Schwartz wrote: (Re-CCing the MySQL list) It is not locking the entire table. It's locking the rows you're selecting. If you don't want the second session to hang and wait, then you need to tell it to lock different rows. It might help if you explain what you're trying to accomplish. It sounds like you're trying to build a message queue or something, which is a problem that has been solved already. On Feb 5, 2008 9:44 AM, Frederic Belleudy [EMAIL PROTECTED] wrote: 1) damn, I had to recompile myssql because the innodb option wasnt enabled 2) ok now it seems to work almost perfectly. I'm doing the same query on the first session but the second one is waiting for the other session to commit after the selection. Is there a way to tell mysql not locking the table entirely, just the row from the selection? Tks a lot for your help!! Baron Schwartz wrote: 1) is the table InnoDB? 2) is AUTOCOMMIT on? On Feb 5, 2008 8:44 AM, Frederic Belleudy [EMAIL PROTECTED] wrote: Select for update is not working like the way I expected it: FIRST SESSION: mysql start transaction; Query OK, 0 rows affected (0.00 sec) mysql select video_id from videos_innodb where state='QUEUE' limit 5 FOR UPDATE; +--+ | video_id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) So, you notice I didn't COMMIT those rows... Check the second session opened: mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql select video_id from videos_innodb where state='QUEUE' limit 5 FOR UPDATE; +--+ | video_id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ Same thing, I dont want anyway other session to be able to get that selection until I commit. Any other suggestion? Michael Dykman wrote: SELECT FOR UPDATE On Feb 4, 2008 4:58 PM, Frederic Belleudy [EMAIL PROTECTED] wrote: Hi there, I'm new with innodb and I'm not sure it's good to go with innodb for my personnal goals. Ok, let's assume I 've a table and want to select the first 10 rows from that table but I want to be sure that no other scripts will select the same rows I've previously got by the first script. How can I do that? my table contains one primary key. Let's say id is the column name. So my first script is running and select the ids: 1, 2, 3 10 Then that script will play with the returned ids. In the same time, I'm running a second script and do the same select. But I don't want him to get the first 10 ids. The only thing I can think about is to lock WRITE my table. I taught innodb was able to automatically lock the selected rows and not allowed any other script to get the same rows until it's commited... Tks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Deleting duplicate rows via temporary table either hung or taking way way too long [SOLVED]
-Original Message- From: Chris W [mailto:[EMAIL PROTECTED] Sent: Monday, February 04, 2008 9:05 PM To: Daevid Vincent; MYSQL General List Subject: Re: Deleting duplicate rows via temporary table either hung or taking way way too long Daevid Vincent wrote: DROP TABLE IF EXISTS `dupes`; CREATE TEMPORARY TABLE dupes SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) 1 ORDER BY BID; LOCK TABLES buglog WRITE; SELECT * FROM buglog WHERE LogID IN (SELECT LogID FROM dupes) LIMIT 10; #DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes); UNLOCK TABLES; The problem is the SELECT (DELETE) is either taking way too long to return or it's hung. I don't sit there long enough to figure it out. It seems like it shouldn't take as long as I wait. If I run the delete version, my buglog table count never decreases in the time I wait. I am pretty sure I have does this in the past and having an index on the temporary table made it amazingly faster. I assume the LogID field has an index in the other table already, if not you will want to add an index for that field in that table too. The easiest way is to add the index with your create temporary table statement and then do an ... INSERT INTO dupes (SELECT .) WOW! You are right! That's silly. It's a table with a single column. All unique. Anyways, here's the magic incantation that worked for me: DROP TABLE IF EXISTS `dupes`; CREATE TEMPORARY TABLE dupes SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) 1 ORDER BY BID; ALTER TABLE `dupes` ADD INDEX `LogID` ( `LogID` ); #//This index is critical. DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes); DROP TABLE IF EXISTS `dupes`; Trying to use the LOCK TABLES didn't work for me for some reason, but I didn't care enough to try and debug why. I just wanted the rows gone. :) Thanks C.W. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting duplicate rows via temporary table either hung or taking way way too long [SOLVED]
Daevid Vincent wrote: WOW! You are right! That's silly. It's a table with a single column. All unique. With out the index MySQL doesn't know they are unique. Anyways, here's the magic incantation that worked for me: DROP TABLE IF EXISTS `dupes`; CREATE TEMPORARY TABLE dupes SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) 1 ORDER BY BID; ALTER TABLE `dupes` ADD INDEX `LogID` ( `LogID` ); #//This index is critical. DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes); DROP TABLE IF EXISTS `dupes`; I think what happens if the index isn't there on the dupes table, MySQL looks at every row in the buglog table and then does a sequential search in the dupes table for that LogID. So if there there are say 100,000 in bug log and say 1000 in dupes that would be 100,000 x 1,000 = 100 million compares. If it were to do it the other way around, it would be faster. Take every record in the dupes table and then use the index in the buglog table to find the row that matches the LogID. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: You can't specify target table '...' for update in FROM clause
Hi, On Feb 5, 2008 11:26 AM, Yves Goergen [EMAIL PROTECTED] wrote: Hi, I've got an error message from MySQL 5.0 that I don't understand. UPDATE message_revision SET HasData = 1, Data = (SELECT Data FROM message_revision WHERE MessageId = 7 AND RevisionNumber = 5) WHERE MessageId = 7 AND RevisionNumber = 6 SQL error: [SQLSTATE:HY000, 1093] You can't specify target table 'message_revision' for update in FROM clause What went wrong? You can't select from a table you're updating at the same time. What at the same time means is a bit unclear unless you're one of the MySQL developers ;-) However, you can do multi-table updates like this: UPDATE tbl AS a INNER JOIN tbl AS b ON SET a.col = b.col If you absolutely need the subquery, there's a workaround, but it's ugly for several reasons, including performance: UPDATE tbl SET col = ( SELECT ... FROM (SELECT FROM) AS x); The nested subquery in the FROM clause creates an implicit temporary table, so it doesn't count as the same table you're updating. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: You can't specify target table '...' for update in FROM clause
On 05.02.2008 23:25 CE(S)T, Baron Schwartz wrote: You can't select from a table you're updating at the same time. What at the same time means is a bit unclear unless you're one of the MySQL developers ;-) Yes, Paul DuBois already replied to me off-list. Now I found that documentation part and understand that MySQL cannot do this. (Haven't tested whether other DBMS can, would be pointless anyway.) My goal was to copy some potentially large BLOB from one record to another in the same table, nothing more. I have now chosen the way to fetch it from the database and have my application just write it back again. I wanted to avoid this unnecessary copying around. However, you can do multi-table updates like this: UPDATE tbl AS a INNER JOIN tbl AS b ON SET a.col = b.col That sounds interesting, however, I couldn't find it in PostgreSQL's and SQLite's reference. Is this a MySQL extension over the SQL standard? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: You can't specify target table '...' for update in FROM clause
Yves Goergen wrote: On 05.02.2008 23:25 CE(S)T, Baron Schwartz wrote: You can't select from a table you're updating at the same time. What at the same time means is a bit unclear unless you're one of the MySQL developers ;-) Yes, Paul DuBois already replied to me off-list. Now I found that documentation part and understand that MySQL cannot do this. (Haven't tested whether other DBMS can, would be pointless anyway.) My goal was to copy some potentially large BLOB from one record to another in the same table, nothing more. I have now chosen the way to fetch it from the database and have my application just write it back again. I wanted to avoid this unnecessary copying around. Update table set blob2_field=blob1_field; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
