Re: InnoDB + FULLTEXT

[FalconSoft, Inc]

So, based on this article, they were looking for a coder in April of this 
past year.  Did anyone start working on it?  Does anyone know the status of 
the project?


Tim Gustafson
FalconSoft, Inc
[EMAIL PROTECTED]
http://falconsoft.com/
(831) 425-4522
(831) 621-6299 (Fax)

- Original Message - 
From: "Visolve DB Team" <[EMAIL PROTECTED]>

To: "FalconSoft, Inc" <[EMAIL PROTECTED]>; 
Sent: Wednesday, November 08, 2006 11:25 PM
Subject: Re: InnoDB + FULLTEXT


Hi,

Till MySQL 5.0 there was no support for FULLTEXT by InnoDB.  More info on: 
www.innodb.com/innodbtalkUC2005.pdf


Thanks
ViSolve DB Team.

- Original Message - 
From: "FalconSoft, Inc" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, November 09, 2006 6:28 AM
Subject: InnoDB + FULLTEXT



Does anyone know if/when InnoDB will support FULLTEXT indexes?  I have a
project that I'm working on now that really needs support for both.

Thanks!

Tim Gustafson
FalconSoft, Inc
[EMAIL PROTECTED]
http://falconsoft.com/
(831) 425-4522
(831) 621-6299 (Fax)


--
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: InnoDB + FULLTEXT

[Visolve DB Team]

Hi,

Till MySQL 5.0 there was no support for FULLTEXT by InnoDB.  More info on: 
www.innodb.com/innodbtalkUC2005.pdf 

Thanks
ViSolve DB Team.

- Original Message - 
From: "FalconSoft, Inc" <[EMAIL PROTECTED]>
To: 
Sent: Thursday, November 09, 2006 6:28 AM
Subject: InnoDB + FULLTEXT


> Does anyone know if/when InnoDB will support FULLTEXT indexes?  I have a 
> project that I'm working on now that really needs support for both.
> 
> Thanks!
> 
> Tim Gustafson
> FalconSoft, Inc
> [EMAIL PROTECTED]
> http://falconsoft.com/
> (831) 425-4522
> (831) 621-6299 (Fax)
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

access full-text index

[Rares Vernica]

Hi,

Is it possible to access the Full-Text Index structures from SQL?

Thanks a lot,
Ray


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

problem with starting mysql on mac osx 10.4

[uszek]

Hi, I constantly keep receiving this kind of error:

mariuszlenks-ibook-g4:~ mariuszlenk$ cd /usr/local/mysql
mariuszlenks-ibook-g4:/usr/local/mysql mariuszlenk$ sudo ./bin/mysqld_safe
Password:
./bin/mysqld_safe: line 1: ./bin/my_print_defaults: cannot execute binary file
./bin/mysqld_safe: line 1: ./bin/my_print_defaults: cannot execute binary file
Starting mysqld daemon with databases from /usr/local/mysql/data
STOPPING server from pid file 
/usr/local/mysql/data/mariuszlenks-ibook-g4.local.pid
061108 23:28:05  mysqld ended


I tried many other tricks, but the installation seems to be solvable by another 
smart one. Please help me.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: BINARY(N) as primary key?

[Jon Frisby]

It's my understanding that a PK in MySQL is basically the same as a  
unique index -- for MyISAM tables at least.  For InnoDB it's a bit  
different with InnoDB storing rows within the PK index (and inserting  
a hidden PK if none is provided).


In short:  I don't think you'll see any better performance by making  
your PK be this BINARY column, instead of just using an  
auto_increment PK column and having a UNIQUE constraint on your  
BINARY column.  I doubt you'll see significantly worse performance by  
doing it either.


-JF


On Nov 8, 2006, at 6:02 PM, Michael Fischer wrote:


On Wed, Nov 08, Jon Frisby wrote:


On Nov 7, 2006, at 6:47 PM, Michael Fischer wrote:



Any thoughts on using BINARY(N) or CHAR(N) as a primary key?

Performance issues? In mysql, in general?

Yes, in the context of the application, there is a very good
reason for doing this, and not using an auto increment integer.



I'm curious to know why simply having a UNIQUE constraint on the
column is inadequate...

-JF


I'm concerned with performance, not integrity constraints.

I was wondering, with respect to mysql internals, whether there
was any substantial penalty to using CHAR or BINARY vs. INTEGER
based primary keys. I imagine, though I have not probed the
source code, that comparisons are done with bitwise-ands, or
memcmp(3). For this of course, VARCHAR and VARBINARY fields
would be a pain, because, from row to row, you couldn't
accurately optimize for the width of the data in the key field.
However, it might be perfectly reasonable to do so with fixed
length CHAR or BINARY ones.

Thanks.

Michael
--
Michael Fischer Happiness is a config option.
[EMAIL PROTECTED]Recompile and be happy.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: BINARY(N) as primary key?

[Michael Fischer]

On Wed, Nov 08, Jon Frisby wrote:

> On Nov 7, 2006, at 6:47 PM, Michael Fischer wrote:
> 
> >
> > Any thoughts on using BINARY(N) or CHAR(N) as a primary key?
> >
> > Performance issues? In mysql, in general?
> >
> > Yes, in the context of the application, there is a very good
> > reason for doing this, and not using an auto increment integer.

> I'm curious to know why simply having a UNIQUE constraint on the  
> column is inadequate...
> 
> -JF

I'm concerned with performance, not integrity constraints.

I was wondering, with respect to mysql internals, whether there
was any substantial penalty to using CHAR or BINARY vs. INTEGER
based primary keys. I imagine, though I have not probed the 
source code, that comparisons are done with bitwise-ands, or 
memcmp(3). For this of course, VARCHAR and VARBINARY fields
would be a pain, because, from row to row, you couldn't 
accurately optimize for the width of the data in the key field.
However, it might be perfectly reasonable to do so with fixed
length CHAR or BINARY ones.

Thanks.

Michael
-- 
Michael Fischer Happiness is a config option.
[EMAIL PROTECTED]Recompile and be happy. 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

InnoDB + FULLTEXT

[FalconSoft, Inc]

Does anyone know if/when InnoDB will support FULLTEXT indexes?  I have a 
project that I'm working on now that really needs support for both.


Thanks!

Tim Gustafson
FalconSoft, Inc
[EMAIL PROTECTED]
http://falconsoft.com/
(831) 425-4522
(831) 621-6299 (Fax)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

installing mysql on OSX 10.4

[uszek]

This problem is driving me crazy. Please help.

127:~ mariuszlenk$ cd /usr/local/mysql
127:/usr/local/mysql mariuszlenk$ sudo ./bin/mysqld_safe
Password:
./bin/mysqld_safe: line 1: ./bin/my_print_defaults: cannot execute binary file
./bin/mysqld_safe: line 1: ./bin/my_print_defaults: cannot execute binary file
Starting mysqld daemon with databases from /usr/local/mysql/data
STOPPING server from pid file /usr/local/mysql/data/127.0.0.1.pid
061108 00:31:05  mysqld ended

this is kind of error im getting all the time round? could you help me to solve 
it?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Optimize question

[Francis]

Hi list,

I have a table with about 17 millons of records. This table
contain log of web for one month and I have an other table but with only
1 day of log.

If I do a select to get the log for this date 2006-01-01 :
Select * from mytable where mydate => '2006-01-01' and mydate <=
'2006-01-01'. 

Do you think is more faster to do on table with have only one date or is
the same think if I do it on table whit all day ? 

And last question, if I do a group by, I think the group by is
faster on column as type INT Vs Varchar ?

Ty for your reply 




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: BINARY(N) as primary key?

[Jon Frisby]

I'm curious to know why simply having a UNIQUE constraint on the  
column is inadequate...


-JF

On Nov 7, 2006, at 6:47 PM, Michael Fischer wrote:



Any thoughts on using BINARY(N) or CHAR(N) as a primary key?

Performance issues? In mysql, in general?

Yes, in the context of the application, there is a very good
reason for doing this, and not using an auto increment integer.


Michael
--
Michael Fischer Happiness is a config option.
[EMAIL PROTECTED]Recompile and be happy.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Regular expression not working as expected (documented)?

[Leandro Guimarães Faria Corcete DUTRA]

Em Qua, 2006-11-08 às 12:53 -0600, Dan Nelson escreveu:
> "." within brackets is treated literally anyway, so there's no need to
> use "[.period.]".

OK, I had taken the effects of - as being the effects of .

 
> REGEXP BINARY '^wa[bhkl][-_.acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ].*'

It indeeds works much better.

I tried to improve it:

'^wa[bhkl][-_.acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ][-_.acegmnopqrsuvwxyz]*'

This is to account that the last characters can’t have ascendents
(don’t ask me why), but it still doesn’t seem to be quite right:

[…]
| wabatizelli | -- I don’t want two ‘l’s
| wabetosp| -- I want one of [23890IJOQ] after that t
| walvent33   | -- I don’t want two ‘3’s, only one.
[…]


I tried putting {1} after each of [14Lt] and [23890IJOQ] to no avail;
and MySQL didn’t accept *? at the end, to become non-greedy.

Perhaps regexps are just too hard to me…

