вівторок, 3 лютого 2015 р.

[Вирішено] помилка "MySQL error 2006: mysql server has gone away"


При виконанні Django ORM bulk_create() в IPython Notebook часом вилітала така помилка.
Вирішена була збільшенням, параметру max_allowed_packet (конфігураційний файл my.cnf секція [mysqld]) з 8M до 500М:

$ sudo vim /etc/mysql/my.cnf
# відшукати секцію [mysqld]
...
max_allowed_packet=8M   <<-- замінити, наприклад, на 500M

пʼятниця, 22 серпня 2014 р.

[MySQL] Збереження і відновлення серверних процедур і функцій при експорті та імпорті

Так склалось, що в нашій базі даних, яка зберігається в MySQL, з'явилось декілька вкрай необхідних нам серверних функцій та процедур (stored procedures and functions) і виникла потреба зберігати і відновлювати їх з дампу бази даних.

Виявляється, що за замовчуванням (by default), MySQL НЕ зберігає в дампі серверні функції та процедури (а тригери, навпаки, - зберігає!).

Я бачу три основні способи їх зберігання:
  • зберігати серверні процедури та функції разом з базою даних
  • зберігати їх в окремому дампі
  • не зберігати взгалі, але завжди мати під рукою свіжі інсталяційні скрипти для їх завантаження
Для першого варіанту необхідно додати ключ `--routines` в mysqldump:
mysqldump -u<user> -p<password> -h<host> --routines <DB> >all_data.sql

Для другого варіанту необхідно вивантажувати тільки серверні процедури та функції:
mysqldump -u<user> -p<password> \
-h<host --single-transaction --no-data --no-create-info \
--routines --skip-triggers <DB> >routines.sql

ЗАУВАЖЕННЯ!


Якщо дамп бази даних використовується для переносу даних між різними хостами, завантажені на інший сервер процедури і функції працювати не будуть через те, що в них буде параметер DEFINER з іншого хосту:

Наприклад, на робочому сервері в нас DEFINER=`dbhost`@`%`, а на моєму комп'ютері, (куди я переношу дамп) - DEFINER=`root`@`localhost`.

Виправити це можна двома способами:
  1. після вивантаження дампу, підкоректувати дамп і вилучити з нього текст з DEFINER=*. Наприклад, так:
    $ cat routines.sql \
      | sed 's/^CREATE DEFINER=[^ ]* /CREATE /' \
      > routines-wo-definer.sql
  2. при завантаженні дампу (якщо вже в ньому виявився чужий DEFINER) - підкоректувати DEFINER в самій базі даних. Наприклад, так:
    SQL> update mysql.proc
            set definer='root@localhost'
          where db='mydb';

понеділок, 11 серпня 2014 р.

[MySQL] (помилка - вирішено): змінити назву колонки з Foreign Key

Неправильно створив колонку (`region` замість `region_id`)  в таблиці і вирішив її переіменувати.

Але при цьому почала з'являтись не дуже зрозуміла помилка `ERROR 1025 (HY000): Error on rename of ... to ... (errno:150)`:

mysql> alter table my_shop change region region_id integer not null;
ERROR 1025 (HY000): Error on rename of './bi/#sql-2e9_158a49' to
'./bi/my_shop' (errno: 150)

Як виявилось, виною всьому було те, що я пробував змінити колонку, яка була FOREIGN KEY до іншої таблиці. Тобто, перед зміною потрібно було вилучити Constraint FOREIGN KEY, внести зміни, і повернути FOREIGN KEY назад.

Робимо це в 4 кроки:

1. Дивимось, як називається наш FOREIGN KEY, щоб його вилучити:
mysql> SHOW CREATE TABLE my_shop \G
*************************** 1. row ***************************
       Table: my_shop
Create Table: CREATE TABLE `my_shop` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `region` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `x_my_shop_fk` (`region`),
  CONSTRAINT `x_my_shop_fk` FOREIGN KEY (`region`) REFERENCES
`my_region` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
В нашому випадку це - `x_my_shop_fk`

2. Вилучаємо FOREIGN KEY з поля, яке хочемо змінити:
mysql> alter table my_shop drop foreign key x_my_shop_fk;

3. Вносимо зміни:
mysql> alter table my_shop change region region_id integer not null;

4. Повертаємо FOREIGN KEY (змінюючи, принагідно, назву констрейнта на `x_my_shop_region_fk`):
mysql> alter table my_shop add constraint x_my_shop_region_fk
foreign key (region_id) references my_region(id);

четвер, 12 січня 2012 р.

[ORA] Перегляд плану виконання Oracle в sqlplus

Як зформувати і переглянути план виконання в Oracle 9i+ з sqlplus?
  1. запустити запит командою:
  2. SQL> delete from plan_table;
    SQL> explain plan for select ...;
  3. вивести план виконання:
  4. SQL> select * from table(dbms_xplan.display);
або використати set autot[race] (в цьому випадку план виконання буде автоматично відображатись після виконання кожного DML-виразу (DML(data manipulation language) - select, insert, update, delete):
SQL> set autotrace on explain
SQL> select ...;
SQL> set autotrace off

Приклад:
SQL> explain plan for select * from tab;

Explained.
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 457676135

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |  1146 | 98556 |   397   (5)| 00:00:02 |
|   1 |  NESTED LOOPS OUTER   |        |  1146 | 98556 |   397   (5)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL   | OBJ$   |  1146 | 90534 |   357   (5)| 00:00:02 |
|   3 |   TABLE ACCESS CLUSTER| TAB$   |     1 |     7 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN  | I_OBJ# |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("O"."TYPE#"<=5 AND "O"."OWNER#"=USERENV('SCHEMAID') AND
              "O"."TYPE#">=2 AND "O"."LINKNAME" IS NULL)
   4 - access("O"."OBJ#"="T"."OBJ#"(+))

18 rows selected.
(Як трактувати виведені дані - будемо розбиратись пізніше :)

[ORA] Модель оцінки вартості (cost) в Oracle

Для того, щоб навчитись оптимізувати роботу Oracle потрібно зрозуміти, як він це робить. Так, як зараз основна (якщо не єдина стратегія оптимізації в Oracle 10-11... це оптимізація по вартості (cost based optimization, CBO)), то варто було б зрозуміти, а що, власне таке, означає для Oracle поняття "вартість"?

Згідно CPU Costing Model маємо таке визначення:

Вартість = ( #SRds * sreadtim
           + #MRds * mreadtim
           + #CPUCycles / CPUSpeed
           ) / sreadtim
де:
#Srds - кількість одноблочних читань,  sreadtim - час одноблочного читання

#MRds - кількість багатоблочних читань, mreadtim - час багатоблочного читання
#CPUcycles - кількість циклів процесора; CPUSpeed - кількість циклів процесора у секунду,

В перекладі на "людську мову" маємо:
Вартість - це час, використаний на одноблочні читання, плюс час, витрачений на багатоблочні читання, плюс необхідний процесорний час і усе це ділене на час, необхідний для виконання 1-го одноблочного читання. Таким чином, вартість (cost) - це сумарний прогнозований час виконання оператора, виражене в одиницях часу виконання одного одноблочного читання.*

* знайдено тут: Jonathan Lewis. Cost-Based Oracle Fundamentals/ Дж.Льюис. Oracle. Основы стоимостной оптимизации