[MXS-4980] show shards does not show all information expected - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Not a Bug
Affects Version/s: 23.08.4
Fix Version/s: 23.08.5
Component/s: maxctrl, schemarouter
Labels:
Environment:
Linux, n.a.

Description

show shards does NOT show all the databases nor the tables but only some:

mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 customer_0030 --execute='show shards' | grep customer_00.*1 | column -t
customer_0010. shard1
customer_0010.address shard1
customer_0010.sales shard1
customer_0021. shard2
customer_0021.address shard2
customer_0021.sales shard2
customer_0011. shard1
customer_0011.address shard1
customer_0011.sales shard1

This contradicts IMHO what is written in the docu:
"The schemarouter will also intercept the SHOW DATABASES command and generate it based on its internal data. This means that newly created databases will not show up immediately and will only be visible when the cached data has been updated."
https://mariadb.com/kb/en/mariadb-maxscale-2308-schemarouter/#custom-sql-commands

The missing tables were created long before the MaxScale was restarted last time...

Attachments

Activity

Ascending order - Click to sort in descending order

View 4 older comments

Oli Sennhauser added a comment - 2024-02-14 14:20

I separated the users now as described above. But three is no change I only see parts of the shards...

mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 customer_0030 --execute='show shards' | grep customer_00.*1 | column -t
customer_0021. shard2
customer_0021.address shard2
customer_0021.sales shard2
customer_0010. shard4
customer_0010.address shard4
customer_0010.sales shard4

Oli Sennhauser added a comment - 2024-02-14 14:20 I separated the users now as described above. But three is no change I only see parts of the shards... mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 customer_0030 --execute='show shards' | grep customer_00.*1 | column -t customer_0021. shard2 customer_0021.address shard2 customer_0021.sales shard2 customer_0010. shard4 customer_0010.address shard4 customer_0010.sales shard4

markus makela added a comment - 2024-02-22 14:46

I tested this locally and it seems to work as expected. Here's the output I got:

$ mariadb -sss --user=app --password=secret --host=127.0.0.1 --port=4010 customer_0030 --execute='show shards' | grep -E -v 'information_schema|performance_schema|mysql|sys' | sort | column -t

customer_0010.address  server1

customer_0010.sales    server1

customer_0010.         server1

customer_0011.address  server1

customer_0011.sales    server1

customer_0011.         server1

customer_0020.address  server2

customer_0020.sales    server2

customer_0020.         server2

customer_0021.address  server2

customer_0021.sales    server2

customer_0021.         server2

customer_0022.address  server2

customer_0022.sales    server2

customer_0022.         server2

customer_0030.address  server3

customer_0030.sales    server3

customer_0030.         server3

Here are where the tables are located:

$ for i in 3000 3001 3002; do mariadb -umaxuser -pmaxpwd -h 127.0.0.1 -P $i -e 'select @@hostname, @@port;select table_schema, table_name from information_schema.tables where table_schema like "%customer%";'; done

+------------+--------+

| @@hostname | @@port |

+------------+--------+

| monolith   |   3000 |

+------------+--------+

+---------------+------------+

| table_schema  | table_name |

+---------------+------------+

| customer_0010 | address    |

| customer_0010 | sales      |

| customer_0011 | address    |

| customer_0011 | sales      |

+---------------+------------+

+------------+--------+

| @@hostname | @@port |

+------------+--------+

| monolith   |   3001 |

+------------+--------+

+---------------+------------+

| table_schema  | table_name |

+---------------+------------+

| customer_0020 | address    |

| customer_0020 | sales      |

| customer_0021 | address    |

| customer_0021 | sales      |

| customer_0022 | address    |

| customer_0022 | sales      |

+---------------+------------+

+------------+--------+

| @@hostname | @@port |

+------------+--------+

| monolith   |   3002 |

+------------+--------+

+---------------+------------+

| table_schema  | table_name |

+---------------+------------+

| customer_0030 | address    |

| customer_0030 | sales      |

+---------------+------------+

Here's my configuration:

[maxscale]

threads=auto

[server1]

type=server

address=127.0.0.1

port=3000

protocol=MariaDBBackend

[server2]

type=server

address=127.0.0.1

port=3001

protocol=MariaDBBackend

[server3]

type=server

address=127.0.0.1

port=3002

protocol=MariaDBBackend

[Monitor]

type=monitor

module=galeramon

servers=server1,server2,server3

user=maxscale

password=secret

monitor_interval=15s

[Router]

type=service

router=schemarouter

servers=server1,server2,server3

user=app

password=secret

auth_all_servers=true

[Listener]

type=listener

