Tag Archives: The database

Unable to open the physical file “d:\***.mdf”. Operating system error 5: “5(Access is denied.)”.

SQL SERVER2008 is installed under Windows 7. An error occurred trying to attach an existing database MDF file,
Unable to open the physical file “d:\***.mdf”. Operating system error 5: “5(Access is denied.)”.
Is to set the SQL Server related account access to the file
Let Management Studio run as an administrator
 

SQL Server 2008 login 4064 error solution

This error is caused by the account being unable to open the default database
Change the default open database for this account.
If, unfortunately, your SA account and Windows authentication are both open to the same database by default, logging in either way is equally useless
If you have an account that you can log in to, just right click on the security – login username property and change the default database to master.
If you cannot log in either way, modify the default database for the SA by following the command below
If you’re unlucky enough to forget your SA password and you can only log in with Windows authentication, and you don’t happen to know the Windows login name,
Ga can only try to find out what the Windows identity login name is
The combination is probably the computer name \Administrator. Run the following command with CMD to change the default open database for an account, sql2005 version below, and change SQLCMD to ISQL
sqlcmd -E -d”master” -Q”exec sp_defaultdb N’sa’, N’master'”

Reproduced in: https://www.cnblogs.com/Jerseyblog/p/4971227.html

An error 1064 is reported when pymysql accesses the image

An error has been reported while accessing images in the mysql database using Pymysql. The code is as follows
Error message: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘\xff\xd8\xff\xe0\x00\x10JFIF\x00\x01\x01\x00\x00\x01\x00\x01\x00\x00\xff\xdb\x00′ at line 1”)
I found a lot of methods on the Internet, but I couldn’t solve the problem. Later, I tried again and again and finally solved it
Note that the size of the binary BLOB in mysql is smaller than the size of the binary BLOB when designing a table, change the binary data to mediumBLOB
The code is as follows:
import pymysql
from datetime import datetime
To create the database, first connect to the mysql database
Conn = Pymysql.connect (host=’127.0.0.1′, user=’root’, Database =’ smart_apartment_DB ‘,
password=’160507pcsd’, charset=’utf8′)
Create a cursor
cursor = conn.cursor()
The # ExCute () function can execute simple SQL statements
f = open(file=’./img/imageOne.jpg’, mode=’rb’)
dataimg = f.read()
f.close()
nowtime = datetime.now().strftime(“%Y-%m-%d, %H:%M:%S”)
argdata = pymysql.Binary(dataimg)
sqlone = “insert into img_pack(imgtime,imgdata) values(‘%s’,’%s’)” % (
nowtime, argdata)
try:
cursor.execute(sqlone)
conn.commit()
except Exception as e:
conn.rollback()
Print (” Error message: “, e)
Close the cursor first
cursor.close()
Close the database connection again
conn.close()
 
