MySQL Insert Into advanced query syntax error

How To
1

Syntax that inserts multiple records in a MySQL database table works on PhpMyAdmin but not when calling from Make MySQL Advanced Query.

A simplified, one-record command looks like this:

INSERT INTO media (First_Name) VALUES (‘John’);

This works in PhpMyAdmin. In Make Mysql Advanced query, it generates:

Failed to refresh metadata. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘​’ at line 3

At first I thought this was a multi-record syntax issue, so I kept simplifying it, and it turned out the module generated an error message for a one-field/one-record insertion.

Ultimately I would like to pass this insertion, which does work fine in PhpMyAdmin but not in Make:

INSERT INTO media(Listicle_ID, Article_ID, Post_ID, Target_Keyword) values (1,‘a’,‘b’,‘my word’),(1,‘a’,‘b’,‘my word’),(1,‘a’,‘b’,‘my word’);

Thank you for any insights!

2

Hi @Caroline_Bogart,

Make may be trying to do more than just run the insert statement. It could also be trying to read the schema to make sense of the data being returned from your database. The Make documentation (https://www.make.com/en/help/app/mysql) mentions:

Remote access must be allowed, and CREATE ROUTINE privilege must be granted on the MySQL server!

and

ensure the account you are using to connect to your database has read access to information_schema.tables , information_schema.columns , information_schema.routines , and information_schema.parameters tables

Does your account have access to those?

It also looks like your server is running MariaDB, not plain MySQL. I notice that phpMyAdmin “supports a wide range of operations on MySQL and MariaDB”. But it may be that the Make MySQL tool isn’t compatible with MariaDB. :grimacing:

Let us know if you manage to get it working.

Regards, Terry.

3

hi and thank you for the info.
Here’s the db info:

Database server

  • Server: Localhost via UNIX socket
  • Server type: MariaDB
  • Server connection: SSL is not being
  • Server version: 10.11.9-MariaDB-cll-lve - MariaDB Server
  • Protocol version: 10
  • Server charset: cp1252 West European (latin1)

Web server

  • cpsrvd 11.118.0.16
  • Database client version: libmysql - mysqlnd 8.3.10
  • PHP extension: mysqli
  • PHP version: 8.3.10

phpMyAdmin

  • Version information: 5.2.1

The remote user has all permissions allowed.
I didn’t know it ran mariadb, it’s just the web database associated with my web hosting. Maybe I can get them to change it to something more compatible.

This is interesting, I guess I could assign permissions at the row level, I don’t think there’s a console command to grant these – but I don’t think anything would work if these were off, so I’ll look into it.

ensure the account you are using to connect to your database has read access to information_schema.tables , information_schema.columns , information_schema.routines , and information_schema.parameters tables