A.1 Как определить, чем вызваны проблемы
При возникновении проблемы прежде всего следует обнаружить ее источник - программу или элемент оборудования:
Если присутствует один из следующих симптомов, то проблема, скорее всего, связана с аппаратным обеспечением (с памятью, материнской платой, процессором или жестким диском) либо с ядром:
Не работает клавиатура. Обычно ее работоспособность можно проверить по реакции на нажатие Caps Lock. Если индикатор Caps Lock не меняется, то клавиатуру необходимо заменить (прежде чем это сделать, следует попробовать перезагрузить компьютер и проверить все кабели к клавиатуре).
Не перемещается курсор мыши.
Машина не отвечает на ping-запросы удаленной машины.
Различные не связанные между собой программы не работают, как надо.
Система неожиданно перезагрузилась (дефектная программа пользовательского уровня никогда не должна быть способна вызвать отказ системы).
В этом случае необходимо начать с проверки всех кабелей и запуска диагностических средств для проверки аппаратуры! Следует также проверить, нет ли патчей, обновлений, сервисных пакетов (service pack) для используемой операционной системы, при помощи которых вы, возможно, могли бы решить проблемы. Кроме того, следует удостовериться, что у вас установлены достаточно свежие версии библиотек (таких как glibc).
Для раннего обнаружения проблем хорошо использовать машину с ECC-памятью!
В случае блокировки клавиатуры положение можно исправить, если войти на свою машину с другой машины и выполнить на своей машине kbd_mode
-a.
Исследуйте свой системный журнальный файл (`/var/log/messages' или т.п.) на предмет причин возникающих проблем. Если есть основания полагать, что проблема - в MySQL, то следует также изучить журнальные файлы MySQL (see section 4.9.3 Журнал обновлений (update)).
Если вы считаете, что аппаратные проблемы отсутствуют, следует попробовать обнаружить вызывающую проблемы программу. Попробуйте с помощью top, ps, taskmanager или подобной программы проверить, какая программа забирает все ресурсы процессора или блокирует машину.
Проверьте с помощью top, df или подобной программы, нет ли нехватки памяти, дискового пространства, дескрипторов для открытия файлов или каких-либо других критических ресурсов.
Если проблема связана с бесконтрольным процессом, то всегда можно попробовать уничтожить его. Если он не хочет уничтожаться, то, вероятно, существует ошибка в операционной системе.
Если после изучения всех возможных причин вы сделали вывод, что источником проблемы является именно MySQL-сервер или клиент, то следует сделать отчет об ошибке для нашего списка рассылки или команды поддержки. В отчете об ошибке постарайтесь дать очень подробное описание поведения системы и свое мнение по поводу происходящего. Следует также объяснить, почему вы считаете, что проблемы вызывает именно MySQL. Примите во внимание все ситуации, описанные в данном разделе. Опишите все проблемы в точности так, как они наблюдаются при исследовании системы. При помещении в отчет для всего вывода программ и/или их сообщений об ошибках и/или подобной информации из журнальных файлов используйте метод "вырезать и вставить"!
Просьба детально описать, какая именно программа не работает, и какие симптомы вы наблюдали! Нам доводилось получать много отчетов об ошибках, где просто утверждалось, что "система не работает", - такие отчеты не давали никакой информации о характере возможной проблемы.
Если программа сбоит, то всегда полезно выяснить:
Не вызвала ли данная программа ошибки сегментации (core dump)?
Не забирает ли программа все ресурсы процессора? Проверьте с помощью top. Дайте программе немного поработать - возможно, она занимается сложными вычислениями.
Если проблемы вызваны именно сервером mysqld, то можно ли выполнить mysqladmin -u root ping или mysqladmin -u root processlist?
Что сообщает клиентская программа (попробуйте поработать, например, с mysql) при попытке соединиться с MySQL? Происходит ли заклинивание клиента? Выдает ли программа какой-нибудь вывод?
При посылке отчета об ошибке необходимо придерживаться схемы, описанной в этом руководстве (see section 1.8.1.2 Как задавать вопросы и направлять сообщения об ошибках).
A.2.1 Ошибка Access denied
See section 4.2.11 Причины появления ошибок Access denied ("в доступе отказано"). See section 4.2.6 Как работает система привилегий.
A.2.2 Ошибка MySQL server has gone away
Все изложенное в данном разделе относится также и к родственной ошибке Lost connection to server during query.
Наиболее часто ошибка MySQL server has gone away возникает в результате тайм-аута соединения и его закрытия сервером. По умолчанию сервер закрывает соединение по прошествии 8 часов бездействия. Можно изменить лимит времени, установив при запуске mysqld переменную wait_timeout.
Другой распространенной причиной получения ошибки MySQL server has gone away является выдача команды "закрытия" на соединении MySQL с последующей попыткой выполнить запрос на закрытом соединении.
Если это получено в скрипте, то достаточно просто повторить запрос от клиента, чтобы соединение автоматически восстановилось.
Обычно в этом случае выдаются следующие коды ошибки (какой из них вы получите, зависит от ОС):
CR_SERVER_GONE_ERROR
CR_SERVER_LOST
Ошибка будет также выдана, если кто-нибудь уничтожит выполняющийся поток посредством kill номерпотока.
Проверить, что MySQL на ходу, можно, запустив mysqladmin version и изучив время работы (uptime). Если проблема в аварийном завершении mysqld, то необходимо сосредоточиться на поиске причины аварии. В этом случае следует сначала проверить, не будет ли уничтожен MySQL снова при повторном задании запроса (see section A.4.1 Что делать, если работа MySQL сопровождается постоянными сбоями).
Эти ошибки будут также выдаваться при посылке серверу неверного или слишком длинного запроса. Если mysqld получает неправильный или слишком большой пакет, то сервер предполагает, что с клиентом что-то не так, и закрывает соединение. Если необходимо выполнять объемные запросы (например, при работе с большими столбцами типа BLOB), можно увеличить предельный размер запроса, запустив mysqld с опцией -O max_allowed_packet=# (по умолчанию 1 Mб). Дополнительная память выделяется по требованию, так что mysqld будет выделять больше памяти только в случае, когда выдан большой запрос или когда mysqld должен возвратить большую строку результата!
Вы также можете получить разрыв соединения, если вы отправили пакет больше 16Мб, если ваш клиент старше чем 4.0.8, а ваш сервер 4.0.8 или новее.
Если у вас возникнет желание сделать отчет об ошибке по этой проблеме, то не забудьте включить в него следующие сведения:
Информацию о том, упал MySQL или нет (это можно определить по файлу hostname.err (see section A.4.1 Что делать, если работа MySQL сопровождается постоянными сбоями).
Если определенный запрос уничтожает mysqld, а используемые в нем таблицы перед выполнением запроса проверялись с помощью CHECK TABLE, то желательно составить контрольный тест (see section E.1.6 Создание контрольного примера при повреждении таблиц).
Значение переменной wait_timeout в сервере (это значение выдает mysqladmin variables).
Информацию о том, пробовали ли вы запускать mysqld с --log и проверять, появляется ли выданный запрос в журнале.
Обращайтесь к разделу See section 1.8.1.2 Как задавать вопросы и направлять сообщения об ошибках.
A.2.3 Ошибка Can't connect to [local] MySQL server
Клиент MySQL на Unix может соединиться с сервером mysqld двумя различными способами: используя либо Unix-сокеты, когда соединение происходит через файл в файловой системе (по умолчанию `/tmp/mysqld.sock'), либо TCP/IP с соединением через номер порта. Unix-сокеты обеспечивают большую скорость, чем TCP/IP, но могут применяться только при соединении с сервером на том же компьютере. Unix-сокеты используются, если не задано имя хоста или если задано специальное имя localhost.
В Windows, если сервер mysqld выполняется в 9x/Me, возможно соединение только через TCP/IP. Если сервер работает на NT/2000/XP и mysqld запущен с --enable-named-pipe, то можно также устанавливать соединение с помощью именованных каналов. Имя именованного канала - MySQL. Если имя хоста не указано при соединении с mysqld, то клиент MySQL сначала попробует подключиться к именованному каналу, а если этого сделать не удастся, то к порту TCP/IP. Можно предписать использование именованных каналов в Windows, используя . в качестве имени хоста.
Ошибка (2002) Can't connect to ... обычно говорит о том, что MySQL не запущен на данной системе или что при попытке соединиться с сервером mysqld используется неверный сокет-файл или порт TCP/IP.
Для начала проверьте (с помощью ps или диспетчера задач в Windows), выполняется ли на сервере процесс с именем mysqld! Если процесса mysqld
нет, то его необходимо запустить (see section 2.4.2 Проблемы при запуске сервера MySQL).
Если процесс mysqld выполняется, то можно проверить сервер, пробуя использовать следующие различные соединения (конечно, номер порта и путь сокета для вашей конфигурации могут быть другими):
shell> mysqladmin version shell> mysqladmin variables shell> mysqladmin -h `hostname` version variables shell> mysqladmin -h `hostname` --port=3306 version shell> mysqladmin -h 'ip for your host' version shell> mysqladmin --protocol=socket --socket=/tmp/mysql.sock version
Обратите внимание на то, что для hostname используются обратные кавычки вместо прямых; это задает подстановку вывода hostname (т.е. текущего имени хоста) в команду mysqladmin.
Ниже приводится несколько причин, которые могут вызывать ошибку Can't connect to local MySQL server:
mysqld не выполняется.
Сервер запущен на системе, использующей MIT-потоки. При работе на системе, в которой отсутствуют "родные" потоки, mysqld использует пакет MIT-pthreads (see section 2.2.5 Операционные системы, поддерживаемые MySQL). Однако не во всех версиях MIT-потоков поддерживаются Unix-сокеты. В системе без поддержки сокетов при соединении с сервером всегда необходимо явно указывать имя хоста. Попробуйте проверить соединение с сервером с помощью следующей команды:
shell> mysqladmin -h `hostname` version
Кто-либо удалил Unix-сокет, используемый mysqld (по умолчанию `/tmp/mysqld.sock'). Возможно, есть задание cron, которое удаляет сокет MySQL (например, задание, удаляющее старые файлы в каталоге `/tmp'). В таком случае всегда можно выполнить mysqladmin version и проверить, действительно ли существует сокет, который используется mysqladmin. Решение здесь заключается в следующем: можно либо изменить задание cron с тем, чтобы оно не удаляло `mysqld.sock', либо поместить сокет в каком-нибудь другом месте (see section A.4.5 Как защитить или изменить сокет-файл MySQL `/tmp/mysql.sock').
Сервер mysqld запущен с опцией --socket=/path/to/socket. Если путь к сокету для сервера был изменен, то необходимо уведомить о новом пути и клиентов MySQL. Это можно сделать, передав клиенту путь к сокету в качестве аргумента. see section A.4.5 Как защитить или изменить сокет-файл MySQL `/tmp/mysql.sock'.
Используется Linux, и один из потоков аварийно завершился (core dump). В этом случае, перед тем как заново запустить MySQL, необходимо уничтожить все остальные потоки mysqld (например, с помощью сценария mysql_zap). Обращайтесь к разделу See section A.4.1 Что делать, если работа MySQL сопровождается постоянными сбоями.
Возможно, пользователь, от которого запущен MySQL-сервер, не имеет привилегий чтения и записи для каталога, содержащего сокет-файл, либо для самого сокет-файла. В этом случае необходимо либо изменить привилегии для каталога/файла, либо перезапустить mysqld, так чтобы сервер использовал каталог, к которому пользователь имеет доступ.
Если получено сообщение об ошибке Can't connect to MySQL server on some_hostname, то чтобы выяснить, в чем проблема, можно попробовать выполнить следующие действия:
Проверить, запущен ли сервер, выполнив telnet your-host-name tcp-ip-port-number, и несколько раз нажать Enter. Если MySQL работает на этом порту, то должен быть получен ответ, включающий номер версии запущенного сервера. Если будет выдана ошибка вроде telnet: Unable to connect to remote host: Connection refused, то на указанном порту сервер не работает.
Попробуйте соединиться с демоном mysqld на локальной машине и проверьте с помощью mysqladmin variables, какой порт TCP/IP сконфигурирован для использования mysqld (переменная port).
Проверьте, не запускается ли сервер mysqld с опцией --skip-networking.
A.2.4 Ошибка Host '...' is blocked
Ошибка, подобная следующей:
Host 'hostname' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'
говорит о том, что от хоста hostname имело место большое количество (max_connect_errors) прерванных посредине запросов на соединение к mysqld. После max_connect_errors неудачных запросов mysqld делает предположение, что что-то не так (может, атака от кракера), и блокирует последующие соединения с узла до того момента, пока кто-нибудь не выполнит команду mysqladmin flush-hosts.
По умолчанию mysqld блокирует хост после 10 ошибок соединения. Это значение можно легко отрегулировать, запустив сервер следующим образом:
shell> safe_mysqld -O max_connect_errors=10000 &
Заметим, что если для некоторого хоста получено это сообщение об ошибке, то следует сначала проверить, все ли в порядке с TCP/IP-соединениями от этого хоста. Если TCP/IP-соединения не работают, то увеличивать значение переменной max_connect_errors бесполезно!
A.2.5 Ошибка Too many connections
Получение ошибки Too many connections при попытке соединиться с MySQL означает, что уже есть max_connections клиентов, соединившихся с сервером mysqld.
Если есть потребность в большем количестве соединений, чем задано по умолчанию (100), то следует перезапустить mysqld с заданием большего значения для переменной max_connections.
Заметим, что фактически mysqld разрешает соединяться (max_connections+1) клиентам. Последнее соединение зарезервировано для пользователя с привилегией SUPER. Если не наделять этой привилегией обычных пользователей (они могут обойтись и без нее), то администратор, располагая этой привилегией, может войти и использовать SHOW PROCESSLIST для выяснения причин неполадок (see section 4.5.6.6 SHOW PROCESSLIST).
Максимальное число соединений MySQL зависит от того, насколько хорошей является библиотека потоков на данной платформе. Linux или Solaris должны быть в состоянии поддерживать 500-1000 одновременных соединений, в зависимости от количества имеющейся памяти и того, чем занимаются клиенты.
A.2.6 Ошибка Some non-transactional changed tables couldn't be rolled back
Получение ошибки/предупреждения: Warning: Some non-transactional changed tables couldn't be rolled back при попытке сделать ROLLBACK означает, что для некоторых использованных в транзакции таблиц не поддерживаются транзакции. Команда ROLLBACK на эти нетранзакционные таблицы не подействует.
Наиболее типичный случай возникновения такой ошибки связан с попыткой создать таблицу, тип которой не поддерживается бинарником mysqld. Если mysqld не поддерживает тип таблиц (или тип таблиц отключен опцией при запуске), то сервер создаст таблицу с типом, наиболее близким к запрошенному (скорее всего, MyISAM).
Чтобы проверить тип таблицы, следует выполнить:
SHOW TABLE STATUS LIKE 'table_name'. See section 4.5.6.2 SHOW TABLE STATUS.
Можно проверить, какие расширения поддерживает исполняемый файл mysqld, выполнив:
show variables like 'have_%'. See section 4.5.6.4 SHOW VARIABLES.
A.2.7 Ошибка Out of memory
По выданному запросу может быть получена ошибка следующего вида:
mysql: Out of memory at line 42, 'malloc.c'
mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)
ERROR 2008: MySQL client ran out of memory
Как видим, в ней есть ссылка на MySQL-клиент mysql. Причина этой ошибки в том, что клиенту просто не хватает памяти для размещения всего результата.
Чтобы устранить данную проблему, сначала проверьте правильность запроса. Действительно ли есть необходимость в том, чтобы запрос возвращал так много строк? Если да, то можно использовать mysql --quick, где для извлечения результирующего множества применяется mysql_use_result(). При этом уменьшается загрузка клиента (но увеличивается загрузка сервера).
A.2.8 Ошибка Packet too large
Когда клиент MySQL или сервер mysqld получают пакет с размерами, превышающими max_allowed_packet байтов, программа выдает ошибку Packet too large и закрывает соединение.
В MySQL 3.23 размер самого большого возможного пакета составляет 16 Mб (из-за ограничений клиент-серверного протокола). В MySQL 4.0.1 и выше размер пакета ограничивается только количеством имеющейся на сервере памяти (вплоть до теоретического максимума в 2 Гб).
Коммуникационный пакет - это одна команда SQL, посылаемая серверу, или одна строка, посылаемая клиенту.
Когда клиент MySQL или сервер mysqld получают пакет, размеры которого превышают max_allowed_packet байтов, программа выдает ошибку Packet too large и закрывает соединение. Если коммуникационный пакет слишком велик, то в некоторых клиентах может быть выдана ошибка Lost connection to MySQL server during query.
Отметим, что и клиент, и сервер имеют свои собственные переменные max_allowed_packet. Если требуется обрабатывать большие пакеты, то эту переменную необходимо увеличить как для клиента, так и для сервера.
Увеличение переменной не опасно, поскольку память выделяется только при необходимости; эта переменная скорее является мерой предосторожности для "отлавливания" неправильных пакетов между клиентом и сервером, а также для того, чтобы предупредить ситуацию нехватки памяти, вследствие случайного использования больших пакетов.
Если используется клиент mysql, то можно задать больший буфер, запустив клиент посредством mysql --set-variable=max_allowed_packet=8M. Для других клиентов существуют собственные методы установки этой переменной. Обратите внимание, что начиная с 4.0 использование --set-variable не рекомендуется, используйте просто --max-allowed-packet=8M.
Для установки max_allowed_packet большего размера в mysqld можно также использовать файл опций. Например, если в таблице предполагается хранить значение типа MEDIUMBLOB максимальной длины, то нужно запускать сервер с опцией set-variable=max_allowed_packet=16M.
При использовании больших пакетов могут также возникать непредсказуемые проблемы, если вы работаете с большими значениями типа BLOB, и mysqld не был предоставлен доступ к достаточному объему памяти для обработки запроса. Если есть подозрение, что дело в этом, попробуйте добавить ulimit -d 256000 в начале скрипта safe_mysqld и перезапустить mysqld.
A.2.9 Коммуникационные ошибки / Оборванные соединения
Начиная с MySQL 3.23.40 ошибка Aborted connection выдается только в случае, если mysqld запущен с --warnings.
В журнале ошибок могут присутствовать ошибки наподобие этой:
010301 14:38:23 Aborted connection 854 to db: 'users' user: 'josh'
(see section 4.9.1 Журнал ошибок).
Такие ошибки сигнализируют об одной из следующих ситуаций:
Клиентская программа не выполнила mysql_close() перед выходом.
Клиент бездействовал на протяжении более чем wait_timeout или interactive_timeout (see section 4.5.6.4 SHOW VARIABLES).
Клиентская программа внезапно завершилась посреди передачи.
В подобных ситуациях увеличивается значение переменной сервера Aborted_clients.
Значение переменной сервера Aborted_connects увеличивается в следующих случаях:
Когда пакет соединения содержит неверную информацию.
Когда пользователь не имеет привилегий для соединения с базой данных.
Когда пользователь использует неверный пароль.
Когда на получение пакета соединения уходит более connect_timeout
секунд. See section 4.5.6.4 SHOW VARIABLES.
Обратите внимание: все перечисленное выше может свидетельствовать о попытке взлома базы данных!
Ниже перечислены другие причины проблем, которые могут возникнуть с оборванными клиентами или разорванными соединениями.
Использование как полудуплексного, так и полного дуплексного Ethernet-протокола под Linux. Данная ошибка присутствует во многих Linux-драйверах Ethernet. Выполните тест на данную ошибку - для этого следует передать очень большой файл через ftp-соединение между этими двумя машинами. Если передача проходит в режиме всплеск-пауза-всплеск-пауза ..., то можно констатировать наличие дуплексного синдрома Linux. Единственное решение проблемы - отключить как полу-, так и полнодуплексную передачу на концентраторах и коммутаторах.
Некоторая проблема с библиотекой потоков, вызывающая прерывания при чтении.
"Криво" сконфигурированный TCP/IP.
Дефектные Ethernet-карты, концентраторы, коммутаторы или кабели... Такие проблемы можно как следует диагностировать только путем замены оборудования.
max_allowed_packet слишком мала, или запросам требуется памяти больше, чем было выделено для mysqld (see section A.2.8 Ошибка Packet too large).
A.2.10 Ошибка The table is full
Существует несколько случаев, когда выдается эта ошибка:
Используется старая версия MySQL (до 3.23.0), а размещенная в памяти временная таблица становится больше, чем tmp_table_size байтов. Для решения этой проблемы можно использовать опцию -O tmp_table_size=#, чтобы mysqld увеличил размер временных таблиц, или опцию SQL SQL_BIG_TABLES, перед тем как выдать сомнительный запрос (see section 5.5.6 Синтаксис команды SET). Можно также запускать mysqld с опцией --big-tables - эффект здесь будет таким же, как и от использования SQL_BIG_TABLES для всех запросов. В версии MySQL 3.23 размещенные в памяти временные таблицы после того, как размер таблицы превысит tmp_table_size, автоматически преобразуются в расположенные на диске таблицы типа MyISAM.
Используются таблицы InnoDB и исчерпалось место в табличном пространстве InnoDB. В таком случае следует увеличить табличное пространство InnoDB.
Используются таблицы ISAM или MyISAM в операционной системе, которая поддерживает файлы размером до 2 Гб, и файл данных или индексный файл достигли этого предела.
Используются таблицы MyISAM, и размер требуемых данных или индекса превышает тот, который предусматривался MySQL при выделении указателей (если MAX_ROWS не указано в CREATE TABLE, MySQL выделяет указатели, предусматривающие размещение только 4 Гб данных). Проверить максимальные размеры данных/индекса можно посредством
SHOW TABLE STATUS FROM database LIKE 'table_name';
или с помощью myisamchk -dv база_данных/таблица. Если проблема связана с указателями, то это можно исправить с помощью команды наподобие следующей:
ALTER TABLE table_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;
Указывать AVG_ROW_LENGTH нужно только для таблиц с полями типа BLOB/TEXT, поскольку в этом случае MySQL не может оптимизировать требуемое пространство, исходя только из количества строк.
A.2.11 Ошибка Can't create/write to file
Получение для некоторых запросов ошибки вида:
Can't create/write to file '\\sqla3fe_0.ism'.
означает, что MySQL не может создать в заданном временном каталоге временный файл для результирующего набора (приведенное сообщение об ошибке типично для Windows, примерно такой же вид имеет сообщение об ошибке Unix). Чтобы решить проблему, запустите mysqld с --tmpdir=path или добавьте в своем файле опций:
[mysqld] tmpdir=C:/temp
исходя из предположения, что каталог `c:\\temp' существует (see section 4.1.2 Файлы параметров `my.cnf').
Проверьте также код полученной ошибки с помощью perror. Одной из причин также может быть ошибка переполнения диска;
shell> perror 28 Error code 28: No space left on device
A.2.12 Ошибка в клиенте Commands out of sync
Если получена ошибка Commands out of sync; you can't run this command now в клиентском коде, то клиентские функции вызываются в неправильном порядке!
Это может произойти, например, если используется mysql_use_result() и делается попытка выполнить новый запрос до того, как вызвана mysql_free_result(), или если клиент пытается выполнить два возвращающих данные запроса без обращения к mysql_use_result() либо mysql_store_result() в промежутке между ними.
A.2.13 Ошибка Ignoring user
Получение следующей ошибки:
Found wrong password for user: 'some_user@some_host'; ignoring user
означает, что при запуске mysqld или при перезагрузке таблиц привилегий сервер нашел в таблице user запись с неправильным паролем и в результате запись просто игнорируется системой привилегий.
Причины проблемы и способы ее решения могут быть следующими:
Возможно, работает новая версия mysqld со старой таблицей user. Это можно проверить путем выполнения mysqlshow mysql user, чтобы посмотреть, короче ли поле пароля, чем 16 символов. Если это так, то положение можно исправить, запустив сценарий `scripts/add_long_password'.
У пользователя старый пароль (длиной в 8 символов), а mysqld запущен без опции --old-protocol. Обновите данные пользователя в таблице user, задав новый пароль, или перезапустите mysqld с --old-protocol.
Пароль был установлен в таблице user без применения функции PASSWORD(). Воспользуйтесь mysql для обновления пароля пользователя в таблице user. Позаботьтесь о том, чтобы была использована функция PASSWORD():
mysql> UPDATE user SET password=PASSWORD('your password') -> WHERE user='XXX';
A.2.14 Ошибка Table 'xxx' doesn't exist
Получение ошибки Table 'xxx' doesn't exist или Can't find file: 'xxx' (errno: 2), означает, что в текущей базе данных не существует таблицы с именем xxx.
Обратите внимание: поскольку в MySQL для хранения баз данных и таблиц используются каталоги и файлы, то имена баз данных и каталогов являются
зависимыми от регистра символов! (Под Windows имена баз данных и таблиц не зависят от регистра символов, но для всех ссылок на заданную таблицу в пределах запроса должен использоваться одинаковый регистр!)
Проверить, какие таблицы имеются в текущей базе данных, можно с помощью SHOW TABLES. see section 4.5.6 Синтаксис команды SHOW.
A.2.15 Ошибка Can't initialize character set xxx
Получение ошибки наподобие:
MySQL Connection Failed: Can't initialize character set xxx
Означает, что имеется одна из следующих ситуаций:
Кодировка является многобайтовой и не поддерживается клиентом. В этом случае необходимо перекомпилировать клиент с --with-charset=xxx или с --with-extra-charsets=xxx (see section 2.3.3 Типичные опции configure). Весь стандартный бинарный код MySQL откомпилирован с --with-extra-character-sets=complex, что обеспечивает поддержку всех многобайтовых кодировок (see section 4.6.1 Набор символов, применяющийся для записи данных и сортировки).
Кодировка является простой кодировкой, не скомпилированной в mysqld, и файлы определения кодировки находятся не там, где рассчитывает их найти клиент. В этом случае необходимо:
Перекомпилировать клиент с поддержкой этой кодировки (see section 2.3.3 Типичные опции configure).
Указать клиенту, где расположены файлы определения кодировки. Для многих клиентов это можно сделать с помощью опции --character-sets-dir=path-to-charset-dir.
Скопировать файлы определения кодировки туда, где, по мнению клиента, они должны находиться.
A.2.16 Не найден файл (File not found)
Получение от MySQL ERROR '...' not found (errno: 23), Can't open file: ... (errno: 24), или любой другой ошибки с номером 23 или 24 означает, что для MySQL выделено недостаточно файловых дескрипторов. Можно использовать утилиту perror для получения описания ошибки с определенным номером:
shell> perror 23 File table overflow shell> perror 24 Too many open files shell> perror 11 Resource temporarily unavailable
Проблема здесь в том, что mysqld пытается одновременно держать открытыми слишком много файлов. Можно либо указать mysqld не открывать так много файлов одновременно, либо увеличить количество файловых дескрипторов, доступных mysqld.
Чтобы предписать mysqld держать одновременно открытыми меньше файлов, можно уменьшить табличный кэш, задав safe_mysqld опцию -O table_cache=32
(значение по умолчанию 64). Уменьшение значения max_connections также уменьшит количество открытых файлов (по умолчанию значение переменной 90).
Чтобы изменить число доступных mysqld файловых дескрипторов, можно использовать опцию --open-files-limit=# в safe_mysqld или опцию -O open-files-limit=# в mysqld (see section 4.5.6.4 SHOW VARIABLES). Проще всего это сделать путем добавления опции в файл опций (see section 4.1.2 Файлы параметров `my.cnf'). Если используется ранняя версия mysqld, не поддерживающая эту возможность, можно отредактировать скрипт safe_mysqld. В скрипте есть закомментированная строка ulimit -n 256; можно, убрав символ `#', раскомментировать эту строку и, заменив значение 256 другим, повлиять на количество доступных mysqld файловых дескрипторов.
При помощи ulimit (и open-files-limit) можно увеличить количество файловых дескрипторов только до предела, установленного в операционной системе. Существует также "жесткий" предел, который может быть переопределен только при запуске safe_mysqld или mysqld от имени пользователя root (но помните, что в этом случае также следует использовать опцию --user=...). Если необходимо увеличить предел ОС по количеству доступных отдельному процессу файловых дескрипторов, обращайтесь к документации по своей операционной системе.
Обратите внимание: при использовании tcsh ulimit работать не будет! Кроме того, tcsh будет сообщать неверные значения при запросе текущих пределов! В этом случае необходимо запускать safe_mysqld с использованием sh!
A.2 Распространенные ошибки при использовании MySQL
В этом разделе перечислены некоторые ошибки, с которыми часто приходится сталкиваться пользователям; дается описание этих ошибок и способы их исправления.
A.3.1 Проблемы при линковании с клиентской библиотекой MySQL
Если при линковании программы получены ошибки неразрешенных ссылок на символы, имена которых начинаются с mysql_, подобные следующим:
/tmp/ccFKsdPa.o: In function `main': /tmp/ccFKsdPa.o(.text+0xb): undefined reference to `mysql_init' /tmp/ccFKsdPa.o(.text+0x31): undefined reference to `mysql_real_connect' /tmp/ccFKsdPa.o(.text+0x57): undefined reference to `mysql_real_connect' /tmp/ccFKsdPa.o(.text+0x69): undefined reference to `mysql_error' /tmp/ccFKsdPa.o(.text+0x9a): undefined reference to `mysql_close'
то их можно устранить, добавив -Lpath-to-the-mysql-library -lmysqlclient в самом конце используемой команды линкования.
Если выдаются ошибки undefined reference для функции uncompress или compress, добавьте в самом конце команды линкования -lz и повторите попытку!
Если получены ошибки undefined reference для функций, которые должны присутствовать в системе, таких как connect, то сверьтесь по странице руководства для данной функции, какие библиотеки необходимо добавить в команде линкования!
Получение ошибок undefined reference для функций, отсутствующих в используемой системе, наподобие следующего:
mf_format.o(.text+0x201): undefined reference to `__lxstat'
обычно означает, что библиотека скомпилирована в системе, которая не на 100% совместима с системой пользователя. В этом случае необходимо загрузить последнюю поставку с исходными текстами MySQL и откомпилировать библиотеку самостоятельно (see section 2.3 Установка исходного дистрибутива MySQL).
Если при попытке выполнить программу выдаются ошибки о ненайденных символах, начинающихся с mysql_, или о том, что не удается найти библиотеку mysqlclient, то это означает, что система не может найти динамической библиотеки `libmysqlclient.so'.
Чтобы исправить это, необходимо предписать системе проводить поиск динамических библиотек в каталоге с клиентской библиотекой MySQL. Это можно сделать одним из следующих способов:
Добавить в переменную окружения LD_LIBRARY_PATH путь к каталогу, содержащему `libmysqlclient.so'.
Добавить в переменную окружения LD_LIBRARY путь к каталогу, содержащему `libmysqlclient.so'.
Скопировать `libmysqlclient.so' в каталог, просматриваемый системой при поиске библиотек, таких как `/lib', и обновить информацию о распределенных библиотеках, выполнив ldconfig.
Другой способ решения этой проблемы заключается в статическом линковании программы с использованием -static, или в удалении динамических библиотек MySQL до линкования своего кода. Во втором случае необходимо удостовериться, что динамические библиотеки не используются другими программами!
A.3.2 Запуск MySQL от обычного пользователя
Сервер mysqld может запускаться и работать от любого пользователя. Чтобы настроить mysqld для работы под Unix-пользователем user_name, необходимо выполнить следующие действия:
Если сервер работает, остановите его (используйте mysqladmin shutdown).
Измените каталоги и файлы баз данных так, чтобы user_name имел привилегии для чтения и записи файлов в этих каталогах (возможно, это нужно будет делать из Unix-аккаунта root):
shell> chown -R user_name /path/to/mysql/datadir
Если среди каталогов или файлов в каталоге данных MySQL присутствуют символические ссылки, то нужно будет также перейти по этим ссылкам и изменить каталоги и файлы, на которые они указывают. chown -R может не отработать символических ссылок.
Запустите сервер из аккаунта user_name или, если у вас MySQL 3.22 и выше, запустите mysqld из Unix-аккаунта root и используйте опцию --user=user_name. mysqld переключится на выполнение в контексте Unix-пользователя user_name до того, как начнет обслуживать запросы на соединение.
Чтобы запускать сервер от имени данного пользователя автоматически в ходе загрузки системы, добавьте строку user, указывающую имя пользователя, в группе [mysqld] файла опций `/etc/my.cnf' или файла опций `my.cnf' из каталога данных сервера. Например:
[mysqld] user=user_name
К этому моменту процесс mysqld должен без помех работать под Unix-пользователем user_name. Однако содержимое таблиц привилегий не изменяется. По умолчанию (сразу после выполнения скрипта инсталляции таблиц привилегий mysql_install_db) MySQL-пользователь root является единственным пользователем с правами на доступ к базе данных mysql, а также на создание и удаление баз данных. Если вы не меняли этих полномочий, они по-прежнему действительны. То, что вы вошли в систему как Unix-пользователь, отличный от root, не может вам помешать получить доступ к MySQL в качестве MySQL-пользователя root; просто задайте клиентской программе опцию -u root.
Отметим, что работа с MySQL в качестве MySQL-пользователя root посредством указания -u root в командной строке не имеет ничего общего с выполнением MySQL под Unix-пользователем root, или вообще под каким бы то ни было Unix-пользователем. Привилегии доступа и имена пользователей MySQL никак не связаны с именами Unix-пользователей. Единственная связь с именами Unix-пользователей заключается в том, что если при запуске клиентской программы не задана опция -u, то клиент попытается соединиться, используя в качестве имени MySQL-пользователя имя Unix-аккаунта.
Если Unix-сервер не слишком хорошо защищен или не нуждается в повышенных мерах безопасности, следует как минимум установить в таблицах привилегий пароль для MySQL-пользователя root. В противном случае любой пользователь с аккаунтом на данной машине сможет запустить mysql -u root db_name и делать в MySQL все, что ему заблагорассудится.
A.3.3 Проблемы с правами доступа к файлам
Если существуют проблемы с правами доступа к файлам, например, если mysql
при создании таблицы выдает следующее сообщение об ошибке:
ERROR: Can't find file: 'path/with/filename.frm' (Errcode: 13)
то, возможно, при запуске сервера mysqld неправильно устанавливается переменная окружения UMASK. По умолчанию значение umask 0660. Его можно изменить, запуская safe_mysqld следующим образом:
shell> UMASK=384 # = 600 in octal shell> export UMASK shell> /path/to/safe_mysqld &
По умолчанию MySQL создает каталоги баз данных и RAID-каталоги с правами доступа 0700. Такое поведение можно изменить, установив переменную UMASK_DIR. Если установить эту переменную, то при создании новых каталогов используется комбинация UMASK и UMASK_DIR. Например, если требуется, чтобы доступ ко всем новым каталогам получала группа, то можно выполнить:
shell> UMASK_DIR=504 # = 770 in octal shell> export UMASK_DIR shell> /path/to/safe_mysqld &
В версии MySQL 3.23.25 и выше MySQL предполагает, что значения для UMASK и UMASK_DIR, начинающиеся с нуля, восьмеричные.
see section F Переменные окружения.
A.4.1 Что делать, если работа MySQL сопровождается постоянными сбоями
Перед официальным выпуском все версии MySQL тестируются на многих платформах. Это не означает, что в MySQL совсем нет ошибок, но если они и есть, то мало, и их не так просто отыскать. В любом случае, столкнувшись с какой-либо проблемой, всегда полезно попытаться точно определить, что вызывает аварию системы, - тогда шансы, что проблема будет устранена в скором времени, станут значительно выше.
Сначала надо попробовать локализовать проблему. Определите, что происходит: то ли демон mysqld прекращает работу, то ли проблема связана с клиентом. Узнать, сколько времени сервер mysqld уже работает, можно, выполнив mysqladmin version. Если mysqld прекратил выполнение, то для выяснения причин можно изучить файл `mysql-data-directory/`hostname`.err'
(see section 4.9.1 Журнал ошибок).
Причиной многих аварий MySQL являются поврежденные индексные файлы или файлы данных. MySQL обновляет данные на диске, используя системный вызов write(), после каждой команды SQL и до того, как клиент будет уведомлен о результате (однако при выполнении с delay_key_write это не так: записываются только данные). Отсюда следует, что данные не пострадают даже в случае аварийного завершения mysqld, поскольку ОС позаботится о том, чтобы те данные, которые не сброшены, были записаны на диск. Можно заставить MySQL сбрасывать все на диск после каждой SQL-команды, запустив mysqld с --flush.
Все это означает, что обычно таблицы не должны повреждаться; исключение составляют следующие случаи:
Кто-нибудь/что-нибудь убьет процесс mysqld или выключит машину посреди операции обновления.
Проявила себя ошибка в mysqld, вызывающая прекращение его выполнения посреди операции обновления.
Кто-нибудь работает с файлами данных или индексными файлами вне mysqld и при этом не делает блокировку таблиц как следует.
Если работает несколько серверов mysqld с одними данными на системе без пристойной поддержки блокировок файловой системы (обычно реализуется демоном lockd) или если выполняется несколько серверов со --skip-external-locking
Существует поврежденный индексный файл или файл данных, содержащий очень неправильные данные, которые вводят в заблуждение mysqld.
Проявила себя в коде записи данных. Это маловероятно, но в общем случае возможно. В этом случае можно попробовать изменить формат файла на соответствующий другому обработчику баз данных, используя ALTER TABLE на исправленной копии таблицы!
Поскольку найти причину сбоя всегда непросто, сначала можно попробовать выяснить, что из того, что работает у других, вызывает аварии у вас. Попытайтесь выполнить следующие действия:
Остановите демон mysqld с помощью mysqladmin shutdown, выполните myisamchk --silent --force */*.MYI на всех таблицах и перезапустите демон mysqld. Этим гарантируется безошибочность исходного состояния (see section 4 Администрирование баз данных).
Используйте mysqld --log и попытайтесь определить по информации в журналах, не вызвано ли прекращение работы сервера каким-либо специфическим запросом. Около 95% всех ошибок обусловлены конкретными запросами! Обычно это один из последних запросов в журнальном файле непосредственно до перезапуска MySQL (see section 4.9.2 Общий журнал запросов). Если вы сумеете повторно вызвать отказ MySQL при помощи одного из запросов, даже когда таблицы были проверены непосредственно перед выполнением запроса, то возможна локализация ошибки и подготовка отчета об ошибке! see section 1.8.1.3 Как отправлять отчеты об ошибках или проблемах.
Попробуйте сделать контрольный тест, который мы могли бы использовать, чтобы воспроизвести проблему (see section E.1.6 Создание контрольного примера при повреждении таблиц).
Попробуйте выполнить входящий в поставку тест mysql-test и тесты производительности MySQL (see section 9.1.2 Пакет тестирования MySQL). Эти тесты должны довольно хорошо протестировать MySQL. Вы можете также добавить в тесты производительности код для имитации своего приложения! Тесты производительности можно найти в каталоге bench в поставке с исходными кодами или, в случае бинарной поставки, в подкаталоге sql-bench своего каталога инсталляции MySQL.
Попробуйте fork_test.pl и fork2_test.pl.
Если что- то пойдет не так, то собирать информацию о возможных ошибках будет значительно проще, если MySQL сконфигурирован для отладки. Переконфигурируйте MySQL, применяя configure с опцией --with-debug или --with-debug=full, и затем перекомпилируйте (see section E.1 Отладка сервера MySQL).
Конфигурирование MySQL в отладочном режиме приводит к включению безопасного распределителя памяти, который может находить некоторые ошибки. Помимо этого, отладочная версия выдает большое количество информации о том, что происходит.
Выясните, применены ли последние патчи для используемой операционной системы.
Используйте опцию --skip-external-locking к mysqld. На некоторых системах менеджер блокировок lockd не работает как следует; опция --skip-external-locking указывает mysqld не применять внешнюю блокировку (это означает, что нельзя выполнять два сервера mysqld на одних данных и что необходимо быть осторожным при использовании myisamchk, однако применение этой опции может принести большую пользу для целей тестирования).
Если возникнет ситуация, когда кажется, что mysqld запущен, но не отвечает, стоит попробовать выполнить mysqladmin -u root processlist. Иногда mysqld не является зависшим, даже если кажется, что это так. Проблема может быть в том, что все соединения используются, или, возможно, имеется некая внутренняя проблема с блокировками. mysqladmin processlist обычно способна установить соединение даже в таких случаях и выдать полезную информацию о текущем количестве соединений и их состоянии.
Выполните команду в отдельном окне mysqladmin -i 5 status или mysqladmin -i 5 -r для вывода статистики, пока будут выполняться другие запросы.
Попробуйте выполнить следующие действия:
Запустите mysqld в gdb (или в другом отладчике). See section E.1.3 Отладка mysqld при помощи gdb.
Запустите тестовые скрипты.
Отобразите стек (backtrace) и локальные переменные на трех нижних уровнях. В gdb это можно сделать следующими командами после аварийного завершения mysqld внутри gdb:
backtrace info local up info local up info local
С помощью gdb можно также выяснить, какие имеются потоки (посредством info threads), и переключиться на определенный поток посредством thread #, где # - номер потока.
Попробуйте имитировать работу своего приложения с помощью Perl-скрипта, который бы вызвал аварийное завершение или неправильное функционирование MySQL.
Отправьте нам обычный отчет об ошибке (see section 1.8.1.3 Как отправлять отчеты об ошибках или проблемах). Любые подробности будут нелишними. Поскольку MySQL нормально эксплуатируется в очень многих местах, то, возможно, авария вызвана причиной, которая свойственна только вашему компьютеру (например, ошибка, связанная с вашими особенными системными библиотеками).
Если возникла проблема с таблицами, имеющими динамическую длину строк, и не используются столбцы типа BLOB/TEXT (а только столбцы типа VARCHAR), то можно попробовать изменить все VARCHAR на CHAR с помощью ALTER TABLE. Это заставит MySQL использовать строки фиксированного размера. Для строк фиксированного размера понадобится немного дополнительной памяти, однако они гораздо менее чувствительны к повреждениям! Сегодняшний код динамических строк без каких бы то ни было проблем эксплуатируется в MySQL AB по крайней мере 3 года, но в принципе строки динамической длины более подвержены ошибкам, поэтому данный рецепт, возможно, сможет вам чем-то помочь!
A.4.2 Как переустановить забытый пароль пользователя root
Если для MySQL пароль пользователя root никогда не устанавливался, то для соединения с сервером в качестве пользователя root пароль не потребуется. Рекомендуется всегда устанавливать пароль для каждого пользователя (see section 4.2.2 Как обезопасить MySQL от хакеров).
Если вы забыли установленный для root пароль, то новый пароль можно задать при помощи следующей процедуры:
Остановите сервер mysqld; для этого нужно послать kill (но не kill -9) серверу mysqld. Номер процесса хранится в файле `.pid', обычно расположенном в каталоге баз данных MySQL:
shell> kill `cat /mysql-data-directory/hostname.pid`
Чтобы выполнить эту команду, необходимо быть либо Unix-пользователем root, либо пользователем, под которым работает mysqld.
Перезапустите mysqld с опцией --skip-grant-tables.
Установите новый пароль с помощью команды mysqladmin password:
shell> mysqladmin -u root password 'mynewpassword'
Теперь можно либо просто остановить mysqld и заново запустить его обычным способом, либо просто загрузить таблицы привилегий посредством:
shell> mysqladmin -h hostname flush-privileges
После этого можно будет соединяться, пользуясь новым паролем.
Есть и другой способ установки нового пароля - с помощью клиента mysql:
Остановите mysqld и перезапустите его с опцией --skip-grant-tables, как было описано ранее.
Соединитесь с сервером mysqld посредством:
shell> mysql -u root mysql
Выполните следующие команды из клиента mysql:
mysql> UPDATE user SET Password=PASSWORD('mynewpassword') -> WHERE User='root'; mysql> FLUSH PRIVILEGES;
После этого можно будет соединяться по новому паролю.
Остановите mysqld и запустите его заново, как обычно.
A.4.3 Как MySQL реагирует на переполнение диска
Когда возникает ситуация переполнения диска, MySQL реагирует следующим образом:
Один раз в минуту сервер проверяет, достаточно ли места для записи текущей строки. Если места достаточно, то сервер продолжает работу так, как будто ничего не произошло.
Каждые 6 минут сервер помещает в журнальный файл запись с предупреждением о ситуации переполнения диска.
Для снижения остроты проблемы можно принять следующие меры:
Чтобы продолжать работу, необходимо просто освободить на диске место, достаточное для вставки всех записей.
Чтобы прервать поток, необходимо послать потоку mysqladmin kill. Поток будет аварийно прекращен, когда он в следующий раз проверит диск (в течение 1 минуты).
Учтите, что таблицу, вызвавшую ситуацию переполнения диска, могут ожидать другие потоки. Если существует несколько "блокированных" потоков, то удаление одного потока, ожидающего по причине переполнения диска, позволит продолжаться другим.
Исключение составляет использование REPAIR или OPTIMIZE, а также случай, когда индексы создаются в пакете после команды LOAD DATA INFILE или ALTER TABLE.
Все упомянутые команды могут использовать большие временные файлы, которые, если их оставить без внимания, могут вызвать большие проблемы во всей системе. Если MySQL сталкивается с переполнением диска при выполнении одной из указанных операций, то сервер удаляет большие временные файлы и отмечает таблицу как поврежденную (за исключением ALTER TABLE, для которого старая таблица остается без изменений).
A.4.4 Где MySQL хранит временные файлы
Переменная окружения TMPDIR содержит полное имя каталога, в котором в MySQL хранит временные файлы. Если TMPDIR не установлена, то MySQL использует каталог, заданный в системе по умолчанию (обычно это `/tmp' или `/usr/tmp'). Если файловая система, в которой находится каталог временных файлов, слишком мала, то следует, отредактировав safe_mysqld, присвоить TMPDIR значение, указывающее на каталог в "более просторной" файловой системе! Временный каталог можно также задавать с помощью опции --tmpdir к mysqld.
Все временные файлы MySQL создает как скрытые; таким образом гарантируется, что временные файлы будут удалены, если mysqld умрет. Недостаток использования скрытых файлов в том, что не будут видны большие временные файлы, забирающие место в файловой системе, где расположен каталог временных файлов.
При сортировке (ORDER BY или GROUP BY) MySQL обычно использует один или два временных файла. Максимальный размер требующегося для этого пространства на диске составляет:
(размер сортируемых данных + sizeof(указатель базы данных)) * количество совпавших записей * 2
sizeof(указатель базы данных) обычно равен 4, но со временем для очень больших таблиц может увеличиться.
Для некоторых запросов SELECT MySQL также создает временные SQL-таблицы. Они не скрытые и имеют имена вида `SQL_*'.
ALTER TABLE создает временную таблицу в одном каталоге с исходной таблицей.
Если вы используете версию 4.1 или более новую - вы можете распределять нагрузку между несколькими физическими дисками путем установления --tmpdir в список путей, разделенных двоеточием : (точкой с запятой ; - под Winodws). Они будут использоваться в ротации. Замечание: эти пути должны представлять различные физические диски, а не различные разделы одного и того же диска.
A.4.5 Как защитить или изменить сокет-файл MySQL `/tmp/mysql.sock'
Если возникают проблемы с тем, что кто угодно может удалить коммуникационный сокет MySQL `/tmp/mysql.sock', то в большинстве версий Unix можно защитить содержимое `/tmp', установив на каталоге ``липучий'' (sticky) бит. Войдите в систему как пользователь root и выполните следующую команду:
shell> chmod +t /tmp
Это защитит ваш каталог `/tmp': теперь удалять в нем файлы смогут только их владельцы или суперпользователь (root).
Проверить, установлен ли ``липучий'' (sticky) бит, можно, выполнив ls -ld /tmp. Если последним битом прав является t, то бит установлен.
Изменить путь к каталогу, где MySQL открывает сокет-файл, можно, воспользовавшись одним из следующих способов:
Укажите путь в глобальном или локальном файле опций. Например, поместите в `/etc/my.cnf':
[client] socket=path-for-socket-file
[mysqld] socket=path-for-socket-file
See section 4.1.2 Файлы параметров `my.cnf'.
Укажите, где расположен файл, в командной строке safe_mysqld и большинства клиентов с помощью опции --socket=путь-к-файлу-сокета.
Укажите путь к сокету в переменной окружения MYSQL_UNIX_PORT.
Определите путь с помощью опции конфигурирования --with-unix-socket-path=путь-к-файлу-сокета (see section 2.3.3 Типичные опции configure).
Проверить, работает ли сокет, можно следующей командой:
shell> mysqladmin --socket=/path/to/socket version
A.4.6 Проблемы с часовыми поясами
Если есть проблема с тем, что SELECT NOW() возвращает значения GMT, а не местное время, то необходимо установить переменную окружения TZ равной местному часовому поясу. Это должно быть сделано для окружения, в котором работает сервер, например, в safe_mysqld или mysql.server (see section F Переменные окружения).
A.5.1 Чувствительность к регистру при поиске
По умолчанию поиск в MySQL является независимым от регистра символов (хотя существуют некоторые кодировки, которые всегда чувствительны к регистру, такие как czech). Это означает, что при поиске с помощью col_name LIKE 'a%' будут выданы все значения столбца, начинающиеся на A или a. Если необходимо выполнить тот же поиск с учетом регистра, для проверки префикса следует использовать что-то вроде INSTR(col_name, "A")=1 или STRCMP(col_name, "A") = 0, если значение в столбце точно равно A.
Простые операции сравнения (>=, >, =, , сортировка и группировка) основываются на "сорте" каждого символа. Символы одного сорта (такие как E, e и E) обрабатываются как одинаковые символы!
В старых версиях MySQL сравнения по LIKE выполнялись над символами, переведенными в верхний регистр (E == e, но E <> E). В новых версиях MySQL LIKE работает точно так же, как другие операторы сравнения.
Если необходимо, чтобы столбец всегда обрабатывался в с учетом регистра, объявите его с типом BINARY (see section 6.5.3 Синтаксис оператора CREATE TABLE).
Если вы используете китайские данные в так называемой кодировке big5, то имеет смысл объявить все символьные столбцы как BINARY. Сортировка таких столбцов будет работать, поскольку порядок сортировки символов в кодировке big5 основывается на порядке кодов ASCII.
A.5.2 Проблемы с использованием столбцов типа DATE
Значения типа DATE имеют формат YYYY-MM-DD; согласно стандарту ANSI SQL, никакой другой формат не допускается. Пользователь должен применять этот формат в выражениях UPDATE и в определении WHERE операторов SELECT. Например:
mysql> SELECT * FROM tbl_name WHERE date >= '1997-05-05';
Из соображений удобства MySQL автоматически преобразовывает дату в число, если дата используется в числовом контексте (и наоборот). Благодаря своей ``разумности'' MySQL допускает также ``мягкую'' строковую форму при обновлении и в определении WHERE, сравнивающем дату со столбцом типа TIMESTAMP, DATE
или DATETIME ("мягкая" форма означает, что для разделения составляющих даты можно использовать любой знак пунктуации; например, 1998-08-15 и 1998#08#15 эквивалентны). MySQL может также преобразовывать в даты строки, не содержащие разделителей (наподобие 19980815), при условии, что представляемая строкой дата не лишена смысла.
Специальная дата 0000-00-00 может записываться и извлекаться в виде 0000-00-00. При использовании даты 0000-00-00 из MyODBC она будет автоматически преобразована в NULL в версии MyODBC 2.50.12 и выше, так как ODBC не обеспечивает обработку такого рода дат.
Поскольку в MySQL выполняются описанные выше преобразования, следующие команды будут работать:
mysql> INSERT INTO tbl_name (idate) VALUES (19970505); mysql> INSERT INTO tbl_name (idate) VALUES ('19970505'); mysql> INSERT INTO tbl_name (idate) VALUES ('97-05-05'); mysql> INSERT INTO tbl_name (idate) VALUES ('1997.05.05'); mysql> INSERT INTO tbl_name (idate) VALUES ('1997 05 05'); mysql> INSERT INTO tbl_name (idate) VALUES ('0000-00-00'); mysql> SELECT idate FROM tbl_name WHERE idate >= '1997-05-05'; mysql> SELECT idate FROM tbl_name WHERE idate >= 19970505; mysql> SELECT MOD(idate,100) FROM tbl_name WHERE idate >= 19970505; mysql> SELECT idate FROM tbl_name WHERE idate >= '19970505';
Однако приведенные ниже команды работать не будут:
mysql> SELECT idate FROM tbl_name WHERE STRCMP(idate,'19970505')=0;
STRCMP() - строковая функция, следовательно, она преобразует idate в строку и сравнивает строки. Функция не станет преобразовывать 19970505 в дату и сравнивать даты.
Заметим, что MySQL осуществляет очень ограниченную проверку правильности дат, поэтому такая некорректная дата, как 1998-2-31, будет занесена в базу.
Поскольку MySQL пакует даты для сохранения, он не может сохранить любую дату, так как она может не поместится в результирующий буфер. Правила принятия дат следующие:
Если MySQL может хранить и выбирать заданную дату, неправильная дата принимается для столбцов DATE и DATETIME.
Все значения дней между 0-31 принимаются для любой даты, это удобно для приложения, где вы запрашиваете год, месяц и день в 3х различных полях.
День или месяц могут быть нулем. Это удобно когда вы хотите хранить дату рождения в столбце DATE и знаете только часть ее.
Если же дату нельзя преобразовать в какое-нибудь разумное значение, в поле типа DATE заносится 0. Проверка правильности дат не делается - в основном из соображений, связанных со скоростью: мы считаем, что проверкой дат должно заниматься приложение, а не сервер.
A.5.3 Проблемы со значением NULL
Концепция NULL-значения часто вводит в заблуждение новичков в SQL, которые считают, что NULL - то же, что и пустая строка "". Это ошибка! Например, следующие команды совершенно различны:
mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES ("");
Обе команды вставляют значение в столбец phone, но первая - значение NULL, а вторая - пустую строку. Смысл первого можно передать как ``номер телефона неизвестен'', смысл второго - ``у нее нет телефона''.
В SQL сравнение значения NULL с любым другим значением, даже со значением NULL, всегда ложно. Выражение, содержащее NULL, всегда дает значение NULL, за исключением случаев, специально оговоренных в документации по операторам и функциям, присутствующим в выражении. Все столбцы в следующем примере возвращают NULL:
mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);
Если в столбце нужно найти значения NULL, то нельзя использовать условие =NULL. Следующая команда не возвращает ни одной строки, поскольку для любого выражения expr = NULL ЛОЖНО:
mysql> SELECT * FROM my_table WHERE phone = NULL;
Для поиска значений NULL необходимо использовать проверку IS NULL. Ниже показано, как найти телефонный номер NULL и пустой телефонный номер:
mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = "";
Заметим, что добавлять индекс по столбцу, в котором допускаются значения NULL, можно только в случае, если вы работаете с версией MySQL 3.23.2 или выше, а используемый тип таблиц - MyISAM или InnoDB. В более ранних версиях или для других типов таблиц необходимо объявлять такие столбцы с атрибутом NOT NULL. Это также подразумевает, что тогда нельзя вставлять NULL в индексированный столбец.
При чтении данных с помощью LOAD DATA INFILE пустые поля обновляются значениями ''. Если необходимо поместить в столбец значение NULL, то в текстовом файле следует использовать \N. Также при некоторых обстоятельствах можно использовать слово-литерал NULL (see section 6.4.9 Синтаксис оператора LOAD DATA INFILE).
При использовании ORDER BY значения NULL выдаются первыми. В версиях предшествующих 4.0.2, при сортировке в убывающем порядке при помощи DESC, значения NULL также выдаются последними. При использовании GROUP BY все значения NULL считаются равными.
Для обработки NULL предназначены операторы IS NULL и IS NOT NULL, а также функция IFNULL().
Для некоторых типов столбцов значения NULL обрабатываются специальным образом. Если NULL вставляется в первый в таблице столбец типа TIMESTAMP, то в него помещается значение текущей даты и времени. При вставке NULL в AUTO_INCREMENT-столбец вставляется следующее число последовательности.
A.5.4 Проблемы с alias
Псевдонимы можно использовать для ссылки на столбец в GROUP BY, ORDER BY
или в части HAVING, а также для лучшего именования столбцов:
SELECT SQRT(a*b) as rt FROM table_name GROUP BY rt HAVING rt > 0; SELECT id,COUNT(*) AS cnt FROM table_name GROUP BY id HAVING cnt > 0; SELECT id AS "Customer identity" FROM table_name;
Заметим, что в ANSI SQL запрещено ссылаться на псевдоним в определении WHERE. Вызвано это тем, что при выполнении кода WHERE значение столбца может быть еще не определенным. Например, следующий запрос недопустим:
SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id;
Выражение WHERE выполняется, чтобы определить, какие строки следует включить в часть GROUP BY, тогда как HAVING используется для тех строк из результирующего множества, которые должны использоваться.
A.5.5 Удаление строк из взаимосвязанных таблиц
Поскольку MySQL до 4.1.0 не поддерживает вложенных запросов, а также использование более одной таблицы в команде DELETE (до версии 4.0), то для удаления строк из 2 взаимосвязанных таблиц следует использовать следующий подход:
Выполните в главной таблице SELECT строк на основе некоторого условия WHERE.
Выполните DELETE строк главной таблицы на основе этого же условия.
DELETE FROM related_table WHERE related_column IN (selected_rows).
где selected_rows - количество строк, выбранных по запросу в п.1.
Если общее количество символов в запросе с related_column больше 1048576 (значение max_allowed_packet по умолчанию), то следует разбить запрос на меньшие части и выполнить несколько команд DELETE. Если related_column
является индексом/индексирован, то самый быстрый DELETE получится при удалении 100-1000 идентификаторов related_column за запрос. Если related_column не индекс, то скорость не зависит от количества аргументов в операторе IN.
A.5.6 Решение проблем с отсутствием строк, удовлетворяющих условиям поиска
Если сложный запрос на множестве таблиц совсем не возвращает строк, то для выяснения причин его неуспешного выполнения необходимо выполнить следующую последовательность действий:
Проверьте запрос с помощью EXPLAIN и посмотрите, не присутствуют ли в нем явно неправильные записи (see section 5.2.1 Синтаксис оператора EXPLAIN (получение информации о SELECT)).
Выберите только поля, используемые в выражении WHERE.
По одной убирайте из запроса таблицы до тех пор, пока не станут возвращаться какие-нибудь строки. Если таблицы большие, то в запросе имеет смысл использовать LIMIT 10.
Выполните SELECT для столбца, который должен был дать совпадение строки с последней исключенной из запроса таблицей.
Если столбцы типа FLOAT или DOUBLE сравниваются с имеющими дробную часть числами, то нельзя использовать `='. Это распространенная проблема в большинстве компьютерных языков, поскольку значения с плавающей запятой не являются точными. В большинстве случаев проблему решает изменение FLOAT на DOUBLE. See section A.5.7 Проблемы со сравнением чисел с плавающей точкой.
Если так и не удалось выяснить, в чем загвоздка, то для демонстрации возникших у вас проблем создайте минимальный тест, запускающийся при помощи mysql test < query.sql. Тестовый файл можно создать, воспользовавшись mysqldump --quick database tables > query.sql. Далее откройте файл в редакторе, удалите некоторые команды вставки (если их слишком много) и добавьте в конце файла свою команду выборки. Убедитесь, что проблема по-прежнему проявляется, посредством:
shell> mysqladmin create test2 shell> mysql test2 < query.sql
Используя mysqlbug, пошлите тестовый файл в список рассылки на mysql@lists.mysql.com.
A.5.7 Проблемы со сравнением чисел с плавающей точкой
Числа с плавающей точкой иногда служат источником неприятностей, поскольку эти числа архитектурно хранятся в компьютере не как точные числа. То, что обычно мы видим на экране, не является точным значением числа.
Поля типов FLOAT, DOUBLE и DECIMAL следующие.
CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2)); INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00), (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40), (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00), (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00), (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20), (6, 0.00, 0.00), (6, -51.40, 0.00);
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b -> FROM t1 GROUP BY i HAVING a <> b; +------+--------+-------+ | i | a | b | +------+--------+-------+ | 1 | 21.40 | 21.40 | | 2 | 76.80 | 76.80 | | 3 | 7.40 | 7.40 | | 4 | 15.40 | 15.40 | | 5 | 7.20 | 7.20 | | 6 | -51.40 | 0.00 | +------+--------+-------+
Результат правильный. Хотя кажется, что первые пять записей должны быть отброшены, не выдержав проверки на сравнение, тем не менее, они могут удовлетворить условиям по той причине, что в зависимости от архитектуры компьютера между числами проявляются отличия примерно на уровне десятого знака.
С помощью ROUND() (или подобной функции) проблема не может быть решена, поскольку результат все равно будет числом с плавающей запятой, например:
mysql> SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b -> FROM t1 GROUP BY i HAVING a <> b; +------+--------+-------+ | i | a | b | +------+--------+-------+ | 1 | 21.40 | 21.40 | | 2 | 76.80 | 76.80 | | 3 | 7.40 | 7.40 | | 4 | 15.40 | 15.40 | | 5 | 7.20 | 7.20 | | 6 | -51.40 | 0.00 | +------+--------+-------+
Вот как выглядят числа в столбце 'a':
mysql> SELECT i, ROUND(SUM(d1), 2)*1.0000000000000000 AS a, -> ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i HAVING a <> b; +------+----------------------+-------+ | i | a | b | +------+----------------------+-------+ | 1 | 21.3999999999999986 | 21.40 | | 2 | 76.7999999999999972 | 76.80 | | 3 | 7.4000000000000004 | 7.40 | | 4 | 15.4000000000000004 | 15.40 | | 5 | 7.2000000000000002 | 7.20 | | 6 | -51.3999999999999986 | 0.00 | +------+----------------------+-------+
В вашей системе результаты могут либо такими, либо нет - это зависит от архитектуры компьютера. Каждый процессор выполняет вычисления с плавающей точкой по-своему. Например, на некоторых машинах можно получить ``правильные'' результаты, если умножить оба аргумента на 1 (см. пример ниже).
ПРЕДУПРЕЖДЕНИЕ: НИКОГДА НЕ ПОЛАГАЙТЕСЬ НА ДАННЫЙ МЕТОД В СВОЕМ ПРИЛОЖЕНИИ, ЭТО ПРИМЕР ТОГО, КАКИЕ МЕТОДЫ НЕ СЛЕДУЕТ ИСПОЛЬЗОВАТЬ!!!
mysql> SELECT i, ROUND(SUM(d1), 2)*1 AS a, ROUND(SUM(d2), 2)*1 AS b -> FROM t1 GROUP BY i HAVING a <> b; +------+--------+------+ | i | a | b | +------+--------+------+ | 6 | -51.40 | 0.00 | +------+--------+------+
Приведенный выше пример вроде бы работает. Но это случайность, поскольку именно на той конкретной машине, где выполнялась проверка, процессор выполняет арифметические операции с плавающей точкой таким образом, что числа округляются до одинаковых значений. Однако полагаться на то, что так будут работать все без исключения процессоры, нельзя.
Правильный способ сравнения чисел с плавающей запятой заключается в том, чтобы сначала определиться с допустимым отклонением одного числа от другого, а затем при сравнении учитывать этот допуск. Например, если мы договоримся, что числа должны считаться одинаковыми, если они равны с точностью до одной десятитысячной (0,0001), то сравнение должно проводиться следующим образом:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 -> GROUP BY i HAVING ABS(a - b) > 0.0001; +------+--------+------+ | i | a | b | +------+--------+------+ | 6 | -51.40 | 0.00 | +------+--------+------+ 1 row in set (0.00 sec)
И наоборот, если мы хотим оставить строки, в которых числа одинаковы, то проверка должна быть следующей:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 -> GROUP BY i HAVING ABS(a - b) < 0.0001; +------+-------+-------+ | i | a | b | +------+-------+-------+ | 1 | 21.40 | 21.40 | | 2 | 76.80 | 76.80 | | 3 | 7.40 | 7.40 | | 4 | 15.40 | 15.40 | | 5 | 7.20 | 7.20 | +------+-------+-------+
A.6.1 Проблемы с ALTER TABLE
ALTER TABLE изменяет таблицу в соответствии с текущей кодировкой. Если при выполнении ALTER TABLE выдается ошибка дублирующегося ключа, то причина либо в том, что новая кодировка отображает ключи в одинаковые значения, либо в том, что таблица повреждена. В последнем случае на таблице необходимо выполнить REPAIR TABLE.
Если работа ALTER TABLE прекращается с ошибкой, подобной приведенной ниже:
Error on rename of './database/name.frm' to './database/B-a.frm' (Errcode: 17)
то проблема может быть связана с тем, что MySQL аварийно завершился на предыдущей команде ALTER TABLE и осталась старая таблица с именем `A-что_нибудь' или `B-что_нибудь'. В этом случае перейдите в каталог данных MySQL и удалите все файлы, имена которых начинаются с A- или B- (их можно и не удалять, а куда-либо переместить).
ALTER TABLE работает следующим образом:
Создается новая таблица с именем `A-xxx' с заданными в запросе изменениями.
Все строки старой таблицы копируются в `A-xxx'.
Старая таблица переименовывается в `B-xxx'.
`A-xxx' переименовывается в имя старой таблицы.
Удаляется `B-xxx'.
Если что-то приключится при операции переименования, MySQL попытается отменить изменения. Если случится что-то серьезное (чего произойти, конечно, не должно), MySQL может оставить старую таблицу именованной как `B-xxx' - в этом случае, однако, для восстановления данных достаточно будет просто переименовать ее на системном уровне.
A.6.2 Как изменить порядок столбцов в таблице
Основная идея SQL заключается в том, чтобы разделить приложения и формат хранения данных. Всегда следует указывать порядок извлечения данных, например:
SELECT col_name1, col_name2, col_name3 FROM tbl_name;
возвратит столбцы в порядке col_name1, col_name2, col_name3, тогда как:
SELECT col_name1, col_name3, col_name2 FROM tbl_name;
возвратит столбцы в порядке col_name1, col_name3, col_name2.
В приложения никогда нельзя использовать SELECT * и извлекать столбцы, полагаясь на их позиции, поскольку порядок, в котором возвращаются столбцы, не может быть гарантирован. Простое изменение в базе данных может катастрофически сказаться на поведении приложения.
Если порядок столбцов все-таки требуется изменить, то сделать это можно следующим образом:
Создайте новую таблицу со столбцами в правильном порядке.
Выполните INSERT INTO new_table SELECT поля-в-желаемом-порядке FROM old_table.
Удалите или переименуйте old_table.
ALTER TABLE new_table RENAME old_table.
A.6.3 Проблемы, относящиеся к временным (TEMPORARY) таблицам
Ниже перечислены ограничения, относящиеся к временным таблицам.
Временной может быть только таблица типа HEAP, ISAM, MyISAM, MERGE или InnoDB.
Временная таблица не может использоваться в одном запросе более одного раза. Например, следующий запрос работать не будет.
mysql> SELECT * FROM temporary_table, temporary_table AS t2;
Нельзя использовать RENAME на таблице с атрибутом TEMPORARY. Отметим, однако, что ALTER TABLE оригинальное_имя RENAME новое_имя работает!
