mysql教程

PostgreSQL数据库中的常见错误

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:47 PM

j2eepostgresql常見開資料庫錯誤

J2EE开发各类资源下载清单, 史上最全IT资源，点击进入！我们在操作数据库的时候，我们总会遇到很多错误。下面整理了一下常见的错误。错误1 FATAL: connection limit exceeded for non-superusers 原因：非超级用户的连接数（max_connections - superuser_r

J2EE开发各类资源下载清单, 史上最全IT资源，点击进入！

我们在操作数据库的时候，我们总会遇到很多错误。下面整理了一下常见的错误。

错误1

FATAL: connection limit exceeded for non-superusers

原因：非超级用户的连接数（max_connections - superuser_reserved_connections）超过了设定值

解决办法：增加max_connections设定值，但如果增加了过多的话，数据库负担太大还容易产生内存错误。可以记住pg-pool等工具来辅助解决。

错误2

FATAL: sorry, too many clients already

原因：数据库服务器的连接数超过了max_connections设定值。

解决办法：和错误1解决办法类似。

错误3

LOG: checkpoints are occurring too frequently

原因：checkpoint处理正频繁发生。

解决办法：增加checkpoint_segments的值。

错误4

LOG: archive command failed with exit code (X)

原因：archive_command失败了。

解决办法：因为有可能是硬盘没空间了，所以可以把数据库的log删除一些。

错误5

LOG: number of page slots needed (X) exceeds max_fsm_pages (Y)

原因：max_fsm_pages不足了。

解决办法：增加max_fsm_pages的同时进行VACUUM FULL。

错误6

ERROR: current transaction is aborted, commands ignored until end of transaction blockp
原因：数据库操作时，前一次操作中已经发生过错误了。

解决办法： PostgreSQL包級r：ERROR: current transaction is aborted, commands ignored until end of transaction blockp

错误7

ERROR: operator does not exist: character = integer

原因：PostgreSQL8.3以后，取消了默认类型转换。因此需要使比较的类型保持一致。可以看cast函数。

附错误code

