Category Archives: MYSQL

How to Solve MYSQL ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

The salve replication thread stops, and an error 1872 is reported when trying to start slave

mysql> system perror 1872  
MySQL error code 1872 (ER_SLAVE_RLI_INIT_REPOSITORY): Slave failed to initialize relay log info structure from the repository

Solution process
1. It seems that the warehouse of relay log cannot be found, but the location of relay log is set

mysql> show variables like 'relay%';
+---------------------------+---------------------------------------------------+
| Variable_name             | Value                                             |
+---------------------------+---------------------------------------------------+
| relay_log                 | relay-log                                         |
| relay_log_basename        | /datadir/relay-log       |
| relay_log_index           | /datadir/relay-log.index |
| relay_log_info_file       | relay-log.info

2. Log related information is also recorded in master.infor, and no exception is found after viewing
3. Reset the replication information

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
**************************\* 1. row ***************************
               Slave_IO_State: 
                  Master_Host: *******
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4

Note: There is still a copy message

After that, proceed to
set global gtid_purged=’*******’;
change master to …… ;
The error is still reported when starting the slave
4. MySQL DOC description of start slave:
RESET SLAVE does not change any replication connection parameters such as master host, master port, master user, or master password, which are retained in memory. This means that START SLAVE can be issued without requiring a CHANGE MASTER TO statement following RESET SLAVE.

Connection parameters are reset by RESET SLAVE ALL.

5. Use reset slave all to clear all replication information, then reset gtid_purged and master.infor
After start slave, replication is normal

How to Solve Error: ORA-27101: shared memory realm does not exist

Ora-01034 and ora-27101 error messages when I try to connect to the database. It always displays such messages as “ora-01034 – Oracle not available” and “ora-27101 – shared memory real does not exist”
unable to connect to my Oracle database
because there are a lot of problems with firewalls, I’ll take a look this time and find that there is no interception at all. After checking the monitoring, the database instance is also running<It’s a strange thing. It’s still good just now. It won’t work after restarting the machine. After searching the Internet, the problem is solved

the solution is not troublesome…
let’s take a look at Oracle first_ BASE\ORACLE_ If there is this error in the oradim.log file under home/database,
ora-12640: failed to verify adapter initialization,
then the problem is it.
go to Oracle_ BASE\ORACLE_ Find the sqlnet.ora file under home/network/Admin
and put the sqlnet.authentication_ Services = (NTS) is changed to
sqlnet.authentication_ Services = (none)
and then manually restart which database service you have; Control panel – > Management tools – > In the service, then change the startup account of this service to your account… In fact, if you don’t change it, there’s probably no problem. I didn’t change it

after looking at this method, there are probably two reasons for the error: the account has been modified, and the instance has changed

Note: the above contents are quoted

Postscript:

Just today, my 8i database also had errors: “ora-01034 – Oracle not available” and “ora-27101 – shared memory real does not exist”. Then I found the above content on the Internet and solved the problem according to what I said. Very happy

One more thing to add is that my oradim.log file is in the directory orant/database, while the file sqlnet.ora is in the directory orant/network/Admin

Next. The possible cause of this problem in my database is: shut down the database abnormally (shut down directly without shutting down the database)

On this issue, itpub has the following statement: (but I haven’t tested it)

1. Instance not started

sqlplus/nolog
connect/as sysdba
startup

2. Recently, the antivirus software killed Oracle down, and Oracle did not report an error

Just start up

3. Execute the following statement to start the database
sqlplus/nolog
connect/as SYSDBA
startup force

Because sqlplus cannot be executed in CMD, the window execution mode sqlplusw is adopted

Basically use LS

sqlplusw/nolog
connect/as sysdba
startup force

In addition, we also found a record about this problem of an expert (although I didn’t test it, I believe what he said is feasible, and the “abnormal shutdown” mentioned in him is similar to the reason I said above, “abnormal shutdown of the database (direct shutdown without closing the database)”):

Last night, I was busy writing programs until more than 12 o’clock. A friend said there was something wrong with his database, so he used JoinNet to check it. The discovery is ora-27101. First, sort out the operation steps for your reference

1。 First, check the alert.log in Oracle/admin/SID/bdump and find that there is no information about the last database startup. That is to say, the computer may have been shut down abnormally

2。 Enter the CMD mode, sqlplus/nolog, connect/as SYSDBA, execute startup, or 27101 error. It is preliminarily determined that the init.ora file is abnormal, and the init.ora file before copy is overlapped

3。 Enter the CMD mode, sqlplus/nolog, connect/as SYSDBA, and execute startup pfile = C:: (Oracle, admin, Sid, pfile, init. ORA). The startup is successful

mysql Slave_IO_Running:NO [How to Solve]

