The OpenNET Project / Index page

[ новости /+++ | форум | теги | ]

Каталог документации / Раздел "Базы данных, SQL" / Оглавление документа

11 Проблемы и общие ошибки

Эта глава описывает некоторые общие проблемы и сообщения об ошибках, с которыми пользователи столкнулись. Вы узнаете, как точно определить происходящее, и что делать, чтобы решить возникшую проблему. Вы также найдете здесь соответствующие решения для некоторых общих проблем.

11.1 Как определять, что вызывает проблемы

Когда Вы сталкиваетесь с проблемами, первое, что Вы должны сделать, это однозначно выяснить, которая программа или часть оборудования вызывает данные проблемы.

Если после того, как Вы исследовали все другие возможности и заключили, что проблема заключается в клиенте или сервере MySQL, самое время сделать отчет об ошибке для списка рассылки или группы поддержки. В отчете ошибки попробуйте дать очень детальное описание того, как система ведет себя, и что с ней случается. Вы должны также установить, почему Вы думаете, что это вызвано именно MySQL. Учтите все ситуации в этой главе. Установите любые проблемы точно, как они появляются, когда Вы исследуете вашу систему. Используйте метод "вырезать и вставить" для любого вывода, сообщений об ошибках из программ и журналов.

Если сбоит программа, всегда полезно знать следующее:

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

11.2 Общие ошибки при использовании MySQL

Этот раздел перечисляет некоторые ошибки, с которыми пользователи часто сталкиваются. Здесь Вы найдете описания ошибок, и как решить проблему.

11.2.1 Ошибка Access denied

Обратитесь к разделам "10.5 Как работает система привилегий" и особенно "10.10 Причины ошибки Access denied".

11.2.2 Ошибка MySQL server has gone away

Наиболее общая причина для ошибки MySQL server has gone away состоит в том, что у сервера кончилось время ожидания, и он закрыл подключение. По умолчанию сервер закрывает подключение после 8 часов отсутствия активности. Вы можете изменять срок, устанавливая переменную wait_timeout, когда Вы запускаете mysqld

Другая общая причина получения такой ошибки состоит в том, что Вы закрыли соединение, а теперь пробуете выполнить запрос на закрытом подключении.

Вы можете проверять, что MySQL работает, выполняя mysqladmin version и исследуя uptime.

Если Вы имеете скрипт, Вы только должны снова выдать запрос клиенту, чтобы сделать автоматическое переподключение.

Вы обычно можете получать следующие коды ошибки в этом случае:

CR_SERVER_GONE_ERRORКлиент не может послать запрос на сервер.
CR_SERVER_LOSTКлиент не получал ошибку при записи на сервер, но и не получил полный ответ (или любой ответ) на запрос.

Вы можете также получать эти ошибки, если Вы посылаете неправильной или слишком большой запрос серверу. Если mysqld получает пакет, который является слишком большим или вне правил, он считает, что что-то пошло неправильно с клиентом и закрывает подключение. Если Вы нуждаетесь в больших запросах (например, если Вы работаете с большими столбцами BLOB), Вы можете увеличить ограничение запроса, запуская mysqld с опцией -O max_allowed_packet=# (по умолчанию 1M). Память дополнительного пространства распределена по требованию, так что mysqld использует большее количество памяти только, когда Вы выдаете большой запрос, или когда mysqld должен возвратить большую строку результатов!

11.2.3 Ошибка Can't connect to [local] MySQL server

Клиент MySQL под ОС Unix может соединяться с сервером mysqld двумя различными способами: Unix-сокеты, которые подключают через файл в файловой системе (значение по умолчанию /tmp/mysqld.sock) или по протоколу TCP/IP, который соединяется через номер порта. Unix-сокеты быстрее, чем TCP/IP, но могут использоваться только при соединении с сервером на том же самом компьютере. Они применяются, если Вы не определяете hostname, или если Вы определяете специальное имя localhost.

