RE: Order By and Ignore Punctuation

[Andreas Iwanowski]

I would suggest you order by something that includes a fulltext index on
the specific column.
Maybe check out the documentation on the MATCH()AGAINST() systax as well
as fulltext searches in general.

For example:
SELECT Col1, Col2, Score AS MATCH(TextCol) AGAINST ("") WHERE ... ORDER
BY Score;

Hope to help,
   -Andy

-Original Message-
From: Bill Guion [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 03, 2007 8:33 PM
To: mysql@lists.mysql.com
Subject: Order By and Ignore Punctuation

I would like to perform a query of a personnel database with an ORDER BY
clause that ignores punctuation. For example, O'shea would sort after
Osbourne, not to the beginning of the Os.

Is this doable in the query?

  -= Bill =-
-- 

You can tell a lot about a man by the way he handles these three
things: a rainy day, lost luggage, and tangled Christmas tree lights.



--
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: expire_logs_days

[Paul DuBois]

At 9:55 PM -0400 5/3/07, Baron Schwartz wrote:

Mark Leith wrote:

Paul DuBois wrote:

At 8:46 PM -0400 5/2/07, Baron Schwartz wrote:


Ofer Inbar wrote:

That's a good point, though probably a minor one: At most you would
end up with one binary logfile that's "old" and not deleted.  As soon
as you create a new one, that one would be deleted (if this 
feature works).


In our case, we flush logs nightly.  (but hardly ever restart mysqld)
  -- Cos



We roll many logs every day, but never restart unless we have to. 
So for us, it looked like it genuinely wasn't working on roll; I 
have no idea about restart.


I have a 4.1.13 server that's been up for 100 days.  It has 
expire_logs_days,

and I have 7 binlog files.  I do flush my logs once a day to force the logs
to rotate.

So that's one confirmation that it works, at least in 4.1.13. :-)



This seems to work just fine on 5.0.40 as well:

medusa:/usr/local/mysql/data root# ls -l
total 58352
-rw-rw1 mysql  wheel   5242880 May  3 10:49 ib_logfile0
-rw-rw1 mysql  wheel   5242880 May  2 22:27 ib_logfile1
-rw-rw1 mysql  wheel  18874368 May  3 10:47 ibdata1
-rw-rw1 mysql  wheel102514 May  3 10:55 medusa-bin.01
-rw-rw1 mysql  wheel102517 May  3 10:55 medusa-bin.02
-rw-rw1 mysql  wheel102517 May  3 10:55 medusa-bin.03
-rw-rw1 mysql  wheel102517 May  3 10:56 medusa-bin.04
-rw-rw1 mysql  wheel 81473 May  3 10:56 medusa-bin.05
-rw-rw1 mysql  wheel   375 May  3 10:56 medusa-bin.index
-rw-rw1 mysql  wheel 5 May  3 10:49 medusa.pid
drwxr-x---   53 mysql  wheel  1802 May  2 22:26 mysql
drwxr-x---9 mysql  wheel   306 May  3 10:52 test
medusa:/usr/local/mysql/data root# cat /etc/my.cnf
[mysqld]

log-bin
max_binlog_size = 100K
expire_logs_days = 2
medusa:/usr/local/mysql/data root# date  Thu May  3 10:58:22 BST 2007
medusa:/usr/local/mysql/data root# date
Sun May  6 10:58:42 BST 2007
medusa:/usr/local/mysql/data root# while [ 1 ]
 > do
 >   mysql -u root test -e 'insert into binlog_test (i,j) values (1,1)'
 > done
^C
medusa:/usr/local/mysql/data root# ls -l
total 57888
-rw-rw1 mysql  wheel   5242880 May  3 10:49 ib_logfile0
-rw-rw1 mysql  wheel   5242880 May  2 22:27 ib_logfile1
-rw-rw1 mysql  wheel  18874368 May  3 10:47 ibdata1
-rw-rw1 mysql  wheel102517 May  6 10:59 medusa-bin.05
-rw-rw1 mysql  wheel102517 May  6 10:59 medusa-bin.06
-rw-rw1 mysql  wheel 55853 May  6 10:59 medusa-bin.07
-rw-rw1 mysql  wheel   225 May  6 10:59 medusa-bin.index
-rw-rw1 mysql  wheel 5 May  3 10:49 medusa.pid
drwxr-x---   53 mysql  wheel  1802 May  2 22:26 mysql
drwxr-x---9 mysql  wheel   306 May  3 10:52 test

I declare 'No Bug Here' :) At least on the current versions of 5.0 
(tested on 5.0.40), anyway.


