kkubek

I'm too lazy to create the table to test this, but does this work ? select b.name, a.name from cats a, cats b where a.parent = b.id and a.id =2

select a.*, b.c1, b.c2, b.c3 from table1 a, table2 b where a.e_id=b.e_id c1,c2 and c3 are the column names u want from table2

I'm not exactly clear on what you're trying to do, but if you want data from 2 identical tables, you'd use a UNION select * from table1 UNION select * from table2 You'd add a where clause to both select if you wanted only a subset

How about select unit_id,count(*) from squads group by unit_id

select count(*) as namecount, column_name_with_name from tab group by column_name_with_name order by namecount

How about: SELECT surname FROM names WHERE left(surname,1) in ('A','B','C' ......) A little better than the ugly OR

It's hard to give a complete answer without fully understanding your requirements, but based on a lot of assumptions, your schema is a very good starting point. A couple suggestions: Many of the columns should be changed to NOT NULL. As a matter of fact, it looks to me like every one should be NOT NULL. Even the iffy ones like image, couldn't be populated with "No Image" if one is not available. You'll want to add FKs also. For example, mr_mobiles should have an FK on comp_id that points to mr_complist. And, mr_mfeatures needs FK to mr_mobiles. You need more FKs than that but I don't want to make it too easy 8-) Not sure what other info you're looking for.

fenway, I tried changing tmpdir, by doing: TMPDIR=/usr/local/mysql (from Linux) and I still got the error 17. I also tried droppping indexes: mysql> ALTER TABLE kk DROP INDEX idx_orders_rosnumber; ERROR 1030 (HY000): Got error 127 from storage engine mysql> ALTER TABLE kk DROP INDEX idx_orders_recordid -> ; ERROR 1030 (HY000): Got error 127 from storage engine mysql>

Yup, same result ... the dreaded error 17

I know, that error 17 is driving me nuts too. I am mysql when I try this, and yes mysqld has been stoppped [mysql@localhost transactions]$ isamchk -e -a -i -o orders_history isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17) isamchk: error: Got error 17 when trying to recreate indexfile ISAM-table 'orders_history' is not fixed because of errors User time 0.00, System time 0.01 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 142, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 1

fenway, yes I have made copies of all the files Here's the output from a few variations of isamchk: [mysql@localhost transactions]$ isamchk -f -r orders_history isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17) isamchk: error: Got error 17 when trying to recreate indexfile ISAM-table 'orders_history' is not fixed because of errors [mysql@localhost transactions]$ isamchk -f -q orders_history Checking ISAM file: orders_history Data records: 20877511 Deleted blocks: 0 - check file-size isamchk: warning: Size of datafile is: 49 Should be: 2734016830 - check delete-chain - check index reference - check data record references index: 1 isamchk: error: Found key at page 1024 that points to record outside datafile isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17) isamchk: error: Got error 17 when trying to recreate indexfile ISAM-table 'orders_history' is not fixed because of errors [mysql@localhost transactions]$ isamchk -o orders_history isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17) isamchk: error: Got error 17 when trying to recreate indexfile ISAM-table 'orders_history' is not fixed because of errors [mysql@localhost transactions]$ [mysql@localhost transactions]$ ls -l orders_history.* -rw-rw-rw- 1 mysql mysql 9782 Jan 3 03:03 orders_history.frm -rw-rw-rw- 1 mysql mysql 4294967345 Jan 3 03:12 orders_history.ISD -rw-rw-rw- 1 mysql mysql 541793280 Jan 3 03:14 orders_history.ISM [mysql@localhost transactions]$ id uid=500(mysql) gid=500(mysql) groups=500(mysql) context=root:system_r:unconfined_t:SystemLow-SystemHigh [mysql@localhost transactions]$

my tmpdir has 22G, and the ISD file is 4G

How about: select filename, max(path), max(revision), max(id) from repos group by filename And, you might want to consider adding path to the group by (as fenway suggested). It's hard to tell from your data sample if filename is always 1 to 1 to path. Can you have the same filename in 2 diff paths ? If so, add path to the group by. Actually, probably better to do it regardless

