0

The table looks like this

mysql> DESCRIBE tenants;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   |     | NULL    |                |
| domain     | varchar(255) | NO   | UNI | NULL    |                |
| database   | varchar(255) | NO   | UNI | NULL    |                |
| created_at | timestamp    | YES  |     | NULL    |                |
| updated_at | timestamp    | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> INSERT INTO tenants(name,domain,database) VALUES ('varun','varun.localhost','sms_varun');
ERROR 1064 (42000): 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 'database) VALUES
('varun','varun.localhost','sms_varun')' at line 1

I am using mysql Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)

Progman
  • 16,827
  • 6
  • 33
  • 48
Vromahya
  • 51
  • 6
  • Maybe try a space after "INSERT INTO tenants"? – J Griffiths Sep 06 '22 at 10:53
  • 2
    Also, maybe try using backticks, some of your column names might be reserved keywords https://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql – J Griffiths Sep 06 '22 at 10:55
  • 1
    https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-D – P.Salmon Sep 06 '22 at 10:58

1 Answers1

3

DATABASE is a reserved MySQL keyword (see here), so you will have to escape it in your insert statement (and forever) to make it work:

INSERT INTO tenants (name, domain, `database`)
VALUES ('varun', 'varun.localhost', 'sms_varun');

You should avoid using reserved MySQL keywords for your column and table names.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360