Repair/add spatial index on a spatial column
Dear all, Is repair by sort working for building spatial indexes on spatial columns? I have a table with a spatial column for storing geospatial data. My table is rather big (18M records, 7GB disk space) When I want to add a spatial index CREATE SPATIAL INDEX geomindex ON arcs(geometry), I end up to a repair from keycache process state for a quite long time (I killed it after several hours). I tried to increase the myisam_max_sort_file_size to 30GB (I have the disk space) but it didn't help. If I try on a smaller table (90K records, 100MB), with the same parameter set to 30GB, I still have the repair by keycache issue. So this is not likely a temp file size limitation. If I add an index on another column of my 18M records table, repair using sort works. I am using mysql 5.0.32 Thanks! Etienne
Re: Send INSERT statement from MS SQL SERVER to MySQL
Save the sql statement to text file,then execute it inside the mysql shell. On Feb 12, 2008 2:00 PM, Mário Gamito [EMAIL PROTECTED] wrote: Hi, Is it possible to send an INSERT statement from a Windows server running MS SQL SERVER 2005 to a Linux box running MySQL ? If so, how ? Do I need any special tools ? Any help would be appreciated. Warm Regards, Mário Gamito -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: finding duplicate key
Maybe you should view this article: http://blog.chinaunix.net/u/29134/showart_375303.html On Feb 12, 2008 8:32 AM, Grant Limberg [EMAIL PROTECTED] wrote: Something like this should help you find all of the dupes select email_address from table group by email_address having count(*)1; On Feb 11, 2008 4:23 PM, Ferindo Middleton [EMAIL PROTECTED] wrote: I have a table (customers) without a primary key. I want to make the email_address field the primary key, only problem is, several records already share the same email_address How do I write a query which will show me all the instances where email_address is duplicated throughout the table. -- Ferindo -- Grant Limberg [EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: stored procedure, parameter type help needed
Just use the type varchar.I always use it in my regular life.
On Feb 11, 2008 4:44 PM, Magne Westlie [EMAIL PROTECTED] wrote:
Jerry Schwartz wrote:
SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT
uid
FROM temp_uids);
[JS] Couldn't you replace the WHERE user_id IN (SELECT uid FROM
temp_uids)
with a simple JOIN? If IN is badly optimized, as I've read here,
wouldn't
that be more efficient? Or am I (as usual) missing something?
I may be the one missing something :-). I have tried to think out of the
box and use other solutions, but haven't come up with a way that works
without using IN.
The query are to be used in a calendar-ish application, for finding when
people are free to attend meetings. The ids I send as parameter is the
ids of users that I want to check availability for. The id-list may
contain between 1 and 50 user ids.
Maybe I could use
JOIN ... ON (user_id=1 OR user_id=2 OR user_id=5...) (?)
but as far as I understand, I'd still have to generate this list as a
string because I do not know how many users to check for, and then
CONCAT the query, PREPARE etc. as described in Peter Brawley's email.
Then I think I prefere using IN.
As for the optimization of IN, I've read the following in the manual:
The search for the item then is done using a binary search. This means
IN is very quick if the IN value list consists entirely of constants.
(
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in
)
, so if I am to rely on the doc, it should be very quick the way I use
it.
Thanks for your comment Jerry.
Magne
DROP PREPARE stmt;
END; ::
DELIMITER ;
CALL get_users('(2), (3)');
---
MW
Peter Brawley wrote:
Hi Magne
...the query I actually want to use this in, is a 100 line query
with
lots of arguments.
I don't feel to good about creating it into a bunch of strings (16)
that I have to
concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to
assemble
the query in the app layer.
Also, I was moving the query into a stored procedure because I
wanted
to make
the request fast, and the concatenating and string handling takes
some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about
creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I
wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single
quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
Re: R: rename database in 4.1
Nanni Claudio wrote: You can try to dump-to-sql the whole DB, Create a new DB and import the sql-dump in the new DB. Aloha! Claudio -Messaggio originale- Da: Thomas Raso [mailto:[EMAIL PROTECTED] Inviato: martedì 12 febbraio 2008 17.20 A: mysql@lists.mysql.com Oggetto: rename database in 4.1 hi list, how can I rename a database with full innodb tables ? The version is 4.1 Thnaks all Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto * This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person. You can also use the rename table command by creating the new database name then use rename dbname.table newdbname.table. You can list multiple tables. You could script it. Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to find the statement was successfull in procedure
Hi List, I am trying to write a procedure. I want to know whether the last statement updated correctly. Then I can add the count. anybody can help me. Saravanan Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 on OS X Leopard
Does it also fix the problem with the MySQL 5 Admin GUI (located in System Preferences) or does one have to use launchd manually every- time from the command line? -Unnsse On Feb 11, 2008, at 8:28 PM, Grant Limberg wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I found this a while back and it seems to work just fine. It uses launchd rather than the old /Library/StartupItems folder http://blog.tomatocheese.com/archives/2007/11/1/migrating_mysql_to_mac_os_x_leopard/ Regards, Grant Limberg [EMAIL PROTECTED] On Feb 11, 2008, at 6:34 PM, Unnsse Khan wrote: Hello there, I am having problems running MySQL 5 on OS X Leopard... Nothing happens when I try to start MySQL in System Preferences. From the command line, this is the error I receive (when trying to start MySQL): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) On the MySQL 5 downloads site, it doesn't have a version for Leopard. Are there any workarounds for getting MySQL 5 running on Leopard? Happy programming, Unnsse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (Darwin) iD8DBQFHsSBWIAfARU4EW68RAkO2AJ4r6qedXM6zwuzVwsSNuoEnbuJbDQCeJdGT XC/14Qt+oiJtidm1u9p63Iw= =TzER -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
crash
Can someone explain this? Thanks. 080212 15:54:39 mysqld started 080212 15:54:39 InnoDB: Started; log sequence number 0 43743 080212 15:54:40 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.54-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution Number of processes running now: 0 080212 17:23:24 mysqld restarted 080212 17:23:25 InnoDB: Started; log sequence number 0 43743 080212 17:23:25 [Note] Recovering after a crash using mysql-bin 080212 17:23:25 [Note] Starting crash recovery... 080212 17:23:25 [Note] Crash recovery finished. /usr/libexec/mysqld: Out of memory (Needed 463827968 bytes) /usr/libexec/mysqld: Out of memory (Needed 347870208 bytes) /usr/libexec/mysqld: Out of memory (Needed 260901888 bytes) /usr/libexec/mysqld: Out of memory (Needed 195674112 bytes) /usr/libexec/mysqld: Out of memory (Needed 146755584 bytes) 080212 17:23:26 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.54-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution Number of processes running now: 0 080212 20:45:03 mysqld restarted 080212 20:45:04 InnoDB: Started; log sequence number 0 43743 080212 20:45:04 [Note] Recovering after a crash using mysql-bin 080212 20:45:08 mysqld ended 080212 21:08:21 mysqld started 080212 21:08:22 InnoDB: Started; log sequence number 0 43743 080212 21:08:22 [Note] Recovering after a crash using mysql-bin 080212 21:08:23 [Note] Starting crash recovery... 080212 21:08:23 [Note] Crash recovery finished. 080212 21:08:23 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.54-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution [EMAIL PROTECTED] ~]# date Tue Feb 12 21:09:19 IST 2008 [EMAIL PROTECTED] ~]# Internal Virus Database is out-of-date. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.4/1146 - Release Date: 22/11/2007 18:55
Crashed InnoDB
We had a power outage, now the mysql wont start at all. Here is the err file output... Any help on how to recover? 080212 11:35:50 mysqld started 080212 11:35:50 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... 080212 11:35:50 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 115 2637413615. InnoDB: Doing recovery: scanned up to log sequence number 115 2637626081 080212 11:35:50 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 080212 11:35:51 - 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=2093056 max_used_connections=0 max_connections=2500 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3012828 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=0xbf3feaf8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80d4205 0x835537c 0x82c8b43 0x82c97dc 0x8294835 0x8295489 0x82851fd 0x82b02cd 0x8203f89 0x834fcb5 0x8388daa 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://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. You are running a statically-linked LinuxThreads binary on an NPTL system. This can result in crashes on some distributions due to LT/NPTL conflicts. You should either build a dynamically-linked binary, or force LinuxThreads to be used with the LD_ASSUME_KERNEL environment variable. Please consult the documentation for your distribution on how to do that. 080212 11:35:51 mysqld ended
locking rows with innodb
Hello, I'm having some problems to figure out how I could handle my
problem with innodb.
my table is innodb engine:
CREATE TABLE `videos_innodb` (
`video_id` int(10) unsigned NOT NULL auto_increment,
`client_id` int(10) unsigned default NULL,
`client_id_upload` int(11) NOT NULL default '0',
`state`
enum('GET','QUEUE','AVAILABLE','UPLOAD','ERROR','QUEUE_TRANSCODING')
default 'GET',
`input_file_type_id` int(10) unsigned default NULL,
`output_file_type_id` int(10) unsigned default NULL,
`input_file_name` varchar(150) NOT NULL,
`output_file_name` varchar(150) NOT NULL,
`date_inserted` date default NULL,
`time_inserted` time default NULL,
`date_available` date default '-00-00',
`time_available` time default '00:00:00',
`time_start` time NOT NULL,
`time_end` time NOT NULL,
PRIMARY KEY (`video_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12916 DEFAULT CHARSET=latin1
Ok, the column state is really important for my example. I'd like to
start multiple times the same script to convert videos. But I dont want
them to select the same rows for each sessions that select where
state='QUEUE'.
so I tried to use select ... for update with 2 different sessions. I've
set in each of them autocommit=0;
session 1
session 2
set @@autocommit=0;
set @@autocommit=0;
begin;
begin;
select video_id from videos_innodb where state='QUEUE' limit 10 FOR UPDATE;
= returns me 10 videos with the state='QUEUE'
select video_id from videos_innodb where state='QUEUE' limit 10 FOR UPDATE;
= it's waiting for the first to session to commit, so I cannot get
other videos with the same state!!
commit;
= I get 10 video_id
How can I tell mysql to lock only rows that are selected and allow other
sessions to query the table without be locking on the entire table?
Tks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to find the statement was successfull in procedure
if row_count()0 then select 'Successfully update'; else select 'Failed update.'; endif; On Feb 13, 2008 7:42 AM, Saravanan [EMAIL PROTECTED] wrote: Hi List, I am trying to write a procedure. I want to know whether the last statement updated correctly. Then I can add the count. anybody can help me. Saravanan Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: locking rows with innodb
On Feb 12, 2008 12:28 PM, Frederic Belleudy [EMAIL PROTECTED] wrote: select video_id from videos_innodb where state='QUEUE' limit 10 FOR UPDATE; = it's waiting for the first to session to commit, so I cannot get other videos with the same state!! commit; = I get 10 video_id How can I tell mysql to lock only rows that are selected and allow other sessions to query the table without be locking on the entire table? It is only locking the rows it selected. Your problem is that both queries select the same rows. The common way to handle this is to change the state of the rows to something else like 'PROCESSING' in order to remove them from the queue. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: rename database in 4.1
You can try to dump-to-sql the whole DB, Create a new DB and import the sql-dump in the new DB. Aloha! Claudio -Messaggio originale- Da: Thomas Raso [mailto:[EMAIL PROTECTED] Inviato: martedì 12 febbraio 2008 17.20 A: mysql@lists.mysql.com Oggetto: rename database in 4.1 hi list, how can I rename a database with full innodb tables ? The version is 4.1 Thnaks all Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto * This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rename database in 4.1
hi list, how can I rename a database with full innodb tables ? The version is 4.1 Thnaks all
Re: Send INSERT statement from MS SQL SERVER to MySQL
Mário Gamito [EMAIL PROTECTED] wrote on 02/12/2008 01:00:25 AM: Hi, Is it possible to send an INSERT statement from a Windows server running MS SQL SERVER 2005 to a Linux box running MySQL ? If so, how ? Do I need any special tools ? Any help would be appreciated. Warm Regards, Mário Gamito We use MS SQL Server 2000 and MySQL and move data using DTS and an ODBC connection to MySQL. You can also try to set up SQL Server transactional replication to an ODBC data source. I experimented with this a while back and couldn't make it work, but it might be easier in MSSQL 2005. Donna
RE: crash
Unfortuantely the server crashed and I had to manually boost it It happened before and I would like to know why so it doesn’t again I don’t have any innodb database on the system, only mysiam Do you think adding ram might help ? From: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 13, 2008 8:05 AM To: Eli Shemer Subject: Re: crash Hi, The below information shows that innodb data get crashed and then started the recovery of data. During recovery of data there was a memory shortage (Out of memory). At last recovery gets completed. Thanks, Prajapati On Feb 13, 2008 12:44 AM, Eli Shemer HYPERLINK mailto:[EMAIL PROTECTED][EMAIL PROTECTED] wrote: Can someone explain this? Thanks. 080212 15:54:39 mysqld started 080212 15:54:39 InnoDB: Started; log sequence number 0 43743 080212 15:54:40 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.54-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution Number of processes running now: 0 080212 17:23:24 mysqld restarted 080212 17:23:25 InnoDB: Started; log sequence number 0 43743 080212 17:23:25 [Note] Recovering after a crash using mysql-bin 080212 17:23:25 [Note] Starting crash recovery... 080212 17:23:25 [Note] Crash recovery finished. /usr/libexec/mysqld: Out of memory (Needed 463827968 bytes) /usr/libexec/mysqld: Out of memory (Needed 347870208 bytes) /usr/libexec/mysqld: Out of memory (Needed 260901888 bytes) /usr/libexec/mysqld: Out of memory (Needed 195674112 bytes) /usr/libexec/mysqld: Out of memory (Needed 146755584 bytes) 080212 17:23:26 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.54-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution Number of processes running now: 0 080212 20:45:03 mysqld restarted 080212 20:45:04 InnoDB: Started; log sequence number 0 43743 080212 20:45:04 [Note] Recovering after a crash using mysql-bin 080212 20:45:08 mysqld ended 080212 21:08:21 mysqld started 080212 21:08:22 InnoDB: Started; log sequence number 0 43743 080212 21:08:22 [Note] Recovering after a crash using mysql-bin 080212 21:08:23 [Note] Starting crash recovery... 080212 21:08:23 [Note] Crash recovery finished. 080212 21:08:23 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.54-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution [EMAIL PROTECTED] ~]# date Tue Feb 12 21:09:19 IST 2008 [EMAIL PROTECTED] ~]# Internal Virus Database is out-of-date. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.4/1146 - Release Date: 22/11/2007 18:55 -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: HYPERLINK http://ed-ventures-online.comed-ventures-online.com Email-id: HYPERLINK mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Internal Virus Database is out-of-date. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.4/1146 - Release Date: 22/11/2007 18:55 Internal Virus Database is out-of-date. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.4/1146 - Release Date: 22/11/2007 18:55
