Ошибка mysql не может быть read

Today I updated openssl (due to the recent heartbleed vulnerability) and all of a sudden mysql is acting strangely. I was recently able to modify tables, but now when I try to add a column I get:

ERROR 1005 (HY000): Can't create table '#sql-34f_872b' (errno: 13)

And trying to do show tables results in:

ERROR 1018 (HY000): Can't read dir of './<db>/' (errno: 13)

During the openssl upgrade there was a prompt asking about an upgrade to mysql. It was asking if I want to keep my current /etc/my.cnf or if I wanted to replace with the new one — I selected to keep.

Typically this would be a permissions issue, and I’ve tried and tested the permissions on the mysql datadir (using this answer from a similar question).

A few other strange things:

  • When I try these commands, I’m using the mysql command client and logged in as root
  • I am able to insert new rows into existing tables

I feel like the openssl upgrade is too much of a coincidence to ignore, and I’m not keen on the idea of restarting mysqld without really knowing that the server will definitely come back up (since there’s an unknown issue going on here).

Any ideas?

Reply to comments and questions:

Output of ls -ltrFa:

remy@ip-10-168-9-52:~$ ls -ltrFa /vol/mysql/
total 49367084
-rwxr-xr-x 1 mysql mysql           0 Feb 11  2013 debian-5.5.flag*
drwxr-xr-x 2 mysql mysql        4096 Feb 11  2013 test/
drwxr-xr-x 2 mysql mysql        4096 Feb 11  2013 performance_schema/
drwxr-xr-x 2 mysql mysql        4096 Feb 11  2013 mysql/
-rwxr-xr-x 1 mysql mysql           6 Feb 11  2013 mysql_upgrade_info*
-rwxr-xr-x 1 mysql mysql          25 Feb 12  2013 slave-relay-bin.index*
-rwxr-xr-x 1 mysql mysql         126 Feb 12  2013 slave-relay-bin.000001*
drwxr-xr-x 6 mysql mysql        4096 Oct 14 14:35 ./
drwxr-xr-x 2 mysql mysql        4096 Apr  3 15:50 jsbin/
drwxrwxrwx 5 root  root         4096 Apr  9 16:07 ../
-rwxr-xr-x 1 mysql mysql 50417631232 Apr  9 17:24 ibdata1*
-rwxr-xr-x 1 mysql mysql    67108864 Apr  9 17:24 ib_logfile0*
-rwxr-xr-x 1 mysql mysql    67108864 Apr  9 17:25 ib_logfile1*

Output of ps aux | grep mysql:

mysql      847  1.4 83.9 16342212 14681964 ?   Ssl   2013 3646:34 /usr/sbin/mysqld
remy      4038  0.0  0.0 101816  2824 pts/0    S+   16:58   0:00 mysql -uroot -px xxxxx jsbin

Note that I’ve also tried running mysql using sudo -u mysql mysql -uroot -pxxx jsbin and it results in the same issue.

Here is the the log from the apt-get upgrade openssl which shows mysql being included in the update: https://gist.github.com/remy/10291829

Server is ubuntu-precise-12.04-amd64 (installations are all via apt-get rather than manually compiled).

Versions of mysql:

$ mysqld --version
mysqld  Ver 5.5.29-0ubuntu0.12.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))
$ mysql --version
mysql  Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (x86_64) using readline 6.2

Источник

Страниц: 1

  • Список
  •  » Раздел для начинающих
  •  » ошибка mysqld.exe — инструкция по адресу… память не может быть «read»…

#1 03.10.2010 00:22:59

Azmandius
Участник
Зарегистрирован: 03.10.2010
Сообщений: 4

ошибка mysqld.exe — инструкция по адресу… память не может быть «read»…

Приветствую.

У меня Windows XP SP 2 и MySQL Server 5.1 Essential (sql использую для радиовещания посредством SAM Broadcaster).

Без какой либо причины, обычно после примерно 40 дней работы системы слетает mysqld.exe.
Смог остановить службу командой net stop mysql, а вот запустить через net start mysql не получается.
Перезагрузка компьютера не помогает, и переустановка sql тоже не решает проблему.

