headers
Vladimir V. Kolpakov | 1 Nov 2004 05:01

Re: IP regex

On Sun, Oct 31, 2004 at 02:02:26PM -0500, Michael Stassen wrote:
> My advice: It makes no sense to keep IPs in a VARCHAR(30) column.  (Even if 
> a string was the right way to go, you'd need at most 15 characters.)  The 
> better way is to use an INT UNSIGNED.  Then you store an IP using 
> INET_ATON(IP) and retrieve it with INET_NTOA(ip_column).  That takes 4 
> bytes instead of 8 to 16, and reduces the likelihood of garbage values in 

It's very common mistake, --
to think integer occupies in databases 4 bytes.
Please look DESC on your table, and you can find
it takes 11 bytes, because is stored in character form.

> >>>I have been given the chore of taking a DB table of client records, 
> >>>which has a field called IP address.  Whenever a client logs onto our 
> >>>site, I'm supposed to display the IP address they logged on from THE 
> >>>LAST TIME they visited, as a security measure.

> >>>Can someone point me in the direction of creating REGEX to test whether 
> >>>an IP address is valid. (Valid in the sense of syntax i.e. 
> >>>192.168.24.20 is  valid, but
> >>>277.22.49.75, or 65.23 is not)?
> >>>Gregg Allen

Gregg, --

Why you need to check IP addr, anyway?
If it comes from apache cgi's $ENV{REMOTE_ADDR},
and user input does not affect on it,
what's the reason for regex-ing?
(Continue reading)

Permalink | Reply | 6 comments |
headers
Jamie McCarthy | 1 Nov 2004 05:31

Re: IP regex

w <at> sfgate.com (Vladimir V. Kolpakov) writes:

> On Sun, Oct 31, 2004 at 02:02:26PM -0500, Michael Stassen wrote:
> > Then you store an IP using INET_ATON(IP) and retrieve it with
> > INET_NTOA(ip_column).  That takes 4 bytes instead of 8 to 16
> 
> It's very common mistake, -- to think integer occupies in
> databases 4 bytes. Please look DESC on your table, and you can
> find it takes 11 bytes, because is stored in character form.

You misunderstand the output of DESCRIBE.  INT is stored in 4 bytes.
The "(11)" is the display width, which doesn't really affect anything
done through the perl interface.
-- 
  Jamie McCarthy
 http://mccarthy.vg/
  jamie <at> mccarthy.vg

--

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules <at> m.gmane.org
Permalink | Reply | 5 comments |
headers
Michael Stassen | 1 Nov 2004 05:58
Picon

Re: IP regex

The storage requirements for each column type are documented in the manual 
<http://dev.mysql.com/doc/mysql/en/Storage_requirements.html>, which 
confirms that INTs take 4 bytes.

Michael

Jamie McCarthy wrote:

> w <at> sfgate.com (Vladimir V. Kolpakov) writes:
> 
>>On Sun, Oct 31, 2004 at 02:02:26PM -0500, Michael Stassen wrote:
>>
>>>Then you store an IP using INET_ATON(IP) and retrieve it with
>>>INET_NTOA(ip_column).  That takes 4 bytes instead of 8 to 16
>>
>>It's very common mistake, -- to think integer occupies in
>>databases 4 bytes. Please look DESC on your table, and you can
>>find it takes 11 bytes, because is stored in character form.
> 
> You misunderstand the output of DESCRIBE.  INT is stored in 4 bytes.
> The "(11)" is the display width, which doesn't really affect anything
> done through the perl interface.

--

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules <at> m.gmane.org
Permalink | Reply | 0 comments |
headers
Vladimir V. Kolpakov | 1 Nov 2004 06:05

Re: IP regex

On Sun, Oct 31, 2004 at 11:31:33PM -0500, Jamie McCarthy wrote:
> w <at> sfgate.com (Vladimir V. Kolpakov) writes:
> > On Sun, Oct 31, 2004 at 02:02:26PM -0500, Michael Stassen wrote:
> > > Then you store an IP using INET_ATON(IP) and retrieve it with
> > > INET_NTOA(ip_column).  That takes 4 bytes instead of 8 to 16
> > 
> > It's very common mistake, -- to think integer occupies in
> > databases 4 bytes. Please look DESC on your table, and you can
> > find it takes 11 bytes, because is stored in character form.
> 
> You misunderstand the output of DESCRIBE.  INT is stored in 4 bytes.
> The "(11)" is the display width, which doesn't really affect anything
> done through the perl interface.

It's true,... but only for field data either, -- unless index is not created.

--w

--

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules <at> m.gmane.org
Permalink | Reply | 3 comments |
headers
Rudy Lippan | 1 Nov 2004 14:49

ANNOUNCE: DBD::mysql 2.9005_3


Hello everyone,

DBD::mysql 2.9005_3 should now be up on CPAN.

Changelog:

2004-10-27  Rudolf Lippan <rlippan <at> remotelinux.com> (2.9005_3)
        * Removed requirement that mysql.mtest be included in the distribution.
        * DBI 1.43 is required -- It is needed for support of 
          $dbh->last_insert_id().
        * Added $dbh->{mysql_unsafe_bind_type_guessing} = 2 which will look at
          each bind_param and make guess based on whether it looks like a 
          number, and if the parameter looks like a number, it will not be 
          quoted.  [thanks to dragonchild for cleaning up the initial rough 
          draft impementation making it ready for prime time]
        * Applied patch to fix column which broke with later version of mysql
          [Tim Bunce]
        * Merged $dbh->last_inert_id() from the 3.0 dev tree
        * If you are using perl 5.008 without a $ENV{LANG} of "C". Makefile.PL
          will now 1. Complain loudly, and 2. set LANG to "C" and then
          exec itself with the new lang setting.
        * Added '$dbh->{mysql_unsafe_bind_type_guessing}'. When this flag is
          enabled any scalar that is passed to execute() and was used in
          numeric context will be treated as a number when being sent to the
          database backend. This can be a secuity problem in that if you 
          use a string in numeric context, it will NOT be quoted before being 
          passed to the database -- use at your own risk. The default is off.
        * fixed typo in pod changing  failed_auto_reconnects_failed to 
          auto_reconnects_failed [Paul DuBois <paul <at> snake.net>]
(Continue reading)

Permalink | Reply | 0 comments |
headers
Michael Stassen | 1 Nov 2004 15:51
Picon

Re: IP regex


Vladimir V. Kolpakov wrote:

> On Sun, Oct 31, 2004 at 11:31:33PM -0500, Jamie McCarthy wrote:
> 
>>w <at> sfgate.com (Vladimir V. Kolpakov) writes:
>>
>>>On Sun, Oct 31, 2004 at 02:02:26PM -0500, Michael Stassen wrote:
>>>
>>>>Then you store an IP using INET_ATON(IP) and retrieve it with
>>>>INET_NTOA(ip_column).  That takes 4 bytes instead of 8 to 16
>>>
>>>It's very common mistake, -- to think integer occupies in
>>>databases 4 bytes. Please look DESC on your table, and you can
>>>find it takes 11 bytes, because is stored in character form.
>>
>>You misunderstand the output of DESCRIBE.  INT is stored in 4 bytes.
>>The "(11)" is the display width, which doesn't really affect anything
>>done through the perl interface.
> 
> 
> It's true,... but only for field data either, -- unless index is not created.
> 
> 
> --w

I don't understand.  Are you saying that an INT column will be more than 4 
bytes if you take its index into account?  Well, yes, but the same will be 
true for an indexed VARCHAR(15).  Furthermore, an index on a VARCHAR(15) 
will be larger than an index on an INT.  Try it.  You'll see that an index
(Continue reading)

Permalink | Reply | 2 comments |
headers
Vladimir V. Kolpakov | 2 Nov 2004 06:45

Re: IP regex

On Mon, Nov 01, 2004 at 09:51:43AM -0500, Michael Stassen wrote:
> Vladimir V. Kolpakov wrote:
> >On Sun, Oct 31, 2004 at 11:31:33PM -0500, Jamie McCarthy wrote:
> >>w <at> sfgate.com (Vladimir V. Kolpakov) writes:
> >>>On Sun, Oct 31, 2004 at 02:02:26PM -0500, Michael Stassen wrote:
> >>>>Then you store an IP using INET_ATON(IP) and retrieve it with
> >>>>INET_NTOA(ip_column).  That takes 4 bytes instead of 8 to 16

> I don't understand.  Are you saying that an INT column will be more than 4 
> bytes if you take its index into account?  Well, yes, but the same will be 
> true for an indexed VARCHAR(15).  Furthermore, an index on a VARCHAR(15) 
> will be larger than an index on an INT.  Try it.  You'll see that an index 
> on an INT column has key_len=5, and an index on a VARCHAR(15) has 
> key_len=16.  Together, a VARCHAR(15) column + index is at least twice the 
> size of an INT column + index.

Sorry for confusion,
my focus was slightly different: what's retrieval requirements are,
rather then storage requirements. In general they are opposite
in data schema design process.
IP address is not integer by it's nature and is not string also.
It's closer to bits string representation, which can involve
creation of bitmap index (or maked up with SET type),
but quite often can be effectively worked around using fulltex index.

Original author didn't respond on my question about usage
of IP address, so further discussion in that focus becomes meaningless.

As of "integer versus character" question,
I can also show example where storage requirements are equal
(Continue reading)

Permalink | Reply | 1 comment |
headers
Steve Hay | 2 Nov 2004 11:07

Re: ANNOUNCE: DBD::mysql 2.9005_3

Rudy Lippan wrote:

>Hello everyone,
>
>DBD::mysql 2.9005_3 should now be up on CPAN.
>
Attached is a patch which removes the use (or, rather, attempted use!) 
of mysql_config on Win32, since that program doesn't exist on Win32.

The patch isn't as big as it looks at first -- it's just that I've 
inserted a couple of "if ($^O !~ /mswin32/i)" lines and indented the 
sections of code within them.

It also omits "-g" from the cflags (the VC++ compiler uses different 
flags to enable debugging, and in fact Perl on Win32 uses some debugging 
flags by default anyway) and omits "-lz -lm -lcrypt -lnsl" from libs 
(since these libraries don't exist and aren't required on Win32).

Btw, This release passes all tests on Win32 using perl-5.8.5 and 
mysql-4.1.3.

- Steve

PS.  I'm still longing for some Unicode support.  What are the current 
plans on this?  Are we waiting for a "stable" mysql-4.1.x and/or DBI v2, 
or will something be done sooner?

------------------------------------------------
This email has been scanned for viruses and content by the Radan Computational Webshield Appliances.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Harald Fuchs | 2 Nov 2004 11:26

Re: IP regex

In article <20041102054559.GA17567 <at> sfgate.com>,
"Vladimir V. Kolpakov" <w <at> sfgate.com> writes:

> IP address is not integer by it's nature and is not string also.

Huh?  An IPv4 address is a sequence of 32 bits.  Although MySQL does
not have a column type for an IPv4 address, it does have support
functions for them, namely INET_ATON and INET_NTOA.  Since these
functions operate on integers, what's wrong with using INT4?

> It's closer to bits string representation, which can involve
> creation of bitmap index (or maked up with SET type),
> but quite often can be effectively worked around using fulltex index.

If you're talking about indexed searching, I doubt there's anything
faster than

  SELECT whatever FROM mytable WHERE ipv4 = INET_ATON(myinput)

with a normal index on the ipv4 column.

> As of "integer versus character" question,
> I can also show example where storage requirements are equal
> for both types,

Of course also CHAR(4) BINARY would be able to hold an IPv4 address,
but you gain nothing with that and lose the support functions.

--

-- 
MySQL Perl Mailing List
(Continue reading)

Permalink | Reply | 0 comments |
headers
Scott Simpson | 3 Nov 2004 17:49

Load Data into more than 1 Table

Can I use 'LOAD DATA INFILE' to load data into more than 1 table?

For example, say I have a text file containing

1       birthday       cat
2       anniversary    dog

and I want column 2 in table birthdays and column 3 in table pets and
I'm lazy and can't be bothered to split the file or loop through an
insert statement.  

Can someone suggest a shortcut?

Thanks,
Scott
Permalink | Reply | 3 comments |

Gmane