Решение ошибки #1064 — You have an error in your SQL syntax

Заметил однажды при выполнении SQL запроса ошибку следующего содержания:

Читать далее «Решение ошибки #1064 — You have an error in your SQL syntax»

Выполнение SQL запроса из скрипта в Linux

Понадобилось недавно написать скрипт выполняющий sql запрос в базу MySQL.
Пример содержимого:

# Описание, тут я написал для других что скрипт добавлен в crontab, дабы его не переместили
mysql -u ПОЛЬЗОВАТЕЛЬ -pПАРОЛЬ -h 127.0.0.1 -e "UPDATE nika_system.abon SET otkl=0 WHERE depozit > '10' AND (otkl='-1' OR otkl='-2');";

Чтобы скрипт выполнялся автоматически, откроем в любом текстовом редакторе файл crontab (в редакторе nano CTRL+X для выхода и y/n для сохранения или отмены изменений):

sudo nano /etc/crontab

Добавим в него следующую строку:

0 9 * * * root /home/nika/scripts/reset_credit_nika.sh > /dev/null 2>&1

Теперь ежедневно в 9 утра скрипт будет выполняться.

Приведу пример ежедневного автоматического вывода данных из sql таблицы в текстовый файл:

#!/bin/bash
mysql -u ПОЛЬЗОВАТЕЛЬ -pПАРОЛЬ -h 192.168.1.1 -s -N -e "SELECT id FROM nika_system.abon WHERE tarif=109;";

В /etc/crontab добавим:

0 8 * * * root /scripts/freektb.sh > /srv/samba/dir/mirazh/$(date +%Y-%m-%d).txt

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

Решение ошибки запуска MySQL сервера — «Unknown/unsupported storage engine: InnoDB»

Заметил недавно ошибку запуска MySQL сервера:

150821 20:34:03 InnoDB: Fatal error: cannot allocate memory for the buffer pool
150821 20:34:03 [ERROR] Plugin ‘InnoDB’ init function returned error.
150821 20:34:03 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
150821 20:34:03 [ERROR] Unknown/unsupported storage engine: InnoDB
150821 20:34:03 [ERROR] Aborting

Удаление следующих файлов не дало результата:

sudo rm /var/lib/mysql/ib_logfile0
sudo rm /var/lib/mysql/ib_logfile1 
sudo rm /var/lib/mysql/ibdata1
sudo service mysql restart

Причиной ошибки оказалась нехватка оперативной памяти.
Посмотреть количество свободной памяти можно например командой:
free -m

После добавления оперативной памяти (ОЗУ) к серверу данная ошибка больше не отображалась.

SQL запросы для АСРМ «Ника»

Недавно нужно было написать несколько sql запросов для биллинга АСРМ «Ника», поэтому выложу их:

Смена тарифа абонентам:

UPDATE abon SET tarif=57 WHERE tarif=58;
UPDATE abon SET tarif=137 WHERE tarif=122 AND id=1478;
UPDATE abon SET tarif=123 WHERE tarif=159 AND street=216;

Очистка старой истории трафика:

DELETE FROM opt_traffic where date < '2016-05-01';
DELETE FROM stat_traffic where date < '2016-05-01';

Очистка старой истории предоставленных услуг:

DELETE FROM stat_serv where date < '2016-01-01';

Очистка старой SNMP истории модемов:

DELETE FROM stat_snmp where date < '2014-01-01';

Сумма платежей за предыдущий день:

SELECT SUM(money) FROM nika_system.mon WHERE date= CURDATE() - INTERVAL 1 DAY;

Количество абонентов отключенных за неуплату:

SELECT count(*) FROM nika_system.abon WHERE killed='killed' AND state=1;

Количество абонентов отключенных оператором:

SELECT count(*) FROM nika_system.abon WHERE killed='killed' AND state=2;

Количество всех активных абонентов:

SELECT count(*) FROM nika_system.abon WHERE killed='';

Количество активных абонентов со статусом «Включен и не отключается»:

SELECT count(*) FROM nika_system.abon WHERE killed='' AND state=3;

Количество абонентов добавленых в 2016 году:

