Grant Tables problem (I think)

[mysql]

 Hi there, 

I have a problem connecting to the mysql server. I installed a new server with 
mysql 4.1.9, apache2, php4 on freebsd5.3 and have some websites running on it 
using the mysql server. Last friday I had a crash of one off my other servers 
and I copied the websites and db's to this new server. (old server was mysql 
3). After that the mysql server didn't startup anymore. I then chown'ed the 
data dir to mysql:mysql. After that I was able to start the server with 
--skip-grant-tables . But it didn't start with out the --skip-grant-tables 
option. So I found out to use mysql_install_db to create new grant tables. 
After that the server starts with and without --skip-grant-tables. But only 
with the --skip-grant-tables option, websites can connect to the databases. I 
can't find any real errors in the logs and I can't find anything on the web or 
forum. I run mysql_fix_privilege_tables and installed a root passwd. 
Hopefully somebody can help me on this. 

Roger 

Managing virtual e-mails

[Fafa Diliha Romanova]

Hello.

I am running the Postfix+Courier on MySQL setup found on:

http://www.high5.net/howto

I am wondering how to add and delete users, as well as adding
and deleting aliases -- and managing my virtual e-mail database
in general -- using a pure, clean and efficient approach,
rather than having to use Postfixadmin, which I find to be sort
of unprofessional and bloated with bad design.

For instance, I managed to avoid using the PHPMyAdmin, and
instead got all my setup recorded in clean text:

USE mysql;
CREATE DATABASE gtg_mail;
GRANT USAGE ON gtg_mail.* TO [EMAIL PROTECTED] IDENTIFIED BY 'gatNanav';
GRANT CREATE, SELECT, INSERT, DELETE, UPDATE ON gtg_mail.* TO [EMAIL PROTECTED];
FLUSH PRIVILEGES;

USE gtg_mail;
CREATE TABLE admin (
  username varchar(255) NOT NULL default '',
  password varchar(255) NOT NULL default '',
  created datetime NOT NULL default '-00-00 00:00:00',
  modified datetime NOT NULL default '-00-00 00:00:00',
  active tinyint(1) NOT NULL default '1',
  PRIMARY KEY (username),
  KEY username (username)
) TYPE=MyISAM COMMENT='Postfix: virtual admins';

USE gtg_mail;
CREATE TABLE alias (
  address varchar(255) NOT NULL default '',
  goto text NOT NULL,
  domain varchar(255) NOT NULL default '',
  created datetime NOT NULL default '-00-00 00:00:00',
  modified datetime NOT NULL default '-00-00 00:00:00',
  active tinyint(1) NOT NULL default '1',
  PRIMARY KEY (address),
  KEY address (address)
) TYPE=MyISAM COMMENT='Postfix: virtual aliases';

USE gtg_mail;
CREATE TABLE domain (
  domain varchar(255) NOT NULL default '',
  description varchar(255) NOT NULL default '',
  aliases int(10) NOT NULL default '0',
  mailboxes int(10) NOT NULL default '0',
  maxquota int(10) NOT NULL default '0',
  transport varchar(255) default NULL,
  backupmx tinyint(1) NOT NULL default '0',
  created datetime NOT NULL default '-00-00 00:00:00',
  modified datetime NOT NULL default '-00-00 00:00:00',
  active tinyint(1) NOT NULL default '1',
  PRIMARY KEY (domain),
  KEY domain (domain)
) TYPE=MyISAM COMMENT='Postfix: virtual domains';

USE gtg_mail;
CREATE TABLE domain_admins (
  username varchar(255) NOT NULL default '',
  domain varchar(255) NOT NULL default '',
  created datetime NOT NULL default '-00-00 00:00:00',
  active tinyint(1) NOT NULL default '1',
  KEY username (username)
) TYPE=MyISAM COMMENT='Postfix: virtual domain admins';

USE gtg_mail;
CREATE TABLE mailbox (
  username varchar(255) NOT NULL default '',
  password varchar(255) NOT NULL default '',
  name varchar(255) NOT NULL default '',
  maildir varchar(255) NOT NULL default '',
  quota int(10) NOT NULL default '0',
  domain varchar(255) NOT NULL default '',
  created datetime NOT NULL default '-00-00 00:00:00',
  modified datetime NOT NULL default '-00-00 00:00:00',
  active tinyint(1) NOT NULL default '1',
  PRIMARY KEY (username),
  KEY username (username)
) TYPE=MyISAM COMMENT='Postfix: virtual mailboxes';

USE gtg_mail;
CREATE TABLE vacation (
  email varchar(255) NOT NULL default '',
  subject varchar(255) NOT NULL default '',
  body text NOT NULL,
  cache text NOT NULL,
  domain varchar(255) NOT NULL default '',
  created datetime NOT NULL default '-00-00 00:00:00',
  active tinyint(1) NOT NULL default '1',
  PRIMARY KEY (email),
  KEY email (email)
) TYPE=MyISAM COMMENT='Postfix: virtual vacation';

