RE: can I recover a database from db files?

[Logan, David (SST - Adelaide)]

Hi Patrick,

The error says a permissions problem. Did you change the ownership/group
to mysql:mysql, to the mentioned files, after you restored them?

Regards 


---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +61 8 8408 4273
  _/  _/  _/_/_/  Mobile: +61 417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia 
invent   
---

-Original Message-
From: Patrick Aljord [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, 15 November 2006 9:40 AM
To: mysql@lists.mysql.com
Subject: can I recover a database from db files?

Hey all,

I host my app on a friend server who make backup every night, well
yesterday he installed another distro so I asked him for my db backup
and it turns out the only backup he did was the whole hard drive. So
he just sent me a tarball of my database directory containing:
ads_categories.MYD,ads_categories.MYI,ads.frm,ads.MYD,ads.MYI,categories
.frm,categories.MYD,categories.MYI,db.opt,regions.frm,regions.MYD,region
s.MYI.

I tried to create a database called mydb on my computer (debian etch)
and just copied all the files to the place /var/lib/mysql/mydb/

then when I try to do a select * from categories, I get:

ERROR 1017 (HY000): Can't find file: './mydb/categories.frm' (errno: 13)

is there any way to get my db back?

thanx in advance

Pat

-- 
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]

can I recover a database from db files?

[Patrick Aljord]

Hey all,

I host my app on a friend server who make backup every night, well
yesterday he installed another distro so I asked him for my db backup
and it turns out the only backup he did was the whole hard drive. So
he just sent me a tarball of my database directory containing:
ads_categories.MYD,ads_categories.MYI,ads.frm,ads.MYD,ads.MYI,categories.frm,categories.MYD,categories.MYI,db.opt,regions.frm,regions.MYD,regions.MYI.

I tried to create a database called mydb on my computer (debian etch)
and just copied all the files to the place /var/lib/mysql/mydb/

then when I try to do a select * from categories, I get:

ERROR 1017 (HY000): Can't find file: './mydb/categories.frm' (errno: 13)

is there any way to get my db back?

thanx in advance

Pat

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

Re: question on create_tmp_table_priv

[Ryan Stille]

Marten Lehmann wrote:
that is the sort of thing that I expected and was afraid of. Also, I 
think the mysql documentation doesn't really point out that temporary 
tables are always kept in memory. Couldn't mysql create them in 
/tmp/. instead?
Actually I was just reading through the docs about this the other day.  
It said temp tables are always created in memory, but can be moved to 
disk if they get too large, or will be created on disk initially if the 
table contains a certain type of field (maybe text or binary, can't 
remember).


-Ryan


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

Re: question on create_tmp_table_priv

[Marten Lehmann]

Hello,

> If a person forgets to drop temp tables and stays within a connection

for hours or even days, memory resources can be overloaded a lot of temp tables.
Furthermore, loading temp tables with a GB of data is bad practice but is 
possible
under such circumstances.


that is the sort of thing that I expected and was afraid of. Also, I 
think the mysql documentation doesn't really point out that temporary 
tables are always kept in memory. Couldn't mysql create them in 
/tmp/. instead?


Regards
Marten

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

Re: RE: Backing up large dbs with tar

[Dan Buettner]

Interesting question - I too noticed that in the comments.  For what
it's worth, I used it in production environment for more than 5 years
with no problems, from 2001 on.  I did restore a few things here and
there, so I know it was working!  ;)

I use mysqldump for backups now because we use InnoDB tables where I'm at now.

Dan

On 11/14/06, Tim Lucia <[EMAIL PROTECTED]> wrote:

Is mysqlhotcopy still considered "beta"?  We steered clear of it for
production use for that reason.

Tim

