Сайт не может подключиться к базе данных

Если на сайте возникает ошибка подключения к базе данных (Unable to connect to the Database: Could not connect to MySQL), необходимо проверить правильно ли указаны данные для подключения к базе данных в конфигурационном файле сайта. Данные можно посмотреть в разделе “Хостинг – Логины и пароли“:

Импорт базы данных

2. Импорт базы данных через phpmyadmin.

Для того, чтоб не возникло ошибок во время импорта базы, нужно проверить, что:

  1. Ваша база данных в виде .sql файла. Если она в архиве – разархивируйте его.
  2. Дамп Вашей базы данных не содержит запросов типа «CREATE DATABASE, /*!40101 SET @OLD». Проверить наличие подобного запроса Вы можете открыв дамп вашей базы «блокнотом» или другим текстовым редактором.
  3. В разделе “Панель управления – Хостинг – Базы данных” нужно проверить создана ли база (первая база при заказе хостинга создаётся автоматически). Если у Вас еще нет созданной базы данных, то на этой же странице нужно нажать на кнопку “Создать базу данных“.
  4. В этом же разделе нажмите на кнопку “войти как login_db” для того чтоб автоматически перейти в phpmyadmin.
  5. Найдите свою базу в списке в левом меню и нажимаете на неё.

     
  6. Вы попадаете в саму базу, убедитесь что база пуста и в ней нет таблиц.

Если таблицы есть, то нужно в нижмен меню программы отметить все таблицы и выбрать действие “Удалить“, подтвердив его, нажав на кнопку “Да“.

Дальше нажимаете “Импорт” и выбираете свой файл дампа базы, после нажимаете “Ок“:

и ждете, пока процесс заливки базы завершится.

 

Экспорт базы данных

2. Через phpmyadmin.

Для экспортирования базы данных выполните следующие действия:

1. Зайдите в PHPMyAdmin, введя реквизиты для доступа к БД.

2. Выберите (нажмите на нее) базу данных, которую Вы хотите экспортировать:

3. В верхнем меню выберите пункт “Экспорт”:

4. На открывшейся странице выберите способ экспорта “Быстрый” и нажмите на кнопку “ОК”.

 

Неправильная кодировка при выводе данных из базы

В случае, если кодировка сайта и базы данных не совпадает (часть текста на сайте выводится нормально, а часть текста из базы – в виде непонятных знаков). Необходимо в скрипте, который подключается к базе данных добавить команды, которые укажут MySQL серверу кодировку, в которой выводить текст. В зависимости от того, какую библиотеку PHP вы используете команды будут выглядеть так:

Для библиотеки mysql

mysql_query(“SET NAMES ‘utf8’ COLLATE ‘utf8_general_ci'”);
mysql_query(“SET CHARACTER SET ‘utf8′”);

Для библиотеки mysqli

mysqli_query($link, “SET NAMES ‘utf8’ COLLATE ‘utf8_general_ci'”);
mysqli_query($link, “SET CHARACTER SET ‘utf8′”);

где:

  • вместо utf8 необходимо указать нужную кодировку (например cp1251)
  • вместо utf8_general_ci нужное сопоставление кодировок (например cp1251_general_ci). Полный перечень кодировок и сопоставлений MySQL доступен в документации.
  • для библиотеки mysqli первым параметром указывается указатель на подключение к БД – в вашем скрипте он может быть отличным от $link, узнать его можно посмотрев в исходном коде название переменной, которой присваивается результат выполнения функции mysqli_connect().

Инструкция по оптимизации MySQL запросов

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

К примеру есть запрос:

SELECT 
   p.product_id, 
  (SELECT AVG(rating) AS total FROM mc_review r1 WHERE r1.product_id = p.product_id AND r1.STATUS =‘1’ GROUP BY r1.product_id) AS rating 
FROM mc_product p 
LEFT JOIN mc_product_description pd ON (p.product_id = pd.product_id) 
LEFT JOIN mc_product_to_store p2s ON (p.product_id = p2s.product_id) 
WHERE 
   pd.language_id = ‘2’ AND 
   p.STATUS = ‘1’ AND 
   p.date_available <= NOW() AND 
   p2s.store_id = ‘0’ AND 
   p.product_id IN (SELECT pt.product_id FROM mc_product_tag pt WHERE pt.language_id = ‘2’ AND LOWER(pt.tag) LIKE ‘%роксолана%’) 
ORDER BY rating ASC 
LIMIT 0,20

Если запрос выполнить с условием EXPLAIN в начале, то получим схему выполнения запроса:

Тип выборки Таблица Тип Возможные ключи Ключ Длина ключа Ссылка Строки Дополнительная информация
PRIMARY p ALL PRIMARY       2907 Using where; Using filesort
PRIMARY pd eq_ref PRIMARY PRIMARY 8 mebelnyc_db.p.product_id,const 1 Using where; Using index
PRIMARY p2s eq_ref PRIMARY PRIMARY 8 mebelnyc_db.p.product_id,const 1 Using where; Using index
DEPENDENT SUBQUERY pt ALL         6803 Using where
DEPENDENT SUBQUERY r1 ref product_id product_id 4 mebelnyc_db.p.product_id 1 Using where
  1. Если убрать из этого запроса условие LIMIT, то он вернет 2907 записей. Именно 2907 раз будет выполнен вложенный в условие SELECT запрос. Если эту часть запроса вынести в отдельный запрос, то это уменьшит нагрузку на базу данных 2907/20=145 раз. Хотя судя по названию запроса можно сделать вывод относительно того, что таким интересным способом автор программы пытается при каждом заходе посетителя на сайт считать статистику товаров, которая может пересчитываться к примеру раз в сутки или еще лучше – при добавлении отзыва к товару и добавляться в отдельную колонку таблицы mc_product. Что позволит избавиться от этого вложенного запроса.
  2. В условии WHERE мы видим вложенный запрос, который выполняется в условии IN. Если бы автор программы в условии IN укзал не вложенный запрос, а просто статические значения, непример IN (121, 1235, 43554), то MySQL использовал бы индекс и отработал быстро. Но с вложенными запросами дело обстоит совсем по другому – MySQL выполняет их без использования индексов, а точней так FIN_IN_SET(p.product_id, ‘121,1235,43554’). В таких случаях нужно писать запрос отдельно, а потом подставлять результат его выполнения в условие IN.

Ошибка в phpmyadmin: `В таблице отсутствует уникальный столбец`

Если при работе с базой данных через phpmyadmin возникает ошибка “Данная таблица не содержит уникального столбца. Изменение сетки, выставление галочки, редактирование, копирование и удаление не доступно“, нужно выполнить SQL запрос в базе данных:

ALTER TABLE tablename ADD newid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;



Только вместо tablename необходимо поставить название проблемной таблицы (без скобок и кавычек).

Оптимизация базы данных

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

Пример таблицы, которая состоит из более чем 40 000 записей и к которой отправляются запросы, которые создают немалую нагрузку на сервер, так как их поступает очень много.

CREATE TABLE `links` (
 `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 `source` int(11) UNSIGNED NOT NULL,
 `category` int(11) UNSIGNED NOT NULL,
 `title` text NOT NULL,
 `description` text NOT NULL,
 `text` text NOT NULL,
 `link` text NOT NULL,
 `publication` datetime NOT NULL,
 `scan` datetime NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `unique` (`id`) USING BTREE,
 KEY `normal` (`source`,`category`,`title`(100),`publication`,`description`(100),`text`(100),`scan`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=40000 DEFAULT CHARSET=utf8

Пример запросов, которые поступают:

SELECT COUNT(*)
FROM links
WHERE link =  ‘http://www…….com.ua/31-08-2014/trolleybook-podgotovil-prazdnichnuyu-programmu-k-limba-noastr-/n96349/’
AND UNIX_TIMESTAMP( publication ) =1409472019

Выполнив запрос с приставкой EXPLAIN мы видим, что для получения результатов сканируется вся таблица, около 40 тысяч строк. Для того, что б оптимизировать работу таблицы:

  1. Добавляем индекс по полю publication.
  2. Меняем часть запроса UNIX_TIMESTAMP(publication) = 1409472019 на publication=FROM_UNIXTIME(1409472019). Только при таком запросе MySQL будет использовать индекс. Так как в первом случае СУБД придется пройтись по каждой строке в таблице и применить к ней функцию UNIX_TIMESTAMP после чего сравнить полученный результат с числом 1409472019 , а во втором – один раз меняется константа FROM_UNIXTIME(1409472019) после чего производится поиск по индексу.

Этих двух операций достаточно для того, что б все начало работать быстро и без нагрузки на процессор сервера MySQL.

В качестве дополнения рекомендую:

  1. Изменить тип полей title, description, link с text на varchar нужной длинны.
  2. Убрать индекс UNIQ по полю id, зачем он там, ведь эту функцию уже выполняет индекс PRIMARY по этому же полю.
  3. Индекс с названием normal еще больше вызывает сомнения, так как для его создания серверу приходится немало потрудиться, он просто огромен, а я очень сомневаюсь, что поиск происходит по всем полям, которые в нем указаны. Скорее всего это “мертвый” индекс, который больше мешает, чем помогает. Разработчикам стоит знать, что индекс будет использоваться только в том случае, если в запросе будут содержаться поля, которые в нем находятся слева-направо. Так например этот индекс будет использоваться если в условии WHERE будут указаны поля category, title, source, если же поле source указано в запросе не будет, то и индекс использоваться не будет. 

Ну и напоследок есть вопрос относительно необходимости использования самого запроса COUNT(*), очень похоже, что его автор хочет таким образом получить информацию о наличии записи в таблице, а не суммарное количество записей в таблице с указанным URL за указанную дату. В таком случае правильно использовать запрос без COUNT(*) с условием LIMIT 1:

SELECT id
FROM links
WHERE
link =  ‘http://www…….com.ua/31-08-2014/trolleybook-podgotovil-prazdnichnuyu-programmu-k-limba-noastr-/n96349/’
AND UNIX_TIMESTAMP( publication ) =1409472019
LIMIT 1

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

PS. Не смотря на то, что в SQL запросе идет поиск по двум столбцам publication и link я не добавлял в индекс поле link. Это связано с тем, что для данной таблицы поле publication с датой является доволно таки уникальным и максимум будет содержать несколько строк с разным link. Поэтому накладные расходы на формирование индекса для поля link значительно превышают расходы на сканирование нескольких строк по одному индекс publicate.

Как импортировать БД размером более 300 Мб

1. Импорт через HeidiSQL:

a) Для начала Вам необходимо скачать данное приложение с официального сайта.    

После установки открыть программу HeidiSQL и перейти во вкладку настройки, где нужно указать параметры Вашей БД с раздела [Панель управления — Хостинг — Логины и пароли], как показано на скриншоте :

     c) В открывшимся окне :

[File (Файл) — Load SQL file (Открыть SQL файл)] или ctrl + O, как показано на скриншоте :

d) После чего выбираете, слева, свою базу и нажмите кнопку “Execute SQL (Выполнить SQL)” или кнопку F9, как показано на скриншоте :

2. Импорт через Sympex Dumper :

   a) Для начала нужно скачать архив с файлами с официального сайта разработчика.

    b) Разархивировать архив на локальном компьютере, после чего перейти в Файл менеджер [Панель управления — Хостинг — Файл менеджер], создать новую папку с произвольным названием и добавить в нее содержание скачанного архива.

    с) После этого dumper будет доступен по ссылке domain.com/folder_name, где folder_name — название Вашей папки.

    dЗалить соответствующий файл с дампом на Ваш ftp-аккаунт в папку backups, для этого Вы можете воспользоваться сторонним ftp-клинентом FileZilla

    e) Перейти по ссылке : domain.com/folder_name и заполнить соответствующую форму, как показано на скриншоте :

 

 

Примечание : актуальность вводимых данных можно найти на странице :


[Панель управления — Хостинг — Логины и пароли]


    f) После того, как Вы войдете в систему, перейдите на вкладку “Import (Импорт), установите необходимую кодировку в поле “Charset (кодировка)” и выберите нужный дамп-файл в поле “File (Файл)”, после чего нажмите кнопку “Execute (Выполнить)”.

 

 

3. Импорт через SSH-консоль:

 

   a) Для того, чтобы выгрузить базу данных через ssh-консоль, Вам необходимо иметь верифицированый аккаунт на нашем хостинге, а также установленное стороннее приложение PuTTY или встроенный в операционную систему терминал. Более подробно о подключение по SSH описано в инструкции >> тут

    b) Залейте дамп базы непосредственно на Ваш ftp-аккаунт при помощи стороннего клиента, например FileZilla.


    с) Сам импорт базы необходимо выполнить следующей командой :


mysql -h сервер_БД -u пользователь_БД -p имя БД< путь_к_файлу/файл_дампа_БД.sql

После ввода команды будет запрошен пароль от БД. Данные к подключению можно найти в разделе [Панель управления — Хостинг — Логины и пароли]