USE gtg_mail;
CREATE TABLE log (
  timestamp datetime NOT NULL default '-00-00 00:00:00',
  username varchar(255) NOT NULL default '',
  domain varchar(255) NOT NULL default '',
  action varchar(255) NOT NULL default '',
  data varchar(255) NOT NULL default '',
  KEY timestamp (timestamp)
) TYPE=MyISAM COMMENT='Postfix: log';

-- 
___
Sign-up for Ads Free at Mail.com
http://promo.mail.com/adsfreejump.htm


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

Setting up an organization's member database

[Fafa Diliha Romanova]

Hey!

I am running this non-profit organization working together with
various charity organizations across the world.

We would like to setup an efficient member database using MySQL
rather than having some ugly formatted MS Word document listing
them.

What would be the purest and cleanest way of setting one up,
and maintaining it? It should support all personal biodata fields,
arranged under individual, organizational, corporate and honorary
memberships.

I hope anybody has been in the same situation as me.

Thanks a lot!
   Fafa
   Red Reachout

-- 
___
Sign-up for Ads Free at Mail.com
http://promo.mail.com/adsfreejump.htm


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

Select previous group

[Carl]

Seems like there should be a simple solution to my 
problem but I have been unable to find it.

Suppose you have a phone book of names, addresses, 
etc. You are looking at page 100 and want to now see page 99. (The 
person data is, of course, dynamic so a specific name is not tagged to a 
specific page.) How can I select the names for page 99 (in this case) 
knowing only the first name on page 100 and the number of names I want to 
display?

Thanks,

Carl
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 3/18/2005

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

Re: Newbie: mysql syntax error question

[Michael Stassen]

Graham Anderson wrote:
My server has mysql:  version 3.23.58
// Performing SQL query
SELECT cities.city, regions.region, countries.country
 FROM cities
 JOIN subnets on subnets.cityid=subnets.cityid
  ^^^
Shouldn't that be
  JOIN subnets on subnets.cityid=cities.cityid?
 JOIN regions on subnets.regionid=regions.regionid
 JOIN countries on subnets.countryid=countries.countryid
 where subnets.subnetaddress='24.24.172'
this gives a syntax error:
#1064 - You have an error in your SQL syntax near 'on 
subnets.cityid=subnets.cityid
 JOIN regions on subnets.regionid=regions.regi' at line

all the tables seem proper named and populated.
what could this be ?
g
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Setting up an organization's member database

[David Lloyd]

Fafa,

 I am running this non-profit organization working together with
 various charity organizations across the world.
 
 We would like to setup an efficient member database using MySQL
 rather than having some ugly formatted MS Word document listing
 them.
 
 What would be the purest and cleanest way of setting one up,
 and maintaining it? It should support all personal biodata fields,
 arranged under individual, organizational, corporate and honorary
 memberships.
 
 I hope anybody has been in the same situation as me.

Look for a project at http://freshmeat.net/ - there's a few of them
around (go searching - eg. member database or some such thing).

DSL

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

Re: Grant Tables problem (I think)

[David Lloyd]

Hi,

 I have a problem connecting to the mysql server. I installed a new
 server with mysql 4.1.9, apache2, php4 on freebsd5.3 and have some
 websites running on it using the mysql server. Last friday I had a
 crash of one off my other servers and I copied the websites and db's
 to this new server. (old server was mysql 3). After that the mysql
 server didn't startup anymore. I then chown'ed the data dir to
 mysql:mysql. After that I was able to start the server with
 --skip-grant-tables . But it didn't start with out the
 --skip-grant-tables option. So I found out to use mysql_install_db
 to create new grant tables. After that the server starts with and
 without --skip-grant-tables. But only with the --skip-grant-tables
 option, websites can connect to the databases. I can't find any real
 errors in the logs and I can't find anything on the web or forum. I
 run mysql_fix_privilege_tables and installed a root passwd.  Hopefully
 somebody can help me on this. 

I wonder if putting:

[mysql]
old-passwords

...in your my.cnf (I always use /etc/my.cnf because trying to guess
where the 'real' data directory is, is painful).

http://dev.mysql.com/doc/mysql/en/password-hashing.html

DSL

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

update command

[Eko Budiharto]

Hi,
I am having problem with update command. I run it directy in the mysql console, 
it works. But when I run it from perl, it does not work.
 
here is the perl script

 my $server2 = 'localhost';
 my $db2 = 'database';
 my $username2 = 'username';
 my $password2 = 'password';

 my $dbh2 = DBI-connect(dbi:mysql:$db2:$server2, $username2, $password2);

 my $query = UPDATE flightInfo SET route='chicago, atlanta' WHERE 
passengerIndex='15' AND passengerName='Eko Budiharto';; 
 
 $dbh2-disconnect;
I do not why the UPDATE command does not work with the script. Please help. I 
thought I used wrong syntax.
 


-
Do you Yahoo!?
 Yahoo! Small Business - Try our new resources site! 