Error Code	Meaning	Condition Name
Class 00 — Successful Completion
`00000`	SUCCESSFUL COMPLETION	successful_completion
Class 01 — Warning
`01000`	WARNING	warning
`0100C`	DYNAMIC RESULT SETS RETURNED	dynamic_result_sets_returned
`01008`	IMPLICIT ZERO BIT PADDING	implicit_zero_bit_padding
`01003`	NULL VALUE ELIMINATED IN SET FUNCTION	null_value_eliminated_in_set_function
`01007`	PRIVILEGE NOT GRANTED	privilege_not_granted
`01006`	PRIVILEGE NOT REVOKED	privilege_not_revoked
`01004`	STRING DATA RIGHT TRUNCATION	string_data_right_truncation
`01P01`	DEPRECATED FEATURE	deprecated_feature
Class 02 — No Data (this is also a warning class per the SQL standard)
`02000`	NO DATA	no_data
`02001`	NO ADDITIONAL DYNAMIC RESULT SETS RETURNED	no_additional_dynamic_result_sets_returned
Class 03 — SQL Statement Not Yet Complete
`03000`	SQL STATEMENT NOT YET COMPLETE	sql_statement_not_yet_complete
Class 08 — Connection Exception
`08000`	CONNECTION EXCEPTION	connection_exception
`08003`	CONNECTION DOES NOT EXIST	connection_does_not_exist
`08006`	CONNECTION FAILURE	connection_failure
`08001`	SQLCLIENT UNABLE TO ESTABLISH SQLCONNECTION	sqlclient_unable_to_establish_sqlconnection
`08004`	SQLSERVER REJECTED ESTABLISHMENT OF SQLCONNECTION	sqlserver_rejected_establishment_of_sqlconnection
`08007`	TRANSACTION RESOLUTION UNKNOWN	transaction_resolution_unknown
`08P01`	PROTOCOL VIOLATION	protocol_violation
Class 09 — Triggered Action Exception
`09000`	TRIGGERED ACTION EXCEPTION	triggered_action_exception
Class 0A — Feature Not Supported
`0A000`	FEATURE NOT SUPPORTED	feature_not_supported
Class 0B — Invalid Transaction Initiation
`0B000`	INVALID TRANSACTION INITIATION	invalid_transaction_initiation
Class 0F — Locator Exception
`0F000`	LOCATOR EXCEPTION	locator_exception
`0F001`	INVALID LOCATOR SPECIFICATION	invalid_locator_specification
Class 0L — Invalid Grantor
`0L000`	INVALID GRANTOR	invalid_grantor
`0LP01`	INVALID GRANT OPERATION	invalid_grant_operation
Class 0P — Invalid Role Specification
`0P000`	INVALID ROLE SPECIFICATION	invalid_role_specification
Class 20 — Case Not Found
`20000`	CASE NOT FOUND	case_not_found
Class 21 — Cardinality Violation
`21000`	CARDINALITY VIOLATION	cardinality_violation
Class 22 — Data Exception
`22000`	DATA EXCEPTION	data_exception
`2202E`	ARRAY SUBSCRIPT ERROR	array_subscript_error
`22021`	CHARACTER NOT IN REPERTOIRE	character_not_in_repertoire
`22008`	DATETIME FIELD OVERFLOW	datetime_field_overflow
`22012`	DIVISION BY ZERO	division_by_zero
`22005`	ERROR IN ASSIGNMENT	error_in_assignment
`2200B`	ESCAPE CHARACTER CONFLICT	escape_character_conflict
`22022`	INDICATOR OVERFLOW	indicator_overflow
`22015`	INTERVAL FIELD OVERFLOW	interval_field_overflow
`2201E`	INVALID ARGUMENT FOR LOGARITHM	invalid_argument_for_logarithm
`22014`	INVALID ARGUMENT FOR NTILE FUNCTION	invalid_argument_for_ntile_function
`22016`	INVALID ARGUMENT FOR NTH_VALUE FUNCTION	invalid_argument_for_nth_value_function
`2201F`	INVALID ARGUMENT FOR POWER FUNCTION	invalid_argument_for_power_function
`2201G`	INVALID ARGUMENT FOR WIDTH BUCKET FUNCTION	invalid_argument_for_width_bucket_function
`22018`	INVALID CHARACTER VALUE FOR CAST	invalid_character_value_for_cast
`22007`	INVALID DATETIME FORMAT	invalid_datetime_format
`22019`	INVALID ESCAPE CHARACTER	invalid_escape_character
`2200D`	INVALID ESCAPE OCTET	invalid_escape_octet
`22025`	INVALID ESCAPE SEQUENCE	invalid_escape_sequence
`22P06`	NONSTANDARD USE OF ESCAPE CHARACTER	nonstandard_use_of_escape_character
`22010`	INVALID INDICATOR PARAMETER VALUE	invalid_indicator_parameter_value
`22023`	INVALID PARAMETER VALUE	invalid_parameter_value
`2201B`	INVALID REGULAR EXPRESSION	invalid_regular_expression
`2201W`	INVALID ROW COUNT IN LIMIT CLAUSE	invalid_row_count_in_limit_clause
`2201X`	INVALID ROW COUNT IN RESULT OFFSET CLAUSE	invalid_row_count_in_result_offset_clause
`22009`	INVALID TIME ZONE DISPLACEMENT VALUE	invalid_time_zone_displacement_value
`2200C`	INVALID USE OF ESCAPE CHARACTER	invalid_use_of_escape_character
`2200G`	MOST SPECIFIC TYPE MISMATCH	most_specific_type_mismatch
`22004`	NULL VALUE NOT ALLOWED	null_value_not_allowed
`22002`	NULL VALUE NO INDICATOR PARAMETER	null_value_no_indicator_parameter
`22003`	NUMERIC VALUE OUT OF RANGE	numeric_value_out_of_range
`22026`	STRING DATA LENGTH MISMATCH	string_data_length_mismatch
`22001`	STRING DATA RIGHT TRUNCATION	string_data_right_truncation
`22011`	SUBSTRING ERROR	substring_error
`22027`	TRIM ERROR	trim_error
`22024`	UNTERMINATED C STRING	unterminated_c_string
`2200F`	ZERO LENGTH CHARACTER STRING	zero_length_character_string
`22P01`	FLOATING POINT EXCEPTION	floating_point_exception
`22P02`	INVALID TEXT REPRESENTATION	invalid_text_representation
`22P03`	INVALID BINARY REPRESENTATION	invalid_binary_representation
`22P04`	BAD COPY FILE FORMAT	bad_copy_file_format
`22P05`	UNTRANSLATABLE CHARACTER	untranslatable_character
`2200L`	NOT AN XML DOCUMENT	not_an_xml_document
`2200M`	INVALID XML DOCUMENT	invalid_xml_document
`2200N`	INVALID XML CONTENT	invalid_xml_content
`2200S`	INVALID XML COMMENT	invalid_xml_comment
`2200T`	INVALID XML PROCESSING INSTRUCTION	invalid_xml_processing_instruction
Class 23 — Integrity Constraint Violation
`23000`	INTEGRITY CONSTRAINT VIOLATION	integrity_constraint_violation
`23001`	RESTRICT VIOLATION	restrict_violation
`23502`	NOT NULL VIOLATION	not_null_violation
`23503`	FOREIGN KEY VIOLATION	foreign_key_violation
`23505`	UNIQUE VIOLATION	unique_violation
`23514`	CHECK VIOLATION	check_violation
Class 24 — Invalid Cursor State
`24000`	INVALID CURSOR STATE	invalid_cursor_state
Class 25 — Invalid Transaction State
`25000`	INVALID TRANSACTION STATE	invalid_transaction_state
`25001`	ACTIVE SQL TRANSACTION	active_sql_transaction
`25002`	BRANCH TRANSACTION ALREADY ACTIVE	branch_transaction_already_active
`25008`	HELD CURSOR REQUIRES SAME ISOLATION LEVEL	held_cursor_requires_same_isolation_level
`25003`	INAPPROPRIATE ACCESS MODE FOR BRANCH TRANSACTION	inappropriate_access_mode_for_branch_transaction
`25004`	INAPPROPRIATE ISOLATION LEVEL FOR BRANCH TRANSACTION	inappropriate_isolation_level_for_branch_transaction
`25005`	NO ACTIVE SQL TRANSACTION FOR BRANCH TRANSACTION	no_active_sql_transaction_for_branch_transaction
`25006`	READ ONLY SQL TRANSACTION	read_only_sql_transaction
`25007`	SCHEMA AND DATA STATEMENT MIXING NOT SUPPORTED	schema_and_data_statement_mixing_not_supported
`25P01`	NO ACTIVE SQL TRANSACTION	no_active_sql_transaction
`25P02`	IN FAILED SQL TRANSACTION	in_failed_sql_transaction
Class 26 — Invalid SQL Statement Name
`26000`	INVALID SQL STATEMENT NAME	invalid_sql_statement_name
Class 27 — Triggered Data Change Violation
`27000`	TRIGGERED DATA CHANGE VIOLATION	triggered_data_change_violation
Class 28 — Invalid Authorization Specification
`28000`	INVALID AUTHORIZATION SPECIFICATION	invalid_authorization_specification
Class 2B — Dependent Privilege Descriptors Still Exist
`2B000`	DEPENDENT PRIVILEGE DESCRIPTORS STILL EXIST	dependent_privilege_descriptors_still_exist
`2BP01`	DEPENDENT OBJECTS STILL EXIST	dependent_objects_still_exist
Class 2D — Invalid Transaction Termination
`2D000`	INVALID TRANSACTION TERMINATION	invalid_transaction_termination
Class 2F — SQL Routine Exception
`2F000`	SQL ROUTINE EXCEPTION	sql_routine_exception
`2F005`	FUNCTION EXECUTED NO RETURN STATEMENT	function_executed_no_return_statement
`2F002`	MODIFYING SQL DATA NOT PERMITTED	modifying_sql_data_not_permitted
`2F003`	PROHIBITED SQL STATEMENT ATTEMPTED	prohibited_sql_statement_attempted
`2F004`	READING SQL DATA NOT PERMITTED	reading_sql_data_not_permitted
Class 34 — Invalid Cursor Name
`34000`	INVALID CURSOR NAME	invalid_cursor_name
Class 38 — External Routine Exception
`38000`	EXTERNAL ROUTINE EXCEPTION	external_routine_exception
`38001`	CONTAINING SQL NOT PERMITTED	containing_sql_not_permitted
`38002`	MODIFYING SQL DATA NOT PERMITTED	modifying_sql_data_not_permitted
`38003`	PROHIBITED SQL STATEMENT ATTEMPTED	prohibited_sql_statement_attempted
`38004`	READING SQL DATA NOT PERMITTED	reading_sql_data_not_permitted
Class 39 — External Routine Invocation Exception
`39000`	EXTERNAL ROUTINE INVOCATION EXCEPTION	external_routine_invocation_exception
`39001`	INVALID SQLSTATE RETURNED	invalid_sqlstate_returned
`39004`	NULL VALUE NOT ALLOWED	null_value_not_allowed
`39P01`	TRIGGER PROTOCOL VIOLATED	trigger_protocol_violated
`39P02`	SRF PROTOCOL VIOLATED	srf_protocol_violated
Class 3B — Savepoint Exception
`3B000`	SAVEPOINT EXCEPTION	savepoint_exception
`3B001`	INVALID SAVEPOINT SPECIFICATION	invalid_savepoint_specification
Class 3D — Invalid Catalog Name
`3D000`	INVALID CATALOG NAME	invalid_catalog_name
Class 3F — Invalid Schema Name
`3F000`	INVALID SCHEMA NAME	invalid_schema_name
Class 40 — Transaction Rollback
`40000`	TRANSACTION ROLLBACK	transaction_rollback
`40002`	TRANSACTION INTEGRITY CONSTRAINT VIOLATION	transaction_integrity_constraint_violation
`40001`	SERIALIZATION FAILURE	serialization_failure
`40003`	STATEMENT COMPLETION UNKNOWN	statement_completion_unknown
`40P01`	DEADLOCK DETECTED	deadlock_detected
Class 42 — Syntax Error or Access Rule Violation
`42000`	SYNTAX ERROR OR ACCESS RULE VIOLATION	syntax_error_or_access_rule_violation
`42601`	SYNTAX ERROR	syntax_error
`42501`	INSUFFICIENT PRIVILEGE	insufficient_privilege
`42846`	CANNOT COERCE	cannot_coerce
`42803`	GROUPING ERROR	grouping_error
`42P20`	WINDOWING ERROR	windowing_error
`42P19`	INVALID RECURSION	invalid_recursion
`42830`	INVALID FOREIGN KEY	invalid_foreign_key
`42602`	INVALID NAME	invalid_name
`42622`	NAME TOO LONG	name_too_long
`42939`	RESERVED NAME	reserved_name
`42804`	DATATYPE MISMATCH	datatype_mismatch
`42P18`	INDETERMINATE DATATYPE	indeterminate_datatype
`42809`	WRONG OBJECT TYPE	wrong_object_type
`42703`	UNDEFINED COLUMN	undefined_column
`42883`	UNDEFINED FUNCTION	undefined_function
`42P01`	UNDEFINED TABLE	undefined_table
`42P02`	UNDEFINED PARAMETER	undefined_parameter
`42704`	UNDEFINED OBJECT	undefined_object
`42701`	DUPLICATE COLUMN	duplicate_column
`42P03`	DUPLICATE CURSOR	duplicate_cursor
`42P04`	DUPLICATE DATABASE	duplicate_database
`42723`	DUPLICATE FUNCTION	duplicate_function
`42P05`	DUPLICATE PREPARED STATEMENT	duplicate_prepared_statement
`42P06`	DUPLICATE SCHEMA	duplicate_schema
`42P07`	DUPLICATE TABLE	duplicate_table
`42712`	DUPLICATE ALIAS	duplicate_alias
`42710`	DUPLICATE OBJECT	duplicate_object
`42702`	AMBIGUOUS COLUMN	ambiguous_column
`42725`	AMBIGUOUS FUNCTION	ambiguous_function
`42P08`	AMBIGUOUS PARAMETER	ambiguous_parameter
`42P09`	AMBIGUOUS ALIAS	ambiguous_alias
`42P10`	INVALID COLUMN REFERENCE	invalid_column_reference
`42611`	INVALID COLUMN DEFINITION	invalid_column_definition
`42P11`	INVALID CURSOR DEFINITION	invalid_cursor_definition
`42P12`	INVALID DATABASE DEFINITION	invalid_database_definition
`42P13`	INVALID FUNCTION DEFINITION	invalid_function_definition
`42P14`	INVALID PREPARED STATEMENT DEFINITION	invalid_prepared_statement_definition
`42P15`	INVALID SCHEMA DEFINITION	invalid_schema_definition
`42P16`	INVALID TABLE DEFINITION	invalid_table_definition
`42P17`	INVALID OBJECT DEFINITION	invalid_object_definition
Class 44 — WITH CHECK OPTION Violation
`44000`	WITH CHECK OPTION VIOLATION	with_check_option_violation
Class 53 — Insufficient Resources
`53000`	INSUFFICIENT RESOURCES	insufficient_resources
`53100`	DISK FULL	disk_full
`53200`	OUT OF MEMORY	out_of_memory
`53300`	TOO MANY CONNECTIONS	too_many_connections
Class 54 — Program Limit Exceeded
`54000`	PROGRAM LIMIT EXCEEDED	program_limit_exceeded
`54001`	STATEMENT TOO COMPLEX	statement_too_complex
`54011`	TOO MANY COLUMNS	too_many_columns
`54023`	TOO MANY ARGUMENTS	too_many_arguments
Class 55 — Object Not In Prerequisite State
`55000`	OBJECT NOT IN PREREQUISITE STATE	object_not_in_prerequisite_state
`55006`	OBJECT IN USE	object_in_use
`55P02`	CANT CHANGE RUNTIME PARAM	cant_change_runtime_param
`55P03`	LOCK NOT AVAILABLE	lock_not_available
Class 57 — Operator Intervention
`57000`	OPERATOR INTERVENTION	operator_intervention
`57014`	QUERY CANCELED	query_canceled
`57P01`	ADMIN SHUTDOWN	admin_shutdown
`57P02`	CRASH SHUTDOWN	crash_shutdown
`57P03`	CANNOT CONNECT NOW	cannot_connect_now
Class 58 — System Error (errors external toPostgreSQL itself)
`58030`	IO ERROR	io_error
`58P01`	UNDEFINED FILE	undefined_file
`58P02`	DUPLICATE FILE	duplicate_file
Class F0 — Configuration File Error
`F0000`	CONFIG FILE ERROR	config_file_error
`F0001`	LOCK FILE EXISTS	lock_file_exists
Class P0 — PL/pgSQL Error
`P0000`	PLPGSQL ERROR	plpgsql_error
`P0001`	RAISE EXCEPTION	raise_exception
`P0002`	NO DATA FOUND	no_data_found
`P0003`	TOO MANY ROWS	too_many_rows
Class XX — Internal Error
`XX000`	INTERNAL ERROR	internal_error
`XX001`	DATA CORRUPTED	data_corrupted
`XX002`	INDEX CORRUPTED	index_corrupted

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

