problem open a innodb table
I messed up the ibdata1,ib_logfile1,0 files and encounter the problems of openning innodb tables even after I copied the back-up files of few months ago. I don't have many innodb tables and so this not causing too much trouble. I don't know what to do for restoring those tables, and not even know if that is possible or not. Please help. Attached is a table of primes that contains 2+ million primes yet it is so small that I can attached here. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Fw: problem open a innodb table
I messed up the ibdata1,ib_logfile1,0 files and encounter the problems of openning innodb tables even after I copied the back-up files of few months ago. I don't have many innodb tables and so this not causing too much trouble. I don't know what to do for restoring those tables, and not even know if that is possible or not. Please help. Attached is a table of primes that contains 2+ million primes yet it is so small that I can attached here. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
where clause
I have a table with a column pv (int) I want to fetch all the rows r such that (r.pv)+1 is the pv-value of another row. what the sql looks like? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: my.ini
Thanks a lot Andrew. - Original Message - From: andrew.2.mo...@nokia.com To: e...@pdtnetworks.net; mysql@lists.mysql.com Sent: Thursday, October 07, 2010 3:07 AM Subject: RE: my.ini Open you're my.ini and correct the values as stated in the event viewer Check the online documentation for the keys and values to set. http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html Regards -Original Message- From: ext Elim PDT [mailto:e...@pdtnetworks.net] Sent: 07 October 2010 04:51 To: mysql@lists.mysql.com Subject: my.ini I recently upgraded mysql from 5.1.9-beta to 5.1.50 Since I used the custom data dir path, my upgrade is basically a replacement of the bin folder and I used the old my.ini file that pointing the data dir to the right path. Then I found, in the event viewer, two warnings: (1) --myisam_max_extra_sort_file_size is deprecated and does nothing in this version. It will be removed in a future release. (2) '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead. I mainly used MyISAM tables but have a few of innodb tables. What should I do to correct the my.ini? Thanks for your help. EL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=e...@pdtnetworks.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.862 / Virus Database: 271.1.1/3182 - Release Date: 10/07/10 00:34:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
my.ini
I recently upgraded mysql from 5.1.9-beta to 5.1.50 Since I used the custom data dir path, my upgrade is basically a replacement of the bin folder and I used the old my.ini file that pointing the data dir to the right path. Then I found, in the event viewer, two warnings: (1) --myisam_max_extra_sort_file_size is deprecated and does nothing in this version. It will be removed in a future release. (2) '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead. I mainly used MyISAM tables but have a few of innodb tables. What should I do to correct the my.ini? Thanks for your help. EL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: prime number table
Thank you so much Chris. But is this mean that myisam table stores integer very differntly and inefficiently than InnoDB table? I see that oid can be ommitted. But in the case that I like to query on ordinal number of the prime. oid can make things faster, am i right? seperate the storage for descript is a great idea. thanks again! - Original Message - From: Chris W 4rfv...@cox.net To: Elim PDT e...@pdtnetworks.net Cc: mysql@lists.mysql.com Sent: Saturday, September 18, 2010 6:57 AM Subject: Re: prime number table Keep in mind that to store a number as a 32 bit unsigned integer takes 4 bytes of data no matter how small or large that number is as long as it is less than 4,294,267,296. If you store numbers as a string like it is in your file it takes 8 bits per digit so for 19,999,999 it would take 8 bytes or 64 bits. I assume the file has a line feed between each number, that is another 8 bits and may also have a carriage return adding another 8 bits. You could probably make your table even smaller if you were to move the descript filed to a new table. I highly recommend this option based on your statement that most of them are empty. A table with only integer values will have a fixed row size and probably be a little faster to access. If most of the rows will have descript text them leave it in this table, other wise it would be more efficient to keep in a different table. Also I'm not sure you really need an independent field for a primary key since the prime numbers could be used as the primary key. That would make your table even smaller. I'm not sure why you would what a table of prime numbers unless you had an index on that field and making it the primary key does that for you. Chris W On 9/17/2010 1:28 PM, Elim PDT wrote: I got a file of the list of the 1st 1270607 prime numbers (the 1270607th prime is 1999, beat the $227 book at http://www.amazon.com/prime-numbers-Carnegie-institution-Washington/dp/B0006AH1S8). the file is an output of a python script. the file size is about 12Mb. Then I created a simeple mysql table prime as mysql desc prime; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | oid | int(10) unsigned | NO | PRI | NULL| auto_increment | | pv | int(10) unsigned | YES | | NULL|| | descript | text | YES | | NULL|| +--+--+--+-+-++ mysql show create table prime; --+ | Table | Create Table --+ | prime | CREATE TABLE `prime` ( `oid` int(10) unsigned NOT NULL AUTO_INCREMENT, `pv` int(10) unsigned DEFAULT NULL, `descript` text, PRIMARY KEY (`oid`) ) ENGINE=MyISAM AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 The table file size is (prime.frm,prime.MYD,prime.MYI) = (9k; 24,817KB; 12,754KB) Then I do mysql create table prm select * from prime order by prime.oid; mysql alter table prm modify oid int unsigned primary key auto_increment; mysql desc prm; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | oid | int(10) unsigned | NO | PRI | NULL| auto_increment | | pv | int(10) unsigned | YES | | NULL|| | descript | text | YES | | NULL|| +--+--+--+-+-++ mysql show create table prm; +---+-- | Table | Create Table +---+-- | prm | CREATE TABLE `prm` ( `oid` int(10) unsigned NOT NULL AUTO_INCREMENT, `pv` int(10) unsigned DEFAULT NULL, `descript` text, PRIMARY KEY (`oid`) ) ENGINE=InnoDB AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 | +---+-- The table file prm.frm is only 9KB My question is that how come it's SO SMALL? (currently the colum description in both tables prime and prm are empty except one identical row, with very short string value. Is is recommend to index the other twoo columns? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=e...@pdtnetworks.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.851 / Virus Database: 271.1.1/3143 - Release Date: 09/18/10 00:34:00 -- MySQL General
prime number table
I got a file of the list of the 1st 1270607 prime numbers (the 1270607th prime is 1999, beat the $227 book at http://www.amazon.com/prime-numbers-Carnegie-institution-Washington/dp/B0006AH1S8). the file is an output of a python script. the file size is about 12Mb. Then I created a simeple mysql table prime as mysql desc prime; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | oid | int(10) unsigned | NO | PRI | NULL| auto_increment | | pv | int(10) unsigned | YES | | NULL|| | descript | text | YES | | NULL|| +--+--+--+-+-++ mysql show create table prime; --+ | Table | Create Table --+ | prime | CREATE TABLE `prime` ( `oid` int(10) unsigned NOT NULL AUTO_INCREMENT, `pv` int(10) unsigned DEFAULT NULL, `descript` text, PRIMARY KEY (`oid`) ) ENGINE=MyISAM AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 The table file size is (prime.frm,prime.MYD,prime.MYI) = (9k; 24,817KB; 12,754KB) Then I do mysql create table prm select * from prime order by prime.oid; mysql alter table prm modify oid int unsigned primary key auto_increment; mysql desc prm; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | oid | int(10) unsigned | NO | PRI | NULL| auto_increment | | pv | int(10) unsigned | YES | | NULL|| | descript | text | YES | | NULL|| +--+--+--+-+-++ mysql show create table prm; +---+-- | Table | Create Table +---+-- | prm | CREATE TABLE `prm` ( `oid` int(10) unsigned NOT NULL AUTO_INCREMENT, `pv` int(10) unsigned DEFAULT NULL, `descript` text, PRIMARY KEY (`oid`) ) ENGINE=InnoDB AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 | +---+-- The table file prm.frm is only 9KB My question is that how come it's SO SMALL? (currently the colum description in both tables prime and prm are empty except one identical row, with very short string value. Is is recommend to index the other twoo columns? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
5.1.x review
There are so many versions of 5.1, Is there some review or recommendations for a stable one? thanks
timezone questions
data from one server in timezone A ported to another server in timezone B, what will happen for the records with datetime columns? What is the recommended way of handling this kind of issues? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
create a user for app admin
Is there a way to create a user that has full control over a specified database but cannot access any other database at all? that user cannot delete the specified database, cannot create/delete other database, but can do everything within the specified database? Thanks for any help.
Re: Hard? query to with group order by group head's name
Hi Darryle, Your result was: +---+---+-+ | member_id | name | head_id | +---+---+-+ | 2 | Ann | 1 | | 3 | David | NULL | | 1 | Elim | NULL | | 5 | Jane | 3 | | 4 | John | 3 | +---+---+-+ which not groups correctly. Seems it's a hard query. - Original Message - From: Darryle Steplight dstepli...@gmail.com To: Elim PDT e...@pdtnetworks.net Cc: mysql@lists.mysql.com Sent: Wednesday, July 15, 2009 11:50 PM Subject: Re: Hard? query to with group order by group head's name Hi Elim, I didn't test it out but it sounds like you want to do this SELECT * FROM group_members GROUP BY head_id, member_id ORDER BY name ASC . On Thu, Jul 16, 2009 at 1:20 AM, Elim PDTe...@pdtnetworks.net wrote: My table group_member looks like this: +---+---+-+ | member_id | name | head_id | +---+---+-+ | 1 | Elim | NULL | | 2 | Ann | 1 | | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | +---+---+-+ Record with null head_id means the member is a group head. Record with head_id k are in the group with head whoes id equals k. I like to fetch the rows in the following ordaer | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | | 1 | Elim | NULL | | 2 | Ann | 1 | That is (1) A head-row follewed by the group members with that head (2)head rows are ordered alphabetically by name. What the query looks like? Thanks -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Hard? query to with group order by group head's name
My table group_member looks like this: +---+---+-+ | member_id | name | head_id | +---+---+-+ | 1 | Elim |NULL | | 2 | Ann | 1 | | 3 | David |NULL | | 4 | John | 3 | | 5 | Jane | 3 | +---+---+-+ Record with null head_id means the member is a group head. Record with head_id k are in the group with head whoes id equals k. I like to fetch the rows in the following ordaer | 3 | David |NULL | | 4 | John | 3 | | 5 | Jane | 3 | | 1 | Elim |NULL | | 2 | Ann | 1 | That is (1) A head-row follewed by the group members with that head (2)head rows are ordered alphabetically by name. What the query looks like? Thanks
