Keep getting many warning about the folloing MySQL data types
# No Schema type for mysql type char. (Get dozens of these)
# No Schema type for mysql type timestamp. (Get many of these)
# No Schema type for mysql type decimal. (Get some of these)
# No Schema type for mysql type enum. (Get some of these)
# No Schema type for mysql type date. (Get some of these)
# No Schema type for mysql type set. (Get some of these)
# No Schema type for mysql type year. (Get a few of these)
# No Schema type for mysql type mediumblob. (Get a few of these)
# No Schema type for mysql type tinytext. (Get a few of these).

PLUS,
The Schema comparison report shows:

* 217 extra tables
* 22 module with missing tables
* 1 module with mis-matching tables

Note that it IS seeing tables from other applications within the same database (Different table prefixes) which it should not be if it was using the installed database table prefix.

The comparison report indicates (from a basically a fresh install):
Tables in the schema that are not present in the database.
forum

* forum

user

* access
* authmap
* permission
* role
* users_roles

upload

* upload

update

* cache_update

trigger

* trigger_assignments

taxonomy

* term_data
* term_hierarchy
* term_node
* term_relation
* term_synonym
* vocabulary
* vocabulary_node_types

system

* variable
* actions
* actions_aid
* batch
* cache
* cache_form
* cache_page
* cache_menu
* files
* flood
* history
* menu_router
* menu_links
* sessions
* system
* url_alias

statistics

* accesslog

search

* search_dataset
* search_index
* search_total
* search_node_links

profile

* profile_fields
* profile_values

poll

* poll
* poll_choices
* poll_votes

openid

* openid_association

node

* node
* node_access
* node_counter
* node_revisions
* node_type

menu

* menu_custom

locale

* languages
* locales_source
* locales_target

filter

* filters
* filter_formats
* cache_filter

dblog

* watchdog

contact

* contact

comment

* comments
* node_comment_statistics

book

* book

block

* blocks
* blocks_roles
* boxes
* cache_block

sections

* sections_data

And finally, ...
Tables where schema and database are different
*
users
o uid: not in database
o name: not in database
o pass: not in database
o mail: not in database
o mode: not in database
o sort: not in database
o threshold: not in database
o theme: not in database
o signature: not in database
o created: not in database
o access: not in database
o login: not in database
o status: not in database
o timezone: not in database
o language: not in database
o picture: not in database
o init: not in database
o data: not in database
o user_name: unexpected column in database
o user_group: unexpected column in database
o user_passwd: unexpected column in database
o user_pw: unexpected column in database
o ts: unexpected column in database
o user_id: unexpected column in database
o primary key:
declared: array('uid')
actual: array('user_name')
o unique keys name: missing in database
o indexes access: missing in database
o indexes created: missing in database
o unique keys fk_user_id: unexpected (not an error)
o indexes fk_user_group: unexpected (not an error)

I suspect that this is probably due to not looking at the correct users table

CommentFileSizeAuthor
#6 schema_mysql.inc_.patch2.14 KBkwikone
#6 Schema_After_Patch.jpg23.16 KBkwikone
#5 schema_mysql2.inc_.patch1.74 KBkwikone
#3 settings_copy.php_.txt8.73 KBkwikone

Comments

bjaspan’s picture

bjaspan commented

Please attach your settings.php file (remove the password first, of course).

bakr’s picture

bakr commented

PLUS,
The Schema comparison report shows:

* 217 extra tables
* 22 module with missing tables
* 1 module with mis-matching tables

Another bug is that I do have the 22 missing tables, they actually do exist, but I am using a prefix, the Schema Module should pick the Prefix value and find the proper table before reporting as missing.

Another concept problem is the generalization that the "Schema" Module is not exactly tailored for those who are having one database and multiple table namespases,

All in all this tool is great. some work tweaking is required.

kwikone’s picture

kwikone commented
StatusFileSize
new settings_copy.php_.txt8.73 KB

Attached is my settings file. All I did was replace the user and password

kwikone’s picture

kwikone commented

I have found where the problem of schema finding all tables (instead of just the drupal and module tables).
In engines/schema_mysql.inc at line 93 is:

  $sql = ('SELECT * FROM information_schema.COLUMNS '.
	  'WHERE TABLE_SCHEMA="%s" ');
  if (isset($name)) {
    $sql .= 'AND TABLE_NAME = "%s" ';
  }
  $sql .= 'ORDER BY TABLE_NAME, ORDINAL_POSITION';

This means that if not getting a specific table it will get the tables for the entire db.
In order to only get the tables that we want to work with the code would need to be something like...

  $sql = ('SELECT * FROM information_schema.COLUMNS '.
	  'WHERE TABLE_SCHEMA="%s" ');
  if (isset($name)) {
    $sql .= 'AND TABLE_NAME = "%s" ';
  } else {
    $sql .= 'AND TABLE_NAME LIKE "drupal_%" ';
    //I have not checked how to get the table prefix so this needs correcting, my prefix is drupal_
    //and it works hardcoded as I have here (manual sql via phpmyadmin).
    //Note that the trailing % after the prefix is required for the sql to work
  }
  $sql .= 'ORDER BY TABLE_NAME, ORDINAL_POSITION';
kwikone’s picture

kwikone commented
Status: Active » Needs review
StatusFileSize
new schema_mysql2.inc_.patch1.74 KB

Schema prob
Also found at line 136 (in engines/schema_mysql.inc) the following:

  $res = db_query('SELECT * FROM information_schema.STATISTICS '.
    'WHERE TABLE_SCHEMA="%s" '.
    'ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX',
    $database);

This also will select ALL tables in the databse, not just the drupal and module tables
So, to fix this the following is a fix (again, I have not had the time to find out where/how
to get the TABLE_PREFIX)

  $sql = 'SELECT * FROM information_schema.STATISTICS '.
    'WHERE TABLE_SCHEMA="%s" AND TABLE_NAME LIKE "drupal_%" '.
    'ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX';
  $res = db_query($sql, $database);

This then will ONLY select the relevant tables.

And I found the following (in engines/schema_mysql.inc) at line 94

  $sql = ('SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="%s" ');

Again this select ALL tables in the DB. So to Resolve

  $sql = ('SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="%s" WHERE TABLE_NAME LIKE "drupal_%"');

I've also attached a patch (with proper access to the TABLE_PREFIX) which resolves the problem as originally reported. However, once that was resolved something else cropped up, but I will report it in another issue.

kwikone’s picture

kwikone commented
StatusFileSize
new Schema_After_Patch.jpg23.16 KB
new schema_mysql.inc_.patch2.14 KB

Finally!!! It now is working!!! The attached patch (replaces previous patch) now gets the results shown in the image.

kwikone’s picture

kwikone commented

Note that an equivalent patch still needs to be done for the pgsql engine which i did not do since I have no way of testing at this time.

kwikone’s picture

kwikone commented
Priority: Minor » Normal

Still have to do the pgsql equivalent patch

bjaspan’s picture

bjaspan commented
Status: Needs review » Fixed

Fixed in Schema 6.x-1.2.

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.

febbraro’s picture

febbraro commented

I have Schema 6.x 1.3 and I don't have any of the changes from this patch in my file? Was this reverted for some reason?

frankcarey’s picture

frankcarey commented

yeah, I seem to be getting errors not related to tables I'm working with (using the tw and migrate modules), sounds like this could be it?

yesct’s picture

yesct commented

#453002: "SELECT command denied to user" when analyzing table
related?