Problems with Views - "ERROR 1449 ... The user specified as a definer ('xxx'@'xxx') does not exist"

[phlbnks]

This is a suggest / query / pre-PR question.

I am pulling a db from serverA to serverB and running a search-replace on it. It is failing because the dB contains a view. The view references the serverA MySQL user which doesn't exist on serverB.

(presuming I'm not missing something / haven't messed something up in my setup...)

I don't know of any reason to run a search-replace on a View as they are dynamic by nature. Would a PR to skip them be accepted? Is there any reason Views should be included?

These would work on MySQL/MariaDB (but haven't been extensively tested):

SHOW TABLE STATUS
# WHERE `comment` != 'VIEW'
WHERE `Rows` IS NOT NULL

It would mean modifying wp_get_table_names() in wp-cli/php/utils-wp.php fairly extensively, unless there's an easier way to identify Views.

I think this is going to get more widely applicable if the use of a View in core goes ahead (wasn't there talk of a View for taxonomy data?)

While I'm obviously talking about modifying a function in core wp-cli I thought I'd post this hear first as the most obvious (to me) place that it would affect and use case for the change.

[schlessera]

I think it makes sense to be able to skip views for certain commands. However, the default behavior for wp_get_table_names() should be to include them, as they are meant to be transparent to the end user.

I suggest the following changes (in 2 separate PRs with corresponding tests for each):

• Add additional flags --base-tables-only and --views-only to wp_get_table_names() (through its second $assoc_args argument). Without these, the default result will return both regular tables and views.

• In the search-replace command, make a second query with --views-only and check this array to skip while looping over the table. The report can then still show all found tables/views but views will show something like skipped view as the reason for not processing. This will avoid user confusion when they see a table in the list but the search-replace does not show it being processed.

Do you think this makes sense?

[phlbnks]

Sorry for the slow response. That sounds sensible - I'll aim to get on with this at #wcldn today.

I'll try and remember to also check if db export includes views, as that's a other place a --skip-views might make sense.