Avatar of jullialynch
jullialynch

asked on 

correct phpMyadmin mysql script to alter mysql event sql mode (mysql log: Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14). WAMP on vista home premium 32 bit.

Hello,
I am setting up a localhost  on a dell lalptop with vista home premium, 32 bit. The site is a highly modified oscommerce site.I successfully installed WAMP with the following versions:
mysql 5.1.3, apache: 2.2.11 (just noticed this  version number does not show up on the WAMP monitor), PhpMyAdmin:3.1.1, PHP: 5.2.8 .
The site is up and running but not communicating with the server. The MSQL site has a solution that seems promising. It is on http://lists.mysql.com/commits/44927 near the bottom of the page.  The thread is " bk commit into 5.1 tree (davi:1.2566) BUG#35824. However, when I try to copy and paste the script into PhpMyAdmin, I get an error. What should this script be in order to alter the sql_mode?

Thank you for your help.
Julie
I find the following error in Mysql log:

090301 22:43:03  InnoDB: Started; log sequence number 0 46419
090301 22:43:03 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_Z
090301 22:43:03 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
090301 22:43:03 [Note] wampmysqld: ready for connections.
Version: '5.1.30-community-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
The MSQL site has a solution that seems promising. It is on http://lists.mysql.com/commits/44927 near the bottom of the page.  The thread is " bk commit into 5.1 tree (davi:1.2566) BUG#35824. However, when I try to copy and paste the script into PhpMyAdmin, I get an error. What should this script be in order to alter the sql_mode?
diff -Nrup a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
--- a/scripts/mysql_system_tables_fix.sql	2008-02-07 08:47:37 -02:00
+++ b/scripts/mysql_system_tables_fix.sql	2008-04-04 13:46:02 -03:00
@@ -433,7 +433,10 @@ ALTER TABLE db MODIFY Event_priv enum('N
 #
 ALTER TABLE event DROP PRIMARY KEY;
 ALTER TABLE event ADD PRIMARY KEY(db, name);
-ALTER TABLE event ADD sql_mode
+# Add sql_mode column just in case.
+ALTER TABLE event ADD sql_mode set ('NOT_USED') AFTER on_completion;
+# Update list of sql_mode values.
+ALTER TABLE event MODIFY sql_mode
                         set('REAL_AS_FLOAT',
                             'PIPES_AS_CONCAT',
                             'ANSI_QUOTES',

Open in new window

MySQL Server

Avatar of undefined
Last Comment
Umesh

8/22/2022 - Mon