BUG CMSB 3.75 Generated Columns - RESOLVED

4 posts by 2 authors in: Forums > CMS Builder
Last Post: December 19 (RSS)

By kitsguru - December 15 - edited: December 19

I changed my generated column, but the output did not reflect the results. A quick check showed a discrepancy between the schema and the MySQL source.

//Schema
  'gen_links' => array(
    'order' => 53,
    'label' => 'Gen links',
    'type' => 'GeneratedColumn',
    'gcExpr' => 'CONCAT(permalink,if(LENGTH(shadow_permalink),CONCAT("<br>-> ", shadow_permalink)))',
    'gcType' => 'STORED',
    'indexed' => '0',
    'customColumnType' => 'TEXT',
  ),

//MySQL
`gen_links` text GENERATED ALWAYS AS (concat(`permalink`,`shadow_permalink`)) STORED,

I quit the browser, reopened it, and then navigated to the database editor, hoping it would detect and fix the issue. The problem remained. I tried editing the column again, but it would not update, regardless of the changes.

UPDATE:

Manually deleted the column from the database, went back to database editor, says it added column. I checked MySQL source, and the column has NOT been added.

Jeff Shields
yaadev.com

BUG CMSB 3.75 Generated Columns

By Tim - December 18

Hi Jeff,

Just posting here to let you know that I am looking into this for you and will let you know what I find out over the next day or two.

Thanks!

Tim Hurd
Senior Web Programmer
Interactivetools.com

BUG CMSB 3.75 Generated Columns

By Tim - December 19

Hi Jeff,

I wasn't able to recreate the issue exactly as you are experiencing, but I did encounter issues when I tried with your statement. Your IF only has the "true" part and not a "false" part. Can you try specifying a false part to your statement and see if you get the desired results? Can you also look in your cmsb field viewer and see if the MySQL Type column has "varchar(255) STORED GENERATED" as a value? See attached screenshot (generated-column-screenshot1.png) for confirmation?

I wasn't able to see a value for this column (and was only in the schema file, not synched with the database) when not specifying the false part of the statement. As soon as I added the false condition to the statement I saw this in the field editor...

CONCAT(permalink,if(LENGTH(shadow_permalink),CONCAT("<br>-> ", shadow_permalink), 'test'))

Which became this in the schema file...

'gen_links' => array(
    'order' => 15,
    'label' => 'gen links',
    'type' => 'GeneratedColumn',
    'gcExpr' => 'CONCAT(permalink,if(LENGTH(shadow_permalink),CONCAT("<br>-> ", shadow_permalink), \'test\'))',
    'gcType' => 'STORED',
    'indexed' => '0',
  ),

And became this in the mysql source tab...

`gen_links` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (concat(`permalink`,if(length(`shadow_permalink`),concat(_utf8mb4'
-> ',`shadow_permalink`),_utf8mb4'test'))) STORED,

It also appears to be operating correctly for the generated column. Appending 'test' to the permalink if the shadow_permalink field is empty, otherwise appending the br tag and shadow_permalink if there is indeed a shadow_permalink specified.

I think specifying the false condition might be all you need to see everything sync up.

Let me know how it goes.

Tim Hurd
Senior Web Programmer
Interactivetools.com

Attachments:

generated-column-screenshot1.png 11K

By kitsguru - December 19

Thanks, Tim,

That was it. I stared at it for hours and forgot that the false condition was required.

Jeff Shields
yaadev.com