I will test again on my servers now that I have upgraded to 5.0.38. 
One question for people for whom expire_logs_days DOES work: do you 
have any slaves connected to the server?


Not in my case.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

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

Can't find file: 'general_log'

[Joseph Koenig]

Hi,

I'm running MySQL 5.0.27-standard-log on RHEL 4. After install, I moved the
data directory of MySQL. I updated everything that SELinux complained about,
etc., and have a functional install of MySQL. It's actually been up and
running with no issues for months. However, every time I run a mysqldump, I
get:

mysqldump: Got error: 1017: Can't find file: 'general_log' (errno: 2) when
using LOCK TABLES
mysqldump: Couldn't execute 'show create table `general_log`': Can't find
file: 'general_log' (errno: 2) (1017)
mysqldump: Couldn't execute 'show create table `slow_log`': Can't find file:
'slow_log' (errno: 2) (1017)

My dump proceeds and just spits these errors out to me. From what I can
tell, no harm is done, as the dump is full and is perfectly usable for
restoring databases from. However, it's driving me nuts. Is there any way to
create the necessary tables now so that MySQL does actually start logging
everything they way it should be able to and also will make these errors go
away? Thanks in advance,

Joe

-- 
Joseph Koenig
Creative Anvil, Inc.
Ph: 314.773.2611
FX: 314.773.2942
http://www.creativeanvil.com



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

Re: Order By and Ignore Punctuation

[Baron Schwartz]

Hi,

Bill Guion wrote:
I would like to perform a query of a personnel database with an ORDER BY 
clause that ignores punctuation. For example, O'shea would sort after 
Osbourne, not to the beginning of the Os.


Is this doable in the query?


If you only have a limited number of punctuation characters to remove, you could 
do something like


ORDER BY REPLACE(last_name, "'", "")

You can nest REPLACE() as many times as needed.  This is admittedly ugly and 
will defeat indexes, but it's the only thing I can think of.


If the sorting must be efficient, you might consider maintaining another column 
on the table, which has the name without punctuation.  You could then index this 
column and ORDER BY it without using any string manipulation.


Baron

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

Re: expire_logs_days

[Baron Schwartz]

Mark Leith wrote:

Paul DuBois wrote:

At 8:46 PM -0400 5/2/07, Baron Schwartz wrote:


Ofer Inbar wrote:

That's a good point, though probably a minor one: At most you would
end up with one binary logfile that's "old" and not deleted.  As soon
as you create a new one, that one would be deleted (if this feature 
works).


In our case, we flush logs nightly.  (but hardly ever restart mysqld)
  -- Cos



We roll many logs every day, but never restart unless we have to. So 
for us, it looked like it genuinely wasn't working on roll; I have no 
idea about restart.


I have a 4.1.13 server that's been up for 100 days.  It has 
expire_logs_days,
and I have 7 binlog files.  I do flush my logs once a day to force the 
logs

to rotate.

So that's one confirmation that it works, at least in 4.1.13. :-)



This seems to work just fine on 5.0.40 as well:

medusa:/usr/local/mysql/data root# ls -l
total 58352
-rw-rw1 mysql  wheel   5242880 May  3 10:49 ib_logfile0
-rw-rw1 mysql  wheel   5242880 May  2 22:27 ib_logfile1
-rw-rw1 mysql  wheel  18874368 May  3 10:47 ibdata1
-rw-rw1 mysql  wheel102514 May  3 10:55 medusa-bin.01
-rw-rw1 mysql  wheel102517 May  3 10:55 medusa-bin.02
-rw-rw1 mysql  wheel102517 May  3 10:55 medusa-bin.03
-rw-rw1 mysql  wheel102517 May  3 10:56 medusa-bin.04
-rw-rw1 mysql  wheel 81473 May  3 10:56 medusa-bin.05
-rw-rw1 mysql  wheel   375 May  3 10:56 medusa-bin.index
-rw-rw1 mysql  wheel 5 May  3 10:49 medusa.pid
drwxr-x---   53 mysql  wheel  1802 May  2 22:26 mysql
drwxr-x---9 mysql  wheel   306 May  3 10:52 test
medusa:/usr/local/mysql/data root# cat /etc/my.cnf
[mysqld]

