Dev {Tricks}

You are here: Home / Archives for Server and Hosting

by

How to change the email sending limit in DirectAdmin?

SSH method

Login to SSH as root and Run below line.

echo 500 > /etc/virtual/bdhostit.com/limit/helpdesk1

The above command will increase daily email sending limit to 500 of helpdesk1@bdhostit.com.
Change the bold text replacing your details.

 

by

Unable to read the User data files for username

  • Could not execute your request
  • Domain does not exist on the system. Unable to find domaincom in /etc/virtual/domainowners, and domain is not set as hostname (servername) in DirectAdmin configuration. Exiting…
    no valid domain found – exiting
  • Unable to read the User data files for username

This would mean that the account likely already exists on the system in /etc/passwd, and the the data in /usr/local/directadmin/data/users/username does not exist.

  • If there is not yet any data for the User on the system, then remove the account from the system, and try the restore again.
userdel -r username
  • If there is data setup everywhere that you do not want to delete, then try repairing the DA account with:
cd /usr/local/directadmin/scripts
./fix_da_user.sh username user domain.com

 

by

How To convert all MySQL tables from MyISAM into InnoDB Storage engine

Purpose:

To solve the error “General error: 1005 Can’t create table ` `.`review_round_files` (errno: 150 “Foreign key constraint is incorrectly formed”)”

We will convert MySQL tables from MyISAM into InnoDB in two different ways.

  1. PhpMyAdmin (suitable for shared hosting)
  2. SSH Methed (suitable for vps, vds, dedicated server owner)

PhpMyAdmin

  • Login to PhpMyAdmin
  • run the query below (Replace DBNAME to your database name) 
    SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;')
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE'
  • You will get result like below 
    ALTER TABLE DBNAME.access_keys ENGINE=InnoDB;
ALTER TABLE DBNAME.announcement_settings ENGINE=InnoDB;
ALTER TABLE DBNAME.announcement_type_settings ENGINE=InnoDB;
ALTER TABLE DBNAME.announcement_types ENGINE=InnoDB;
ALTER TABLE DBNAME.announcements ENGINE=InnoDB;
ALTER TABLE DBNAME.article_event_log ENGINE=InnoDB;
ALTER TABLE DBNAME.auth_sources ENGINE=InnoDB;
  • If you don’t see full line text like above, you have to activate the “Full Text” option just above the result.
  • Now copy the result and paste it in PhpMyAdmin query again and wait for the conversion.
  • You will get success message.
  • That’s all.

SSH Method

  • Login to MySQL/MariaDB
mysql -u root -p
  • Then run below command to get conversion commands.
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'yourdb';

Replace yourdb to your database name. And the conversion commands will be used later.

  • Now switch to the database
use yourdb
  • Then copy each line of conversion commands and paste it. For example
ALTER TABLE pln_deposit_objects ENGINE=InnoDB;
ALTER TABLE issues ENGINE=InnoDB;
ALTER TABLE custom_section_orders ENGINE=InnoDB;
ALTER TABLE access_keys ENGINE=InnoDB;
ALTER TABLE journals ENGINE=InnoDB;
ALTER TABLE notification_subscription_settings ENGINE=InnoDB;
ALTER TABLE notifications ENGINE=InnoDB;
ALTER TABLE review_object_types ENGINE=InnoDB;
ALTER TABLE filter_settings ENGINE=InnoDB;
ALTER TABLE users ENGINE=InnoDB;

That’s all.

 

Related scripts you may need.

Convert all DBNAME’s InnoDB Tables to MyISAM

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=MyISAM;')
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'

Convert all DBNAME’s MyISAM Tables to InnoDB

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;')
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE'

Convert all InnoDB Tables to MyISAM (all databases)

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=MyISAM;') 
FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';

Convert all MyISAM Tables to InnoDB (all databases)

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=InnoDB;') 
FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';

 

by

Moving mysql database to another user | DirectAdmin

  • Moving a database to a different user

To move a database from one user, to another user,  run the following command:

VERBOSE=1 DBUSER="da_admin" DBPASS="da_adminpass" USERNAME="username" NEWUSERNAME="newusername" /usr/local/bin/php /usr/local/directadmin/scripts/change_database_username.php
  • Replace all bold text with the appropriate info.
  • Ggrab the da_adminpass from /usr/local/directadmin/conf/mysql.conf