В общем невозможно запустить процесс mysqld.exe, и вылетает всегда одна и та же дурацкая ошибка:
http://om.md/public/sql%20error.jpg

Файл ibdata1 в порядке, я специально после переустановки всего заменял новый на старый и проблем не было.

Если кто видел такое помогите, устал уже переустанавливать Винду и всё остальное.

Неактивен

#2 03.10.2010 13:18:34

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6753

Re: ошибка mysqld.exe — инструкция по адресу… память не может быть «read»…

Ощущение чего-то windows-специфичного. Вирусы могут быть?

Если сами по себе данные не испорченные, то испорченный код. Код
испортиться за 40 дней не может. Значит, что-то стороннее его портит.
Возможно, ставили какие-то библиотеки системные, еще что-нибудь.

Наоборот — может, не вирусы, а антивирусы, которые услужливо что-
нибудь контролируют в запущенных процессах и портят в живом коде
нормальную работу.

Неактивен

#3 03.10.2010 17:26:20

Azmandius
Участник
Зарегистрирован: 03.10.2010
Сообщений: 4

Re: ошибка mysqld.exe — инструкция по адресу… память не может быть «read»…

А о каком конкретно коде речь, чтобы знать где копать?
Спасибо.

Неактивен

#4 03.10.2010 18:58:41

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6753

Re: ошибка mysqld.exe — инструкция по адресу… память не может быть «read»…

У Вас внезапно останавливается mysqld. Если его переустановка не помогает,
значит, сбоит какая-то системная библиотека, которую он использует, или ему
мешает работать какое-то стороннее приложение.

Подумайте, какие приложения ставились недавно на этот сервер. Возможно,
туда приехали какие-то обновления, еще что-то. К сожалению, порекомендовать
тут мало что можно. Я бы начал с того, что поставил какой-нибудь современный
антивирус с полными обновлениями, проверил всё на вирусы. После этого оста-
новил бы все антивирусы и попробовал бы запустить mysqld. Если он запустится —
значит дело действительно в этом. Если нет — попробовал бы переустановить
еще раз MySQL и запустить его.

Неактивен

#5 03.10.2010 23:20:23

Azmandius
Участник
Зарегистрирован: 03.10.2010
Сообщений: 4

Re: ошибка mysqld.exe — инструкция по адресу… память не может быть «read»…

Что-ж, спасибо за помощь.
Подожду рокового дня и попробую как вы говорите пару раз переустановить MySQL.

Неактивен

#6 03.10.2010 23:58:43

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6753

Re: ошибка mysqld.exe — инструкция по адресу… память не может быть «read»…

Дело не в «пару раз переустановить». Надо найти, почему так происходит. Что-то в
системе меняется. Надо понять — что.

Неактивен

#7 04.10.2010 00:03:18

Azmandius
Участник
Зарегистрирован: 03.10.2010
Сообщений: 4

Re: ошибка mysqld.exe — инструкция по адресу… память не может быть «read»…

Пока не знаю как определить что меняется.
Подожду «полевых условий» и посмотрю какое решение сработает.

Неактивен

Страниц: 1

  • Список
  •  » Раздел для начинающих
  •  » ошибка mysqld.exe — инструкция по адресу… память не может быть «read»…

Источник

I’m experiencing a strange MySQL error, seemingly related to the database’s read-only flag. A Web application that uses MySQL is running on Debian 7.9. It was running well for weeks, if not more, while, suddenly, attempts to access the application-powered website started producing the following error message on a blank webpage:

Error: 500 — SQLSTATE[HY000]: General error: 1290 The MySQL server is
running with the —read-only option so it cannot execute this
statement