Под Windows Вы можете соединяться только через TCP/IP, если сервер mysqld запущен под Win95/Win98. Если же использована NT, Вы можете также соединяться с именованными каналами. Имя такого именованного канала MySQL. Если Вы не задаете hostname при соединении с сервером, клиент сначала попробует соединяться с именованным каналом, и если это не работает, то соединится с TCP/IP портом. Вы можете заставить использовать именованный канал под Windows, указав точку (.) как hostname.

Сообщение error (2002) Can't connect to ... обычно означает, что не имеется сервера MySQL на системе, что Вы используете неправильный файл сокета или не тот TCP/IP порт при попытке соединиться с mysqld.

Начните с проверки (ps или через администратор задач под Windows), что вообще имеется процесс, управляющий mysqld на Вашем сервере! Если не имеется никакого процесса mysqld, Вы должны обратиться к администратору и попросить его запустить сервер.

Если процесс 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 --socket=/tmp/mysql.sock version

Обратите внимание на использование апострофов вместо кавычек с командой hostname: они заставляют вывод hostname (то есть, текущий hostname) вставиться в mysqladmin.

Имеются некоторые причины, по которым может происходить ошибка Can't connect to local MySQL server:

Если Вы получаете сообщение об ошибке Can't connect to MySQL server on some_hostname, Вы можете попробовать следующие вещи, чтобы выяснить, какова проблема:

11.2.4 Ошибка Host '...' is blocked

Если Вы получаете сообщение об ошибке:

Host 'hostname' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

Это означает, что mysqld получил много (max_connect_errors) запросов подключения с компьютера hostname, которые были прерваны в середине. После max_connect_errors потерпевших неудачу запросов mysqld считает, что что-то неправильно (подобно нападению хакеров) и блокирует дальнейшие соединения с этой машины, пока кто-то не выполняет команду mysqladmin flush-hosts.

По умолчанию mysqld блокирует компьютер после 10 ошибок подключения. Вы можете легко корректировать это, запуская сервер так:

shell> safe_mysqld -O max_connect_errors=10000 &

Обратите внимание, что, если Вы получаете это сообщение об ошибках для данного компьютера, Вы должны сначала проверить, что не имеется чего-нибудь неправильного с TCP/IP подключениями. Если Ваши TCP/IP подключения не работают, увеличение переменной max_connect_errors не поможет!

11.2.5 Ошибка Too many connections

Если Вы получаете ошибку Too many connections, когда Вы пробуете соединиться с MySQL, это означает, что уже имеется max_connections клиентов, работающих с сервером.

Если Вы нуждаетесь в большем количестве подключений, чем значение по умолчанию (100), то перезапустите mysqld с большим значением для переменной max_connections.

Обратите внимание, что mysqld фактически позволяет (max_connections+1) подключений. Последнее подключение зарезервировано для пользователя с привилегией process. Не давая эту привилегию нормальным пользователям (они не должны нуждаться в ней), администратор с этой привилегией может войти и использовать SHOW PROCESSLIST, чтобы выяснить то, что могло бы быть неправильно. Подробности приведены в разделе " 4.10 Синтаксис команды SHOW".

Максимальное количество подключений зависит от того, как хороша библиотека потоков на данной платформе. Linux или Solaris могут поддерживать 500-1000 одновременных подключений в зависимости от того, сколько RAM Вы имеете, и что Ваша клиентура делает.

11.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'.
Подробности в разделе "4.10.2 SHOW TABLE STATUS".

Вы можете проверять, какие расширения поддерживает Ваша версия mysqld следующей командой:

show variables like 'have_%'. Подробности в разделе "4.10.4 SHOW VARIABLES ".

11.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 --quick, который использует mysql_use_result(), чтобы получить набор результатов. Это помещает меньшее количество нагрузки на клиента (но больше на сервер).

11.2.8 Ошибка Packet too large

