MySQL Bugs: #65492: MySql updates blank values for the data values captured from form

Bug #65492	MySql updates blank values for the data values captured from form
Submitted:	2 Jun 2012 12:53	Modified:	22 Sep 2012 9:45
Reporter:	Alex modirzadeh	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	MySql 5.1.63-cll version: 10 and phpMyAdmin Version information: 3.4.10.1 - php-5.2.x	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
$this->db->where('user_id', $agent_id);
$result = $this->db->update('users', $update_array); 

The above codes update the database record correct when data moved to the update table in the program such as:

$update_array['us_address']="54 George street";

but, the above codes updates the database record with blank values when the data captured from the form.

In both cases, print_r($update_array); shows the data is there, in both cases the $result is equal to 1 that means update was successful.

How to repeat:

You don't have access to the codes, however if the above information are not enough, please advise to provide you with the instruction to run the system.

We need to run a number of programs to reach the point, this needs to be run twice, the 1st time needs to be run when the data is captured from the form, and  the second time the data will be moved in the program to the variables.

Suggested fix:
Just give you a hint, this problem occurs only within the Codeigniter environment. I tested the subject out of the Codeigniter environment, MySql works correct and updates the database record in both cases correct,including when data captured from the form. To resolve this problem, I believe, We need to concentrate on all the programs & micros related to MySql database inside the codeigniter environment. Thanks for your assistance.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

I believe this is a bug and a big bug, mySql updates the database record with a wrong value (the captured data value from the from is lost and the database is updated with blank).  This is a bug that causes having wrong result, while no erroneous message displayed. Thanks

Why do you think this bug is in MySQL software and not in that 3rd party software/environment you use, or in your application?

sir, I believe the bug is associated with mysql because print_r($upadte_array); shows the correct value of the data captured from the form, but mysql updates the record with blank field. While there is no extra code in between the print_r($update_array) and the update query statement in the program. By the way if there is a problem with the third party, why when the data is moved into the variable, the database record get updated with the correct data value? It sounds for any reason mysql ignores data values captured from from. Thanks

In any case we need complete, repeatable test case that can be reproduced outside of your environment, with clear description of all the software used and exact steps (or complete application code with build instructions) to reproduce the problem. I do not know what "form" are you talking about, how exactly this "form" communicates with MySQL server, what this version number (3.4.10.1) refers to... 

You have to put everything one may need to reproduce the problem into this bug report (if you want it to be considered seriously). Or it will just hang around in "Open" status until somebody will add missing details and explanations.

All programs related to this process are attached, in addition the flow diagram is also attached for your information. Please follow the direction of the flow diagram to reach the update statement of mySql that will be in the last program "Agency_mode".

Please note the line numbers are on the left side of each box, these indicate entry to and exit from each program.

Thanks

I do not see anything attached to this report. Had you used "Files" tab to upload files, or something else?

Diagram for the process from the menu to reach the bug point .

Attachment: Process-Diagram.docx (application/vnd.openxmlformats-officedocument.wordprocessingml.document, text), 44.83 KiB.

This is the program that update take place in it between lines 89 - 214

Attachment: agency_mod.php (application/octet-stream, text), 20.27 KiB.

Agency program - Please refer to the diagram

Attachment: agency.php (application/octet-stream, text), 69.18 KiB.

This is the agent edit program that we enter the agent's info for update

Attachment: agency_agencyAdmin_editAgentProfile.php (application/octet-stream, text), 11.01 KiB.

This program shows all the agent related to one agency

Attachment: agency_agencyAdmin_manageAgentProfile_showAgent.php (application/octet-stream, text), 3.07 KiB.

This program display heading related to the agency

Attachment: agency_agencyAdmin_manageAgentProfile_top.php (application/octet-stream, text), 2.10 KiB.

this is the menu program by cliking on a button line 116 we start the process

Attachment: agency_agencyAdmin_homePage.php (application/octet-stream, text), 8.40 KiB.

Dear sir,

I uploaded all the related programs.

Please advise
Thanks

Dear Mr. Valeriy Kravchuk,

I prepared all the materials you asked for, cloud you please advise what is the progress in finding a solution to this bug?

Thanks
Alex

