dedecms重整dede_arctiny表

症状: dede:list 标签出现混淆,一些分类的文章没有显示,原因是删除文章的时候只删除了dede_archives 表,而dede_arctiny表没做相应的删除行,你会发现有些文章id在dede_arctiny表里依然存在,而dedecms原本为了加快速度的dede_arctiny表反而导致了错误。

select id from dede_arctiny where not exists(select * from dede_archives where dede_arctiny.id=dede_archives.id);

这个语句就可以看出来存在于dede_arctiny表里的多余数据,但是并不保证缺失数据

删除dede_arctiny里面的无效数据的sql语句为

delete from dede_arctiny where not exists(select * from dede_archives where dede_arctiny.id=dede_archives.id);

================================================================

下面是我们写的查缺补漏的全部重新生成最新鲜数据dede_arciny表的方法:

dedecms 织梦的dede_arctiny表的重整

truncate table `dede_arctiny`;
insert into `dede_arctiny`(`id`,`typeid`,`typeid2`,`arcrank`,`channel`,`senddate`,`sortrank`,`mid`) select `id`,`typeid`,`typeid2`,`arcrank`,`channel`,`senddate`,`sortrank`,`mid` from `dede_archives` where `arcrank` > -1 order by `id` asc ;

discuz 附件过多导致超出数据库的修复

今天一个朋友的discuz论坛上传图片出现无法上传情况,经过chrome F12 调试发现是数据库的附件表的aid超出了MEDIUMINT(8)的允许最大值,所以适当增大就好了,下面的sql有效