mysql Slave_IO_Running:NO (How to Solve)

Description：

Slave_IO_Running:NO

Slave_SQL_Running:Yes

Seconds_Behind_Master: NULL

I have encountered the following two cases of Slave_IO_Running:NO.

1. in the configuration of slave synchronization because the slave does not have permission to access the master caused.

2. the mysql-bin.xxxxxxx file on the master was deleted by me by mistake.

For the first case, double-check the database access rights to solve.

For the second case, the following is a little more detailed.

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 192.168.3.21

Master_User: slave

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000016

Read_Master_Log_Pos: 173

Relay_Log_File: mysqld-relay-bin.000008

Relay_Log_Pos: 98

Relay_Master_Log_File: mysql-bin.000016

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table: br> Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 173

Relay_Log_Space: 98

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

1 row in set (0.00 sec)

[root@slave mysql]# tail /var/log/mysqld.log

081223 15:51:50 InnoDB: Started; log sequence number 0 43655

081223 15:51:51 [Warning] Neither –relay-log nor –relay-log-index were used; so replication may break when

this MySQL server acts as a slave and has his hostname changed!! Please use ‘–relay-

log=/var/run/mysqld/mysqld-relay-bin’ to avoid this problem.

081223 15:51:51 [Note] /usr/libexec/mysqld: ready for connections.

Version: ‘5.0.45-log’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3307 Source distribution

081223 15:51:51 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.000016’ at

position 173, relay log ‘/var/run/mysqld/mysqld-relay-bin.000007′ position: 98

081223 15:51:51 [Note] Slave I/O thread: connected to master

[email protected]:3307”>’[email protected]:3307’

, replication started

in log ‘mysql-bin.000016’ at position 173

081223 15:51:51 [ERROR] Error reading packet from server: Could not find first log file name in binary log

index file ( server_errno=1236)

081223 15:51:51 [ERROR] Got fatal error 1236: ‘Could not find first log file name in binary log index file’

from master when reading data from binary log

081223 15:51:51 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000016’, position 173

081223 15:51:58 [Note] Error reading relay log event: slave SQL thread was killed

Solution steps.

Restart the master library: service mysqld restart

mysql> show master status;

+——————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000001 | 98 | | |

+——————+———-+————–+——————+

mysql> slave stop;

mysql> change master to Master_Log_File=’mysql-bin.000001′,Master_Log_Pos=98;

mysql> slave start;

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.3.21

Master_User: slave

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 235

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 98

Relay_Log_Space: 235

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

1 row in set (0.00 sec)

[Solved] SQLSTATE[HY000] [2003] Can’t connect to MySQL serv

 

I’ve really seen what SELinux can do, and I’ve been looking for a long time for the cause of this problem:

It turns out that SELinux doesn’t allow httpd access to the extranet, and yesterday I installed Chrome and it didn’t allow me to run ….. under the root account

SQLSTATE[HY000] [2003] Can’t connect to MySQL server on ‘XXX’ (13)

Solution:

Selinux’s setting Can’t connect to MySQL (13)

Connecting to local database via mysql_connect(‘localhost’,’usr’,’pass’) in php is successful
However, mysql_connect(‘127.0.0.1:3306′,’usr’,’pass’) gives
can’t connect to MySQL server using ” (13) error
Cause.
#getsebool -a | grep httpd
[neo@neo phpMyTest]$ getsebool -a | grep httpd
allow_httpd_anon_write –> off
allow_httpd_mod_auth_ntlm_winbind –> off
allow_httpd_mod_auth_pam –> off
allow_httpd_sys_script_anon_write –> off
httpd_builtin_scripting –> on
httpd_can_check_spam –> off
httpd_can_network_connect –> off
httpd_can_network_connect_cobbler –> off
httpd_can_network_connect_db –> off
httpd_can_network_memcache –> off
httpd_can_network_relay –> off
httpd_can_sendmail –> off
httpd_dbus_avahi –> on
httpd_enable_cgi –> on
httpd_enable_ftp_server –> off
httpd_enable_homedirs –> off
httpd_execmem –> off
httpd_read_user_content –> off
httpd_setrlimit –> off
httpd_ssi_exec –> off
httpd_tmp_exec –> off
httpd_tty_comm –> on
httpd_unified –> off
httpd_use_cifs –> off
httpd_use_gpg –> off
httpd_use_nfs –> off
httpd_can_network_connect –> off
Solution:

#setsebool httpd_can_network_connect 1

————————————————————————————————————————

Linux Error: Can’t connect to MySQL server on ” (10060)

The above error occurred in the remote connection server, after solving the summary, if there are incorrect, please correct me.
There are three main reasons.
1, mysql authorization table does not have the remote machine privileges, and the need to add in the authorization table mysql.user
grant all privileges on *. * to ‘root’@’remote login IP’ identified by ‘remote login password’
flush privileges;

