We have two mysql sql RDS servers and we are facing an issue which i am not able to resolve. The query that we are using is a simple selection of max date row from the data set. This is working fine on the first server, where as this is not working correctly on the other server even on the same data set.
The query we are using is below:
SELECT `name`,last_update_date FROM
(SELECT * FROM `table1` ORDER BY last_update_date DESC) X GROUP BY `name`;
I am not sure why this is happening. I checked for the global variables on both the servers and found the below variables which are not present in the new server:
binlogging_impossible_mode
innodb_additional_mem_pool_size
innodb_mirrored_log_groups
innodb_use_sys_malloc
simplified_binlog_gtid_recovery
sql_log_bin
storage_engine
thread_concurrency
timed_mutexes
Any help is appreciated.
This is not the correct way to get the max date for each name, and there's no reason to expect consistent results from it. The correct way is:
select name, MAX(last_update_date) as last_update_date
FROM table1
GROUP BY name
The fact that it seems to be working on one server is just coincidence, and it might not last.
If you want to get the entire row that contains the max date, see SQL Select only rows with Max Value on a Column