I can't test this, but I believe this should do it: select filename, max(path), max(revision) from repos group by filename

How about: CREATE TABLE emailListDuplicate SELECT (minIid),name, email, storeStatus, storePhoto, storeAds FROM emailList GROUP BY id, name, email, storeStatus, storePhoto, storeAds

When you get the error "Whatever Table can not be Null" is it the same table that you changed or a different table ? I'm thinking maybe your original table has a trigger that inserts or updates another table.

fenway, Nope. no backup of these files. I inherited this db when they started getting the error messages, and it turns out the backup they were running gets overwritten each night. I've tried every possible option in isamchk (including the ones you mentioned. I also tried that 4 step option from the man page, but no luck. Stupid question from a MySQL newbie, how do I know what dir is my temp dir ? KK

Thanks for the help. If i move the indexfile: -bash-3.00$ mv kk.ISM kk.ISM2 -bash-3.00$ isamchk -r kk isamchk: error: File 'kk' doesn't exist Also, a dump won't work after moving indexfile: -bash-3.00$ mv kk.ISM kk.ISM2 -bash-3.00$ mysqldump transactions kk -- MySQL dump 10.9 -- -- Host: localhost Database: transactions -- ------------------------------------------------------ -- Server version 4.1.20 /*!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 */; /*!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 `kk` -- DROP TABLE IF EXISTS `kk`; CREATE TABLE `kk` ( `clientID` int(11) NOT NULL default '0', `recordID` bigint(30) NOT NULL default '0', `store_number` varchar(100) default NULL, `pickup_location` varchar(100) default NULL, `fname` varchar(100) default NULL, `middle_initial` varchar(5) default NULL, `lname` varchar(100) default NULL, `phone1` varchar(100) default NULL, `phone2` varchar(100) default NULL, `phone3` varchar(100) default NULL, `address` varchar(100) default NULL, `city` varchar(255) default NULL, `state` varchar(10) default NULL, `zip` varchar(20) default NULL, `comments` text, `description` varchar(255) default NULL, `sku` varchar(100) default NULL, `model` varchar(100) default NULL, `manufacturer` varchar(100) default NULL, `delivery_date` varchar(100) default NULL, `delivery_time` varchar(5) default NULL, `delv_method` varchar(50) default NULL, `status` varchar(20) default NULL, `trans_date` varchar(100) default NULL, `ros_number` varchar(15) NOT NULL default '', `emailDate` date default '0000-00-00', `result` varchar(25) default NULL, `email_from` varchar(50) NOT NULL default '', `recordID2` int(11) NOT NULL default '0', `vehicle` varchar(20) default NULL, `customer_email` varchar(100) default NULL, KEY `idx_orders_rosnumber` (`recordID`), KEY `idx_orders_recordid` (`ros_number`) ) ENGINE=ISAM DEFAULT CHARSET=latin1 PACK_KEYS=1; -- -- Dumping data for table `kk` -- /*!40000 ALTER TABLE `kk` DISABLE KEYS */; LOCK TABLES `kk` WRITE; mysqldump: Error 1030: Got error 127 from storage engine when dumping table `kk` at row: 0 And here's the kicker : the backups they do overwrite each day, and don't go to tape. So, there's no way to restore. What other options do I have ? Is there any other support out there ? Can I send the files to someone who specializes in recovering ?

