# MySQL

# Repair

`mysqlcheck -r -u'база_данных' -p'пароль_базы_данных' 'база_данных'`

`REPAIR TABLE tablename USE_FRM;`

Если побилась `mysql.user` и мускуль из-за этого не поднимается

```bash
for i in $(ls *.MYI | awk -F'.' '{print $1}') ; do myisamchk -r -q $i  ;done
```

# Проблемы с переносом баз

### ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline

```bash
sed -i 's#ENGINE=InnoDB#ENGINE=InnoDB ROW_FORMAT=DYNAMIC#g' dump.sql
```

### ERROR 1416 (22003) at line 801: Cannot get geometry object from data you send to the GEOMETRY field

Где-то есть таблица с пустым геометрическим полем. Заполни его какими-нибудь координатами, 0,0,0 например

### ERROR 1449 (HY000) at line 4012: The user specified as a definer ('asdf'@'localhost') does not exist

```
sed -i 's/\/\*!50013 DEFINER=`asdf`@`localhost` SQL SECURITY DEFINER \*\///g' dump.sql

```

### ERROR 1071 (42000) at line 874: Specified key was too long; max key length is 255 bytes

```bash
sed -i 's/tinytext/VARCHAR(255)/g'
```

# SQL

### <span class="mw-headline" id="bkmrk-sql-alter%2Fconvert-0">SQL alter/convert</span>

- таблицу и поля в кодировки

`ALTER TABLE `DATABASE`.`TABLE` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;`

- базу, таблицы, поля в кодировки

`ALTER DATABASE `DATABASE` DEFAULT CHARSET=utf8 COLLATE utf8_bin;`

[https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8#6115705](https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8#6115705)

> Use the ALTER DATABASE and ALTER TABLE commands.
> 
> ```
> ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
> ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
> ```
> 
> Or if you're still on MySQL 5.5.2 or older which didn't support 4-byte UTF-8, use utf8 instead of utf8mb4:
> 
> ```
> ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
> ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
> ```

# клиент

Красивый шелл:

<span style="font-family: monospace;">`prompt='\u at \h in \d> '`  
  
<span style="color: #000000; background-color: #ffffff;"> </span>  
</span>