PHP Tuning / PHP Config

After install, getting lot of Warning / Notice in my application e.g. Notice: Undefined index: isimg in /Library/WebServer/Documents/myproject/products/prodList.php on line 459 Fix was changed following var in php.ini From error_reporting=E_ALL & ~E_DEPRECATED & ~E_STRICT To error_reporting=E_ALL & ~E_NOTICE Don't forget to restart apache for the change to take effect. On my Mac17 files in vi /Applications/XAMPP/etc/php.ini

MySQL change sroage for all tables

MySQL move from MyISAM to InnoDB Simple dynamic SQL to change stoage type in MySQL SELECT concat('alter table ', table_name, ' engine=innodb;') SQL_strg FROM information_schema.tables WHERE table_schema = 'db_dev_db' and engine='MyISAM';

Tomcat & JDBC error connecting to 19c

ORA-28040: No matching authentication protocol

This is quite generic error.
This fixed worked for me.

1. Find the JDBC driver that is being used, jdbc.jar can reside in many location e.g.

$CATALINA_HOME/lib/

webapps/myApp/WEB-INF/lib

2. Updated the correct JDBC in your location

3. Restart tomcat

Count Actual Rows of Every Tables (MySQL)

On root database run, 1st change to your_db_name: SELECT CONCAT('SELECT ''',table_name,''', COUNT(*) FROM ', table_name, ' union all') FROM information_schema.tables WHERE table_schema = 'your_db_name'; Then connect to your database and remove the last 'union all' and add ';' then run sql e.g. MariaDB > use db_dev_01; MariaDB [db_dev_01]> SELECT 'APPLICATIONS', COUNT(*) FROM APPLICATIONS union all -> SELECT 'ATTRIBUTE', COUNT(*) FROM ATTRIBUTE union all .... .... .... -> SELECT 'ZBS_LOG', COUNT(*) FROM ZBS_LOG;

Mac Mini MySQL 5.6 start stop

MySQL 5.6 on Mac Start / Stop # launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist # launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist # mysql -u root -p Update config to tune MySQL cache cd /etc vi my.cnf

Mac MySQL tuning - basic

mysql> show variables like 'query_cache_%' ; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 5 rows in set (0.07 sec) Above is 1MB for both _limit and _size My 2022 Tuning setting # cd /usr/local/mysql # vi my.cnf query_cache_limit = 2M query_cache_size = 256M query_cache_type = 1 innodb_buffer_pool_size=1G mysql> show variables like 'query_ca%'; +------------------------------+-----------+ | Variable_name | Value | +------------------------------+-----------+ | query_cache_limit | 2097152 | | query_cache_min_res_unit | 4096 | | query_cache_size | 268435456 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+-----------+ 5 rows in set (0.00 sec) Start Stop MySQL (5.6.22) on Mac mini Catalina # /usr/local/mysql/support-files/mysql.server stop Shutting down MySQL . SUCCESS! # /usr/local/mysql/support-files/mysql.server start Starting MySQL .. SUCCESS! # On my 2022 install, Mac Mini (2012) i5, 8GB

MySQL 8 filling up disk (Linux, Ubuntu, MySQL 8.0.22, binlog)

Disk was 100% full, noticed following folder was taking up most of the space, with lot of files like 'binlog.000024'. Quick fix was purge those files via MySQL see end of this doc. /var/lib/mysql Show variables like 'binlog%'; mysql> show variables like 'binlog%'; +------------------------------------------------+--------------+ | Variable_name | Value | +------------------------------------------------+--------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_encryption | OFF | | binlog_error_action | ABORT_SERVER | | binlog_expire_logs_seconds | 2592000 | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_rotate_encryption_master_key_at_startup | OFF | | binlog_row_event_max_size | 8192 | | binlog_row_image | FULL | | binlog_row_metadata | MINIMAL | | binlog_row_value_options | | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlog_transaction_compression | OFF | | binlog_transaction_compression_level_zstd | 3 | | binlog_transaction_dependency_history_size | 25000 | | binlog_transaction_dependency_tracking | COMMIT_ORDER | +------------------------------------------------+--------------+ 23 rows in set (0.01 sec) Note : 2592000 (seconds) / 60 / 60 / 24 is 30 days. Now added to config file # vi /etc/mysql/my.cnf [mysqld] binlog_expire_logs_seconds=172800 Restart MySQL # service mysql stop # service mysql start mysql> show variables like 'binlog%'; +------------------------------------------------+--------------+ | Variable_name | Value | +------------------------------------------------+--------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_encryption | OFF | | binlog_error_action | ABORT_SERVER | | binlog_expire_logs_seconds | 172800 | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_rotate_encryption_master_key_at_startup | OFF | | binlog_row_event_max_size | 8192 | | binlog_row_image | FULL | | binlog_row_metadata | MINIMAL | | binlog_row_value_options | | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlog_transaction_compression | OFF | | binlog_transaction_compression_level_zstd | 3 | | binlog_transaction_dependency_history_size | 25000 | | binlog_transaction_dependency_tracking | COMMIT_ORDER | +------------------------------------------------+--------------+ 23 rows in set (0.01 sec) Manullay purge log from MySql, files are deleted from filesystem (worked fine on 16 Jan 2021) PURGE BINARY LOGS BEFORE '2020-12-28 22:46:26';