Error Creating Tables

Help
DaBoomer
2007-03-28
2013-04-02
  • DaBoomer
    DaBoomer
    2007-03-28

    WinXPsp2 & Win2K
    Apache2
    PHP 5.12 & 5.20
    MySQL 5.0,5.027,5.037

    I cannot get past this step at install, no matter what I try. I am trying on 2 different computers with various installs of php and mysql, trying to get it to work.  MySQL has the schema and user, but the page continually comes back "Error Creating Tables".  I even edited the Step1.php file and removed the section to removes tables if error, and it does create tables. Eight of them to be exact.  I have made this run on Suse Linux, but am so far unable to make it run on a windows box.
    Any help or ideas would be appreciated

     
    • Hi,

      Is this a clean installation of CoMoblog 1.1 that you are doing ?

      There should be at least 10 tables at least from memory, which suggests that something is going wrong with the table creation. Can you post a 'show tables' so I can see what tables are being created ?

      Regards,
      Mark.

       
      • DaBoomer
        DaBoomer
        2007-03-30

        I am doing a clean, new install.
        I am not much of a command line user in mysql yet -- "limited experience".

        using Mysql Administrator, I show it creates 8 tables with the error function removed from step 1
        comoblog_comments               Data Length 0b     Index Length 1k
        comoblog_images                 Data Length 0b     Index Length 1k
        comoblog_modules                Data Length 0b     Index Length 1k
        comoblog_posts                  Data Length 0b     Index Length 1k
        comoblog_preferences            Data Length 0b     Index Length 1k
        comoblog_preferences_topics     Data Length 1.9k   Index Length 3k
        comoblog_topics                 Data Length 0b     Index Length 1k
        comoblog_trackback_pings        Data Length 0b     Index Length 1k

         
        • Well, for a base 1.1 install that is all of them. I'm probably going to have to give you a debug file to work out what is happening - perhaps it's something different with your MySQL installation. If I give you a replacement php file for one of the steps will you be able to copy it to your server and try again ?

           
          • DaBoomer
            DaBoomer
            2007-04-03

            I am the Big Dog, when it comes to deciding what to do with the computers here at our facility, so I can do anything you need me to.  Just to fill you in a little. We are a non-profit nursing home in Iowa and we are using your program (easymoblog) a little differently then you had intended.  We are actually using it like a bulleting board for our employees. We cannot afford user accounts for all our staff, yet the need existed to be able to distribute information effectively and simply.  I setup easymoblog to check an email address every 15 minutes and modified the web page to automatically refresh every 5 minutes. I then created a batch file on a windows box to open the webpage in IE in kiosk(full screen) mode. We mounted a flat screen on the wall next to where the employees punch in and now, when ever management has information they need all staff to get, they simply send an email to the right address.
            The reason I am working now with Comoblog, is because we have an Assisted Living facility, that wants the same thing for their staff. I thought I would let the blog run locally on the computer that would display the page.  Since I first reported the problem to you, I RETHUNK the idea and came up with a different solution.  I just created a seperate easymoblog installation and database and email address, on the same machine I was using (Suse 10), and have the other facility hit that webpage. It works fine.
            There is no pressure to make Comoblog install anymore, however I would be more then happy to test and work with you on different installation setups to fix the installation problem in windows.  The reason I couldn't get a Dos Shell for mysql before was while i was attempting to figure out a way around the install issue, I had reinstalled mysql with ascii as default.  I have since change mysql back to the latin default.

             
            • While I can't reproduce it I would be keen to find out what it is if you don't mind giving me a hand.

              You can download a replacement _install\step_1.php file from the URL below. If you can replace the step_1.php file you have with it and rerun the installation then it should tell you what the SQL error was.

              http://test.serialmonkey.com/sqldebug.zip

               
              • DaBoomer
                DaBoomer
                2007-04-12

                Well, I replaced the file. I get the Error Creating Tables page, but I receive no error information.

                MySQL 5.0 Manual discusses strict mode in section 5.2.6

                5.2.6. SQL Modes
                The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients. This capability enables each application to tailor the server's operating mode to its own requirements.

                For answers to some questions that are often asked about server SQL modes in MySQL, see Section A.3, “MySQL 5.0 FAQ — Server SQL Mode”.

                Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.

                You can set the default SQL mode by starting mysqld with the --sql-mode="modes" option. modes is a list of different modes separated by comma (‘,’) characters. The default value is empty (no modes set). The modes value also can be empty (--sql-mode="") if you want to clear it explicitly.

                You can change the SQL mode at runtime by using a SET [GLOBAL|SESSION] sql_mode='modes' statement to set the sql_mode system value. Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time.

                You can retrieve the current global or session sql_mode value with the following statements:

                SELECT @@global.sql_mode;
                SELECT @@session.sql_mode;

                The most important sql_mode values are probably these:

                ANSI

                Change syntax and behavior to be more conformant to standard SQL.

                STRICT_TRANS_TABLES

                If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More detail is given later in this section. (Implemented in MySQL 5.0.2)

                TRADITIONAL

                Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. Note: The INSERT/UPDATE aborts as soon as the error is noticed. This may not be what you want if you are using a non-transactional storage engine, because data changes made prior to the error are not be rolled back, resulting in a “partially done” update. (Added in MySQL 5.0.2)

                When this manual refers to “strict mode,” it means a mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled.

                The following list describes all supported modes:

                ALLOW_INVALID_DATES

                Don't do full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This is very convenient for Web applications where you obtain year, month, and day in three different fields and you want to store exactly what the user inserted (without date validation). This mode applies to DATE and DATETIME columns. It does not apply TIMESTAMP columns, which always require a valid date.

                This mode is implemented in MySQL 5.0.2. Before 5.0.2, this was the default MySQL date-handling mode. As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To allow such dates, enable ALLOW_INVALID_DATES.

                ANSI_QUOTES

                Treat ‘"’ as an identifier quote character (like the ‘`’ quote character) and not as a string quote character. You can still use ‘`’ to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotes to quote literal strings, because it is interpreted as an identifier.

                ERROR_FOR_DIVISION_BY_ZERO

                Produce an error in strict mode (otherwise a warning) when a division by zero (or MOD(X,0)) occurs during an INSERT or UPDATE. If this mode is not enabled, MySQL instead returns NULL for divisions by zero. For INSERT IGNORE or UPDATE IGNORE, MySQL generates a warning for divisions by zero, but the result of the operation is NULL. (Implemented in MySQL 5.0.2)

                HIGH_NOT_PRECEDENCE

                From MySQL 5.0.2 on, the precedence of the NOT operator is such that expressions such as NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c). Before MySQL 5.0.2, the expression is parsed as (NOT a) BETWEEN b AND c. The old higher-precedence behavior can be obtained by enabling the HIGH_NOT_PRECEDENCE SQL mode. (Added in MySQL 5.0.2)

                mysql> SET sql_mode = '';
                mysql> SELECT NOT 1 BETWEEN -5 AND 5;
                        -> 0
                mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
                mysql> SELECT NOT 1 BETWEEN -5 AND 5;
                        -> 1

                IGNORE_SPACE

                Allow spaces between a function name and the ‘(’ character. This causes built-in function names to be treated as reserved words. As a result, identifiers that are the same as function names must be quoted as described in Section 9.2, “Database, Table, Index, Column, and Alias Names”. For example, because there is a COUNT() function, the use of count as a table name in the following statement causes an error:

                mysql> CREATE TABLE count (i INT);
                ERROR 1064 (42000): You have an error in your SQL syntax

                The table name should be quoted:

                mysql> CREATE TABLE `count` (i INT);
                Query OK, 0 rows affected (0.00 sec)

                The IGNORE_SPACE SQL mode applies to built-in functions, not to user-defined functions or stored functions. It is always allowable to have spaces after a UDF or stored function name, regardless of whether IGNORE_SPACE is enabled.

                For further discussion of IGNORE_SPACE, see Section 9.2.3, “Function Name Parsing and Resolution”.

                NO_AUTO_CREATE_USER

                Prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless a non-empty password also is specified. (Added in MySQL 5.0.2)

                NO_AUTO_VALUE_ON_ZERO

                NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.

                This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. mysqldump now automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.

                NO_BACKSLASH_ESCAPES

                Disable the use of the backslash character (‘\’) as an escape character within strings. With this mode enabled, backslash becomes an ordinary character like any other. (Implemented in MySQL 5.0.1)

                NO_DIR_IN_CREATE

                When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on slave replication servers.

                NO_ENGINE_SUBSTITUTION

                Prevent automatic substitution of the default storage engine when a statement such as CREATE TABLE specifies a storage engine that is disabled or not compiled in. An error occurs instead. (Implemented in MySQL 5.0.8)

                NO_FIELD_OPTIONS

                Do not print MySQL-specific column options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

                NO_KEY_OPTIONS

                Do not print MySQL-specific index options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

                NO_TABLE_OPTIONS

                Do not print MySQL-specific table options (such as ENGINE) in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

                NO_UNSIGNED_SUBTRACTION

                In integer subtraction operations, do not mark the result as UNSIGNED if one of the operands is unsigned. In other words, the result of a subtraction is always signed whenever this mode is in effect, even if one of the operands is unsigned. For example, compare the type of column c2 in table t1 with that of column c2 in table t2:

                mysql> SET SQL_MODE='';
                mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
                mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;
                mysql> DESCRIBE t1;
                +-------+---------------------+------+-----+---------+-------+
                | Field | Type                | Null | Key | Default | Extra |
                +-------+---------------------+------+-----+---------+-------+
                | c2    | bigint(21) unsigned |      |     | 0       |       |
                +-------+---------------------+------+-----+---------+-------+

                mysql> SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
                mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;
                mysql> DESCRIBE t2;
                +-------+------------+------+-----+---------+-------+
                | Field | Type       | Null | Key | Default | Extra |
                +-------+------------+------+-----+---------+-------+
                | c2    | bigint(21) |      |     | 0       |       |
                +-------+------------+------+-----+---------+-------+

                Note that this means that BIGINT UNSIGNED is not 100% usable in all contexts. See Section 12.8, “Cast Functions and Operators”.

                mysql> SET SQL_MODE = '';
                mysql> SELECT CAST(0 AS UNSIGNED) - 1;
                +-------------------------+
                | CAST(0 AS UNSIGNED) - 1 |
                +-------------------------+
                |    18446744073709551615 |
                +-------------------------+

                mysql> SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';
                mysql> SELECT CAST(0 AS UNSIGNED) - 1;
                +-------------------------+
                | CAST(0 AS UNSIGNED) - 1 |
                +-------------------------+
                |                      -1 |
                +-------------------------+

                NO_ZERO_DATE

                In strict mode, don't allow '0000-00-00' as a valid date. You can still insert zero dates with the IGNORE option. When not in strict mode, the date is accepted but a warning is generated. (Added in MySQL 5.0.2)

                NO_ZERO_IN_DATE

                In strict mode, don't accept dates where the month or day part is 0. If used with the IGNORE option, MySQL inserts a '0000-00-00' date for any such date. When not in strict mode, the date is accepted but a warning is generated. (Added in MySQL 5.0.2)

                ONLY_FULL_GROUP_BY

                Do not allow queries for which the SELECT list refers to non-aggregated columns that are not named in the GROUP BY clause. The following query is invalid with this mode enabled because address is not named in the GROUP BY clause:

                SELECT name, address, MAX(age) FROM t GROUP BY name;

                As of MySQL 5.0.23, this mode also restricts references to non-aggregated columns in the HAVING clause that are not named in the GROUP BY clause.

                PIPES_AS_CONCAT

                Treat || as a string concatenation operator (same as CONCAT()) rather than as a synonym for OR.

                REAL_AS_FLOAT

                Treat REAL as a synonym for FLOAT. By default, MySQL treats REAL as a synonym for DOUBLE.

                STRICT_ALL_TABLES

                Enable strict mode for all storage engines. Invalid data values are rejected. Additional detail follows. (Added in MySQL 5.0.2)

                STRICT_TRANS_TABLES

                Enable strict mode for transactional storage engines, and when possible for non-transactional storage engines. Additional details follow. (Implemented in MySQL 5.0.2)

                Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a column that has no explicit DEFAULT clause in its definition.

                For transactional tables, an error occurs for invalid or missing values in a statement when either of the STRICT_ALL_TABLES or STRICT_TRANS_TABLES modes are enabled. The statement is aborted and rolled back.

                For non-transactional tables, the behavior is the same for either mode, if the bad value occurs in the first row to be inserted or updated. The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict option is enabled:

                For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, in this case, the earlier rows still have been inserted or updated. This means that you might get a partial update, which might not be what you want. To avoid this, it's best to use single-row statements because these can be aborted without changing the table.

                For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and insert the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. Implicit defaults are described in Section 11.1.4, “Data Type Default Values”.

                Strict mode disallows invalid date values such as '2004-04-31'. It does not disallow dates with zero parts such as '2004-04-00' or “zero” dates. To disallow these as well, enable the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes in addition to strict mode.

                If you are not using strict mode (that is, neither STRICT_TRANS_TABLES nor STRICT_ALL_TABLES is enabled), MySQL inserts adjusted values for invalid or missing values and produces warnings. In strict mode, you can produce this behavior by using INSERT IGNORE or UPDATE IGNORE. See Section 13.5.4.27, “SHOW WARNINGS Syntax”.

                The following special modes are provided as shorthand for combinations of mode values from the preceding list. All are available in MySQL 5.0 beginning with version 5.0.0, except for TRADITIONAL, which was implemented in MySQL 5.0.2.

                The descriptions include all mode values that are available in the most recent version of MySQL. For older versions, a combination mode does not include individual mode values that are not available except in newer versions.

                ANSI

                Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE. Before MySQL 5.0.3, ANSI also includes ONLY_FULL_GROUP_BY. See Section 1.9.3, “Running MySQL in ANSI Mode”.

                DB2

                Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

                MAXDB

                Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

                MSSQL

                Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

                MYSQL323

                Equivalent to NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE.

                MYSQL40

                Equivalent to NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE.

                ORACLE

                Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

                POSTGRESQL

                Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

                TRADITIONAL

                Equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER.

                 
                • Thanks. I won't waste anymore of your time on this. What I'll do is raise a bug in SF to remind me to investigate and I'll setup a strict MySql server myself sometime to dig deeper.

                  Thanks for your help.

                   
    • Ricky
      Ricky
      2007-04-05

      I tried to get them to do something similar at the school I work at.  It would be easy to upload lesson plans and assignments and have the students be able to check them, BUT our network admin no likey me (thinks I'm a haxor because I use firefox) Keep up the good work mark!

      Ricky
      www.cakevideo.com/blog

       
      • DaBoomer
        DaBoomer
        2007-04-10

        Well, I restarted with a fresh load of apache2.2.4, mysql 5.0.37 & PHP 5.2.1
        It worked.... So that really bugged me.  I went back through my installs trying to figure out what i done differently this time than all the other times.  I went back to a PC where it was not working and began reinstalling PHP with a couple different extensions. This wasnt it. So, then I removed and re-installed MySQL. I installed with one option being different.  When you install MySQL, the recommended setting is "STRICT MODE".  I installed MySQL without strict mode and that fixed the problem.  Not sure what strict mode does, I will have to research it!  Do you still want me to try the debug file on a strict mode install of mysql?

         
        • Sure, because I have no idea what "STRICT MODE" is either, and if it's going to cause problems I want to try and code a check into the installer to warn people.

          Good work ;-)