mariadb – MySQL show total row from one table when sum of shared id from 2nd table does not equal total from first table

Woke up thick this morning :/

MariaDB Mysql
Trying to do what seems like a basic query (join) on 2 tables.

1st table
Receipts
id date supplier total

2nd table
Parts
id receipt_id partType subtotal

Trying to get the row from the first table when the totals of subtotal of each parts.receipt_id does not = receipts.id.total.

My butchered attempts include:
SELECT receipts.* FROM receipts LEFT JOIN ON receipts.id=parts.receipt_id WHERE SUM(parts.subtotal) != receipts.total GROUP BY parts.receipt_id;

Current work around using compare in PHP (very inefficient)

With regards
Xander

mariadb – MySQL High Load CPU

I have problem with MariaDB , when connect analytics ~ 200
mysql started to appear 132% load and pushed the main CPU to about 40%. Making the website extremely slow
I’m using centos 7 ( 8GB ram , 2 core , NVME SSD + Nginx + php-fpm )

(client)
#password   = your_password
port        = 3306
socket      = /var/lib/mysql/mysql.sock

(mysqld)
wait_timeout=300
interactive_timeout = 300
binlog_cache_size = 128K
thread_stack = 256K
join_buffer_size = 2048K
query_cache_type = 1
max_heap_table_size = 512M
port        = 3306
socket      = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 100G
table_open_cache = 384
sort_buffer_size = 1024K
net_buffer_length = 4K
read_buffer_size = 1024K
read_rnd_buffer_size = 768K
myisam_sort_buffer_size = 16M
thread_cache_size = 128
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 8M
tmp_table_size = 512M
#sql-mode=NO_ENGINE_SUBSTITUTION

#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/home/abc.com.com/logs/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on


innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 1G
innodb_log_file_size = 128M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 2
innodb_write_io_threads = 2
innodb_buffer_pool_instances = 1


(mysqldump)
quick
max_allowed_packet = 500M

(mysql)
no-auto-rehash

(myisamchk)
key_buffer_size = 64M
sort_buffer_size = 1M
read_buffer = 2M
write_buffer = 2M

(mysqlhotcopy)
interactive-timeout

And tunning via mysqltuner

-------- Storage Engine Statistics -----------------------------------------------------------------
(--) Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA
(--) Data in InnoDB tables: 48.8M (Tables: 21)
(OK) Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
(--) innodb_stats_on_metadata: OFF
(OK) No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
(OK) There are no anonymous accounts for any database users
(OK) All database users have passwords assigned
(!!) There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
(--) Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
(--) Up for: 8m 0s (85K q (178.621 qps), 6K conn, TX: 41M, RX: 7M)
(--) Reads / Writes: 98% / 2%
(--) Binary logging is enabled (GTID MODE: ON)
(--) Physical Memory     : 7.6G
(--) Max MySQL memory    : 504.5G
(--) Other process memory: 0B
(--) Total buffers: 2.0G global + 1.0G per thread (500 max threads)
(--) P_S Max memory usage: 0B
(--) Galera GCache Max memory usage: 0B
(!!) Maximum reached memory usage: 95.5G (1250.25% of installed RAM)
(!!) Maximum possible memory usage: 504.5G (6604.94% of installed RAM)
(!!) Overall possible memory usage with other process exceeded memory
(OK) Slow queries: 0% (0/85K)
(OK) Highest usage of available connections: 18% (93/500)
(OK) Aborted connections: 0.04%  (3/6727)
(!!) name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
(OK) Query cache is disabled by default due to mutex contention on multiprocessor machines.
(OK) Sorts requiring temporary tables: 0% (0 temp sorts / 700 sorts)
(OK) No joins without indexes
(!!) Temporary tables created on disk: 88% (5K on disk / 6K total)
(OK) Thread cache hit rate: 98% (93 created / 6K connections)
(OK) Table cache hit rate: 215% (114 open / 53 opened)
(OK) table_definition_cache(400) is upper than number of tables(261)
(OK) Open file limit used: 0% (29/65K)
(OK) Table locks acquired immediately: 100% (65K immediate / 65K locks)
(OK) Binlog cache memory access: 100.00% (1144 Memory / 1144 Total)

-------- Performance schema ------------------------------------------------------------------------
(--) Performance schema is disabled.
(--) Memory used by P_S: 0B

-------- ThreadPool Metrics ------------------------------------------------------------------------
(--) ThreadPool stat is enabled.
(--) Thread Pool Size: 2 thread(s).
(--) Using default value is good enough for your version (10.0.38-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
(!!) Key buffer used: 18.7% (75M used / 402M cache)
(OK) Key buffer size / total MyISAM indexes: 384.0M/123.0K
(OK) Read Key buffer hit rate: 99.7% (355 cached / 1 reads)
(OK) Write Key buffer hit rate: 100.0% (96 cached / 96 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
(--) InnoDB is enabled.
(--) InnoDB Thread Concurrency: 0
(OK) InnoDB File per table is activated
(OK) InnoDB buffer pool / data size: 1.0G/48.8M
(OK) Ratio InnoDB log file size / InnoDB Buffer pool size: 128.0M * 2/1.0G should be equal to 25%
(OK) InnoDB buffer pool instances: 1
(--) InnoDB Buffer Pool Chunk Size not used or defined in your version
(OK) InnoDB Read buffer efficiency: 100.00% (422760044 hits/ 422761190 total)
(!!) InnoDB Write Log efficiency: 46.23% (484 hits/ 1047 total)
(OK) InnoDB log waits: 0.00% (0 waits / 1531 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
(--) Aria Storage Engine is enabled.
(OK) Aria pagecache size / total Aria indexes: 128.0M/0B
(OK) Aria pagecache hit rate: 98.6% (146 cached / 2 reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
(--) TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
(--) XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
(--) Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
(--) Galera Synchronous replication: NO
(--) No replication slave(s) for this server.
(--) Binlog format: MIXED
(--) XA support enabled: ON
(--) Semi synchronous replication Master: Not Activated
(--) Semi synchronous replication Slave: Not Activated
(--) This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/lib/mysql/dichvu.err file
    Control error line(s) into /var/lib/mysql/dichvu.err file
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Performance schema should be activated for better diagnostics
    Performance schema shouldn't be activated for MariaDB 10.0 for performance issue
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    performance_schema = ON enable PFS
    performance_schema = OFF disable PFS

Please give me advice in this case

Free -m when cpu load high

              total        used        free      shared  buff/cache   available
Mem:           7821        1421        4755         431        1644        5715
Swap:          1023           0        1023

mariadb – Recovering InnoDB database from backed up “data” folder in XAMP in Windows 10

I copied the data directory from mysql folder and uninstalled the XAMPP as the MariaDB was not starting. I installed XAMPP same version and backed up the data folder into mysql data directory. At first only copied the required database folder named production inside the mysql data directory. PhpMyAdmin showed the tables correctly though no record/row was there and it showed “Table doesn’t exist in engine”. Then I copied all the contents from the DATA folder to the mysql data folder and now it does not start. Now the problem is MariaDB is not starting. I tried setting the
innodb_force_recovery but it failed to start the mysql.

The error log is given below:

2021-06-11  0:08:23 0 (Note) InnoDB: Mutexes and rw_locks use Windows interlocked functions
2021-06-11  0:08:23 0 (Note) InnoDB: Uses event mutexes
2021-06-11  0:08:23 0 (Note) InnoDB: Compressed tables use zlib 1.2.11
2021-06-11  0:08:23 0 (Note) InnoDB: Number of pools: 1
2021-06-11  0:08:23 0 (Note) InnoDB: Using SSE2 crc32 instructions
2021-06-11  0:08:23 0 (Note) InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2021-06-11  0:08:23 0 (Note) InnoDB: Completed initialization of buffer pool
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.jobsapplied.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``jobs`.`applied``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `jobs`.`applied` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.jobsdata.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``jobs`.`data``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `jobs`.`data` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.jobsdata_employer.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``jobs`.`data_employer``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `jobs`.`data_employer` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.jobseducation.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``jobs`.`education``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `jobs`.`education` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.jobseducation_list.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``jobs`.`education_list``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `jobs`.`education_list` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.jobsindustry.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``jobs`.`industry``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `jobs`.`industry` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.jobsjobs.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``jobs`.`jobs``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `jobs`.`jobs` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.jobslogin.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``jobs`.`login``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `jobs`.`login` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.jobslogin_admini.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``jobs`.`login_admini``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `jobs`.`login_admini` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.jobslogin_employer.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``jobs`.`login_employer``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `jobs`.`login_employer` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.jobsresume.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``jobs`.`resume``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `jobs`.`resume` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.jobsselected.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``jobs`.`selected``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `jobs`.`selected` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.projectsentries.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``projects`.`entries``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `projects`.`entries` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.projectsgeoname.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``projects`.`geoname``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `projects`.`geoname` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.projectsgeonamebackup.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``projects`.`geonamebackup``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `projects`.`geonamebackup` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.projectspincodes.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``projects`.`pincodes``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `projects`.`pincodes` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.projectsspecifier.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``projects`.`specifier``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `projects`.`specifier` because it could not be opened.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 3 in a file operation.
2021-06-11  0:08:23 0 (ERROR) InnoDB: The error means the system cannot find the path specified.
2021-06-11  0:08:23 0 (ERROR) InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11  0:08:23 0 (ERROR) InnoDB: Cannot open datafile for read-only: '.projectsusers.ibd' OS error: 203
2021-06-11  0:08:23 0 (ERROR) InnoDB: Operating system error number 203 in a file operation.
2021-06-11  0:08:23 0 (Note) InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11  0:08:23 0 (ERROR) InnoDB: Could not find a valid tablespace file for ``projects`.`users``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11  0:08:23 0 (Warning) InnoDB: Ignoring tablespace for `projects`.`users` because it could not be opened.
2021-06-11  0:08:23 0 (Note) InnoDB: 128 out of 128 rollback segments are active.
2021-06-11  0:08:23 0 (Note) InnoDB: Creating shared tablespace for temporary tables
2021-06-11  0:08:23 0 (Note) InnoDB: Setting file 'D:Program FilesXAMPmysqldataibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-06-11  0:08:23 0 (Note) InnoDB: File 'D:Program FilesXAMPmysqldataibtmp1' size is now 12 MB.
2021-06-11  0:08:23 0 (Note) InnoDB: Waiting for purge to start
2021-06-11  0:08:23 0 (Note) InnoDB: 10.4.19 started; log sequence number 1515509790; transaction id 611499
2021-06-11  0:08:23 0 (Note) InnoDB: Loading buffer pool(s) from D:Program FilesXAMPmysqldataib_buffer_pool
2021-06-11  0:08:23 0 (Note) Plugin 'FEEDBACK' is disabled.
2021-06-11  0:08:23 0 (Note) Server socket created on IP: '::'.

mysql – How to create a table in mariadb with virtual columns from JSON

Below works in mysql but not in mariadb. Any suggestions?

CREATE TABLE `purchases` (
  `id` int(10) UNSIGNED NOT NULL,
  `product` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`json`,'$.product'))) stored,
  `customer` varchar(255) GENERATED ALWAYS AS (json_unquote(json_value(`json`,'$.customer'))) stored,
  `json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

mariadb – Ruby on Rails cannot access production RDS on AWS. Part 2

I can connect with Squirrel to my DB, which is created with rake db:create.
When I run rake db:migrate I get the error:

Caused by:
Mysql2::Error: Can't create table `gmrdb`.`comments` (errno: 150 "Foreign key constraint is incorrectly formed")

A GRANT ALL ON pfhp.gmrdb TO 'leder'@'%'; runs fine on squirrel, but does not change the error message in rails! Same for SET FOREIGN_KEY_CHECKS=0.

What to do to let rails create the tables? In development environment with sqlite3 it works!

This is my 20210525180118_create_comments.rb

class CreateComments < ActiveRecord::Migration(6.1)
  def change
    create_table :comments do |t|
      t.string :commenter
      t.text :body
      t.references :article, foreign_key: true

      t.timestamps
    end
  end
end

innodb – Queries freezes in MariaDB

I have a problem where queries freezes sporadically during the day. I made a script monitoring the processlist and have detected a pattern where a slow query is running (different slow queries) and then (fast) queries are stuck in different states, ie. opening tables, query end, update, etc, like this:

time (seconds)  state           query
7               Opening tables  SELECT id FROM... 
8               Opening tables  SELECT type FROM...
8               query end       UPDATE cache...
8               Opening tables  SELECT language FROM...
9               query end       INSERT INTO cache...
9               query end       INSERT INTO cache...
29              Sending data    SELECT product_id FROM...

Usually it’s write operations getting stuck, but also simple selects in state “Opening tables”. Then something happens and the queries disappears from the list. Not sure what this “something” is though. It feels like something is locking the queries but I’m not able to pinpoint what it is.

Any ideas what can be causing this problem or how I can debug it further?

MariaDB version: 10.1.41-MariaDB-0+deb9u1

SHOW GLOBAL STATUS:
https://pastebin.com/gyZNhEsP

SHOW VARIABLES:
https://pastebin.com/YAXFcz2G

wordpress – How to connect from Wordperss docker container to Mariadb docker container?

I installed WordPress docker and Mariadb docker, and Nginx as a reverse proxy, and all work just fine, but I’m trying to use something like mysql-client to log in to the DB, I tried to install mysql-client on WordPress container using Dockerfile but didn’t work.
Any way to connect from my WordPress container to the Mariadb container?

Resources I tried:

1- To install WordPress and MariaDB: https://www.atlantic.net/vps-hosting/install-wordpress-with-docker-on-ubuntu-20-04/

2- Tried to extended the same WordPress container to have MySQL inside it so maybe I can access to the DB through terminal: https://gabi.dev/2019/02/05/extending-wordpress-dockerfile-to-use-mysql-5-7-or-8-0/

mariadb – MaridDB 10.3.29 prevents me from deleting a foreign key

This is a minimal example that triggers the error

CREATE TABLE A (
  id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE B (
  what tinyint NOT NULL,

  a bigint NOT NULL,

  CONSTRAINT x FOREIGN KEY (a) REFERENCES A(id),
  CONSTRAINT y CHECK (what > 0)
);


SELECT b.a FROM B b;

ALTER TABLE B DROP CONSTRAINT x;

When i run this in a fresh MariaDB instance (docker mariadb:10.3.28) everything works fine.

When MariaDB 10.3.29 was released the query suddenly broke. So, when i run this in a fresh MariaDB instance (docker mariadb:10.3.29) i get the following error message for the last statement:

(42S22)(1054) (conn=8) Unknown column '`db`.`b`.`width`' in 'CHECK'

Some weird observations:

  • if i delete the y check and add it after dropping x everything works
  • if i remove the SELECT everything works
  • the b from the error message changes with the table alias from the SELECT statement
  • running the last statement again shows the same error
  • the error message makes no sense at all

So my question: what’s wrong with my SQL?

mysql – MariaDB remote access ERROR 1045 (28000): Access denied

mysql – MariaDB remote access ERROR 1045 (28000): Access denied – Database Administrators Stack Exchange

mariadb – LONG BLOB, can it be read and written to in chunks?

I have a long blob in a table, the intention is to read the data from a binary file and store in the database. The question is I can read the file in manageable chunks, can I populate the field in the table also in chunks or can it only be written in one pass?

If it is possible to update the record in chunks can it also be read in chunks?

DreamProxies - Cheapest USA Elite Private Proxies 100 Private Proxies 200 Private Proxies 400 Private Proxies 1000 Private Proxies 2000 Private Proxies ExtraProxies.com - Buy Cheap Private Proxies Buy 50 Private Proxies Buy 100 Private Proxies Buy 200 Private Proxies Buy 500 Private Proxies Buy 1000 Private Proxies Buy 2000 Private Proxies ProxiesLive Proxies-free.com New Proxy Lists Every Day Proxies123