2, the network does not work, this will not be said.
3, firewall to prohibit port 3306, iptable for example
vi /etc/sysconfig/iptables
-A RH-FIREWALL-1-INPUT -m state –state NEW -m tcp -p tcp –dport 3306-j ACCEPT
service iptables restart
ps:iptables settings
1) Take effect after reboot
Enabled: chkconfig iptables on
off: chkconfig iptables off
2) Effective immediately, expire after reboot
Enable: service iptables start
Shutdown: service iptables stop

DB2 Errors and How to Solve them

1.SQL0668N Operation not allowed for reason code “3” on table

“tablename”. SQLSTATE=57016

Solution:

db2 load query table tablename

 

2. SQL3501W The table space(s) in which the table resides will not be placed in

backup pending state since forward recovery is disabled for the database.

Tablestate:

Load Pending This table is pending

Solution:

Run: db2 load from 1.ixf of ixf terminate into swt_his_tran_log

 

3. SQL0668N Operation not allowed for reason code “1” on table

“tablename”. SQLSTATE=57016

Solution: db2 set integrity for tablename immediate checked

 

4. SQL0668N Operation not allowed for reason code “7” on table

“tablename”. SQLSTATE=57016

Solution.

Do the table of reorg ：db2 “reorg table tablename”

 

5. db2 connect to ibank

SQL1015N The database is in an inconsistent state. SQLSTATE=55025

Solution:

db2 restart db ibank

 

6.SQL0290N Table space access is not allowed. SQLSTATE=55039

db2 list tablespaces show detail

Status is 0x0020

Solution.

Online backup tablespace operation

db2 backup database jxcx926 user db2inst1 using db2inst1 tablespace TBS_32K online to /home/db2inst1

 

7.When starting the cluster database, it reports an error that some nodes failed to start

Solution: Modify db2diag log level: db2 update dbm cfg using diaglevel 4 immediate

View the cause of the problem from the detailed log

The original is 3, after modifying it to 4, I can see the detailed information and found that it is a shared memory problem

Release the shared memory: ipclean and restart the database

 

8. db2advis execution failed

Solution.

Execute db2 -tvf EXPLAIN.DDL in /sqllib/misc directory

 

9.When there is an error code about SQLSTATE in the database error report, you want to see the specific information

You can execute db2 ?sqlstate (db2 ?55039) on the database

 

SQLSTATE[01002] Adaptive Server connection failed (severity 9)

PHP uses dblib to connect to sqlserver. An error is reported

SQLSTATE[01002] Adaptive Server connection failed (severity 9)

solution 1:

In/usr/local/freetds/etc/freetds.conf (the specific location may be different), add:

[mssql]
host = your ip
Port = 1433
tds version = 8.0

solution 2:

Remove the version number setting under [global] in/usr/local/freetds/etc/freetds.conf and change it to 8.0

That is to say, it will; TDS version = 4.2 the semicolon in front of the line is removed and changed to:

tds version = 8.0

or

Adding version number in DSN

new PDO(” dblib:version=8.0; host=192.168.0.1:1433; dbname=test”,”test”,”pwd”);

—————————–

If not, try changing the version number to 4.2 or other possible version numbers

Processing method of DB2 SQL error (sqlcode: – 964, sqlstate: 57011)

Description of fault phenomenon:

When executing SQL statement, the following error message appears

Instruction SQL: insert into t_ stat_ file_ Temp sqlstate: 57011, vendor error code: – 964, DB2 SQL error: sqlcode: – 964, sqlstate: 57011, sqlerrmc: null

Fault reason description:

There is not enough storage space in the database heap to process the statement. This error message is the result of running out of space in the transaction log

Troubleshooting:

Method 1: expand the storage space of transaction log

Adjust the number of primary log files and secondary log files by similar statements. For example, allocates eight main log files and allocates up to 100 auxiliary log files< u> Note & lt/ u>: auxiliary log files will be allocated as needed and deleted when is not needed

db2 update db cfg for $DBNAME using logprimary 8
db2 update db cfg for $DBNAME using logsecond 100

Method 2: handle abnormal transaction

If the transaction log storage space is insufficient due to transaction exception, simply expanding the storage space is often difficult to completely solve the problem, so this method needs to provide thorough troubleshooting steps. Note: the following operations are performed after DB2 connect to $dbname connects to the database

2.1 locating abnormal database nodes

Through the following statement, view the log space usage of each node and locate the transaction log space usage (log)_ UTILIZATION_ Percent) is too high

