Mailing List
Home
Forum Home
MySQL General - General MySQL discussion
MaxDB - Everything about MaxDB, formerly known as SAP DB
MySQL++ - Programming with the C++ API to MySQL
MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP
ODBC - ODBC with the MySQL Connector/ODBC driver
Java Help - Mostly related to the MySQL Connector/J driver
Perl - Perl support for MySQL with DBI and DBD::mysql
GUI - MySQL GUI Tools
Announcement
Subjects
• mysql openssl Question
• mysql:it 's a db not a dbms how it 's possible?!
• Does the binary log enabling affect the MySQL performances?
• Strange behavior, Table Level Permission
• FULLTEXT query format question
• Preventing Duplicate Entries
• Comparing and writing out BLOBS
• Executing MySQL Commands From Within C Program
• Can 't access mysql after kernel upgrade
• Mysql 4 0 Oracle Stored Procedure Trigger Conversion
• Downgrade Mysql from 4 to 3 23
• MySQL Cluster Software
• mysql test 4 1 fails with the gis test
• ERROR 2002: Can 't connect to local MySQL server through socket
• Getting Identity after INSERT
• Update one field with more fields from another table
• ERROR 1045: Access denied for user: 'root@localhost ' (Using
password: NO)
• mysql have same function mthod as Oracle decode()
 
DELETE TRIGGER/DEFAULT SERIAL/DELETE KEY combination fails

DELETE TRIGGER/DEFAULT SERIAL/DELETE KEY combination fails

2006-08-08       - By James Prosser

 Back
Reply:     1     2  

Hi All,

My first foray into DELETE TRIGGER has struck a problem demonstrated by the
following SQL through websql into MaxDB
  Kernel     Kernel 7.6.00 Build 016-123-109-428
  Runtime Environment   W32/INTEL 7.6.00 Build 016-123-109-428


CREATE TABLE "myTable" ("myTableID" INTEGER KEY DEFAULT SERIAL)

CREATE TRIGGER "myTrigger"
          FOR "myTable"
        AFTER DELETE
      EXECUTE (RETURN;)

/* this insert not required to demonstrate problem */
INSERT INTO "myTable" ("myTableID") VALUES (1)

DELETE FROM "myTable" KEY "myTableID" = 1
/* Fails with message
  [MySQL MaxDB][SQLOD32 DLL][MaxDB] General error;-9111
  and the knldiag.err entries
2006-08-08 17:38:27      0xD1C ERR 20011 MOVECODE VAK506:23
2006-08-08 17:38:27      0xD1C ERR 20011 MOVECODE 2006-08-08 17:38:27 MOVECODE
Error 20011
2006-08-08 17:38:27      0xD1C ERR 20011 MOVECODE Bad parameter: limit source 11
destination 32768, source [0X7D6D9BA0]+12, destination [0X7F860498]+12, 3 bytes
2006-08-08 17:38:27      0xD1C ERR 51080 SYSERROR -9111 Move error
2006-08-08 17:38:27      0xD1C ERR 20011 MOVECODE VAK506:23
2006-08-08 17:38:27      0xD1C ERR 20011 MOVECODE 2006-08-08 17:38:27 MOVECODE
Error 20011
2006-08-08 17:38:27      0xD1C ERR 20011 MOVECODE Bad parameter: limit source 11
destination 32768, source [0X7D6D9BA0]+12, destination [0X7F860498]+12, 3 bytes
2006-08-08 17:38:27      0xD1C ERR 20011 MOVECODE  +
SAPDB_PascalMoveKernel.cpp:96
2006-08-08 17:38:27      0xD1C ERR 20011 MOVECODE  + 2006-08-08 17:38:27
MOVECODE Error 20013
2006-08-08 17:38:27      0xD1C ERR 20011 MOVECODE  -   Module VAK506 call index
23
*/

I have found two solutions:

/* A: Remove the default */
ALTER TABLE "myTable" COLUMN "myTableID" DROP DEFAULT

-OR-

/* B: Delete by WHERE clause rather than KEY */
DELETE FROM "myTable" WHERE "myTableID" = 1

As it happens, key values are always provided so losing DEFAULT SERIAL is an
option.

Before I proceed though, am I doing something silly here?  I can't see why the
trio of DEFAULT SERIAL, DELETE by KEY and a DELETE TRIGGER should be
problematic.

Either solution requires changes to our domain engine so I'd like to be sure it
actually needs to be done. And there are likely to be other solutions I haven't
considered.

If I went for B option, am I right in thinking MaxDB will execute the WHERE
DELETE on a Key field as efficiently as the explicit KEY syntax?

Thanks for any advice.

James Prosser

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=mysql@(protected)