Additional questions about vitess

[Charles Shapiro]

1) What parameter to vttablet do I adjust to fix the error:

    vttablet: rpc error: code = ResourceExhausted desc = grpc: trying to send message larger than max 

2) How do I run a vttablet which connects to a Percona DB on a different host than the vttablet ?

3) Why would I get errors like :

failed to exec SQL insert ignore into TABLE1 (PKEY,PID) ..., reason: 1235 ...

vttablet: unsupported: cannot identify primary key of statement (CallerID: mysql_user)

failed to exec SQL update ignore TABLE1 set PID=1135058 where PID in (1145860,1231707)..., reason: 1105, vtgate: http://bd2da89d6063:15001/: syntax error at position 14 near 'ignore'

[Simon Mudd]

Hi,

On 25 Jul 2018, at 09:47, Charles Shapiro <charlie...@gmail.com> wrote:

1) What parameter to vttablet do I adjust to fix the error:

    vttablet: rpc error: code = ResourceExhausted desc = grpc: trying to send message larger than max 

I’d expect that if you need to use a larger packet size it’s because mysql’s max_allowed_packet is larger than vitess’s default.

You can fix this when starting vttablet by using: -grpc_max_message_size xxxxx

Sougou: could vttablet once it connects to mysqld auto-resize the grpc message size if needed?

I would expect that would remove the need to configure this on the tablet as it would be “automatic”.

One thing less to configure simplifies things for everyone.

2) How do I run a vttablet which connects to a Percona DB on a different host than the vttablet ?

Ideally you should talk to a local mysqld. This keeps latency between vttablet and mysqld to a minimum.

So startup another tablet on that host…

3) Why would I get errors like :

failed to exec SQL insert ignore into TABLE1 (PKEY,PID) ..., reason: 1235 ...

vttablet: unsupported: cannot identify primary key of statement (CallerID: mysql_user)

failed to exec SQL update ignore TABLE1 set PID=1135058 where PID in (1145860,1231707)..., reason: 1105, vtgate: http://bd2da89d6063:15001/: syntax error at position 14 near ‘ignore'

vitess does not yet support a large part of the MySQL SQL syntax. It supports a lot but if you have apps that have existing code

it’s quite likely that you’ll bump into things like this. It’s not idea but is the way things are at the moment.  For now I’d suggest

you file an issue at https://github.com/vitessio/vitess/issues saying that Vitess does not support a specific syntax you were

hoping to use and at least this will get recorded. In the meantime I’d remove the ‘ignore’ part and things should probably work ok.

There are plans to make Vitess offer better support for most of the SQL syntax understood by MySQL but that is simply going to

take time to implement.

Simon

[Sugu Sougoumarane]

On Wed, Jul 25, 2018 at 3:16 AM 'Simon Mudd' via vitess <vit...@googlegroups.com> wrote:

Hi,

On 25 Jul 2018, at 09:47, Charles Shapiro <charlie...@gmail.com> wrote:

1) What parameter to vttablet do I adjust to fix the error:

    vttablet: rpc error: code = ResourceExhausted desc = grpc: trying to send message larger than max 

I’d expect that if you need to use a larger packet size it’s because mysql’s max_allowed_packet is larger than vitess’s default.

You can fix this when starting vttablet by using: -grpc_max_message_size xxxxx

Sougou: could vttablet once it connects to mysqld auto-resize the grpc message size if needed?

I would expect that would remove the need to configure this on the tablet as it would be “automatic”.

One thing less to configure simplifies things for everyone.

Which version of Vitess are you running. The default grpc max message size was increased to match mysql (16MB) about 3 months ago: https://github.com/vitessio/vitess/pull/3890.

 

2) How do I run a vttablet which connects to a Percona DB on a different host than the vttablet ?

Ideally you should talk to a local mysqld. This keeps latency between vttablet and mysqld to a minimum.

So startup another tablet on that host…

I recently submitted a change that simplifies all this. If you specify a host+port or socket to vttablet, it will connect to that mysql presuming it's external. The docs have been updated accordingly: https://vitess.io/user-guide/server-configuration/#db-config-parameters.

But more detailed explanations are on the way.

 

3) Why would I get errors like :

failed to exec SQL insert ignore into TABLE1 (PKEY,PID) ..., reason: 1235 ...

vttablet: unsupported: cannot identify primary key of statement (CallerID: mysql_user)

This is probably because the database is setup to use SBR, which is the default for vitess (we need to change this). If you used RBR, this should work.

If you actually meant to use SBR: either the table doesn't have a primary key, or the insert expression for the primary key is too complex.

 

failed to exec SQL update ignore TABLE1 set PID=1135058 where PID in (1145860,1231707)..., reason: 1105, vtgate: http://bd2da89d6063:15001/: syntax error at position 14 near ‘ignore'

