Dev {Tricks}

You are here: Home / Archives for Server and Hosting / MySQL

by

Solved: Can’t save module, exceeds limits | Joomla

  • Save failed with the following error the content exceeds allowed limits
  • Can’t save module, exceeds limits

Solution

The error message is triggered by the size limit of the text that can be stored into the column ‘content’ in the modules table. The text limit is 65535 in the MySQL database.

  • Login to your hosting panel (For example cPanel, Plesk, DirectAdmin, CWP etc.)
  • Now open your database in phpMyAdmin
  • Check the definition of the column ‘content’ and it should be ‘text’ and we have to make it ‘mediumtext’.

That’s all!

If not solved, please change Default editor to TinyMCE.

by

Update a column value, replacing part of a string

  • How to Replace Part of a String in MySQL
  • Replace part of string in mysql table column
  • Find and Replace text in the entire table
  • MySQL REPLACE String Function: Find & Replace Text

Replace the bold text as per your table name, field/column name, strings and conditions.

UPDATE table_name
SET field_name = REPLACE(field_name, 'string_to_find', 'string_to_replace')
WHERE conditions;

Example,
Table name: publication_settings
Field/column name: setting_value
String to change: Copyright © to ©

where conditions
Field/column name: setting_name
Field string/value: abstract

UPDATE publication_settings
SET setting_value = REPLACE(setting_value, 'Copyright ©', '©')
WHERE setting_name LIKE ('abstract');

 

by

MySQL database table “in use” problem

  • MySQL table collation suddenly changed to “in use”
  • Unable to login, registration, reset password

Backup database.

Error occurs when you set the connection collation to anything other then the current server collation.

So

  • Repair database (repairing database will fix the “in use” issue)
  • Then change the collation to same everywhere.
  • You may also check the collation in the config file.

That’s all!

by

How to increase memory limit?

  • Fatal error: Allowed memory size of 123456 bytes exhausted (tried to allocate 234567 bytes) in /path/file.php

Usually your php.ini will be located at (XX – PHP version, for example 73):

vi /usr/local/php73/lib/php.ini

or use below trick to find your php.ini would be to ask php itself what it’s using, eg:

/usr/local/bin/php --ini | grep 'Loaded Configuration File'

or another trick to find php.ini

php -i | grep "php.ini"

Now open the php.ini file and edit memory_limit to a higher value.

memory_limit = 8M      ; Maximum amount of memory a script may consume (8MB)

Save, and then restart apache.

systemctl start httpd.service

CentOS/RHEL (Red Hat) Linux version 7.x or newer specific commands

or

sudo systemctl restart httpd.service

Alternative way: php.ini per folder

Location of the global server php.ini file is

/usr/local/php/php.ini

Location of the global per account php.ini file is

/home/USERNAME/php.ini

You can also have per folder changes, example:

/home/USERNAME/public_html/website1.com/php.ini
/home/USERNAME/public_html/website2.com/php.ini
/home/USERNAME/public_html/website2.com/myapp/php.ini

How to test php.ini changes?
Simple create phpinfo.php file in the users public_html folder and add the following content in it.

Example location: /home/USERNAME/public_html/phpinfo.php

<?php
phpinfo();
?>

Now open url in your browser (Apache restart is NOT needed):

http://your-domain.com/phpinfo.php

PHP.ini values

http://php.net/manual/en/ini.list.php

 

by

How To Find Duplicate Values in MySQL

Find duplicate values in one column

SELECT 
    col, 
    COUNT(col)
FROM
    table_name
GROUP BY col
HAVING COUNT(col) > 1;

Find duplicate values in multiple columns

SELECT 
    col1, COUNT(col1),
    col2, COUNT(col2),
    ...

FROM
    table_name
GROUP BY 
    col1, 
    col2, ...
HAVING 
       (COUNT(col1) > 1) AND 
       (COUNT(col2) > 1) AND 
       ...