Adding custom table results in break down website [#3186756]

After updating Drupal 7 core from 7.74 to 7.77 my website no longer works.
It seems to be an error with MySQL executed on line 174 of view_custom_table.module.

clearing the cash using drush results in the following message:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `mytablename` mytablename LIMIT 50 OFFSET 0' at line 1

WD menu: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name ' mytablename ': DESCRIBE { mytablename }; Array [error]
(
)
in view_custom_table_views_data() (line 174 of /var/www/webroot/ROOT/sites/all/modules/view_custom_table/view_custom_table.module).
Invalid argument supplied for foreach() views.module:155

When I disable the module the site works.
When I empty table custom_table_view_data the site also works with the module enabled.
When I add a new custom tabel the site gives an error after displaying this message on screen: Add columns relationship for [tablename]
It looks like information in column 'column_relations' is not correct. It is filled with: a:0:{}
How can this be fixed?

Comment	File	Size	Author
#27	3186756.patch	1.67 KB	joseph.olstad
#15	view_custom_table-blank-spaces-3186756-15.patch	2.09 KB	mkrizaj

Issue fork view_custom_table-3186756

Show commands

Start within a Git clone of the project using the version control instructions.

Add & fetch this issue fork’s repository

Or, if you do not have SSH keys set up on git.drupalcode.org:

Add & fetch this issue fork’s repository

3186756-adding-custom-tabel changes, plain diff MR !1
Check out this branch for the first time

Check out existing branch, if you already have it locally

About issue forks

Comments

Comment #1

5 December 2020 at 23:09

LiqueFactive created an issue. See original summary.

Comment #2

eric wassink - qumup commented 5 December 2020 at 23:35

Issue summary:

View changes

Comment #3

sirish.ayyagari commented 7 December 2020 at 16:26

i am having same issue with views_custom_table, not sure why $query = format_string('DESCRIBE { @table_name }', array('@table_name' => $custom_table->table_name)); is failing in views_custom_table.module

Comment #4

sirish.ayyagari commented 7 December 2020 at 16:44

Fix::::
$conf['mysql_identifier_quote_character'] = '';

adding this line to settings.php solved the problem

Comment #5

sirish.ayyagari commented 7 December 2020 at 16:45

Fixed!!!

$conf['mysql_identifier_quote_character'] = '';

adding this line to settings.php solved the problem

Comment #6

ckng

English

commented 10 December 2020 at 18:02

Status:

Active

» Needs review

Solution #4 is working.

Comment #7

avpaderno

he/him

Italian

Brescia, 🇮🇹 🇪🇺

commented 11 December 2020 at 14:01

Version:	7.x-1.0-rc1	» 7.x-1.x-dev
Status:	Needs review	» Active

Comment #8

alexh commented 15 December 2020 at 14:21

Thank you, I can also confirm that solution #4 is working.

Comment #9

eric wassink - qumup commented 20 December 2020 at 20:21

Thanks for this simple solution (#4).

Comment #10

5 January 2021 at 13:54

oldspot made their first commit to this issue’s fork.

Comment #11

5 January 2021 at 13:58

oldspot opened merge request !1

Comment #12

oldspot commented 5 January 2021 at 14:03

Status:

Active

» Needs review

That solution seemed more like a workaround rather than fixing the actual problem, especially when it involves updating such an important constant.

I've debugged the issue a bit and seems like the error is happening due to the blank spaces before and after the "@table_name" - it looks like it's literally looking for the table name including the spaces which is why it can't find it.

Looking at all other examples of code in core and the db_query documentation the spaces before or after the table name are not needed so I've created a PR for it and works fine for me.

Comment #13

avpaderno

he/him

Italian

Brescia, 🇮🇹 🇪🇺

commented 6 January 2021 at 23:48

Title:

Adding custom tabel results in break down website

» Adding custom table results in break down website

Comment #14

oknaru commented 11 January 2021 at 21:16

#12 works for me perfect.
Remove space before & after the table_name line 173 'DESCRIBE {@table_name}'

Comment #15

mkrizaj commented 25 January 2021 at 14:28

Status	File	Size
new	view_custom_table-blank-spaces-3186756-15.patch	2.09 KB

I've created a patch for the issue raised above.

Comment #16

khurrami commented 29 January 2021 at 04:43

#12 works for me perfect.
Thanks

Comment #17

avpaderno

he/him

Italian

Brescia, 🇮🇹 🇪🇺

commented 30 January 2021 at 11:22

Status:

Needs review

» Needs work

The patch needs to be created from the directory containing the module.

Comment #18

oldspot commented 8 February 2021 at 11:45

I've update my Merge Request to include the extra occurrence of blank space found by @mkrizaj

As per the new gitlab merge requests documentation - https://www.drupal.org/drupalorg/docs/gitlab-integration/issue-forks-mer... - you can get the patch by appending ".patch" to the merge request url rather than creating new patch files:
https://git.drupalcode.org/project/view_custom_table/-/merge_requests/1....

Comment #19

oldspot commented 8 February 2021 at 12:22

Status:

Needs work

» Needs review

Updating to 'Needs review' as added the extra fix but the rest of it has been confirmed as working by a few above so could go to RTBC soon.

Needs review

» Reviewed & tested by the community

I've just stumbled into the same issue again when adding another custom table to views and can confirm the last fix added to the patch from @mkrizaj works well:
https://git.drupalcode.org/project/view_custom_table/-/merge_requests/1....

Changing to RTBC so it can be committed soon hopefully as I've just discovered a duplicate issue to this one which has the exact same solution:
https://www.drupal.org/project/view_custom_table/issues/3069124

Comment #23

promo-il commented 8 July 2021 at 22:52

Error on update Drupal 7.72 to 7.81. Patch #15 is OK

Comment #24

joseph.olstad

French

commented 10 September 2021 at 05:21

Parent issue:

» #3232266: Offering to maintain Views Custom Table

Comment #25

avpaderno

he/him

Italian

Brescia, 🇮🇹 🇪🇺

commented 10 September 2021 at 17:56

Status:

Reviewed & tested by the community

» Needs work

+  $text_query = format_string('DESCRIBE {@table_name}', array('@table_name' => $table_name));

The correct code would be the following one.

$text_query = 'DESCRIBE {' . db_escape_table($table_name) . '}';

See the code in DrupalDatabaseCache::getMultiple().

Comment #26

joseph.olstad

French

commented 22 October 2021 at 15:05

New release for Drupal 7 , version 7.x-1.0

download version 7.x-1.0

Comment #27

joseph.olstad

French

commented 22 October 2021 at 15:05

Status	File	Size
new	3186756.patch	1.67 KB

Comment #28

joseph.olstad

French

commented 22 October 2021 at 15:06

Status:

Needs work

» Needs review

patch is against 7.x-1.6

Comment #29

22 October 2021 at 15:34

joseph.olstad committed 7b6ffaf on 7.x-1.x authored by apaderno

Issue #3186756 by oldspot, joseph.olstad, mkrizaj, apaderno: Adding...

Comment #30

joseph.olstad

French

commented 22 October 2021 at 15:35

Status:

Needs review

» Fixed

new release shortly

Comment #31

joseph.olstad

French

commented 22 October 2021 at 16:31

here's the new release, enjoy and thanks everyone! Too bad we had to wait this long to get a maintainer.

https://www.drupal.org/project/view_custom_table/releases/7.x-1.1

Comment #32

5 November 2021 at 16:34

Status:

Fixed

» Closed (fixed)

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