-- 
+55 (11) 5685 2219   xmpp:[EMAIL PROTECTED]
+55 (11) 9406 7191  Yahoo!: ymsgr:sendIM?lgcdutra
+55 (11) 5686 9607  ICQ/AIM: aim:GoIM?screenname=61287803
MSN: msnim:[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Regular expression not working as expected (documented)?

[Christian Hammers]

On 2006-11-08 Leandro Guimarães Faria Corcete DUTRA wrote:
> > > u.login REGEXP 
> > > '^wa[bhkl][_[.period.]-acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ].*...
> > > But I get, among otherss, a string beginning with 'walt' (several, in 
> > > fact).
> > 
> > Probably MySQL don't know about this "[.period.]" thing and just takes the 
> > "." in it
> > as "any char", e.g. your "t" from "walt".
> 
>   I got that from cname.h, mentioned in the MySQL regexp documentation.
> 
> > Try '^wa[bhkl][-_acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ].*', if there is a 
> > "-" at the
> > beginning of a character class it is usually threaten literally as it makes 
> > no sense
> > as "from..to" separator in those cases.
> 
>   Hm, but I do need to take into account the period.
Right, the "[.period.]" really exists, so you can safly insert it. But only 
*after* 
this "-" character!

>   Also, do you mean the - at the beginning will make all following
> characters be treated literally?
Hm, no, the "-" inside "[" and "]" is a special character, i.e. "[a-z]" means 
all chars
from a to z. So in your case that would have been all chars from . to a which 
makes not
much sense. If you write "[-az]" on the other hand that means the dash char or 
a or z.
So just move the "-" right after the "[" and you're done.

"walt12" -> false
"wal.12" -> true
"wala12" -> true

bye,

-christian-

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Regular expression not working as expected (documented)?

[Jerry Schwartz]

According to the documentation, [.period.] should match but so should [...]
(if I read it right).

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -Original Message-
> From: Christian Hammers [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, November 08, 2006 1:16 PM
> To: Leandro Guimarães Faria Corcete DUTRA
> Cc: mysql@lists.mysql.com
> Subject: Re: Regular expression not working as expected (documented)?
>
>
>
> On 2006-11-08 Leandro Guimarães Faria Corcete DUTRA wrote:
> > u.login REGEXP
> '^wa[bhkl][_[.period.]-acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ].*'
> ...
> > But I get, among otherss, a string beginning with 'walt'
> (several, in fact).
>
> Probably MySQL don't know about this "[.period.]" thing and
> just takes the "." in it
> as "any char", e.g. your "t" from "walt".
>
> Try '^wa[bhkl][-_acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ].*',
> if there is a "-" at the
> beginning of a character class it is usually threaten
> literally as it makes no sense
> as "from..to" separator in those cases.
>
> bye,
>
> -christian-
>
> --
> 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: Regular expression not working as expected (documented)?

[Dan Nelson]

In the last episode (Nov 08), Leandro Guimar~aes Faria Corcete DUTRA said:
> Em Qua, 2006-11-08 às 19:16 +0100, Christian Hammers escreveu:
> > 
> > On 2006-11-08 Leandro Guimar~aes Faria Corcete DUTRA wrote:
> > > u.login REGEXP 
> > > '^wa[bhkl][_[.period.]-acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ].*'
> > ...
> > > But I get, among otherss, a string beginning with 'walt'
> > > (several, in fact).
> > 
> > Probably MySQL don't know about this "[.period.]" thing and just
> > takes the "." in it as "any char", e.g. your "t" from "walt".
> 
>   I got that from cname.h, mentioned in the MySQL regexp documentation.
> 
> > Try '^wa[bhkl][-_acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ].*', if
> > there is a "-" at the beginning of a character class it is usually
> > threaten literally as it makes no sense as "from..to" separator in
> > those cases.
> 
>   Hm, but I do need to take into account the period.

"." within brackets is treated literally anyway, so there's no need to
use "[.period.]".
 
>   Also, do you mean the - at the beginning will make all
> following characters be treated literally?

No, but it prevents - from being parsed as a range operator. "[.-a]"
matches any character between ascii 46 and 97.  Also, since mysql uses
case-insensitive compares by default, it will match any lowercase
character (since the uppercase characters between 65 and 90 are within
your range).  That's why the "t" in "walt" was matching.  The right
syntax for your original regex would probably look like:

REGEXP BINARY '^wa[bhkl][-_.acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ].*'

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Regular expression not working as expected (documented)?

[Leandro Guimarães Faria Corcete DUTRA]

Em Qua, 2006-11-08 às 19:16 +0100, Christian Hammers escreveu:
> 
> On 2006-11-08 Leandro Guimarães Faria Corcete DUTRA wrote:
> > u.login REGEXP 
> > '^wa[bhkl][_[.period.]-acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ].*'
> ...
> > But I get, among otherss, a string beginning with 'walt' (several, in fact).
> 
> Probably MySQL don't know about this "[.period.]" thing and just takes the 
> "." in it
> as "any char", e.g. your "t" from "walt".

I got that from cname.h, mentioned in the MySQL regexp documentation.


> Try '^wa[bhkl][-_acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ].*', if there is a 
> "-" at the
> beginning of a character class it is usually threaten literally as it makes 
> no sense
> as "from..to" separator in those cases.

Hm, but I do need to take into account the period.

Also, do you mean the - at the beginning will make all following
characters be treated literally?


-- 
+55 (11) 5685 2219   xmpp:[EMAIL PROTECTED]
+55 (11) 9406 7191  Yahoo!: ymsgr:sendIM?lgcdutra
+55 (11) 5686 9607  ICQ/AIM: aim:GoIM?screenname=61287803
MSN: msnim:[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Regular expression not working as expected (documented)?

[Christian Hammers]

On 2006-11-08 Leandro Guimarães Faria Corcete DUTRA wrote:
> u.login REGEXP 
> '^wa[bhkl][_[.period.]-acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ].*'
...
> But I get, among otherss, a string beginning with 'walt' (several, in fact).

Probably MySQL don't know about this "[.period.]" thing and just takes the "." 
in it
as "any char", e.g. your "t" from "walt".

Try '^wa[bhkl][-_acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ].*', if there is a "-" 
at the
beginning of a character class it is usually threaten literally as it makes no 
sense
as "from..to" separator in those cases.

bye,

-christian-

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Regular expression not working as expected (documented)?

[Leandro Guimarães Faria Corcete DUTRA]

I must have understood something wrong in the regexps docs:

 SELECT
login
   FROM
orolixMain.Usersu
  WHERE
u.login REGEXP 
'^wa[bhkl][_[.period.]-acegmnopqrsuvwxyz]{1,5}[14Lt][23890IJOQ].*'
;

[EMAIL PROTECTED](none)> \. usr/src/oro/qry/phshr.sql
+---+
| login |
+---+
[…]
| walt[…]   | 
[…]
+---+
49 rows in set (4.46 sec)


The regexp I used was to return strings began by wa, followed by [bhkl],
and then by one to five characters in the [_[.period.]-acegmnopqrsuvwxyz] list.
But I get, among otherss, a string beginning with 'walt' (several, in fact).

Ideas?

-- 
Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel)
Administrador de (Bases de) Dados +55 (11) 2122 0302 (com)
http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res)
BRASILmailto:[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Re: MySQL on a ram disk?

[Dan Buettner]

Depends on the value of your data, and what you might want to invest
in cluster setup (time, money) to get this done.

Another simple option from a hardware perspective might be the use of
a PCI card with RAM that serves as a Serial ATA disk.  They're
relatively new on the market but they're out there, and not too
expensive.  If you set that up as the storage point for the database
in question, and possibly also moved MySQL temp space onto it, it
could provide a speed boost.  Seems like you could also use two or
more of these cards and software or hardware RAID for even more speed.

Not exactly enterprise-grade clustering but may suit your needs.

Dan

On 11/8/06, Ryan Stille <[EMAIL PROTECTED]> wrote:

Maybe I should just be looking at using a HEAP table?

-Ryan


--
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: Undelete a droped table

[Scott Johnson]

Sorry, Microsoft SQL!



From: "Panos Tsapralis" <[EMAIL PROTECTED]>
Sent: Wednesday, November 08, 2006 6:48 AM
To: [EMAIL PROTECTED]
Subject: Re: Undelete a droped table 

I suppose you mySQL - not mSQL (there was a lite RDBMS once named mSQL - I 
don't know whether it exists any more...).

On 11/8/06, scott < [EMAIL PROTECTED]> wrote:As a note: mSQL has an attach 
function that will allow you to take those files and recreate the database by 
only giving it the database name and the file names. It's a way to put back a 
backup made of the physical database if you backup directly from the SQL data 
directory and not through the SQL backup utility and then through your tape or 
other backup. 

Scott.


From: "Panos Tsapralis" <[EMAIL PROTECTED]>
Sent: Tuesday, November 07, 2006 1:10 PM 
To: "Eric Lommatsch" <[EMAIL PROTECTED]>
Subject: Re: Undelete a droped table  

Eric, as far as SQL is concerned, there is no way to recover dropped tables
and, to my knowledge, there is no MYSQL-specific way to do that either, so,
your only hope is to have a rather fresh backup of your database. 

BTW, this gives me an idea: I suppose that dropping a database table is a
two-task process, namely deleting the table's attributes from the database
metadata (an SQL operation) and deleting the table's files from the 
file-system. So, wouldn't it be preferrable to move the table's files into
the Recycle Bin (or the Trash or some other "stand-by" place into the
file-system) instead of permanently deleting them? In that case, it would be 
possible to recover a dropped table by creating a blank table into the
database (equivalent to the one that got dropped) and then replace its empty
data files with those from the Recycle Bin.

HTH,

Panos Tsapralis.

On 11/7/06, Eric Lommatsch wrote:
>
> Hello,
>
> I have accidently droped a table from one of my database's. The table was
> an
> innodb table. Is there anyway that I can recover the table or is that 
> information gone for good if I don't have a current backup of that table?
>
> Thank you
>
> Eric H. Lommatsch
> Programmer
> MICRONix, Inc. 
> 2087 South Grant Street
> Denver, CO 80210
> Tel 303-777-8939
> Fax 303-778-0378
>
> [EMAIL PROTECTED]
>
>
>

-- 
Panos Tsapralis,
Senior Software / Systems Engineer,
+306948076902,
Athens, GREECE.

-- 
Panos Tsapralis,
Senior Software / Systems Engineer,
+306948076902,
Athens, GREECE.

Re: Undelete a droped table

[Panos Tsapralis]

I suppose you mySQL - not mSQL (there was a lite RDBMS once named mSQL - I
don't know whether it exists any more...).

On 11/8/06, scott <[EMAIL PROTECTED]> wrote:


As a note: mSQL has an attach function that will allow you to take those
files and recreate the database by only giving it the database name and the
file names. It's a way to put back a backup made of the physical database if
you backup directly from the SQL data directory and not through the SQL
backup utility and then through your tape or other backup.

Scott.

 --
*From*: "Panos Tsapralis" <[EMAIL PROTECTED]>
*Sent*: Tuesday, November 07, 2006 1:10 PM
*To*: "Eric Lommatsch" <[EMAIL PROTECTED]>
*Subject*: Re: Undelete a droped table

Eric, as far as SQL is concerned, there is no way to recover dropped
tables
and, to my knowledge, there is no MYSQL-specific way to do that either,
so,
your only hope is to have a rather fresh backup of your database.

BTW, this gives me an idea: I suppose that dropping a database table is a
two-task process, namely deleting the table's attributes from the database
metadata (an SQL operation) and deleting the table's files from the
file-system. So, wouldn't it be preferrable to move the table's files into
the Recycle Bin (or the Trash or some other "stand-by" place into the
file-system) instead of permanently deleting them? In that case, it would
be
possible to recover a dropped table by creating a blank table into the
database (equivalent to the one that got dropped) and then replace its
empty
data files with those from the Recycle Bin.

HTH,

Panos Tsapralis.

On 11/7/06, Eric Lommatsch wrote:
>
> Hello,
>
> I have accidently droped a table from one of my database's. The table
was
> an
> innodb table. Is there anyway that I can recover the table or is that
> information gone for good if I don't have a current backup of that
table?
>
> Thank you
>
> Eric H. Lommatsch
> Programmer
> MICRONix, Inc.
> 2087 South Grant Street
> Denver, CO 80210
> Tel 303-777-8939
> Fax 303-778-0378
>
> [EMAIL PROTECTED]
>
>
>


--
Panos Tsapralis,
Senior Software / Systems Engineer,
+306948076902,
Athens, GREECE.






--
Panos Tsapralis,
Senior Software / Systems Engineer,
+306948076902,
Athens, GREECE.

Re: V 5.0.18 on Mac OS X

[Dan Buettner]

Good morning, Steffan -

Can you post some details about what you mean when you say the tables
are damaged?  What makes you say that / what are the signs/symptoms?

Does the server process crash, or stop responding?  Do you see
anything in the error logs?  Do you have enough disk space for temp
tables?

Can you post the output of SHOW STATUS; after it has been running for
a while as well ?

If the server truly is just being overloaded, then an analysis of the
problematic/slow queries would be in order.  Could be that better
queries and/or additional indices could help.  Still, I'd like to know
more about what is happening with the damaged tables.

I don't know of any specific problems with 5.0.18, but it generally
doesn't hurt to upgrade to the latest production release.  The release
notes are online so you could search for relevant fixes before
deciding to download and upgrade.

Dan


On 11/8/06, Steffan A. Cline <[EMAIL PROTECTED]> wrote:

I am having an issue with MySQL running on Mac OS X. Currently the version
as stated 5.0.18 on a dual G4 Xserve with 1gb of ram. MySQL is mainly the
only thing running on this server. I am trying to track down an issue in
which MySQL is being overloaded and it consistently damages the same one or
two tables. I am trying to narrow down the issue to the web service
connecting to MySQL or MySQL itself. When I check the status I see a ton of
locks and unauthenticated connections. Any suggestions of what to look for
on the MySQL side? It seems rather odd that being overloaded is that it
damages the tables. There is no replication or auto backups in place with
this that could cause these issues. Some of the queries thrown are indeed
big ones and do require many ticks to calculate but still, what is left? I
just made some changes to the config and this is now the base of what I
have:

# The MySQL server
[mysqld]
port= 3306
socket  = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 50M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
#thread_cache_size = 8
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 128M
# added the next few lines for debugging all the way to the next comment
skip-name-resolve
interactive_timeout = 300
wait_timeout = 300
max_connections = 250
thread_cache_size = 40
log_error = /var/log/mysqld-error.log
log_slow_queries = /var/log/mysqld-slow.log
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8


Could it be the version of MySQL that is causing the damage? I did a fsck on
the drive and all comes back fine. What's left?


Thanks

Steffan

---
T E L  6 0 2 . 5 7 9 . 4 2 3 0 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
[EMAIL PROTECTED] Phoenix, Az
http://www.ExecuChoice.net  USA
AIM : SteffanC  ICQ : 57234309
  Lasso Partner Alliance Member
---




--
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: Undelete a droped table

[scott]

As a note: mSQL has an attach function that will allow you to 
take those files and recreate the database by only giving it the database name 
and the file names. It's a way to put back a backup made of the physical 
database if you backup directly from the SQL data directory and not through the 
SQL backup utility and then through your tape or other backup.

Scott.



From: "Panos Tsapralis" <[EMAIL PROTECTED]>
Sent: Tuesday, November 07, 2006 1:10 PM
To: "Eric Lommatsch" <[EMAIL PROTECTED]>
Subject: Re: Undelete a droped table 

Eric, as far as SQL is concerned, there is no way to recover dropped tables
and, to my knowledge, there is no MYSQL-specific way to do that either, so,
your only hope is to have a rather fresh backup of your database.

BTW, this gives me an idea: I suppose that dropping a database table is a
two-task process, namely deleting the table's attributes from the database
metadata (an SQL operation) and deleting the table's files from the
file-system. So, wouldn't it be preferrable to move the table's files into
the Recycle Bin (or the Trash or some other "stand-by" place into the
file-system) instead of permanently deleting them? In that case, it would be
possible to recover a dropped table by creating a blank table into the
database (equivalent to the one that got dropped) and then replace its empty
data files with those from the Recycle Bin.

HTH,

Panos Tsapralis.

On 11/7/06, Eric Lommatsch wrote:
>
> Hello,
>
> I have accidently droped a table from one of my database's. The table was
> an
> innodb table. Is there anyway that I can recover the table or is that
> information gone for good if I don't have a current backup of that table?
>
> Thank you
>
> Eric H. Lommatsch
> Programmer
> MICRONix, Inc.
> 2087 South Grant Street
> Denver, CO 80210
> Tel 303-777-8939
> Fax 303-778-0378
>
> [EMAIL PROTECTED]
>
>
>

-- 
Panos Tsapralis,
Senior Software / Systems Engineer,
+306948076902,
Athens, GREECE.

Re: MySQL on a ram disk?

[Ryan Stille]

Maybe I should just be looking at using a HEAP table?

-Ryan


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MySQL on a ram disk?

[Jimmy Guerrero]

Hello,

Depending on the characteristics of the data and baring dependencies on
specific features like FKs or complex JOINs, you may want to take a look at
MySQL Cluster.

MySQL Cluster supports in-memory and disk-based databases. So, it may give
you the high-performance characteristics you are looking for, with the added
benefit of high availability.

More info at: http://www.mysql.com/products/database/cluster/

Thanks,

Jimmy Guerrero
Sr Product Manager
MySQL, Inc

> -Original Message-
> From: Ryan Stille [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, November 08, 2006 8:22 AM
> To: mysql@lists.mysql.com
> Subject: MySQL on a ram disk?
> 
> We have an intense data process that runs every few minutes, 
> clearing and then loading a database with thousands of 
> records of data, which are then queried on from a website.  
> The periodic load takes about 20 seconds.  Some of the front 
> end select queries take a second or two.  
> This is all running on MSSQL, and we are wondering if we 
> could find any 
> speed improvements by moving to MySQL, possibly running on a 
> ram disk.   
> Any input appreciated.
> 
> Thanks,
> -Ryan
> 
> 
> 
> --
> 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]

MySQL on a ram disk?

[Ryan Stille]

We have an intense data process that runs every few minutes, clearing 
and then loading a database with thousands of records of data, which are 
then queried on from a website.  The periodic load takes about 20 
seconds.  Some of the front end select queries take a second or two.  
This is all running on MSSQL, and we are wondering if we could find any 
speed improvements by moving to MySQL, possibly running on a ram disk.   
Any input appreciated.


Thanks,
-Ryan



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

V 5.0.18 on Mac OS X

[Steffan A. Cline]

I am having an issue with MySQL running on Mac OS X. Currently the version
as stated 5.0.18 on a dual G4 Xserve with 1gb of ram. MySQL is mainly the
only thing running on this server. I am trying to track down an issue in
which MySQL is being overloaded and it consistently damages the same one or
two tables. I am trying to narrow down the issue to the web service
connecting to MySQL or MySQL itself. When I check the status I see a ton of
locks and unauthenticated connections. Any suggestions of what to look for
on the MySQL side? It seems rather odd that being overloaded is that it
damages the tables. There is no replication or auto backups in place with
this that could cause these issues. Some of the queries thrown are indeed
big ones and do require many ticks to calculate but still, what is left? I
just made some changes to the config and this is now the base of what I
have:

# The MySQL server
[mysqld]
port= 3306
socket  = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 50M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
#thread_cache_size = 8
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 128M
# added the next few lines for debugging all the way to the next comment
skip-name-resolve
interactive_timeout = 300
wait_timeout = 300
max_connections = 250
thread_cache_size = 40
log_error = /var/log/mysqld-error.log
log_slow_queries = /var/log/mysqld-slow.log
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8


Could it be the version of MySQL that is causing the damage? I did a fsck on
the drive and all comes back fine. What's left?


Thanks

Steffan

---
T E L  6 0 2 . 5 7 9 . 4 2 3 0 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
[EMAIL PROTECTED] Phoenix, Az
http://www.ExecuChoice.net  USA
AIM : SteffanC  ICQ : 57234309
  Lasso Partner Alliance Member
---




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: wait_timeout help

[Sayed Hadi Rastgou Haghi]

Linux Redhat ASEL 4.0

On 11/8/06, Visolve DB Team <[EMAIL PROTECTED]> wrote:


 Hi

Actually I don't get any idle processes listed.. but have came across such
mails..

On which platform you are running mysql?

I guess this might be:
If Windows, the server will drop the connection probably because of
'wait_timeout' expired.
For others, the mysql gets reconnected with 'reconnect flag' set to 1 in
the MySQL structure...

Thanks
ViSolve DB Team

- Original Message -
*From:* Sayed Hadi Rastgou Haghi <[EMAIL PROTECTED]>
*To:* Visolve DB Team <[EMAIL PROTECTED]>
*Cc:* mysql@lists.mysql.com
*Sent:* Wednesday, November 08, 2006 11:51 AM
*Subject:* Re: wait_timeout help

Hi
I set the wait_timeout in my.cnf.
Sometimes it works well and there is no idle process 120 seconds time.
But sometimes there are idle processes with 1900s time.

What the reason can be?

On 11/7/06, Visolve DB Team <[EMAIL PROTECTED]> wrote:
>
>  Hi
>
>  According to the VARIABLE "wait_timeout"  [default: 28800 seconds] a
> running MySQL daemon clears up idle connections if their
> "idle period" > "wait_timeout".so the sleeping threads will be
> automatically cleared if the time exceeds 'wait_timeout' variable value.
>
> 'wait_timeout' variable : The number of seconds the server waits for
> activity on a non-interactive connection before closing it. This timeout
> applies only to TCP/IP connections, not to connections made via Unix socket
> files, named pipes, or shared memory.
> 'interactive_timeout' variable's value or wait_timeout variables values
> -depends on the type of client (as defined by the CLIENT_INTERACTIVE connect
> option to mysql_real_connect())
>
> Thanks
> ViSolve DB Team.
>
> - Original Message -
> *From:* Sayed Hadi Rastgou Haghi <[EMAIL PROTECTED]>
> *To:* Visolve DB Team <[EMAIL PROTECTED]>
> *Cc:* mysql@lists.mysql.com
>  *Sent:* Tuesday, November 07, 2006 4:12 PM
> *Subject:* Re: wait_timeout help
>
> Thanks again
> I have some questions that may help me explain my problem well.
>
> 1- What's this mean:
> mysql> SHOW PROCESSLIS;
> 
+--+---+--++---+-++-+
>
>  | Id   | User | Host   |db   | Command | Time | State |
> Info   |
>
> 
+--+---+--++---+-+-++
> |1   | usr  | myhost:36336 | mydb | Sleep   | 2587  |  |
> NULL |
> |   11  | usr  | myhost:36341 | mydb | Sleep   | 2587  |  |
> NULL |
> |   12  | usr  | myhost:36348 | mydb | Sleep   | 2587  |  |
> NULL |
> | 3732| usr  | myhost:43940  | mydb | Sleep   | 2587  | |
> NULL |
>
> 
+--+---+--++---+-+-++
>
> Are these processes idle?
> If yes why MySQL doesn't kill them?
>
>
> 2- If I set wait_timeout variable, will they be killed on that time?
> 3- If I set interactive_timeout, what will happen to queries that take
> time longer than interactive_timeout?
>
> On 11/7/06, Visolve DB Team <[EMAIL PROTECTED]> wrote:
> >
> > Hi
> >
> > Try command line option, like
> >
> > mysql >set @@session.wait_timeout=15;
> > mysql> show variables;
> >
> >
> > Thanks
> > Visolve DB Team.
> > - Original Message -
> > From: "Sayed Hadi Rastgou Haghi" < [EMAIL PROTECTED]>
> > To: "Visolve DB Team" <[EMAIL PROTECTED]>
> > Cc: < mysql@lists.mysql.com>
> > Sent: Tuesday, November 07, 2006 3:21 PM
> > Subject: Re: wait_timeout help
> >
> >
> > > Thanks
> > > But this sets. GLOBAL wait_timeout variable.
> > > Cab be seen by SHOW GLOBAL VARIABLES not by SHOW VARIABLES;
> > >
> > > On 11/7/06, Visolve DB Team <[EMAIL PROTECTED]> wrote:
> > >>
> > >> Hi
> > >>
> > >> specify it in the .cnf file in the mysqld options:   wait_timeout=x
> >
> > >> or specify it at the command line  :  set wait_timeout=x
> > >> or start mysqld with --wait_timeout=x along with other options.
> > >>
> > >> Thanks
> > >> ViSolve DB Team
> > >> - Original Message -
> > >> From: "Sayed Hadi Rastgou Haghi" <[EMAIL PROTECTED]>
> > >> To: 
> > >> Sent: Tuesday, November 07, 2006 2:08 PM
> > >> Subject: wait_timeout help
> > >>
> > >>
> > >> > Dear All,
> > >> >
> > >> > I want to set wait_timeout variable on mysqld startup.
> > >> > Is there any way to do that?
> > >> >
> > >> > --
> > >> > Sincerely,
> > >> > Hadi Rastgou
> > >> > A Google Account is the key that unlocks the world of Google.
> > >> > http://www.spreadfirefox.com/?q=affiliates&id=0&t=1
> > ">
> > >> > Get
> > >> > FireFox! 
> > >> >
> > >>
> > >>
> > >
> > >
> > > --
> > > Sincerely,
> > > Hadi Rastgou
> > > A Google Account is the key that unlocks the world of Google.
> > > http://www.spreadfirefox.com/?q=affiliates&id=0&t=1
> > ">
> > > Get
> > > FireFox! 
> > >
> >
> >
>
>
> --
>

Re: wait_timeout help

[Visolve DB Team]

Hi

Actually I don't get any idle processes listed.. but have came across such 
mails..

On which platform you are running mysql?

I guess this might be:
If Windows, the server will drop the connection probably because of 
'wait_timeout' expired.
For others, the mysql gets reconnected with 'reconnect flag' set to 1 in the 
MySQL structure...

Thanks
ViSolve DB Team
  - Original Message - 
  From: Sayed Hadi Rastgou Haghi 
  To: Visolve DB Team 
  Cc: mysql@lists.mysql.com 
  Sent: Wednesday, November 08, 2006 11:51 AM
  Subject: Re: wait_timeout help


  Hi
  I set the wait_timeout in my.cnf.
  Sometimes it works well and there is no idle process 120 seconds time.
  But sometimes there are idle processes with 1900s time.

  What the reason can be?


  On 11/7/06, Visolve DB Team <[EMAIL PROTECTED]> wrote:
Hi

 According to the VARIABLE "wait_timeout"  [default: 28800 seconds] a 
running MySQL daemon clears up idle connections if their 
"idle period" > "wait_timeout".so the sleeping threads will be 
automatically cleared if the time exceeds 'wait_timeout' variable value.
'wait_timeout' variable : The number of seconds the server waits for 
activity on a non-interactive connection before closing it. This timeout 
applies only to TCP/IP connections, not to connections made via Unix socket 
files, named pipes, or shared memory. 

'interactive_timeout' variable's value or wait_timeout variables values 
-depends on the type of client (as defined by the CLIENT_INTERACTIVE connect 
option to mysql_real_connect())

Thanks
ViSolve DB Team.
  - Original Message - 
  From: Sayed Hadi Rastgou Haghi 
  To: Visolve DB Team 
  Cc: mysql@lists.mysql.com 
  Sent: Tuesday, November 07, 2006 4:12 PM
  Subject: Re: wait_timeout help


  Thanks again 
  I have some questions that may help me explain my problem well.

  1- What's this mean:
  mysql> SHOW PROCESSLIS;
  
+--+---+--++---+-++-+
 
   | Id   | User | Host   |db   | Command | Time | State | 
Info   |
  
+--+---+--++---+-+-++
  |1   | usr  | myhost:36336 | mydb | Sleep   | 2587  |  | 
NULL | 
  |   11  | usr  | myhost:36341 | mydb | Sleep   | 2587  |  | 
NULL |
  |   12  | usr  | myhost:36348 | mydb | Sleep   | 2587  |  | 
NULL |
  | 3732| usr  | myhost:43940  | mydb | Sleep   | 2587  | | 
NULL | 
  
+--+---+--++---+-+-++

  Are these processes idle?
  If yes why MySQL doesn't kill them?


  2- If I set wait_timeout variable, will they be killed on that time?
  3- If I set interactive_timeout, what will happen to queries that take 
time longer than interactive_timeout?


  On 11/7/06, Visolve DB Team <[EMAIL PROTECTED]> wrote: 
Hi

Try command line option, like

mysql >set @@session.wait_timeout=15;
mysql> show variables;


Thanks
Visolve DB Team.
- Original Message -
From: "Sayed Hadi Rastgou Haghi" < [EMAIL PROTECTED]>
To: "Visolve DB Team" <[EMAIL PROTECTED]>
Cc: < mysql@lists.mysql.com>
Sent: Tuesday, November 07, 2006 3:21 PM
Subject: Re: wait_timeout help


> Thanks
> But this sets. GLOBAL wait_timeout variable.
> Cab be seen by SHOW GLOBAL VARIABLES not by SHOW VARIABLES; 
>
> On 11/7/06, Visolve DB Team <[EMAIL PROTECTED]> wrote:
>>
>> Hi
>>
>> specify it in the .cnf file in the mysqld options:   wait_timeout=x 
>> or specify it at the command line  :  set wait_timeout=x
>> or start mysqld with --wait_timeout=x along with other options.
>>
>> Thanks
>> ViSolve DB Team
>> - Original Message - 
>> From: "Sayed Hadi Rastgou Haghi" <[EMAIL PROTECTED]>
>> To: 
>> Sent: Tuesday, November 07, 2006 2:08 PM
>> Subject: wait_timeout help
>>
>>
>> > Dear All,
>> >
>> > I want to set wait_timeout variable on mysqld startup. 
>> > Is there any way to do that?
>> >
>> > --
>> > Sincerely,
>> > Hadi Rastgou
>> > A Google Account is the key that unlocks the world of Google.
>> > http://www.spreadfirefox.com/?q=affiliates&id=0&t=1 ">
>> > Get
>> > FireFox!  
>> >
>>
>>
>
>
> --
> Sincerely,
> Hadi Rastgou
> A Google Account is the key that unlocks the world of Google.
> http://www.spreadfirefox.com/?q=affiliates&id=0&t=1";>
> Get
> FireF