After many attempts, the problem was found
sqlone = “insert into img_pack(imgtime,imgdata) values(‘%s’,’%s’)” % (
Remove the ‘%s’ single quotation mark from nowtime, argdata and change to
sqlone = “insert into img_pack(imgtime,imgdata) values(%s,%s)”
Cursor. Execute (SQlone) changed to CURSOR. Execute (SQlone, (nowtime, argdata))
You can store images in a binary stream like mysql
The correct code is as follows
import pymysql
from datetime import datetime
 
To create the database, first connect to the mysql database
Conn = Pymysql.connect (host=’127.0.0.1′, user=’root’, Database =’ smart_apartment_DB ‘,
password=’160507pcsd’, charset=’utf8′)
Create a cursor
cursor = conn.cursor()
The # ExCute () function can execute simple SQL statements
 
f = open(file=’./img/imageOne.jpg’, mode=’rb’)
dataimg = f.read()
f.close()
nowtime = datetime.now().strftime(“%Y-%m-%d, %H:%M:%S”)
argdata = pymysql.Binary(dataimg)
 
sqlone = “insert into img_pack(imgtime,imgdata) values(%s,%s)”
 
try:
cursor.execute(sqlone, (
nowtime, argdata)
)
conn.commit()
except Exception as e:
conn.rollback()
Print (” Error message: “, e)
 
 
Close the cursor first
cursor.close()
Close the database connection again
conn.close()

【Oracle】 ORA-00257: archiver error. Connect internal only, until freed

The following error occurred recently while operating an Oracle database:

ORA-00257: archiver error. Connect internal only, until freed

The main reason for this error is that there are too many archived logs, which leads to insufficient disk space. It can be solved by deleting logs or increasing space. The specific operation is as follows :(this error is solved by deleting logs here)
1. First log in to Oracle at the terminal using the following command

sqlplus /nolog
connect /as sysdba

2. Execute the following statement and check the use of Flash Recovery Area. Archivelog is already very large, close to 100.

select * from V$FLASH_RECOVERY_AREA_USAGE;

3. Use the following command to query the storage directory of Archivelog and backup it if necessary.

show parameter recover;

4. Delete the log files in the corresponding directory
5. After the log document is deleted, the control file must be maintained using RMAN, otherwise the space will still not be released. The terminal enters RMAN maintenance with the following command:

rman target sys/pass

Check for useless Archivelog:

crosscheck archivelog all;

Delete all Archivelog as of the previous day:

delete archivelog until time 'sysdate-1';

6. Query the use of Flash Recovery Area again, and you will find that a lot of space is released. At this point, the error has been resolved

Mongodb encountered an error: connect econnreused 127.0.0.1:27017

Node with MongoDB is configured to appear when access is completed
Error: reach econnunion 127.0.0.0.1 :27017, return Error
The reason is that your MongoDB database is not open,
The solution under Node can be turned on by Mongod
 
Here are the links I found:
https://cnodejs.org/topic/5646b746c5dcc06702210adf
https://stackoverflow.com/questions/36400233/connect-econnrefused-127-0-0-127017

Reproduced in: https://www.cnblogs.com/herewego/p/9278904.html

com.microsoft.sqlserver . jdbc.SQLServerException : invalid object name ‘XX’

Myeclipse error message:
Error: com. Microsoft. Essentially. JDBC. SQLServerException: object name ‘xx’ is invalid
Warning: SQL Error: 208, SQLState: S0002
 
Error behavior: HQL statements can run in sqlserver, but run in hibernate to report an error.
Solution: The Settings in the.hbm.xml file of the data table mapping are incorrect. The database name catalog=”eportal” should be added, and the schema name schema=” DBO “should also be added.
This is a new feature in sqlserver2005, in mysql can be omitted.

Oracle database file is damaged, Error:ORA-01033 :ORACLE initialization or shutdown in progress

Due to create your own table space, but want to rename, will directly to smash this table space, after the myeclipse deployment project under the service is just an error, pl/SQL or navicat is to open other existing connection, is all the database file is unusable, comprehensive look at a few other people’s blog last problem solved:
Attached to the blog: http://blog.csdn.net/liverliu/article/details/6410287
I’ll just stick to his summary:
First of all, the cause of the problem is that I created a bookspace table space in f:/ LLH/directory before, but I didn’t want the table space later, so I just deleted it. This error is caused by deleting several files such as F :/ LLH /bookspace.dbf by mistake.
Ora-01033: ora-01033: Oracle initialization or shutdown in progress
1. First, run CMD under Windows and enter the DOS environment. 2. Login as DBA user
(1) sqlplus /NOLOG
 
(2) Connect sys/change_on_install as sysdba
Tip: Successful
 
(3) Shutdown Normal
Tip: the database has been closed
has been uninstalled the database
the ORACLE routine has been closed
(4) Startup Mount
Tip: ORACLE routine has been started
Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 82886080 bytes
Database Buffers 33554432 Bytes
Redo Buffers 532480 bytes
database finished loading
(5) the alter database open;
 

error on line 1 :
ora-01157: unable to identify/lock data file 19 – refer to DBWR trace file
ora-01110: data file 19: “” F:/LLH/ bookspace.dbf “; The
tip file section is slightly different for each individual.
Continue to input
(6) Alter Database Datafile 19 offline Drop;

hint: the database has changed.
 
Use the last two steps in the “database has changed” loop until alter Database Open. No more error, “Database has changed”.
 
Up to this point, I will follow the original blogger’s operation. Now I need to close the command line and click computer — > Management – & gt; After the service, find the Oracle-related service and stop it, and then restart it. Oracle services are closed and started in a certain order, specific query can be online.
You can also refer to the following website content for setting, I did not try, roughly the same.
http://www.linuxidc.com/Linux/2016-04/130111.htm
 
 
 

node.js Server MySQL database connection timeout (error: connect etimeout)

Node. js server mysql database connection timeout problem
Sometimes connection timeouts occur while the Node server is connecting to the database. This Error is Error: Connect ETIMEDOUT. The error position in the code is line 421 to 433 of mysql’s Connection.js file:

Connection.prototype._handleConnectTimeout = function() {
  if (this._socket) {
    this._socket.setTimeout(0);
    this._socket.destroy();
  }

  var err = new Error('connect ETIMEDOUT');
  err.errorno = 'ETIMEDOUT';
  err.code = 'ETIMEDOUT';
  err.syscall = 'connect';

  this._handleNetworkError(err);
};

This error is literally a connection timeout, and then my error is due to the database connection problem, when creating the database should carefully compare the parameters of the problem, like me:
when creating the database connection pool:

pool = mysql.createPool({  
        host: "127.0.0.1",
        user: "user",
        password: "",
        database: "nodejs",
        port: 3306,
    });

The header information in the database is:

Source Server         : localhost
Source Server Version : 50624
Source Host           : localhost:3306
Source Database       : nodejs

Target Server Type    : MYSQL
Target Server Version : 50624
File Encoding         : 65001

It can be seen that there is a problem with the connection address of the server, so the connection was successful after changing to localhost

Solution to error 1045 in mysqldump

Error message:
mysqldump: Got error: 1045: Access denied for user ‘root’@’localhost’ (using pas
sword: YES) when trying to connect
Operating environment: CMD
You can log into mysql normally with root-@localhost, but mySQldump denied access, checked the root permission, found that there was no problem with the permission, and finally decided to create a new experiment account

CREATE USER dumper@'localhost' IDENTIFIED BY 'dumper';
GRANT select ON test.* TO dumper@localhost;
GRANT show view ON test.* TO dumper@localhost;
GRANT trigger ON test.* TO dumper@localhost;
GRANT lock tables ON test.* TO dumper@localhost;

Dumper is set to back up the Test library, can log in to the database normally, and still cannot access mySQldump
I began to add parameters to mySQldump gradually, and finally found that the access was successful after adding port -P3307

mysqldump -udumper -pdumper -hlocalhost -P3307 test>test.sql

According to the configuration file, the port of mysql has been modified to 3307, but mySQldump defaults to 3306. After that, the root account can also be normally backed up

In depth analysis of mysq exceeding the number of connections error 1040 (HY000): too many connections

ERROR 1040 (HY000): Too many connections indicates that mysql has more than one connection
The first solution (not recommended) :
Need to wait for a period of time (quite long), let the existing connection timeout automatically released; Or restart mysql (CentOS7: SystemCTL Restart Mysqld.Service)
The second option (use with caution) :
Login to mysql: mysql-uroot-p your root password
Look at the maximum number of connections mysql is currently setting. In general, the default number of connections to mysql is over 100, and the maximum number can be set to 16384 (2 ^ 14)
show variables like ‘%max_connections%’;
Set the maximum number of connections as needed, so I’m going to set 1000 here
set GLOBAL max_connections = 1000;

Note: This is used with caution because once the server or mysql service is restarted, the Settings will not take effect and the default Settings will be restored
 
The third option (the conditions allow the recommended use) :
Add or modify the max_connections parameter in the mysql configuration file
Linux (centos7) environment:
Windows environment:
Find my.ini in the installation directory. If you don’t have it, find My-default.ini, make a copy and rename it my.ini. Add or modify the max_connections parameter
After setting parameters, restart mysql service.
 
Resources:
FAQ for Installing Mysql5.7 and mysql under Linux(Centos7)

MySQL error 1205 (HY000): lock wait timeout exceeded; try restarting transaction



after querying the current thread, it was found that multiple threads were running at the same time…

to view all currently running transactions:

SELECT * FROM information_schema.INNODB_TRX;


you can find three transactions all running at the same time… So you can find the direct cause of the error reported by the shopkeeper here is:
before the stored procedure (which opened the transaction) ran for half a day, MySQL was manually closed without any response… Didn’t make it commit… And the subsequent deletion of data in the same transaction creates a transaction lock…
The fundamental reason is that MySQL USES the Innodb engine by default, and the default parameter of Innodb: innodb_lock_wait_timeout, which sets the maximum time for transactions to wait for acquiring resources (default 50s). Once the maximum time is exceeded, it will report errors such as questions without obtaining resources.
Well, now that the cause of the error has been identified, there are three solutions:
Find the thread of the current transaction, kill the thread; Enlarge this parameter: innodb_lock_wait_TIMEOUT wait time; Optimize stored procedures.
Here the shopkeeper takes the first approach, killing the redundant threads and then checking all the currently running transactions again:

find something strange?The killed thread Number 10 is still working??Its current state is “ROLLING BACK,” which means that the transaction that was killed a while ago has been in a rollback state. So the shopkeeper continued to look up materials and found that such a situation occurred because although the kill command was triggered, it took a long time to terminate the kill logic, and it was also affected by IO resources. Therefore, rollback occurred even though the kill was executed.
Solutions:
One is to wait for it to roll back; Another is to restart the MySQL, but restart MySQL, the transaction will roll back, so in general is actually etc. It can be rolled back end 😂!

waited for about seven or eight minutes before everything was cleared, and all transactions were over:

and then delete again:

Error 945 Database cannot be opened due to inaccessible files or insufficient memory or disk space

I encountered the following error when Connecting db today:
SQL SERVER — FIX: Error 945 Database cannot be opened to capacity files or disk space. See the SQL SERVER Error log for details

The first intuition is the DBF permission issue, a check sure enough…
Due to lack of space
Also moved the DBF file below
and attached the
SQL template as follows:
CREATE DATABASE [ALS_LFE] ON PRIMARY
( NAME = N’DXXXXXXXXXXXXXXXXX’, FILENAME = N’DXXXXXXXXXXXXXXXXX.mdf’ , MAXSIZE = XXXXKB, FILEGROWTH = XXXXKB )
LOG ON
( NAME = N’DXXXXXXXXXXXXXXXXX_log’, FILENAME = N’DXXXXXXXXXXXXXXXXX.ldf’ ,MAXSIZE = UNLIMITED , FILEGROWTH = 10%)
FOR ATTACH
GO[@more@]

From “ITPUB blog” link: http://blog.itpub.net/8337095/viewspace-1033527/, if you want to reprint, please indicate the source, otherwise will be investigated for legal responsibility.

Reproduced in: http://blog.itpub.net/8337095/viewspace-1033527/