ERROR CREATING TABLE

Hi @ketr64,

Which steps do you perform to create the table? What error message do you get? Can you add some screenshots?

You create table statement looks good. I can create the table from the MySQL command line, Data Explorer and Data Designer. If you run your script from the Data Explorer, please make sure you create table statement end with a ; and a new line character directly behind the ;

Best regards,
Peter

To create the table I used the Data Explorer page. I entered my information, saved the design and then used the button ‘create table’. So I did not ‘touch’ the script the plugin submits.
I have to say that my MySQL db is version 8.0.20 on AWS Lightsail. Maybe something to consider.

Fyi I tried the same thing on a 5.7.30 MySQL db and I got the same error message.
I have one error in the top ‘banner’ which is ‘ERROR: CREATE TABLE failed’ and at the bottom of the page
The following CREATE TABLE statement failed
CREATE TABLE TEST1
(COL1 varchar(100) NULL
) ENGINE InnoDB DEFAULT CHARACTER SET utf8 COLLATE=utf8_general_ci;

I am running WP v5.4.2 and my environment is on AWS Lightsail.

Hi @ketr64,

Can you add a screenshot of the error?

Thanks,
Peter

Yes. I can. But how do I do that? Do you an email @ ? I don’t seem to be able to do it with a post.

Hi @ketr64,

You can use this upload service:
https://nl.imgbb.com/

Thanks,
Peter

There you go.

One link shows that the db cnx works and the other the page with the error message.

https://ibb.co/RNjHXhQ

https://ibb.co/gzqqc89

Hi @ketr64,

Thanks, that helps! I wasn’t aware you were trying to create a table using a remote connection. Your create table statement looks good, so you might not have the create table privilege on the remote database. You might also be able to see the reason of your error if you scroll down a little on the Data Designer page.

Can you please check if your remote user has the create table privilege?

Thanks,
Peter

Hi Peter,

This is really strange. If I wouldn’t have the remote privilege to create a table then I would not be able to create a table from the WP server with mysql. Because when I use the script your plugin generate to create the table and execute it in mysql from that WP server it works. With the same user.

Here is the result of the show grants;:
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO nttyadmin@% WITH GRANT OPTION |

Is it the right setting?

Thanks

Rene

PS: I don’t see any additional error message on the page. What is at the bottom of the page is:
The following CREATE TABLE statement failed
CREATE TABLE TABLE2
(COL1 varchar(10) NULL
) ENGINE InnoDB DEFAULT CHARACTER SET utf8 COLLATE=utf8_general_ci;

Hi Rene,

A user can have different privileges depending on the host it connects from. You might have the create table privilege if you connect from one server, and not when you connect from another server.

Regarding the sensitivity of this topic, I suggest you use the contactform on the plugin website (wpdataaccess.com) to send me a mail. We can then continue the discussing in private, which allows us to share more sensitive data. Is that OK for you?

Best regards,
Peter

sure. will do.