log-bin
max_binlog_size = 100K
expire_logs_days = 2
medusa:/usr/local/mysql/data root# date  Thu May  3 10:58:22 BST 2007
medusa:/usr/local/mysql/data root# date
Sun May  6 10:58:42 BST 2007
medusa:/usr/local/mysql/data root# while [ 1 ]
 > do
 >   mysql -u root test -e 'insert into binlog_test (i,j) values (1,1)'
 > done
^C
medusa:/usr/local/mysql/data root# ls -l
total 57888
-rw-rw1 mysql  wheel   5242880 May  3 10:49 ib_logfile0
-rw-rw1 mysql  wheel   5242880 May  2 22:27 ib_logfile1
-rw-rw1 mysql  wheel  18874368 May  3 10:47 ibdata1
-rw-rw1 mysql  wheel102517 May  6 10:59 medusa-bin.05
-rw-rw1 mysql  wheel102517 May  6 10:59 medusa-bin.06
-rw-rw1 mysql  wheel 55853 May  6 10:59 medusa-bin.07
-rw-rw1 mysql  wheel   225 May  6 10:59 medusa-bin.index
-rw-rw1 mysql  wheel 5 May  3 10:49 medusa.pid
drwxr-x---   53 mysql  wheel  1802 May  2 22:26 mysql
drwxr-x---9 mysql  wheel   306 May  3 10:52 test

I declare 'No Bug Here' :) At least on the current versions of 5.0 
(tested on 5.0.40), anyway.


I will test again on my servers now that I have upgraded to 5.0.38.  One 
question for people for whom expire_logs_days DOES work: do you have any slaves 
connected to the server?


Thanks
Baron

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

Order By and Ignore Punctuation

[Bill Guion]

I would like to perform a query of a personnel database with an ORDER 
BY clause that ignores punctuation. For example, O'shea would sort 
after Osbourne, not to the beginning of the Os.


Is this doable in the query?

 -= Bill =-
--

You can tell a lot about a man by the way he handles these three
things: a rainy day, lost luggage, and tangled Christmas tree lights.



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

RE: Less | More

[John Trammell]

> -Original Message-
> From: John Kebbel [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 03, 2007 7:34 AM
> To: MySQL
> Subject: Less | More
>
> Is there a mysql command line equivalent to | less or | more to make it
> easier to scan rows one screen at a time?

I have the command:

pager="less -n -S"

in my ".my.cnf" file.  Works like a charm.  The "-S" prevents line wrapping, 
and the "-n" is a speed hack IIRC.

JT




INFORMATION IN THIS MESSAGE, INCLUDING ANY ATTACHMENTS, IS INTENDED FOR THE 
PERSONAL AND CONFIDENTIAL USE OF THE INTENDED RECIPIENT(S) NAMED ABOVE. If you 
are not an intended recipient of this message, or an agent responsible for 
delivering it to an intended recipient, you are hereby notified that you have 
received this message in error, and that any review, dissemination, 
distribution, or copying of this message is strictly prohibited. If you 
received this message in error, please notify the sender immediately, delete 
the message, and return any hard copy print-outs.

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

Re: Bin logs...

[Gerald L. Clark]

Ashley M. Kirchner wrote:


   Probably a silly question, but if I have my logs set to binary, how 
can I read them and check on a query that's failing?  I don't 
necessarily want to switch to text logs because we'll want to do 
replication soon here, so I want to keep it as a binary log.  My problem 
is I have a query that's failing and I can't figure it, I can't read the 
log file.


   Suggestions anyone?


Use the mysqlbinlog command.

--
Gerald L. Clark
Supplier Systems Corporation

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

Bin logs...

[Ashley M. Kirchner]

   Probably a silly question, but if I have my logs set to binary, how 
can I read them and check on a query that's failing?  I don't 
necessarily want to switch to text logs because we'll want to do 
replication soon here, so I want to keep it as a binary log.  My problem 
is I have a query that's failing and I can't figure it, I can't read the 
log file.


   Suggestions anyone?

--
W | It's not a bug - it's an undocumented feature.
 +
 Ashley M. Kirchner    .   303.442.6410 x130
 IT Director / SysAdmin / Websmith . 800.441.3873 x130
 Photo Craft Imaging   . 3550 Arapahoe Ave. #6
 http://www.pcraft.com . .  ..   Boulder, CO 80303, U.S.A. 




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

RE: Less | More

[Jerry Schwartz]

There is no \P command in the Windows version of MySQL (as of 4.x). It would
be a nice addition, since there is a more command in the Windows CLI, but
I'm not sure how (or if) the piping can be set up in Windows.

Regards,

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

860.674.8796 / FAX: 860.674.8341


> -Original Message-
> From: Baron Schwartz [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 03, 2007 8:42 AM
> To: [EMAIL PROTECTED]
> Cc: MySQL
> Subject: Re: Less | More
>
> John Kebbel wrote:
> > Is there a mysql command line equivalent to | less or |
> more to make it
> > easier to scan rows one screen at a time?
> >
> >
>
> Type ? from within the command-line client to see help.  One
> of the lines shows this:
>
> pager (\P) Set PAGER [to_pager]. Print the query results
> via PAGER.
>
> On Linux, when I just type \P, it does this:
>
> mysql> \P
> PAGER set to '/usr/bin/less'
> mysql>
>
> Every query thereafter runs through less.
>
> Baron
>
> --
> 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: Enforcing Data Format

[Martijn Tonies]

> I'd go the other way and use a trigger to validate the data, and roll
> back the transaction if it isn't in valid format (assuming you are using
> transactions). This way, ANY app that puts data in that field gets the
> validation. An error in an application or typing by someone who has
> direct table access will leave you with bunk data, which is worse IMO
> than having to figure out the code for the trigger.
>
> You have to keep in mind that I generally spend half my day fixing
> broken data though :)