The following are the steps that I performed as part of my investigation:

  • found and read read relevant info on the Internet (some pointed to MySQL’s read-only flag);
  • based on the above, tried to find the read-only flag in MySQL config. file (my.cnf) — couldn’t find it there, but read that the default value for the flag is OFF anyway;

  • verified the filesystem to make sure there is plenty of disk space (df -h):

    Filesystem Size Used Avail Use% Mounted on
    udev 10M 0 10M 0% /dev
    tmpfs 3.2G 1.4M 3.2G 1% /run
    /dev/disk/by-uuid/xxxxxxxxxxxxxxxxx 113G 14G 94G 13% /
    tmpfs 5.0M 0 5.0M 0% /run/lock
    tmpfs 7.3G 72K 7.3G 1% /run/shm

  • ran mysqlcheck --all-databases: all tables are OK;

  • verified that there is plenty of RAM available on the server (free):

    total used free shared buffers cached
    Mem: 32898332 2090268 30808064 0 425436 970348
    -/+ buffers/cache: 694484 32203848
    Swap: 5105660 0 5105660

  • finally, I have decided to take a «snapshot» of MySQL-related processes (ps ax | grep mysql) during the problem’s existence and after a temporary fix (DB restart), hoping that it could give people additional context for ideas; here are the corresponding results:

    Problem:

    20307 ? S 0:00 /bin/sh /usr/bin/mysqld_safe
    20635 ? Sl 0:37 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
    20636 ? S 0:00 logger -t mysqld -p daemon.error
    36427 pts/0 S+ 0:00 grep mysql

    No problem:

    36948 pts/0 S 0:00 /bin/sh /usr/bin/mysqld_safe
    37275 pts/0 Sl 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
    37276 pts/0 S 0:00 logger -t mysqld -p daemon.error
    38313 pts/0 S+ 0:00 grep mysql

UPDATE:

I just experienced the issue again and decided to check whether the global read-only flag is set to OFF or not, assuming the latter. My assumption has confirmed:

mysql> SELECT @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

I guess, despite the default OFF value, since it is being overwritten by some process in the system, I will have to set the read-only flag to OFF explicitly and permanently via MySQL configuration file. Will report on results later in an answer.

asked Feb 16, 2016 at 23:39

Aleksandr Blekh's user avatar

Aleksandr BlekhAleksandr Blekh

2,4624 gold badges32 silver badges64 bronze badges

8

As I see it there are two broad reasons for why your database is being set to read only:

1) MySQL is setting itself read only

I’m not sure what might cause MySQL to go read only, perhaps disk issues or corruption of database? In any case I’d expect something to appear in the logs, so check the MySQL (and system) logs.

2) A client is setting the database read only

Clients connecting to MySQL can set the database read only using the command:

SET GLOBAL read_only = ON;

however to do this the user is required to have SUPER privileges. This permission shouldn’t be needed for websites, applications, etc that are using MySQL — keep it only for an admin account that you use for administering the database.

Lock down the permissions that each user has so they only have permission to do the things that they need on the databases / tables that are applicable. If you’re using some out-of-the-box applications they should come with instructions detailing what permissions are required (e.g. SELECT, INSERT, DELETE, UPDATE).

answered Feb 19, 2016 at 9:48

Eborbob's user avatar

EborbobEborbob

1,8851 gold badge14 silver badges30 bronze badges

2

Based on my question’s comments (special thanks to @Eborbob) and my update, I have figured that some process in the system resets the read-only flag to ON (1), which seem to trigger the issue and results in the website becoming inaccessible. In order to fix the problem as well as make this fix persistent across software and server restarts, I decided to update MySQL configuration file my.cnf and restart the DB server.

After making the relevant update (in my case, addition) to the configuration file

read_only=0

let’s verify that the flag is indeed set to OFF (0):

# mysql
mysql> SELECT @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

