MYSQL (15)

How to import mysql innodb with foreign key constraint error?

To import mysql innodb with foreign key constraint error, follow the steps below:- If you have the access to mysql thru terminal, then you can use the command below:- mysql> SET foreign_key_checks = 0; mysql> SOURCE your_db_dump_file; mysql> SET foreign_key_checks = 1; If you import your database thru web interface like phpmyadmin, then you need to edit the sql dump file. Add this to the 1st line of your sql db dump file:- SET foreign_key_checks = 0; then add this line to the last line in your sql db dump file:- SET foreign_key_checks = 1; Now you may import your…
Read more...

[ERROR] /usr/libexec/mysqld: Can't open file: '.s.frm' (errno: 24)

130522 1:45:20 [ERROR] /usr/libexec/mysqld: Can't open file: './alamise/alamise_categories.frm' (errno: 24) ----------------------------------------------------------------- ps -ef|grep mysql 查看 pid pid 32150 --------------------------------------------------------------------- cat /proc/32150/limits |grep files 查看pid最大limite ------------------------------------------------- lsof -p 32150 |wc -l 查看已经打开的数量 lsof | grep mysqld 查看列表 ----------------------------------------------------------- 查看 mysql值 mysql -uUser -pPASS show global variables like "%open_files_limit%"; ------------------------------------- 修复表 mysqlcheck -A -o -r -p password ---------------------------------------- 更改 /etc/my.cnf [mysqld] open_files_limit = 100000
Read more...

mysqldump: Couldn’t execute ‘SHOW FUNCTION STATUS WHERE Db = ‘your-db-here”: Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50077, now running 50152. Please use mysql_upgrade to fix this error. (1558)

mysqldump: Couldn’t execute ‘SHOW FUNCTION STATUS WHERE Db = ‘your-db-here”: Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50077, now running 50152. Please use mysql_upgrade to fix this error. (1558) /usr/bin/mysql_upgrade -uxxxx -pxxxxxx 如果你的webmin/virtualmin自动备份出现下面的错误,那么,就是mysql升级后没有做mysql_upgrade 表修复。 在新版下执行mysql_upgrade命令,其实这个命令包含一下三个命令: # mysqlcheck –check-upgrade –all-databases –auto-repai # mysql_fix_privilege_tables # mysqlcheck –all-databases –check-upgrade –fix-db-names –fix-table-names Note: 在每一次的升级过程中,mysql_upgrade这个命令我们都应该去执行,它通过mysqlcheck命令帮我们去检查表是否兼容新版本的数据库同时作出修复,还有个很重要的作用就是使用mysql_fix_privilege_tables命令去升级权限表。
Read more...

重启MySQL的正确方法

如果你是从源码包安装的Mysql,那么系统中是没有红帽常用的servcie这个脚本的, 所以只好手工重启 有人建议Killall mysql。这种野蛮的方法其实是不行的,强制终止的话,可能会造成表损坏,损失是巨大的。 这里推荐安全的重启方法 $mysql_dir/bin/mysqladmin -u root -p shutdown $mysql_dir/bin/safe_mysqld & mysqladmin和mysqld_safe位于Mysql安装目录的bin目录下,很容易找到的。 /usr/bin/mysqladmin -u root -p shutdown
Read more...

[ERROR] /usr/libexec/mysqld: Can't open file: './xxx/xxxxxxxxx.frm' (errno: 24)

my.cnf innodb_open_files=1024 open_files_limit=5000 mysql -uroot -pXXXXX 1,显示当前系统的文件限制# cat /proc/sys/fs/file-max调整参考:http://hi.baidu.com/loveyoursmile/blog/item/e26b550fed89ff226059f3c4.html2,通过phpmyadmin查询mysql配置参数(既open files limit)。一般情况下,mysql 5的open files limit会自动根据table cache调整。open files limit 系统默认调整为table cache的2倍多一点。 I go the following errors in a database: 091030 0:23:46 [ERROR] /usr/sbin/mysqld-max: Can't open file: './register/member.frm' (errno: 24)091030 0:23:46 [ERROR] /usr/sbin/mysqld-max: Can't open file: './register/description.frm' (errno: 24) This is related to file descriptors. # perror 24OS error code 24: Too many open files I enlarge the value of open_files_limit by adding a line in my.cnf. open_files_limit=5000 For open_files_limit, please refer this link: http://cherry.world.edoors.com/CozM0IXYA-fc For more explanation about this error, please refer this page: B.5.2.18. 'File' Not Found and Similar Errorshttp://dev.mysql.com/doc/refman/5.0/en/not-enough-file-handles.html how…
Read more...

2维数组 input 处理

输出 $rs9 = mysql_fetch_array($sql9);          if($rs9!=null)          {          $i=1;            do{        ?>         <tr>    <td id="mytd"></td>    <td id="mytd"><?php echo $ini_array['menu9_examen_Resultats_Examens'].":" ;?><input type="text" name="area[][1]"><input type="hidden" name="area[][2]" value=""></td>    </tr>        <?php $i++; }    while($rs9 = mysql_fetch_array($sql9));     } ?>   处理   if($_GET['action']=="submit"){$notes = $_POST['area'];}foreach(   $notes   as   $key=>   $value){        $resualt=mysql_query("                              REPLACE INTO `ilci`.`tbl_choix_examen_notes` (                              `notes_stu_id` ,                              `notes_empl_id` ,                              `notes_note`                              )                              VALUES (                              '$value[2]', '$empl_id', '$value[1]'                              );");          if($resualt){                    echo "<tr><td id=\"mytd\">".$ini_array['menu9_examen_mysql_success']."</td></tr>" ; }           else{                   echo "<tr><td id=\"mytd\">".$ini_array['menu9_examen_mysql_fail']."</td></tr>" ; }                         }            ?>            </table><?php} else {
Read more...

mysql TO_DAYS(date) 函数

TO_DAYS(date) 给定一个日期date, 返回一个天数 (从年份0开始的天数 )。 mysql> SELECT TO_DAYS(950501); -> 728779 mysql> SELECT TO_DAYS(‘1997-10-07′); -> 729669 TO_DAYS() 不用于阳历出现(1582)前的值,原因是当日历改变时,遗失的日期不会被考虑在内。 请记住, MySQL“日期和时间类型”中的规则将日期中的二位数年份值转化为四位。例如,  ‘1997-10-07′和 ‘97-10-07′ 被视为同样的日期: mysql> SELECT TO_DAYS(‘1997-10-07′), TO_DAYS(‘97-10-07′); -> 729669, 729669 对于1582 年之前的日期(或许在其它地区为下一年 ), 该函数的结果实不可靠的。
Read more...