Мысли На Тему

Мысли на тему почему там работает, а там нет...


Вот скрипт создания таблицы:

CREATE TABLE IF NOT EXISTS `wacko_user` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(80NOT NULL DEFAULT '',
  `real_name` varchar(80NOT NULL DEFAULT '',
  `password` varchar(64NOT NULL DEFAULT '',
  `salt` varchar(40NOT NULL DEFAULT '',
  `email` varchar(100NOT NULL DEFAULT '',
  `account_type` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `enabled` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `signup_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `change_password` varchar(64NOT NULL,
  `email_confirm` varchar(64NOT NULL DEFAULT '',
  `session_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `session_expire` int(10) unsigned NOT NULL,
  `last_mark` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `login_count` int(10) unsigned NOT NULL DEFAULT '0',
  `lost_password_request_count` smallint(6) unsigned NOT NULL DEFAULT '0',
  `failed_login_count` smallint(6) unsigned NOT NULL DEFAULT '0',
  `total_pages` int(10) unsigned NOT NULL,
  `total_revisions` int(10) unsigned NOT NULL,
  `total_comments` int(10) unsigned NOT NULL,
  `total_uploads` int(10) unsigned NOT NULL,
  `fingerprint` varchar(40DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `idx_user_name` (`user_name`),
  KEY `idx_account_type` (`account_type`),
  KEY `idx_enabled` (`enabled`),
  KEY `idx_signup_time` (`signup_time`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
­


вот скрипт создания записи пользователя

INSERT INTO wacko_user (user_name, password, salt, email, account_type, signup_time) 
VALUES ('System', '', '', '', '1', '')

­
почему на Denwer ( apache + Mysql 5.5.25 + PHP 5.3.13 ) запись вставляется нормально
­
а на IIS 7.5 + My SQL 5.1.66 + PHP 5.3.12
выдает ошибку: #1364 – Field 'change_password' doesn't have a default value
­
кто из них двоих не прав таки?


WikiAdmin (19.10.2012 06:22)
Does this fix work for you?

`change_password` VARCHAR(64) NOT NULL DEFAULT '',

TriS (19.10.2012 07:09) – Yes, but also must be fixed for table "user"
`session_expire` int(10) unsigned NOT NULL,
`total_pages` int(10) unsigned NOT NULL,
`total_revisions` int(10) unsigned NOT NULL,
`total_comments` int(10) unsigned NOT NULL,
`total_uploads` int(10) unsigned NOT NULL,

and some similar problems in other tables, such as, "page" and "revision"

WikiAdmin (19.10.2012 07:13) I'll post the patch here so you can check it before the commit – guess this is also the case for 5.0.1


TriS (19.10.2012 07:21) – Ok, thanks. I would be happy to test it.

WikiAdmin (19.10.2012 08:00)

# HG changeset patch
# User vendeeglobe
# Date 1350633279 -7200
# Node ID 5f18db21638939a0cd721a230cdff4d60e350111
# Parent  9f9581ed33ac285f283113b8e85c1a3aa83e7719
SQL: CREATE TABLE - added missing default values
 
Many thanks to TriS
 
diff -r 9f9581ed33ac -r 5f18db216389 wacko/setup/database-install.php
--- a/wacko/setup/database-install.php    Mon Aug 27 02:56:04 2012 +0200
+++ b/wacko/setup/database-install.php    Fri Oct 19 09:54:39 2012 +0200
@@ -115,7 +115,7 @@
 # $config['theme'] = 'default';
 
 // user 'system' holds all default pages
-$insert_system                = "INSERT INTO ".$config['table_prefix']."user (user_name, password, salt, email, account_type, signup_time) VALUES ('System', '', '', '', '1', '')";
+$insert_system                = "INSERT INTO ".$config['table_prefix']."user (user_name, password, salt, email, account_type, signup_time) VALUES ('System', '', '', '', '1', NOW())";
 $insert_admin                = "INSERT INTO ".$config['table_prefix']."user (user_name, password, salt, email, signup_time) VALUES ('".$config['admin_name']."', '".$password_encrypted."', '".$salt."', '".$config['admin_email']."', NOW())";
 $insert_admin_setting        = "INSERT INTO ".$config['table_prefix']."user_setting (user_id, theme, lang) VALUES ((SELECT user_id FROM ".$config['table_prefix']."user WHERE user_name = '".$config['admin_name']."' LIMIT 1), '".$config['theme']."', '".$config['language']."')";
 
diff -r 9f9581ed33ac -r 5f18db216389 wacko/setup/database_mysql.php
--- a/wacko/setup/database_mysql.php    Mon Aug 27 02:56:04 2012 +0200
+++ b/wacko/setup/database_mysql.php    Fri Oct 19 09:54:39 2012 +0200
@@ -7,7 +7,7 @@
  */
 
 $pref        = $config['table_prefix'];
-$charset    = 'DEFAULT CHARSET=utf8';
+$charset    = 'DEFAULT CHARSET=UTF8';
 $engine        = 'ENGINE='.$config['database_engine'];
 
 $table_acl = "CREATE TABLE {$pref}acl (".
@@ -18,10 +18,10 @@
                 ") {$engine} COMMENT='' {$charset}";
 
 $table_cache = "CREATE TABLE {$pref}cache (".
-                    "name VARCHAR(32) NOT NULL,".
-                    "method VARCHAR(20) NOT NULL,".
-                    "query VARCHAR(100) NOT NULL,".
-                    "lang VARCHAR(2) NOT NULL,".
+                    "name VARCHAR(32) NOT NULL DEFAULT '',".
+                    "method VARCHAR(20) NOT NULL DEFAULT '',".
+                    "query VARCHAR(100) NOT NULL DEFAULT '',".
+                    "lang VARCHAR(2) NOT NULL DEFAULT '',".
                     "cache_time TIMESTAMP NOT NULL,".
                     "INDEX (name),".
                     "KEY timestamp (cache_time)".
@@ -29,16 +29,16 @@
 
 $table_category = "CREATE TABLE {$pref}category (".
                     "category_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,".
-                    "parent INT(10) UNSIGNED NOT NULL,".
-                    "lang VARCHAR(2) NOT NULL,".
-                    "category VARCHAR(100) NOT NULL,".
+                    "parent INT(10) UNSIGNED NOT NULL DEFAULT 0,".
+                    "lang VARCHAR(2) NOT NULL DEFAULT '',".
+                    "category VARCHAR(100) NOT NULL DEFAULT '',".
                     "PRIMARY KEY (category_id),".
                     "UNIQUE KEY idx_category (lang,category)".
                 ") {$engine} COMMENT='' {$charset}";
 
 $table_category_page = "CREATE TABLE {$pref}category_page (".
-                        "category_id INT(10) unsigned NOT NULL,".
-                        "page_id INT(10) unsigned NOT NULL,".
+                        "category_id INT(10) unsigned NOT NULL  DEFAULT 0,".
+                        "page_id INT(10) unsigned NOT NULL DEFAULT 0,".
                         "UNIQUE KEY idx_pageword (category_id, page_id)".
                     ") {$engine} COMMENT='' {$charset}";
 
@@ -56,7 +56,7 @@
                     "from_page_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
                     "to_page_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
                     "to_tag VARCHAR(250) BINARY NOT NULL DEFAULT '',".
-                    "to_supertag VARCHAR(250) NOT NULL,".
+                    "to_supertag VARCHAR(250) NOT NULL DEFAULT '',".
                     "PRIMARY KEY (link_id),".
                     "KEY from_tag (from_page_id,to_tag(78)),".
                     "KEY idx_from_page_id (from_page_id),".
@@ -66,9 +66,9 @@
 $table_log = "CREATE TABLE {$pref}log (".
                     "log_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,".
                     "log_time TIMESTAMP NOT NULL,".
-                    "level TINYINT(1) UNSIGNED NOT NULL,".
+                    "level TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',".
                     "user_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
-                    "ip VARCHAR(15) NOT NULL,".
+                    "ip VARCHAR(15) NOT NULL DEFAULT '',".
                     "message TEXT NOT NULL,".
                     "PRIMARY KEY (log_id),".
                     "KEY idx_level (level),".
@@ -79,11 +79,11 @@
 
 $table_menu = "CREATE TABLE {$pref}menu (".
                     "menu_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,".
-                    "user_id INT(10) UNSIGNED NOT NULL,".
-                    "page_id INT(10) UNSIGNED NOT NULL,".
-                    "lang VARCHAR(2) NOT NULL,".
-                    "menu_title VARCHAR(100) NOT NULL,".
-                    "menu_position SMALLINT(2) UNSIGNED NOT NULL,".
+                    "user_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
+                    "page_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
+                    "lang VARCHAR(2) NOT NULL DEFAULT '',".
+                    "menu_title VARCHAR(100) NOT NULL DEFAULT '',".
+                    "menu_position SMALLINT(2) UNSIGNED NOT NULL DEFAULT '0',".
                     "PRIMARY KEY (menu_id),".
                     "UNIQUE KEY idx_user_id (user_id,page_id)".
                 ") {$engine} COMMENT='' {$charset}";
@@ -101,15 +101,15 @@
                     "created DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',".
                     "modified DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',".
                     "body MEDIUMTEXT NOT NULL,".
-                    "body_r MEDIUMTEXT NOT NULL,".
-                    "body_toc TEXT NOT NULL,".
+                    "body_r MEDIUMTEXT,".
+                    "body_toc TEXT,".
                     "formatting VARCHAR(20) NOT NULL DEFAULT 'wacko',".
                     "edit_note VARCHAR(100) NOT NULL DEFAULT '',".
                     "minor_edit TINYINT(1) UNSIGNED DEFAULT '0',".
                     "reviewed TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',".
                     "reviewed_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',".
                     "reviewer_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
-                    "ip VARCHAR(15) NOT NULL,".
+                    "ip VARCHAR(15) NOT NULL DEFAULT '',".
                     "latest TINYINT(1) UNSIGNED DEFAULT '1',".
                     "handler VARCHAR(30) NOT NULL DEFAULT 'page',".
                     "comment_on_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
@@ -148,12 +148,12 @@
                 ") {$engine} COMMENT='' {$charset}";
 
 $table_poll = "CREATE TABLE {$pref}poll (".
-                    "poll_id INT(10) UNSIGNED NOT NULL,".
-                    "v_id TINYINT(3) UNSIGNED NOT NULL,".
-                    "text VARCHAR(255) NOT NULL,".
-                    "user_id INT(10) UNSIGNED NOT NULL,".
-                    "plural TINYINT(1) NOT NULL,".
-                    "votes SMALLINT(5) UNSIGNED NOT NULL,".
+                    "poll_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
+                    "v_id TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',".
+                    "text VARCHAR(255) NOT NULL DEFAULT '',".
+                    "user_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
+                    "plural TINYINT(1) NOT NULL DEFAULT '0',".
+                    "votes SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',".
                     "start DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',".
                     "end DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',".
                     "KEY idx_poll_id (poll_id),".
@@ -161,9 +161,9 @@
                 ") {$engine} COMMENT='' {$charset}";
 
 $table_rating = "CREATE TABLE {$pref}rating (".
-                    "page_id INT(10) UNSIGNED NOT NULL,".
-                    "value INT(11) NOT NULL,".
-                    "voters INT(10) UNSIGNED NOT NULL,".
+                    "page_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
+                    "value INT(11) NOT NULL DEFAULT '0',".
+                    "voters INT(10) UNSIGNED NOT NULL DEFAULT '0',".
                     "rating_time TIMESTAMP NOT NULL,".
                     "PRIMARY KEY (page_id),".
                     "KEY idx_voters_rate (voters)".
@@ -191,14 +191,14 @@
                     "modified DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',".
                     "body MEDIUMTEXT NOT NULL,".
                     "body_r MEDIUMTEXT NOT NULL,".
-                    "formatting VARCHAR(20) DEFAULT NULL,".
+                    "formatting VARCHAR(20) DEFAULT NULL DEFAULT '',".
                     "edit_note VARCHAR(100) NOT NULL DEFAULT '',".
                     "minor_edit TINYINT(1) UNSIGNED DEFAULT '0',".
                     "reviewed TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',".
                     "reviewed_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',".
                     "reviewer_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
                     "latest TINYINT(1) UNSIGNED DEFAULT '0',".
-                    "ip VARCHAR(15) NOT NULL,".
+                    "ip VARCHAR(15) NOT NULL DEFAULT '',".
                     "handler VARCHAR(30) NOT NULL DEFAULT 'page',".
                     "comment_on_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
                     "lang VARCHAR(2) NOT NULL DEFAULT '',".
@@ -219,16 +219,16 @@
 /*$table_session = "CREATE TABLE {$pref}session (".
                     "session_id VARCHAR( 40 ) DEFAULT '0' NOT NULL ,".
                     "ip_address VARCHAR( 16 ) DEFAULT '0' NOT NULL ,".
-                    "user_agent VARCHAR( 50 ) NOT NULL ,".
-                    "last_activity INT( 10 ) unsigned DEFAULT 0 NOT NULL ,".
-                    "user_data text NOT NULL ,".
+                    "user_agent VARCHAR( 50 ) DEFAULT '' NOT NULL ,".
+                    "last_activity INT( 10 ) unsigned DEFAULT '0' NOT NULL ,".
+                    "user_data text NOT NULL,".
                     "PRIMARY KEY ( session_id )".
                 ") {$engine} COMMENT='' {$charset}";*/
 
 $table_tag = "CREATE TABLE {$pref}tag (".
                     "tag_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,".
                     "user_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
-                    "lang VARCHAR(2) NOT NULL,".
+                    "lang VARCHAR(2) NOT NULL DEFAULT '',".
                     "tag_name VARCHAR(100) NOT NULL DEFAULT '',".
                     "date_created DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',".
                     "date_updated DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',".
@@ -250,7 +250,7 @@
                     "page_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
                     "user_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
                     "file_name VARCHAR(250) NOT NULL DEFAULT '',".
-                    "lang VARCHAR(2) NOT NULL,".
+                    "lang VARCHAR(2) NOT NULL DEFAULT '',".
                     "description VARCHAR(250) NOT NULL DEFAULT '',".
                     "uploaded_dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',".
                     "file_size INT(10) UNSIGNED NOT NULL DEFAULT '0',".
@@ -275,18 +275,18 @@
                     "account_type TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',".
                     "enabled TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',".
                     "signup_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',".
-                    "change_password VARCHAR(64) NOT NULL,".
+                    "change_password VARCHAR(64) NOT NULL DEFAULT '',".
                     "email_confirm VARCHAR(64) NOT NULL DEFAULT '',".
                     "session_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',".
-                    "session_expire INT(10) UNSIGNED NOT NULL,".
+                    "session_expire INT(10) UNSIGNED NOT NULL DEFAULT '99999',".
                     "last_mark DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',".
                     "login_count INT(10) UNSIGNED NOT NULL DEFAULT '0',".
                     "lost_password_request_count SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',".
                     "failed_login_count SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',".
-                    "total_pages INT(10) UNSIGNED NOT NULL,".
-                    "total_revisions INT(10) UNSIGNED NOT NULL,".
-                    "total_comments INT(10) UNSIGNED NOT NULL,".
-                    "total_uploads INT(10) UNSIGNED NOT NULL,".
+                    "total_pages INT(10) UNSIGNED NOT NULL DEFAULT '0',".
+                    "total_revisions INT(10) UNSIGNED NOT NULL DEFAULT '0',".
+                    "total_comments INT(10) UNSIGNED NOT NULL DEFAULT '0',".
+                    "total_uploads INT(10) UNSIGNED NOT NULL DEFAULT '0',".
                     "fingerprint VARCHAR(40),".
                     "PRIMARY KEY (user_id),".
                     "UNIQUE KEY idx_user_name (user_name),".
@@ -297,9 +297,9 @@
 
 $table_user_setting = "CREATE TABLE {$pref}user_setting (".
                     "setting_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,".
-                    "user_id INT(10) UNSIGNED NOT NULL,".
-                    "theme VARCHAR(20) DEFAULT NULL,".
-                    "lang VARCHAR(2) DEFAULT NULL,".
+                    "user_id INT(10) UNSIGNED NOT NULL DEFAULT '0',".
+                    "theme VARCHAR(20) DEFAULT NULL DEFAULT '',".
+                    "lang VARCHAR(2) DEFAULT NULL DEFAULT '',".
                     "changes_count INT(10) UNSIGNED NOT NULL DEFAULT '50',".
                     "revisions_count INT(10) UNSIGNED NOT NULL DEFAULT '20',".
                     "dont_redirect TINYINT(1) UNSIGNED DEFAULT NULL,".
@@ -326,20 +326,20 @@
 
 $table_usergroup = "CREATE TABLE {$pref}usergroup (".
                     "group_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,".
-                    "group_name VARCHAR(100) NOT NULL,".
-                    "description VARCHAR(255) NOT NULL,".
+                    "group_name VARCHAR(100) NOT NULL DEFAULT '',".
+                    "description VARCHAR(255) NOT NULL DEFAULT '',".
                     "moderator INT(10) UNSIGNED NOT NULL DEFAULT '0',".
                     "created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL,".
-                    "is_system TINYINT(1) UNSIGNED NOT NULL,".
-                    "open TINYINT(1) UNSIGNED NOT NULL,".
-                    "active TINYINT(1) UNSIGNED NOT NULL,".
+                    "is_system TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',".
+                    "open TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',".
+                    "active TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',".
                     "PRIMARY KEY (group_id),".
                     "UNIQUE KEY idx_name (group_name)".
                 ") {$engine} COMMENT='' {$charset}";
 
 $table_usergroup_member = "CREATE TABLE {$pref}usergroup_member (".
-                    "group_id INTEGER(10) UNSIGNED NOT NULL,".
-                    "user_id INTEGER(10) UNSIGNED NOT NULL,".
+                    "group_id INTEGER(10) UNSIGNED NOT NULL DEFAULT '0',".
+                    "user_id INTEGER(10) UNSIGNED NOT NULL DEFAULT '0',".
                     "UNIQUE KEY idx_group_id (group_id, user_id)".
                 ") {$engine} COMMENT='' {$charset}";


TriS (20.10.2012 03:46) – Changes worked for installation. But do not work for use.

  1. Do not work with "Edit Page".
    1. Error after save editing:
      Query failed:
      
      ==============================
      UPDATE wacko_page SET comment_on_id = '0', modified = NOW(), created = '2012-10-20 14:11:30', owner_id = '1', user_id = '2', latest = '2', description = '', supertag = 'glavnajastranica', body = '((file:wacko_logo.png WackoWiki))\n**Добро пожаловать в волшебный мир ((WackoWiki:Doc/Russian WackoWiki)).**\n\nНажмите \"Правка\" внизу страницы, чтобы изменить её (между прочим, вы можете просто совершить двойной щелчок мышкой -- это приведёт к тому же результату).\n\nДокументация по ~WackoWiki доступна на WackoWiki:Doc/Russian.\n\nПолезные страницы: ((WackoWiki:Doc/Russian/WackoSintaksis Форматирование)), ((Поиск)).\n\ntestedit', body_r = '
      file:wacko_logo.png ==WackoWiki
      \nДобро пожаловать в волшебный мир WackoWiki:Doc/Russian ==WackoWiki.
      
      
      
      \nНажмите \"Правка\" внизу страницы, чтобы изменить её (между прочим, вы можете просто совершить двойной щелчок мышкой — это приведёт к тому же результату).
      
      
      
      \nДокументация по WackoWiki доступна на WackoWiki:Doc/Russian ==.
      
      
      
      \nПолезные страницы: WackoWiki:Doc/Russian/WackoSintaksis ==Форматирование, Поиск ==Поиск.
      
      
      
      \ntestedit
      
      ', body_toc = '', edit_note = '', minor_edit = '', reviewed = '', reviewed_time = NOW(), reviewer_id = '2', title = '' WHERE tag = 'ГлавнаяСтраница' LIMIT 1
      ==============================
      
      Error 1366: Incorrect integer value: '' for column 'minor_edit' at row 1

      Note that the field "reviewed" as well as "minor_edit" in the request is equal to ''.
    2. Also, perhaps the problem table "wacko_revision". Field "body_r" may be null in table `wacko_revision` (when this field null in page). Run code after installation fix problem:

ALTER TABLE `wacko_revision` CHANGE `body_r` `body_r` MEDIUMTEXT
CHARACTER SET utf8 COLLATE utf8_general_ci NULL

Read comment (1 comment)