INSERT/UPDATE number weirdness
Hi, This is bugging me Using PHP/MySQL. I have three input fields to hold a user's phone number. The MySQL fields are set to SMALLINT. If any of the strings to update the DB starts with a 0 (zero), the first number is truncated (0206 becomes 206). Any other combination where the first digit is not a 0 works. I have tried both $tr_p1SUF = $_POST['phone1SUF']; ...SET phone1SUF = '$tr_p1SUF'... AND ...SET phone1SUF = $tr_p1SUF... I have tried changing the DB field types to char, int, text, varchar Idea, suggestions? Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT/UPDATE number weirdness
Hi James, Well if you used [VAR]CHAR/TEXT and then did the INSERT query *with* the leading 0s, it should work. If the 0s aren't there in PHP, they won't be stored in the CHAR column though. But anyway, it's more efficient to stick with SMALLINT... and use the ZEROFILL attribute on the column. :-) e.g. phone1SUF SMALLINT(n) UNSIGNED ZEROFILL NOT NULL where n is the number of leading 0s to prepend if needed to make the display n digits long. With this method, you *don't need* the leading 0s in your PHP variable. So just ...SET phone1SUF = $tr_p1SUF... Hope that helps. Matt - Original Message - From: James Johnson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, September 20, 2003 1:11 AM Subject: INSERT/UPDATE number weirdness Hi, This is bugging me Using PHP/MySQL. I have three input fields to hold a user's phone number. The MySQL fields are set to SMALLINT. If any of the strings to update the DB starts with a 0 (zero), the first number is truncated (0206 becomes 206). Any other combination where the first digit is not a 0 works. I have tried both $tr_p1SUF = $_POST['phone1SUF']; ...SET phone1SUF = '$tr_p1SUF'... AND ...SET phone1SUF = $tr_p1SUF... I have tried changing the DB field types to char, int, text, varchar Idea, suggestions? Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Next release of v4.1
Hi all, Does anyone know when the next release of MySQL version 4.1 will come out? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Next release of v4.1
Daniel, - Original Message - From: Daniel Kiss [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, September 20, 2003 1:08 PM Subject: Next release of v4.1 Hi all, Does anyone know when the next release of MySQL version 4.1 will come out? my guess is, before Nov 15th, 2003. In July I believed 4.1.1 would arrive in August, but I forgot the amount of work needed to merge all changes from 4.0 to 4.1. Thanks Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
new MySQL Server
I would like to get a viewpoint from the members on the following: 1) the amount of $$$ is NOT a consideration Looking at building a MySQL server to handle in-house critical work. Web = page data is generated from the database plus constant inputting of data = and calculation. Application to be run: Red Hat 9.0 PHP4 MySQL 4 Apache 2 SSH I'm looking at the following: 1) IDE RAID5 with 4 x 120GB drives 7200RPM ATA100 2) Sony CDT or AIT tapebackup 3) 2 x 10/100/1000 Intel NIC 4) Intel P4 3GHz CPU 5) 2GB PC3200 ECC Memory Motherboard consists of the following: Socket 478 Intel=AE Pentium=AE 4/Celeron=AE NetBurst 4 x DDR SDRAM PC3200 ECC Registered, 4GB max. 5 PCI, 1 AGP 8x (1.5v only) Highpoint HPT374 UDMA/ATA 133 RAID Broadcom BCM5705 Gigabit Ethernet ATX Now I've worked with the Broadcom NIC and that works great, but I have not = worked with a Highpoint RAID card. Does anyone have good and /or bad = experiences with this card or should I buy a separate card? My other option is to go with a SCSI system using a Adaptec RAID card, = etc. Jon L. Miller, MCNE, CNS Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Enforcing NOT NULL restriction on UPDATE
I am experiencing odd behavior, and I'm hoping someone can tell me if
I'm doing something wrong or explain why it is behaving this way, and
how to get around it...
When I update a row in a table with a field's data set to NULL, but
the table has a NOT NULL restriction on the field, the update still
completes successfully, but transforms the NULL value to an empty
string.
The preferred behavior would be for mysql to emit an error and reject
the attempt to set a NULL value for a NOT NULL field, and not
transform the data.
Sample sql to reproduce is below:
create table domain
(
-- define columns
id int(10) unsigned not null auto_increment,
namevarchar(128)not null,
-- set primary key
primary key (id),
-- build indexes
unique key domain_name (name),
) type=innodb;
mysql insert into domain (name) values (NULL);
ERROR 1048: Column 'name' cannot be null
-- This is desired behavior;
mysql insert into domain (name) values ('google.com');
Query OK, 1 row affected (0.01 sec)
mysql select * from domain;
+++
| id | name |
+++
| 57 | google.com |
+++
1 row in set (0.00 sec)
mysql update domain set name = NULL where id = 57;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
-- update is accepted, why??
-- shouldn't it error??
mysql select * from domain;
++--+
| id | name |
++--+
| 57 | |
++--+
1 row in set (0.01 sec)
mysql select * from domain where name = '';
++--+
| id | name |
++--+
| 57 | |
++--+
1 row in set (0.00 sec)
mysql select * from domain where name = NULL;
Empty set (0.00 sec)
For some reason, 'show create table domain' shows a different sql
statement than what I actually used to create the table, notably the
appended default '' on the definition of the name field:
CREATE TABLE `domain` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(128) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `domain_name` (`name`)
) TYPE=InnoDB
I'm not sure why mysql is allowing attempted updates of a NOT NULL field
with a NULL value to succeed, or why it is altering the data being
sent in. Is there a way to suppress this behavior and have it be more
strict in what data it accepts for NOT NULL fields?
Thanks for any input.
--
Steven Hilton [EMAIL PROTECTED]
http://mshiltonj.com/
It is the duty
of the patriot
to protect his country
from its government.
-- Paine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data store/extract help almost there ,still error's
Thanks John, I'm trying various syntax changes but still getting Empty set mysql INSERT INTO user_profile (userid,password) - VALUES (James,AES_ENCRYPT(bond,007)); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM user_profile WHERE userid=james AND - AES_DECRYPT(password,bond)=007; Empty set (0.00 sec) -Original Message- From: John Hopkins [mailto:[EMAIL PROTECTED] Sent: Friday, September 19, 2003 6:38 PM To: Stout, Jeff Subject: RE: Data store/extract help almost there I've been following with interest. As I understand the previous messages, you are indeed almost there Try this: mysql INSERT INTO user_profile (userid,password) - VALUES (joeblow,AES_ENCRYPT(spit,swallow)); Query OK, 1 row affected (0.01 sec) mysql SELECT * FROM user_profile WHERE userid=joeblow AND - AES_DECRYPT(password,swallow)=spit; I don't have MySQL running anywhere right now, can't test it. The point is you need to decrypt what's in *password*, and compare that to the unencrypted password (entered by the user?). Hope this helps, John Hopkins Hopkins IT -Original Message- From: Stout, Jeff [mailto:[EMAIL PROTECTED] Sent: Friday, September 19, 2003 4:31 PM To: PF: MySQL; [EMAIL PROTECTED] Subject: RE: Data store/extract help almost there Almost there, here is the error mysql INSERT INTO user_profile (userid,password) - VALUES (joeblow,AES_ENCRYPT(spit,swallow)); Query OK, 1 row affected (0.01 sec) mysql SELECT * FROM user_profile WHERE userid=joeblow AND - password=AES_DECRYPT(spit,swallow); Empty set (0.01 sec) -Original Message- From: PF: MySQL [mailto:[EMAIL PROTECTED] Sent: Friday, September 19, 2003 4:48 PM To: [EMAIL PROTECTED] Subject: RE: Data store/extract help !! Woops, Sorry, Phone call distracted me AES_ENCRYPT(string,key_string) AES_DECRYPT(string,key_string) These functions allow encryption/decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as Rijndael. Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source. We chose 128 bits because it is much faster and it is usually secure enough. The input arguments may be any length. If either argument is NULL, the result of this function is also NULL. As AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated as 16*(trunc(string_length/16)+1). If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key are invalid. You can use the AES functions to store data in an encrypted form by modifying your queries: INSERT INTO t VALUES (1,AES_ENCRYPT(text,password)); You can get even more security by not transferring the key over the connection for each query, which can be accomplished by storing it in a server side variable at connection time: SELECT @password:=my password; INSERT INTO t VALUES (1,AES_ENCRYPT(text,@password)); AES_ENCRYPT() and AES_DECRYPT() were added in version 4.0.2, and can be considered the most cryptographically secure encryption functions currently available in MySQL. -Kevin -- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 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: innodb and fragmentation
When you issue this null ALTER TABLE, is the entire table locked during the build? i.e. Is the only way to defragment to effectively take the table offline during the rebuild? Is there a method to estimate time to do this rebuild? thanks, Jon - Original Message - From: Per Andreas Buer [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, September 19, 2003 6:17 PM Subject: Re: innodb and fragmentation Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. -- Per Andreas Buer -- 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: Data store/extract help almost there ,still error's
hi you mix the password and key, use AES_ENCRYPT(the clear password, the key to crypt); AES_DECRYPT(the crypted password, the key to crypt); example UPDATE user_profile SET username=james , password=AES_ENCRYPT(bond007, sydo89) WHERE personid=1; saves the password in the record with the id = 1 SELECT username, AES_DECRYPT(password,sydo89) AS psw FROM user_profile WHERE personid=1 retrieves the username and the clear password regards benny - Original Message - From: Stout, Jeff [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, September 20, 2003 4:09 PM Subject: RE: Data store/extract help almost there ,still error's Thanks John, I'm trying various syntax changes but still getting Empty set mysql INSERT INTO user_profile (userid,password) - VALUES (James,AES_ENCRYPT(bond,007)); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM user_profile WHERE userid=james AND - AES_DECRYPT(password,bond)=007; Empty set (0.00 sec) -Original Message- From: John Hopkins [mailto:[EMAIL PROTECTED] Sent: Friday, September 19, 2003 6:38 PM To: Stout, Jeff Subject: RE: Data store/extract help almost there I've been following with interest. As I understand the previous messages, you are indeed almost there Try this: mysql INSERT INTO user_profile (userid,password) - VALUES (joeblow,AES_ENCRYPT(spit,swallow)); Query OK, 1 row affected (0.01 sec) mysql SELECT * FROM user_profile WHERE userid=joeblow AND - AES_DECRYPT(password,swallow)=spit; I don't have MySQL running anywhere right now, can't test it. The point is you need to decrypt what's in *password*, and compare that to the unencrypted password (entered by the user?). Hope this helps, John Hopkins Hopkins IT -Original Message- From: Stout, Jeff [mailto:[EMAIL PROTECTED] Sent: Friday, September 19, 2003 4:31 PM To: PF: MySQL; [EMAIL PROTECTED] Subject: RE: Data store/extract help almost there Almost there, here is the error mysql INSERT INTO user_profile (userid,password) - VALUES (joeblow,AES_ENCRYPT(spit,swallow)); Query OK, 1 row affected (0.01 sec) mysql SELECT * FROM user_profile WHERE userid=joeblow AND - password=AES_DECRYPT(spit,swallow); Empty set (0.01 sec) -Original Message- From: PF: MySQL [mailto:[EMAIL PROTECTED] Sent: Friday, September 19, 2003 4:48 PM To: [EMAIL PROTECTED] Subject: RE: Data store/extract help !! Woops, Sorry, Phone call distracted me AES_ENCRYPT(string,key_string) AES_DECRYPT(string,key_string) These functions allow encryption/decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as Rijndael. Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source. We chose 128 bits because it is much faster and it is usually secure enough. The input arguments may be any length. If either argument is NULL, the result of this function is also NULL. As AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated as 16*(trunc(string_length/16)+1). If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key are invalid. You can use the AES functions to store data in an encrypted form by modifying your queries: INSERT INTO t VALUES (1,AES_ENCRYPT(text,password)); You can get even more security by not transferring the key over the connection for each query, which can be accomplished by storing it in a server side variable at connection time: SELECT @password:=my password; INSERT INTO t VALUES (1,AES_ENCRYPT(text,@password)); AES_ENCRYPT() and AES_DECRYPT() were added in version 4.0.2, and can be considered the most cryptographically secure encryption functions currently available in MySQL. -Kevin -- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforcing NOT NULL restriction on UPDATE
At 9:30 AM -0400 9/20/03, Steven Hilton wrote: I am experiencing odd behavior, and I'm hoping someone can tell me if I'm doing something wrong or explain why it is behaving this way, and how to get around it... When I update a row in a table with a field's data set to NULL, but the table has a NOT NULL restriction on the field, the update still completes successfully, but transforms the NULL value to an empty string. Right. If you update a NOT NULL column to NULL, the column is assigned the default value for the column type. See: http://www.mysql.com/doc/en/CREATE_TABLE.html Read the para that begins A DEFAULT value has to be..., which describes what the default values are for each column type. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data store/extract help almost there ,still error's
At 8:09 AM -0600 9/20/03, Stout, Jeff wrote: Thanks John, I'm trying various syntax changes but still getting Empty set mysql INSERT INTO user_profile (userid,password) - VALUES (James,AES_ENCRYPT(bond,007)); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM user_profile WHERE userid=james AND - AES_DECRYPT(password,bond)=007; Empty set (0.00 sec) The key goes in the second argument for both functions. Assuming the key is 007, your second statement has two errors: - It puts the password column in quotes (password), thus causing to be treated as a literal string, not a column value. - It mixes up the password value and key value. Try this for the second statement: SELECT * FROM user_profile WHERE userid=james AND AES_DECRYPT(password,007)=bond; When faced with this kind of situation, you might try try selecting the AES_DECRYPT() value to see what you're getting, rather than just trying to figure out the right format in the WHERE clause. For example: mysql SELECT userid, AES_DECRYPT(password,007) FROM user_profile; ++-+ | userid | AES_DECRYPT(password,007) | ++-+ | James | bond| ++-+ Another thing: Make sure your password column is at least 16 bytes long. AES_ENCRYPT() produces a 128-bit value (if memory serves), so if you have a column shorter than 16 bytes, you'll have problems. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT/UPDATE number weirdness
Hi Matt, Thanks. That did the trick. James -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Friday, September 19, 2003 11:34 PM To: [EMAIL PROTECTED] Subject: Re: INSERT/UPDATE number weirdness Hi James, Well if you used [VAR]CHAR/TEXT and then did the INSERT query *with* the leading 0s, it should work. If the 0s aren't there in PHP, they won't be stored in the CHAR column though. But anyway, it's more efficient to stick with SMALLINT... and use the ZEROFILL attribute on the column. :-) e.g. phone1SUF SMALLINT(n) UNSIGNED ZEROFILL NOT NULL where n is the number of leading 0s to prepend if needed to make the display n digits long. With this method, you *don't need* the leading 0s in your PHP variable. So just ...SET phone1SUF = $tr_p1SUF... Hope that helps. Matt - Original Message - From: James Johnson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, September 20, 2003 1:11 AM Subject: INSERT/UPDATE number weirdness Hi, This is bugging me Using PHP/MySQL. I have three input fields to hold a user's phone number. The MySQL fields are set to SMALLINT. If any of the strings to update the DB starts with a 0 (zero), the first number is truncated (0206 becomes 206). Any other combination where the first digit is not a 0 works. I have tried both $tr_p1SUF = $_POST['phone1SUF']; ...SET phone1SUF = '$tr_p1SUF'... AND ...SET phone1SUF = $tr_p1SUF... I have tried changing the DB field types to char, int, text, varchar Idea, suggestions? Thanks, James -- 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: new MySQL Server
I believe I saw something in the mySQL manual about going with a 64 bit system if it was an option because mySQL uses lots of 64 bit data types. So you will see a significant increase in speed with 64 bits versus 32. :: drools at though of Apple w/ 8GB RAM :: :-D -Dan On Sat, 2003-09-20 at 08:11, Jon Miller wrote: I would like to get a viewpoint from the members on the following: 1) the amount of $$$ is NOT a consideration Looking at building a MySQL server to handle in-house critical work. Web = page data is generated from the database plus constant inputting of data = and calculation. Application to be run: Red Hat 9.0 PHP4 MySQL 4 Apache 2 SSH I'm looking at the following: 1) IDE RAID5 with 4 x 120GB drives 7200RPM ATA100 2) Sony CDT or AIT tapebackup 3) 2 x 10/100/1000 Intel NIC 4) Intel P4 3GHz CPU 5) 2GB PC3200 ECC Memory Motherboard consists of the following: Socket 478 Intel=AE Pentium=AE 4/Celeron=AE NetBurst 4 x DDR SDRAM PC3200 ECC Registered, 4GB max. 5 PCI, 1 AGP 8x (1.5v only) Highpoint HPT374 UDMA/ATA 133 RAID Broadcom BCM5705 Gigabit Ethernet ATX Now I've worked with the Broadcom NIC and that works great, but I have not = worked with a Highpoint RAID card. Does anyone have good and /or bad = experiences with this card or should I buy a separate card? My other option is to go with a SCSI system using a Adaptec RAID card, = etc. Jon L. Miller, MCNE, CNS Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