MySQL與Sqlite有何不同？Apr 24, 2025 am 12:12 AM

MySQL和SQLite的主要區別在於設計理念和使用場景：1.MySQL適用於大型應用和企業級解決方案，支持高性能和高並發；2.SQLite適合移動應用和桌面軟件，輕量級且易於嵌入。

MySQL中的索引是什麼？它們如何提高性能？Apr 24, 2025 am 12:09 AM

MySQL中的索引是數據庫表中一列或多列的有序結構，用於加速數據檢索。 1）索引通過減少掃描數據量提升查詢速度。 2）B-Tree索引利用平衡樹結構，適合範圍查詢和排序。 3）創建索引使用CREATEINDEX語句，如CREATEINDEXidx_customer_idONorders(customer_id)。 4）複合索引可優化多列查詢，如CREATEINDEXidx_customer_orderONorders(customer_id,order_date)。 5）使用EXPLAIN分析查詢計劃，避

說明如何使用MySQL中的交易來確保數據一致性。Apr 24, 2025 am 12:09 AM

在MySQL中使用事務可以確保數據一致性。 1)通過STARTTRANSACTION開始事務，執行SQL操作後用COMMIT提交或ROLLBACK回滾。 2)使用SAVEPOINT可以設置保存點，允許部分回滾。 3)性能優化建議包括縮短事務時間、避免大規模查詢和合理使用隔離級別。