Dear Mr. Valeriy Kravchuk,

Just give me a sign were are we standing? what have you done so far? and what is the degree of progress? Can I help at all?

Thanks
Alex

Any progress on this matter or it has been forgotten?

Please note the new information

MySQL
    Server: Localhost via UNIX socket
    Server version: 5.1.63-cll
    Protocol version: 10
    User: gfireale_root@localhost
    MySQL charset: UTF-8 Unicode (utf8)

Web Server
    cpsrvd 11.32.3.21
    MySQL client version: 5.1.63
    PHP extension: mysql Documentation

phpMyAdmin
    Version information: 3.4.10.1
    Documentation
    Wiki
    Official Homepage
    Contribute
    Get support
    List of changes

Any news, what is the progress..how can I contract someone to discuss the matter?

Thank you for the code provided.

I saw you set $update_array['us_address'] by result of $this->input->xss_clean($this->input->post('address1'));, definition of which you did not provide.

Anyway I think would be better if you turn general query log to ON, then run problem query, then send us general query log content: I want to see actual query which MySQL server receives. Since version 5.1 general log is dynamic, so you can turn it just for this test. You can redirect it to table also. In this case we are interested in the query which updates us_address.

Dear sir/madam

I received your message, the log file is attached for your information.
Please nore while the General Log was enable I run the program with 2 different entries each 3 times, including:

the 1st entry I inserted "55 George Street" in the us_address field of the  screen, the value didn't update the database record, while it shows that the value has been transfered to the update statement, and the value as content of the us_address was displayed on the screen.

then I changed the program and added this line $us_address="77 Kambah Street"; 
the value updated the database record.

The result is MySql doesn't update the record when the value is picked up from the screen, but when it is moved in the program it updates the record.

Regards
Alex

General Queryt Log File

Attachment: general_log.sql (text/plain), 205.25 KiB.

Dear sir/madam

I received your message, the log file is attached for your information.
Please nore while the General Log was enable I run the program with 2 different entries each 3 times, including:

the 1st entry I inserted "55 George Street" in the us_address field of the  screen, the value didn't update the database record, while it shows that the value has been transfered to the update statement, and the value as content of the us_address was displayed on the screen.

then I changed the program and added this line $us_address="77 Kambah Street"; 
the value updated the database record.

The result is MySql doesn't update the record when the value is picked up from the screen, but when it is moved in the program it updates the record.

Regards
Alex

Thank you for the feedback.

I see 3 identical queries with empty values for all fields, except us_address: "UPDATE `users` SET `us_first_name` = '''', `us_address` = ''55 George street'', `us_address2` = '''', `us_suburb_id` = '''', `us_phn_num` = '''', `us_mobile_num` = '''', `us_fax_num` = '''', `us_state_name` = ''Dubai'', `user_id` = ''0'' WHERE `user_id` = ''0'''". Looks like you set all values, except us_address and us_state_name, to empty fields.

Also is not clear from the description if you used your form only or application.

I recommend you to do following test:

1. Run SELECT * FROM users  WHERE `user_id` = '0', record result. If there is no result here is the explanation: your form sends wrong value for user_id

If there is result, continue:

1. Empty general query log table (make backup if you need existent data) or record time
2. Run update from form
3. Find this update in the general query log. If you had not emptied it and want to send to us, inform us about time after which query was executed.
4. Run update from the general query log using MySQL command line client
5. Run SELECT * FROM users  WHERE `user_id` = '0' and compare with result from previous run.

General Log file

Attachment: general_log.sql (text/plain), 205.25 KiB.

Dear sir,

Thanks for your reply, I copied your answer in order to comment for each part, please note:

"I see 3 identical queries with empty values for all fields, except us_address: "UPDATE `users` SET `us_first_name` = '''', `us_address` = ''55 George street'', `us_address2` = '''', `us_suburb_id` = '''', `us_phn_num` = '''', `us_mobile_num` = '''', `us_fax_num` = '''', `us_state_name` = ''Dubai'', `user_id` = ''0'' WHERE `user_id` = ''0'''". Looks like you set all values, except us_address and us_state_name, to empty fields."

As you may notice, I run the program 3 times with entering "55 George street" in the form. Please ignore other fields, because we are only concern why the database record can't be updated when "55 George Street" enteretd to the us_address on the form?

