背景: 程序异常中断TCPProvider, error: 0 - An existing connection was forcibly closed by the remotehost.,手工执行查询没有问题,不过执行时间要几十秒。Trace程序的执行,发现要数十分钟,经了解,原因在于程序使用了SqlDataReader读取数据,并且在
背景:
程序异常中断“TCPProvider, error: 0 - An existing connection was forcibly closed by the remotehost.”,手工执行查询没有问题,不过执行时间要几十秒。Trace程序的执行,发现要数十分钟,经了解,原因在于程序使用了SqlDataReader读取数据,并且在读取的过程中进行了一系列数据处理,导致整个完成过程时间变得很长。由此怀疑是网络不稳定之类的因素导致处理过程中连接中断,协调之后改为DataTable做一次性数据加载,程序调整之后,出现故障的频繁大大降低,但无法完全杜绝。
进一步检查,发现在SQLServer日志中,有如下错误:
Error: 7884, Severity:20, State: 1. (Params:). The error is printed in terse mode because there waserror during formatting. Tracing, ETW, notifications etc are skipped.
这个错误与sys.messages中的标准描述不一样,在网上搜索没有找到合适的解决方案。
排查:
把查询的表专门导到一台服务器做测试,最终发现与一个nvarchar(max)列有关,随后想起在不久前,因为Logreader出问题,把出问题的数据列从ntext改为nvarchar(max),于是专门测试这个修改,最终确定问题是这个修改导致。
(text/ntext列可能会导致logreaderagent出问题,这个遇到多次了,但没有明确的重现故障的方法,所以不讨论这个)
故障重现:
使用下面的T-SQL创建测试表,数据,并修改ntext列为nvarchar(max)
-- ====================================== ================ -- 创建测试表 -- ====================================================== USE tempdb; GO IF OBJECT_ID(N'dbo.tb_test', 'U') IS NOT NULL DROPTABLE dbo.tb_test; GO CREATE TABLEdbo.tb_test( id intIDENTITY PRIMARYKEY, Code varchar(50), Datedatetime, Value ntext ); GO INSERT dbo.tb_test SELECT TOP(10000) Code =RIGHT(10000000000 +ABS(CHECKSUM(NEWID())) % (1000 * 2 ), 20), Date= DATEADD(DAY, CHECKSUM(NEWID()) % 100 , GETDATE() ), Value =CONVERT(char(36), NEWID() ) FROM sys.all_columns A WITH(NOLOCK) ,sys.all_columns B WITH(NOLOCK) ; GO -- ====================================================== -- 修改 ntext 字段类型为 nvarchar(max) -- ====================================================== ALTER TABLEdbo.tb_test ALTERCOLUMN Value nvarchar(max); GO 编写程序,查询数据,并且在查询完成前更改数据,这里用 PowherShell 做测试 # 连接字符串 $ConnectionString = "Data Source=127.0.0.1;InitialCatalog=tempdb;Integrated Security=SSPI" # 打开连接 $SqlCnnectionQuery = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString $SqlCnnectionQuery.Open() # 查询数据 $SqlCommandQuery = New-Object System.Data.SQLClient.SQLCommand $SqlCommandQuery.Connection = $SqlCnnectionQuery $SqlCommandQuery.CommandText = "SELECT * FROM( SELECT value, row_id = ROW_NUMBER()OVER( PARTITION BY Code ORDER BY date DESC) FROM dbo.tb_test WITH(NOLOCK) )DATAWHERE row_id = 1" $SqlReader = $SqlCommandQuery.ExecuteReader() # 修改数据 $SqlCnnectionUpdate = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString $SqlCnnectionUpdate.Open() $SqlCommandUpdate = New-Object System.Data.SQLClient.SQLCommand $SqlCommandUpdate.Connection = $SqlCnnectionUpdate $SqlCommandUpdate.CommandText = "UPDATE top(1000) dbo.tb_test SET Value =CONVERT(char(36), NEWID() ) WHERE id IN( SELECT TOP 1000 id FROM dbo.tb_testORDER BY id DESC )" $UpdateRows = $SqlCommandUpdate.ExecuteNonQuery() $SqlCnnectionUpdate.Close() "Update $UpdateRows rows." #读取查询数据 #读取过程中会出现错误 # (SQL 2008 R2 SP2): TCP Provider, error: 0 - Anexisting connection was forcibly closed by the remote host. # (SQL 2008 R2 SP3): TCP Provider, error: 0 - The specifiednetwork name is no longer available. At line:1 char:22 "Read query data...." $ReadRows=0 While($SqlReader.Read()) {$ReadRows+=1} "Read $ReadRows rows." # 关闭连接 $SqlReader.Close() $SqlCnnectionQuery.Close()
故障处理:
对于修改了数据类型的列,将数据重新更新就可以解决 问题(UPDATE 表 SET 修改的列=修改的列)
这个问题测试了 SQL Server2008 到 2014,都存在问题。其中 SQL Server 2008 R2 SP3中测试,SQL Server日志中的错误信息有所不同,是:
Error: 7886, Severity: 20, State: 2.
A read operation on a large object failedwhile sending data to the client. A common cause for this is if the applicationis running in READ UNCOMMITTED isolation level. This connection will beterminated.

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.

To monitor the health and performance of MySQL servers, you should pay attention to system health, performance metrics and query execution. 1) Monitor system health: Use top, htop or SHOWGLOBALSTATUS commands to view CPU, memory, disk I/O and network activities. 2) Track performance indicators: monitor key indicators such as query number per second, average query time and cache hit rate. 3) Ensure query execution optimization: Enable slow query logs, record and optimize queries whose execution time exceeds the set threshold.

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

Dreamweaver Mac version
Visual web development tools
