I came across this issue when was developing a site for a client. I have MySQL 5.0.X at my localhost and staging host but client's hosting still uses MySQL 4.0.X.

So here is the solution:

0. Always make backup first.

1. Drop tables "accesslog", "cache" and "watchdog"

(DROP TABLE accesslog, cache, watchdog;)

2. Recreate tables from Drupal mysql dump for v4.0

CREATE TABLE accesslog (
  aid int(10) NOT NULL auto_increment,
  sid varchar(32) NOT NULL default '',
  title varchar(255) default NULL,
  path varchar(255) default NULL,
  url varchar(255) default NULL,
  hostname varchar(128) default NULL,
  uid int(10) unsigned default '0',
  timer int(10) unsigned NOT NULL default '0',
  timestamp int(11) unsigned NOT NULL default '0',
  KEY accesslog_timestamp (timestamp),
  PRIMARY KEY (aid)
);

CREATE TABLE cache (
  cid varchar(255) NOT NULL default '',
  data longblob,
  expire int(11) NOT NULL default '0',
  created int(11) NOT NULL default '0',
  headers text,
  PRIMARY KEY (cid),
  INDEX expire (expire)
);

CREATE TABLE watchdog (
  wid int(5) NOT NULL auto_increment,
  uid int(10) NOT NULL default '0',
  type varchar(16) NOT NULL default '',
  message longtext NOT NULL,
  severity tinyint(3) unsigned NOT NULL default '0',
  link varchar(255) NOT NULL default '',
  location varchar(128) NOT NULL default '',
  referer varchar(128) NOT NULL default '',
  hostname varchar(128) NOT NULL default '',
  timestamp int(11) NOT NULL default '0',
  PRIMARY KEY (wid)
);

3. Make a dump for MySQL 4.0.X

mysqldump -u root -p -h localhost --compatible=mysql40 mydatabase > mydatabase.sql

4. Upload this dump to your MySQL 4.0.X server.

5. Refresh your browser a couple of times. Enjoy!