Таблица 6,5 млн записей.
На ней выполняю сложный
SELECT с кучей подзапросов и агреггирований.
Запрос крашится с ошибкой:
ERROR 1114 (HY000): The table C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql27d8_8_1d' is full
Рабочие параметры:
innodb_data_file_path = ibdata1:12M:autoextend
tmp_table_size = 8192M
max_heap_table_size = 8192M
Пишу:
innodb_data_file_path = ibdata1:1024M:autoextend
Сервер не стартует:
2022-04-28T17:21:21.841751Z 1 [ERROR] [MY-012263] [InnoDB] The Auto-extending innodb_system data file '.\ibdata1' is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 65536 pages, max 0 (relevant if non-zero) pages!
2022-04-28T17:21:21.845751Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2022-04-28T17:21:21.850752Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2022-04-28T17:21:21.852752Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-04-28T17:21:21.856752Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-04-28T17:21:21.857756Z 0 [System] [MY-010910] [Server] S:\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.26) MySQL Community Server - GPL.
Пишу:
innodb_data_file_path = ibdata2:1024M;ibdata1:12M:autoextend
Сервер не стартует:
2022-04-28T17:23:24.270754Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2022-04-28T17:23:24.271754Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2022-04-28T17:23:24.271756Z 0 [System] [MY-010116] [Server] S:\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.26) starting as process 10420
2022-04-28T17:23:24.295755Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-28T17:23:25.781840Z 1 [ERROR] [MY-012611] [InnoDB] Operating system error number 2 in a file operation.
2022-04-28T17:23:25.785840Z 1 [ERROR] [MY-012646] [InnoDB] File .\ibdata2: 'open' returned OS error 71. Cannot continue operation
2022-04-28T17:23:25.788840Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
Если поставить в запрос
limit 1000000 запрос выполняется.
Места на дисках полно.
Не понимаю что ещё ему надо увеличить? И почему не получается создать или увеличить
innodb_data_file_path?
Простой