Re: Select previous group

[Rhino]

  - Original Message - 
  From: 
  Carl 
  To: mysql@lists.mysql.com 
  Sent: Sunday, March 20, 2005 8:38 
AM
  Subject: Select previous group
  
  Seems like there should be a simple solution to 
  my problem but I have been unable to find it.
  
  Suppose you have a phone book of names, 
  addresses, etc. You are looking at page 100 and want to now see page 
  99. (The person data is, of course, dynamic so a specific name is not 
  tagged to a specific page.) How can I select the names for page 99 (in 
  this case) knowing only the first name on page 100 and the number of names I 
  want to display?
  
  Thanks,
  
  Carl
  
The answer is: it depends. Are you trying to 
accomplish this in SQL from a command line or batch script or are you doing this 
in aprogram? If you are using a programming language, which one are you 
using and what version of the language are you using? Also, what version of 
MySQL are you using? Different versions have different capabilities. 


In brief, it is easier to do what you are 
describing - scrolling backwards through a result set - in a programming 
language like Java than it is in straight SQL; I'm not even sure if it is 
*possible* in straight SQL but greater minds than mine may have figured out a 
way. Even within programming languages, it can be tedious to manage or 
relatively straightforward; for example, using (Java) JDBC 1.0, it is fairly 
tedious to scroll backwards through a result but later versions of JDBC make it 
relatively easy.

Rhino
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 15/03/2005

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

Re: update command

[Rhino]

- Original Message - 
From: Eko Budiharto [EMAIL PROTECTED]
To: ActivePerl@listserv.ActiveState.com; mysql@lists.mysql.com;
[EMAIL PROTECTED]
Sent: Sunday, March 20, 2005 10:04 AM
Subject: update command


 Hi,
 I am having problem with update command. I run it directy in the mysql
console, it works. But when I run it from perl, it does not work.

 here is the perl script

  my $server2 = 'localhost';
  my $db2 = 'database';
  my $username2 = 'username';
  my $password2 = 'password';

  my $dbh2 = DBI-connect(dbi:mysql:$db2:$server2, $username2,
$password2);

  my $query = UPDATE flightInfo SET route='chicago, atlanta' WHERE
passengerIndex='15' AND passengerName='Eko Budiharto';;

  $dbh2-disconnect;
 I do not why the UPDATE command does not work with the script. Please
help. I thought I used wrong syntax.


Define doesn't work.

Do you mean that you get a compile error or a runtime error? In either case
what message are you getting?

Or do you mean that the statement works but doesn't give the expected
result? For instance, perhaps it gives no error message but the update
doesn't appear to have taken place when you look at the data later?

Remember, we only know what you tell us and you haven't told us enough.

Rhino



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 15/03/2005


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

Re: Select previous group

[Peter Brawley]

Carl,

Seems like there should be a
simple solution to my problem but I have been unable to find it.

Suppose you have a phone book of
names, addresses, etc. You are looking at page 
100 and want to now see page 99. (The person data is, of course,
dynamic so a specific 
name is not tagged to a specific page.) How can I select the names
for page 99 (in this 
case) knowing only the first name on page 100 and the number of
names I want to display?

Assuming...
 1-based page numbers, 
 you are on page P, and 
 each page shows N rows, 
then a SELECT statement that reproduces your page, assuming no rows
have changed, ends with 
 LIMIT ((P-1)*N, N), 
so you could retrieve page 99 with a SELECT statement which ends with
 LIMIT (98*N,N).

Peter Brawley
http://www.artfulsoftware.com




No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 3/18/2005

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

Copy users (was: Grant Tables problem )

[User Roger_ber]

David Lloyd wrote ..
 
 Hi,
 
  I have a problem connecting to the mysql server. I installed a new
  server with mysql 4.1.9, apache2, php4 on freebsd5.3 and have some
  websites running on it using the mysql server. Last friday I had a
  crash of one off my other servers and I copied the websites and db's
  to this new server. (old server was mysql 3). After that the mysql
  server didn't startup anymore. I then chown'ed the data dir to
  mysql:mysql. After that I was able to start the server with
  --skip-grant-tables . But it didn't start with out the
  --skip-grant-tables option. So I found out to use mysql_install_db
  to create new grant tables. After that the server starts with and
  without --skip-grant-tables. But only with the --skip-grant-tables
  option, websites can connect to the databases. I can't find any real
  errors in the logs and I can't find anything on the web or forum. I
  run mysql_fix_privilege_tables and installed a root passwd.  Hopefully
  somebody can help me on this. 
 
 I wonder if putting:
 
 [mysql]
 old-passwords
 
 ...in your my.cnf (I always use /etc/my.cnf because trying to guess
 where the 'real' data directory is, is painful).
 
 http://dev.mysql.com/doc/mysql/en/password-hashing.html
 
 DSL
