Member Avatar for riahc3

I insert something simple like

insert into table(pri1,pri2,value) values (1,1,1);
insert into table(pri1,pri2,value) values (1,2,1);

And in MySQL 5.6 they show up as:

pri1 pri2 value
1    1     1
1    2     1

BUT

In MySQL 5.1 I do the same thing and they show up as:

pri1 pri2 value
1    2     1
1    1     1

As in the first row is the last row inserted.

Why is this? Ive seen strange things but this is one of the strangest!

  • 2 Contributors
  • 2 Replies
  • 507 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by riahc3

Dani AI

Generated

Short answer: this is expected. SQL tables have no guaranteed row order unless you ask for one, and different MySQL versions / storage engines / internal indexes can return rows in different orders. To get stable, repeatable results always add an ORDER BY (or order by an explicit auto-increment id / timestamp). (dev.mysql.com)

Why it happened: different servers/storage engines organize and return rows differently. InnoDB stores rows according to the clustered index (usually the PRIMARY KEY) and will even create a hidden clustered index if you have no PK; that can make the physical order follow insertion or primary-key order. MyISAM stores rows in the data file and can append new rows to the end (or reuse holes), so the visible order can change based on deleted rows or concurrent-insert behavior. These implementation details explain why the same plain SELECT showed a different ordering on the 5.1 box versus the 5.6 box. (stackoverflow.com)

Quick, practical troubleshooting and fixes:

  • Inspect the table definition and engine: run the SHOW CREATE TABLE and SHOW TABLE STATUS commands to see ENGINE and keys. (docs.huihoo.com)
SHOW CREATE TABLE your_table;
SHOW TABLE STATUS LIKE 'your_table';
  • If you want deterministic insertion order, add a real ordering column (AUTO_INCREMENT id or a created_at timestamp) and SELECT ... ORDER BY that column:
ALTER TABLE your_table
  ADD COLUMN row_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

SELECT col1, col2 FROM your_table ORDER BY row_id;

Be careful: ALTER TABLE can rebuild and block the table on older servers (5.1). MySQL 5.6+ improved online DDL for some operations, but on older versions you should plan downtime or test on a copy first. (dev.mysql.com)

Tieback: your observation of the “first row being the last inserted” is exactly the sort of behavior that comes from implementation differences; ’s description matches the symptom. The reliable cure is explicit ORDER BY (or an ordered key) — don’t rely on observed order from an unordered SELECT. (dev.mysql.com)

Recommended Answers

All 2 Replies

Member Avatar for Borzoi

It looks as though the data is just being placed at the beginning of the table in MySQL5.1 instead of the end. Unless specified, MySQL will add the inputted data to the end of the table, not the beginning. I don't recall this being different in MySQL 5.1.

How are you calling the information from the database? I'm assuming you're using the same command to call from both like you have done when inserting data?

Member Avatar for riahc3

It looks as though the data is just being placed at the beginning of the table in MySQL5.1 instead of the end. Unless specified, MySQL will add the inputted data to the end of the table, not the beginning. I don't recall this being different in MySQL 5.1. How are you calling the information from the database? I'm assuming you're using the same command to call from both like you have done when inserting data?

Your basic command:

select * from table;

I mean, I dont recall this being any different either. Like I said the code is the same except instead of connecting to one database (running on a 5.6 server) I connect to another (running 5.1)...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.