Which is exactly why constraints and this type of stuff needs
to be implemented in the database :-)

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

>
>
> Mogens Melander wrote:
> > On Thu, May 3, 2007 12:56, Tim Milstead wrote:
> >> Hello,
> >>
> >> Is it possible to enforce data formatting in fields using something
like
> >> a regular expression?
> >> varchar is great but does not stop someone putting in the wrong
> >> reference number.
> >> I suspect the answer is no, you have to do it at a higher level. What
> >> aprroach would people suggest?
> >> Perhaps another table with two fields - field_name and field_regex?
> >
> > And, what would you have mysql do if a user failed to enter fx. valid
date ?
> >
> > Maybe you'd be better off, handling input validation in the
user-interface.
> >
>
> -- 
> 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]

Seeking a MySQL DB Guru for Project/Partner!!

[bruce]

Hi.

We're creating a startup team for creating web based apps/services. The goal
is to create business that's initially Sweat Equity based. For those of you
not familiar with the term, this means that there will not be any initial
compensation/salary, just the thrill of working with some really good people
on the team, as we attempt to build a successful business! There will also
be equity sharing.

Our basic skills cover the web design/developer roles, the marketing/sales
roles, and the Linux admin roles. The team is currently 5-6 people in the
US/Europe. We need you to be able to fulfill the DB Guru role!

The role of the DB Guru will be to make sure that the apps that tie into the
database are doing so in an efficient manner, and that we have the ability
to scale. You'll be involved with dealing with redundancy issues, efficiency
issues, rollover, hardware/software, etc...

You should have a background in being able to make MySQL 'sing' with regards
to web based implementations.

If you're looking to be part of a team, and you're entrepreneurial in
nature, we'd like to talk to you.

If you aren't the right person, but you know someone who might be, we ask
that you please pass this along!

Thanks for your time on this.

Bruce Douglas
[EMAIL PROTECTED]


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

Re: Enforcing Data Format

[Stephen Cook]

I'd go the other way and use a trigger to validate the data, and roll 
back the transaction if it isn't in valid format (assuming you are using 
transactions). This way, ANY app that puts data in that field gets the 
validation. An error in an application or typing by someone who has 
direct table access will leave you with bunk data, which is worse IMO 
than having to figure out the code for the trigger.


You have to keep in mind that I generally spend half my day fixing 
broken data though :)




Mogens Melander wrote:

On Thu, May 3, 2007 12:56, Tim Milstead wrote:

Hello,

Is it possible to enforce data formatting in fields using something like
a regular expression?
varchar is great but does not stop someone putting in the wrong
reference number.
I suspect the answer is no, you have to do it at a higher level. What
aprroach would people suggest?
Perhaps another table with two fields - field_name and field_regex?


And, what would you have mysql do if a user failed to enter fx. valid date ?

Maybe you'd be better off, handling input validation in the user-interface.



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

Re: Less | More

[John Kebbel]

Thank you, gentlemen. From the little I had read about pager so far, I
assumed it had something to do with outputting to a text file. First
chance I get, I'll read up on pager in detail.

