MySQL 4.0.17 data scrambles, not-null fields become null, etc.

ppowell

I'm having the most bizarre problem with PHP/MySQL that I've ever faced and it's an urgent matter (of course) to try to fix it ASAP.

We have one record inserted into a table with 75 columns, whereby half of the columns are not-null, but for some bizarre reason, the fields are null. The rest of the columns that are null are completely scrambled (e.g. "first_name" is in "address2", "fax" is in "url", "url" is in "last_name", etc.)

This just recently occurred and seems to have no history otherwise. It occurred sometime on Saturday when the record was inserted but logs indicate no unusual activity at that time.

Has anyone seen this before and found a way to prevent it from occurring again?

Thanx
Phil

Houdini

Apparently you are listing the fields out of order when you are inserting them, I have done this before when I was only updating a few out of all the fields and the data was all shifted one column over firstname where lastname belongs lastname where email belongs email where hobbies belong etc., then I saw the error in my code.

ppowell

I'm afraid that's not it. Up until Saturday the data was inserting correctly and no change to the code took place. The fields are listed in my INSERT statement in the correct order and reflect that in the data insertions literally until Saturday.

Phil

Houdini

Have you tried a repair table or optomize?

ppowell

Yes, both, to no avail. Nor can I duplicate the error either, it inserts just fine now

Phil

bradgrafelman

Have you touched the code at ALL recently? Check the modified time of the .php file that inserts code.

Are you sure you haven't had this problem occur before? For example, in this thread?

If you haven't done anything to the structure of the MySQL table, perhaps you could post the code that's inserting/manipulating the MySQL data for us to see?

ppowell

The modified date for the one PHP script that only does a SELECT was 6/27. We had data inserted correctly on 6/28 and 6/29, no data on 6/30 and then the scrambled data on 7/1.

The problem that occurred before occurred about the same time that we had made a repair on a few MySQL tables and couldn't duplicate it even after doing a REPAIR TABLE statement afterwards. This time we never did anything; it seemed to do it "on its own" on 7/1 at some unknown time (the exact time in the datetime field was never recorded)

Phil

ppowell

UPDATE

I checked out all the data in the table and discovered that, on the first day of every month, this phenomenon occurs (data scrambles). Any other day it's just fine. What could do that?

Phil

bradgrafelman

Do you have any cron jobs?

ppowell

I'm trying to get the sysadmins to confirm that as I do not have permission to view crontab on the particular machine in question.

dr_bung

Anything useful in the UPDATE log?

ppowell

Absolutely nothing in the UPDATE log for July 1 🙁

Phil

ppowell

Also I had the system admins check crontab, the general and MySQL crontabs have no cron jobs that specifically run on the first date of the month.

I'm totally out of ideas now.

Phil

ppowell

Bumping this forward in the hopes that someone might have a solution to this.

Phil

tsinka

Hi,

please post the table structure (e.g. the CREATE TABLE sql query) and the PHP code that inserts the data.

Thomas

ppowell

