Flashvideo does not see a field I created and it says unknown column although it exists. CCK database structure is wrong

It looks like CCK has moved your fields off the content type table and it is now using per field storage. This may happen if the field is shared between several content types, or it is defined to use multiple values.

The error happens in flashvideo.module on line 2488, and this suggests that module is not building the SQL statement properly.

See this issue for hints on how CCK decides if the field uses per type or per field storage: #531140: cck fields are storing in diferrent sql tables?

Dear c,

So, is markus right? Are the fields that you're using being used by more than one content type in your system? Or maybe you've set your original video field to receive only one video and your finished video and thumbnail fields are set to receive multiple video files?

FYI, I'm going to close the older issue #505912: I cannot get flashvideo to convert my files and start using this one.

-Mark

Are you inserting the [video] tag into the body of your node? If not, please read about this in the "Help" section included with the module.

I think flashvideo module needs to query for database information related to the CCK fields using CCK APIs.

http://drupal.org/node/531140#comment-1854518

CCK may store field columns in per-content-type or per-field tables, depending on several factors (is field shared? allows multiple values?).

"Are the fields that you're using being used by more than one content type in your system? "

Is this wrong? I am getting this error: "Unknown column 'delta' in 'field list' query: INSERT INTO content_field_finished_thumbnail [...]"

Because the module assumes I am using a multi value field when in fact I am just using the same field shared across 2 content types. Does this module not support that by design?

I see in the module around line 744 that it assumes you either have a single upload field on 1 content type or you must have a multi upload field on 1 content type. What about my scenario where a single upload field is shared across multiple content types?

I just hacked it for now because I'm on a deadline, but it would be nice if it was fixed or if someone gave me an explanation why it is like this. :)

Here's the part of the code I think ignores our (or at least my scenario) of using a single upload field shared across multiple content types:

line 745:

          // For single-upload file fields
          if (db_column_exists($cck_table, $cck_original_video_field .'_fid')) {
            db_query("UPDATE {". $cck_table ."} SET ". $cck_finished_thumbnail_field ."_fid = %d, ". $cck_finished_thumbnail_field ."_list = 0 WHERE nid = %d", $newfile->fid, $newfile->nid);
          }
          else { // For multi-upload file fields
            // Get rid of the initial NULL entry.
            db_query("DELETE FROM {content_". $cck_finished_thumbnail_field ."} WHERE nid = %d", $newfile->nid);
            db_query("INSERT INTO {content_". $cck_finished_thumbnail_field ."} (vid, nid, delta, ". $cck_finished_thumbnail_field ."_fid, ". $cck_finished_thumbnail_field ."_list) VALUES (%d, %d, %d, %d, 0)", $newfile->vid, $newfile->nid, $params->video_index, $newfile->fid);
          }

It has 2 scenarios:

- A single-upload field on one content type
- A multi-upload field on one or more content types

I think it should also cover my scenario:

- A single-upload field on two or more content types

In my mind that bit of code should look something like this:

          // For single-upload file fields
          if (db_column_exists($cck_table, $cck_original_video_field .'_fid')) {
            db_query("UPDATE {". $cck_table ."} SET ". $cck_finished_thumbnail_field ."_fid = %d, ". $cck_finished_thumbnail_field ."_list = 0 WHERE nid = %d", $newfile->fid, $newfile->nid);
          }
          else if (some check in here) { // For single upload field shared across multiple content types
            // Get rid of the initial NULL entry.
            db_query("DELETE FROM {content_". $cck_finished_thumbnail_field ."} WHERE nid = %d", $newfile->nid);
            // The same query as the one below, just without the delta values
            db_query("INSERT INTO {content_". $cck_finished_thumbnail_field ."} (vid, nid, ". $cck_finished_thumbnail_field ."_fid, ". $cck_finished_thumbnail_field ."_list) VALUES (%d, %d, %d, 0)", $newfile->vid, $newfile->nid, $newfile->fid);
          }
          else { // For multi-upload file fields
            // Get rid of the initial NULL entry.
            db_query("DELETE FROM {content_". $cck_finished_thumbnail_field ."} WHERE nid = %d", $newfile->nid);
            db_query("INSERT INTO {content_". $cck_finished_thumbnail_field ."} (vid, nid, delta, ". $cck_finished_thumbnail_field ."_fid, ". $cck_finished_thumbnail_field ."_list) VALUES (%d, %d, %d, %d, 0)", $newfile->vid, $newfile->nid, $params->video_index, $newfile->fid);
          }

@TwiiK: Rather than adding if/else here, FlashVideo should ask CCK for the table name. Just follow the link in my comment in #7 to see how.

That sounds much smarter, yes. :)

I haven't really investigated it all that much, I just hacked it to make it work for me because I was in a hurry.