service=Router

protocol=MariaDBClient

port=4010

Here's the maxscale user and the related role:

$ for i in 3000 3001 3002; do mariadb -umaxuser -pmaxpwd -h 127.0.0.1 -P $i -e 'select @@hostname, @@port;show grants for maxscale; show grants for maxscale_role;'; done

+------------+--------+

| @@hostname | @@port |

+------------+--------+

| monolith   |   3000 |

+------------+--------+

+---------------------------------------------------------------------------------------------------------+

| Grants for maxscale@%                                                                                   |

+---------------------------------------------------------------------------------------------------------+

| GRANT `maxscale_role` TO `maxscale`@`%`                                                                 |

| GRANT USAGE ON *.* TO `maxscale`@`%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' |

| SET DEFAULT ROLE `maxscale_role` FOR `maxscale`@`%`                                                     |

+---------------------------------------------------------------------------------------------------------+

+---------------------------------------------------------------------------------------+

| Grants for maxscale_role                                                              |

+---------------------------------------------------------------------------------------+

| GRANT FILE, BINLOG MONITOR, CONNECTION ADMIN, SLAVE MONITOR ON *.* TO `maxscale_role` |

| GRANT SELECT ON `mysql`.`user` TO `maxscale_role`                                     |

+---------------------------------------------------------------------------------------+

+------------+--------+

| @@hostname | @@port |

+------------+--------+

| monolith   |   3001 |

+------------+--------+

+---------------------------------------------------------------------------------------------------------+

| Grants for maxscale@%                                                                                   |

+---------------------------------------------------------------------------------------------------------+

| GRANT `maxscale_role` TO `maxscale`@`%`                                                                 |

| GRANT USAGE ON *.* TO `maxscale`@`%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' |

| SET DEFAULT ROLE `maxscale_role` FOR `maxscale`@`%`                                                     |

+---------------------------------------------------------------------------------------------------------+

+---------------------------------------------------------------------------------------+

| Grants for maxscale_role                                                              |

+---------------------------------------------------------------------------------------+

| GRANT FILE, BINLOG MONITOR, CONNECTION ADMIN, SLAVE MONITOR ON *.* TO `maxscale_role` |

| GRANT SELECT ON `mysql`.`user` TO `maxscale_role`                                     |

+---------------------------------------------------------------------------------------+

+------------+--------+

| @@hostname | @@port |

+------------+--------+

| monolith   |   3002 |

+------------+--------+

+---------------------------------------------------------------------------------------------------------+

| Grants for maxscale@%                                                                                   |

+---------------------------------------------------------------------------------------------------------+

| GRANT `maxscale_role` TO `maxscale`@`%`                                                                 |

| GRANT USAGE ON *.* TO `maxscale`@`%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' |

| SET DEFAULT ROLE `maxscale_role` FOR `maxscale`@`%`                                                     |

+---------------------------------------------------------------------------------------------------------+

+---------------------------------------------------------------------------------------+

| Grants for maxscale_role                                                              |

+---------------------------------------------------------------------------------------+

| GRANT FILE, BINLOG MONITOR, CONNECTION ADMIN, SLAVE MONITOR ON *.* TO `maxscale_role` |

| GRANT SELECT ON `mysql`.`user` TO `maxscale_role`                                     |

+---------------------------------------------------------------------------------------+

And here's the one for app and the related role:

$ for i in 3000 3001 3002; do mariadb -umaxuser -pmaxpwd -h 127.0.0.1 -P $i -e 'select @@hostname, @@port;show grants for app; show grants for app_role;'; done

+------------+--------+

| @@hostname | @@port |

+------------+--------+

| monolith   |   3000 |

+------------+--------+

+----------------------------------------------------------------------------------------------------+

| Grants for app@%                                                                                   |

+----------------------------------------------------------------------------------------------------+

| GRANT `app_role` TO `app`@`%`                                                                      |

| GRANT USAGE ON *.* TO `app`@`%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' |

| SET DEFAULT ROLE `app_role` FOR `app`@`%`                                                          |

+----------------------------------------------------------------------------------------------------+

+----------------------------------------------------------------------+

| Grants for app_role                                                  |

+----------------------------------------------------------------------+

| GRANT SHOW DATABASES ON *.* TO `app_role`                            |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `customer_%`.* TO `app_role` |

| GRANT SELECT ON `mysql`.`db` TO `app_role`                           |

| GRANT SELECT ON `mysql`.`proxies_priv` TO `app_role`                 |

| GRANT SELECT ON `mysql`.`columns_priv` TO `app_role`                 |

