[thelist] mSQL data lose/NULL Records

[Steven Stalzer]

While we seem to be talking about SQL issues, here is a tough one I've been
dealing with.

We have a mSQL database which is populated with information about web activity
on our website using the msql Perl module. We've had the system set up to record
activity for a few weeks now. We have started to notice that NULL records are
appearing in various places in the tables (i.e. all fields for the record are
NULL), and when this happens, it seems to be clobbering existing records. There
are two things which are weird about this: first of all, we never create records
under any circumstance in which all fields are null; secondly, the tables are
usually defined so that at least one of the fields is set to NOT NULL, so even
if we wanted to put NULLS in there, we wouldn't be able to. Here's a bit of a
dump so you can see what I mean (notice that the records for cookie_id, where
cookie_id >= 10017 and <=10054 are gone - they were THERE on a dump just 3 days
ago):

CREATE TABLE User_ID (
  cookie_id INT NOT NULL,
  last_session_ts INT,
  last_srch_req INT,
  last_merch_id TEXT(12),
  first_session_ts INT,
  first_referral INT,
  last_ipaddress CHAR(15),
  reserved01 INT,
  reserved02 INT,
  reserved03 INT,
  reserved04 INT,
  reserved05 INT,
  reserved06 INT,
  reserved07 INT
) \g

CREATE UNIQUE  INDEX u_idx ON User_ID (
 cookie_id
) \g

CREATE SEQUENCE ON User_ID STEP 1 VALUE 13344 \g


#
# Dumping data for table 'User_ID'
#

###### SNIP #######
INSERT INTO User_ID  VALUES
(10015,2386,0,'',170,16,'216.67.67.70',NULL,NULL,NULL,NULL,NULL,NULL,NULL)\g
INSERT INTO User_ID  VALUES
(10016,171,0,'papa',171,22,'199.173.8.182',NULL,NULL,NULL,NULL,NULL,NULL,NULL)\g
INSERT INTO User_ID  VALUES
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)\g
INSERT INTO User_ID  VALUES
(10055,270,0,'',270,14,'207.162.143.51',NULL,NULL,NULL,NULL,NULL,NULL,NULL)\g
INSERT INTO User_ID  VALUES
(10056,272,0,'',271,0,'24.91.194.72',NULL,NULL,NULL,NULL,NULL,NULL,NULL)\g
###### SNIP #######

It seems impossible that we are somehow inserting those NULLS, but if anyone
knows of a circumstance in which NULLS can in fact be inserted into fields
defined as NOT NULL, I'd be all ears. It looks to me like the database is
getting corrupted by some other force. We are hosting our site and database
virtually (UNIX), and I have yet to be able to convince anyone at their tech
support center that in fact this is their problem, rather than ours. Anyone have
any insight or experience with this?

Many thanks.
--
Steven Stalzer
www.townvalues.com
New Media Creations, Inc.