MySQL Error 1030: Got error -30996 from table handler
List, I would appreciate if somebody has any ideas on the above error. We are getting this error Error 1030: Got error -30996 from table handler with BDB tables on a Linux Server (Red Hat 7.3), the version of MySQL is 3.23.42 Thanks Sridhar - 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
Sorting the query results of a MySQL BerkeleyDb Table produces incorrect results
Dear List,
Sorry for reposting the issue, once again
One of our MySQL db's containing BDB tables is exhibiting a strange
behavior. A query consisting of an ORDER BY clause is returning wrong
result set.
The same query when used without an ORDER BY clause returns the correct
result set.
Environment: Sun Solaris 2.7, MySQL 3.23.51, Table Type = BerkleyDB
Given below is the query for which we are seeing this issue:
select orders.order_uid, orders.status,
(to_days(now()) - to_days(orders.verification_date)) as age
from orders
where orders.status = 'verified'
order by orders.verification_date;
The above query returns:
+---++--+
| order_uid | status | age |
+---++--+
| 130 | new| NULL |
| 130 | new| NULL |
| 130 | new| NULL |
| 130 | new| NULL |
| 130 | new| NULL |
+---++--+
What SHOULD have been returned is:
+---+--+--+
| order_uid | status | age |
+---+--+--+
|57 | verified |4 |
|76 | verified |4 |
|79 | verified |4 |
| 233 | verified |3 |
| 234 | verified |0 |
+---+--+--+
Currently i get around this issue by analyzing the BDB tables, but this
seems to be a temporary solution. The issue is intermittent, keeps
surfacing again after a certain no of days. Did not find any related
bugs with the
MySQL version we use (3.23.51). From what i can understand so far, the
index statistics related to the BDB tables are not getting updated.
What can be done to correct this issue ?
Are there any other alternative's to this issue ?
Would converting the BerkleyDB (BDB) tables to InnoDB tables solve the
issue ?
Here are a few other details about the BDB table that is exhibiting this
problem:
kcaset02:{}mysql sbiz
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21716 to server version: 3.23.51-max-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql desc orders;
+-++--+-+-++
| Field |
Type
| Null | Key | Default | Extra |
+-++--+-+-++
| order_uid |
bigint(20)
| | PRI | NULL| auto_increment |
| creation_date |
datetime
| | | -00-00 00:00:00 ||
| modified_date |
timestamp(14)
| YES | | NULL||
| status |
enum('inprogress','new','submitted','verified','fulfilled','error','canceled')
| YES | | inprogress ||
| verification_num|
char(10)
| YES | | NULL||
| verification_method |
enum('third_party','customer_online','customer_fax','none')
| YES | | none||
| batch_number|
bigint(20)
| YES | | NULL||
| verification_date |
datetime
| YES | | NULL||
| salesmaker_fid |
bigint(20)
| YES | | NULL||
| legalese_fid|
bigint(20)
| | MUL | 0 ||
| loa_sig |
char(60)
| | | ||
| sa_sig |
char(60)
| | | ||
| sa_signed_date |
datetime
| YES | | NULL||
+-++--+-+-++
13 rows in set (0.00 sec)
mysql show table status like 'ord%';
Re: Select with order by clause on a MySQL BDB table returning incorrect
[EMAIL PROTECTED] wrote:
Sridhar,
Wednesday, September 25, 2002, 12:23:29 AM, you wrote:
SP One of our MySQL db's containing BDB tables is exhibiting a strange
SP behavior.
SP A query consisting of an ORDER BY clause is returning wrong result set.
SP The
SP same query when used without an ORDER BY clause returns the correct
SP result
SP set.
SP Environment: Sun Solaris 2.7, MySQL 3.23.51, Table Type = BerkleyDB
SP Given below is the query for which we are seeing this issue:
SPselect orders.order_uid, orders.status,
SP(to_days(now()) - to_days(orders.verification_date)) as age
SPfrom orders
SPwhere orders.status = 'verified'
SPorder by orders.verification_date;
SP The above query returns:
SP +---++--+
SP | order_uid | status | age |
SP +---++--+
SP | 130 | new| NULL |
SP | 130 | new| NULL |
SP | 130 | new| NULL |
SP | 130 | new| NULL |
SP | 130 | new| NULL |
SP +---++--+
SP What SHOULD have been returned is:
SP +---+--+--+
SP | order_uid | status | age |
SP +---+--+--+
SP |57 | verified |4 |
SP |76 | verified |4 |
SP |79 | verified |4 |
SP | 233 | verified |3 |
SP | 234 | verified |0 |
SP +---+--+--+
SP Currently i get around this issue by analyzing the BDB tables, but this
SP seems to be a temporary solution. The issue is intermittent, keeps
SP surfacing
SP again after a certain no of days. Did not find any related bugs with the
SP MySQL version we use (3.23.51). From what i can understand so far, the
SP index
SP statistics related to the BDB tables are not getting updated.
SP What can be done to correct this issue ?
SP Are there any other alternative's to this issue ?
SP Would converting the BerkleyDB (BDB) tables to InnoDB tables solve the
SP issue ?
I tested your query on the test BDB table and it worked well, here is
ther result that I got:
+---+--+--+
| order_uid | status | age |
+---+--+--+
| 5 | verified | 501 |
| 4 | verified | 267 |
+---+--+--+
2 rows in set (0.00 sec)
Could you provide a repeatable test case?
BTW if you send bug report, send output of SHOW CREATE TABLE or output
of mysqldump, not the output of DESC table_name.
I tested it on 3.23.52.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.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
Victoria,
As said earlier, i cannot reproduce the problem because i analyzed the
BDB tables yesterday.
If i don't analyze the tables for a week, then the above said problem
surfaces again. The
application is still in test phase and will be put into production the
following Monday. I
strongly believe this issue is some how related to BDB index link list
statistics that might
be getting corrupted. I do not know if this is due to a bug, if so i
cannot reproduce it, until
i hit the problem again. Speaking of bug, would i abe able to submit
this as a bug ?
I am seriously pondering if i had a mistake in choosing MySQL for our
transactional application.
Here's the 'create table':
CREATE TABLE `orders` (
`order_uid` bigint(20) NOT NULL auto_increment,
`creation_date` datetime NOT NULL default '-00-00 00:00:00',
`modified_date` timestamp(14) NOT NULL,
`status`
enum('inprogress','new','submitted','verified','fulfilled','error','canceled')
default 'inprogress',
`verification_num` char(10) default NULL,
`verification_method`
enum('third_party','customer_online','customer_fax','none') default
'none',
`batch_number` bigint(20) default NULL,
`verification_date` datetime default NULL,
`salesmaker_fid` bigint(20) default NULL,
`legalese_fid` bigint(20) NOT NULL default '0',
`loa_sig` char(60) NOT NULL default '',
`sa_sig` char(60) NOT NULL default '',
`sa_signed_date` datetime default NULL,
PRIMARY KEY (`order_uid`),
KEY `idx01_orders` (`legalese_fid`)
) TYPE=BerkeleyDB
Thanks
Sri
-
Before posting, please check:
Select with order by clause on a MySQL BDB table returning incorrect
Dear List,
One of our MySQL db's containing BDB tables is exhibiting a strange
behavior.
A query consisting of an ORDER BY clause is returning wrong result set.
The
same query when used without an ORDER BY clause returns the correct
result
set.
Environment: Sun Solaris 2.7, MySQL 3.23.51, Table Type = BerkleyDB
Given below is the query for which we are seeing this issue:
select orders.order_uid, orders.status,
(to_days(now()) - to_days(orders.verification_date)) as age
from orders
where orders.status = 'verified'
order by orders.verification_date;
The above query returns:
+---++--+
| order_uid | status | age |
+---++--+
| 130 | new| NULL |
| 130 | new| NULL |
| 130 | new| NULL |
| 130 | new| NULL |
| 130 | new| NULL |
+---++--+
What SHOULD have been returned is:
+---+--+--+
| order_uid | status | age |
+---+--+--+
|57 | verified |4 |
|76 | verified |4 |
|79 | verified |4 |
| 233 | verified |3 |
| 234 | verified |0 |
+---+--+--+
Currently i get around this issue by analyzing the BDB tables, but this
seems to be a temporary solution. The issue is intermittent, keeps
surfacing
again after a certain no of days. Did not find any related bugs with the
MySQL version we use (3.23.51). From what i can understand so far, the
index
statistics related to the BDB tables are not getting updated.
What can be done to correct this issue ?
Are there any other alternative's to this issue ?
Would converting the BerkleyDB (BDB) tables to InnoDB tables solve the
issue ?
Here are a few other details about the BDB table that is exhibiting this
problem:
kcaset02:{}mysql sbiz
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21716 to server version: 3.23.51-max-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql desc orders;
+-++--+-+-++
| Field |
Type
| Null | Key | Default | Extra |
+-++--+-+-++
| order_uid |
bigint(20)
| | PRI | NULL| auto_increment |
| creation_date |
datetime
| | | -00-00 00:00:00 ||
| modified_date |
timestamp(14)
| YES | | NULL||
| status |
enum('inprogress','new','submitted','verified','fulfilled','error','canceled')
| YES | | inprogress ||
| verification_num|
char(10)
| YES | | NULL||
| verification_method |
enum('third_party','customer_online','customer_fax','none')
| YES | | none||
| batch_number|
bigint(20)
| YES | | NULL||
| verification_date |
datetime
| YES | | NULL||
| salesmaker_fid |
bigint(20)
| YES | | NULL||
| legalese_fid|
bigint(20)
| | MUL | 0 ||
| loa_sig |
char(60)
| | | ||
| sa_sig |
char(60)
| | | ||
| sa_signed_date |
datetime
| YES | | NULL||
+-++--+-+-++
13 rows in set (0.00 sec)
mysql show table status like 'ord%';
Multi-Table Delete with MySQL 3.23.51
Guys, I know that version 3.23.51 of MySQL does not handle deleting data from multiple tables directly. I am in a situation where i have to delete millions of records from a dozen tables. The delete logic is not simple, i have to check a number of tables before i can actually delete a row from a given table. The table TYPE is MyISAM. All of my databases run in a 24x7 env. In order to reduce my scheduled downtime here's what i am planning to do, ofcourse this will be tested against the test env first. Please let me know your feedback. I would surely appreciate to hear about any other better way of accomplishing this task. 1) create temporary (not actually a TEMPORARY) tables for all the tables to be purged using CREATE TABLE ... statement. The structure of the table would be the same as the original table to be purged. I could have used CREATE TABLE ... AS SELECT * FROM statement for creating a temp table, but this statement does not create the indexes, PK, auto_increment et al... 2) Load the data from the original purge table into the temp table based upon my delete logic. 3) Drop the original purge table. 4) Rename the temp table that i have created to the name of the purge table that i have dropped. Let me know if this sounds as a viable solution. I would sure like to hear anything about the table locking and grant issues that i might encounter. Thanks Sri - 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
Multi-Table Referential Delete Clarification with MySQL 3.23.51
Guys, I know that version 3.23.51 of MySQL does not handle deleting data from multiple tables directly. I am in a situation where i have to delete millions of records from a dozen tables. The delete logic is not simple, i have to check a number of tables before i can actually delete a row from a given table. All of my databases run in a 24x7 env. In order to reduce my scheduled downtime here's what i am planning to do, ofcourse this will be tested against the test env first. Please let me know your feedback. I would surely appreciate to hear about any other better way of accomplishing this task. 1) create temporary (not actually a TEMPORARY) tables for all the tables to be purged using CREATE TABLE ... statement. The structure of the table would be the same as the original table to be purged. I could have used CREATE TABLE ... AS SELECT * FROM statement for creating a temp table, but this statement does not create the indexes, PK, auto_increment et al... 2) Load the data from the original purge table into the temp table based upon my delete logic. 3) Drop the original purge table. 4) Rename the temp table that i have created to the name of the purge table that i have dropped. Let me know if this sounds as a viable solution. I would sure like to hear anything about the table locking and grant issues that i might encounter. Thanks Sri - 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: Multi-Table Referential Delete Clarification with MySQL 3.23.51
Forgot to mention that all of my tables are of TYPE: MyISAM Thanks Sri [EMAIL PROTECTED] wrote: Guys, I know that version 3.23.51 of MySQL does not handle deleting data from multiple tables directly. I am in a situation where i have to delete millions of records from a dozen tables. The delete logic is not simple, i have to check a number of tables before i can actually delete a row from a given table. All of my databases run in a 24x7 env. In order to reduce my scheduled downtime here's what i am planning to do, ofcourse this will be tested against the test env first. Please let me know your feedback. I would surely appreciate to hear about any other better way of accomplishing this task. 1) create temporary (not actually a TEMPORARY) tables for all the tables to be purged using CREATE TABLE ... statement. The structure of the table would be the same as the original table to be purged. I could have used CREATE TABLE ... AS SELECT * FROM statement for creating a temp table, but this statement does not create the indexes, PK, auto_increment et al... 2) Load the data from the original purge table into the temp table based upon my delete logic. 3) Drop the original purge table. 4) Rename the temp table that i have created to the name of the purge table that i have dropped. Let me know if this sounds as a viable solution. I would sure like to hear anything about the table locking and grant issues that i might encounter. Thanks Sri - 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
Q: Data Modeling Tool for MySQL ???
Can anyone suggest a good data modeling tool that can be used for: a) Reverse enginering: dump file = data model b) Create a new data model c) Generate the ddl from the data model d) The tool should handle the latest features of MySQL 3.23.52 Any comments about the tool called dezign. Is it any good ?? Thanks Sri - 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
How to upgrade/migrate Mysql Server
Mysql Gurus, We are currently using Mysql (3.22.32) on a Solaris platform. We are looking to upgrade/migrate this current version to 3.23.49a, for utilizing the replication functionality. I would appreciate if anyone can clarify the following: 1) Any known/unknown issues with 3.23.49a 2) What are the steps in migrating to this new version. I say this because i come from a Oracle dba world. 2) Anything in particular that i need to watch for, while upgrading to this new version. Thanks Sri - 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
Mysql Error while starting up
Has anyone encountered the following error, while starting the mysql daemon as a user other than root. Can't create IP Socket: Permission Denied I do not encounter any problem when running the same as root I have checked the permission on /datadir /basedir /tmp, they seem to be OK. Thanks Sridhar - 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