"Also is not clear from the description if you used your form only or application."

As mentioned I run also 3 times the appliction and eneterd value of "77 Kambah Street" to us_address, it means us_address = "77 Kambah Street"; In this case the database was updated and there was no problem. As I see you missed this part and it was ignored in your comments.

In summary, the database record gets updated when the value is moved in the application to the us_address field, but when the value is enteretd in the form the database record doesn't get updated.

"I recommend you to do following test:"

"1. Run SELECT * FROM users WHERE `user_id` = '0', record result. If there is no result here is the explanation: your form sends wrong value for user_id"

I don't agree with your comment, because if you have a look at the log file for both updates queries show that values are there, including:

1)when the data entered in the form, the query shows us_address = "55 Gerorge Street", user_id="0", and us_state_name = "Dubai".

2)when the data entered in the program, it means us_address="77 Kambah Street";
the query shows us_address = "77 Kambah Street", user_id="0"; and us_state_name = "Dubai", as I noted you didn't consider this part, please search on the "77 Kambah Street" in the log file to find out that the log file for both "55 George street" and "77 Kambah street" address are the same, but the result are different, it means when the address entered in the form it doesn't update the record, but when it moved into us_address field in the application it updates the database record. By the way in both cases teh values are reflected in the query how do you say that "your form sends wrong value for user_id"?

"If there is result, continue:

1. Empty general query log table (make backup if you need existent data) or record time
2. Run update from form
3. Find this update in the general query log. If you had not emptied it and want to send to us, inform us about time after which query was executed.
4. Run update from the general query log using MySQL command line client
5. Run SELECT * FROM users WHERE `user_id` = '0' and compare with result from previous run."

Thanks for your co-operation
Regards
Alex

Thank you for the feedback.

Please send us output of SELECT * FROM users  WHERE `user_id` = '0';

database record after update

Attachment: database-record-after-update.jpg (image/pjpeg, text), 121.91 KiB.

Second Run Log File

Attachment: general_log2.sql (text/plain), 89.00 KiB.

Hi,

I added the query to show the content of the database, but the log file shows only the script of the query and  not the content of the database. However I used the MySql Admin and have the image of the record after update that is attached for your  information.

The main point that I noticed in the attached log file which is the 2nd log file is very interesting that changes whole the concepts of what I thought.  The log file shows the database record gets updated, but the update process is continued for 3 more times without being asked, as a result in the 2nd, 3rd, and forth updates the field in the form "us_address" has been wiped out then  as a result the database record gets updated with a blank vale and then as a result  the database record gets updated with null value. (in this respect, please have look in the log file when the vale of “55 Kambah Street” entered in the us_address of the form.
This matter proves why the database gets updated  when we enter the value in the application to the field in this case entering   “77 George street” in the $us_address, because the 2nd, 3rd, and 4th update gets the value from the $us_address and the value is there without being wiped out.

We need to find out how the codeigniter gets into the loop and it execute 3 times more the update statement without being asked, I checked the application programs I couldn't find any loop, but I suspect that there must be something wrong in the database update query micros/routines that causes this.

Thanks for your co-operation
Alex

Thank you for the feedback.

Last comment confirms source of the problem is in your application code, not in MySQL code. So I am closing this report as "Not a Bug". We are not allowed to help you more here. Please use other resources if you still need it.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

I take the opportunity to thank you for your time, now that the the problem is fixed it is better to explain for anyone who my face the same strange problem.

Actualy the program was not in loop, but there were 3 lines of PHP code that caused MySql create update event as the log file shown, these false update statement balnk out the updated record, these 3 lines are as follows:

<td><div align="center"><?php echo $sell_left_ads_banner;?></div></td>
<td><div align="center"><?php echo $sell_mid_ads_banner;?></div></td>
<td><div align="center"><?php echo $sell_rite_ads_banner;?></div></td>

For me it is a mistery how a php code that I belive has nothing to do with  MySql script to create update statement and blank out the updated record.

I belive this is both PHP and MySql interactive problem, this error needs to be fixed in order other people not to spent such a long time that took me to be solved.

Thanks for your consideration.
Alex