What is the reason for escaping tables and rows in the database dump with backticks?

[dancesWithCycles]

Hi folks, Thank you so much for providing and maintaining the repository.

Here and in the following lines of the dump you see backtick escaped tables and rows.

The source of the dump is described like this.

-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jan 28, 2023 at 12:14 AM
-- Server version: 10.4.27-MariaDB
-- PHP Version: 8.0.25

Spoiler Alert: I am neither an expert of phpMyAdmin nor MadiaDB.

The backtick escaping is a surprise for me being used to work with the PostgreSQL DBMS. When I import this dump into a PostgreSQL DBMS instance, it is complaining like this.

 $ psql -h localhost -p 5432 < ../Database/community_caring.sql -L ./psql-logfile.log
Password: 
ERROR:  unrecognized configuration parameter "sql_mode"
START TRANSACTION
ERROR:  unrecognized configuration parameter "time_zone"
ERROR:  syntax error at or near "`"
LINE 1: CREATE TABLE `admin` (
                     ^
ERROR:  syntax error at or near "`"
LINE 1: INSERT INTO `admin` (`id`, `email`, `name`, `password`) VALU...
                    ^
ERROR:  syntax error at or near "`"
LINE 1: CREATE TABLE `area_shared` (
                     ^
ERROR:  syntax error at or near "`"
LINE 1: CREATE TABLE `chat` (
                     ^
ERROR:  syntax error at or near "`"
LINE 1: INSERT INTO `chat` (`id`, `time`, `from`, `message`) VALUES
                    ^
ERROR:  syntax error at or near "`"
LINE 1: CREATE TABLE `contact` (
                     ^
ERROR:  syntax error at or near "`"
LINE 1: CREATE TABLE `email_templates` (
                     ^
invalid command \'s
ERROR:  syntax error at or near "`"
LINE 1: INSERT INTO `email_templates` (`id`, `type`, `name`, `subjec...
                    ^

As a consequence, the dump is not imported and the database is empty.

I am facing this backtick escaping the first time. Is that a flavor of phpMyAdmin or MariaDB. If it is an App specific flavor, shouldn't the export result in a SQL standard compliant dump that can be imported in any other SQL standard compliant DBMS?

Cheers!

[faai5200]

Hello there,

Thank you for reaching out and sharing your experience. It seems you're encountering an issue while trying to import the dump into a PostgreSQL DBMS instance due to the specific syntax used in the dump.

The backtick escaping you're seeing is a feature specific to MySQL and its derivatives like MariaDB but it's not a part of the SQL standard, which is why you're facing compatibility issues when trying to import this MySQL-style dump into PostgreSQL.

As for your question about whether this backtick escaping is a flavor of phpMyAdmin or MariaDB, it's important to note that this backtick usage is a MySQL-specific feature. It's not a phpMyAdmin-specific feature, though phpMyAdmin supports this syntax since it's widely used in MySQL environments.

I think a general solution could be to replace the backtick escaping throughout. You can use a text editor or a command-line tool to replace all backticks with double quotes.

Adjusting the dump as mentioned above should help you successfully import the data into PostgreSQL without any syntax errors. If you encounter any specific challenges during this process, feel free to reach out for further assistance.

Thanks!