Well the above article did me search in another way (thanks for that). I found 
out the users are not copied with the db's. I copied about 20 db's 2 weeks ago 
from one server to another and everything went just fine. Probably the problem 
is now that the old server had the db's in /usr/home/mysql/data and on the new 
server in /var/db/mysql (default). How do I copy those users?

Roger

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

RE: update command [problem solved]

[Eko Budiharto]

Hi all,
I just found the error. The error was I did not include the execute statement 
in my perl script.
Thank you very much for whom reply my email. Thank you very much. I really 
appreciate it.

Eko Budiharto [EMAIL PROTECTED] wrote:
Date: Sun, 20 Mar 2005 07:04:27 -0800 (PST)
From: Eko Budiharto [EMAIL PROTECTED]
Subject: update command
To: ActivePerl@listserv.ActiveState.com, mysql@lists.mysql.com,
[EMAIL PROTECTED]

Hi,
I am having problem with update command. I run it directy in the mysql console, 
it works. But when I run it from perl, it does not work.

here is the perl script

my $server2 = 'localhost';
my $db2 = '';
my $username2 = '';
my $password2 = '

my $dbh2 = DBI-connect(dbi:mysql:$db2:$server2, $username2, $password2);

my $query = UPDATE flightInfo SET route='chicago, atlanta' WHERE 
passengerIndex='15' AND passengerName='Eko Budiharto';; 

$dbh2-disconnect;
I do not why the UPDATE command does not work with the script. Please help. I 
thought I used wrong syntax.



-
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site! 

-
Do you Yahoo!?
 Yahoo! Small Business - Try our new resources site! 

Re: update command

[Rhino]

I'm returning this discussionto the 
list where it belongs

Are you sure that the query you do on the 
command line is the same as the one you do in the program? Obviously, they can't 
be precisely identical because the program needs additional punctuation to work 
but is it functionally the same? 

For example, does the command line version 
of the query have:

 passengerIndex = 
'15'

or 

 passengerIndex = 
15

If the passengerIndex column is defined to 
contain character data, the apostrophes are appropriate for both the command 
line and Perl versions of the statement; otherwise, the apostrophes should be 
omitted in both versions. 

Also, have you typed the literal value 
'Edo Budiharto' exactly the same in both queries? If the data contains a single 
space between the first name and the last name and thecommand line version 
of the query also contains one space but the Perl version contains two spaces, 
this could explain the different behaviour of the queries. 

