Re: [ACFUG Discuss] problem inserting crc32 values
Teddy:
Thanks for helping with this! I really appreciate the help.
The storage type is MyISAM.
What's confounding me is that the PHP files are inserting this data
and doing it using the crc32 function, as int(10). Looks like I may
have to dig deeper into the php files or find a php person.
Honestly, this is a bit over my head.
_
Derrick Peavy
derr...@derrickpeavy.com
404-786-5036
“Innovation distinguishes between a leader and a follower.” -Steve Jobs
_
On Nov 25, 2009, at 12:53 PM, Teddy R. Payne wrote:
Derrick,
What I found related has to do deal with storage sizes. In this
manual note:
http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html
I am not sure your storage configuration, but it has a caveat with
NDB and number storage.
BigInt works because it is twice the size of integers by default.
Integer being 4 byte storage versus 8 byte storage of bigint.
Does this apply? Data truncation is when storage sizes are
mismatched, which is an obvious statement. The data engine is
telling you data size is greater than storage availability in the
column.
Beyond that, I would have to defer to the rest of the community.
Teddy R. Payne, ACCFD
Google Talk - teddyrpa...@gmail.com
On Wed, Nov 25, 2009 at 12:04 PM, Derrick Peavy > wrote:
Teddy:
Banging my head here. Can't get CAST to work in any form, even with
the database itself (using phpmyadmin). Here is the actual data and
info.
QUERY 1 (does not have to be two queries, but this allows me to show
you the exact values I am trying to insert)
SELECT
crc32('') AS referer_checksum,
crc32('ccstageread') AS domain_checksum,
crc32('127.0.0.1') AS resource_checksum
Those values are then supplied to the next query...
QUERY 2
INSERT INTO my_table
(dt, readableDT, referer, referer_checksum, domain_checksum,
referer_is_local, resource, resource_checksum, resource_title,
search_terms, img_search_found)
VALUES
(UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', 0, 2846130217,
-1, '127.0.0.1', 3619153832, 'CollegeClassifieds.com - Allegheny
College', '', 0)
The fields referer_checksum, domain_checksum, and resource_checksum
are MySQL 4.1.13 data type int(10)
The length of the crc32 value is 10 as seen in the second query
above. If I change the field type to BIGINT and leave it as length
10, it works just fine. No error. But as an INT(10) I get the
following error, which is caused by the second crc32 value of
2846130217 (the domain_checksum), not the first crc32 value, which
in this case is 0. But if that first value is not 0, then the error
is on that value, ergo each/any first crc32 value.
The problem with changing the data type to BIGINT is, apparently,
the PHP stuff that is reading this data and generating reports
doesn't seem to work correctly if the data type is changed.
Type Database
Query Error
Data truncation: Out of range value adjusted for column
'domain_checksum' at row 1
Native Error Code 0
SQL State 01004
SQL INSERT INTO my_table (dt, readableDT, referer, referer_checksum,
domain_checksum, referer_is_local, resource, resource_checksum,
resource_title, search_terms, img_search_found) VALUES
(UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', 0, 2846130217,
-1, '127.0.0.1', 3619153832, 'CollegeClassifieds.com - Allegheny
College College', '', 0)
_
Derrick Peavy
derr...@derrickpeavy.com
“Innovation distinguishes between a leader and a follower.” -Steve
Jobs
_
On Nov 25, 2009, at 9:19 AM, Teddy R. Payne wrote:
Derrick,
Data truncation usually brings to mind java data conversion. Have
you tried casting the results of the crc32 function?:
CAST(crc32('127.0.0.1') AS int)
Alternatively, what version of ColdFusion are you using? I have
looked at the MySQL driver in CF8 recently. In the past, I had
good success using JConnector to replace the JDBC MySQL default
driver that came with CF.
Here is the 4.1 manual referene to JConnector:
http://dev.mysql.com/doc/refman/4.1/en/connector-j.html
And here is the JDBC driver configurations instructions:
http://kb2.adobe.com/cps/025/6ef0253.html
If you are already using JConnector, ignore the recommendation. =)
Teddy R. Payne, ACCFD
Google Talk - teddyrpa...@gmail.com
On Tue, Nov 24, 2009 at 5:15 PM, Derrick Peavy > wrote:
Trying to remove a PHP script from a cold fusion site by replacing
it with a simple CF query.
Current PHP file puts a crc32 value into an int(10) field in MySQL
4.1.13
Trying to do this with CF, nothing fancy. Basic SQL statement.
Example:
INSERT INTO exampleTable
(domain_checksum)
VALUES (crc32('127.0.0.1'))
I then get this error: Data truncation: Out of range value
adjusted for column 'domain_checksum' at row 1
But the data looks correct. For example, the value for 127.0.0.1 is
generated as 3619153
Re: [ACFUG Discuss] problem inserting crc32 values
Derrick,
What I found related has to do deal with storage sizes. In this manual
note:
http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html
I am not sure your storage configuration, but it has a caveat with NDB and
number storage.
BigInt works because it is twice the size of integers by default. Integer
being 4 byte storage versus 8 byte storage of bigint.
Does this apply? Data truncation is when storage sizes are mismatched,
which is an obvious statement. The data engine is telling you data size is
greater than storage availability in the column.
Beyond that, I would have to defer to the rest of the community.
Teddy R. Payne, ACCFD
Google Talk - teddyrpa...@gmail.com
On Wed, Nov 25, 2009 at 12:04 PM, Derrick Peavy wrote:
> Teddy:
>
> Banging my head here. Can't get CAST to work in any form, even with the
> database itself (using phpmyadmin). Here is the actual data and info.
>
> *
> *
> *QUERY 1 (does not have to be two queries, but this allows me to show you
> the exact values I am trying to insert)*
>
> SELECT
> crc32('') AS *referer_checksum*,
> crc32('ccstageread') AS *domain_checksum*,
> crc32('127.0.0.1') AS *resource_checksum*
>
> Those values are then supplied to the next query...
>
> *QUERY 2*
>
> INSERT INTO my_table
> (dt, readableDT, referer, *referer_checksum*, *domain_checksum*,
> referer_is_local, resource, *resource_checksum*, resource_title,
> search_terms, img_search_found)
> VALUES
> (UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', *0*, *2846130217*, -1,
> '127.0.0.1', *3619153832*, 'CollegeClassifieds.com - Allegheny College',
> '', 0)
>
>
> The fields referer_checksum, domain_checksum, and resource_checksum are
> MySQL 4.1.13 data type int(10)
>
> The length of the crc32 value is 10 as seen in the second query above. If I
> change the field type to BIGINT and leave it as length 10, it works just
> fine. No error. But as an INT(10) I get the following error, which is caused
> by the second crc32 value of 2846130217 (the domain_checksum), not the
> first crc32 value, which in this case is 0. But if that first value is not
> 0, then the error is on that value, ergo each/any first crc32 value.
>
> The problem with changing the data type to BIGINT is, apparently, the PHP
> stuff that is reading this data and generating reports doesn't seem to work
> correctly if the data type is changed.
>
>
> TypeDatabaseQuery Error
> Data truncation: Out of range value adjusted for column 'domain_checksum'
> at row 1Native Error Code0SQL State01004SQLINSERT INTO my_table (dt,
> readableDT, referer, referer_checksum, domain_checksum, referer_is_local,
> resource, resource_checksum, resource_title, search_terms, img_search_found)
> VALUES (UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', 0, 2846130217, -1,
> '127.0.0.1', 3619153832, 'CollegeClassifieds.com - Allegheny College
> College', '', 0)
>
> *
> _*
> *Derrick Peavy*
> derr...@derrickpeavy.com
> *
> *
> *“Innovation distinguishes between a leader and a follower.” -Steve Jobs*
> *_*
>
>
>
> On Nov 25, 2009, at 9:19 AM, Teddy R. Payne wrote:
>
> Derrick,
> Data truncation usually brings to mind java data conversion. Have you
> tried casting the results of the crc32 function?:
>
> CAST(crc32('127.0.0.1') AS int)
>
>
> Alternatively, what version of ColdFusion are you using? I have looked at
> the MySQL driver in CF8 recently. In the past, I had good success using
> JConnector to replace the JDBC MySQL default driver that came with CF.
>
> Here is the 4.1 manual referene to JConnector:
> http://dev.mysql.com/doc/refman/4.1/en/connector-j.html
>
> And here is the JDBC driver configurations instructions:
> http://kb2.adobe.com/cps/025/6ef0253.html
>
> If you are already using JConnector, ignore the recommendation. =)
>
>
>
> Teddy R. Payne, ACCFD
> Google Talk - teddyrpa...@gmail.com
>
>
>
> On Tue, Nov 24, 2009 at 5:15 PM, Derrick Peavy
> wrote:
>
>> Trying to remove a PHP script from a cold fusion site by replacing it with
>> a simple CF query.
>>
>> Current PHP file puts a crc32 value into an int(10) field in MySQL 4.1.13
>>
>> Trying to do this with CF, nothing fancy. Basic SQL statement.
>>
>> Example:
>>
>> INSERT INTO exampleTable
>> (domain_checksum)
>> VALUES (crc32('127.0.0.1'))
>>
>> I then get this error: *Data truncation: Out of range value adjusted for
>> column 'domain_checksum' at row 1*
>>
>> But the data looks correct. For example, the value for 127.0.0.1 is
>> generated as 3619153832
>>
>> If I try to insert the values directly into MySQL, no problem. But using a
>> standard CFQUERY produces an error.
>> *
>> _*
>> *Derrick Peavy*
>> derr...@derrickpeavy.com
>> 404-786-5036
>> *
>> *
>> *“Innovation distinguishes between a leader and a follower.” -Steve Jobs*
>> *_*
>>
>>
>
>
Re: [ACFUG Discuss] problem inserting crc32 values
Teddy:
Banging my head here. Can't get CAST to work in any form, even with
the database itself (using phpmyadmin). Here is the actual data and
info.
QUERY 1 (does not have to be two queries, but this allows me to show
you the exact values I am trying to insert)
SELECT
crc32('') AS referer_checksum,
crc32('ccstageread') AS domain_checksum,
crc32('127.0.0.1') AS resource_checksum
Those values are then supplied to the next query...
QUERY 2
INSERT INTO my_table
(dt, readableDT, referer, referer_checksum, domain_checksum,
referer_is_local, resource, resource_checksum, resource_title,
search_terms, img_search_found)
VALUES
(UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', 0, 2846130217, -1,
'127.0.0.1', 3619153832, 'CollegeClassifieds.com - Allegheny College',
'', 0)
The fields referer_checksum, domain_checksum, and resource_checksum
are MySQL 4.1.13 data type int(10)
The length of the crc32 value is 10 as seen in the second query above.
If I change the field type to BIGINT and leave it as length 10, it
works just fine. No error. But as an INT(10) I get the following
error, which is caused by the second crc32 value of 2846130217 (the
domain_checksum), not the first crc32 value, which in this case is 0.
But if that first value is not 0, then the error is on that value,
ergo each/any first crc32 value.
The problem with changing the data type to BIGINT is, apparently, the
PHP stuff that is reading this data and generating reports doesn't
seem to work correctly if the data type is changed.
TypeDatabase
Query Error Data truncation: Out of range value adjusted for column
'domain_checksum' at row 1
Native Error Code 0
SQL State 01004
SQL INSERT INTO my_table (dt, readableDT, referer, referer_checksum,
domain_checksum, referer_is_local, resource, resource_checksum,
resource_title, search_terms, img_search_found) VALUES
(UNIX_TIMESTAMP(), {ts '2009-11-25 11:51:12'}, '', 0, 2846130217, -1,
'127.0.0.1', 3619153832, 'CollegeClassifieds.com - Allegheny College
College', '', 0)
_
Derrick Peavy
derr...@derrickpeavy.com
“Innovation distinguishes between a leader and a follower.” -Steve Jobs
_
On Nov 25, 2009, at 9:19 AM, Teddy R. Payne wrote:
Derrick,
Data truncation usually brings to mind java data conversion. Have
you tried casting the results of the crc32 function?:
CAST(crc32('127.0.0.1') AS int)
Alternatively, what version of ColdFusion are you using? I have
looked at the MySQL driver in CF8 recently. In the past, I had good
success using JConnector to replace the JDBC MySQL default driver
that came with CF.
Here is the 4.1 manual referene to JConnector:
http://dev.mysql.com/doc/refman/4.1/en/connector-j.html
And here is the JDBC driver configurations instructions:
http://kb2.adobe.com/cps/025/6ef0253.html
If you are already using JConnector, ignore the recommendation. =)
Teddy R. Payne, ACCFD
Google Talk - teddyrpa...@gmail.com
On Tue, Nov 24, 2009 at 5:15 PM, Derrick Peavy > wrote:
Trying to remove a PHP script from a cold fusion site by replacing
it with a simple CF query.
Current PHP file puts a crc32 value into an int(10) field in MySQL
4.1.13
Trying to do this with CF, nothing fancy. Basic SQL statement.
Example:
INSERT INTO exampleTable
(domain_checksum)
VALUES (crc32('127.0.0.1'))
I then get this error: Data truncation: Out of range value adjusted
for column 'domain_checksum' at row 1
But the data looks correct. For example, the value for 127.0.0.1 is
generated as 3619153832
If I try to insert the values directly into MySQL, no problem. But
using a standard CFQUERY produces an error.
_
Derrick Peavy
derr...@derrickpeavy.com
404-786-5036
“Innovation distinguishes between a leader and a follower.” -Steve
Jobs
_
Re: [ACFUG Discuss] problem inserting crc32 values
And many blessings in return, your Majesty. ;-) Teddy
Re: [ACFUG Discuss] problem inserting crc32 values
Ahhh. That's why I will always be on this list. I just needed to see
that.
I will try and let you know.
Thank you, Mr. Payne Teddy R. of USA. Most wonderful blessings.
Sincerely, the King of Nigerian.
_
Derrick Peavy
derr...@derrickpeavy.com
404-786-5036
“Innovation distinguishes between a leader and a follower.” -Steve Jobs
_
On Nov 25, 2009, at 9:19 AM, Teddy R. Payne wrote:
Derrick,
Data truncation usually brings to mind java data conversion. Have
you tried casting the results of the crc32 function?:
CAST(crc32('127.0.0.1') AS int)
Alternatively, what version of ColdFusion are you using? I have
looked at the MySQL driver in CF8 recently. In the past, I had good
success using JConnector to replace the JDBC MySQL default driver
that came with CF.
Here is the 4.1 manual referene to JConnector:
http://dev.mysql.com/doc/refman/4.1/en/connector-j.html
And here is the JDBC driver configurations instructions:
http://kb2.adobe.com/cps/025/6ef0253.html
If you are already using JConnector, ignore the recommendation. =)
Teddy R. Payne, ACCFD
Google Talk - teddyrpa...@gmail.com
On Tue, Nov 24, 2009 at 5:15 PM, Derrick Peavy > wrote:
Trying to remove a PHP script from a cold fusion site by replacing
it with a simple CF query.
Current PHP file puts a crc32 value into an int(10) field in MySQL
4.1.13
Trying to do this with CF, nothing fancy. Basic SQL statement.
Example:
INSERT INTO exampleTable
(domain_checksum)
VALUES (crc32('127.0.0.1'))
I then get this error: Data truncation: Out of range value adjusted
for column 'domain_checksum' at row 1
But the data looks correct. For example, the value for 127.0.0.1 is
generated as 3619153832
If I try to insert the values directly into MySQL, no problem. But
using a standard CFQUERY produces an error.
_
Derrick Peavy
derr...@derrickpeavy.com
404-786-5036
“Innovation distinguishes between a leader and a follower.” -Steve
Jobs
_
Re: [ACFUG Discuss] problem inserting crc32 values
Derrick,
Data truncation usually brings to mind java data conversion. Have you tried
casting the results of the crc32 function?:
CAST(crc32('127.0.0.1') AS int)
Alternatively, what version of ColdFusion are you using? I have looked at
the MySQL driver in CF8 recently. In the past, I had good success using
JConnector to replace the JDBC MySQL default driver that came with CF.
Here is the 4.1 manual referene to JConnector:
http://dev.mysql.com/doc/refman/4.1/en/connector-j.html
And here is the JDBC driver configurations instructions:
http://kb2.adobe.com/cps/025/6ef0253.html
If you are already using JConnector, ignore the recommendation. =)
Teddy R. Payne, ACCFD
Google Talk - teddyrpa...@gmail.com
On Tue, Nov 24, 2009 at 5:15 PM, Derrick Peavy wrote:
> Trying to remove a PHP script from a cold fusion site by replacing it with
> a simple CF query.
>
> Current PHP file puts a crc32 value into an int(10) field in MySQL 4.1.13
>
> Trying to do this with CF, nothing fancy. Basic SQL statement.
>
> Example:
>
> INSERT INTO exampleTable
> (domain_checksum)
> VALUES (crc32('127.0.0.1'))
>
> I then get this error: *Data truncation: Out of range value adjusted for
> column 'domain_checksum' at row 1*
>
> But the data looks correct. For example, the value for 127.0.0.1 is
> generated as 3619153832
>
> If I try to insert the values directly into MySQL, no problem. But using a
> standard CFQUERY produces an error.
> *
> _*
> *Derrick Peavy*
> derr...@derrickpeavy.com
> 404-786-5036
> *
> *
> *“Innovation distinguishes between a leader and a follower.” -Steve Jobs*
> *_*
>
>