| GRANT SELECT ON `mysql`.`roles_mapping` TO `app_role`                |

| GRANT SELECT ON `mysql`.`user` TO `app_role`                         |

| GRANT SELECT ON `mysql`.`tables_priv` TO `app_role`                  |

| GRANT SELECT ON `mysql`.`procs_priv` TO `app_role`                   |

+----------------------------------------------------------------------+

+------------+--------+

| @@hostname | @@port |

+------------+--------+

| monolith   |   3001 |

+------------+--------+

+----------------------------------------------------------------------------------------------------+

| Grants for app@%                                                                                   |

+----------------------------------------------------------------------------------------------------+

| GRANT `app_role` TO `app`@`%`                                                                      |

| GRANT USAGE ON *.* TO `app`@`%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' |

| SET DEFAULT ROLE `app_role` FOR `app`@`%`                                                          |

+----------------------------------------------------------------------------------------------------+

+----------------------------------------------------------------------+

| Grants for app_role                                                  |

+----------------------------------------------------------------------+

| GRANT SHOW DATABASES ON *.* TO `app_role`                            |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `customer_%`.* TO `app_role` |

| GRANT SELECT ON `mysql`.`db` TO `app_role`                           |

| GRANT SELECT ON `mysql`.`proxies_priv` TO `app_role`                 |

| GRANT SELECT ON `mysql`.`columns_priv` TO `app_role`                 |

| GRANT SELECT ON `mysql`.`roles_mapping` TO `app_role`                |

| GRANT SELECT ON `mysql`.`user` TO `app_role`                         |

| GRANT SELECT ON `mysql`.`tables_priv` TO `app_role`                  |

| GRANT SELECT ON `mysql`.`procs_priv` TO `app_role`                   |

+----------------------------------------------------------------------+

+------------+--------+

| @@hostname | @@port |

+------------+--------+

| monolith   |   3002 |

+------------+--------+

+----------------------------------------------------------------------------------------------------+

| Grants for app@%                                                                                   |

+----------------------------------------------------------------------------------------------------+

| GRANT `app_role` TO `app`@`%`                                                                      |

| GRANT USAGE ON *.* TO `app`@`%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' |

| SET DEFAULT ROLE `app_role` FOR `app`@`%`                                                          |

+----------------------------------------------------------------------------------------------------+

+----------------------------------------------------------------------+

| Grants for app_role                                                  |

+----------------------------------------------------------------------+

| GRANT SHOW DATABASES ON *.* TO `app_role`                            |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `customer_%`.* TO `app_role` |

| GRANT SELECT ON `mysql`.`db` TO `app_role`                           |

| GRANT SELECT ON `mysql`.`proxies_priv` TO `app_role`                 |

| GRANT SELECT ON `mysql`.`columns_priv` TO `app_role`                 |

| GRANT SELECT ON `mysql`.`roles_mapping` TO `app_role`                |

| GRANT SELECT ON `mysql`.`user` TO `app_role`                         |

| GRANT SELECT ON `mysql`.`tables_priv` TO `app_role`                  |

| GRANT SELECT ON `mysql`.`procs_priv` TO `app_role`                   |

+----------------------------------------------------------------------+

