Archive for January, 2010
Decoding 1005 on MySQL
If you’re using referential integrity on MySQL, you can easily run into this error with the InnoDB engine. It frequently seems to occur with an ALTER TABLE statement. It can mean many things but typically it means the data types don’t match between the foreign key and primary key column. Likewise, it can mean one of the column data types disagrees in a multiple-column foreign to multiple-column primary key constraint.
The error by itself isn’t very handy. This is a sample:
ERROR 1005 (HY000): Can't create table 'sampledb.#sql-4a0_2' (errno: 150) |
You can try SHOW WARNINGS but you’ll discover more about the error by running the following command as the root superuser:
mysql> SHOW engine innodb STATUS; |
It returns a dump of the InnoDB’s activity. You can see it by unfolding the complete log, if you’re interested in the details. The significant part of the log to solve this type of problem is:
------------------------ LATEST FOREIGN KEY ERROR ------------------------ 100130 17:16:57 Error IN FOREIGN KEY CONSTRAINT OF TABLE sampledb/#sql-4a0_2: FOREIGN KEY(member_type) REFERENCES common_lookup(common_lookup_id): Cannot find an INDEX IN the referenced TABLE WHERE the referenced COLUMNS appear AS the FIRST COLUMNS, OR COLUMN types IN the TABLE AND the referenced TABLE do NOT MATCH FOR CONSTRAINT. |
While this is more information, it isn’t necessarily enough to solve the problem when you’re new to MySQL. The first place you should look is whether the data types agree between the foreign key and primary key columns.
The most common variation that I’ve run into is where the primary key column uses a int unsigned data type and the foreign key column uses an int data type. It’s quite nice that the InnoDB Engine stops this cold. Naturally, you fix it by changing the foreign key data type to match the int unsigned data type.
Complete Log File ↓
If you want to see what the complete dump of the InnoDB status is, click on the title above.
===================================== 100130 17:34:04 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 0 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 11, signal count 11 Mutex spin waits 0, rounds 80, OS waits 2 RW-shared spins 18, OS waits 9; RW-excl spins 1, OS waits 0 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 100130 17:16:57 Error in foreign key constraint of table sampledb/#sql-4a0_2: FOREIGN KEY(member_type) REFERENCES common_lookup(common_lookup_id): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html for correct foreign key definition. ------------ TRANSACTIONS ------------ Trx id counter 0 12958 Purge done for trx's n:o < 0 12956 undo n:o < 0 0 History list length 7 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 3924 MySQL thread id 13, query id 800 localhost 127.0.0.1 root show engine innodb status ---TRANSACTION 0 12957, not started, OS thread id 3276 MySQL thread id 2, query id 797 localhost 127.0.0.1 student -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 136 OS file reads, 1112 OS file writes, 704 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 195193, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 9122680 Log flushed up to 0 9122680 Last checkpoint at 0 9122680 0 pending log writes, 0 pending chkp writes 678 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 58341818; in additional pool allocated 1481088 Dictionary memory allocated 78248 Buffer pool size 3008 Free buffers 2879 Database pages 128 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 126, created 2, written 425 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 3408, state: waiting for server activity Number of rows inserted 454, updated 6, deleted 0, read 6026 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ |
iPad Thoughts …
This is probably defensive because I’ve had to answer the question about two dozen times since the iPad product announcement. The question is naturally, what do yo think about the iPad?
My perspective is biased by the fact that I’ve been using both DOS/Windows PCs and Macs since the 1980s. They each have merits but in short, unlike the media, I have a bias toward Apple products. In fact, I’m an old NeXT system administrator (software gone from the scene because as rumors have it, Steve wouldn’t think of letting the company become ONLY a software company).
I think the idea of the iPad for eBooks is awesome, the features are terrific. It clearly is a better opportunity for my digital movies but a bit awkward because of its size.
I can’t travel with an iPad by itself because it doesn’t support Microsoft Excel, Word, or Visio. That means I’d have to have my MacBook Pro and iPad. Ouch, the security folks will go nuts at the airport, and my bags are now heavier by about 2 pounds. The iPad is 1.5 pounds but the charger has weight too.
I understand all the logic for the device but there’s an underlying assumption in placing everything on the web. Some data can’t be on the web because of legal limits. This goes to my sticking point. Apple’s Office Suite isn’t as robust as Microsoft’s Office Suite. Keynote is easier to use and easily preferred over PowerPoint, but Numbers isn’t even close to Excel (here Apple fails). The problem with Pages is that many companies have templates built around Word and there’s no easy migration back and forth.
Perhaps Apple will reach out to Power Excel users and invest in Numbers to bring it into this millennium; and maybe they’ll also fix the portability between Word and Pages too. For example, one company I work with insists that I use Word 2003 because they’ve never updated their templates to Word 2007 (easy to do through VMWare Fusion). Then, all that’s needed is a rock solid replacement for Visio on Mac OS X.
I think that I might buy one to experiment with, just so I’m current with the product and new features. I’ve also got some product ideas that I’d like to explore but I don’t think this is a home-run like the iPod and iPhone without vitualization software to enable Windows. As an afterthought, maybe the announcement this summer will be “you can have it all now” when they port most features to the core OS X operating system. That would induce me to upgrade my MacBook Pro, wouldn’t it get you to do so too?
Sample PL/SQL Cursor Loops
A few of my students wanted me to post sample cursor loops, so here are examples of simple, FOR, and WHILE loops. There are a couple variations on approaches that demonstrate %TYPE and %ROWTYPE anchoring to data dictionary table definitions and local cursors.
Part of the idea behind these examples is to show the basic structure while mimicking the \G option of MySQL. The \G (Go) displays results as a list of column names and values by row. Ever since I discovered that in MySQL, I’ve hoped Oracle would incorporate something similar in their product. While discussing my wish list, I’d also like Oracle to make the FROM dual optional (like MySQL does) when selecting a string or numeric literal. You can find an implementation here, that leverages an example from Tom Kyte.
You can click any of the titles to view the code, which isn’t needed when you don’t have JavaScript enabled or the RSS expands them for you.
Simple loop with local variables ↓
This simple loop example uses a static cursor and local variables that map to each column returned by the cursor. It uses the %TYPE to anchor local variables to the data dictionary.
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 | SET SERVEROUTPUT ON SIZE 1000000 DECLARE -- Declare local variables that are anchored to column data types. lv_title item.item_title%TYPE; lv_subtitle item.item_subtitle%TYPE; lv_rating item.item_rating%TYPE; -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start the simple loop block. LOOP -- Fetch a row of the cursor and assign it to the three local variables. FETCH c INTO lv_title , lv_subtitle , lv_rating; -- Exit when there aren't any more records in the cursor, without this you loop infinitely. EXIT WHEN c%NOTFOUND; -- Print the local variables on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||lv_title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||lv_subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||lv_rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
Simple loop with a local record structure variable ↓
This simple loop example uses a static cursor, a local record structure data type, and a local variable of the local record structure data type. The local record structure maps to the columns returned by the cursor. It uses explicit data types that match those of the table. You could also use the %TYPE to anchor the elements of the structure in the local data type, like the prior example. With explicit data types, you must modify the program when the definition of the table changes otherwise your program may fail at runtime.
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 | SET SERVEROUTPUT ON SIZE 1000000 DECLARE -- Declare a local record data type, with explicit data types (you could use %TYPE here too). TYPE title_type IS RECORD ( title VARCHAR2(60) , subtitle VARCHAR2(60) , rating VARCHAR2(8)); -- Declare a local variable of the local record structure data type. item_record TITLE_TYPE; -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start the simple loop block. LOOP -- Fetch a row of the cursor and assign it to the local record structure variable. FETCH c INTO item_record; -- Exit when there aren't any more records in the cursor, without this you loop infinitely. EXIT WHEN c%NOTFOUND; -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||item_record.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||item_record.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
Simple loop with a local cursor structure variable ↓
This simple loop example uses a static cursor, a local variable that inherits its record structure from the local cursor. It does so by using the %ROWTYPE against the cursor. Often the %ROWTYPE is only applied when you return a structure that maps to the complete table definition. Sometimes I think using cursor_name%ROWTYPE is the only real purpose for shared cursors but I know that’s not really true.
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 | SET SERVEROUTPUT ON SIZE 1000000 DECLARE -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; -- Declare a local variable of that inherits its structure from a local cursor. item_record c%ROWTYPE; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start the simple loop block. LOOP -- Fetch a row of the cursor and assign it to the local record structure variable. FETCH c INTO item_record; -- Exit when there aren't any more records in the cursor, without this you loop infinitely. EXIT WHEN c%NOTFOUND; -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||item_record.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||item_record.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
For loop with an implicit record structure variable ↓
This FOR loop example uses a static cursor. When a FOR loop uses a cursor it becomes a cursor FOR loop, and the iterator i becomes an implicit cursor record structure. You should note that this is a very compact program because a cursor FOR loop manages opening and closing the cursor, and handling the loop exit implicitly. Many programmers default to this approach whenever it fits because it is simple and easy to implement.
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 | SET SERVEROUTPUT ON SIZE 1000000 DECLARE -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; BEGIN -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start a cursor FOR loop block. FOR i IN c LOOP -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||i.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||i.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; END; / |
For loop without a declaration block ↓
This FOR loop example uses a static cursor. Like the prior example, this FOR loop uses a cursor but it is defined inside the actual FOR loop structure. That approach eliminates the need for the declaration block. It’s a nice feature that some may call a trick. As a rule, you should really avoid this style because formally defining your cursor is a good practice and improves code maintainability. Naturally, this is probably the most compact program because everything is managed implicitly including the cursor assignment to the loop structure.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SET SERVEROUTPUT ON SIZE 1000000 BEGIN -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start a cursor FOR loop block with the static cursor in the definition. FOR i IN (SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i) LOOP -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||i.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||i.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; END; / |
WHILE loop with a cursor guard on entry statement ↓
This WHILE loop example uses a static cursor, like the prior examples. Unlike the prior example, the WHILE loop is a guard on entry loop. The previous loops were guard on exit loops. This has much the same structure as the simple loop with a cursor record structure variable but differs on two key points.
Point one is that you must have two FETCH statements because the guard on entry condition checks whether any records are found in the cursor. The first FETCH statement checks whether at least a one row is returned. When true or false, it initializes the cursor attributes, like %FOUND. The second FETCH statement handles the second row returned to last row returned from the cursor.
Point two is that you don’t have an EXIT WHEN cursor_name%NOTFOUND inside the loop because the guard condition stops the loop when it fails to find at least one record.
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 | DECLARE -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; -- Declare a local variable of that inherits its structure from a local cursor. item_record c%ROWTYPE; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Fetch the first record to put into context the cursor attributes, like %FOUND. FETCH c INTO item_record; -- Start the simple loop block with a guard on entry condition. WHILE (c%FOUND) LOOP -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||item_record.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||item_record.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); -- Fetch the second and subsequent rows of the cursor and assign it to a local variables. FETCH c INTO item_record; END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
The WHILE loop as presented is complex because of the pre-loop FETCH statement, and the internal FETCH statement. It’s may appear better to convert it to a pseudo-infinite loop. You do that by setting the condition in a WHILE loop to a TRUE constant, like this example:
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 | DECLARE -- Declare a static cursor. CURSOR c IS SELECT i.item_title AS title , i.item_subtitle AS subtitle , i.item_rating AS rating FROM item i; -- Declare a local variable of that inherits its structure from a local cursor. item_record c%ROWTYPE; BEGIN -- Open the cursor. OPEN c; -- Print a starting line. dbms_output.put_line('----------------------------------------------------------------------'); -- Start the simple loop block with a guard on entry condition. WHILE (TRUE) LOOP -- Fetch the record set into a user-defined variable. FETCH c INTO item_record; -- Exit when there aren't any more records in the cursor, without this you loop infinitely. EXIT WHEN c%NOTFOUND; -- Print the local variable elements on a single line each to mimic MySQL \G equivalent. dbms_output.put_line('ITEM.ITEM_TITLE ['||item_record.title||']'); dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']'); dbms_output.put_line('ITEM.ITEM_RATING ['||item_record.rating||']'); -- Print an ending line. dbms_output.put_line('----------------------------------------------------------------------'); END LOOP; -- Close the cursor and release the resources. CLOSE c; END; / |
The preceding example behaves much like a simple loop, and you have to ask what is the benefit of WHILE (TRUE) LOOP over LOOP. Generally, it appears that the WHILE loop syntax is slighly longer to type.
I’m sure this will help my students and hope it helps somebody else.
Haste makes waste, again …
I was working on a code example for my database class, got in a hurry, and changed a table name without dropping the original table. Oops!
Naturally, I got this error message.
LOB (administrator_photo) STORE AS admin_photo * ERROR at line 5: ORA-00955: name IS already used BY an existing object |
The LOB segment name existed but why and where. It was in the table that I forgot to drop. This query find the latent table and column while illustrating the relationship (for my students and others) between an OBJECT_NAME and SEGMENT_NAME:
SELECT TABLE_NAME, column_name FROM user_lobs WHERE segment_name = (SELECT object_name FROM user_objects WHERE object_name = UPPER('&object_name') AND object_type = 'LOB'); |
Now, I can grab it later because it’ll happen again. 😉 At Samy mentioned in his comment you also have the option of using ALL_ or DBA_ views when you’re a DBA.
Subscribe via RSS