SELECT count(*) FROM nika_system.abon WHERE datetime LIKE '2016%';

Изменение или удаление начисленной услуги абоненту (где serv — ID услуги):

UPDATE stat_serv SET serv='131' WHERE user='767' AND date='2016-02-04' AND serv='64';
DELETE FROM stat_serv WHERE user='3977' AND date='2016-02-23' AND serv='64';

Пример обнуления кредита пользователям у которых кредит от -1 до -30 и на счету больше 10 грн.:

SELECT * FROM `abon` WHERE otkl = '-5' AND depozit > '10';
UPDATE abon SET otkl=0 WHERE depozit > '10' AND (otkl='-1' OR otkl='-2' OR otkl='-3' OR otkl='-4' OR otkl='-5' OR otkl='-6' OR otkl='-7' OR otkl='-8' OR otkl='-9' OR otkl='-10' OR otkl='-11' OR otkl='-12' OR otkl='-13' OR otkl='-14' OR otkl='-15' OR otkl='-16' OR otkl='-17' OR otkl='-18' OR otkl='-19' OR otkl='-20' OR otkl='-21' OR otkl='-22' OR otkl='-23' OR otkl='-24' OR otkl='-25' OR otkl='-26' OR otkl='-27' OR otkl='-28' OR otkl='-29' OR otkl='-30');

Пример выбора ID не отключенных абонентов с указанными тарифами и сортировкой по возрастанию:

SELECT id FROM nika_system.abon WHERE (tarif='121' OR tarif='135' OR tarif='136') AND killed='' ORDER BY id ASC

Изменение категории пользователям на указанной улице:

UPDATE abon SET category=1 WHERE category=7 AND street = 145;

Изменение группы для пользователей с указанными улицами:

select * FROM abon where street=74;
update abon set groups=73 where street=74;
update abon set groups=73 where (street=14 OR street=74 OR street=81 OR street=86 OR street=134 OR street=144 OR street=152 OR street=154 OR street=249 OR street=277 OR street=283 OR street=290 OR street=292 OR street=329);

Примеры MySQL запросов

Приведу ниже пример некоторых стандартных MySQL запросов.

Подключение к базе данных:

<?php
$link = mysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error());
mysql_close($link);
?>

Выбор базы данных:

<?php
 mysql_select_db ("auth", $link);
?>

Вывод количества записей в таблице:

SELECT count(*) FROM таблица1;

Удаление таблицы:

DROP TABLE таблица1;

Удаление строки с id=25 в таблица1:

DELETE from таблица1 where id = '25';

Изменение кодировки базы данных:

ALTER DATABASE `characters` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Создание таблицы:

CREATE TABLE таблица1 (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
поле2 CHAR(60) NOT NULL,
PRIMARY KEY (id)
);

Сортировка (ORDER BY):

SELECT `поле1`, `поле2`, `поле3` FROM `таблица1` ORDER BY `поле2`; //выбирает поля из таблицы1 и сортирует их по полю2.
SELECT `поле1` FROM `таблица1` WHERE поле1 = 'а%' ORDER BY `поле2` ASC LIMIT 20; //выбирает 20 строк которые начинаются с буквы а и сортирует их по полю2 по возрастанию.

ASC – по возрастанию и DESC – по убыванию. По-умолчанию данные сортируются по возрастанию.

Изменение данных:

UPDATE таблица1 set поле1 = 'текст' where id = '25'

Смотрите также мои статьи:
SQL запросы для ABillS
SQL запросы для АСРМ «Ника»

Как вытащить данные из одного столбца mysql таблицы

Чтобы увидеть данные из одно столбца, достаточно выполнить SQL запрос (где «abcd» — имя столбца в таблице):

SELECT abcd FROM table;

Для экспорта в файл достаточно выполнить команду в Linux:

mysql -u root -e "SELECT abcd FROM database;" -s -N > file.txt

Приведу пример экспорта email адресов из mysql таблицы на http страницу используя PHP.
Мысль сразу пришла такого плана (создаем php файл и открываем его через браузер):