-- STUDENT INFO
-- 3/17/2006: NOTE THAT "has_transcript_dir" AND "has_resume_dir" WILL BE A BOOLEAN TO DETERMINE IF THEY ALREADY HAVE THEIR OWN DIRECTORIES
CREATE TABLE /! IF NOT EXISTS / student (
id int not null auto_increment,
primary key (id),
student_first_name varchar(75) not null,
student_mi varchar(75),
student_last_name varchar(75) not null,
email varchar(125) not null,
birth date not null,
was_our_intern char(1) default '0',
is_american_citizen char(1) default '0',
has_disability char(1) default '0',
home_address1 varchar(125) not null,
home_address2 varchar(50),
home_city varchar(125) not null,
home_state varchar(2) not null,
home_zip varchar(15),
home_country varchar(3) default 'us',
home_phone varchar(20) not null,
home_alt_phone varchar(20),
school_name varchar(125) not null,
school_address1 varchar(125),
school_address2 varchar(50),
school_city varchar(125),
school_state varchar(2),
school_zip varchar(15),
school_country varchar(3) default 'us',
school_phone varchar(20),
school_alt_phone varchar(20),
school_location_city varchar(125) not null,
school_location_state varchar(2) not null,
school_location_country varchar(3) default 'us',
student_school_enrollment_status_id int not null,
student_school_enrollment_status_other varchar(75),
student_school_enrollment_type_id int not null,
student_school_enrollment_type_other varchar(75),
school_type_id int not null,
school_type_other varchar(75),
school_year_id int not null,
school_year_other varchar(75),
graduation date not null,
gpa decimal(3,2) not null,
major varchar(125) not null,
minor varchar(125),
student_customer_preference_other varchar(75),
student_ethnicity_interest_other varchar(75),
student_internship_area_other varchar(75),
will_intern_anywhere char(1) default '0',
will_require_housing char(1) default '0',
has_participated_in_prior_internship_programs char(1) default '0',
prior_internship_sponsors varchar(125),
activities text not null,
written_reasons text not null,
resume text,
has_resume_dir char(1) default '0',
uploaded_resume_path text,
howheard_id int not null,
howheard_other varchar(75),
has_completion_reference_letter1 char(1) default '0',
completion_reference_letter1_receipt_date date,
completion_reference_letter1_path text,
has_completion_reference_letter2 char(1) default '0',
completion_reference_letter2_receipt_date date,
completion_reference_letter2_path text,
has_school_transcript char(1) default '0',
school_transcript_date date,
school_transcript_path text,
has_completion_dir char(1) default '0',
has_proof_grad_enrollment char(1) default '0',
has_completed_employment_application char(1) default '0',
application_date datetime,
modification_date datetime,
unique_key varchar(16) not null,
uuid varchar(255) not null,
is_authorized_to_resubmit char(1) default '0'
) /! TYPE=MyISAM /;

		  switch (strtolower($section)) {
		   case 'student':
		    // SAVE STUDENT
		    $junk = @array_remove_element_at($dbTableFieldNameArray[$section], array_search('uuid', $dbTableFieldNameArray[$section]));
		    $sql = 'INSERT /*! LOW_PRIORITY */ INTO student (' . 
			        @join(', ', $dbTableFieldNameArray[$section]) . ', application_date, modification_date, is_authorized_to_resubmit ' .
		                ') VALUES (';
		    @array_walk($dbTableFieldNameArray[$section], create_function('&$a', '$a = "\'" . mysql_real_escape_string($_POST[$a]) . "\'";'), $_POST);
		    $sql .= substr(@join(',', $dbTableFieldNameArray[$section]), 0, strrpos(trim(@join(', ', $dbTableFieldNameArray[$section])), ',')) . ", now(), now(), '0' " .
		                ')';
		    break;

	   default:
	    // DO NOTHING
	    break;
	  }

tsinka

Ok,

I need some more code, especially the part of the script that creates the arrays. On a second thought ... post the complete script as attachment if possible.

Thomas

ppowell

The arrays are completed as static arrays in a static library script, they are not dynamically generated, however, in the script provided they are inserted with proprietary user-entered $_POST form data.

I can't post anymore than that I'm afraid because it's government blah blah blah

Phil

tsinka

Ok,

you wrote that the contents of some columns are null in some of the "not null" columns in rows that are inserted on the first day of each month. Are the contents really "null" or an empty string (or 0 in case of numeric fields or e.g. 0000-00-00 in case of date fields). If you specify columns in a table as not null and don't provide any default values (in the create table query) then MySQL inserts e.g. empty strings in varchar fields, 0 in numeric fields an 0000-00-00 in date fields if they are not part of the insert query.

If fields specified declared as "not null" contain NULL then something is wrong with the database. If that fields contain values like the ones mentioned above then more likely something is wrong with your php scripts.

You wrote that e.g. first_name is in address2 and fax is in the url field an that the date info isn't correct in the corrupted records.

So it looks like the insert query inserts the values somewhat shifted into the table. Please check if all the values are shifted the same number of columns. If yes then maybe the script(s) behaves in a different way at the beginning of each month. I'd suggest to search the script(s) for code that depends on the current date in any way and to check if there are any dependencies regarding the first day of a month.

I know that this is a rather vague suggestion, but (without seeing your code) this would be my first steps in this case.

I hope I didn't explain things too complicated. It's rather difficult for me to write about more complex issues since english isn't my native language.

Thomas

ppowell

Ok, it's the first of the month and it happened again!

Doing some investigating I found out the following items:

1) It isn't being done by manual form submittal after all.. as it turns out by calling the applications via phone to confirm, they weren't even applying during those dates but have applied back between 11/2005 and end of 1/2006.

2) The records seems to "ghost entry" themselves and not by any manual process

3) The only records that have this occurrence occur when they were old data with application_date field records ranging from 11/2005 and end of 1/2006, with a graduation date following onthe first day of a month

4) On the first day of the month, whose date is found as an entry in the "graduation" field of a record, that record that has that graduation field entry - and has been entered between 11/2005 and end of 1/2006, will have a "ghost entry" consisting of scrambled records, every single time.

Phil