On Thu, 2007-05-03 at 12:39 +,
[EMAIL PROTECTED] wrote:
> at your friendly mysql command-line prompt enter a "?", and then look
> at the options ... hint "pager".
> 
> and/or search the documentation for "set pager".
> 
>   - Rick
> 
>  Original Message 
> > Date: Thursday, May 03, 2007 08:33:57 AM -0400
> > From: John Kebbel <[EMAIL PROTECTED]>
> > To: MySQL 
> > Subject: Less | More
> > 
> > Is there a mysql command line equivalent to | less or | more to make
> > it easier to scan rows one screen at a time? 
> > 
> 
> -- End Original Message --
> 
> 


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

Re: Less | More

[Baron Schwartz]

John Kebbel wrote:

Is there a mysql command line equivalent to | less or | more to make it
easier to scan rows one screen at a time? 





Type ? from within the command-line client to see help.  One of the lines shows 
this:

pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.

On Linux, when I just type \P, it does this:

mysql> \P
PAGER set to '/usr/bin/less'
mysql>

Every query thereafter runs through less.

Baron

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

Re: Enforcing Data Format

[Martijn Tonies]

>
> > > Is it possible to enforce data formatting in fields using
> > something like
> > > a regular expression?
> > > varchar is great but does not stop someone putting in the wrong
> > > reference number.
> >
> > Dates should be in a DATE column, not a varchar.
>
> The OP says 'data' not 'date' :) He is talking about a reference number
that
> needs to be in a certain format.

Woops, I stand corrected :-)

Dang, I really should stop reading data as date. I do that quite often.

> I too would argue that this requirement is more application layer than in
> the database. Surely anyone editting the tables directly should know what
> they're doing and adhere to the correct format.


Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

Urgent: mysql_history on windows ?

[Abhishek Jain]

Hi,
I need to track the mysql commands executed on mine system , I am running 
windows with mysql 5.x 
I know there is a file .mysql_history and hopes there must be a similar file on 
windows too.

Pl. help me urgently,
Thanks,
--
Regards,
Abhishek Jain

Less | More

[John Kebbel]

Is there a mysql command line equivalent to | less or | more to make it
easier to scan rows one screen at a time? 


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

RE: Enforcing Data Format

[Edward Kay]

> > Is it possible to enforce data formatting in fields using
> something like
> > a regular expression?
> > varchar is great but does not stop someone putting in the wrong
> > reference number.
>
> Dates should be in a DATE column, not a varchar.

The OP says 'data' not 'date' :) He is talking about a reference number that
needs to be in a certain format.

I too would argue that this requirement is more application layer than in
the database. Surely anyone editting the tables directly should know what
they're doing and adhere to the correct format.

Edward


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

Re: Enforcing Data Format

[Mogens Melander]

On Thu, May 3, 2007 12:56, Tim Milstead wrote:
> Hello,
>
> Is it possible to enforce data formatting in fields using something like
> a regular expression?
> varchar is great but does not stop someone putting in the wrong
> reference number.
> I suspect the answer is no, you have to do it at a higher level. What
> aprroach would people suggest?
> Perhaps another table with two fields - field_name and field_regex?

And, what would you have mysql do if a user failed to enter fx. valid date ?

Maybe you'd be better off, handling input validation in the user-interface.

-- 
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



-- 
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.


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

Re: Enforcing Data Format

[Martijn Tonies]

> Is it possible to enforce data formatting in fields using something like 
> a regular expression?
> varchar is great but does not stop someone putting in the wrong 
> reference number.

Dates should be in a DATE column, not a varchar.

> I suspect the answer is no, you have to do it at a higher level. What 
> aprroach would people suggest?
> Perhaps another table with two fields - field_name and field_regex?

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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

Re: Enforcing Data Format

[B. Keith Murphy]

Baron Schwartz wrote:

Hi,

Tim Milstead wrote:

Hello,

Is it possible to enforce data formatting in fields using something 
like a regular expression?
varchar is great but does not stop someone putting in the wrong 
reference number.
I suspect the answer is no, you have to do it at a higher level. What 
aprroach would people suggest?

Perhaps another table with two fields - field_name and field_regex?



If you are using 5.x or greater, you can do some kinds of data 
validation with a trigger.


Baron

I would argue that your data-validation should really not be done with 
triggers - I would look more at the php/asp/whatever code to do this.  
Shouldn't this be more efficient than using triggers?  At the very least 
doing validation on the web server will make it easier to add capacity 
with multiple web servers.


Keith



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

Re: Enforcing Data Format

[Baron Schwartz]

Hi,

Tim Milstead wrote:

Hello,

