Member Avatar for Ole Raptor

I am having issues with the following script. I don't see anything wrong here. Very basic but it is giving me a headache. Any suggestions are welcome. I am running version 5.1.37 and the error is:

Error Code : 1064
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 ''visit_recipes'
('id' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
'name' text NO' at line 1
(0 ms taken)

CREATE TABLE 'visit_recipes' 
('id' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
'name' text NOT NULL, 'ingredients' text NOT NULL, 
'instructions' text NOT NULL, 'created_by' INTEGER NOT NULL, 
'userip' VARCHAR(16), 'date_added' text NOT NULL, PRIMARY KEY('id'));
  • 2 Contributors
  • 1 Reply
  • 990 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by griswolf

Dani AI

Generated

reported a 1064 on CREATE TABLE; correctly traced it to an identifier-quoting problem. Beyond fixing the immediate syntax, the table can be made more robust and future-proof with a few schema and server-aware changes.

CREATE TABLE visit_recipes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL,
  ingredients MEDIUMTEXT,
  instructions MEDIUMTEXT,
  created_by INT UNSIGNED,
  userip VARCHAR(45),
  date_added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Notes and checklist:

  • Use a datetime/timestamp column for date_added rather than plain text so you can use DEFAULT CURRENT_TIMESTAMP and efficient date queries.
  • userip sized to 45 chars covers IPv6; keep it nullable if not always available.
  • MEDIUMTEXT/TEXT choices depend on expected length; VARCHAR is preferable for short fields like name.
  • Prefer InnoDB for transactions and foreign keys; add a foreign-key constraint on created_by if you have a users table.
  • On older servers (the OP mentioned 5.1.37), InnoDB fulltext search is not available — fulltext required MyISAM there; InnoDB fulltext arrived in later MySQL releases, so either use MyISAM for fulltext or upgrade.
  • Common causes of persistent 1064: stray non-ASCII or “curly” quotes from copy/paste, missing semicolons, or running the statement in a client with different SQL mode. Verify SELECT VERSION(); and check for typographic quotes.
Member Avatar for griswolf

You appear to be translating from (postgresql?) where type text is the best option. In MySQL, varchar(size) is a better choice if possible. However, it does work, so I have not changed it. The syntax issue is the single quotes. If you want to quote table or column names, you need to use back-quote (`) not a normal quote ('). I've just removed your wrong quotes. This works for me:

CREATE TABLE visit_recipes (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  name text NOT NULL, 
  ingredients text NOT NULL, 
  instructions text NOT NULL, 
  created_by INTEGER NOT NULL, 
  userip VARCHAR(16), 
  date_added text NOT NULL 
);
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.