markus makela added a comment - 2024-02-22 14:46 I tested this locally and it seems to work as expected. Here's the output I got: $ mariadb -sss --user=app --password=secret --host=127.0.0.1 --port=4010 customer_0030 --execute='show shards' | grep -E -v 'information_schema|performance_schema|mysql|sys' | sort | column -t customer_0010.address server1 customer_0010.sales server1 customer_0010. server1 customer_0011.address server1 customer_0011.sales server1 customer_0011. server1 customer_0020.address server2 customer_0020.sales server2 customer_0020. server2 customer_0021.address server2 customer_0021.sales server2 customer_0021. server2 customer_0022.address server2 customer_0022.sales server2 customer_0022. server2 customer_0030.address server3 customer_0030.sales server3 customer_0030. server3 Here are where the tables are located: $ for i in 3000 3001 3002; do mariadb -umaxuser -pmaxpwd -h 127.0.0.1 -P $i -e 'select @@hostname, @@port;select table_schema, table_name from information_schema.tables where table_schema like "%customer%";'; done +------------+--------+ | @@hostname | @@port | +------------+--------+ | monolith | 3000 | +------------+--------+ +---------------+------------+ | table_schema | table_name | +---------------+------------+ | customer_0010 | address | | customer_0010 | sales | | customer_0011 | address | | customer_0011 | sales | +---------------+------------+ +------------+--------+ | @@hostname | @@port | +------------+--------+ | monolith | 3001 | +------------+--------+ +---------------+------------+ | table_schema | table_name | +---------------+------------+ | customer_0020 | address | | customer_0020 | sales | | customer_0021 | address | | customer_0021 | sales | | customer_0022 | address | | customer_0022 | sales | +---------------+------------+ +------------+--------+ | @@hostname | @@port | +------------+--------+ | monolith | 3002 | +------------+--------+ +---------------+------------+ | table_schema | table_name | +---------------+------------+ | customer_0030 | address | | customer_0030 | sales | +---------------+------------+ Here's my configuration: [maxscale] threads=auto [server1] type=server address=127.0.0.1 port=3000 protocol=MariaDBBackend [server2] type=server address=127.0.0.1 port=3001 protocol=MariaDBBackend [server3] type=server address=127.0.0.1 port=3002 protocol=MariaDBBackend [Monitor] type=monitor module=galeramon servers=server1,server2,server3 user=maxscale password=secret monitor_interval=15s [Router] type=service router=schemarouter servers=server1,server2,server3 user=app password=secret auth_all_servers=true [Listener] type=listener service=Router protocol=MariaDBClient port=4010 Here's the maxscale user and the related role: $ for i in 3000 3001 3002; do mariadb -umaxuser -pmaxpwd -h 127.0.0.1 -P $i -e 'select @@hostname, @@port;show grants for maxscale; show grants for maxscale_role;'; done +------------+--------+ | @@hostname | @@port | +------------+--------+ | monolith | 3000 | +------------+--------+ +---------------------------------------------------------------------------------------------------------+ | Grants for maxscale@% | +---------------------------------------------------------------------------------------------------------+ | GRANT `maxscale_role` TO `maxscale`@`%` | | GRANT USAGE ON *.* TO `maxscale`@`%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' | | SET DEFAULT ROLE `maxscale_role` FOR `maxscale`@`%` | +---------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------------------------------------+ | Grants for maxscale_role | +---------------------------------------------------------------------------------------+ | GRANT FILE, BINLOG MONITOR, CONNECTION ADMIN, SLAVE MONITOR ON *.* TO `maxscale_role` | | GRANT SELECT ON `mysql`.`user` TO `maxscale_role` | +---------------------------------------------------------------------------------------+ +------------+--------+ | @@hostname | @@port | +------------+--------+ | monolith | 3001 | +------------+--------+ +---------------------------------------------------------------------------------------------------------+ | Grants for maxscale@% | +---------------------------------------------------------------------------------------------------------+ | GRANT `maxscale_role` TO `maxscale`@`%` | | GRANT USAGE ON *.* TO `maxscale`@`%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' | | SET DEFAULT ROLE `maxscale_role` FOR `maxscale`@`%` | +---------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------------------------------------+ | Grants for maxscale_role | +---------------------------------------------------------------------------------------+ | GRANT FILE, BINLOG MONITOR, CONNECTION ADMIN, SLAVE MONITOR ON *.* TO `maxscale_role` | | GRANT SELECT ON `mysql`.`user` TO `maxscale_role` | +---------------------------------------------------------------------------------------+ +------------+--------+ | @@hostname | @@port | +------------+--------+ | monolith | 3002 | +------------+--------+ +---------------------------------------------------------------------------------------------------------+ | Grants for maxscale@% | +---------------------------------------------------------------------------------------------------------+ | GRANT `maxscale_role` TO `maxscale`@`%` | | GRANT USAGE ON *.* TO `maxscale`@`%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' | | SET DEFAULT ROLE `maxscale_role` FOR `maxscale`@`%` | +---------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------------------------------------+ | Grants for maxscale_role | +---------------------------------------------------------------------------------------+ | GRANT FILE, BINLOG MONITOR, CONNECTION ADMIN, SLAVE MONITOR ON *.* TO `maxscale_role` | | GRANT SELECT ON `mysql`.`user` TO `maxscale_role` | +---------------------------------------------------------------------------------------+ And here's the one for app and the related role: $ for i in 3000 3001 3002; do mariadb -umaxuser -pmaxpwd -h 127.0.0.1 -P $i -e 'select @@hostname, @@port;show grants for app; show grants for app_role;'; done +------------+--------+ | @@hostname | @@port | +------------+--------+ | monolith | 3000 | +------------+--------+ +----------------------------------------------------------------------------------------------------+ | Grants for app@% | +----------------------------------------------------------------------------------------------------+ | GRANT `app_role` TO `app`@`%` | | GRANT USAGE ON *.* TO `app`@`%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' | | SET DEFAULT ROLE `app_role` FOR `app`@`%` | +----------------------------------------------------------------------------------------------------+ +----------------------------------------------------------------------+ | Grants for app_role | +----------------------------------------------------------------------+ | GRANT SHOW DATABASES ON *.* TO `app_role` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `customer_%`.* TO `app_role` | | GRANT SELECT ON `mysql`.`db` TO `app_role` | | GRANT SELECT ON `mysql`.`proxies_priv` TO `app_role` | | GRANT SELECT ON `mysql`.`columns_priv` TO `app_role` | | GRANT SELECT ON `mysql`.`roles_mapping` TO `app_role` | | GRANT SELECT ON `mysql`.`user` TO `app_role` | | GRANT SELECT ON `mysql`.`tables_priv` TO `app_role` | | GRANT SELECT ON `mysql`.`procs_priv` TO `app_role` | +----------------------------------------------------------------------+ +------------+--------+ | @@hostname | @@port | +------------+--------+ | monolith | 3001 | +------------+--------+ +----------------------------------------------------------------------------------------------------+ | Grants for app@% | +----------------------------------------------------------------------------------------------------+ | GRANT `app_role` TO `app`@`%` | | GRANT USAGE ON *.* TO `app`@`%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' | | SET DEFAULT ROLE `app_role` FOR `app`@`%` | +----------------------------------------------------------------------------------------------------+ +----------------------------------------------------------------------+ | Grants for app_role | +----------------------------------------------------------------------+ | GRANT SHOW DATABASES ON *.* TO `app_role` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `customer_%`.* TO `app_role` | | GRANT SELECT ON `mysql`.`db` TO `app_role` | | GRANT SELECT ON `mysql`.`proxies_priv` TO `app_role` | | GRANT SELECT ON `mysql`.`columns_priv` TO `app_role` | | GRANT SELECT ON `mysql`.`roles_mapping` TO `app_role` | | GRANT SELECT ON `mysql`.`user` TO `app_role` | | GRANT SELECT ON `mysql`.`tables_priv` TO `app_role` | | GRANT SELECT ON `mysql`.`procs_priv` TO `app_role` | +----------------------------------------------------------------------+ +------------+--------+ | @@hostname | @@port | +------------+--------+ | monolith | 3002 | +------------+--------+ +----------------------------------------------------------------------------------------------------+ | Grants for app@% | +----------------------------------------------------------------------------------------------------+ | GRANT `app_role` TO `app`@`%` | | GRANT USAGE ON *.* TO `app`@`%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' | | SET DEFAULT ROLE `app_role` FOR `app`@`%` | +----------------------------------------------------------------------------------------------------+ +----------------------------------------------------------------------+ | Grants for app_role | +----------------------------------------------------------------------+ | GRANT SHOW DATABASES ON *.* TO `app_role` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `customer_%`.* TO `app_role` | | GRANT SELECT ON `mysql`.`db` TO `app_role` | | GRANT SELECT ON `mysql`.`proxies_priv` TO `app_role` | | GRANT SELECT ON `mysql`.`columns_priv` TO `app_role` | | GRANT SELECT ON `mysql`.`roles_mapping` TO `app_role` | | GRANT SELECT ON `mysql`.`user` TO `app_role` | | GRANT SELECT ON `mysql`.`tables_priv` TO `app_role` | | GRANT SELECT ON `mysql`.`procs_priv` TO `app_role` | +----------------------------------------------------------------------+

Oli Sennhauser added a comment - 2024-02-26 14:32

As soon as 23.08.5 is out I will run the next tests with all your suggestions...

Oli Sennhauser added a comment - 2024-02-26 14:32 As soon as 23.08.5 is out I will run the next tests with all your suggestions...

Oli Sennhauser added a comment - 2024-03-18 11:02

I tried it out with 23.08.5 again and it it seems to work. Stupid me: It seems to be a grep error: grep customer_00.* vs grep customer_00.*1
From my point of view this issue can be closed.

Oli Sennhauser added a comment - 2024-03-18 11:02 I tried it out with 23.08.5 again and it it seems to work. Stupid me: It seems to be a grep error: grep customer_00.* vs grep customer_00.*1 From my point of view this issue can be closed.

markus makela added a comment - 2024-03-18 11:15

I'll close this as Not a Bug in that case. Thank you for reporting back.

markus makela added a comment - 2024-03-18 11:15 I'll close this as Not a Bug in that case. Thank you for reporting back.

People

Assignee:: markus makela

Reporter:: Oli Sennhauser

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2024-02-13 13:52

Updated:: 2024-03-18 11:15

Resolved:: 2024-03-18 11:15

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB MaxScale