Replication error with stored proc and triggers
Description:
I am using MySQL server as backend database supporting a telecomm platform
providing voice over ip services.
tables involved: acc, cdr, account, location2
Partial processing flow:
1. When a phone call is started. A record will be inserted into table acc
(method=INVITE). This will trigger an insert of a record into table cdr.
2. When the phone call is ended. Another record will be inserted into table
acc (method=BYE). This will trigger an update of the cdr record (endtime and
duration).
3. When table cdr is updated, a trigger will start. If cdr_duration is not
null, the account table will be updated with adjusted balance.
The process flow has been working fine. The database is replicated to
another MySQL server instance running on another machine.
Recently, I am encountering an error that caused the replication to stop.
IN show slave status, I am getting an error message:
Last_Error: Query caused different errors on master and
slave. Error on master: 'Can't update table '%-.64s' in stored
function/trigger because it is already used by statement which invoked this
stored function/trigger.' (1442), Error on slave: 'no error' (0). Default
database: 'opnser'. Query: 'insert into acc
(method,from_tag,to_tag,callid,sip_code,sip_reason,time,ani,did,dnis )
values
('BYE','3405618788-778336','516B0D10-1448','[EMAIL PROTECTED]
hnl.aloha.net','200','OK','2007-12-02
21:14:23','8088646143','818084473408','14378288809164968574')'
Skip counter: 0
Seconds_Behind_Master: NULL
The transaction causing error was completed: cdr record updated, account
balance adjusted.
The only problem is that replication process is frozen.
I have checked the application and it does not look like there is any
deadlock situation.
How-To-Repeat:
It would be difficult to reproduce the error. The creation of acc record is
the result of a voip software 'openser'. The error condition is random and
unpredictable.
Fix:
When this happen I need to re-establish the replication by making a copy
from master and do change master on the slave machine.
Submitter-Id: submitter ID
Originator:Richard Siy
Organization:
organization of PR author (multiple lines) Quattro Broadband, Inc.
MySQL support: none
Synopsis: synopsis of the problem (one line) Error causing
replication to stop.
Severity: [ non-critical | serious | critical ] (one line) serious
Priority: [ low | medium | high ] (one line) high
Category: mysql
Class: [ sw-bug | doc-bug | change-request | support ] (one line)
sw-bug
Release: mysql-5.0.27-standard (MySQL Community Edition - Standard
(GPL))
C compiler:gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2)
C++ compiler: gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2)
Environment:
machine, os, target, libraries (multiple lines)
System: Linux qbdbm1.qbnet 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005
i686 athlon i386 GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake
Compilation info: CC='gcc'
CFLAGS='-O2 -g -pipe -m32 -march=i386 -mtune=pentium4' CXX='gcc'
CXXFLAGS='-O2 -g -pipe -m32 -march=i386 -mtune=pentium4' LDFLAGS=''
ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Aug 17 2006 /lib/libc.so.6 - libc-2.3.4.so
-rwxr-xr-x 1 root root 1439335 Dec 20 2004 /lib/libc-2.3.4.so
Configure command: ./configure '--disable-shared'
'--with-server-suffix=-standard' '--without- embedded-server'
'--with-innodb' '--with-archive-storage-engine' '--without-bench'
'--with-zlib- dir=bundled' '--with-big-tables' '--enable-assembler'
'--enable-local-infile' '--with-mysqld- user=mysql'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-pic'
'--prefix=/' '--with- extra-charsets=complex' '--with-yassl'
'--exec-prefix=/usr' '--libexecdir=/usr/sbin' '-- libdir=/usr/lib'
'--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql'
'-- infodir=/usr/share/info' '--includedir=/usr/include'
'--mandir=/usr/share/man' '--enable-thread-safe- client'
'--with-comment=MySQL Community Edition - Standard (GPL)' '--with-readline'
'CC=gcc' 'CFLAGS= -O2 -g -pipe -m32 -march=i386 -mtune=pentium4'
'CXXFLAGS=-O2 -g -pipe -m32 -march=i386 - mtune=pentium4' 'CXX=gcc'
'LDFLAGS='
Looking forward to hear from you soon.
Thanks,
Richard Siy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with SQL query construction
Hi!
I have a SQL query construction question that I hope someone can help
me with. After comparing a bunch of DNA fragments (see name below) with
a larger reference sequence I get a ordered list ranked according to
similarities, and with start/stop co-ordinates where the fragments map
to the reference sequence:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | NULL |
| B|2 | 2 | 998 | NULL |
| C|4 | 1100 | 2000 | NULL |
| D|3 | 3050 | 4100 | NULL |
| E|5 | 2040 | 3000 | NULL |
| F|6 | 1102 | 2000 | NULL |
| G|7 | 1098 | 1998 | NULL |
| H|8 | 3048 | 4100 | NULL |
| I|9 | 3051 | 4102 | NULL |
+--+--+---+--+--+
A graphical representation of fragments mapped to the ref sequence:
ref
1 A--
2 B
3 D--
4 C--
5 E
6 F---
7 G---
8 H---
9 I---
Now, I want to group fragments in each overlapping position and sub-rank
them according to their rank in that position. The final table would
then look like:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | 1|
| B|2 | 2 | 998 | 2|
| C|4 | 1100 | 2000 | 1|
| D|3 | 3050 | 4100 | 1|
| E|5 | 2040 | 3000 | 1|
| F|6 | 1102 | 2000 | 2|
| G|7 | 1098 | 1998 | 3|
| H|8 | 3048 | 4100 | 2|
| I|9 | 3051 | 4102 | 3|
+--+--+---+--+--+
Is this possible to achieve using SQL queries alone (perhaps with GROUP
BY, nested SELECTs etc)?
I've managed to do this with a Perl-DBI script, but would much prefer to
do it completely with MySQL instead. The Perl code is below and below
that is the MySQL-dump of the test data set...
Many thanks in advance!
Marcus
while (@{$dbh-selectcol_arrayref(SELECT rank FROM test WHERE sub_rank
IS NULL)}) {
@null_sub_ranks = @{$dbh-selectcol_arrayref(SELECT rank FROM test
WHERE sub_rank IS NULL AND NOT (start=(SELECT stop FROM test WHERE rank
= (SELECT min(rank) FROM test WHERE sub_rank IS NULL)) OR stop =
(SELECT start FROM test WHERE rank = (SELECT min(rank) FROM test WHERE
sub_rank IS NULL};
for ($rank=0; $rank scalar(@null_sub_ranks); $rank++ ) {
$sub_rank = $rank + 1;
$dbh-do(UPDATE test SET sub_rank=$sub_rank WHERE rank=
$null_sub_ranks[$rank]);
}
}
-- MySQL dump 10.10
--
-- Host: localhostDatabase: bxb
-- --
-- Server version 5.0.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`name` text,
`rank` int(11) default NULL,
`start` int(11) default NULL,
`stop` int(11) default NULL,
`sub_rank` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `test`
--
/*!4 ALTER TABLE `test` DISABLE KEYS */;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES
('A',1,1,1000,NULL),('B',2,2,998,NULL),('C',4,1100,2000,NULL),('D',3,3050,4100,NULL),('E',5,2040,3000,NULL),('F',6,1102,2000,NULL),('G',7,1098,1998,NULL),('H',8,3048,4100,NULL),('I',9,3051,4102,NULL);
UNLOCK TABLES;
/*!4 ALTER TABLE `test` ENABLE KEYS */;
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with SQL query construction
-Original Message-
From: Marcus Claesson [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 7:49 AM
To: mysql@lists.mysql.com
Subject: Help with SQL query construction
Hi!
I have a SQL query construction question that I hope someone can help
me with. After comparing a bunch of DNA fragments (see name below) with
a larger reference sequence I get a ordered list ranked according to
similarities, and with start/stop co-ordinates where the fragments map
to the reference sequence:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | NULL |
| B|2 | 2 | 998 | NULL |
| C|4 | 1100 | 2000 | NULL |
| D|3 | 3050 | 4100 | NULL |
| E|5 | 2040 | 3000 | NULL |
| F|6 | 1102 | 2000 | NULL |
| G|7 | 1098 | 1998 | NULL |
| H|8 | 3048 | 4100 | NULL |
| I|9 | 3051 | 4102 | NULL |
+--+--+---+--+--+
A graphical representation of fragments mapped to the ref sequence:
ref
1 A--
2 B
3 D--
4 C--
5 E
6 F---
7 G---
8 H---
9 I---
Now, I want to group fragments in each overlapping position and sub-
rank
them according to their rank in that position. The final table would
then look like:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | 1|
| B|2 | 2 | 998 | 2|
| C|4 | 1100 | 2000 | 1|
| D|3 | 3050 | 4100 | 1|
| E|5 | 2040 | 3000 | 1|
| F|6 | 1102 | 2000 | 2|
| G|7 | 1098 | 1998 | 3|
| H|8 | 3048 | 4100 | 2|
| I|9 | 3051 | 4102 | 3|
+--+--+---+--+--+
Is this possible to achieve using SQL queries alone (perhaps with GROUP
BY, nested SELECTs etc)?
I've managed to do this with a Perl-DBI script, but would much prefer
to
do it completely with MySQL instead. The Perl code is below and below
that is the MySQL-dump of the test data set...
Many thanks in advance!
Marcus
while (@{$dbh-selectcol_arrayref(SELECT rank FROM test WHERE sub_rank
IS NULL)}) {
@null_sub_ranks = @{$dbh-selectcol_arrayref(SELECT rank FROM test
WHERE sub_rank IS NULL AND NOT (start=(SELECT stop FROM test WHERE
rank
= (SELECT min(rank) FROM test WHERE sub_rank IS NULL)) OR stop =
(SELECT start FROM test WHERE rank = (SELECT min(rank) FROM test WHERE
sub_rank IS NULL};
for ($rank=0; $rank scalar(@null_sub_ranks); $rank++ ) {
$sub_rank = $rank + 1;
$dbh-do(UPDATE test SET sub_rank=$sub_rank WHERE rank=
$null_sub_ranks[$rank]);
}
}
-- MySQL dump 10.10
--
-- Host: localhostDatabase: bxb
-- --
-- Server version 5.0.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`name` text,
`rank` int(11) default NULL,
`start` int(11) default NULL,
`stop` int(11) default NULL,
`sub_rank` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `test`
--
/*!4 ALTER TABLE `test` DISABLE KEYS */;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES
('A',1,1,1000,NULL),('B',2,2,998,NULL),('C',4,1100,2000,NULL),('D',3,30
50,4100,NULL),('E',5,2040,3000,NULL),('F',6,1102,2000,NULL),('G',7,1098
,1998,NULL),('H',8,3048,4100,NULL),('I',9,3051,4102,NULL);
UNLOCK TABLES;
/*!4 ALTER TABLE `test` ENABLE KEYS */;
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
I'd say perl is
Re: Help with SQL query construction
Marcus,
I've managed to do this with a Perl-DBI script, but
would much prefer to do it completely with MySQL instead.
You could port it to a recursive stored procedure. It would probably be
slower, and what would you have gained?
PB
Marcus Claesson wrote:
Hi!
I have a SQL query construction question that I hope someone can help
me with. After comparing a bunch of DNA fragments (see name below) with
a larger reference sequence I get a ordered list ranked according to
similarities, and with start/stop co-ordinates where the fragments map
to the reference sequence:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | NULL |
| B|2 | 2 | 998 | NULL |
| C|4 | 1100 | 2000 | NULL |
| D|3 | 3050 | 4100 | NULL |
| E|5 | 2040 | 3000 | NULL |
| F|6 | 1102 | 2000 | NULL |
| G|7 | 1098 | 1998 | NULL |
| H|8 | 3048 | 4100 | NULL |
| I|9 | 3051 | 4102 | NULL |
+--+--+---+--+--+
A graphical representation of fragments mapped to the ref sequence:
ref
1 A--
2 B
3 D--
4 C--
5 E
6 F---
7 G---
8 H---
9 I---
Now, I want to group fragments in each overlapping position and sub-rank
them according to their rank in that position. The final table would
then look like:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | 1|
| B|2 | 2 | 998 | 2|
| C|4 | 1100 | 2000 | 1|
| D|3 | 3050 | 4100 | 1|
| E|5 | 2040 | 3000 | 1|
| F|6 | 1102 | 2000 | 2|
| G|7 | 1098 | 1998 | 3|
| H|8 | 3048 | 4100 | 2|
| I|9 | 3051 | 4102 | 3|
+--+--+---+--+--+
Is this possible to achieve using SQL queries alone (perhaps with GROUP
BY, nested SELECTs etc)?
I've managed to do this with a Perl-DBI script, but would much prefer to
do it completely with MySQL instead. The Perl code is below and below
that is the MySQL-dump of the test data set...
Many thanks in advance!
Marcus
while (@{$dbh-selectcol_arrayref(SELECT rank FROM test WHERE sub_rank
IS NULL)}) {
@null_sub_ranks = @{$dbh-selectcol_arrayref(SELECT rank FROM test
WHERE sub_rank IS NULL AND NOT (start=(SELECT stop FROM test WHERE rank
= (SELECT min(rank) FROM test WHERE sub_rank IS NULL)) OR stop =
(SELECT start FROM test WHERE rank = (SELECT min(rank) FROM test WHERE
sub_rank IS NULL};
for ($rank=0; $rank scalar(@null_sub_ranks); $rank++ ) {
$sub_rank = $rank + 1;
$dbh-do(UPDATE test SET sub_rank=$sub_rank WHERE rank=
$null_sub_ranks[$rank]);
}
}
-- MySQL dump 10.10
--
-- Host: localhostDatabase: bxb
-- --
-- Server version 5.0.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`name` text,
`rank` int(11) default NULL,
`start` int(11) default NULL,
`stop` int(11) default NULL,
`sub_rank` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `test`
--
/*!4 ALTER TABLE `test` DISABLE KEYS */;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES
('A',1,1,1000,NULL),('B',2,2,998,NULL),('C',4,1100,2000,NULL),('D',3,3050,4100,NULL),('E',5,2040,3000,NULL),('F',6,1102,2000,NULL),('G',7,1098,1998,NULL),('H',8,3048,4100,NULL),('I',9,3051,4102,NULL);
UNLOCK TABLES;
/*!4 ALTER TABLE `test` ENABLE KEYS */;
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL University session on December 6
Hi, this Thursday, Sergey Petrunia will give a MySQL University session on: Interaction Between Optimizer and Storage Engine Please register for this session by filling in your name on the session Wiki page that you can find here: http://forge.mysql.com/wiki/Interaction_Between_Optimizer_and_Storage_Engine Registering is not required but appreciated. Thanks! That Wiki page also contains a section to post questions. Please use it! Those planning to attend a MySQL University session for the very first time should probably read the instructions for attendees, http://forge.mysql.com/wiki/Instructions_for_Attendees. -- Regards, Stefan Hinz [EMAIL PROTECTED], MySQL AB Documentation Manager Berlin, Germany (UTC +1:00/winter, +2:00/summer) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to drop index if exists
On Nov 29, 2007 4:34 AM, Adam Lipscombe [EMAIL PROTECTED] wrote: Folks How can one conditionally drop an index in MySQL? Googling shows that the drop index does not support an if exists qualifier - apparently a bug has been raised but as far as I know its not fixed yet. Does anyone know of a work-around? TIA - Adam Sent my first response late at night and not the community... And the response also sucked. DROP INDEX is mapped to ALTER TABLE tbl_name DROP INDEX. ALTER IGNORE TABLE tbl_name DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to drop index if exists
You may want to check to see if the index exists first.
Just query the table INFORMATION_SCHEMA.STATISTICS:
SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = 'given schema'
AND table_name = 'given table name'
AND index_name = 'given index name';
This returns the number of columns the index contains.
If this query returns zero(0), then the index does not exist.
If this query returns a positive number, then call
ALTER TABLE tbl-name DROP INDEX index-name;
You may want to write this stored procedure to do this.
Here is the Code (change the 'util' schema to the schema you want) :
DELIMITER $$
DROP PROCEDURE IF EXISTS `util`.`sp_DropIndex` $$
CREATE PROCEDURE `util`.`sp_DropIndex` (tblSchema VARCHAR(64),tblName
VARCHAR(64),ndxName VARCHAR(64))
BEGIN
DECLARE IndexColumnCount INT;
DECLARE SQLStatement VARCHAR(256);
SELECT COUNT(1) INTO IndexColumnCount
FROM information_schema.statistics
WHERE table_schema = tblSchema
AND table_name = tblName
AND index_name = ndxName;
IF IndexColumnCount 0 THEN
SET SQLStatement = CONCAT('ALTER TABLE `',tblSchema,'`.`',tblName,'`
DROP INDEX `',ndxName,'`');
SET @SQLStmt = SQLStatement;
PREPARE s FROM @SQLStmt;
EXECUTE s;
DEALLOCATE PREPARE s;
END IF;
END $$
DELIMITER ;
Give it a try !!!
-Original Message-
From: Rob Wultsch [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 12:51 PM
To: Adam Lipscombe
Cc: mysql@lists.mysql.com
Subject: Re: how to drop index if exists
On Nov 29, 2007 4:34 AM, Adam Lipscombe [EMAIL PROTECTED] wrote:
Folks
How can one conditionally drop an index in MySQL?
Googling shows that the drop index does not support an if exists
qualifier - apparently a bug
has been raised but as far as I know its not fixed yet.
Does anyone know of a work-around?
TIA - Adam
Sent my first response late at night and not the community... And the
response also sucked.
DROP INDEX is mapped to ALTER TABLE tbl_name
DROP INDEX.
ALTER IGNORE TABLE tbl_name
DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal?
--
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: how to drop index if exists
I actually suggested that last night, and thought better of it because
the alter ignore was so much simpler...
On Dec 3, 2007 11:18 AM, Rolando Edwards [EMAIL PROTECTED] wrote:
You may want to check to see if the index exists first.
Just query the table INFORMATION_SCHEMA.STATISTICS:
SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = 'given schema'
AND table_name = 'given table name'
AND index_name = 'given index name';
This returns the number of columns the index contains.
If this query returns zero(0), then the index does not exist.
If this query returns a positive number, then call
ALTER TABLE tbl-name DROP INDEX index-name;
You may want to write this stored procedure to do this.
Here is the Code (change the 'util' schema to the schema you want) :
DELIMITER $$
DROP PROCEDURE IF EXISTS `util`.`sp_DropIndex` $$
CREATE PROCEDURE `util`.`sp_DropIndex` (tblSchema VARCHAR(64),tblName
VARCHAR(64),ndxName VARCHAR(64))
BEGIN
DECLARE IndexColumnCount INT;
DECLARE SQLStatement VARCHAR(256);
SELECT COUNT(1) INTO IndexColumnCount
FROM information_schema.statistics
WHERE table_schema = tblSchema
AND table_name = tblName
AND index_name = ndxName;
IF IndexColumnCount 0 THEN
SET SQLStatement = CONCAT('ALTER TABLE `',tblSchema,'`.`',tblName,'`
DROP INDEX `',ndxName,'`');
SET @SQLStmt = SQLStatement;
PREPARE s FROM @SQLStmt;
EXECUTE s;
DEALLOCATE PREPARE s;
END IF;
END $$
DELIMITER ;
Give it a try !!!
-Original Message-
From: Rob Wultsch [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 12:51 PM
To: Adam Lipscombe
Cc: mysql@lists.mysql.com
Subject: Re: how to drop index if exists
On Nov 29, 2007 4:34 AM, Adam Lipscombe [EMAIL PROTECTED] wrote:
Folks
How can one conditionally drop an index in MySQL?
Googling shows that the drop index does not support an if exists
qualifier - apparently a bug
has been raised but as far as I know its not fixed yet.
Does anyone know of a work-around?
TIA - Adam
Sent my first response late at night and not the community... And the
response also sucked.
DROP INDEX is mapped to ALTER TABLE tbl_name
DROP INDEX.
ALTER IGNORE TABLE tbl_name
DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your
goal?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Rob Wultsch
(480)223-2566
[EMAIL PROTECTED] (email/google im)
wultsch (aim)
[EMAIL PROTECTED] (msn)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Customer Survey :: an exercise in frustration
I started to take this but gave up, here's a few suggestions: 1. you have questions that need a none option 2. NEVER make the questions mandatory. like #32. I don't give a shit about most of those things, but you force me to check '1' for all of them. that's wasting my time. 3. how many damn questions are in this freakin' survey! i'm on #32 and many are multipart!!! I'm done dude. I've got no more time to be wasting on this. UGHHH!!! _ From: MySQL [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 11:02 AM To: Daevid Vincent Subject: MySQL Customer Survey New MySQL User Survey Plus, a Chance to Win a free pass to the 2008 MySQL Conference Expo! Help shape the future direction of MySQL Products! MySQL is conducting a User Survey. Your answers are very important to us. All information you provide will be kept confidential and will only be reported on in aggregate form. Also, eligible users who complete this survey will be entered into a random drawing to win a free pass to the 2008 MySQL Conference Expo. To take this survey (please note that Zoomerang is the tool we use for our surveys), please go to: http://www.zoomerang.com/survey.zgi We look forward to and appreciate your participation. Sincerely, MySQL Cupertino City Center Building 20450 Stevens Creek Blvd., Suite 350 Cupertino, CA 95014 _ Copyright C 2007 MySQL AB| http://www.mysql.com?elq=51B8EE3B2FBD4796A7CE3ACEAEE3899E www.mysql.com Please http://now.eloqua.com/u.asp?s=287elq=51B8EE3B2FBD4796A7CE3ACEAEE3899E click here to unsubscribe http://now.eloqua.com/e/FooterImages/FooterImage1.aspx?elq=51B8EE3B2FBD4796 A7CE3ACEAEE3899Esiteid=287 http://now.eloqua.com/e/FooterImages/FooterImage2.aspx?elq=51B8EE3B2FBD4796 A7CE3ACEAEE3899Esiteid=287
Re: MySQL Customer Survey :: an exercise in frustration
It occurs to me that this sounds very much like my experience the last couple of times I tried to take the survey... As a long-time user of MySQL, I want to make my contribution, but this survey is a dog's breakfast every time. Perhaps someone at AB might take notice this round and try to fix it? - michael dykman On Dec 3, 2007 3:33 PM, Daevid Vincent [EMAIL PROTECTED] wrote: I started to take this but gave up, here's a few suggestions: 1. you have questions that need a none option 2. NEVER make the questions mandatory. like #32. I don't give a shit about most of those things, but you force me to check '1' for all of them. that's wasting my time. 3. how many damn questions are in this freakin' survey! i'm on #32 and many are multipart!!! I'm done dude. I've got no more time to be wasting on this. UGHHH!!! _ From: MySQL [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 11:02 AM To: Daevid Vincent Subject: MySQL Customer Survey New MySQL User Survey Plus, a Chance to Win a free pass to the 2008 MySQL Conference Expo! Help shape the future direction of MySQL Products! MySQL is conducting a User Survey. Your answers are very important to us. All information you provide will be kept confidential and will only be reported on in aggregate form. Also, eligible users who complete this survey will be entered into a random drawing to win a free pass to the 2008 MySQL Conference Expo. To take this survey (please note that Zoomerang is the tool we use for our surveys), please go to: http://www.zoomerang.com/survey.zgi We look forward to and appreciate your participation. Sincerely, MySQL Cupertino City Center Building 20450 Stevens Creek Blvd., Suite 350 Cupertino, CA 95014 _ Copyright C 2007 MySQL AB| http://www.mysql.com?elq=51B8EE3B2FBD4796A7CE3ACEAEE3899E www.mysql.com Please http://now.eloqua.com/u.asp?s=287elq=51B8EE3B2FBD4796A7CE3ACEAEE3899E click here to unsubscribe http://now.eloqua.com/e/FooterImages/FooterImage1.aspx?elq=51B8EE3B2FBD4796 A7CE3ACEAEE3899Esiteid=287 http://now.eloqua.com/e/FooterImages/FooterImage2.aspx?elq=51B8EE3B2FBD4796 A7CE3ACEAEE3899Esiteid=287 -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
speical characters in text column
hi friends, i searched on google but not file a solution. is there a way that i can store special characters (return, new line, tab, etc) into mysql (ver. 4.1.12) table with text type? i use textarea tag for user to enter the text. my goal is to store/display EXACTLY what user entered in the textarea tag. Thanks, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Customer Survey :: an exercise in frustration
Hi, And, what's up with the vertical white lines on dark blue background as a section divider? My eyes actually hurt looking at the survey page. It makes it *very* difficult to read the questions. Regards, Jeremy Daevid Vincent wrote: I started to take this but gave up, here's a few suggestions: 1. you have questions that need a none option 2. NEVER make the questions mandatory. like #32. I don't give a shit about most of those things, but you force me to check '1' for all of them. that's wasting my time. 3. how many damn questions are in this freakin' survey! i'm on #32 and many are multipart!!! I'm done dude. I've got no more time to be wasting on this. UGHHH!!! _ From: MySQL [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 11:02 AM To: Daevid Vincent Subject: MySQL Customer Survey New MySQL User Survey Plus, a Chance to Win a free pass to the 2008 MySQL Conference Expo! Help shape the future direction of MySQL Products! MySQL is conducting a User Survey. Your answers are very important to us. All information you provide will be kept confidential and will only be reported on in aggregate form. Also, eligible users who complete this survey will be entered into a random drawing to win a free pass to the 2008 MySQL Conference Expo. To take this survey (please note that Zoomerang is the tool we use for our surveys), please go to: http://www.zoomerang.com/survey.zgi We look forward to and appreciate your participation. Sincerely, MySQL Cupertino City Center Building 20450 Stevens Creek Blvd., Suite 350 Cupertino, CA 95014 _ Copyright C 2007 MySQL AB| http://www.mysql.com?elq=51B8EE3B2FBD4796A7CE3ACEAEE3899E www.mysql.com Please http://now.eloqua.com/u.asp?s=287elq=51B8EE3B2FBD4796A7CE3ACEAEE3899E click here to unsubscribe http://now.eloqua.com/e/FooterImages/FooterImage1.aspx?elq=51B8EE3B2FBD4796 A7CE3ACEAEE3899Esiteid=287 http://now.eloqua.com/e/FooterImages/FooterImage2.aspx?elq=51B8EE3B2FBD4796 A7CE3ACEAEE3899Esiteid=287 -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Customer Survey :: an exercise in frustration
I just finished it. There were 56 questions, not counting the final give us your name stuff for the prize. Most of the questions were mandatory. This seems to be a fill out the form type of survey system rather than a self-adjusting system based on the answers to your previous questions. Many of the questions asked were for things I had already said I didn't use... Regards, Jeremy Daevid Vincent wrote: I started to take this but gave up, here's a few suggestions: 1. you have questions that need a none option 2. NEVER make the questions mandatory. like #32. I don't give a shit about most of those things, but you force me to check '1' for all of them. that's wasting my time. 3. how many damn questions are in this freakin' survey! i'm on #32 and many are multipart!!! I'm done dude. I've got no more time to be wasting on this. UGHHH!!! _ From: MySQL [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 11:02 AM To: Daevid Vincent Subject: MySQL Customer Survey New MySQL User Survey Plus, a Chance to Win a free pass to the 2008 MySQL Conference Expo! Help shape the future direction of MySQL Products! MySQL is conducting a User Survey. Your answers are very important to us. All information you provide will be kept confidential and will only be reported on in aggregate form. Also, eligible users who complete this survey will be entered into a random drawing to win a free pass to the 2008 MySQL Conference Expo. To take this survey (please note that Zoomerang is the tool we use for our surveys), please go to: http://www.zoomerang.com/survey.zgi We look forward to and appreciate your participation. Sincerely, MySQL Cupertino City Center Building 20450 Stevens Creek Blvd., Suite 350 Cupertino, CA 95014 _ Copyright C 2007 MySQL AB| http://www.mysql.com?elq=51B8EE3B2FBD4796A7CE3ACEAEE3899E www.mysql.com Please http://now.eloqua.com/u.asp?s=287elq=51B8EE3B2FBD4796A7CE3ACEAEE3899E click here to unsubscribe http://now.eloqua.com/e/FooterImages/FooterImage1.aspx?elq=51B8EE3B2FBD4796 A7CE3ACEAEE3899Esiteid=287 http://now.eloqua.com/e/FooterImages/FooterImage2.aspx?elq=51B8EE3B2FBD4796 A7CE3ACEAEE3899Esiteid=287 -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: speical characters in text column
You need to escape the data read from the form. I'm sure that whatever programming language you are using has some function to do this. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 3:09 PM To: mysql@lists.mysql.com Subject: speical characters in text column hi friends, i searched on google but not file a solution. is there a way that i can store special characters (return, new line, tab, etc) into mysql (ver. 4.1.12) table with text type? i use textarea tag for user to enter the text. my goal is to store/display EXACTLY what user entered in the textarea tag. Thanks, T. Hiep -- 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]
Re: MySQL Customer Survey :: an exercise in frustration
At 13:19 -0800 3/12/07, Jeremy Cole wrote: I just finished it. There were 56 questions, not counting the final give us your name stuff for the prize. Most of the questions were mandatory. This seems to be a fill out the form type of survey system rather than a self-adjusting system based on the answers to your previous questions. Many of the questions asked were for things I had already said I didn't use... I didn't have any intention of doing this survey, but my interest was piqued by this little firestorm that's flared up around it. I was curious to have at least a quick look at it, so I did this... To take this survey (please note that Zoomerang is the tool we use for our surveys), please go to: http://www.zoomerang.com/survey.zgi ...which is immediately redirected to the rather incomplete looking http://www.zoomerang.com/survey-start.zgi?p= which, sure enough, simply returns the message: 'Cannot take survey, invalid URL, please check that the URL is correct and try again.' This has happened in two different browsers on my Mac (OmniWeb and Firefox) and that's enough for me - and I'm /definitely/ not firing up my Windows box just for this purpose! It does all seem a little unsatisfactory, doesn't it? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Anyone who is disturbed by the idea of newts in a nightclub is potentially dangerous. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Maybe a bit OT: MySQL forum recommendation?
My apologies in advance if this is a bit off topic, but... On a rather old site we have a dreadful old bulletin board system based on Matt Wright's WWWBoard - all horrid text files and ancient Perl code. We want to replace that with a decent forum system based on MySQL and PHP, but there's no money so it'll have to be a free one. We also need to be able to roll it into our existing database by adding more tables, rather than adding a separate database. Naturally, the easier it is to set up the better. And the real icing on the cake would be to find a script for converting between WWWBoard and whichever one we choose, though I suspect that might be too much to ask. I should be able to roll my own converter script if need be. The host we're using has PHP 4.3.10 and MySQL 3.23.56, so that rules out some I've found, but these seem promising from a quick look: FUD Forum miniBB phpBB PunBB SEO-Board Simple Machines Forum (SMF) Vanilla Any recommendations from among these or others? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Anyone who is disturbed by the idea of newts in a nightclub is potentially dangerous. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maybe a bit OT: MySQL forum recommendation?
On 12/3/07, Chris Sansom [EMAIL PROTECTED] wrote: My apologies in advance if this is a bit off topic, but... On a rather old site we have a dreadful old bulletin board system based on Matt Wright's WWWBoard - all horrid text files and ancient Perl code. We want to replace that with a decent forum system based on MySQL and PHP, but there's no money so it'll have to be a free one. We also need to be able to roll it into our existing database by adding more tables, rather than adding a separate database. Naturally, the easier it is to set up the better. And the real icing on the cake would be to find a script for converting between WWWBoard and whichever one we choose, though I suspect that might be too much to ask. I should be able to roll my own converter script if need be. The host we're using has PHP 4.3.10 and MySQL 3.23.56, so that rules out some I've found, but these seem promising from a quick look: FUD Forum miniBB phpBB PunBB SEO-Board Simple Machines Forum (SMF) Vanilla Any recommendations from among these or others? You might check out vBulletin. They might waive the license fee if you ask nicely, or they may also have a policy when it is in support of open-source software.
RE: MySQL Customer Survey :: an exercise in frustration
Well, there was some other parameters with a special XDHFSKL324234234 style code at the end, which I figured was tied to me directly somehow, so I didn't want to post that -- for exactly this reason below. *wink* The survey was emailed to us. -Original Message- From: Chris Sansom [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 1:54 PM To: Jeremy Cole; Daevid Vincent Cc: mysql@lists.mysql.com; 'MySQL' Subject: Re: MySQL Customer Survey :: an exercise in frustration At 13:19 -0800 3/12/07, Jeremy Cole wrote: I just finished it. There were 56 questions, not counting the final give us your name stuff for the prize. Most of the questions were mandatory. This seems to be a fill out the form type of survey system rather than a self-adjusting system based on the answers to your previous questions. Many of the questions asked were for things I had already said I didn't use... I didn't have any intention of doing this survey, but my interest was piqued by this little firestorm that's flared up around it. I was curious to have at least a quick look at it, so I did this... To take this survey (please note that Zoomerang is the tool we use for our surveys), please go to: http://www.zoomerang.com/survey.zgi ...which is immediately redirected to the rather incomplete looking http://www.zoomerang.com/survey-start.zgi?p= which, sure enough, simply returns the message: 'Cannot take survey, invalid URL, please check that the URL is correct and try again.' This has happened in two different browsers on my Mac (OmniWeb and Firefox) and that's enough for me - and I'm /definitely/ not firing up my Windows box just for this purpose! It does all seem a little unsatisfactory, doesn't it? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Anyone who is disturbed by the idea of newts in a nightclub is potentially dangerous. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
