Проблема уже исправлена и как оказалось была довольно интересная. Я выявил, что в intranet-е нельзя было сохранить лишь два месяца — апрель и ноябрь. Все остальные месяцы успешно работали и сохранялись. Довольно странная ситуация, правда? Ведь все месяцы обрабатываются
одинаково и нет какого-то условия именно для этих двух месяцев. Немного поразмыслив, мы с Пашей предположили, что это проблема существует из-за… перевода времени и неккоретных настроек MySQL базы; Поскольку мы в админке выбираем только год и месяц (без точного времени), я присваиваю 00:00:00 часов первого дня выбраного месяца и года, тоесть самое-самое начало.

(Для програмистов: mktime(0,0,0,$month,1,$year))
В случае перевода времени, идет сдвиг на час, и таким образом мы получаем уже 23:59:00 31-ое марта в случае выбора апреля…

(Для програмистов: получившийся mktime я сохраняю в базу как
FROM_UNIXTIME(mktime))

Это предварительный анализ,теперь факты, относительно этого:
1) Не работает только на тех месяцах, перед которыми изменяется время (если в марте переводят часы — то в апреле, если в октябре переводят часы — то в ноябре)
2) Обнаружена ошибка только при записи в/из базы, обработка функции FROM_UNIXTIME MYSQL
3) На нашем локальном сервере этот же скрипт успешно работает с любыми месяцами — следовательно проблема именно в настройках MySQL базы.

Для более точного исследования необходимо дополнительное время и более глубинно изучить эту проблему с переводом времени, скажу только что я с таким сталкиваюсь впервые, когда компьютер неверно определяет свою же дату (фактически наблюдается де-синхронизация PHP timestamp с MySQL timestamp)

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

Сотрудник сегодня скинул интересные запросы:


Empty set (1.61 sec)


1 row in set (0.11 sec)


1 row in set (0.03 sec)

Таблица t2 была создана до него, t1 создавал он сам.
В общем, он бил себя в грудь и орал, что нашел баг в мускуле. Я не верю ни в такие баги, ни в мистику (меня этому научили уже давно). Если вы видите такой «баг», то для начала найдите все баги у себя.

Не поверил я ему. Полез в эти базы, появился просто спортивный интерес:) Запускаю запросы, действительно отработали так, как он мне их сбросил. Я умышленно изменил таблицы и не показал результаты работы запросов, так вот, меня насторожило, что первый запрос выдал в результате поле login 'IGOR', а второй запрос — поле username 'igor'.

А он в качестве аргумента, что баг найден сделал так:

+-----------------------+
| if('IGOR'='igor',1,0) |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)


+------------------------+
| if('IGOR'!='igor',1,0) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)

Сука, думаю, вот тебе и баг у мускула. Полез дальше в структуру таблиц, а там я вижу.

t1.login — сравнение стоит utf8_bin
t2.username — сравнение стоит utf8_general_ci

utf8_bin — хранит буковки 'G' и 'g' по-разному.
Поменял utf8_bin на utf8_general_ci и пофиксил баг у mysql:)

1. Разбиение таблиц (Partitioning)
Разбиение таблиц — это одна из важных функций, которых не хватало MySQL. Хотя в MySQL и реализован тип таблиц MERGE, однако, его функциональность довольно отличается от partitioning таблиц.

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

Все типы таблиц поддерживают такое разбиение.
В MySQL 5.1 реализованы следующие основные виды разбиения таблиц:

* Разбиение по диапазону. Такое разбиение подходит для таблиц со слабо связанными данными, например, таблица с логами.

CREATE TABLE logs (
value VARCHAR(30) NOT NULL,
create_date TIMESTAMP NOT NULL
)
PARTITION BY RANGE(YEAR(create_date)) (
PARTITION p0 VALUES LESS THAN (2005),
PARTITION p1 VALUES LESS THAN (2006),
PARTITION p2 VALUES LESS THAN (2007),
PARTITION P3 VALUES LESS THAN MAXVALUE
);

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

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
departament TINYINT(2),
)
PARTITION BY LIST(departament) (
PARTITION management VALUES IN(1, 5, 6),
PARTITION sales VALUES IN(2, 3),
PARTITION technical VALUES IN(4, 7, 8)
);

* Разбиение по хеш-функции. В этом виде разбиения можно указать функцию, по которой будут разделяться данные:

CREATE TABLE logs (
value VARCHAR(30) NOT NULL,
create_date TIMESTAMP NOT NULL
)
PARTITION BY HASH( YEAR(create_date) )
PARTITIONS 4;

2. Построчная репликация (row-based replication)
При обычной репликации мастер-слейв при каждом изменении таблицы мастер посылает слейвам ту же команду на изменение данных. В MySQL 5.1 добавлена возможность создания репликации, при которой мастер не посылает слейвам команду на изменение данных, а записывает в лог-файл то, как и в каких строках были изменены данные. Такой вид репликации наиболее надежный и используется в большинстве коммерческих СУБД.

Включить построчную репликацию можно командой

SET GLOBAL binlog_format = 'ROW';

либо в конфигурационном файле.

3. События (events)
В новой версии добавлена возможность создания событий. Эта функциональность позволяет настроить выполнение периодических SQL запросов или процедур. Например, выполнять необходимый пересчет данных раз в день.

DELIMITER //
CREATE EVENT RECALC_SUMM
ON SCHEDULE EVERY 1 WEEK
STARTS '2008-08-13 1:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
UPDATE table1 SET sum = sum + today_amount
END
//

4. Удобство администрирования
Начиная с версии MySQL 5.1 появилась возможность сохранения логов сервера в таблицах. В предыдущих версиях логи можно было сохранять только в файловой системе, что добавляло трудностей с их обработкой. В таблицы можно записывать общий лог MySQL(general log)и лог медленных запросов (slow log). Настроить эту возможность можно параметром --log-output=TABLE. При выборе данной опции в базе данных mysql создадутся таблицы general_log и slow_log.

Кроме этого в MySQL 5.1 появилась таблица PROCESSLIST. В этой таблице сохраняются данные о том, какие процессы выполняет MySQL, идентично тому, как их выводит команда SHOW PROCESSLIST.

5. Поддержка XML XPath
В MySQL 5.1 добавлена поддержка XPath. Теперь XML документ, сохраненный в таблицу, доступен пользователю в виде дерева. Можно получить любое значение из дерева и обновить лишь нужный узел. Для этого были добавлены две функции: ExtractValue и UpdateXML. Например:

SELECT ExtractValue('test', '/a')

выдаст результат test.

SELECT UpdateXML('test', '/a', 'value')

выдаст результат value.

Кроме этого в MySQL 5.1 добавлены:

* Поддержка plug-in архитектуры, позволяющая подключать и изменять компоненты сервера без перезагрузки.
* Репликация кластер-кластер.
* Возможность хранить данные NDB кластера на диске.
* Программа mysqlslap, позволяющая эмулировать нагрузку на MySQL сервер.

Ну что, дети мои, займемся сегодня дрочерством?

1. Не используйте в качестве идентификаторов тип INT(11) SIGNED. Тип INT(10) SIGNED позволяет хранить числовые данные до 2-ух миллиардов (причем поскольку тип SIGNED, то отрицательная половина пространства никогда не будет использована), что практически недостижимо, а занимает 4 байта на каждую запись. Это лишние затраты памяти, времени процессора и пространства.

Используйте менее накладные типы:
TINYINT UNSIGNED — для небольших таблиц, признаков активности, различных маркеров
SMALLINT UNSIGNED — для средних таблиц (новости, города и тд)
MEDUIMINT UNSIGNED — в крайнем случае для больших таблиц.



http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

2. Для дат MySQL имеет сразу 3 специализированных типа: DATETIME, DATE,
TIMESTAMP и именно их стоит использовать для хранения даты.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

3. IP-адреса лучше хранить в INT(10) UNSIGNED (UNSIGNED обязательно), для преобразования
адресов существуют специальные функции в MySQL.






http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton

4. Использовать поля типа TEXT и BLOB стоит только если они действительно необходимы. При выборке их нужно указывать только в случае необходимости не только потому что это уменьшает объем передаваемых данных и размер выделяемой памяти, но и потому, что эти типы данных хранятся в БД в виде отдельных объектов и затрачивается дополнительное время на их извлечение. Для небольших текстов стоит использовать VARCHAR, для текстов, длина которых точно известна и постоянна, например MD5, стоит
использовать CHAR(32).

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

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

7. Индекс UNIQUE и PRIMARY разные индексы. И первый, в отличие от второго, не обеспечивает возможности уникальной идентификации каждой записи.

