Anything but a SELECT * returns too few rows -- index length?
I'm getting strange results that I think can be explained by having an index that isn't set up right. CREATE TABLE URL_POLICY ( url_id INTEGER NOT NULL, policy_idINTEGER NOT NULL, policy_order INTEGER NULL, PRIMARY KEY (url_id, policy_id) ) TYPE=BDB; INSERT INTO testtbl values(100,100,null); INSERT INTO testtbl values(100,101,null); INSERT INTO testtbl values(100,102,null); INSERT INTO testtbl values(100,103,null); INSERT INTO testtbl values(100,104,null); INSERT INTO testtbl values(100,105,null); Here is the problem: SELECT * from URL_POLICY; url_id policy_id policy_order 100 100 NULL 100 101 NULL 100 102 NULL 100 103 NULL 100 104 NULL 100 105 NULL All seems in order, but as soon as I add a where clause (SELECT * from URL_POLICY where url_id=100) I only get a subset of those rows back. Explain isn't all that useful since there are no joins, but the "key_len" of 4 seems like it might be a problem. Another odd thing is that I can't reproduce this using a different database, but I see it on the existing table (even adding new rows to test with). The CREATE/INSERTs above are exactly what we used. Machine is SPARC/Solaris 7, running MySQL 3.23.34a. Any guidance? It seemed like it might be a FAQ, but I didn't see anything in the manual or a quick search of the archives. __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
3.23.33-3.23.34a crashing with panic from BDB handler
I'm experiencing a problem where mysqld is crashing every time I run a particular query. The specific error from the client is: ERROR 1015: Can't lock file (errno: -30989) ERROR 2013: Lost connection to MySQL server during query The server error log is reporting: bdb: transaction has active cursors bdb: PANIC: Invalid argument mysqld got signal 11 At that point it either dumps core and dies (3.23.33) or restarts itself (3.23.34a), sometimes reporting errors on startup again in 3.23.34a. For the time being I have downgraded to 3.23.32, but before I fill out a bug report or send my whole database structure and query (basically the query that crashes things is working on bdb tables, but not as part of a transaction, it joins a total of 4 tables, all on a primary key column), I wanted to find out if anyone else has experienced a similar problem or could point me in the right direction. -Scott -- Scott McCool [EMAIL PROTECTED] (571)633-5749 --
BDB table corruption when inserting/retrieving a BLOB into a BDB table
I'm getting a very strange results all of the sudden when dealing with BLOBs
in a mysql 3.23.32 on a Solaris 7 (SPARC) machine (inserts/deletes handled
with the JDBC driver from http://mmmysql.sourceforge.net/)
If I do the following:
1) Create a BDB table with a blob column:
CREATE TABLE test4(
id INTEGER not null PRIMARY KEY,
ablob BLOB null
) TYPE=BDB;
2) Insert a particular blob into that column (I'm storing XSLT stylesheets
in the real table, most of them work fine but one in particular is all of
the sudden causing problems):
---code snip---
String fn="badblob.txt";// Filename of the blob I'm loading that
causes problems
FileInputStream style_fis=new FileInputStream(fn);
byte[] style_bytes=new byte[style_fis.available()];
style_fis.read(style_bytes);
String dQuery="delete from ttest4";
String iQuery="insert into ttest4(id,ablob) values(1,null);";
conn=cp.getConnection();// Gets a JDBC connection from a pool I
keep elsewhere
Statement stmt=conn.createStatement();
stmt.executeUpdate(dQuery);
stmt.executeUpdate(iQuery);
stmt.close();
String uQuery="update test4 set ablob=? where id=1";
pstmt=conn.prepareStatement(uQuery);
pstmt.setBytes(1,style_bytes);
pstmt.execute();
pstmt.close();
cp.releaseConnection(conn);
---end code snip---
(I've modified this some, but basically my code will insert an empty row
then update it with the blob, this is to deal with some issues we've had
with other RDBMs' implementations of JDBC).
This seems to go just fine.
3) Retrieve that blob with the following code:
--code snip--
conn=cp.getConnection();
Statement stmt=conn.createStatement();
String query="select ablob from ttest4 where id=1";
ResultSet rs=stmt.executeQuery(query);
while(rs.next()) {
Blob b=rs.getBlob(1);
byte[] blobBytes=b.getBytes(0,(int)b.length());
System.out.println(new String(b));
}
--end code snip--
At this point my output ends up very garbled. The output usually starts off
with some very strange ASCII characters (seemingly binary data) with things
like filesystem names thrown in the mix... Then at some point parts of my
actual data (from the "badblob.txt" file in step 2) appears... Then the end
of it is usually overwritten with more strange ASCII characters.
In attempting to fix this problem, I dropped and recreated the entire
database a few times, restarted the server daemon, etc. I finally ended up
changing the table to TYPE=MyISAM and the problem went away. Unfortunately,
I need transaction support for this project.
I've been using this code, data, server version, bdb table, etc for a few
weeks now with no problems, but suddenly when regenerating the schema last
night this started to occur and is now happening regularly.
I'd like to blame this on the particular blob I'm inserting (badblob.txt)
but that doesn't seem to be the problem as it hasn't changed. My other
thought was a corrupt disk, but that doesn't seem likely... I'm open to any
suggestions as this problem seems potentially very serious.
Any help is greatly appreciated!
-Scott
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BDB table corruption when inserting/retrieving a BLOB into a BDB table
I can also verify that the data seems to be stored properly (from mysql's
command line tool). The problem seems most likely to occur with larger text
strings
Anyone else?
-Scott
-Original Message-
From: Stephen Faustino [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 2:51 PM
To: '[EMAIL PROTECTED]'
Subject: RE: BDB table corruption when inserting/retrieving a BLOB into
a BDB table
We have also experienced the same exact behavior, also using the mm.mysql
driver, on Linux. However, our problem is intermittent. There are times
(though these times cannot be accurately predicted) where the data is
retrieved correctly, which implies that the data is stored correclty. We
have verified that the data is being stored correctly by using mysql to
select the data into a dumpfile. We did not try to recreate the problem
with a table handler other than BDB.
Stephen L. Faustino
Senior Software Engineer
SecureLogix Corporation
Direct/Vmail (210)402-9669x949
mailto:[EMAIL PROTECTED]
This email is intended for the named recipient(s) only and may contain
information that is privileged and/or confidential. Nothing in this
email is intended to constitute a waiver of any privilege or the
confidentiality of this message. If you have received this email in
error, please notify me immediately by reply and delete this message.
-Original Message-
From: Scott McCool [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 1:01 PM
To: '[EMAIL PROTECTED]'
Subject: BDB table corruption when inserting/retrieving a BLOB into a
BDB table
I'm getting a very strange results all of the sudden when dealing with BLOBs
in a mysql 3.23.32 on a Solaris 7 (SPARC) machine (inserts/deletes handled
with the JDBC driver from http://mmmysql.sourceforge.net/)
If I do the following:
1) Create a BDB table with a blob column:
CREATE TABLE test4(
id INTEGER not null PRIMARY KEY,
ablob BLOB null
) TYPE=BDB;
2) Insert a particular blob into that column (I'm storing XSLT stylesheets
in the real table, most of them work fine but one in particular is all of
the sudden causing problems):
---code snip---
String fn="badblob.txt";// Filename of the blob I'm loading that
causes problems
FileInputStream style_fis=new FileInputStream(fn);
byte[] style_bytes=new byte[style_fis.available()];
style_fis.read(style_bytes);
String dQuery="delete from ttest4";
String iQuery="insert into ttest4(id,ablob) values(1,null);";
conn=cp.getConnection();// Gets a JDBC connection from a pool I
keep elsewhere
Statement stmt=conn.createStatement();
stmt.executeUpdate(dQuery);
stmt.executeUpdate(iQuery);
stmt.close();
String uQuery="update test4 set ablob=? where id=1";
pstmt=conn.prepareStatement(uQuery);
pstmt.setBytes(1,style_bytes);
pstmt.execute();
pstmt.close();
cp.releaseConnection(conn);
---end code snip---
(I've modified this some, but basically my code will insert an empty row
then update it with the blob, this is to deal with some issues we've had
with other RDBMs' implementations of JDBC).
This seems to go just fine.
3) Retrieve that blob with the following code:
--code snip--
conn=cp.getConnection();
Statement stmt=conn.createStatement();
String query="select ablob from ttest4 where id=1";
ResultSet rs=stmt.executeQuery(query);
while(rs.next()) {
Blob b=rs.getBlob(1);
byte[] blobBytes=b.getBytes(0,(int)b.length());
System.out.println(new String(b));
}
--end code snip--
At this point my output ends up very garbled. The output usually starts off
with some very strange ASCII characters (seemingly binary data) with things
like filesystem names thrown in the mix... Then at some point parts of my
actual data (from the "badblob.txt" file in step 2) appears... Then the end
of it is usually overwritten with more strange ASCII characters.
In attempting to fix this problem, I dropped and recreated the entire
database a few times, restarted the server daemon, etc. I finally ended up
changing the table to TYPE=MyISAM and the problem went away. Unfortunately,
I need transaction support for this project.
I've been using this code, data, server version, bdb table, etc for a few
weeks now with no problems, but suddenly when regenerating the schema last
night this started to occur and is now happening regularly.
I'd like to blame this on the particular blob I'm inserting (badblob.txt)
but that doesn't seem to be the problem as it hasn't changed. My other
thought was a corrupt disk, but that doesn't seem likely... I'm open to any
suggestions as this problem seems potentially very serious.
Any help is greatly appreciated!
-Scott
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To req
transactions -- whole table locked from reads too?
If I start a transaction (set autocommit=0 in the command line utility), then do an "insert" in one session, then open a second session and try to do a select on that table, before the first session has committed, the select just hangs. Once I commit in the first session, my select proceeds fine. Is there some reason the whole table seems to be locked from reads just because someone has an uncommitted transaction? The table type is BDB, it's running on Solaris 7/SPARC. Thanks for any help you can offer! -Scott ----- Scott McCool [EMAIL PROTECTED] (703)847-3303x2051 -