-Original Message-
From: Dan Buettner [mailto:[EMAIL PROTECTED]
Sent: Monday, November 13, 2006 12:39 PM
To: Van
Cc: mysql@lists.mysql.com
Subject: Re: Backing up large dbs with tar

Van, I'll second what Gerald said about mysqlhotcopy.

When we first began using MySQL at my last job, we had terrible
problems with MySQL crashing.  Turned out to be due to a 3rd party
backup process attempting to lock and read the database files while
MySQL was attempting to use them.

Using mysqlhotcopy to copy the files elsewhere, and excluding the data
directory from the backup software, gave us a stable solution.

mysqldump might also work well for you, as it can lock
tables/databases and give you a consistent snapshot.  Potentially
takes longer to restore from a mysqldump file though.

HTH,
Dan


On 11/13/06, Van <[EMAIL PROTECTED]> wrote:
> Greetings:
>
> I have a 600M data file that never gets backed up.  The following error
> occurs in the cron job:
>
> tar: /data/mysql/"my_db_name"/"my_large_table_name".MYI: file changed as
we read it
>
> Is there a way I can set this one table to read-only prior to the backup
> without affecting other db writes during this operation?
>
> Thanks,
> Van
>
> --
> 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 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: Backing up large dbs with tar

[Tim Lucia]

Is mysqlhotcopy still considered "beta"?  We steered clear of it for
production use for that reason.

Tim

-Original Message-
From: Dan Buettner [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 13, 2006 12:39 PM
To: Van
Cc: mysql@lists.mysql.com
Subject: Re: Backing up large dbs with tar

Van, I'll second what Gerald said about mysqlhotcopy.

When we first began using MySQL at my last job, we had terrible
problems with MySQL crashing.  Turned out to be due to a 3rd party
backup process attempting to lock and read the database files while
MySQL was attempting to use them.

Using mysqlhotcopy to copy the files elsewhere, and excluding the data
directory from the backup software, gave us a stable solution.

mysqldump might also work well for you, as it can lock
tables/databases and give you a consistent snapshot.  Potentially
takes longer to restore from a mysqldump file though.

HTH,
Dan


On 11/13/06, Van <[EMAIL PROTECTED]> wrote:
> Greetings:
>
> I have a 600M data file that never gets backed up.  The following error
> occurs in the cron job:
>
> tar: /data/mysql/"my_db_name"/"my_large_table_name".MYI: file changed as
we read it
>
> Is there a way I can set this one table to read-only prior to the backup
> without affecting other db writes during this operation?
>
> Thanks,
> Van
>
> --
> 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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Find foreign key for a table

[Tim Lucia]

I've use MS Visio's "Reverse Engineer" database feature to find the FK
relationships between tables.  I'd bet there are other tools which do
something similar, probably even free ones.

 

Tim

 

  _  

From: Peter Brawley [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 14, 2006 2:36 PM
To: Clyde Lewis
Cc: MySQL List
Subject: Re: Find foreign key for a table

 

Clyde,

>How can I find the foreign keys for a table?

At http://www.artfulsoftware.com/mysql-queries.php in the left pane click on
DDL then on 'Find parent tables'. HTH.

PB

-



Hello, 

How can I find the foreign keys for a table? I would like to be able 
to find, for any table, the columns that are foreign keys and what 
tables/columns the foreign keys are related to. Basically what I am 
trying to do is this. My developers want to allow logical deletes of 
data. I don't want them to be able to logically delete a record that 
has related records. 

Thanks in advance 

Re: Find foreign key for a table

[Peter Brawley]

Clyde,

>How can I find the foreign keys for a table?

At http://www.artfulsoftware.com/mysql-queries.php in the left pane
click on DDL then on 'Find parent tables'. HTH.

PB

-
Hello,
  
  
How can I find the foreign keys for a table? I would like to be able
  
to find, for any table, the columns that are foreign keys and what
  
tables/columns the foreign keys are related to. Basically what I am
  
trying to do is this. My developers want to allow logical deletes of
  
data. I don't want them to be able to logically delete a record that
  
has related records.
  
  
Thanks in advance
  
  
  



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.14.5/533 - Release Date: 11/13/2006


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

RE: Find foreign key for a table

[Waldemar Jankowski]

Alternatively you can use:

SHOW TABLE STATUS from name_db like '%part_of_table_name%';

to get a list of all fk constraints in the database that match
a certain string. It also gives some other interesting information.

As far as dependencies go you can see which table the constraint 
references in both of the suggested methods.  Not sure if that's

what you're after though.

Waldemar

On Tue, 14 Nov 2006, Clyde Lewis wrote:

Will the output from the show command also provide a list of other dependent 
tables? From what I've seen with the "show create table " command, 
it only provide keys associated with that table , but other 
dependent tables.


Thanks so much.

At 01:18 PM 11/14/2006, Howard Hart wrote:


show create table ?


-Original Message-
From: Clyde Lewis [mailto:[EMAIL PROTECTED]
Sent: Tue 11/14/2006 10:16 AM
To: MySQL List
Subject: Find foreign key for a table

Hello,

How can I find the foreign keys for a table? I would like to be able
to find, for any table, the columns that are foreign keys and what
tables/columns the foreign keys are related to. Basically what I am
trying to do is this. My developers want to allow logical deletes of
data. I don't want them to be able to logically delete a record that
has related records.

Thanks in advance


--
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: Find foreign key for a table

[Clyde Lewis]

Will the output from the show command also provide a list of other 
dependent tables? From what I've seen with the "show create table 
" command, it only provide keys associated with that table 
, but other dependent tables.


Thanks so much.

At 01:18 PM 11/14/2006, Howard Hart wrote:


show create table ?


-Original Message-
From: Clyde Lewis [mailto:[EMAIL PROTECTED]
Sent: Tue 11/14/2006 10:16 AM
To: MySQL List
Subject: Find foreign key for a table

Hello,

How can I find the foreign keys for a table? I would like to be able
to find, for any table, the columns that are foreign keys and what
tables/columns the foreign keys are related to. Basically what I am
trying to do is this. My developers want to allow logical deletes of
data. I don't want them to be able to logically delete a record that
has related records.

Thanks in advance


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

Find foreign key for a table

[Clyde Lewis]

Hello,

How can I find the foreign keys for a table? I would like to be able
to find, for any table, the columns that are foreign keys and what
tables/columns the foreign keys are related to. Basically what I am
trying to do is this. My developers want to allow logical deletes of
data. I don't want them to be able to logically delete a record that
has related records.

Thanks in advance


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

RE: Max date in recordset

[Jerry Schwartz]

>From what I know, your solution would only work if you have exactly three
records to find (there might be 1 or 100), only if the latest records fall
within the last three days. I think Vittorio said he didn't know ahead of
time what most recent date would be.

Regards,

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

860.674.8796 / FAX: 860.674.8341


> -Original Message-
> From: João Cândido de Souza Neto [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 14, 2006 12:00 PM
> To: mysql@lists.mysql.com
> Subject: Re: Max date in recordset
>
> select * from table where InsertDate = date_sub(now,interval
> 1 day) limit 3;
>
> Not tested, but i think it will work fine.
>
> ""Vittorio Zuccalà"" <[EMAIL PROTECTED]>
> escreveu na
> mensagem news:[EMAIL PROTECTED]
> > Hello,
> > i've a table with a lot of field and in particular:
> > "InsertDate","Box","Prt"
> > Example:
> > InsertDate, Box, PRT
> > 2006-11-01, BXT, 34
> > 2006-11-01, TTS, 33
> > 2006-11-01, RRT, 55
> > 2006-11-02, BXT, 22
> > 2006-11-02, TTS, 99
> > 2006-11-02, SAR, 75
> >
> >
> > I'd like to find all record inserted in the last day...
> > In this example the last three records...
> > I do not know which is the last day before
> >
> > Any suggestions?
> >
>
>
>
> --
> 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: Max date in recordset

[Jerry Schwartz]

Assuming that insertdate is a DATE column,

SELECT * FROM t
 WHERE t.insertdate = (SELECT MAX(t.insertdate) FROM t));

would do it.

Regards,

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

860.674.8796 / FAX: 860.674.8341


> -Original Message-
> From: Vittorio Zuccalà [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 14, 2006 11:44 AM
> To: mysql@lists.mysql.com
> Subject: Max date in recordset
>
> Hello,
> i've a table with a lot of field and in particular:
> "InsertDate","Box","Prt"
> Example:
> InsertDate, Box, PRT
> 2006-11-01, BXT, 34
> 2006-11-01, TTS, 33
> 2006-11-01, RRT, 55
> 2006-11-02, BXT, 22
> 2006-11-02, TTS, 99
> 2006-11-02, SAR, 75
>
>
> I'd like to find all record inserted in the last day...
> In this example the last three records...
> I do not know which is the last day before
>
> Any suggestions?
>
>




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

Re: Max date in recordset

[Dan Buettner]

If you're looking for the records from the last full day contained in
the data, not the past 24 hours according to the clock, then this
ought to work:

select * from table
where InsertDate >= date_sub( (select max(InsertDate from table),interval 1 day)
order by InserDate desc

Dan


On 11/14/06, Vittorio Zuccalà <[EMAIL PROTECTED]> wrote:

Hello,
i've a table with a lot of field and in particular: "InsertDate","Box","Prt"
Example:
InsertDate, Box, PRT
2006-11-01, BXT, 34
2006-11-01, TTS, 33
2006-11-01, RRT, 55
2006-11-02, BXT, 22
2006-11-02, TTS, 99
2006-11-02, SAR, 75


I'd like to find all record inserted in the last day...
In this example the last three records...
I do not know which is the last day before

Any suggestions?




--
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: Max date in recordset

[Jo�o C�ndido de Souza Neto]

select * from table where InsertDate = date_sub(now,interval 1 day) limit 3;

Not tested, but i think it will work fine.

""Vittorio Zuccalà"" <[EMAIL PROTECTED]> escreveu na 
mensagem news:[EMAIL PROTECTED]
> Hello,
> i've a table with a lot of field and in particular: 
> "InsertDate","Box","Prt"
> Example:
> InsertDate, Box, PRT
> 2006-11-01, BXT, 34
> 2006-11-01, TTS, 33
> 2006-11-01, RRT, 55
> 2006-11-02, BXT, 22
> 2006-11-02, TTS, 99
> 2006-11-02, SAR, 75
>
>
> I'd like to find all record inserted in the last day...
> In this example the last three records...
> I do not know which is the last day before
>
> Any suggestions?
> 



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

Re: access full-text index

[Philip Mather]

Leandro Guimarães Faria Corcete DUTRA wrote:

On Wed, 08 Nov 2006 18:51:20 -0800, Rares Vernica wrote:



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

What do you mean exactly?  SQL is not intended for physical structures.



I started writing a little PHP shell script to import the data from the
myisam_ftdump into a table, but unfortunately this a side project at
work and I have to effectively steal time to work on it so it's not
going very far at the moment. The table structure I was using was...

CREATE TABLE `SEARCH_STATS` (
`ID` int(11) NOT NULL auto_increment,
`Parent_Table` varchar(255) NOT NULL,
`Name` varchar(255) NOT NULL,
`Occurances` int(11) NOT NULL default '0',
`Search_Count` int(11) NOT NULL default '0',
`Global_Weight` float(10,7) NOT NULL default '0.000',
`Manual_Weight` float(10,7) NOT NULL default '1.000',
`Date_deleted` timestamp NOT NULL default '-00-00 00:00:00',
`Date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
`Date_created` timestamp NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`ID`),
UNIQUE KEY `Name` (`Name`),
KEY `Occurances` (`Occurances`),
KEY `Search_Count` (`Search_Count`),
KEY `Global_Weight` (`Global_Weight`),
KEY `Manual_Weight` (`Manual_Weight`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

...and a rough bit of psuedo-code for you...

shell( myisam_ftdump /home/databases/mysql/{database}/{tables}4 -c > 
global_weights_and_counts.index );
shell( myisam_ftdump /home/databases/mysql/{database}/{tables}4 -d > 
local_weights.index );

$databaseWrapperObject_ = new DatabaseWrapper($includePath);
$handle = @fopen("global_weights_and_counts.index", "r");

if ($handle)
{
 while (!feof($handle))
 {
 $buffer = fgets($handle);
 $data = explode(" ",$buffer);
 $sql = 'INSERT INTO SEARCH_STATS (Parent_Table, Name, Occurances, Global_Weight, 
Date_created) VALUES ("WD_NAMES", "'.trim($data[20]).'", '.$data[8].', 
'.$data[19].', NOW())';

 mysql_query($sql);
 }

 fclose($handle);
}

$handle = @fopen("local_weights.index", "r");
if ($handle)
{
while (!feof($handle))
{
$buffer = fgets($handle);
$data = explode(" ",$buffer);
//8, 19,20
//  $sql = 'INSERT INTO SEARCH_STATS (Parent_Table, Name, 
Occurances, Global_Weight, Date_created) VALUES ("WD_NAME
//  mysql_query($sql);
}
fclose($handle);
}


...that last loop needs sorting.

Regards,
Phil


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

Limiting the number of fulltext indexes

[Seth Fischer]

I have a table (example 1):

tblRegulations
fldCountryID (int)
fldRegualtionType (int)
fldRegulationUpdated (date)
fldRegulation (text)

Each country can have up to 7 different regulation types. I have
designed the table like this so I can have one fulltext index on
fldRegulation, and thus search all the regulations for all countries
in one hit.

If I designed the table like this (example 2):

tblRegulations
fldCountryID (int)
fldRegualtion_1 (text)
fldRegulation_1_Updated (date)
fldRegualtion_2 (text)
fldRegulation_2_Updated (date)
fldRegualtion_3 (text)
fldRegulation_3_Updated (date)
…
fldRegualtion_7 (text)
fldRegulation_7_Updated (date)

I would need 7 full text indexes, one for each of fldRegulation_X. And
if I wanted to search any combination of regulation types I would need
2^7 full text indexes! So I have decided my table design will be the
first example.

Now for my problem:

I want the return a result from the first example table like this:

Results
fldCountryID (int)
fldRegulation_1_Updated (date)
fldRegulation_2_Updated (date)
fldRegulation_3_Updated (date)
fldRegulation_4_Updated (date)
fldRegulation_5_Updated (date)
fldRegulation_6_Updated (date)
fldRegulation_7_Updated (date)

How would I write such a query? Am I trying to do the impossible? I
want only one fulltext index but it seems like I can only get the
summary results if I design my table as in example 2.

I will greatly appreciate any advice.

Best regards,
Seth

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

question on create_tmp_table_priv

[Marten Lehmann]

Hello,

if there are not any security impacts, why does the 
Create_tmp_table-privilege exist separately to the 
create_table-privilege? Or if it has security impacts (maybe 
automatically granting other rights), which ones?


The mysql documentation doesn't tell much about this.

Regards
Marten

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

Re: access full-text index

[Rolando Edwards]

As I mentioned before, you could use 'myisam_ftdump' to see the contents
and word distribution of a MyISAM table's fulltext indexes. If you are
very bold with source code, download the MySQL Source Code and lookup
how fulltext indexes are populated.

You should be able to find that section of the code if you just look for
the built-in stop word list, where it is used, and how words are inserted
into the fulltext indexes once a word gets by the stop word list.

Have Fun With It !!!

- Original Message -
From: Rares Vernica <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Sent: Tuesday, November 14, 2006 1:09:31 AM GMT-0500 US/Eastern
Subject: Re: access full-text index

I think the full-text index is an inverted index structure. So, it has 
all the words from the fields it indexes. For each word it has a list of 
record ID which have that word.

What I am interested to get is this inverted index structure. I imagine 
it can be represented as 1-2 table(s). Can I do that? Is the structure 
accessible as a table? (Of course, I don't want to change it.)

Thanks a lot,
Ray

Leandro Guimarães Faria Corcete DUTRA wrote:
> On Wed, 08 Nov 2006 18:51:20 -0800, Rares Vernica wrote:
> 
>> Is it possible to access the Full-Text Index structures from SQL?
> 
>   What do you mean exactly?  SQL is not intended for physical structures.
> 


-- 
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: question on create_tmp_table_priv

[Rolando Edwards]

Maybe it is because you can manually create temporary tables in Stored 
Procedures.

Creating tables using 'create_priv' allows other users to view your table if
the other users have 'select_priv' for that same table.

Creating tables using 'create_tmp_table_priv' allows only the calling user
to view that table within the current MySQL session. You can drop temp tables
you make within a connection. Once a connection terminates, all temp tables
are dropped. If a person forgets to drop temp tables and stays within a 
connection
for hours or even days, memory resources can be overloaded a lot of temp tables.
Furthermore, loading temp tables with a GB of data is bad practice but is 
possible
under such circumstances.

Would you want every user who has 'Create_routine_priv' and 'Alter_Routine_Priv'
to go willy-nilly on temp tables? Other than root, DBAs and other superusers 
who know
what they are doing, that invites memory problems on the part of Stored 
Procedure developers.

If a Server has been hacked using a mysql user that is not root, you want to be 
sure
the mysql user being used in a hack does not create denial of service problems 
by
ballooning temp tables here and there. Why worry about this scenario ???

Remember :  TEMPORARY TABLES ARE NOT VISIBLE 

EXAMPLE
---
create temporary table test.namelist ( id int not null auto_increment, name 
varchar(100), primary key (id) );
insert into test.namelist (name) values ('JOHN'),('GROVER'),('JEFF'),('MIKE');

THEY DO NOT APPEAR when you run the following three ways:
1)   use test
 show tables;
2)   select table_name from information_schema.tables where table_schema='test';
3)   select table_name from information_schema.tables where 
table_name='namelist';

Yet, you can select from, delete from, insert into,
and update temporary tables because they do exist.

mysql> select * from test.namelist;
+++
| id | name   |
+++
|  1 | JOHN   |
|  2 | GROVER |
|  3 | JEFF   |
|  4 | MIKE   |
+++
4 rows in set (0.00 sec)

Imagine the havoc a person could wreak maliciously or accidently with temporary 
tables.
Unless you know that exact name of the table, you cannot drop it. What makes 
this worse
is that you have to be within the connection that created the temporary table 
in the first
place to even manipulate it or delete it.

Of course, killing a connection drops all temporary tables
created in the session of the MySQL connection. Shutting
down MySQL drops all temporary tables from the server.

- Original Message -
From: Marten Lehmann <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Sent: Tuesday, November 14, 2006 10:49:57 AM GMT-0500 US/Eastern
Subject: question on create_tmp_table_priv

Hello,

if there are not any security impacts, why does the 
Create_tmp_table-privilege exist separately to the 
create_table-privilege? Or if it has security impacts (maybe 
automatically granting other rights), which ones?

The mysql documentation doesn't tell much about this.

Regards
Marten

-- 
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]

Max date in recordset

[Vittorio Zuccalà]

Hello,
i've a table with a lot of field and in particular: "InsertDate","Box","Prt"
Example:
InsertDate, Box, PRT
2006-11-01, BXT, 34
2006-11-01, TTS, 33
2006-11-01, RRT, 55
2006-11-02, BXT, 22
2006-11-02, TTS, 99
2006-11-02, SAR, 75


I'd like to find all record inserted in the last day...
In this example the last three records...
I do not know which is the last day before

Any suggestions?


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