db2 " select DB_NAME, LOG_UTILIZATION_PERCENT, TOTAL_LOG_USED_KB,TOTAL_LOG_AVAILABLE_KB,TOTAL_LOG_USED_TOP_KB, DBPARTITIONNUM 
from SYSIBMADM.LOG_UTILIZATION order by DBPARTITIONNUM "

The execution results are similar as follows.

2.2 check abnormal activity connection

On the node where the transaction log space utilization is too high, execute the following command to locate whether the transaction log usage (UOW) exists_ LOG_ SPACE_ Used) is too high

db2 "select APPLICATION_HANDLE,UOW_LOG_SPACE_USED,UOW_START_TIME 
from TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) 
order by UOW_LOG_SPACE_USED"

If there is an abnormal active connection, it can be terminated by a command similar to the following

db2 "force application (h1 [,h2,..hn])"

H1 [, H2,… HN] stands for application handle identifier

2.3 checking in double transaction

On the node with high utilization rate of transaction log space, execute the following command to locate whether there is an in double transaction in interactive mode

db2 list indoubt transactions with prompting

If there are uncertain transactions, operations such as COMMIT or ROLLBACK can be performed through interactive commands, as described in the WITH PROMPTING mode.

Command parameters WITH PROMPTING Indicates that indoubt transactions are to be processed. If this parameter is specified, an interactive dialog mode is initiated, permitting the user to commit, roll back, or forget indoubt transactions. If this parameter is not specified, indoubt transactions are written to the standard output device, and the interactive dialog mode is not initiated.
Interactive dialog mode permits the user to:

List all indoubt transactions (enter l)

List indoubt transaction number x (enter l, followed by a valid transaction number)

Quit (enter q)

Commit transaction number x (enter c, followed by a valid transaction number)

Roll back transaction number x (enter r, followed by a valid transaction number)

Forget transaction number x (enter f, followed by a valid transaction number).

A blank space must separate the command letter from its argument.

 

DB2 Database table operation error SQL0668N Operation not allowed for reason code “1” on table “db”. S…

Error SQL

Operation not allowed for reason code “1” on table “MARKET.PURE_ USER”.. SQLC

//run sql
select * from PURE_USER

There may be one or more rows that violate the
constraint on the data definition. This table cannot be used for operations. If the subordinate table is in the check pending state, the operation on the parent table that is not in the check pending
State may also receive this error

user response: execute the set integrity
statement with the immediate checked option, and ensure that the data meets all the constraints defined on the table or its subordinate tables

//solution:
set integrity for dbname immediate checked

Any operation on the table is not allowed, and sqlstate = 57016, sqlcode = – 668 is prompted. Error of reason code “7”: sql0668n operation not allowed for reason code “7” on table XXX

//run 
CALL SYSPROC.ADMIN_CMD('reorg table dbname') 　

How to Delete using INNER JOIN with SQL Server?

https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server

You need to specify what table you are deleting from, here is a version with an alias:

DELETE w
FROM WorkRecord2 w
INNER JOIN Employee e
  ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'

SQL Server does not support deleting data from multiple tables at once

https://stackoverflow.com/questions/783726/how-do-i-delete-from-multiple-tables-using-inner-join-in-sql-server

You can take advantage of the “deleted” pseudo table in this example. Something like:

begin transaction;

   declare @deletedIds table ( id int );

   delete t1
   output deleted.id into @deletedIds
   from table1 t1
    join table2 t2
      on t2.id = t1.id
    join table3 t3
      on t3.id = t2.id;

   delete t2
   from table2 t2
    join @deletedIds d
      on d.id = t2.id;

   delete t3
   from table3 t3 ...

commit transaction;

Obviously you can do an ‘output deleted.’ on the second delete as well, if you needed something to join on for the third table.

As a side note, you can also do inserted.* on an insert statement, and both inserted.* and deleted.* on an update statement.

EDIT: Also, have you considered adding a trigger on table1 to delete from table2 + 3? You’ll be inside of an implicit transaction, and will also have the “inserted.” and “deleted.” pseudo-tables available.

[Five Methods] SqlServer_ View SQL server version information

Method 1

Execute SQL statement

SELECT @@VERSION

Method 2

Connect to SQL Server Management Studio and use the main version number information displayed by object explorer to display the current instance product version number 11.0.6020

Method 3

Look at the agent log

Method 4

In SQL Server Management Studio, by executing [sys]. [XP]_ Msver] to get the version number information

[sys].[xp_msver]

Method 5

SELECT   
 SERVERPROPERTY('servername') AS 实例名,  
 SERVERPROPERTY('ProductVersion') AS 实例版本,  
 SERVERPROPERTY('Edition') AS 产品版本,  
 SERVERPROPERTY('ProductLevel') AS 版本级别,  
 @@VERSION AS 版本信息