Error 1030 hy000 at line 34 got error 168 from storage engine

I am not sure if this is the right Stack Exchange to post to or not (StackOverflow figured it belonged elsewhere, so maybe it will do better here?) but I keep receiving this MySQL error when I try to install Magento2 on a VPS (Centos6, MySQL 5.7.11):

SQLSTATE[HY000]: General error: 1030 Got error 168 from storage engine

I have searched around and have found that it has to do with InnoDB not being able to write to its data files. I have also found that people who have had the same 1030 error mostly receive a -1 or 28 error and both are easily identified as 1) innodb_force_recovery set to greater than 0 (I don’t have this set in my my.cnf file), and 2) not enough disk space (I have 5GB left on the drive mysql is on).

I should note that it installs anywhere from 136-162 tables fine before it stops and errors out. After that I try to add the last table it errored on manually, it then exhibits symptoms similar to this issue:

Can’t create table, but table doesn’t exist

The MySQL manual says:

«Check the %d value to see what the OS error means. For example, 28 indicates that you have run out of disk space.»

So my question(s) are:

1. Does anyone know what the 168 code means? -OR-
2. Where to find the list of «%d value» errors so I can hopefully get some idea for what else to try? I have Googled %d value mysql and other versions of these search criteria and don’t get back any results of value (that I can decipher anyway)

What I have tried thus far:

1. Adding a line to my fstab to up the storage of my /temp folder to 2GB
2. I have tried to repair tables (which actually was not a good idea, that messed up a bunch of stuff)
3. I tried to run the mysql_upgrade (I recently updated the MySQL install so I could install Magento2 so I was hoping I forgot to do that and that might work)
4. Various other tinkering-type things that probably broke something else I don’t know about…

If anyone has any insight on where these %d descriptions are or can point me in the right direction besides what’s already out there for the -1 and 28 versions of this error, I would appreciate it. I am not a Database expert at all, but I’ve never had so much trouble installing into a database before, usually it’s pretty straight forward.

EDIT:

After reviewing the log, this is the top-most part of the error and is the table it failed on:

 2016-05-15T15:08:22.610729Z 240 [ERROR] InnoDB: Unable to lock
 ./magento_test2/authorization_rule.ibd error: 37
 2016-05-15T15:08:22.610789Z 240 [ERROR] InnoDB: Operating system error
 number 37 in a file operation. 2016-05-15T15:08:22.610840Z 240 [ERROR]
 InnoDB: Error number 37 means 'No locks available'
 2016-05-15T15:08:22.610879Z 240 [Note] InnoDB: Some operating system
 error numbers are described at
 http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
 2016-05-15T15:08:22.610896Z 240 [ERROR] InnoDB: Cannot create file
 './magento_test2/authorization_rule.ibd' 2016-05-15T15:08:23.025333Z 0
 [ERROR] InnoDB: Unable to lock ./magento_test2/quote_address.ibd
 error: 37 2016-05-15 08:08:23 0x7f968ae75700  InnoDB: Assertion
 failure in thread 140284552238848 in file fil0fil.cc line 884 InnoDB:
 Failing assertion: success InnoDB: We intentionally generate a memory
 trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
 InnoDB: If you get repeated assertion failures or crashes, even
 InnoDB: immediately after the mysqld startup, there may be InnoDB:
 corruption in the InnoDB tablespace. Please refer to InnoDB:
 http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
 InnoDB: about forcing recovery. 15:08:23 UTC - mysqld got signal 6 ;
 This could be because you hit a bug. It is also possible that this
 binary or one of the libraries it was linked against is corrupt,
 improperly built, or misconfigured. This error can also be caused by
 malfunctioning hardware. Attempting to collect some information that
 could help diagnose the problem. As this is a crash and something is
 definitely wrong, the information collection process might fail.

I guess it’s just not working very well in general.

How to solve MySQL General error: 1030 Got error 139 from storage engine

Carlos Delgado

Learn how to solve the MySQL General error: 1030 Got error 139 from storage engine.

You will face this kind of error random times, where an insert statement will fail, causing the fatal error 139. It happens normally in production, when the user inserts a lot of text in some field of a table (although the field may be of type LONGTEXT). Innodb triggers this error when it is unable to store all of the variable-length columns for a given row on a single database page.

In this article, we’ll show you how to bypass this limitation and explain you why this happens.

Why does this happens?