在哪些情況下，您可以選擇PostgreSQL而不是MySQL？Apr 24, 2025 am 12:07 AM

選擇PostgreSQL而非MySQL的場景包括：1)需要復雜查詢和高級SQL功能，2)要求嚴格的數據完整性和ACID遵從性，3)需要高級空間功能，4)處理大數據集時需要高性能。 PostgreSQL在這些方面表現出色，適合需要復雜數據處理和高數據完整性的項目。

如何保護MySQL數據庫？Apr 24, 2025 am 12:04 AM

MySQL數據庫的安全可以通過以下措施實現：1.用戶權限管理：通過CREATEUSER和GRANT命令嚴格控制訪問權限。 2.加密傳輸：配置SSL/TLS確保數據傳輸安全。 3.數據庫備份和恢復：使用mysqldump或mysqlpump定期備份數據。 4.高級安全策略：使用防火牆限制訪問，並啟用審計日誌記錄操作。 5.性能優化與最佳實踐：通過索引和查詢優化以及定期維護兼顧安全和性能。

您可以使用哪些工具來監視MySQL性能？Apr 23, 2025 am 12:21 AM

如何有效監控MySQL性能？使用mysqladmin、SHOWGLOBALSTATUS、PerconaMonitoringandManagement(PMM)和MySQLEnterpriseMonitor等工具。 1.使用mysqladmin查看連接數。 2.用SHOWGLOBALSTATUS查看查詢數。 3.PMM提供詳細性能數據和圖形化界面。 4.MySQLEnterpriseMonitor提供豐富的監控功能和報警機制。

MySQL與SQL Server有何不同？Apr 23, 2025 am 12:20 AM

MySQL和SQLServer的区别在于：1)MySQL是开源的，适用于Web和嵌入式系统，2)SQLServer是微软的商业产品，适用于企业级应用。两者在存储引擎、性能优化和应用场景上有显著差异，选择时需考虑项目规模和未来扩展性。

在哪些情況下，您可以選擇SQL Server而不是MySQL？Apr 23, 2025 am 12:20 AM

在需要高可用性、高級安全性和良好集成性的企業級應用場景下，應選擇SQLServer而不是MySQL。 1)SQLServer提供企業級功能，如高可用性和高級安全性。 2)它與微軟生態系統如VisualStudio和PowerBI緊密集成。 3)SQLServer在性能優化方面表現出色，支持內存優化表和列存儲索引。

See all articles