I've just checked in some changes for this. They don't account for the scenario Twiik brought up where some fields are shared and some are not, but the changes do make use of the CCK functions pointed out by markus_petrux. Thanks!

Here is the missing else-if check that TwiiK noted in comment #11:

          else if (!isset($cck_finished_video_field_db_info['columns']['delta'])) {

This should allow the Flashvideo module to support using a shared CCK field for the video upload field. In other words, you can have multiple content types that share the same video Filefield.

In my testing, this else-if statement along with TwiiK's code in #11 seems to work fine. Can anyone else test, or can the maintainer comment on whether they see any problems with this?

Thanks for this aasarava,

If Mark ( attheshow ) does not commit this, I certainly will when I am back on task mid December. I am hoping to finally have a stable release out by then.

Thanks everyone for your efforts on clearing out these remaining issues.

Travis.

Here's a patch against the 1.5 release. Can someone please test?

A brief test gives this:
user warning: Column count doesn't match value count at row 1 query: _flashvideo_perform_postop /* superadmin : _flashvideo_perform_postop */ INSERT INTO content_field_video_thumb (vid, nid, delta, field_video_thumb_fid, field_video_thumb_list) VALUES (178, 178, 385, 0) in /home/etvin0/public_html/tv-nh/sites/all/modules/flashvideo/flashvideo.module on line 758.

It seems you've forgotten to remove the 'delta,' from the two first additional inserts :) When removing those, the patch seems to work fine here...

#18@roald
Removing ‘delta’ fields from insert query got rid of user warning.
But the video conversion is still failing.

Correcting the note:
Problem was in my ffmpeg settings.

Hello,

Thanks! Patch#17 with correction #18 is working for me.

The only thing is that I am getting a couple of these errors when cron runs...related to the thumbnail.

# user warning: Duplicate entry '135-0' for key 1 query: INSERT INTO content_field_product_video_thumb (vid, nid, field_product_video_thumb_fid, field_product_video_thumb_list) VALUES (135, 134, 749, 0) 
in .../flashvideo.module on line 776 and same thing for line 758.

[EDIT]...just realized that nid=135 does not exists on my dev site...even though it is being referenced by the query above.

Thanks,

KH

I think there's another copy/paste error on line 776 and 778 in the patch, which is causing Duplicate entry error. I tried to fix it, here's the patch I used against 1.5

Hello Jrao,

Thank you very much for the patch! I got around to test it and I am still getting a duplicate entry error...when uploading more than one video onto a multiple upload field. Single upload fields are working ok and I do not get this error.

For Multiple upload fields:

When converting the videos it gives the error below for each video and then leaves the converted video and the image on the root of the files folder. Hence the video is not displayed and/or listed on node view. Note converting multiple videos only worked when I enabled conversion on node save. It did not work when I set to convert videos on cron.

Duplicate entry 11789-0; for key 1 query: INSERT INTO content_field_product_video_thumb (vid, nid, field_product_video_thumb_fid, field_product_video_thumb_list) VALUES (11789, 11787, 877, 0) in /.../flashvideo/flashvideo.module on line 758.

Thanks,

KH

Ok, the previous patch has more problem than I originally thought, here's another attempt to get this fixed:
1. Fixed more copy/paste errors: previous patch uses finished_video in place of thumbnail and original_video
2. I don't think

!isset($cck_finished_video_field_db_info['columns']['delta'])

is the correct way to check if delta column exists, I used this instead:

empty($cck_finished_thumbnail_field['multiple'])

3. The problem in issue #767906: Delta not being seen for multiple videos also needs to be fixed, but I don't think the fix in 767906 is correct, the line

$video_index = (!empty($node->$cck_finished_video_field['field_name']) && is_array($node->$cck_finished_video_field['field_name'])) ? count($node->$cck_finished_video_field['field_name']) - 1 : $video_index;

is: a. not needed; b. in the wrong place; c. incorrect. I think the previously uploaded videos are already taken into account by initialization of the index:

      // Initialize new index of the video.
      $result = db_query("SELECT DISTINCT video_index FROM {flashvideo} WHERE nid = %d ORDER BY video_index ASC", $node->nid);
      while ($row = db_fetch_object($result)) {
        $video_index = $row->video_index + 1;
      }

So I commented out the offending line instead, hopefully some flashvideo guru can confirm my understanding is correct, since this seems to be a pretty big change.
4. Related to #3, moved video_index increment inside if (!$found), so that it only get incremented for new video

Hello Jrao,

Thanks for your patch! I greatly appreciate your help :)

I actually went ahead and tried #767906: Delta not being seen for multiple videos (without patch #23) and it is working for me. Now I can have multiple upload fields that are shared among 4+ content types. The videos convert ok and display in the right order (have the right deltas).

For now, I agree with you that it would be very helpful if we can get flashvideo gurus to provide input on your work above.

Regards,

KH

Looks good jrao! Thank you so much. Just committed to CVS.