vitess does not yet support a large part of the MySQL SQL syntax. It supports a lot but if you have apps that have existing code

it’s quite likely that you’ll bump into things like this. It’s not idea but is the way things are at the moment.  For now I’d suggest

you file an issue at https://github.com/vitessio/vitess/issues saying that Vitess does not support a specific syntax you were

hoping to use and at least this will get recorded. In the meantime I’d remove the ‘ignore’ part and things should probably work ok.

There are plans to make Vitess offer better support for most of the SQL syntax understood by MySQL but that is simply going to

take time to implement. 

Simon

--
You received this message because you are subscribed to the Google Groups "vitess" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vitess+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Simon Mudd]

On 26 Jul 2018, at 05:28, Sugu Sougoumarane <sso...@gmail.com> wrote:

On Wed, Jul 25, 2018 at 3:16 AM 'Simon Mudd' via vitess <vit...@googlegroups.com> wrote:

Hi,

On 25 Jul 2018, at 09:47, Charles Shapiro <charlie...@gmail.com> wrote:

1) What parameter to vttablet do I adjust to fix the error:

    vttablet: rpc error: code = ResourceExhausted desc = grpc: trying to send message larger than max 

I’d expect that if you need to use a larger packet size it’s because mysql’s max_allowed_packet is larger than vitess’s default.

You can fix this when starting vttablet by using: -grpc_max_message_size xxxxx

Sougou: could vttablet once it connects to mysqld auto-resize the grpc message size if needed?

I would expect that would remove the need to configure this on the tablet as it would be “automatic”.

One thing less to configure simplifies things for everyone.

Which version of Vitess are you running. The default grpc max message size was increased to match mysql (16MB) about 3 months ago: https://github.com/vitessio/vitess/pull/3890.

I’m running a newish version of Vitess but I don’t always use the default value. Some systems use different larger values for various reasons, e.g.

 max_allowed_packet => '32M',

 max_allowed_packet => '20M',

 max_allowed_packet => '32M',

 max_allowed_packet => '64M',

 max_allowed_packet => '32M',

 max_allowed_packet => '64M',

 max_allowed_packet => '20M',

 max_allowed_packet => '1024M',

 max_allowed_packet => '1024M',

 max_allowed_packet => '128M',

 max_allowed_packet => '40M’,

comes from checking the non-default configuration settings in puppet. There’s quite a variety. Not all of these systems may go to Vitess but the point here is that vitess does need to be aware of the value

used otherwise breakage will occur at some point. (as experience confirms).

2) How do I run a vttablet which connects to a Percona DB on a different host than the vttablet ?

Ideally you should talk to a local mysqld. This keeps latency between vttablet and mysqld to a minimum.

So startup another tablet on that host…

I recently submitted a change that simplifies all this. If you specify a host+port or socket to vttablet, it will connect to that mysql presuming it's external. The docs have been updated accordingly: https://vitess.io/user-guide/server-configuration/#db-config-parameters.

But more detailed explanations are on the way.

If socket information can be provided how does that make the mysqld remote? A socket is a unix socket and is local to the machine, so maybe your definition of :external doesn’t match mine

which would be “it is on another server” ??

Simon

[Sugu Sougoumarane]

The more correct term would be "externally managed".

[Charles Shapiro]

On Thursday, July 26, 2018 at 6:28:33 AM UTC+3, Sugu Sougoumarane wrote:

On Wed, Jul 25, 2018 at 3:16 AM 'Simon Mudd' via vitess <vit...@googlegroups.com> wrote:

Hi,

3) Why would I get errors like :

failed to exec SQL insert ignore into TABLE1 (PKEY,PID) ..., reason: 1235 ...

vttablet: unsupported: cannot identify primary key of statement (CallerID: mysql_user)

This is probably because the database is setup to use SBR, which is the default for vitess (we need to change this). If you used RBR, this should work.

If you actually meant to use SBR: either the table doesn't have a primary key, or the insert expression for the primary key is too complex.

 

Even though I am now using a non-replicated single vttablet, I am still getting this error.

mysql> describe TABLE1;

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

| Field | Type             | Null | Key | Default | Extra |

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

| PKEY  | binary(20)       | NO   | PRI | NULL    |       |

| PID   | int(10) unsigned | NO   | PRI | NULL    |       |

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

Both columns in the table are in the primary key.

Using Percona 5.6 as the database.

[Sugu Sougoumarane]

Can you show the full insert statement and the exact error?

Also, are you still using SBR?

--

[Charles Shapiro]

On Thursday, August 2, 2018 at 5:16:23 PM UTC+3, Sugu Sougoumarane wrote:

Can you show the full insert statement and the exact error?

Also, are you still using SBR?

Table is defined as:

CREATE TABLE `TABLE1` (

  `PKEY` binary(20) NOT NULL,

  `PID` int(10) unsigned NOT NULL,

  PRIMARY KEY (`PKEY`,`PID`),

  KEY `TABLE1PID` (`PID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The insert command is (for example):

insert ignore into TABLE1 (PKEY,PID) values (UNHEX('deadbeefdeadbeef'),12341234);

It works perfectly fine connecting to the mysql directly, and fails with the following error when connected to the vtgate:

ERROR 1235 (HY000): vtgate: http://9e8d17b3b26d:15001/: execInsertUnsharded: target: schema1.0.master, used tablet: test-100 (9e8d17b3b26d), vttablet: rpc error: code = Unimplemented desc = unsupported: cannot identify primary key of statement (CallerID: mysql_user)

The vttablet is running with no replication at all, a single mysql and vttablet.

/vt/bin/vttablet -topo_implementation zk2 -topo_global_server_address localhost:21811,localhost:21812,localhost:21813 -topo_global_root /vitess/global -log_dir /vt/vtdataroot/tmp -tablet-path test-0000000100 -tablet_hostname '' -init_keyspace schema1 -init_shard 0 -init_tablet_type replica -health_check_interval 5s -enable_replication_reporter -backup_storage_implementation file -file_backup_storage_root /vt/vtdataroot/backups -restore_from_backup -queryserver-config-query-timeout 30000 -queryserver-config-transaction-timeout 30000 -queryserver-config-max-result-size 1999999999 -queryserver-config-transaction-cap 3300 -port 15100 -grpc_port 16100 -service_map grpc-queryservice,grpc-tabletmanager,grpc-updatestream -pid_file /vt/vtdataroot/vt_0000000100/vttablet.pid -vtctld_addr http://9e8d17b3b26d:15000/ -db-config-app-unixsocket /bigssd/percona56/mysql/mysql.sock -grpc_max_message_size 33554432

 /vt/bin/vtgate -topo_implementation zk2 -topo_global_server_address localhost:21811,localhost:21812,localhost:21813 -topo_global_root /vitess/global -log_dir /vt/vtdataroot/tmp -port 15001 -grpc_port 15991 -mysql_server_port 15306 -mysql_server_socket_path /tmp/mysql.sock -mysql_auth_server_static_file ./mysql_auth_server_static_creds.json -cell test -cells_to_watch test -tablet_types_to_wait MASTER,REPLICA -gateway_implementation discoverygateway -service_map grpc-vtgateservice -pid_file /vt/vtdataroot/tmp/vtgate.pid

And this is the my.cnf:

[mysql]

max_allowed_packet = 8M

[mysqld]

sql_mode = STRICT_TRANS_TABLES

datadir=/bigssd/percona56/mysql

socket=/bigssd/percona56/mysql/mysql.sock

user=mysql

port = 3306

max_connections = 200

key_buffer = 1M

max_allowed_packet = 8M

# table_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

# thread_concurrency = 24

innodb_read_io_threads = 24

innodb_buffer_pool_size = 70G

innodb_buffer_pool_instances = 16

innodb_additional_mem_pool_size = 20M

innodb_io_capacity = 50000

innodb_checksums = 0

innodb_log_file_size = 2000M

innodb_log_buffer_size = 1024M

innodb_flush_log_at_trx_commit = 0

innodb_file_format=Barracuda

bulk_insert_buffer_size=16M

innodb_file_per_table

transaction-isolation = READ-UNCOMMITTED

tmpdir = /bigssd/tmp

secure-file-priv = ""

performance_schema = off

server-id = 1810861327

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=1

[mysqld_safe]

thp-setting=never

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[Sugu Sougoumarane]

It's because of the 'UNHEX' expression. If you're running on SBR, you're only allowed to specify simple values for primary keys, even if you don't have any replicas.

But it will work if you change mysql to RBR.

Vitess needs to know which rows were affected by each statement (for features like update stream to work). It cannot figure this out if you have SBR because the statement goes as is into the binlog.

In RBR, it changes into the actual values of the affected rows, which is why it's allowed in RBR only.

[Charles Shapiro]

As I tried to explain before, I'm running with NO replication.  However, I don't think I gave the vttablet the correct parameters to make it use that.

What parameters do I use to tell the vttablet that I am not using replication, or to tell it I'm using RBR?

My boss is getting very impatient.  Can you recommend where we might find skilled vitess consultants for hire, who could rapidly set up configurations on amazon AWS instances for initial compatibility testing and eventual performance testing?

--Charles

[Sugu Sougoumarane]

I'll send you a separate email.