Posts

Stefan Helander

Ispconfig not processing job queue – spamfilter_policy error

/0 Comments/in /by

Suddenly some of my slave servers stopped processing the job queue. Ispconfig version 3.2.9. Investigating the log files.

/var/log/ispconfig/cron.log shows:

PHP Fatal error:  Uncaught mysqli_sql_exception: Data truncated for column 'warnvirusrecip' at row 1 in /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php:302

See this discussion: https://forum.howtoforge.com/threads/uncaught-mysqli_sql_exception-data-truncated-for-column-warnvirusrecip-at-row-1.90116/

This is how I solved it. On the master server:

mysqldump -uroot -p --add-drop-table dbispconfig spamfilter_policy > spamfilter_policy.sql

Then copy the spamfilter_policy.sql to each slave server having problems processing the queue and do:

mysql -uroot -p dbispconfig < spamfilter_policy.sql

Erik Svensson

ERROR 1067 (42000) at line xxx: Invalid default value for ‘field’

/0 Comments/in , /by

This is beacuse mysql server 5.7 have changed the the date time default field 0000-00-00 00:00:00 options. You can only have one field with the value 0000-00-00 00:00:00. Where fore you have to change the default indata mask to.

datetime NOT NULL DEFAULT ‘1000-01-01 00:00:00’
or
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

exampel
ALTER TABLE testdate CHANGE datestart datestart DATETIME NOT NULL DEFAULT ‘1000-01-01 00:00:00’;

Erik Svensson

How to change mysql table engine MyISAM to InnoDB

/0 Comments/in /by

login to mysql shell as root.
locate the database where the tables are situated.
SET @DATABASE_NAME = ‘name_of_your_db’; // name_of_your_db = the database you want to change table engine.
SELECT CONCAT(‘ALTER TABLE ', table_name, ' ENGINE=InnoDB;’) AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = @DATABASE_NAME AND ENGINE = ‘MyISAM’ AND TABLE_TYPE = ‘BASE TABLE’ ORDER BY table_name DESC;
The result will end up in a list of the tables that needs to be changed. Copy the list and do the following.
Shift to the database involved:

USE name_of_your_db
START TRANSACTION;
insert the copied list
COMMIT;
You have now changed the engine of the tables.

Erik Svensson

[ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.user’ doesn’t exist

/0 Comments/in /by

This error ocurs then the database mysql is missing or corrupt.
Stop the mysql server “service mysql-server stop”
Make a backup of /var/db/mysql “mv /var/db/mysql /var/db/mysql.old”.
To rebuild the database execute “/usr/local/libexec/mysqld –initialize”
You will get a temporary password. Remember the password for later use.
Start the mysql server “service mysql-server start”
To start upp the new configuration “mysql_secure_installation”. Use the Password to start the configuration and step thru the wizzard.
Restore the mysql backup and the server is good as new.

Stefan Helander

MySQL Error 1215: Cannot add foreign key constraint

/0 Comments/in /by

Check that both tables have the same engine type, so there isn’t a mix of for example MyISAM and InnoDB.

Erik Svensson

MySQL 5.7 and mysql_secure_installation

/0 Comments/in /by

If you are using mysql:secure_installation witch you should, beware of that the script reset all passwords in the SQL server database mysql. To fix this problem use the following.
Root password:
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass’;
Session password:
ALTER USER ‘mysql.session’@’localhost’ IDENTIFIED BY ‘password’ PASSWORD EXPIRE NEVER;

Exampel error messages:
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Stefan Helander

Akeeba restore error – 1118 – Row size too large (>8126) [solution]

/0 Comments/in , , /by

When restoring an Akeeba backup of a WordPress site the restore process was interrupted with the error message saying 1118 – Row size too large (>8126).

Solution:

  • Use SSH to log in to your database server
  • Edit /etc/mysql/my.cnf or if you use a config file under /etc/mysql/conf.d, edit that one
  • Under the [mysqld] section add:
    internal_tmp_disk_storage_engine=MyISAM
    innodb_strict_mode = 0
  • Restart mysql:
    service mysql restart

Edit: After you successfully restored the site, remove the lines and restart MySQL. I didn’t do this and later tried to move another site from this server to another using Akeeba. When the site was installed on the new server, I just got an error message saying “Error Establishing a Database Connection”. To resolve it, I had to go back to the above server, remove the lines from mysql config, restart MySQL, make a new backup of the site using Akeeba and successfully restore it on the new server.

Credit: The solution to this problem was found here.

MySQLOracle Corporation
Stefan Helander

MySQL not sorting swedish characters ÅÄÖ correct [solved]

/0 Comments/in /by

Problem: When using ORDER BY or GROUP BY, the swedish characters Å,Ä and Ö is sorted as A and O.

Solution: add COLLATE utf8_swedish_ci to your ORDER BY or GROUP BY, for example:

SELECT field1,field2 FROM table1 ORDER BY field1 ASC

should be changed to:

SELECT field1,field2 FROM table1 ORDER BY field1 COLLATE utf8_swedish_ci ASC

Stefan Helander

Joomla! 1.5 create new admin user via MySQL and phpMyAdmin

/0 Comments/in /by

If you need to gain administrator access to a Joomla! 1.5 website where the admin password is not known (forgotten or the original admin is no longer available) you can resolve the situation by adding a new administrator user through MySQL.

For this, obviously, you need to now the database credentials. This is how you do it using MySQL and phpMyAdmin. Click on the images below to see them enlarged.

  1. Log in to phpMyAdmin using the credentials for the Joomla! database
  2. Go to the table jos_users and go to the last record. If your last user id is less than 1000 you can use the values in this example. Otherwise you have to adapt it to a user id larger than your last user id (if you have less than 1000 users in your Joomla! database user id 1000 will be fine).
  3. Insert a new record into the jos_users table:

    Joomla! 1.5 create new admin user via MySQL and phpMyAdmin step 1

    Joomla! 1.5 create new admin user via MySQL and phpMyAdmin step 1

    id = 1000
    name = A new administrator
    username = admin2
    email = admin2@example.com (or perhaps your own email address ;))
    password = d2064d358136996bd22421584a7cb33e:trd7TvKHx6dMeoMmBVxYmg0vuXEA4199
    usertype = Super Administrator
    block = 0
    sendEmail = 1
    gid = 25

    Then click on Run to save the entry

  4. Go to the table jos_core_acl_aro and insert a new record:

    Joomla! 1.5 create new admin user via MySQL and phpMyAdmin step 2

    Joomla! 1.5 create new admin user via MySQL and phpMyAdmin step 2

    id = 1000
    section_value = users
    value = 1000
    order_value = 0
    name = Administrator
    hidden = 0

    Then click on Run to save the entry

  5. Go to the table jos_core_acl_groups_aro_map and insert a new record:

    Joomla! 1.5 create new admin user via MySQL and phpMyAdmin step 3

    Joomla! 1.5 create new admin user via MySQL and phpMyAdmin step 3

    group_id = 25
    section_value = (empty)
    aro_id = 1000

    Then click on Run to save the entry

  6. Now go to the backend of your site (i.e /administrator) and log in with the username “admin2” and the password “secret” (whithout “”)
  7. Go to the user section and immediately change the password of admin2 user since “secret” is a very insecure password
Stefan Helander

Ubuntu 9.10 package mysql-server-5.1 5.1.37-1ubuntu5.1 failed to install/upgrade: subprocess new pre-removal script returned error exit status 1

/0 Comments/in /by

When trying to do an apt-get upgrade the MySQL package failed with the error message:

package mysql-server-5.1 5.1.37-1ubuntu5.1 failed to install/upgrade: subprocess new pre-removal script returned error exit status 1

The problem is that the start / stop script for MySQL fails to stop the running MySQL server before upgrade.

Solution:

apt-get update
ps ax | grep mysql

Now kill each process that has to do with MySQL.

apt-get upgrade

Voila – now it works and MySQL gets upgraded.