Hi, I recently inherited a MySQL database that appears to have a corrupt table. I am an oracle DBA and have never used MySQL until this week. Unfortunately, isamchk is giving me an error that's driving me nuts. I've searched the Web for hours, and can't find anything useful. [mysql@localhost transactions]$ isamchk -r kk isamchk: Can't create/write to file 'kk.ISM' (Errcode: 17) isamchk: error: Got error 17 when trying to recreate indexfile ISAM-table 'kk' is not fixed because of errors [mysql@localhost transactions]$ perror 17 OS error code 17: File exists Well duh, of course kk.ISM exists: [mysql@localhost transactions]$ ll kk* -rwxrwxrwx 1 mysql mysql 9782 Jan 4 05:27 kk.frm -rwxrwxrwx 1 mysql mysql 4294967345 Jan 3 05:18 kk.ISD -rwxrwxrwx 1 mysql mysql 541793280 Jan 3 08:05 kk.ISM I would assume that isamchk would overwrite the file (permissions are OK), but maybe it doesn't overwrite. So, let's try moving it : [mysql@localhost transactions]$ mv kk.ISM kk.ISM2 [mysql@localhost transactions]$ isamchk -r kk isamchk: error: File 'kk' doesn't exist Maybe something is locking the file ? It's not Mysqld because I copied the all 3 files to another server that mysqld is not running on. [mysql@localhost transactions]$ ps -ef|grep mysq mysql 14021 14020 0 12:01 pts/0 00:00:00 -bash mysql 14198 14021 0 12:32 pts/0 00:00:00 ps -ef mysql 14199 14021 0 12:32 pts/0 00:00:00 grep mysq What am I missing ? Please help me keep what little sanity remains.

Fenway, Thanks for the help. I inherited this database on Thursday. I believe it was created a couple years ago, and has never been maintained ... until now when they started getting errors. I should have included this in my original post, but I tried to convert to MyIsam: mysql> alter table orders_history engine=myisam; ERROR 1030 (HY000): Got error 127 from storage engine and -bash-3.00$ mysql_convert_table_format -user=root -password=password transactions orders_history Can't convert orders_history: Error Got error 127 from storage engine Check table says "The storage engine for the table doesn't support check" Repair table says "The storage engine for the table doesn't support repair" I've spent hours searching the Web, and trying different ideas to fix, this with no luck. If/when we get this fixed, I'll probably convert everything to MyISAM. What are the advantages/disadvantages of MyISAM ? Thanks in advance, KK

mySQL 4.1.20-standard ISAM table orders_history select recordID, ros_number,description,sku,model,manufacturer,emailDate,delivery_date,delv_method,status,vehicle,store_number,zip,email_from from orders_history where ros_number = '704572' order by 1,8; ERROR 1030 (HY000): Got error 127 from storage engine but this works: select recordID, ros_number,description,sku,model,manufacturer,emailDate,delivery_date,delv_method,status,vehicle,store_number,zip,email_from from orders_history where ros_number = '417638735' order by 1,8; isamchk shows an error: isamchk -v orders_history Checking ISAM file: orders_history Data records: 20892617 Deleted blocks: 0 - check file-size isamchk: warning: Size of datafile is: 49 Should be: 2739177090 - check delete-chain index 1: index 2: No recordlinks - check index reference - check data record references index: 1 isamchk: error: Found key at page 1024 that points to record outside datafile - check record links isamchk: error: Recordlink that points outside datafile at 0 isamchk: error: Record-count is not ok; is 0 Should be: 20892617 isamchk: warning: Found 0 record-data and 0 unused data and 0 deleted-data Total 0, Should be: 49 isamchk: warning: Found 0 parts Should be: 20892625 parts ISAM-table 'orders_history' is corrupted Fix it using switch "-r" or "-o" But, trying to fix it fails: isamchk -r orders_history isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17) isamchk: error: Got error 17 when trying to recreate indexfile ISAM-table 'orders_history' is not fixed because of errors isamchk -o orders_history isamchk: Can't create/write to file 'orders_history.ISM' (Errcode: 17) isamchk: error: Got error 17 when trying to recreate indexfile ISAM-table 'orders_history' is not fixed because of errors ls -l orders_history.* -rw-rw---- 1 mysql mysql 9782 Nov 10 2007 orders_history.frm -rw-rw---- 1 mysql mysql 4294967345 Jan 3 14:42 orders_history.ISD -rw-rw---- 1 mysql mysql 542180352 Jan 3 14:42 orders_history.ISM Is this related to the fact that the ISD file is 4G ?