Is it possible to enforce data formatting in fields using something like 
a regular expression?
varchar is great but does not stop someone putting in the wrong 
reference number.
I suspect the answer is no, you have to do it at a higher level. What 
aprroach would people suggest?

Perhaps another table with two fields - field_name and field_regex?



If you are using 5.x or greater, you can do some kinds of data validation with 
a trigger.

Baron

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

Enforcing Data Format

[Tim Milstead]

Hello,

Is it possible to enforce data formatting in fields using something like 
a regular expression?
varchar is great but does not stop someone putting in the wrong 
reference number.
I suspect the answer is no, you have to do it at a higher level. What 
aprroach would people suggest?

Perhaps another table with two fields - field_name and field_regex?

Thanks,

Tim.

This message has been checked for viruses but the contents of an attachment
may still contain software viruses, which could damage your computer system:
you are advised to perform your own checks. Email communications with the
University of Nottingham may be monitored as permitted by UK legislation.


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

Re: expire_logs_days

[Mark Leith]

Paul DuBois wrote:

At 8:46 PM -0400 5/2/07, Baron Schwartz wrote:


Ofer Inbar wrote:

That's a good point, though probably a minor one: At most you would
end up with one binary logfile that's "old" and not deleted.  As soon
as you create a new one, that one would be deleted (if this feature 
works).


In our case, we flush logs nightly.  (but hardly ever restart mysqld)
  -- Cos



We roll many logs every day, but never restart unless we have to. So 
for us, it looked like it genuinely wasn't working on roll; I have no 
idea about restart.


I have a 4.1.13 server that's been up for 100 days.  It has 
expire_logs_days,
and I have 7 binlog files.  I do flush my logs once a day to force the 
logs

to rotate.

So that's one confirmation that it works, at least in 4.1.13. :-)



This seems to work just fine on 5.0.40 as well:

medusa:/usr/local/mysql/data root# ls -l
total 58352
-rw-rw1 mysql  wheel   5242880 May  3 10:49 ib_logfile0
-rw-rw1 mysql  wheel   5242880 May  2 22:27 ib_logfile1
-rw-rw1 mysql  wheel  18874368 May  3 10:47 ibdata1
-rw-rw1 mysql  wheel102514 May  3 10:55 medusa-bin.01
-rw-rw1 mysql  wheel102517 May  3 10:55 medusa-bin.02
-rw-rw1 mysql  wheel102517 May  3 10:55 medusa-bin.03
-rw-rw1 mysql  wheel102517 May  3 10:56 medusa-bin.04
-rw-rw1 mysql  wheel 81473 May  3 10:56 medusa-bin.05
-rw-rw1 mysql  wheel   375 May  3 10:56 medusa-bin.index
-rw-rw1 mysql  wheel 5 May  3 10:49 medusa.pid
drwxr-x---   53 mysql  wheel  1802 May  2 22:26 mysql
drwxr-x---9 mysql  wheel   306 May  3 10:52 test
medusa:/usr/local/mysql/data root# cat /etc/my.cnf
[mysqld]

log-bin
max_binlog_size = 100K
expire_logs_days = 2
medusa:/usr/local/mysql/data root# date  
Thu May  3 10:58:22 BST 2007

medusa:/usr/local/mysql/data root# date
Sun May  6 10:58:42 BST 2007
medusa:/usr/local/mysql/data root# while [ 1 ]
> do
>   mysql -u root test -e 'insert into binlog_test (i,j) values (1,1)'
> done
^C
medusa:/usr/local/mysql/data root# ls -l
total 57888
-rw-rw1 mysql  wheel   5242880 May  3 10:49 ib_logfile0
-rw-rw1 mysql  wheel   5242880 May  2 22:27 ib_logfile1
-rw-rw1 mysql  wheel  18874368 May  3 10:47 ibdata1
-rw-rw1 mysql  wheel102517 May  6 10:59 medusa-bin.05
-rw-rw1 mysql  wheel102517 May  6 10:59 medusa-bin.06
-rw-rw1 mysql  wheel 55853 May  6 10:59 medusa-bin.07
-rw-rw1 mysql  wheel   225 May  6 10:59 medusa-bin.index
-rw-rw1 mysql  wheel 5 May  3 10:49 medusa.pid
drwxr-x---   53 mysql  wheel  1802 May  2 22:26 mysql
drwxr-x---9 mysql  wheel   306 May  3 10:52 test

I declare 'No Bug Here' :) At least on the current versions of 5.0 
(tested on 5.0.40), anyway.


Cheers!

Mark

--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified?  www.mysql.com/certification


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