Insert rows in MYSQL 5.0

[jkmomaha]

We upgraded from 3.0 to 5.0 for MYSQL, when we upgraded our Freebsd to 6.1, now when I insert a row using basic MYSQL syntax, the row is added, however our application
does not display the new row in reports. When we add a row thru the application, it appears on the report. Our
scripts have'nt changed(PERL). Is there a parm setting that
needs to be checked in MYSQL or in some ini file.
Completely new to UNIX,MYSQL and PERL.
Can anyone help or tell me where to check.
thanks

 

[rac2]

You know the cause lies in the difference between what the application does to add a row and what you do to add a row.

Possibilities to explore would be

You are not adding all of the same values in all of the same columns which the application is adding. Consequently the SELECT query which the application uses to retrieve rows has conditions which do not include the row you INSERTed.

The application is adding rows to other tables as well as the one you are INSERTing, with the same consequence. Note that this would be a second SQL INSERT statement somewhere in the application, not a trigger in the database. A trigger would fire regardless of where the initial INSERT occurs.

 

[jkmomaha]

Some more info;
Using a small table with no indexes or foreign keys.

When i insert a row using the latest backup of our Database
(i extract 1 row from this small table and change the primaryKEY, it adds the row and I can select it with a query in MYSQL, but it does not show up in report.

INSERT INTO WO_REQUESTS VALUES (275,1,'2007-06-29','Medium',
'This is a test for insert into wo orders thru backdoor.','In progress',
'medium',2,
'Cindy Richter','Not started - 07/13/2004','2007-08-15','2007-08-22','Criss II','666','John MacLennan');

mysql> SELECT STATUS, REQUEST_ID, REQUEST_DATE, PERSON, REQUEST FROM WO_REQUESTS WHERE REQUEST_ID = 275 ORDER BY REQUEST_ID;
+-------------+------------+--------------+----------------+---------------------------------------------------------+
| STATUS | REQUEST_ID | REQUEST_DATE | PERSON | REQUEST |
+-------------+------------+--------------+----------------+---------------------------------------------------------+
| In progress | 275 | 2007-06-29 | John MacLennan | This is a test for insert into wo orders thru backdoor. |
+-------------+------------+--------------+----------------+---------------------------------------------------------+
1 row in set (0.00 sec)




However when I insert from a backup PRIOR to the upgrade it
will appear on the application report, this backup has a
different format (1 insert statement for every row).

INSERT INTO WO_REQUESTS VALUES (273,6,'2007-08-01',
'Medium','Investigate methods for E-mail notification when spreadsheet updated',
'In progress',
'medium',2,'Jim Byars','','2007-08-01','0000-00-00',
'Criss II','121','Susan Zuger');


mysql> SELECT STATUS, REQUEST_ID, REQUEST_DATE, PERSON, REQUEST FROM WO_REQUESTS WHERE REQUEST_ID = 273 ORDER BY REQUEST_ID;
+-------------+------------+--------------+-------------+----------------------------------------------------------------------+
| STATUS | REQUEST_ID | REQUEST_DATE | PERSON | REQUEST |
+-------------+------------+--------------+-------------+----------------------------------------------------------------------+
| In progress | 273 | 2007-08-01 | Susan Zuger | Investigate methods for E-mail notification when spreadsheet updated |
+-------------+------------+--------------+-------------+----------------------------------------------------------------------+
1 row in set (0.01 sec)


How can I revert back to the older format (or can I), have tried using different parameters with no success with mysqldump.

 

[rac2]

this backup has a different format

revert back to the older format

What format are you referring to?

 

[jkmomaha]

Thanks for the pointer about other tables,
I realized that the application was using other tables to
display the data, so I loaded our Production tables to
our test system and the data showed up. The person doing the inserts said that it worked last year, however I think
maybe it was another table or it was an update not an insert that they did.
-
About the format, when we use mysqldump to backup our database, it produces only 1 insert statement with all the
values, in the old version there was 1 insert for each row,
which made it easier to manipulate, we have a small database and none really large tables.