Did you commit the update before 
inspecting it? If I recall correctly, Perl, like other languages,requires 
that you commit updates before they are visible to users. Perhaps you failed to 
commit and that is why you aren't seeing any apparent change in your data. Try 
making sure that you did a commit - and that it worked! - before looking to see 
that the update worked. My Perl is VERY rusty but if it works like other 
languages, you will have an option to either "autocommit", i.e. commit after 
every statement, or you will be able to commit manually; if you say 
"autocommit=no" and fail to commit manually, your update won't be visible until 
a commit happens. (Typically, if the program ends normally, a commit will be 
issued "under the covers" but if you are inspecting the value from the command 
line before the program has ended, the commit may not have happened yet which 
would explain why the update doesn't appear to have taken place.)

Rhino


  - Original Message - 
  From: 
  Eko 
  Budiharto 
  To: Rhino 
  Sent: Sunday, March 20, 2005 10:57 
  AM
  Subject: Re: update command
  
  sorry Rhino,
  what I mean with "does not work" is it does not update the value in the 
  mysql, but it does not give any error message either.
  Rhino [EMAIL PROTECTED] 
  wrote:
  - Original Message - From: "Eko Budiharto" 
<[EMAIL PROTECTED]>To: ; 
;<[EMAIL PROTECTED]>Sent: Sunday, March 
20, 2005 10:04 AMSubject: update command Hi, I 
am having problem with update command. I run it directy in the 
mysqlconsole, it works. But when I run it from perl, it does not 
work. here is the perl script my $server2 = 
'localhost'; my $db2 = ''; my $username2 = 
''; my $password2 = '

Problem with fedora

[iñaki]

I update mysql to 4 in my fedora, but when i put mysqld says me that 
other service is in this port, before when i put ps shows me a myql_safe 
but whe i updated no shows
Why don´t star mysql?

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

Re: update command

[David Dick]

Presumably you remembered to include
Eko Budiharto wrote:
Hi,
I am having problem with update command. I run it directy in the mysql console, it works. But when I run it from perl, it does not work.
 
here is the perl script

 my $server2 = 'localhost';
 my $db2 = 'database';
 my $username2 = 'username';
 my $password2 = 'password';
 my $dbh2 = DBI-connect(dbi:mysql:$db2:$server2, $username2, $password2);
 my $query = UPDATE flightInfo SET route='chicago, atlanta' WHERE passengerIndex='15' AND passengerName='Eko Budiharto';; 
my ($sth) = $dbh2-prepare($query);
$sth-execute();
$sth-finish();
 $dbh2-disconnect;
I do not why the UPDATE command does not work with the script. Please help. I thought I used wrong syntax.
 
yes?
uru
-Dave
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problem with fedora

[Jeff Steinkamp]

I tried to answer this directory to your email address but the address bounced;
1. disable selinux
2. change the ownership rights to /var/lib/mysql to mysql:mysql
3. run mysql_install_db
4. add your users to the mysql group
5. restart the machine
If mysqld is still not running, check the error file in /var/lib/mysql for
messages.  Look at the last entries in the file.
That is what got mysql version 4 running on my FC3 box.
Jeff Steinkamp - N7YG
Tucson, AZ
SCUD Missile Coordinates
N32-13-52 W110-52-15
http://home.earthlink.net/~jksteinkamp
http://n7yg.webhop.org
___
A man who is always declaring he's no fool usually has his suspicions.
- Original Message - 
From: iñaki [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, March 20, 2005 11:50
Subject: Problem with fedora


I update mysql to 4 in my fedora, but when i put mysqld says me that other 
service is in this port, before when i put ps shows me a myql_safe but whe i 
updated no shows
Why don´t star mysql?

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

DateTime Select optimised

[Pete Moran]

Hi All,

 

Is there a simpler way of doing a select for a given date, for instance if I
have a datetime field called date

 

And so its populated with a load of values such as 

 

2005-01-07 09:00

2005-01-07 10:00

2005-01-07 11:00

2005-01-07 12:00

 

If I wanted all records which fall on 2005-01-07 I could of course do 

 

Select * from TABLE where date  2005-01-07 and date 
DATE_ADD(2005-01-07, INTERVAL 24 HOUR)

 

However is there a simpler way of doing it by just passing one date like

 

Select * from TABLE where date = 2005-01-07

 

?

 


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 

RE: DateTime Select optimised

[Logan, David (SST - Adelaide)]

 
SELECT * FROM table WHERE date LIKE '2005-01-07%';

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Pete Moran [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 10:46 AM
To: mysql@lists.mysql.com
Subject: DateTime Select optimised

Hi All,

 

Is there a simpler way of doing a select for a given date, for instance
if I
have a datetime field called date

 

And so its populated with a load of values such as 

 

2005-01-07 09:00

2005-01-07 10:00

2005-01-07 11:00

2005-01-07 12:00

 

If I wanted all records which fall on 2005-01-07 I could of course do 

 

Select * from TABLE where date  '2005-01-07' and date 
DATE_ADD('2005-01-07', INTERVAL 24 HOUR)

 

However is there a simpler way of doing it by just passing one date like

 

Select * from TABLE where date = '2005-01-07'

 

?

 


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 

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

RE: DateTime Select optimised

[Pete Moran]

The table is indexed on the date field, doing a 'like' results in a table
scan, is there another way similar principal but would allow the indexes to
be used ?

-Original Message-
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:24 AM
To: Pete Moran; mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

 
SELECT * FROM table WHERE date LIKE '2005-01-07%';

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Pete Moran [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 10:46 AM
To: mysql@lists.mysql.com
Subject: DateTime Select optimised

Hi All,

 

Is there a simpler way of doing a select for a given date, for instance
if I
have a datetime field called date

 

And so its populated with a load of values such as 

 

2005-01-07 09:00

2005-01-07 10:00

2005-01-07 11:00

2005-01-07 12:00

 

If I wanted all records which fall on 2005-01-07 I could of course do 

 

Select * from TABLE where date  '2005-01-07' and date 
DATE_ADD('2005-01-07', INTERVAL 24 HOUR)

 

However is there a simpler way of doing it by just passing one date like

 

Select * from TABLE where date = '2005-01-07'

 

?

 


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 

-- 
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 Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 


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

RE: DateTime Select optimised

[Logan, David (SST - Adelaide)]

I would investigate a partial index perhaps on the date only? You could
index on just the date eg.

ALTER TABLE thing ADD INDEX (date(10));

I don't have to time to check it out now and I'm not sure it will
automatically use it but an EXPLAIN on the statement would point you in
the right direction.

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Pete Moran [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:07 AM
To: Logan, David (SST - Adelaide); mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

The table is indexed on the date field, doing a 'like' results in a
table
scan, is there another way similar principal but would allow the indexes
to
be used ?

-Original Message-
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:24 AM
To: Pete Moran; mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

 
SELECT * FROM table WHERE date LIKE '2005-01-07%';

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Pete Moran [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 10:46 AM
To: mysql@lists.mysql.com
Subject: DateTime Select optimised

Hi All,

 

Is there a simpler way of doing a select for a given date, for instance
if I
have a datetime field called date

 

And so its populated with a load of values such as 

 

2005-01-07 09:00

2005-01-07 10:00

2005-01-07 11:00

2005-01-07 12:00

 

If I wanted all records which fall on 2005-01-07 I could of course do 

 

Select * from TABLE where date  '2005-01-07' and date 
DATE_ADD('2005-01-07', INTERVAL 24 HOUR)

 

However is there a simpler way of doing it by just passing one date like

 

Select * from TABLE where date = '2005-01-07'

 

?

 


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 

-- 
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 Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 


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

RE: DateTime Select optimised

[Pete Moran]

Will try with the partial index,

Comparing the two without it is as follows

Using Like

mysql explain select count(*) from trip where pick_up_date like
'2005-01-01%';
+---+---+---+--+-+--+-+-
-+
| table | type  | possible_keys | key  | key_len | ref  |
rows| Extra|
+---+---+---+--+-+--+-+-
-+
| trip  | index | pick_up_date  | pick_up_date |   8 | NULL |
9365778 | Using where; Using index |
+---+---+---+--+-+--+-+-
-+
1 row in set (0.00 sec)

Using 2 dates

mysql explain select count(*) from trip where pick_up_date 
'2005-01-01' and pick_up_date  DATE_ADD('2005-01-01', INTERVAL 24
HOUR);
+---+---+---+--+-+--+---+---
---+
| table | type  | possible_keys | key  | key_len | ref  | rows 
| Extra|
+---+---+---+--+-+--+---+---
---+
| trip  | range | pick_up_date  | pick_up_date |   8 | NULL | 15437
| Using where; Using index |
+---+---+---+--+-+--+---+---
---+
1 row in set (0.01 sec)

-Original Message-
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:43 AM
To: Pete Moran
Cc: mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

I would investigate a partial index perhaps on the date only? You could
index on just the date eg.

ALTER TABLE thing ADD INDEX (date(10));

I don't have to time to check it out now and I'm not sure it will
automatically use it but an EXPLAIN on the statement would point you in
the right direction.

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Pete Moran [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:07 AM
To: Logan, David (SST - Adelaide); mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

The table is indexed on the date field, doing a 'like' results in a
table
scan, is there another way similar principal but would allow the indexes
to
be used ?

-Original Message-
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:24 AM
To: Pete Moran; mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

 
SELECT * FROM table WHERE date LIKE '2005-01-07%';

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Pete Moran [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 10:46 AM
To: mysql@lists.mysql.com
Subject: DateTime Select optimised

Hi All,

 

Is there a simpler way of doing a select for a given date, for instance
if I
have a datetime field called date

 

And so its populated with a load of values such as 

 

2005-01-07 09:00

2005-01-07 10:00

2005-01-07 11:00

2005-01-07 12:00

 

If I wanted all records which fall on 2005-01-07 I could of course do 

 

Select * from TABLE where date  '2005-01-07' and date 
DATE_ADD('2005-01-07', INTERVAL 24 HOUR)

 

However is there a simpler way of doing it by just passing one date like

 

Select * from TABLE where date = '2005-01-07'

 

?

 


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 

-- 
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 Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 


-- 
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 Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 


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

RE: DateTime Select optimised

[Logan, David (SST - Adelaide)]

Hi Paul,

On my installation I get 

mysql explain select * from mytest where date_thing like
2005-03-21%\G
*** 1. row ***
table: mytest
 type: ALL
possible_keys: date_thing
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 12
Extra: Using where
1 row in set (0.01 sec)

mysql

It won't use the index but you could use the FORCE INDEX (date_thing) to
ensure it does. It might be worth trying some timings to see what you
get.

BTW the FORCE INDEX is only there as of 4.0.9, if that can't be used
then try USE INDEX.

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Pete Moran [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:22 AM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

Will try with the partial index,

Comparing the two without it is as follows

Using Like

mysql explain select count(*) from trip where pick_up_date like
'2005-01-01%';
+---+---+---+--+-+--+---
--+-
-+
| table | type  | possible_keys | key  | key_len | ref  |
rows| Extra|
+---+---+---+--+-+--+---
--+-
-+
| trip  | index | pick_up_date  | pick_up_date |   8 | NULL |
9365778 | Using where; Using index |
+---+---+---+--+-+--+---
--+-
-+
1 row in set (0.00 sec)

Using 2 dates

mysql explain select count(*) from trip where pick_up_date 
'2005-01-01' and pick_up_date  DATE_ADD('2005-01-01', INTERVAL 24
HOUR);
+---+---+---+--+-+--+---
+---
---+
| table | type  | possible_keys | key  | key_len | ref  | rows 
| Extra|
+---+---+---+--+-+--+---
+---
---+
| trip  | range | pick_up_date  | pick_up_date |   8 | NULL | 15437
| Using where; Using index |
+---+---+---+--+-+--+---
+---
---+
1 row in set (0.01 sec)

-Original Message-
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:43 AM
To: Pete Moran
Cc: mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

I would investigate a partial index perhaps on the date only? You could
index on just the date eg.

ALTER TABLE thing ADD INDEX (date(10));

I don't have to time to check it out now and I'm not sure it will
automatically use it but an EXPLAIN on the statement would point you in
the right direction.

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Pete Moran [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:07 AM
To: Logan, David (SST - Adelaide); mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

The table is indexed on the date field, doing a 'like' results in a
table
scan, is there another way similar principal but would allow the indexes
to
be used ?

-Original Message-
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:24 AM
To: Pete Moran; mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

 
SELECT * FROM table WHERE date LIKE '2005-01-07%';

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Pete Moran [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 10:46 AM
To: mysql@lists.mysql.com
Subject: DateTime Select optimised

Hi All,

 

Is there a simpler way of doing a select for a given date, for instance
if I
have a datetime field called date

 

And so its populated with a load of values such as 

 

2005-01-07 09:00

2005-01-07 10:00

2005-01-07 11:00

2005-01-07 12:00

 

If I wanted all records which fall on 2005-01-07 I could of course do 

 

Select * from TABLE where date  '2005-01-07' and date 
DATE_ADD('2005-01-07', INTERVAL 24 HOUR)

 

However is there a simpler way of doing it by just passing one date like

 

Select * from TABLE where date = '2005-01-07'

 

?

 


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To 

Re: DateTime Select optimised

[Michael Stassen]

Pete Moran wrote:
Hi All,
Is there a simpler way of doing a select for a given date, for instance if I
have a datetime field called date
And so its populated with a load of values such as 

2005-01-07 09:00
2005-01-07 10:00
2005-01-07 11:00
2005-01-07 12:00
If I wanted all records which fall on 2005-01-07 I could of course do 

Select * from TABLE where date  2005-01-07 and date 
DATE_ADD(2005-01-07, INTERVAL 24 HOUR)
However is there a simpler way of doing it by just passing one date like
Select * from TABLE where date = 2005-01-07
?
No.  A date is fundamentally a range of datetime values, so this is the
right way.
I'd like to point out, however, that your query misses midnight (00:00). 
You should change it to use '=' instead of '':

  SELECT * FROM TABLE
  WHERE date = 2005-01-07 AND date  2005-01-07 + INTERVAL 24 HOUR;
Alternatively, you could add 1 day instead of 24 hours:
  SELECT * FROM TABLE
  WHERE date = 2005-01-07 AND date  2005-01-07 + INTERVAL 1 DAY;
You could make the query look simpler by changing the datetime column into a 
date or string and then doing an = comparison, but that would prevent the 
use of the index.

The only other way to make this simpler would be to split the date and time 
into separate columns, but that would no doubt cause headaches elsewhere.

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

Re: DateTime Select optimised

[Michael Stassen]

LIKE is a string comparison.  Using date LIKE '2005-01-07%' forces mysql 
to convert each datetime value in column date into a string in order to make 
the comparison.  When your comparison is based on a function of a column 
(the implicitly called cast as string, in this case), the index on the 
column cannot be used.  Hence, you get a full table scan.  Neither crippling 
the index on the datetime column nor adding FORCE INDEX will solve that 
basic problem.

Michael
Logan, David (SST - Adelaide) wrote:
Hi Paul,
On my installation I get 

mysql explain select * from mytest where date_thing like
2005-03-21%\G
*** 1. row ***
table: mytest
 type: ALL
possible_keys: date_thing
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 12
Extra: Using where
1 row in set (0.01 sec)
mysql
It won't use the index but you could use the FORCE INDEX (date_thing) to
ensure it does. It might be worth trying some timings to see what you
get.
BTW the FORCE INDEX is only there as of 4.0.9, if that can't be used
then try USE INDEX.
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
-Original Message-
From: Pete Moran [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:22 AM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

Will try with the partial index,
Comparing the two without it is as follows
Using Like
mysql explain select count(*) from trip where pick_up_date like
'2005-01-01%';
+---+---+---+--+-+--+---
--+-
-+
| table | type  | possible_keys | key  | key_len | ref  |
rows| Extra|
+---+---+---+--+-+--+---
--+-
-+
| trip  | index | pick_up_date  | pick_up_date |   8 | NULL |
9365778 | Using where; Using index |
+---+---+---+--+-+--+---
--+-
-+
1 row in set (0.00 sec)
Using 2 dates
mysql explain select count(*) from trip where pick_up_date 
'2005-01-01' and pick_up_date  DATE_ADD('2005-01-01', INTERVAL 24
HOUR);
+---+---+---+--+-+--+---
+---
---+
| table | type  | possible_keys | key  | key_len | ref  | rows 
| Extra|
+---+---+---+--+-+--+---
+---
---+
| trip  | range | pick_up_date  | pick_up_date |   8 | NULL | 15437
| Using where; Using index |
+---+---+---+--+-+--+---
+---
---+
1 row in set (0.01 sec)

-Original Message-
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:43 AM
To: Pete Moran
Cc: mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

I would investigate a partial index perhaps on the date only? You could
index on just the date eg.
ALTER TABLE thing ADD INDEX (date(10));
I don't have to time to check it out now and I'm not sure it will
automatically use it but an EXPLAIN on the statement would point you in
the right direction.
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
-Original Message-
From: Pete Moran [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:07 AM
To: Logan, David (SST - Adelaide); mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

The table is indexed on the date field, doing a 'like' results in a table
scan, is there another way similar principal but would allow the indexes
to be used ?
-Original Message-
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 11:24 AM
To: Pete Moran; mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

 
SELECT * FROM table WHERE date LIKE '2005-01-07%';

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
-Original Message-
From: Pete Moran [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 10:46 AM
To: mysql@lists.mysql.com
Subject: DateTime Select optimised

Hi All,
Is there a simpler way of doing a select for a given date, for instance
if I have a datetime field called date
And so its populated with a load of values such as 

2005-01-07 09:00
2005-01-07 10:00
2005-01-07 11:00
2005-01-07 12:00
If I wanted all records which fall on 2005-01-07 I could of course do 

Select * from TABLE where date  '2005-01-07' and date 
DATE_ADD('2005-01-07', INTERVAL 24 HOUR)
However is there a simpler way of doing it by just 

RE: DateTime Select optimised

[Pete Moran]

Thanks Michael,

This way works fine anyway was just interested if there was a better way of
doing it.

Pete

-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Monday, 21 March 2005 4:07 PM
To: Pete Moran
Cc: mysql@lists.mysql.com
Subject: Re: DateTime Select optimised


Pete Moran wrote:

 Hi All,
 
 Is there a simpler way of doing a select for a given date, for instance if
I
 have a datetime field called date
 
 And so its populated with a load of values such as 
 
 2005-01-07 09:00
 2005-01-07 10:00
 2005-01-07 11:00
 2005-01-07 12:00
 
 If I wanted all records which fall on 2005-01-07 I could of course do 
 
 Select * from TABLE where date  2005-01-07 and date 
 DATE_ADD(2005-01-07, INTERVAL 24 HOUR)
 
 However is there a simpler way of doing it by just passing one date like
 
 Select * from TABLE where date = 2005-01-07
 
 ?

No.  A date is fundamentally a range of datetime values, so this is the
right way.

I'd like to point out, however, that your query misses midnight (00:00). 
You should change it to use '=' instead of '':

   SELECT * FROM TABLE
   WHERE date = 2005-01-07 AND date  2005-01-07 + INTERVAL 24 HOUR;

Alternatively, you could add 1 day instead of 24 hours:

   SELECT * FROM TABLE
   WHERE date = 2005-01-07 AND date  2005-01-07 + INTERVAL 1 DAY;

You could make the query look simpler by changing the datetime column into a

date or string and then doing an = comparison, but that would prevent the 
use of the index.

The only other way to make this simpler would be to split the date and time 
into separate columns, but that would no doubt cause headaches elsewhere.

Michael




-- 
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
 


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

Re: [MySQL] mysql/snort/webmin/permissions

[Mark Sargent]

Mark Sargent wrote:
Ashley M. Kirchner wrote:
Mark Sargent wrote:
[EMAIL PROTECTED] pcre-5.0]# mysql -h mysql -p
-bash: mysql: command not found

You don't have mysql in root's path. Once you fix that little 
problem, you should be all set.

Hi All,
heck, how exactly do I do that..? Move the whole mysql dir to a path
dir..? Why is this so, when I followed the install steps from mysql's
site. Hmm, damn annoying. Cheers.
I followed the below from their install page,
If you run into problems, /please always use *mysqlbug*/ when posting
questions to a MySQL mailing list. Even if the problem isn't a bug,
*mysqlbug* gathers system information that helps others solve your
problem. By not using *mysqlbug*, you lessen the likelihood of getting a
solution to your problem. You can find *mysqlbug* in the |bin| directory
after you unpack the distribution. See Section 1.4.1.3, How to Report
Bugs or Problems http://dev.mysql.com/doc/mysql/en/bug-reports.html.
The basic commands you must execute to install and use a MySQL binary
distribution are:
shell groupadd mysql
shell useradd -g mysql mysql
shell cd /usr/local
shell gunzip  /|/path/to/mysql-VERSION-OS|/.tar.gz | tar xvf -
shell ln -s /|full-path-to-mysql-VERSION-OS|/ mysql
shell cd mysql
shell scripts/mysql_install_db --user=mysql
shell chown -R root  .
shell chown -R mysql data
shell chgrp -R mysql .
shell bin/mysqld_safe --user=mysql 
For versions of MySQL older than 4.0, substitute *bin/safe_mysqld* for
*bin/mysqld_safe* in the final command.
Mark Sargent.

Hi All,
guys, my sincere apologies, I just didn't do enuff research on things. I 
do have a Fedora book, and took a better look at it last night. I now 
understand what is required, and have successfully got things running. I 
appreciate your pateince with me. Cheers.

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

myodbc on mandrake

[symbulos partners]

Dear friends,

I cannot locat the odbc driver for mysql in mandrake.

Does anyone have an idea about the whereabouts of the thing?

thanks in advance
-- 
symbulos partners
-.-
symbulos - ethical services for your organisation
http://www.symbulos.com

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