Когда клиент MySQL или сервер mysqld получает пакет больше, чем max_allowed_packet байт, он выдает ошибку Packet too large и закрывает подключение.

Если Вы используете клиента mysql, Вы можете определять больший буфер, запуская клиента командой mysql --set-variable=max_allowed_packet=8M.

Если Вы используете другую клиентуру, которая не позволяет Вам определять максимальный размер пакета (типа DBI), Вы должны установить размер пакета, когда Вы запускаете сервер. Вы используете опцию командной строки для mysqld, чтобы установить max_allowed_packet к большему размеру. Например, если Вы ожидаете сохранять полную длину BLOB в таблицу, Вы будете должны запустить сервер с опцией --set-variable=max_allowed_packet=16M.

Вы можете также получать странные проблемы с большими пакетами, если Вы используете большие blob'ы, но не дали mysqld доступ к достаточной памяти, чтобы обработать запрос. Если Вы подозреваете, что дело обстоит именно так, попробуйте добавить ulimit -d 256000 к скрипту safe_mysqld и перезапустить mysqld.

11.2.9 Ошибки связи и прерванные подключения

Начиная с MySQL 3.23.40 Вы получаете только ошибку Aborted connection при запуске mysqld с опцией --warnings.

Если Вы находите ошибки, подобные следующей, в Вашем файле регистрации:

010301 14:38:23  Aborted connection 854 to db: 'users' user: 'josh'

Это означает, что что-то из следующего случилось:

Когда вышеупомянутое случается, переменная сервера Aborted_clients будет увеличена.

Переменная сервера Aborted_connects растет:

Обратите внимание, что вышеупомянутое может указывать, что кто-то пробует врываться в Вашу базу данных!

Другие причины для проблем с клиентами и прерванными подключениями.

11.2.10 Ошибка The table is full

Эта ошибка происходит в старых версиях MySQL, когда расположенная в памяти временная таблица становится больше, чем tmp_table_size байт. Чтобы избежать этой проблемы, Вы можете использовать опцию -O tmp_table_size=#, чтобы увеличить размер временной таблицы или использовать опцию SQL_BIG_TABLES прежде, чем Вы выдаете проблематичный запрос.

Вы можете также запустить mysqld с опцией --big-tables. Это действует точно также, как использование для всех запросов SQL_BIG_TABLES.

В MySQL Version 3.23 временная таблица в памяти будет автоматически преобразована в дисковую MyISAM после того, как размер таблицы станет больше, чем tmp_table_size.

11.2.11 Ошибка Can't create/write to file

Если для некоторых запросов Вы получаете ошибку типа:

Can't create/write to file '\\sqla3fe_0.ism'.

Это означает, что MySQL не может создать временный файл для набора результатов в заданном временном каталоге. Для исправления запустите mysqld с опцией --tmpdir=path или добавьте к файлу опцией сервера следующее:

[mysqld]
tmpdir=C:/temp

Считается, что каталог c:\temp существует.

Проверьте также код ошибки, который Вы получаете через perror. Причиной может быть также полный диск;

shell> perror 28
Error code 28: No space left on device

11.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() между ними.

11.2.13 Ошибка Ignoring user

Если Вы получаете следующую ошибку:

Found wrong password for user: 'some_user@some_host'; Ignoring user

Это означает, что, когда mysqld запустился или перезагрузил таблицы привилегий, он нашел запись в таблице user с недопустимым паролем. В результате запись просто игнорируется системой.

Возможные причины для этой проблемы:

11.2.14 Ошибка Table 'xxx' doesn't exist

Если Вы получаете ошибку Table 'xxx' doesn't exist или Can't find file: 'xxx' (errno: 2), это означает, что таблица с именем xxx не существует в текущей (актуальной) базе данных.

Обратите внимание, что MySQL использует каталоги и файлы, чтобы сохранить базы данных и таблицы, так что имена баз данных и таблиц чувствительны к регистру! В Windows это не так, но все ссылки к данной таблице внутри запроса должны использовать тот же самый регистр.