Innodb has a known limit for the maximum row size, which is slightly less than the half of a database page (the actual math is 16K — (page header + page trailer)/2. For the default page size of 16kb. this sets an

8000 bytes limit on row size. This limit is a consequence of InnoDB storing two rows on a page. If you’re using compression, however, you can store a single row on a page. If your row has variable length columns and the complete row size exceeds this limit, InnoDB chooses variable length columns for off-page storage.

It’s worth mentioning that this limit applies to the byte-size of values, not the character-size. This means if you insert a 500 character string with all multi-byte characters into a VARCHAR(500) column, that value will also be chosen for off page storage. This means it is possible that you hit this error after converting from a native language character set to utf8, as it can increase the size dramatically. For example, in case that you have a table with at least 8 columns with text (TEXT, LONGTEXT) and each exceeds 768 bytes, then you’ll have at least 8448 bytes for local storage. This exceeds the limit and therefore you will face the exception.

In short words, a lot of data that InnoDB is unable to store with the current configuration. The best solution for this problem is changing the default format of your MySQL installation from Compact or Redundant to Dynamic or Compressed.

You will face normally this exception on MySQL versions equal or lower than 5.5 (MySQL 5.7 introduces the option innodb_default_row_format , which defaults to DYNAMIC, so in newer versions you will never see this exception).

1. Verify if Barracuda format is available

The solution to this problem is to set the row format of the table where you have the problem to DYNAMIC, however this options is not available when the format of the table is set to Antelope. To verify if your MySQL server has the Barracuda format enabled, you can check for example with a tool like PHPMyAdmin the row format options of any table:

If you don’t see the DYNAMIC or COMPRESSED option, you need to change the innodb_file_format to barracuda following the step #2, which normally is set Antelope. If you don’t have a tool like PHPMyAdmin available, you can open the mysql terminal and get the information with the following command:

A mysql version with this problem should output something like:

As you can see, the file format is set to Antelope, which doesn’t support the dynamic row format.

2. Change file format to Barracuda

The first thing that you need to do is to locate the configuration file of Mysql ( my.cnf in unix or my.ini in windows) and add the new 2 options with the following values on the mysqld block:

If after adding these options, the mysql server is unable to start, then the format is not supported, which means that the solution to the problem isn’t neither. You will be forced to upgrade the MySQL server version to have the DYNAMIC and COMPACT row formats available.

Restart the mysql server and repeat the first step to see if the DYNAMIC and COMPACT row formats are available or running again the following instruction in the mysql terminal:

Which should output now:

Using the innodb_file_format with Barracuda as value, you will be able to use the required Dynamic row format on the table where you have this storage engine problem.

3. Change row format of the table to DYNAMIC

Now that you have the barracuda file format configured, simply change the table row format either with the tool in PHPMyAdmin (go to the table, then operations and search for Table options and change the row_format option):

Or if you can’t use a tool, with an instruction on the mysql command line:

After modifying the row format of the table, saving a row on the database with a lot of data shouldn’t be a problem anymore and you won’t see this exception neither.

Источник

скрипт переустановки mysql — ошибка storage engine : УКМ-4

Попробовал этот скрипт, он скачал с сервера установочные файлы, но при создании БД ругается

ERROR 1030 (HY000) at line 1: Got error -1 from storage engine

Если автор или кто-то, в нем копавшийся, заглянет, подскажите, пожалуйста, в чем дело. Может, он уже доработан, и новая версия есть, я просто давно не проверял.

Вложения

create_mysql.zip (4.3 Кб, 60 просмотров)

Еще на одной кассе попробовал, теперь прочитал внимательнее.

Видно, что ukm.sql отработал, и ошибка возникает на setver.sql. Пропустил его. База создалась, ukmclient запустился и пишет «КОД НЕИЗВЕСТЕН НЕИЗВЕСТНАЯ ОШИБКА Can not convert » to j».

Видимо, осталось разобраться, что неверно в setver.sql, и дальше уже просить совета у разработчика.

Гм. После перезагрузки кассы ошибка изменилась.

КОД НЕИЗВЕСТЕН НЕИЗВЕСТНАЯ ОШИБКА Query failed: Error(1146) Table ‘ukmclient.ukm_ukmversion’ doesn’t exist: SQL SELECT MAX(version) FROM ukm_ukmversion

Захожу в mysql, не пойму, что за глюк.

Переставил еще раз. Уже нет этого глюка.

Осталось понять, что за проблема вставить строчку в таблицу.

На другой рабочей кассе смотрю.

mysql > select * from ukm_ukmversion ;
+———+—————+———————+———————+
| version | full_version | moment | cli_updates_version |
+———+—————+———————+———————+
| 74000 | | 2017 — 01 — 13 19 : 55 : 00 | 74000 |
| 75000 | | 2017 — 05 — 14 11 : 34 : 36 | 75000 |
| 75001 | | 2017 — 05 — 14 11 : 34 : 47 | 75001 |
| 78000 | | 2018 — 02 — 06 11 : 35 : 32 | 78000 |
| 78001 | | 2018 — 02 — 06 11 : 35 : 33 | 78001 |
| 78002 | | 2018 — 02 — 06 11 : 35 : 43 | 78002 |
| 83000 | | 2018 — 12 — 18 15 : 12 : 57 | 83000 |
| 83001 | | 2018 — 12 — 18 15 : 12 : 58 | 83001 |
| 83002 | | 2018 — 12 — 18 15 : 12 : 58 | 83002 |
| 83003 | | 2018 — 12 — 18 15 : 13 : 12 | 83003 |
+———+—————+———————+———————+
10 rows in set ( 0 , 00 sec )

На этой не хочет.

mysql > insert into ukm_ukmversion values ( 83000 , ‘83000’ , NOW (), 83000 );
ERROR 1030 ( HY000 ): Got error — 1 from storage engine
mysql > CREATE TABLE ukm_test (
-> version int ( 10 ) unsigned NOT NULL default ‘0’ , — Идентификатор версии
-> full_version varchar ( 15 ) NOT NULL default » , — Полный номер версии
-> moment datetime NOT NULL default ‘0000-00-00 00:00:00’ , — Дата установки версии
-> cli_updates_version int ( 10 ) unsigned NOT NULL default ‘0’ — Версия клиентских обновлений
-> );
Query OK , 0 rows affected ( 0 , 07 sec )

mysql >
mysql > insert into ukm_test values ( 83000 , » , NOW (), 83000 );
Query OK , 1 row affected ( 0 , 00 sec )

mysql > insert into ukm_ukmversion select * from ukm_test ;
ERROR 1030 ( HY000 ): Got error — 1 from storage engine
mysql > select * from ukm_test ;
+———+—————+———————+———————+
| version | full_version | moment | cli_updates_version |
+———+—————+———————+———————+
| 83000 | | 2019 — 06 — 16 11 : 54 : 18 | 83000 |
+———+—————+———————+———————+
1 row in set ( 0 , 00 sec )

mysql > drop table ukm_ukmversion ;
Query OK , 0 rows affected ( 0 , 13 sec )

mysql > rename table ukm_test to ukm_ukmversion ;
Query OK , 0 rows affected ( 0 , 00 sec )

Источник

Fixing MySQL ERROR 1030 (HY000): Got error 28 from storage engine

TL;DR You need to free-up some space on your server (or computer) or get a bigger HDD (or partition).

Today some of the pages on CSSDeck just stopped showing up. All one could see was a blank white page. Pretty scary!

What’s the one thing every developer wants? More screens! Enhance your coding experience with an external monitor to increase screen real estate.

I checked the pages across Firefox and Chrome to make sure the issue persisted on both, which was true.

Next, I quickly wget one of the webpages that wasn’t showing up and noticed something weird. It was giving me a 500 Internal Server Error . With 500 errors, I would usually suspect something wrong with the Web Server but had no dice with Apache’s /var/log/apache2/access.log or /var/log/apache2/error.log .

Checking the development environment locally, no issues were found. So, I almost guessed that something is wrong with MySQL. I quickly picked one of the SELECT SQL queries from my codebase and executed on the mysql command line on server, and here’s what the output was:

Wait, what ?! Soon I figured out error code 28 means – “No space left on device”. But why for a SELECT query ? Fetching data should not eat up any extra space, right ? Well, it depends. The data from the query needs to be stored somewhere especially if it has ORDER BY and GROUP BY . So mysql creates a temporary file on the disk as far as I know. Plus, there’s even more to be done like opening a file handle, a connection, etc. You get the idea, right ?

You can check your File System’s disk space usage with df -h . Checking the disk space usage of a file or directory is also simple with du -hs dir/ .

So just fix your drive from being out of space. In my case, I simply purged some big old backup directories.

Источник

mysql Ошибка SQL Error (1030): Got error 139 from storage engine — выход за размер записи (страницы) хранения данных

Primary tabs

Forums:

Получаю такую ошибку при попытке обновить поле типа TEXT.

• Причем в таблице полей с таким типом много (в моём случае 14 штук).
• Другие поля той же строки почти все заполнены, если в этом поле передавать коротнкое значение, то всё сохраняется, но начиная с определённого момента падает.

Как пишут люди (см. ссылки внизу) проблема состоит в том, что в mySQL InnoDB имеется ограничение места на хранение строки. Далее будем выяснять подробности.

Возможные причины

Ещё раз про возможные причины:

1. У вас на диске просто закончилось место
2. У вас «слишком много» полей типа TEXT (вообще текстовых полей)

Второй вариант рассмотрим подробнее.

Слишком много полей типа TEXT

По сути причина ошибки формулируется так:

. чтобы поддерживать как минимум 256-симольный в UTF-8 prefix индекс,
InnoDB резервирует минимум 768 байт на каждую колонку внутри данной записи [кортежа, который например добавляется или обновляется]. Если у вас 11 полей типа TEXT. то вы выходите за ограничение в 8000 байт [т.е. за половину размера страницы — при размере страницы хранения данных 16 кб = 16 000 байт]

to support at least 256-character UTF-8 column prefix indexes, InnoDB stores at least 768 bytes of each column ‘internally’ to the record. With 11 TEXT fields you will run over the 8000 byte record len limit.

Как это работает — почему мы тогда вообще можем хранить длинные поля

. как известно, MySQL хранят текстовые данные в хранилище, отдельном от табличных строк. Оказалось, что это верно только на половину. На самом деле InnoDB хранит в отдельном хранилище только «излишки», к коим он относит первые 768 байтов каждого текстового поля. Т.е. любой текст будет отъедать от длины строки столько байт, сколько он содержит, но не больше 768. Несложно подсчитать, что максимальное число текстовых полей длиной от 768 байт, которое можно безопасно хранить в одной таблице — 10.

Источник

From storage engine error code 1030

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I have tried to deploy a fresh mediawiki 1.28.2 with Azure Database for MySQL (location west europe).
The very last step of the mediawiki installer (http://uri/mw-config/) ends with an error message due to a create table query with MyISAM engine, see below (1). Do you have an idea on how to fix?

It appears, the query fails because the engine is set to MyISAM. The same query works well with engine set to InnoDB, as shown in the test below (2). I would check deeper Mediawiki manual or eventually code to understand why Mediawiki installer is trying to create some tables with MyISAM engine even Innodb mode was chosen during installation wizzard. Might need to check if Azure Database for MySQL service currently supports MySAM, my DB instance shows Support=Yes — see below (3).

1 — Issue during Mediawiki install wizzard (Innodb mode)

Setting up database. done
Creating tables.
[cdf307cab214b13c3b736a6b] /mediawiki-1.28.2/mw-config/index.php?page=Install DBQueryError from line 1054 of /home/site/wwwroot/mediawiki-1.28.2/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application’s database schema updater after upgrading?
Query: CREATE TABLE `searchindex` (
si_page int unsigned NOT NULL,
si_title varchar(255) NOT NULL default »,
si_text mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Function: Database::sourceFile( /home/site/wwwroot/mediawiki-1.28.2/maintenance/tables.sql )
Error: 1030 Got error 1 from storage engine (mymediawiki.mysql.database.azure.com)
Backtrace:
#0 /home/site/wwwroot/mediawiki-1.28.2/includes/libs/rdbms/database/Database.php(912): Database->reportQueryError(string, integer, string, string, boolean)
#1 /home/site/wwwroot/mediawiki-1.28.2/includes/libs/rdbms/database/Database.php(3150): Database->query(string, string)
#2 /home/site/wwwroot/mediawiki-1.28.2/includes/libs/rdbms/database/Database.php(3098): Database->sourceStream(unknown type, NULL, NULL, string, NULL)
#3 /home/site/wwwroot/mediawiki-1.28.2/includes/installer/DatabaseInstaller.php(195): Database->sourceFile(string)
#4 /home/site/wwwroot/mediawiki-1.28.2/includes/installer/DatabaseInstaller.php(218): DatabaseInstaller->stepApplySourceFile(string, string, boolean)
#5 /home/site/wwwroot/mediawiki-1.28.2/includes/installer/Installer.php(1515): DatabaseInstaller->createTables(MysqlInstaller)
#6 /home/site/wwwroot/mediawiki-1.28.2/includes/installer/WebInstallerInstall.php(44): Installer->performInstallation(array, array)
#7 /home/site/wwwroot/mediawiki-1.28.2/includes/installer/WebInstaller.php(279): WebInstallerInstall->execute()
#8 /home/site/wwwroot/mediawiki-1.28.2/mw-config/index.php(81): WebInstaller->execute(array)
#9 /home/site/wwwroot/mediawiki-1.28.2/mw-config/index.php(39): wfInstallerMain()
#10

2 — Table creation test on Azure Database for MySQL instance

> Error Code: 1030. Got error 1 from storage engine

3 — Other test on Azure Database for MySQL instance

Seems the MySQL instance supports MyISAM engines.

# Engine, Support, Comment, Transactions, XA, Savepoints
‘InnoDB’, ‘DEFAULT’, ‘Supports transactions, row-level locking, and foreign keys’, ‘YES’, ‘YES’, ‘YES’
‘MyISAM’, ‘YES’, ‘MyISAM storage engine’, ‘NO’, ‘NO’, ‘NO’
‘MRG_MYISAM’, ‘YES’, ‘Collection of identical MyISAM tables’, ‘NO’, ‘NO’, ‘NO’

Источник

Часовой пояс GMT +3, время: 14:21.