ALTER TABLE  `pre_forum_attachment` CHANGE  `aid`  `aid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE  `pre_forum_attachment_unused` CHANGE  `aid`  `aid` BIGINT UNSIGNED NOT NULL;
ALTER TABLE  `pre_forum_attachment_0` CHANGE  `aid`  `aid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
。
。
。
ALTER TABLE  `pre_forum_attachment_9` CHANGE  `aid`  `aid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

帝国cms手动分表

1)帝国后台分表管理,建立phome_ecms_news_data_2表
2)insert into  phome_ecms_news_data_2(id)   select id   from  phome_ecms_news_data_1 order by id desc  limit 30001,30000; //每3万条一个分表
3) update phome_ecms_news_data_2 a ,phome_ecms_news_data_1 b set a.classid=b.classid, a.keyid=b.keyid,a.dokey=b.dokey,a.newstempid=b.newstempid,a.closepl=b.closepl,a.haveaddfen=b.haveaddfen,a.infotags=b.infotags,a.writer=b.writer,a.befrom=b.befrom,a.newstext=b.newstext where a.id=b.id; // 更新 phome_ecms_news_data_2 的其它字段
4) update phome_ecms_news set stb =2 where id in (select id from phome_ecms_news_data_2); // 更新news表的stb字段
5) 要分更多表,依次类推

网盘程序的update

相信很多程序猿在设计产品表的时候,都相应设置了一个 view_number 字段
然后在每次用户打开产品的内容页的时候用 view_number=view_number+1 来更新产品表,语句是
UPDATE  products set view_number = view_number + 1 where product_id= 123456;
但是UPDATE 经常导致 select 语句的阻塞,从而SELECT 出现 wait for table lock,
那么我们怎么解决这样的锁表问题呢,用 LOW_PRIORITY 可以把 UPDATE 语句的优先级降低,从而不阻塞SELECT.

UPDATE  LOW_PRIORITY products set view_number = view_number + 1 where product_id= 123456;

mssql2005 bak文件恢复,无法登陆的修复

参考文件
http://nxdxt.blog.51cto.com/394/97996

抄录原文如下,以备查阅

使用sp_change_users_login排除孤立用户,所谓孤立帐户,就是某个数据库的帐户只有用户名而没有登录名,这样的用户在用户库的sysusers系统表中存在,而在master数据库的syslogins中却没有对应的记录。
孤立帐户的产生一般是一下两种:
1.将备份的数据库在其它机器上还原;
2.重装系统或SQL SERVER之后只还原了用户库
解决方法是使用sp_change_users_login来修复。
sp_change_users_login的用法有三种
用法1:

exec sp_change_users_login 'REPORT'

列出当前数据库的孤立用户
用法2:

exec sp_change_users_login 'AUTO_FIX','用户名'

可以自动将用户名所对应的同名登录添加到syslogins中
用法3:

exec sp_change_users_login 'UPDATE_ONE','用户名','登录名'

将用户名映射为指定的登录名。
—————————————————————————————————————-
看看是否有用

SQL孤立用户解决方案

症状
当您将数据库备份恢复到另一台服务器时,可能会遇到孤立用户的问题。SQL Server 联机丛书中的孤立用户疑难解答主题中没有讲述解决此问题的具体步骤。
本文介绍了如何解决孤立用户问题。
状态
Microsoft 已经确认这是在本文开头列出的 Microsoft 产品中存在的问题。
更多信息
虽然术语“登录”和“用户”经常交换使用,但它们之间有很大的不同。登录用于用户身份验证,而数据库用户帐户用于数据库访问和权限验证。登录通过安全识别符 (SID) 与用户关联。访问 SQL Server 服务器需要登录。验证特定登录是否有效的过程称为“身份验证”。登录必须与 SQL Server 数据库用户相关联。您使用用户帐户控制数据库中执行的活动。如果数据库中不存在针对特定登录的用户帐户,使用该登录的用户即使能够连接到 SQL Server 服务器,也无法访问数据库。但是,该情形的唯一例外是当数据库包含“guest”用户帐户时。与用户帐户不关联的登录将被映射到 guest 用户。相反,如果存在数据库用户,但没有与其关联的登录,则该用户将无法登录到 SQL Server 服务器中。
将数据库恢复到其他服务器时,数据库中包含一组用户和权限,但可能没有相应的登录或者登录所关联的用户可能不是相同的用户。这种情况被称为存在“孤立用户”。
孤立用户疑难解答
当您将数据库备份恢复到另一台服务器时,可能会遇到孤立用户的问题。以下情形说明了该问题并阐述如何加以解决。
1. 向主数据库添加一个登录,并将默认数据库指定为 Northwind: Use master go sp_addlogin ‘test’, ‘password’, ‘Northwind’
2. 向刚创建的用户授予访问权限: Use Northwind go sp_grantdbaccess ‘test’
3. 备份数据库。 BACKUP DATABASE Northwind
TO DISK = ‘C:MSSQLBACKUPNorthwind.bak’
4. 将数据库恢复到其他 SQL Server 服务器: RESTORE DATABASE Northwind
FROM DISK = ‘C:MSSQLBACKUPNorthwind.bak’

恢复的数据库包含名为“test”的用户,但没有相应的登录,这就导致“test”成为孤立用户。
5. 现在,为了检测孤立用户,请运行此代码: Use Northwind go sp_change_users_login ‘report’

输出中列出了所有登录,其中包含 Northwind 数据库的 sysusers 系统表和主数据库的 sysxlogins 系统表中不匹配的条目。
解决孤立用户问题的步骤
1. 为前一步中的孤立用户运行以下命令:
Use Northwind
go
sp_change_users_login ‘update_one’, ‘test’, ‘test’

这样,就将服务器登录“test”与 Northwind 数据库用户“test”重新连接起来。
sp_change_users_login 存储过程还可以使用“auto_fix”参数对所有孤立用户执行更新,但不推荐这样做,因为 SQL Server 会尝试按名称匹配登录和用户。大多数情况下这都是可行的;但是,如果用户与错误登录关联,该用户可能拥有错误的权限。
2. 在上一步中运行代码后,用户就可以访问数据库了。然后用户可以使用 sp_password 存储过程更改密码: Use master
go
sp_password NULL, ‘ok’, ‘test’

此存储过程不能用于 Microsoft Windows NT 安全帐户。通过 Windows NT 网络帐户连接到 SQL Server 服务器的用户是由 Windows NT 授权的;因此,这些用户只能在 Windows NT 中更改密码。
只有 sysadmin 角色的成员可以更改其他用户的登录密码。
—————————————————————————————————————-
SQL2005删除用户的时候,产生“数据库主体在该数据库中拥有架构,无法删除”的解决办法
–执行如下SQL语句
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
–然后手动删除就可以了。

—————————————————————————————————————-
[导入]sql2000备份的数据库还原到sql2005后,选择“数据库关系图”提示:此数据库没有有效所有者,因此无法安装数据库关系图支持对象”的解决方法
sql2000备份的数据库还原到sql2005后,选择“数据库关系图”提示:此数据库没有有效所有者,因此无法安装数据库关系图支持对象。若要继续,请首先使用“数据库属性”对话框的“文件”页或 ALTER AUTHORIZATION 语句将数据库所有者设置为有效登录名,然后再添加数据库关系图支持对象。

解决方法如下:
1、设置兼容级别为90(2005为90)
USE [master]
GO
EXEC dbo.sp_dbcmptlevel @dbname=’数据库名’, @new_cmptlevel=90
GO
或是选责你还原的数据库,点右键,选属性->选项->兼容级别,选择sqlserver2005(90) 然后确定,
这时,你在该数据库下展开“数据库关系图”节点时会有个提示,”此数据库缺少一个或多个使用数据库关系图所需的支持对象,是否创建”,选择“是”即可。
2、通过以上的方法操作,如果问题依然存在的话,按下列方法继续
选择你的数据库,然后选择”安全性”->”用户”,选择dbo,打开属性页,如登录名为空的话,新建查询,然后
use [你的数据库名]
EXEC sp_changedbowner ‘sa’
执行成功后,你再选择”数据库关系图”节点,时提示 “此数据库缺少一个或多个使用数据库关系图所需的支持对象,是否创建”,选择“是”即可。 就可以看到原先建的关系图了。

—————————————————————————————————————-

从服务器上作导入导出至本地机上,数据库中的表都在,可是表名前段的架构身份不是“dbo”了,而是服务器上数据库的“库名”。这样架构身份不同了,程序运行就出问题了。试过单个修改表,在sql2005的属性窗口可以更改架构者,可是N多表哪儿能手动改得过来呀!还请高手指点批量更改的方法。在此谢过。
SQL Server2005可以使用系统存储过程sp_changeobjectowner更改数据库对象的所有者。

sp_changeobjectowner ‘对象名(包括架构名)’,’新架构名’

批量修改请用:

方法一:使用游标

declare @name sysname
declare csr1 cursor
for
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
open csr1

FETCH NEXT FROM csr1 INTO @name
while (@@FETCH_STATUS=0)
BEGIN
SET @name=’原架构名.’+@name
EXEC SP_ChangeObjectOwner @name, ‘新架构名’
fetch next from csr1 into @name
END
CLOSE csr1
DEALLOCATE csr1

方法二:使用系统存储过程sp_MSforeachtable
EXEC sp_MSforeachtable @command1=”EXEC SP_ChangeObjectOwner ‘?’,’新架构名'”
—————————————————————————————————————-
在sql server 2005数据库中更改数据架构
在数据库testDB中存在架构A及用户A,现将testDB数据库所属的用户由A改为B,同时删除用户A;架构也由A改为B,删除架构A,操作如下:
1、创建用户B,再创建架构B;
2、将架构A的权限赋给用户B,取消用户A拥有架构A的权限,删除用户A;
3、将数据库的所有属于架构A的对象改为架构B,代码如下:
ALTER SCHEMA [新架构名] TRANSFER 旧架构名.[数据库中的对象表或视图或存储过程]
ALTER SCHEMA [B] TRANSFER A.[对象1]
ALTER SCHEMA [B] TRANSFER A.[对象2]
ALTER SCHEMA [B] TRANSFER A.[对象n]

两个ecshop的同步,相同goods_sn的价格保持一致

假设 A库 同步到 B 库,就是说A库发生变化了,B库就要和A保持一致,相同goods_sn的商品价格也变化到和A相同,那么A 为sorce_table,B 为 target_table  ,相应的SQL为 :

update b.ecs_goods target_table  ,a.ecs_goods source_table set target_table.shop_price = source_table.shop_price where source_table.goods_sn = target_table.goods_sn;

mysql 重置密码的正确方法

net stop mysql 先停下当前mysql服务 
c:\mysql-init.txt 内容如下
UPDATE mysql.user SET Password=PASSWORD('phpsir') WHERE User='root';
FLUSH PRIVILEGES;

然后 启动 mysql

C:\> "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld.exe"  --defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.5\\my.ini" --init-file=C:\\mysql-init.txt  --console

参考 : http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html

mysql innodb表捞数据的几点步骤和注意事项

参考文献
http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:start

  1. 确保 ibdata1 和 ib_logfile0 ib_logfile1 是原来的
  2. 确保你的数据库文件夹不要换名
  3. 重启动你的mysql看是否可以正常启动,然后看是否可以导出数据
  4. 在任意库里面,建立监控表,可以看到 ibdata1 里面的所有的库名字和表名字以及表的索引和数据在ibdata1 里面的位置CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;
  5. 根据最上面那个文件用page-parser 和 content-parser 来从ibdata1里面捞取数据

mysql 数据库索引问题,杰奇 cms 的jieqi_article_article表

“SELECT * FROM jieqi_article_article WHERE display=0 AND size>0 AND lastvisit>=1343750400 ORDER BY monthvisit DESC LIMIT 0, 30”

上面的sql 需要对jieqi_article_article 增加索引 index_display_monvisit  在 display 和 monthvisit

方法 alter table jieqi_article_article add INDEX index_display_monvisit(`display`,`monthvisit`);

总结,形式如 select * from table where a = N1 and b = N2 order by c  ;

在 a 和 c 做复合索引,可以消除filesort

 

 

mysql 1067 错误解决的方法和思路

问题描述: windows 2003 平台 mysql 5 的 服务无法启动,显示 1067 错

问题解决过程:

经排查发现 1067错原因在于进程里面还有一个mysqld-nt 没有完全释放,在进程中强制终止后,可以正常启动mysql ,但是远程连接不上,而服务器已经关闭了防火墙等可能屏蔽的因素,一度以为是网络限制了3306端口的访问,可是在外部 nmap 居然可以看到3306端口是open的。在服务器上可以连接 localhost 的mysql 服务器,使用 show processlist; 发下大量的 login 慢请求,显示unauthorized, 判断为mysqld 需要反差ip 的dns name ,再进一步检查发现时服务器的dns server 失效。

问题解决方案:

1) 更换好的names server ip 地址

2) 在my.ini 里面加 [mysqld] 加入 skip-name-resolve 后重启 mysqld ,发下原来无法正常关闭的mysqld-nt.exe 进程也可以完全关闭和正常启动了,问题解决

 

问题总结:

由于是服务器参数以及程序未作调整而出现问题,所以不应盲目判断为服务器内部问题,多分析状态,思路开阔,根据显示内容去查询搜索引擎,可以快速解决问题

 

my.cnf 内 [mysqld] 调整参数备忘

[mysqld]

open_files_limit = 10240
back_log = 600
max_connections = 5000
max_connect_errors = 6000
table_cache = 614
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 300
#thread_concurrency = 8
query_cache_size = 512M
query_cache_limit = 2M
query_cache_min_res_unit = 2k
default-storage-engine = MyISAM
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 246M
max_heap_table_size = 246M
key_buffer_size = 256M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

interactive_timeout = 120
wait_timeout = 120

skip-name-resolve

mysql 经常连接不上 error 10048 10061 错误的解决办法 ,微软补丁 KB967723 造成

原因: 微软补丁 KB967723 造成

打开注册表编辑器regedit

TcpTimedWaitDelay 设置:
找到 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\ Services\TCPIP\Parameters 注册表子键
并创建名为 TcpTimedWaitDelay 的新 REG_DWORD 值
设置此值为十进制 30, 十六进制为 0×0000001e
该值等待时间将是 30 秒。
本项的默认值:0xF0(16进制), 等待时间设置为 240 秒

MaxUserPort 设置(增加最大值端口连接):
找到 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\ Services\TCPIP\Parameters 注册表子键
并创建名为 MaxUserPort 的新 REG_DWORD 值
设置此值为十进制 65534
本项的默认值:5000(十进制)

关闭注册表编辑器, 重启windows系统。

mysql 4.0 升级到 mysql 5.0 的 discuz bbs 系统解决方案

一个discuz 运行于mysql 4 上面 现在需要把数据库升级到 mysql 5
经验证以下方式可行,且无错

1) 用mysql5的bin\mysqldump 把 mysql4的数据导出,

分别以结构形式和数据形式导出成2分文件
d:\mysql5\bin\mysqldump   -uroot -p   DBNAME -d -P3306   > d:\phpsir\jokcnbbs.sql
d:\mysql5\bin\mysqldump   -uroot -p   DBNAME  -t -P3306  –default-character-set=latin1       > d:\phpsir\jokcnbbs-data.sql

2) 用mysql5的bin\mysql 以latin1 导入,注意, create database 要用latin1
d:\mysql5\bin\mysql -P3307 -uroot -p    -e “drop database DBNAME;”
d:\mysql5\bin\mysql -P3307 -uroot -p    -e “create database `DBNAME` DEFAULT CHARACTER SET latin1 collate latin1_general_ci;”
d:\mysql5\bin\mysql -P3307 -uroot -p -D DBNAME  -f  –default-character-set=latin1 < d:\phpsir\jokcnbbs.sql
d:\mysql5\bin\mysql -P3307 -uroot -p -D DBNAME -f  –default-character-set=latin1  < d:\phpsir\jokcnbbs-data.sql