MySQL limitations due to shared hosting

MySQL limitations due to shared hosting

On DreamHost shared accounts, some of the SQL commands listed in the MySQL documentation link below or in references such as the O'Reilly book Managing & Using MySQL by Reese, et al., are not available for scripting nor in phpMyAdmin.

Their functionality is available instead through the DreamHost control panel. 

These include:

  • CREATE DATABASE: only works from the shell if you previously created a database with the specified same name using your DreamHost Panel and then dropped it at some point. You should use --drop-database with mysqldump when backing up a database.
  • GRANT
  • REVOKE

The following table summarizes the privileges a MySQL user at DreamHost may have, as compared to all available privileges.

Privilege Meaning Availability
ALL [PRIVILEGES] Grant all privileges at specified access level except GRANT OPTION NO
ALTER Enable use of ALTER TABLE YES
ALTER ROUTINE Enable stored routines to be altered or dropped (Only if you are the user@host DEFINER that created the routine) YES
CREATE Enable database and table creation (Only tables can be created, Databases must be created in the control panel) YES
CREATE ROUTINE Enable stored routine creation (Cannot set DEFINER as non-root user, please remove DEFINER='user'@'host' combo to create routines) YES
CREATE TEMPORARY TABLES Enable use of CREATE TEMPORARY TABLE YES
CREATE USER Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES NO
CREATE VIEW Enable views to be created or altered YES
DELETE Enable use of DELETE YES
DROP Enable databases, tables, and views to be dropped YES
EVENT Enable use of events for the Event Scheduler NO
EXECUTE Enable the user to execute stored routines YES
FILE Enable the user to cause the server to read or write files (unnecessary, if you get this error you are likely using "LOAD DATA INFILE" and should use "LOAD DATA LOCAL INFILE" instead) NO
GRANT OPTION Enable privileges to be granted to or removed from other accounts NO
INDEX Enable indexes to be created or dropped YES
INSERT Enable use of INSERT YES
LOCK TABLES Enable use of LOCK TABLES on tables for which you have the SELECT privilege YES
PROCESS Enable the user to see all processes with SHOW PROCESSLIST (shows only your specific MySQL users processes) YES
REFERENCES Not implemented NO
RELOAD Enable use of FLUSH operations NO
REPLICATION CLIENT Enable the user to ask where master or slave servers are NO
REPLICATION SLAVE Enable replication slaves to read binary log events from the master NO
SELECT Enable use of SELECT YES
SHOW DATABASES Enable SHOW DATABASES to show all databases (Only shows those databases you have access to) NO
SHOW VIEW Enable use of SHOW CREATE VIEW YES
SHUTDOWN Enable use of mysqladmin shutdown NO
SUPER Enable use of CHANGE MASTER TO, KILL, PURGE BINARY LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached NO
TRIGGER Enable triggers to be created or dropped (Cannot set DEFINER as non-root user, please remove DEFINER='user'@'host' combo to create trigger) YES
UPDATE Enable use of UPDATE YES
USAGE Synonym for “no privileges” NO

 

Did this article answer your questions?

Article last updated .