<?php
// Соединяемся с mysql сервером
mysql_connect("localhost", "USER", "PASSWORD") or die (mysql_error ());
// Выбираем базу данных
mysql_select_db("users") or die(mysql_error());
// SQL-запрос
$rows = "SELECT * FROM account";
// Выполняем этот SQL-запрос
$d = mysql_query($rows);
// Каждый ряд становится массивом ($row) с помощью функции mysql_fetch_array
while($row = mysql_fetch_array($d)) {
// Выводим значения столбца email
echo $row['email'] . "<br />";
}
// Закрываем соединение с базой данных
mysql_close();
?>

online.pl скрипт количества пользователей онлайн

Приведу пример скрипта выполняющего sql запрос в mysql базу с выводом результата. Можно прицепить его к графикам Zabbix, Cacti и т.д. чтобы мониторить например количество игроков онлайн на каком нибудь игровом сервере.

#!/usr/bin/perl
use DBI;
my $host = "localhost";
my $port = "3306";
my $user = "root";
my $pass = "";
my $db = "test";
$dbh = DBI->connect("DBI:mysql:$db:$host:$port",$user,$pass);
$sth = $dbh->prepare("SELECT count(id) FROM `online`");
$sth->execute;
while ($ref = $sth->fetchrow_arrayref) {
print "$$ref[0]\n";
}
$rc = $sth->finish;
$rc = $dbh->disconnect;

Установка и настройка MySQL сервера на Ubuntu

Установка сервера и клиента чтобы можно было через консоль заливать файлы:

sudo apt-get install mysql-server mysql-client

Директория с файлами конфигурации находится по адресу:

/etc/mysql

Директория хранения баз данных:

/var/lib/mysql

Чтобы разрешить удаленный доступ к mysql серверу, в конфигурационном файле /etc/mysql/my.cnf необходимо закомментировать строку bind-address = 127.0.0.1 (поставить перед ней символ #) и выполнить перезапуск mysql сервера чтобы изменения применились:

sudo service mysql restart

Можно установить пароль root, удалить базу test, запретить удаленный доступ root пользователю и настроить прочие параметры безопасности выполнив команду:

sudo mysql_secure_installation

Покажу некоторые примеры команд:
Подключение к mysql через командную строку:

mysql -u имяпользователя -p

Просмотр доступных баз данных:

show databases;

Открываем нужную базу данных:

use имябазы;

Смотрим какие в базе есть таблицы:

show tables;

Либо создаем новую базу:

create database имябазы;

Удаление базы:

drop database имябазы;

Создание пользователя:

CREATE USER 'имяпользователя'@'localhost' IDENTIFIED BY 'пароль';

Даем пользователю полные привилегии над нужной базой:

GRANT ALL PRIVILEGES ON имябазы.* to 'имяпользователя'@'localhost';

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

mysqldump -u имяпользователя -p имябазы > имядампа.sql

Заливаем дамп в нужную базу:

mysql -u имяпользователя -p имябазы < имядампа.sql

Команда справки — help

Для безопасности подключаемся и удаляем стандартную базу test:

mysql -uroot -p
DROP DATABASE test;

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

SHOW GRANTS FOR ''@'localhost'
DROP USER "";
quit

Смотрим какие существуют пользователи и какие у них привилегии:

use mysql;
select * from user;

Проверяем запущен ли сервер:

sudo netstat -tap | grep mysql

Проверяем соединения на порте 3306:

netstat -na | grep 3306

Можно посмотреть пакеты на порт 3306, когда возникают проблемы с подключением к mysql из вне:

tcpdump port 3306
tcpdump port 3306 -i eth0

Смотрите также:
IPTables правила для MySQL
Восстановление MySQL таблиц
Прочее

Исправление ошибки MySQL: Can’t create table ‘tablename’ (errno: 150)

Ошибка MySQL: Can’t create table ‘tablename’ (errno: 150) может возникать когда вы например заливаете бекап и MySQL питается создать таблицу 1 которая ссылается на таблицу 2, которой по сути еще нету. Решить можно вставив в бекап строки:
в начале
SET FOREIGN_KEY_CHECKS = 0;
и в самом конце
SET FOREIGN_KEY_CHECKS = 1;