MySQL Bugs: #46118: SYSDATE() and NOW() return an innacurate time through C API

Bug #46118	SYSDATE() and NOW() return an innacurate time through C API
Submitted:	10 Jul 2009 17:38	Modified:	10 Aug 2009 22:03
Reporter:	Jon Vance	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S2 (Serious)
Version:	5.0.77	OS:	Windows (XP Pro)
Assigned to:		CPU Architecture:	Any
Tags:	NOW(), SYSDATE()

Description:
When performing any query that contains the NOW or SYSDATE function through the C API on a remote server, a value that is consistently 45 seconds in the future (according to the server) is used.  I have tried the test program provided on two computers, one a Windows XP Pro desktop whose clock is synchronized with the server, and one a Vista laptop whose clock is not synchronized.  Both return the exact same results.

How to repeat:
compile and run the following program, and simultaneously run a SELECT NOW() query from the command line on the server.

#include <windows.h>
#include <mysql.h>

int main()
{
  st_mysql* handle = mysql_init(NULL);

  mysql_real_connect(handle, "server.com", "user", "pw", "db", 0, NULL, CLIENT_MULTI_RESULTS);

  mysql_query(handle, "SELECT NOW()");

  MYSQL_RES* res = mysql_use_result(handle);
  MYSQL_ROW row = mysql_fetch_row(res);

  MessageBox(NULL, row[0], "NOW", MB_OK);

  return 0;
}

Thank you for the report.

Do you get same offset if connect to remote server using mysql command line client? Also, please, indicate accurate minor version of server and client libraries you are using.

Thank you for the feedback.

Please use function mysql_get_client_version() (http://dev.mysql.com/doc/refman/5.0/en/mysql-get-client-version.html)

Server version: 5.0.77
Client version: 60000

Thank you for the feedback.

Do you get same offset if connect to remote server using mysql command line client?

Sorry, I missed that question :)  No, the command line client produces no offset.

C api client and command line

Attachment: now.png (image/png, text), 61.33 KiB.

Thank you for the feedback. I am not quite sure if I understood the bug which you are reporting. I created a C client as below:

#include <my_global.h>
#include <m_string.h>
#include <mysql.h>

#define DB_HOST			"localhost" 
#define DB_USER			"root"
#define DB_PASSW		   ""
#define DB_NAME			"test"
#define DB_PORT			3500
#define DB_UNIX_SOCKET	NULL

#define SELECT_QUERY "SELECT NOW()"

int
main( void )
{
  MYSQL mysql;
  MYSQL_RES *result;
 
  mysql_init( &mysql );
  
   
  if ( !mysql_real_connect( &mysql,DB_HOST,DB_USER,DB_PASSW,DB_NAME,DB_PORT,DB_UNIX_SOCKET,CLIENT_MULTI_RESULTS ) )
  {
    printf("Error: %s\n",mysql_error(&mysql));
    return 0;
  }
  else
    printf("Server: %s Client: %s\n\n",mysql_get_server_info(&mysql),mysql_get_client_info());

    if (mysql_query( &mysql, SELECT_QUERY) )
  {
    printf("Error (query): %s\n", mysql_error( &mysql ));
    mysql_close( &mysql );
    return 0;
  }

  result = mysql_use_result(&mysql);
  MYSQL_ROW row = mysql_fetch_row(result);

  printf("now: %s\n", row[0]);
  
  mysql_free_result(result);
  mysql_close(&mysql);
  return 1;

}

Then I launched the above client and the command line almost at the same time how showed in the prior attached picture. I used the mysql client tool -e option because the connection takes time so I think isn't a valid test to connect first the command line and then query. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".