Re: Determining Table Storage Engine Type on Crashed Table
How about looking at the .frm file of the table. On 11/28/07, Richard Edward Horner <[EMAIL PROTECTED]> wrote: > FYI, this did not work :) > > Thanks though! > > Rich(ard) > > On Nov 23, 2007 3:37 AM, Paul McCullagh <[EMAIL PROTECTED]> > wrote: > > Maybe this will work: > > > > SHOW CREATE TABLE table_name; > > > > > > On Nov 21, 2007, at 9:42 PM, Richard Edward Horner wrote: > > > > > Hey everybody, > > > > > > Hopefully some of you are already enjoying time off. I am not...yet :) > > > > > > Anyway, is there a way to determine what storage engine a table is > > > using if it's crashed? When it's fine, I can just run: > > > > > > mysql> show table status like 'table_name'; > > > +-++-++ > > > ++-+--+-- > > > +---++- > > > +-+-+--- > > > +--++-+ > > > | Name| Engine | Version | Row_format | Rows | > > > Avg_row_length | Data_length | Max_data_length | Index_length | > > > Data_free | Auto_increment | Create_time | Update_time > > > | Check_time | Collation | Checksum | Create_options > > > | Comment | > > > +-++-++ > > > ++-+--+-- > > > +---++- > > > +-+-+--- > > > +--++-+ > > > | table_name | MyISAM | 10 | Fixed | 985984 | 13 > > > |12817792 | 3659174697238527 | 34238464 | 0 | > > > 1182153 | 2007-11-15 17:44:28 | 2007-11-21 15:28:07 | 2007-11-21 > > > 15:28:18 | latin1_swedish_ci | NULL || | > > > +-++-++ > > > ++-+--+-- > > > +---++- > > > +-+-+--- > > > +--++-+ > > > 1 row in set (0.00 sec) > > > > > > As you can see, the second column returned is the Engine. In this > > > case, MyISAM. Now, if I crash the table, it doesn't work: > > > > > > mysql> show table status like 'table_name'; > > > +-++-++-- > > > ++-+-+-- > > > +---++-+- > > > ++---+--+ > > > +- > > > ---+ > > > | Name| Engine | Version | Row_format | Rows | Avg_row_length > > > | Data_length | Max_data_length | Index_length | Data_free | > > > Auto_increment | Create_time | Update_time | Check_time | Collation | > > > Checksum | Create_options | Comment > > > | > > > +-++-++-- > > > ++-+-+-- > > > +---++-+- > > > ++---+--+ > > > +- > > > ---+ > > > | table_name | NULL |NULL | NULL | NULL | NULL | > > >NULL |NULL | NULL | NULL | > > > NULL | NULL| NULL| NULL | NULL | NULL | > > > NULL | Table './blah/table_name' is marked as crashed and > > > should be repaired | > > > +-++-++-- > > > ++-+-+-- > > > +---++-+- > > > ++---+--+ > > > +- > > > ---+ > > > 1 row in set (0.00 sec) > > > > > > Now, let's assume for a moment this were an InnoDB table. If I were to > > > try and run repair, it would say that the storage engine does not > > > support repair so clearly it knows what the storage engine is. How do > > > I get it to tell me? Or I guess a broader more helpful question would > > > be, "What are all the ways to determine a table's storage engine > > > type?" > > > > > > Thanks, > > > -- > > > Richard Edward Horner > > > Engineer / Composer / Electric Guitar Virtuoso > > > [EMAIL PROTECTED] > > > http://richhorner.com - updated June 28th > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe:http://lists.mysql.com/mysql? > > > [EMAIL PROTECTED] > > > > > > > > > > > -- > Richard Edward Horner > Engineer / Composer / Electric Guitar Virtuoso > [EMAIL PROTECTED] > http:
Re: backup InnoDB db to another server
You might want to use --single-transaction option when mysqldumping innodb On Dec 1, 2007 1:20 AM, Jeff Mckeon <[EMAIL PROTECTED]> wrote: > > -Original Message- > > From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED] > > Sent: Friday, November 30, 2007 11:16 AM > > To: mysql@lists.mysql.com > > Subject: Re: backup InnoDB db to another server > > > > > On Friday 30 November 2007 17:12, Jeff Mckeon wrote: > > > Ok, so what would be the command to get a mysqldump of DB1 from > > 10.10.0.1 > > > into file DB1backup.sql on 10.10.0.2? > > > > What about running mysqldump on 10.10.0.2? > > > > or > > > > mysqldump DB1 -uroot -ppassword > dump.sql > > scp dump.sql [EMAIL PROTECTED]:. > > > > -- > > Jørn Dahl-Stamnes > > homepage: http://www.dahl-stamnes.net/dahls/ > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > Ok so on 10.10.0.2 (destination server) issue a: > > % mysqldump DB1 -h10.10.0.1 -C -uroot -ppassword > /DATA/DB01bacup.sql > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order table by org heirarchy (emp-mgr)
The resultant table should be this: +--+---+--+ | id | name | mgr | +--+---+--+ | 1001 | Denis Eaton-Hogg | NULL | | 1002 | Bobbi Flekman | 1001 | | 1003 | Ian Faith | 1002 | | 1004 | David St. Hubbins | 1003 | | 1005 | Nigel Tufnel | 1003 | | 1006 | Derek Smalls | 1003 | +--+---+--+ The number happened to be sorted here - but that may not be the case - there are new manager hires etc.. Thanks. On Dec 1, 2007 4:48 PM, Anoop kumar V <[EMAIL PROTECTED]> wrote: > Hi Experts, > > I need a small help - I think the solution is not complex, but I am not > sure where to start. > > Here is my problem. I have a table that defines the employee to manager > relationship. This table will interface with another system and the users > would be created in the other system. There will be a pointer to the user > who is the manager of the currently processed user. So if the manager user > has not yet been created, the current user process will not complete as the > pointer to the manager-user does not exist. > > So I need to ensure that all managers are created first before creating > the users. But managers are also like other users in the same table in the > emp column. > > Here is a sample of the table: > > mysql> select * from emp; > +--+---+--+ > | id | name | mgr | > +--+---+--+ > | 1006 | Derek Smalls | 1003 | > | 1005 | Nigel Tufnel | 1003 | > | 1004 | David St. Hubbins | 1003 | > | 1003 | Ian Faith | 1002 | > | 1002 | Bobbi Flekman | 1001 | > +--+---+--+ > 6 rows in set (0.00 sec) > > I need to sort this table to look like this: > > +--+---+--+ > | id | name | mgr | > +--+---+--+ > | 1001 | Denis Eaton-Hogg | NULL | > | 1002 | Bobbi Flekman | 1001 | > | 1003 | Ian Faith | 1002 | > | 1001 | Denis Eaton-Hogg | NULL | > | 1004 | David St. Hubbins | 1003 | > | 1005 | Nigel Tufnel | 1003 | > | 1006 | Derek Smalls | 1003 | > +--+---+--+ > > > See how 1001 is the manager of everybody - so I can create this user > first, that would take care of 1002 as it would contain a link to the 1001 > user and all would be good. So on for 1002 and 1003 > > I think I need to do a self join and order by - if you could even give me > a hint that would be most helpful. > > Thanks, > Anoop >
Order table by org heirarchy (emp-mgr)
Hi Experts, I need a small help - I think the solution is not complex, but I am not sure where to start. Here is my problem. I have a table that defines the employee to manager relationship. This table will interface with another system and the users would be created in the other system. There will be a pointer to the user who is the manager of the currently processed user. So if the manager user has not yet been created, the current user process will not complete as the pointer to the manager-user does not exist. So I need to ensure that all managers are created first before creating the users. But managers are also like other users in the same table in the emp column. Here is a sample of the table: mysql> select * from emp; +--+---+--+ | id | name | mgr | +--+---+--+ | 1006 | Derek Smalls | 1003 | | 1005 | Nigel Tufnel | 1003 | | 1004 | David St. Hubbins | 1003 | | 1003 | Ian Faith | 1002 | | 1002 | Bobbi Flekman | 1001 | +--+---+--+ 6 rows in set (0.00 sec) I need to sort this table to look like this: +--+---+--+ | id | name | mgr | +--+---+--+ | 1001 | Denis Eaton-Hogg | NULL | | 1002 | Bobbi Flekman | 1001 | | 1003 | Ian Faith | 1002 | | 1001 | Denis Eaton-Hogg | NULL | | 1004 | David St. Hubbins | 1003 | | 1005 | Nigel Tufnel | 1003 | | 1006 | Derek Smalls | 1003 | +--+---+--+ See how 1001 is the manager of everybody - so I can create this user first, that would take care of 1002 as it would contain a link to the 1001 user and all would be good. So on for 1002 and 1003 I think I need to do a self join and order by - if you could even give me a hint that would be most helpful. Thanks, Anoop
MySQL Browser - limit 1000 by default?
Hi, on Linux version of MySQL Browser (v 1.2.4 beta), when double-click on any table, default query is SELECT * FROM LIMIT 0,1000 On Win version (v 1.2.9 rc), there is no LIMIT part - what caused me to pull so many times tens, even hundreds thousands of records. I was looking for in setting for this feature, but wasn't able to find. is there way to set the limit value on default select query, on win version? thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
Jeff: Mysqldump don't back up your index, that's your data only. Osvaldo Sommer -Mensaje original- De: Jeff Mckeon [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 30 de Noviembre de 2007 03:24 p.m. Para: 'David Campbell'; mysql@lists.mysql.com Asunto: RE: backup InnoDB db to another server > -Original Message- > From: David Campbell [mailto:[EMAIL PROTECTED] > Sent: Friday, November 30, 2007 11:29 AM > To: mysql@lists.mysql.com > Subject: Re: backup InnoDB db to another server > > Jørn Dahl-Stamnes wrote: > > On Friday 30 November 2007 17:12, Jeff Mckeon wrote: > >> Ok, so what would be the command to get a mysqldump of DB1 from > 10.10.0.1 > >> into file DB1backup.sql on 10.10.0.2? > > > > What about running mysqldump on 10.10.0.2? > > > > or > > > > > > scp dump.sql [EMAIL PROTECTED]:. > > > > Onliner > > mysqldump DB1 -uroot -ppassword > dump.sql | ssh 10.10.0.2 "cat > > dump.sql" > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] The Mysqldump has finished but I've only got a 10gig .sql file. The db is about 65gig in raw size. Does this sound right? Is there a filesize limit for mysqldump .sql files? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.10/1160 - Release Date: 29/11/2007 08:32 p.m. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