8. При выборке база данных в общем случае может использовать только один индекс на таблицу, поэтому создание отдельного индекса по каждому столбцу в отдельности, которые участвуют в WHERE не имеет смысла, нужно создавать композитные индексы, исходя из конкретных выборок.

9. Создавать индексы у которых избирательность менее 30% нет смысла. К ним относятся такие поля как активность, статус, видимость и т.д… База данных все равно их использовать не будет.

--
На правах рекламы: Уроки по фотошоп
--

Вот интересно получается. В свете того что Sun купил MySQL а Oracle купил Sun — теперь при установке MySQL будем любоваться значком Oracle=)

Итак, имеем ошибку MySql 1442

Для рассмотрения проблемы в полевых условиях создадим таблицу:

Она содержит поля a и b. Необходимо проапдейтить поле a до какого-то 'n', когда b = 0.
Решение 1:

Тригер создается на ура, но мы получаем сабжевую ошибку MySql 1442
Can’t update table ‘t1′ in stored function/trigger because it is already used by statement which invoked this stored function/trigger
когда пытаемся заапдейтить поле b в таблице t1.

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

С новым тригером ошибка вида «ERROR 1442 (HY000): Can’t update table ‘t1′ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.» уже не вознимает при апдейте. Проверим?

Таким образом, если мы хотим создать тригер на таблицу, которая будет апдейтись себя по изменению на свои же поля, то нам необходимо использовать поле NEW.column_name, которое будет ссылаться на строку после апдейта и не делать полного апдейта.

Если же вы будете апдейтить другую таблицу, то первое решение вполне подойдет:

Наверняка все знают о таком понятии в SQL как NULL.

NULL — это не 0, это не 1, это не пустая строка. NULL обозначает что на данный момент неизвестно какое значение хранится в базе данных.

Теперь маленькое исследование.

1. Что будет результатом логической операции



Поскольку неизвестно, чем именно является NULL — TRUE или FALSE, результат запроса тоже неизвестный, поэтому результат этой операции — NULL

2. Что будет результатом логической операции



Поскольку один из операндов логической операции — TRUE, то независимо от значения второго операнда результат операции будет TRUE. MySQL следует этой логике и выдает результат — TRUE

3. Что будет в результате арифмитической операции



Можно было бы легко следовать логике, аналогичной второму вопросу и прийти к тому что в результате будет 0. Но как показала практика у MySQL на этот счет своя логика. Результат этой операции будет NULL.

Вот такая арифметика.

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

В PHP4 есть такая замечательная функция substr

substr() возвращает подстроку строки string длиной length, начинающегося с start символа по счету.
Вся проблема в том что для кодировки unicode эта фунция работает некорректно.
                                    

Эту проблему я решил с помощью mysql. Поскольку данные хранятся в базе, логично было бы делать выборку и возвращать уже обрезанные данные. К тому же это будет значительно производительнее, чем выполнять эту операцию средствами php.

В MySQL есть такая же функция SUBSTR которая прекрасно работает со всеми кодировками. Единственный подводный камень — в MySQL строка начинается не с 0 как в php а с 1. Если вы вторым параметром укажите 0 то в результате получите пустую строку.



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

Для этого используем конструкцию IF из синтаксиса MySQL. Если строка до обрезания не равна строке после обрезания то нужно добавить '...' к этому комментарию. Получим вот такой запрос:


В результате получаем как раз то что нам нужно:



Потом просто выводим уже готовые username и comment в нужном нам месте на странице.

Сегодня на одном из моих сайтов вылезла интересная ошибка mysql. Начал разбираться в чем дело — запросы никто не менял и на ровном месте появилась ошибка.

Ошибка оказалась в таком запросе:

Кто не в курсе — выбор случайных 5 записей из таблички(а вы как выбираете?)
Причем запрос
выполняется на ура.
В чем же проблема? Погуглил и оказалось что что эта ошибка вылазит когда кончается место на диске. Освободил место и… О чудо! Все заработало.

P.S. Только что захожу на сайт мобильного оператора Jeans и вижу что не у одного меня такие проблемы=):

Дано: В базе хранится продолжительность ролика в секундах, к примеру 453 секунд.
Задача: средствами mysql сделать выборку что бы время было в человеческом формате минуты:секунды.
Решение довольно простое, надо знать лишь несколько функций для работы с датой и временем


Для нашего примера в 453 секунды получим результат: 07:33.