Finally, let’s restart MySQL server (for some reason, a dynamic reloading of MySQL configuration (/etc/init.d/mysql reload) didn’t work, so I had to restart the database server explicitly:

service mysql stop
service mysql start

Voila! Now access to the website is restored. Will update my answer, if any changes will occur.

answered Feb 18, 2016 at 2:34

Aleksandr Blekh's user avatar

Aleksandr BlekhAleksandr Blekh

2,4624 gold badges32 silver badges64 bronze badges

1

set global read_only = off;
make read only mode off later it will work sure.

answered Aug 1, 2019 at 8:02

Ajay Gadhavana's user avatar

Not related to the issue, but related to the error ‘mysql read-only’.

Make sure you are not trying to write something to a slave instance of mysql.

answered Nov 18, 2020 at 10:02

Yuri Tinyukov's user avatar

the server might be set to recovery mode
find the innodb_force_recovery in my.cnf and uncomment it and restart the server then run the upgrade.

answered Feb 1, 2022 at 17:41

Nguyen's user avatar

I just experienced the same error and fixed it by connecting to the hostname of the mysql server as opposed to the IP address. I’m not sure why this fixed it but it did. Just FYI

answered Sep 20, 2017 at 19:00

Chris Hall's user avatar

Chris HallChris Hall

8616 gold badges13 silver badges21 bronze badges

1

As Eborbob say it’s probably a client,

Did you check your backup tool ?

Do you use some SQL proxy like proxySQL or maxscale ?
For exemple Mascale can enforce readonly by monitoring : https://jira.mariadb.org/browse/MXS-1859

Replication Manager can also change READ ONLY flag

answered Nov 24, 2018 at 4:12

NicoMinsk's user avatar

NicoMinskNicoMinsk

1,7066 gold badges28 silver badges53 bronze badges

The below error:-
The MySQL server is running with the —read-only option so it cannot execute this statement

It occurs when a user not having the write permission for the sql db tries to insert/update some data into the db.

It is quite a valid security error, as it is stating that you currently are having just —read-only rights and hence cannot execute a query that has anything to do with writing.

To resolve this error:-
Get the write access from the DBA.

e.g.

GRANT ALL PRIVILEGES ON database.table TO 'user'@'localhost';

The above query will grant all privileges to the user with username ‘user’.

David's user avatar

David

33.2k11 gold badges79 silver badges117 bronze badges

answered Jul 23, 2019 at 12:18

Akash Verma's user avatar

Akash VermaAkash Verma

6081 gold badge11 silver badges13 bronze badges

executing below statement worked for me
mysql> SET GLOBAL read_only = OFF;

mysql cmd

answered Oct 13, 2021 at 6:23

adarsh kavtiyal's user avatar

This worked for me and you can try it.

Make a backup of your .sql file ( change your query )

Find all Engine=InnoDB
and replace them with Engine=MyISAM

and try executing again.

answered Jun 25, 2020 at 18:24

AnupRaj's user avatar

AnupRajAnupRaj

6615 silver badges11 bronze badges

1

Источник

In this post we will see the resolution of MySQL “permission denied” error due to movement of database to a different partition on CentOS.

Recently, a friend was stuck on following issue and he was not able to access the tables under ‘sakila’ database. Though he was able to read all other databases!

ERROR 1018 (HY000): Can't read dir of './sakila/' (errno: 13 - Permission denied)

This is what happens when he try accessing the tables from sakila database:

mysql>use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
ERROR 1018 (HY000): Can't read dir of './sakila/' (errno: 13 - Permission denied)

We reviewed the ‘sakila’ database, it was well owned by mysql user and group with proper permissions.

[root@localhost mysql]# ls -lrth | grep sakila
lrwxrwxrwx. 1 mysql mysql   15 Jul  6 07:51 sakila -> /database/sakila

Note that “sakila” is pointing (/soft-linked) to a different partition which was the recent change he made! We confirmed that the partition and sakila database / datafiles were again having correct permissions too.

Usually such error is caused by wrong permission due to which MySQL isn’t able to read the specifics. Correcting the file/folder permissions can fix them quickly!

$] chown mysql:mysql /database -R  # Making sure /database and everything under is mysql owned.
$] chown mysql:mysql /var/lib/mysql -R # Making sure mysql $] datadir and everything under is mysql owned.

We can actually connect as mysql user and confirm that the table files are readable by mysql!

[root@localhost mysql]# su - mysql
[mysql@localhost ~] cd /var/lib/mysql/sakila
[mysql@localhost sakila]# cat actor.frm
...

Though ours was not the permission issue and without softlink it was working fine!

Solution to the Permission Denied Error

Considering, all but sakila database  tables were generating “permission denied” errors and mysql error log remained silent about this, we’re surely dealing with some “external” power!

We are on CentOS and we have something called SELinux!

Security-Enhanced Linux (SELinux) is a Linux kernel security module that provides a mechanism for supporting access control security policies, including ... mandatory access controls (MAC).
- Wikipedia

