new rows added using MySQL and QSqlTableModel aren't added at the end

[scot_hansen]

I am writting an application that displays a FUEL_MAPS table from a MySQL database and allows the user to enter additional rows of data using QTableView. During a software test, I added three new rows of data to the table.

The three rows of data were entered correctly into the FUEL_MAPS table. However, when I run a sql query "SELECT * FROM FUEL_MAPS", the first row entered is at the beginning of the querry results. The second row entered is the last row of the querry results and the third row entered is the second entry. The newly entered rows are switching back and forth between the beginning of the query results and the end.

I was expecting that the newly added rows would be at the end of the querry results. I would like to see the newly entered rows be the last entries in the FUEL_MAPS table.

I am using the following software on Windows XP:
1. MySQL 5.1
2. Python 2.7
3. Qt 4.6.2

The FUEL_MAPS table has a column called ID which is auto-incrementing.

The data entry screen has a "Add Row" button to add a new row. Here is the event handler code:

#mdlFuelMaps is the QSqlTableModel
row = self.mdlFuelMaps.rowCount()
print "new code: row = %d" % row
self.mdlFuelMaps.insertRow(row)
index = self.mdlFuelMaps.index(row, MAP_TYPE)
#tblFuelMaps is the QTableView
self.tblFuelMaps.setCurrentIndex(index)
self.tblFuelMaps.edit(index)

The row number is being incremented correctly.

The data entry screen has a "submit" button to insert the data with this event handler code:

submit button event handler
---------------------------
def submitData(self) :

self.mdlFuelMaps.database().transaction()
if self.mdlFuelMaps.submitAll() :
self.mdlFuelMaps.database().commit()
print "submitted the rows"
else :
print "error submitting the rows"

When the submit button is clicked, I always get the print string "submitted the rows".

Finally, I set the mdlFuelMaps edit property using the following statement when generating the entry screen:

self.mdlFuelMaps.setEditStrategy(QSqlTableModel.On ManualSubmit)

I've tried earlier versions of the code where I wasn't calling submitAll and didn't use the OnManualSubmit property but I got the same result. I also tried submitting each newly entered row.

I feel the problem is one of these things:

1. I need to call an additional function
2. This is a "bug" with MySQL

Has anybody else had this issue with MySQL? If you want to see all of code, let me know and I'll be glad to send it.

Thank you.

[ChrisW67]

If you do not specify an ORDER BY clause in your selection SQL then the order that rows are returned from an RDBMS is not defined or guaranteed. Often the retrieval order is governed by the index or indexes used during query execution. For example, a hash-based index will give what appears a random ordering. Other reasons for seemingly random order are that new rows may be inserted into database pages in the space vacated by deleted rows.

If you have an ID that is monotonically increasing then just ORDER BY that column (by using QSqlTableModel::setSort() in this case).