王筝的博客
ruby学习

今天存储数据的时候报错,发现是3435065640超出了常规int的存储长度,

RangeError (3435065640 is out of range for ActiveRecord::Type::Integer with limit 4)

$ bundle exec rails db -p
mysql> desc recommended_videos;
+-------------------------+--------------+------+-----+---------+----------------+
| Field                   | Type         | Null | Key | Default | Extra          |
+-------------------------+--------------+------+-----+---------+----------------+
| id                      | int(11)      | NO   | PRI | NULL    | auto_increment |
| video_id                | varchar(255) | YES  |     |         |                |
| res_desc                | text         | YES  |     | NULL    |                |
| state                   | int(11)      | YES  |     | 0       |                |
| created_at              | datetime     | YES  |     | NULL    |                |           |
+-------------------------+--------------+------+-----+---------+----------------+

http://dev.mysql.com/doc/refman/5.7/en/integer-types.html

12.2.1 Integer Types (Exact Value) – INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT. As an extension to the standard, MySQL also supports the integer types TINYINTMEDIUMINT, and BIGINT. The following table shows the required storage and range for each integer type.


Type Storage Minimum Value Maximum Value
(Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615
int的存储上限是2147483647,
所以change_column
class ChangeTotalVvTypeToRecommendedVideos < ActiveRecord::Migration
  def change
    change_column :recommended_videos, :total_vv, :integer, :limit => 8
  end 
end
mysql> desc recommended_videos;
+-------------------------+--------------+------+-----+---------+----------------+
| Field                   | Type         | Null | Key | Default | Extra          |
+-------------------------+--------------+------+-----+---------+----------------+
| id                      | int(11)      | NO   | PRI | NULL    | auto_increment |
| state                   | int(11)      | YES  |     | 0       |                |
| sequence                | int(11)      | YES  |     | 1       |                |
| created_at              | datetime     | YES  |     | NULL    |                |
| updated_at              | datetime     | YES  |     | NULL    |                |
| total_vv                | bigint(20)   | YES  |     | NULL    |                |
+-------------------------+--------------+------+-----+---------+----------------+

 

本文为转载,原内容见下方博客链接

http://www.cnblogs.com/starof/p/4680083.html

一、系统环境

yum update升级以后的系统版本为

[root@yl-web yl]# cat /etc/redhat-release 
CentOS Linux release 7.1.1503 (Core)

二、mysql安装

#yum install mysql
#yum install mysql-server
#yum install mysql-devel

安装mysql和mysql-devel都成功,但是安装mysql-server失败

查资料发现是CentOS 7 版本将MySQL数据库软件从默认的程序列表中移除,用mariadb代替了。

有两种解决办法:

1、方法一:安装mariadb

MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可。开发这个分支的原因之一是:甲骨文公司收购了MySQL后,有将MySQL闭源的潜在风险,因此社区采用分支的方式来避开这个风险。MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。

安装mariadb,大小59 M。

[root@yl-web yl]# yum install mariadb-server mariadb 

mariadb数据库的相关命令是:

systemctl start mariadb  #启动MariaDB

systemctl stop mariadb  #停止MariaDB

systemctl restart mariadb  #重启MariaDB

systemctl enable mariadb  #设置开机启动

所以先启动数据库

[root@yl-web yl]# systemctl start mariadb

然后就可以正常使用mysql了

[root@yl-web yl]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.41-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

安装mariadb后显示的也是 MariaDB [(none)]> ,可能看起来有点不习惯。下面是第二种方法。

2、方法二:官网下载安装mysql-server

# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
# rpm -ivh mysql-community-release-el7-5.noarch.rpm
# yum install mysql-community-server

安装成功后重启mysql服务。

# service mysqld restart

所以安装完以后mariadb自动就被替换了,将不再生效。

 

 

mysql> SELECT desc,status,play_time,push_type, ‘interval’ FROM cms_push;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 700
Current database: xxx_cms

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘desc,status,play_time,push_type, ‘interval’ FROM cms_push’ at line 1

问题很简单,desc和interval是关键字,需要加上引号,以后起名要注意了

mysql> SELECT ‘desc’,status,play_time,push_type,’interval’ FROM cms_push;

 

$mysql

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

$service mysqld status

-bash: service: command not found

查阅了网上的各种说法

最后解决的方法就一句

$mysqld

由于之前调整了/etc/mysql/my.cnf试图修复数据库不能存中文的问题,这个问题没解决,以至于数据库连接不上了.

tail -f /var/log/mysql/error.log

新开窗口启动 sudo service mysql start

这时候的log里的报错为[ERROR]/usr/sbin/mysqld: unknow variable ‘default-character-set=utf8′

终于知道是因为修改了/etc/mysql/my.cnf引起的问题了,把这一行删掉就ok了