SELinux logs at /var/log/audit/audit.log and following is a snippet from audit log:

type=AVC msg=audit(1436192732.305:119): avc:  denied  { read } for  pid=7063 comm="mysqld" name="sakila" dev=sda3 ino=655362 scontext=unconfined_u:system_r:mysqld_t:s0 tcontext=unconfined_u:object_r:default_t:s0 tclass=dir

This confirms that SELinux is obstructing MySQL to read from location other than datadir and fixing that will make MySQL happy.

Command ‘sestatus’ can show the SELinux Status:

[root@localhost mysql]# sestatus
SELinux status:                 enabled
SELinuxfs mount:                /selinux
Current mode:                   enforcing
Mode from config file:          enforcing
Policy version:                 24
Policy from config file:        targeted

Here we can note SELinux is enabled and the mode is enforcing.

Here comes 2 more commands: setenforce & getenforce.

The “setenforce 0” command switches off SELinux enforcing, and getenforce shows you the current status. Though setenforce is a “temporary” solution and it will auto-reset to configuration option upon system reboot.

To make it persistent over reboots, we’ll need to change a configuration file, /etc/sysconfig/selinux:

[root@localhost mysql]# cat /etc/sysconfig/selinux
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
#SELINUX=enforcing # Removed this line to disable SELinux completely.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

We added “SELINUX=disabled” to disable SELinux completely. (You might want to keep it “permissive” or even better, manage SELinux policies.)

Upon disabling SELinux the issue went away and ‘sakila’ database  tables were accessible.

mysql> use sakila
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.00 sec)

Just to note that /etc/sysconfig/selinux configuration file for SELinux is actually pointing to /etc/selinux/config.

[root@localhost mysql]# ls -l /etc/sysconfig/selinux
lrwxrwxrwx. 1 root root 17 Oct  8  2014 /etc/sysconfig/selinux -> ../selinux/config

Later I found plenty of posts just talking about the same issue! So we walked a common path, but not without learning!

On CentOS, Redhat or Fedora we have SELinux while on Ubuntu, Debian or SuSE you should find AppArmor! Refer a good post from Jeremy @Oracle here.

Hope this helps (or refreshes).

Источник

За последние 24 часа нас посетили 11689 программистов и 714 роботов. Сейчас ищут 304 программиста …

Mysql error память не может быть ‘read’ (Windows)

Тема в разделе «MySQL», создана пользователем X-Cray, 11 авг 2006.


  1. X-Cray

    X-Cray
    Активный пользователь

    С нами с:
    10 июл 2006
    Сообщения:
    255
    Симпатии:
    0
    Адрес:
    Москва

    1. SELECT t.id, t.title, t.last_post_id, t.last_post_time, t.last_poster_id, t.last_poster_nick, MATCH p.message_compiled AGAINST (‘слова для поиска’ ) as relev FROM `themes` t LEFT JOIN `posts` p ON (p.theme=t.id) ORDER BY t.id ASC, relev DESC;

    При выполнении этого кода иногда (1 раз из 50), вылетает сообщение о том, что «память не может быть ‘read’, приложение обратилось по несуществующему адресу и будет закрыто»… и mysqld-nt слетает, приходится перезапускать.

    Это глюки винды или в запросе западло такое?
    Ошибка от слов для поиска в запросе не зависит, а индексы и фултексты на полях проставлены по правилам.

    … в общем, никакой закономерности не заметил, но ошибка вываливается только при MATCH-e

    Если это винда, то я спокоен, если нет — что это такое и как исправить?


  2. Hight

    Команда форума
    Модератор

    это глюк связки MySQL + винды


  3. X-Cray

    X-Cray
    Активный пользователь

    С нами с:
    10 июл 2006
    Сообщения:
    255
    Симпатии:
    0
    Адрес:
    Москва

    А, ну тогда все замечательно, добавлю ошибку в игнор, а мускул на авто-рефреш. (ибо, на локале — только тесты, драйв идет на FreeBSD и *nix)
    Вопрос снят.

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка mysql query error create index
  • Ошибка mysql query error alter table
  • Ошибка mysql php как модуль
  • Ошибка mysql no such file or directory
  • Ошибка mysql invalid default value for