TW doesn't seem to recognize primary key

Are you sure the MySQL table has that field defined as the primary key? It sounds like you're talking about the input data having a primary key, but you need to actually define the primary key index in MySQL for the field to be recognized as the primary key.

I don't know if this is related, but I've also noticed that the module exhibits this behaviour if the table has zero rows in it. Here's a random example from the project I'm trying to import:

CREATE TABLE `cms_bbdisallow` (
  `disallow_id` mediumint(8) unsigned NOT NULL auto_increment,
  `disallow_username` varchar(25) default NULL,
  PRIMARY KEY  (`disallow_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

There are no rows in the table (as evidenced by AUTO_INCREMENT=1). And as you can see, it has a single-column primary key. Yet, when I go to analyze it, I get:

cms_bbdisallow has no primary key defined. A single-column primary key is necessary to use this table as the base table for a view.

Digging further, this appears to be by design, since all the logic for building the $pks array and such happens within this block:

    if (($flag == TW_COLS_ALL) ||
       (($flag == TW_COLS_EMPTY) && $row->isempty) ||
       (($flag == TW_COLS_NONEMPTY) && !$row->isempty)) {
...
    }

I'm not sure why it's desirable to not be able to analyze a table with no data in it (it might be nice to create views ahead of time for tables that will eventually contain data), but at the very least it seems like we could give people a more descriptive error message so that they know why it's not working.

Should I open a separate issue for this, or..?

Oh, also note that my flag is in this case is 3 (TW_COLS_NONEMPTY). I'm not yet adept enough with the code to know a) what the heck a flag is :) b) why it's set to that and c) how I would change it to something like TW_COLS_ALL instead.

Apologies if this is a red herring.

Ahhh, this explains a lot... The original design center was importing and analyzing tables full of data, so consideration of tables that are yet to be populated wasn't considered. So, if a column was empty for all rows, it was considered not to matter...

The page right now defaults to showing only non-empty columns (TW_COLS_NONEMPTY), and empty columns automatically are treated as "ignore" (so they won't appear on views). I think I need to change this to default to viewing all columns, displaying the isempty flag, and letting the user explicitly mark them to be ignored...

OK, I've committed this... The "flags" are now based on ignore status rather than empty status, the PK cannot be ignored, I've fixed up the help and some labels on the analysis page...

I'm also seeing the initial issue referenced here. This is happening for a table that's in a non-Drupal database that's on the same server. I've attached a couple of screenshots.

The "Could not analyze" message means that the Schema module failed to inspect the table successfully. I'll try replicating your table in an external database to see if I can reproduce it - I wonder if it might be the "date" field.

I created the table as shown and had no trouble analyzing it. I then removed the auto_increment column, turned the 3-column unique key into the primary key, and tried again - no problem.

What version of MySQL are you using? I've seen some reports for the Schema module that I can't reproduce and may involve a later version than I have (5.0.41), it's probably time I pick up the latest 5.1 and test against that...

This is MySQL 5.0.51 and Schema 6.x-1.3. I should also note that I'm using Table Wizard 6.x-1.x-dev (2009-Apr-08), so my code is a little behind the times.

Could this be because it's outside of my primary Drupal database? Can Schema analyze tables in additional databases that are defined in your settings.php file?

I thought I tested this previously with a separate database and was able to analyze it with TW.

$db_url['default'] = 'mysql://[USER]:[PASS]@localhost/drupal';
$db_url['facultyreport'] = 'mysql://[USER]:[PASS]@[HOST]/Jones_RPTDB';

Oh, I think I found my issue. I need Schema 1.4 at least to inspect external database tables. Just found this in the Schema issue queue: #411538: Support inspection of external databases

That worked. After upgrading to Schema 1.4rc1, I was able to successfully analyze my table with Table Wizard. Thanks for the help!

I am seeing the same problem with tables imported from CSV files - after setting a primary key, tw doesn't see it until I remove it from tw and then re-add it as an existing table.

The initial import goes fine, I get the message to set a primary key as expected. I go into phpmyadmin and change my PK field to int(10), unsigned, not null, set it as PK, and then add the auto-increment option. I return to tw and re-analyze, no luck - the PK is not seen.

If I then remove the table in tw and then simply add it as an existing table, tw picks up the PK immediately.

A separate bug (but maybe not separate?) is that after the successful add, the default view created does not work until I resave it - the page URL simply displays the tw admin page. Once saved it works fine. I'll move this to a separate issue if it is not related.

I can't reproduce this - I did the same as you did with a test file, importing, resetting the first field to an unsigned, not null, auto-increment PK. Reanalysis did work - the field was recognized as a primary key.

Is there any other information you can provide to help reproduce this? What versions of the Table Wizard and Schema modules are you using? MySQL?

In the future, please don't re-open old issues - if you read through this issue, you can see that the circumstances of the original problem (analyzing empty tables) don't apply in this case, whatever you're seeing is a different issue.

Thanks.

Hi Mike,

Apologies if this isn't the place for this post, but I had a related experience and found a solution. Please move/delete/instruct if inappropriate.
------------------------------

I was exporting CSVs from my localhost and uploading into TW. It would see the table fine, but not the Primay Key. No matter what I did at that localhost end, TW wouldn't recognize it.

What I did:

I imported the csv file through TW. Deleted the primary key field in phpmyadmin on the server and added a new one using:

ALTER TABLE `csv_data` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST

Then went back to TW -> Add Existing Table and it successfully recognized the primary key field.

I'm confirming this issue. I have a table with a primary key and I Table Wizard says that there is no primary key (See pictures).

I'm using:
Drupal 6.14
Schema 6.x-1.5
Table Wizard 6.x-1.1
PHP 5.1.6
MySQL 5.0.45

I've tried several things such as changing the data type of the primary key (int, varchar etc). Nothing seems to be working.

Please go to /admin/build/schema/inspect - what does the analysis there show for your table?

Thanks.

No further information provided

Here's the schema output from a table with this situation.

$schema['import2'] = array(
  'description' => t('TODO: please describe this table!'),
  'fields' => array(
    'tnid' => array(
      'description' => t('TODO: please describe this field!'),
      'type' => 'int',
      'not null' => TRUE,
    ),
[... removed for brevity nothing unusual here ...]
  ),
  'primary key' => array('tnid'),
);

Is there anyone out there currently seeing this problem, who can describe how to reproduce it as well as describe their MySQL configuration? I have never been able to get this to happen, and I suspect it's ultimately something on the MySQL end (access to information_schema?).

Thanks for an awsome module.
I think I've reproduced this error, and hope I can provide the some information to help along the way.

phpMyAdmin and Schema module tells me that my PK column ("xkey" in example below) is interpreted as text, which probably prevents it from being used as primary key:

$schema['export9'] = array(
  'description' => t('TODO: please describe this table!'),
  'fields' => array(
    'xkey' => array(
      'description' => t('TODO: please describe this field!'),
      'type' => 'text',
      'size' => 'big',
      'not null' => FALSE,
    ),
    'category' => array(
      'description' => t('TODO: please describe this field!'),
      'type' => 'text',
      'size' => 'big',
      'not null' => FALSE,
    ),
    'question' => array(
      'description' => t('TODO: please describe this field!'),
      'type' => 'text',
      'size' => 'big',
      'not null' => FALSE,
    ),
    'answer' => array(
      'description' => t('TODO: please describe this field!'),
      'type' => 'text',
      'size' => 'big',
      'not null' => FALSE,
    ),
    'xcomment' => array(
      'description' => t('TODO: please describe this field!'),
      'type' => 'text',
      'size' => 'big',
      'not null' => FALSE,
    ),
    'quality' => array(
      'description' => t('TODO: please describe this field!'),
      'type' => 'text',
      'size' => 'big',
      'not null' => FALSE,
    ),
    'difficulty' => array(
      'description' => t('TODO: please describe this field!'),
      'type' => 'text',
      'size' => 'big',
      'not null' => FALSE,
    ),
  ),
);

It could be changed rather quickly in phpMyAdmin, but having an option in TW would be great.

I thought my database was set to utf8_swedish_ci, but I now realize that it was in utf8_general_ci. Don't know if this matters, but my import contains some local characters.

Perhaps this thread might be useful: http://drupal.org/node/563054#comment-2591444
Don't know enough to provide any exact information, but on instructions I can post screenshots or other information from the import.

Cheers,
//Johan Falk, [NodeOne](http://nodeone.se)

The last instance reported was due to not changing the type of the PK field to one suitable for a PK. Note the message displayed when importing a CSV file using Table Wizard:

%filename has been imported into new table %tablename. All fields have initially been defined as text, with no indexing - it is important to use your database management software to set appropriate data types for each field, and to define a single (unique, non-null) field as the primary key. After you have done this, reanalyze the table.

Please, anyone thinking about reopening this issue yet again - carefully reread the original report and see if that fits your circumstances. If not, open a new issue.

I had same problem and I fixed as soon as i Change my sql version 5.5 to 5.1..

Same for me: At MySQL 5.1 the PK will be recognized and used. In MySQL 5.5 it is ignored. Although Schema can see it:

  'Nummer' => array(
    'description' => t('TODO: bitte dieses Feld beschreiben!'),
    'type' => 'serial',
    'not null' => TRUE,
  ),
  'primary key' => array('Nummer'),

BTW: 8 of the 29 fields of this table are missing in Table Wizard when using MySQL 5.5. Schema can see them. The SQL Create definition is attached.

TW isn't picking up table fields that are serial or int. The serial field holds the primary key and without it, I am not able to generate a view for the table data. We are using MySQL 5.5.15.

This is a great module. Thanks in advance for reviewing this issue.

I am running into the same issue as #28, where TW doesn't recognize fields created with the "serial" and "int" schema types. Everything works fine on a local site using MySQL 5.1.66, but not on a remote site (over which I have limited control) using MySQL 5.5.31. Both sites are running Drupal 6.22 and Table Wizard 6.x-1.7.

I think this has to do with the fact that drupal_write_record is failing on MySQL 5.5.

User warning: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'maxvalue)VALUES(9,'override',6,0,0,0,0,0,'int',0,1)' at line 5 query: INSERT INTO tw_columns (twtid, colname, weight, primarykey, availablefk, secure, ignorecol, isempty, coltype, minvalue, maxvalue)VALUES(9,'override',6,0,0,0,0,0,'int',0,1) in _db_query() (line 170 of /var/www/Landlords2/httpdocs/includes/database.mysql.inc).

I've tried to get around it with no avail for now.

Thanks,
Alex

The patch at #1100896: Reserved SQL keywords being used works to solve this issue.