Вы можете проверить, какие таблицы Вы имеете в текущей базе данных, через SHOW TABLES. Подробности в разделе " 4.10 Синтаксис SHOW".

11.2.15 Ошибка Can't initialize character set xxx

Если Вы получаете ошибку подобно:

MySQL Connection Failed: Can't initialize character set xxx

Это означает одно из следующего:

11.2.16 Файл не найден

Если Вы получаете ERROR '...' not found (errno: 23), Can't open file: ... (errno: 24) или любую другую ошибку с errno 23 или errno 24 из MySQL, это означает, что Вы не распределили достаточно описателей файла для 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 хранил открытым меньшее количество файлов одновременно, Вы можете сделать кэш таблицы меньшим, используя опцию -O table_cache=32 при вызове safe_mysqld (значение по умолчанию 64). Уменьшение значения max_connections также уменьшит число открытых файлов (значение по умолчанию: 90).

Чтобы изменять число описателей файла, доступных mysqld, Вы можете использовать опцию --open-files-limit=# при вызове safe_mysqld или -O open-files-limit=# при запуске mysqld. Самый простой способ сделать это состоит в том, чтобы добавить опцию к Вашему файлу опции. Если Вы имеете старую версию mysqld, которая не поддерживает это, Вы можете отредактировать скрипт safe_mysqld. Имеется прокомментированная строка ulimit -n 256 в скрипте. Вы можете удалять символ #, чтобы раскомментировать строку и изменить число 256, чтобы воздействовать на число описателей файла.

ulimitopen-files-limit) может увеличивать число описателей файла, но только до ограничения, наложенного операционной системой. Имеется также жесткое ограничение, которое может быть перекрыто только, если Вы запускаете safe_mysqld или mysqld как root (только не забудьте, что Вы должны также использовать опцию --user=.. в этом случае). Если Вы должны увеличить ограничение OS на число описателей файла, доступных каждому процессу, консультируйтесь с документацией для Вашей операционной системы.

Обратите внимание, что, если Вы выполняете оболочку tcsh, ulimit не будет работать! tcsh также сообщит неправильные значения, когда Вы спросите о текущих ограничениях. В этом случае Вы должны запустить safe_mysqld из sh.

11.3 Проблемы при установке

11.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, проверьте man-страницу для рассматриваемой функции, чтобы выяснить, какую библиотеку Вы должны добавить.

Если Вы получаете ошибки undefined reference для функций, которые не существуют в Вашей системе, подобно следующему:

mf_format.o(.text+0x201): undefined reference to `__lxstat'

Это обычно означает, что библиотека компилируется на системе, которая не на 100%, совместима с Вашей. В этом случае Вы должны загрузить последний исходник MySQL и откомпилировать библиотеку непосредственно.

Если Вы пробуете выполнять программу и получаете ошибки для невызванных символов, которые начинаются с mysql_, или о том, что библиотека mysqlclient не может быть найдена, это означает, что Ваша система не может найти общую библиотеку libmysqlclient.so.

Другой способ решать эту проблему состоит в том, чтобы скомпоновать Вашу программу статически с опцией -static или удалить динамические библиотеки MySQL перед компоновкой Вашего кода. Во втором случае Вы должны убедиться, что никакие другие программы не используют динамические библиотеки!

11.3.2 Как запустить MySQL от имени нормального пользователя

MySQL-сервер mysqld может быть запущен и выполняться любым пользователем. Чтобы mysqld работал как Unix-пользователь user_name, Вы должны сделать следующие шаги:

  1. Завершите сервер, если он запущен (mysqladmin shutdown).
  2. Измените каталоги баз данных и файлы так, чтобы user_name имел права на чтение и запись:
    shell> chown -R user_name /path/to/mysql/datadir
    
    Если каталоги или файлы внутри каталога данных MySQL представляют собой символические ссылки, Вы будете также должны следовать за теми связями и изменять каталоги и файлы, на которые они указывают. Увы, chown -R не может следовать за ссылками для Вас.
  3. Запустите сервер как пользователь user_name или, если Вы используете MySQL Version 3.22 или позже, как Unix-пользователь root с опцией --user=user_name. mysqld переключится на Unix-пользователя user_name перед принятием любых подключений.
  4. Чтобы запустить сервер как данное имя пользователя автоматически при запуске системы, добавьте строку user, которая определяет имя пользователя, к группе [mysqld] файла /etc/my.cnf (или my.cnf) в каталоге данных сервера. Например:
    [mysqld]
    user=user_name
    

Обратите внимание, что доступ к MySQL как root указанием -u root в командной строке, не имеет никакого отношения к запуску MySQL как root. Права доступа и имена пользователей MySQL полностью независимы от имен в Unix. Единственное подключение с именем пользователя Unix происходит, если Вы не указали опцию -u, когда Вы вызываете программу пользователя. Клиент пробует подключиться, используя Ваше Unix-имя входа в систему как имя пользователя MySQL.

11.3.3 Проблемы с доступом к файлам

Если Вы имеете проблемы с правами доступа к файлам, например, если mysql выдает такое сообщение об ошибке, когда создаете таблицу:

ERROR: Can't find file: 'path/with/filename.frm' (Errcode: 13)

То системная переменная UMASK может быть установлена неправильно, когда запускается mysqld. Значение по умолчанию для 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 в аосьмеричном формате.
shell> export UMASK_DIR
shell> /path/to/safe_mysqld &

В MySQL Version 3.23.25 и выше MySQL считает, что значения UMASK и UMASK_DIR заданы в восьмеричном формате, если они начинаются с ноля.

11.4 Проблемы, связанные с администрированием

11.4.1 Что делать, если MySQL падает

Все версии MySQL проверены на многих платформах прежде, чем они выпущены. Это не означает, что не имеется любых ошибок в MySQL, но это означает, что их там очень мало.

Сначала Вы должны пробовать выяснять, состоит ли проблема в том, что mysqld падает, или ее корень надо искать на клиенте. Вы можете проверять время работы сервера mysqld командой mysqladmin version. Если mysqld свалился, Вы можете находить причину этого в файле mysql-data-directory/hostname.err.

Много сбоев MySQL вызваны разрушенным индексом или файлами данных. MySQL модифицирует данные относительно диска через системный вызов write() после каждой инструкции SQL, но прежде, чем пользователю сообщают относительно результата. Это не так, если Вы работаете с опцией delayed_key_writes, когда только данные будут записаны. Это означает, что данные в безопасности, даже если сбоит mysqld, если ОС гарантируют, что данные будут сброшены из кэша на диск. Вы можете заставить MySQL сбрасывать кэш принудительно после каждой команды SQL, запуская mysqld с опцией --flush.

Вышеупомянутое означает, что обычно Вы не должны получить разрушенные таблицы и файлы, если:

Поскольку очень трудно узнать, почему что-то терпит крах, сначала попробуйте проверить следующее:

11.4.2 Как сбрасывать забытый пароль

Если Вы забыли пароль пользователя root для MySQL, Вы можете восстанавливать его следующей процедурой:

  1. Завершите сервер mysqld отправкой ему команды kill (но не kill -9!). Идентификатор процесса будет сохранен в .pid-файле, который обычно хранится в каталоге для баз данных MySQL:
    kill `cat /mysql-data-directory/hostname.pid`
    
    Вы должны быть Unix-пользователем root или тем пользователем, от имени которого выполняется сервер.
  2. Перезапустите mysqld с опцией --skip-grant-tables.
  3. Соединитесь с mysqld командой mysql -h hostname mysql и смените пароль командой GRANT. Подробности в разделе "10.1 Синтаксис GRANT и REVOKE". Вы можете также делать это с помощью команды mysqladmin -h hostname -u user password new_password.
  4. Загрузите таблицы предоставления привилегий: mysqladmin -h hostname flush-privileges или командой SQL FLUSH PRIVILEGES.

Обратите внимание, что после того, как Вы запустили mysqld с опцией --skip-grant-tables, любое использование команды GRANT даст Вам ошибку Unknown command, пока Вы не выполните FLUSH PRIVILEGES.

11.4.3 Как MySQL обрабатывает полный диск

Когда диск полностью заполнится, MySQL делает следующее:

Чтобы облегчить проблему, Вы можете предпринять следующие действия:

Исключительные ситуации: Вы используете REPAIR или OPTIMIZE, индексы созданы в пакете после LOAD DATA INFILE, или выполнена инструкция ALTER TABLE.

Все вышеприведенные команды могут использовать большие временные файлы, которые остались после них на диске, что вызовет большие проблемы для остальной части системы. Если MySQL переполняет диск при выполнении любой из вышеупомянутых операций, он удалит большие временные файлы и отметит таблицу как поврежденную (исключение: ALTER TABLE, в этом случае старая таблица будет оставлена неизменной).

11.4.4 Где MySQL хранит временные файлы

MySQL использует значение системной переменной TMPDIR как имя пути каталога, чтобы сохранить временные файлы. Если Вы не имеете TMPDIR, MySQL использует системное значение по умолчанию, которое является обычно /tmp или /usr/tmp. Если файловая система, содержащая Ваш временный каталог слишком маленькая, Вы должны отредактировать скрипт safe_mysqld, чтобы установить TMPDIR так, чтобы указать на каталог в файловой системе, где Вы имеете достаточно места. Вы можете также устанавливать временный каталог, используя опцию --tmpdir при вызове mysqld.

MySQLсоздает все временные файлы как скрытые. Это гарантирует, что временные файлы будут удалены, если mysqld завершен. Недостаток использования скрытых файлов в том, что Вы не будете видеть большой временный файл, заполняющий файловую систему, в которой размещен временный каталог.

При сортировке (ORDER BY или GROUP BY) MySQL обычно использует один или два временных файла. Максимальное дисковое пространство, необходимое для сортировки:

(Длина сортируемых данных+sizeof(database pointer))*
число совпадающих строк*2

Здесь sizeof(database pointer) обычно 4, но может расти в будущем для действительно больших таблиц.

Для некоторого запроса SELECT MySQL также создает временные SQL-таблицы. Они не скрыты и имеют имена формы SQL_*.

ALTER TABLE создает временную таблицу в том же самом каталоге, где находится и первоначальная таблица.

11.4.5 Как защитить или сменить файл сокета MySQL /tmp/mysql.sock

Если Вы имеете проблемы с тем фактом, что любой может удалять сокет MySQL /tmp/mysql.sock, Вы можете, на большинстве версий Unix, защитить Вашу файловую систему /tmp, устанавливая липкий бит на ней. Зайдите в систему как root и скомандуйте:

shell> chmod +t /tmp

Это защитит Вашу файловую систему /tmp так, чтобы файлы могли быть удалены только их владельцами или суперпользователем (root).

Вы можете проверять, установлен ли липкий бит, выполняя ls -ld /tmp. Если последний бит прав доступа равен t, липкий бит установлен.

Вы можете изменять место, где MySQL помещают файл сокета, так:

Вы можете проверять, что сокет работает, с помощью команды:

shell> mysqladmin --socket=/path/to/socket version

11.4.6 Проблемы с временными зонами

Если Вы имеете проблему с SELECT NOW(), возвращающим значения в GMT, а не по Вашему местному времени, Вы должны установить системную переменную TZ к Вашему текущему часовому поясу. Это должно быть выполнено для среды, в которой выполняется сервер, например, в скрипте safe_mysqld или в mysql.server.

11.5 Проблемы с запросами

11.5.1 Чувствительность к регистру в поиске

По умолчанию поиск в MySQL нечувствителен к регистру (хотя имеются некоторые наборы символов, которые всегда чувствительны, например, czech). Это означает, что, если Вы ищете col_name LIKE 'a%', Вы получите все значения столбца, которые начинаются с A или a. Если Вы хотите сделать этот поиск с учетом регистра, используйте нечто подобное INSTR(col_name, "A")=1, чтобы проверить префикс. Или используйте STRCMP(col_name,"A")=0, если значение столбца было точно "A".

Простые операции сравнения (>=, >, =, < ,<=, сортировка и группировка) основаны на значении сорта каждого символа. Символы с тем же самым значением сорта обрабатываются как тот же самый символ.

В старых версиях MySQL LIKE выполнялся по значениям, приведенным к верхнему регистру. В более новых версиях MySQL LIKE работает точно так же, как другие операторы сравнения.

Если Вы хотите, чтобы столбец всегда обработался в режиме чувствительности к регистру, объявите его как BINARY. Подробности в разделе "7.3 Синтаксис CREATE TABLE ".

Если Вы используете китайские данные в так называемом кодировании big5, Вы должны сделать все символьные столбцы BINARY. Это работает потому, что порядок сортировки символов кодирования big5 основан на порядке ASCII кодов.

11.5.2 Проблемы использования столбцов DATE

Формат значения DATE всегда YYYY-MM-DD. Согласно ANSI SQL, никакой другой формат не позволяется. Вы должны использовать этот формат в выражениях UPDATE и в предложении WHERE инструкций SELECT. Например:

mysql> SELECT * FROM tbl_name WHERE date >= '1997-05-05';

Как дополнительное удобство 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 Version 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, неправильная дата будет сохранена. Если дата не может быть преобразована в любое приемлемое значение, 0 будет сохранен в поле DATE. Это главным образом проблема быстродействия.

11.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:

mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);

Если Вы хотите искать значения столбца, которые являются NULL, Вы не можете использовать тест =NULL. Следующая инструкция не возвращает никаких строк потому, что expr=NULL всегда равно FALSE для любого выражения:

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 = "";

В MySQL как и во многих других SQL-серверах, Вы не можете индексировать столбцы, которые могут иметь значения NULL. Вы должны объявить столбцы для индекса как NOT NULL. Наоборот, Вы не можете вставлять NULL в индексированный столбец.

При чтении данных через LOAD DATA INFILE, пустые столбцы модифицируются с ''. Если Вы хотите иметь в столбце значение NULL, Вы должны использовать \N в текстовом файле. Литеральное слово 'NULL' может также с успехом использоваться при некоторых обстоятельствах. Подробности есть в разделе "8.9 Синтаксис LOAD DATA INFILE".

При использовании ORDER BY значения NULL будут представлены первыми. Если Вы сортируете в порядке по убыванию, используя DESC, значения NULL, понятно, окажутся в хвосте. При использовании GROUP BY, все значения NULL будут расценены как равные.

Чтобы помочь СУБД с обработкой NULL, Вы можете использовать операторы IS NULL и IS NOT NULL, а также функцию IFNULL(). Производительность улучшится.

Для некоторых типов столбцов значения NULL обработаны особо. Если Вы вставляете NULL в первый столбец типа TIMESTAMP в таблице, реально будут вставлены текущая дата и время. Если Вы вставляете NULL в столбец с поддержкой AUTO_INCREMENT, вставится следующее число в последовательности.

11.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 используется, чтобы решить, которые строки из набора результатов должны использоваться.

11.5.5 Удаление строк из связанных таблиц

Поскольку MySQL не поддерживает подвыборы или использование больше, чем одной таблицы в инструкции DELETE, Вы должны использовать следующий подход, чтобы удалить строки из двух связанных таблиц:

  1. SELECT строки, основываясь на некотором условии WHERE в основной таблице.
  2. DELETE строки в основной таблице, основываясь на том же самом условии.
  3. DELETE FROM связанная_таблица WHERE связанные_столбцы IN (выбранные_строки).

Если общее количество символов в запросе со связанными столбцами (related_column) больше, чем 1048576 (значение по умолчанию для max_allowed_packet), Вы должны расчленить его на меньшие части и выполнить много инструкций DELETE. Вы, вероятно, достигнете пика производительности при удалении 100-1000 related_column на запрос, если related_column является индексом. В противном случае быстродействие независимо от числа параметров в предложении IN.

11.5.6 Решение проблем с несоответствием строк

Если Вы имеете сложный запрос, который имеет много таблиц, а он не возвращает никаких строк, Вы должны использовать следующую процедуру, чтобы выяснить, что неправильно с Вашим запросом:

  1. Проверьте запрос с помощью EXPLAIN и выправьте его, если Вы можете найти что-то такое, что является очевидно неправильным. Подробности в разделе "14.2.1 Синтаксис EXPLAIN (получение информации о SELECT)".
  2. Выберите только те поля, которые используются в предложении WHERE.
  3. Удаляйте по одной таблице из запроса, пока он не будет возвращать строки. Если таблицы большие, хорошо использовать с запросом LIMIT 10.
  4. Сделайте SELECT для столбца, который должен был соответствовать в таблице, которая была последней удалена из запроса.
  5. Если Вы сравниваете столбцы FLOAT или DOUBLE с числовыми столбцами, которые имеют десятичные числа, Вы не можете использовать =. Эта проблема общая в большинстве компьютерных языков потому, что значения с плавающей запятой не точные значения:
    mysql> SELECT * FROM table_name WHERE float_column=3.5;
        ->
    mysql> SELECT * FROM table_name WHERE float_column between 3.45 and 3.55;
    
    В большинстве случаев замена FLOAT на DOUBLE исправит это дело.
  6. Если Вы все еще не можете вычислить, что идет неправильно, создайте минимальный тест, который может быть выполнен с mysql test < query.sql, который показывает Ваши проблемы. Вы можете создавать файл теста с помощью mysqldump --quick database tables > query.sql. Откройте файл в редакторе, удалите некоторые строки и добавьте Вашу инструкцию выбора в конце файла. Проверьте, что Вы все еще имеете Вашу проблему:
    shell> mysqladmin create test2
    shell> mysql test2 < query.sql
    
    Отправьте файл теста, используя mysqlbug на [email protected].

11.6 Проблемы с определениями таблиц

11.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 работает следующим образом:

Если что-то идет неправильно с операцией переименования, MySQL пробует отменить изменения. Если что-то идет совсем уж неправильно (этого не должно случиться, конечно), MySQL может оставить старую таблицу как B-xxx, поскольку простое переименование на уровне системы должно вернуть Ваши данные обратно в целости и сохранности.

11.6.2 Как менять порядок столбцов в таблице

Вы должны всегда определять порядок, в котором Вы желаете получить данные:

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 * и получать столбцы, основываясь на их позиции потому, что порядок, в котором столбцы возвращены, НЕЛЬЗЯ ГАРАНТИРОВАТЬ через какое-то время. Простое изменение Вашей базы данных может заставить Вашу прикладную программу развалиться полностью.

Если по какой-то причине Вы все же хотите изменять порядок столбцов, Вы можете сделать это следующим образом:

  1. Создайте новую таблицу со столбцами в правильном порядке.
  2. Выполните INSERT INTO new_table SELECT fields-in-new_table-order FROM old_table.
  3. Удалите или переименуйте old_table.
  4. Выполните ALTER TABLE new_table RENAME old_table.

11.6.3 Проблемы с TEMPORARY TABLE

Следующее представляет собой список всех ограничений, действительных для временных таблиц (TEMPORARY TABLES).




Партнёры:
PostgresPro
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2024 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру