Tag Archives: database

Solve the problem that dbvisualizer cannot connect to mysql8

reference resources: https://blog.csdn.net/yeshang_ lady/article/details/108224358

Reason: due to the change of MySQL driver, we need to change the MySQL driver of dbvisualizer.

resolvent:

Step 1: download the MySQL 8 driver. I didn’t find the address of the blog post above. I used Maven to download it. Just build a maven project. Enter the address of mysql8 in pom.xml

<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

Then, go to Maven reference to find the package address.

Step 2: put the jar package into the MySQL driver directory of dbvisualizer

Step 3: reconfigure the MySQL driver. As shown in the figure below, delete the original MySQL driver configuration and replace it with a new jar directory

 
Step 4: create a connection:

 
Special note: in the database column, you should not only input the database, but also include the time zone

test?serverTimezone=UTC&characterEncoding=utf-8&useSSL=true

 

The solution of duplicate entry ‘for key’ primary ‘when inserting data in MySQL

The problem is the key duplication encountered in inserting data table.
solution:
1. Using ignore, if there are duplicate values in the inserted record, the record row with duplicate values will be ignored, and the insertion of other rows will not be affected.

INSERT IGNORE INTO Table_name(…..) VALUES(1,1),(2,2),(3,3);

2. Use replace to delete the duplicate record row in the table before inserting when the inserted record encounters primary key or unique duplicate

REPLACE INTO Table_name() VALUES(1,1),(2,2),(3,3)

3. Using values after on duplicate key update refers to the value of the inserted record, while not using values refers to the value of the table itself. The record of the subsequent update is the ID of the duplicate primary key or unique key where.

NSERT TO Table_name() VALUES(1,1),(1,2) ON DUPLICATE KEY UPDATE NAME1=NAME1+1;

Download: https://blog.csdn.net/zhangyr_student/article/details/80119238

Node.js operation mysql error Cannot enqueue Handshake after invoking quit

Error Background:
When the database operation is performed for the first time, it is successful. The second time a database operation is performed, an error is reported as shown in the title.
The reason:
This is because after we close the connection with the.end() method, we need to re-call createConnection to recreate a connection.
Solutions:
For example, the following encapsulates a method that operates on database queries and additions. Each time a new connection is created using mysql.createconnection () before the database operation is performed, instead of putting it directly to the outermost layer.

var mysql  = require('mysql');  
exports.find=function(findSql,callback){
    var connection = mysql.createConnection({     
        host     : 'localhost',       
        user     : 'root',              
        password : '123456', 
        port: '3306',                   
        database: 'namesharing',
    });
    connection.connect();
    connection.query(findSql,function (err, result) {
        if(err){
          console.log('[SELECT ERROR] - ',err.message);
          return;
        }
        connection.end();
        callback(result)
    });
}


exports.insert=function(addSql,addSqlParams,callback){
    var connection = mysql.createConnection({     
        host     : 'localhost',       
        user     : 'root',              
        password : '123456',       
        port: '3306',                   
        database: 'namesharing',
    });
    connection.connect();
    connection.query(addSql,addSqlParams,function (err, result) {
        if(err){
            console.log('[INSERT ERROR] - ',err.message);
            return;
        }
        connection.end();
        // console.log('INSERT ID:',result);  
        callback(result)
    });
}

 

Impdp meets ora-31685

Impdp error:

Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
ORA-31685: Object type  failed due to insufficient privileges. Failing sql is:
ORA-31685: Object type  failed due to insufficient privileges. Failing sql is:
ORA-31685: Object type  failed due to insufficient privileges. Failing sql is:
Job "SYS"."SYS_IMPORT_FULL_01" completed with 9 error(s) at 14:37:45

The reason is that there are materialized views in the exported objects, but the user does not have permission to create materialized views

After the user is authorized, delete the imported data and import it repeatedly to solve the problem

grant create materialized view to user2;

PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE[08004] [1040] Too many connections

preface

The OJ, which was built before, suddenly made an HTTP 500 error this evening. When I opened the log, I found that the MySQL database collapsed.

Text

Then open the MySQL configuration file. The path of the configuration file is as follows

/etc/mysql/mysql.conf.d/mysqld.cnf

Max the number of links in it_ Change the connections to 1000, and then restart mysql.

[reprint and save] MySQL does not set the primary key and uses the self growing ID method

MySQL can only have one auto growth field per table. This auto growth field can be used as a primary key or a non primary key. However, please note that when you use an auto growth field as a non primary key, you must add a unique index to it, otherwise the system will report an error. For example:

1. Set the auto grow field as the primary key.

create table t1 (id int auto_ increment Primary key,sid int);

2. Set the auto grow field to a non primary key. Note that the unique key must be added explicitly.

create table t2 (sid int primary key,id int auto_ increment Unique);

3. Set the auto growth field to a non primary key. If no unique index is added, an error will be reported * *, as shown in the following statement

create table t3 (sid int primary key,id int auto_ increment)。

MySQL is a relational database management system, developed by MySQL AB company of Sweden, which is a product of Oracle at present. MySQL is one of the most popular relational database management systems. In web application, MySQL is the best RDBMS (relational database management system) application software. MySQL is a kind of relational database management system. Relational database stores data in different tables instead of putting all data in a large warehouse, which increases the speed and flexibility. The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software adopts dual authorization policy, which is divided into community version and commercial version. Because of its small size, fast speed, low total cost of ownership, especially the open source, MySQL is generally chosen as the website database for the development of small and medium-sized websites. Because of the excellent performance of its community version, it can form a good development environment with PHP and Apache

come from: https://zhidao.baidu.com/question/621026495899595372.html

SQL Server 2008 18456 error and SA unable to login solution

SQL Server 2008 does not allow remote connection by default, and SA account is disabled by default. If you want to use SSMS to connect to SQL Server 2008 on remote server locally, you need to do two parts of configuration:

SQL Server 2008 does not allow remote connection by default, and SA account is disabled by default. If you want to use SSMS to connect to SQL Server 2008 on remote server locally, you need to do two parts of configuration:

1. SQL Server Management Studio Express (SSMS)

2. SQL Server Configuration Manager/SQL Server Configuration Manager (SSCM)

There are two points to note:

12005’s peripheral application configurator changed place in 2008

2. Some settings take effect and need to restart SQL server

Step 1: open SSMS, connect to database with windows identity, log in, right-click and select “properties”

Step 2: select “security” on the left and “SQL server and windows authentication mode” on the right to enable mixed login mode

Step 3: select “connect”, check “allow remote connection to this server”, and then click “OK”

Step 4: expand “security” & gt; “login” & gt; “Sa”, right-click and select “properties”

Step 5: select “general” on the left and “SQL server authentication” on the right, and set the password

Step 6: select “status”, select “enable”, and click “OK”

Step 7: right click the database and select aspect

Step 8: set “server configuration” and “remoteaccessenabled” property to “true”, and click “OK”

Step 9: so far, SSMS has been set up. Exit first, stop and restart SQL Server service, and then log in with SA. Success means that the SA account has been enabled

 

Step 10: start to configure SSCM

Start SQL Server Configuration Manager in windows menu

Select “SQL Server service” on the left and make sure that “SQL Server” and “SQL server browser” on the right are running (if not, go to “Microsoft SQL Server 2012localdb” and re open SQL configuration manager)

Step 11: the following is the English version of the interface. As shown in the figure: the TCP/IP on the right is “disabled” by default. Double click to open the settings panel and change it to “enabled”

Step 12: select the “IP addresses” tab and set the port “1433”

Step 13: as shown in the figure, change “TCP/IP” of “client protocols” to “enabled”

Step 14: after configuration, restart SQL Server 2008.

At this point should be able to use, but still want to confirm the firewall.

After SQL Server 2005/2008 Express is installed, remote connection is disabled by default. If remote access is required, manual configuration is required.

Turn on firewall settings. take SQLServr.exe (C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\ sqlservr.exe ）Add to the allowed list.

More articles, related information, and so on, WeChat official account: fish science and technology information. Or click the link: http://mp.weixin.qq.com/mp/homepage?__ biz=MzU5MjY1ODg0NA==&hid=3&sn=c8b9f858ce938d785e3f799a8cb59574&scene=18#wechat_ redirect 

The solution of PL / SQL can’t connect to the remote server after installing Oracle locally

Before, there was no Oracle client installed on the computer, just using PL/SQL to access the remote server. During this time, I want to install myself for fun. As a result, after installing Oracle 10g locally, PL/SQL can’t access the original server. Baidu didn’t find a feasible solution for a long time. Later, I thought of Google. It’s really professional. The first result I found solved my problem… I don’t want to talk too much nonsense. I want to talk about my problems and solutions.

 

Prompt when connecting to the server:

Ora-12514: the listener is currently unable to recognize the service requested in the link description

 

I think there is something wrong with the configuration. And then check it on the Internet. The solutions found (I used them and they are feasible) are as follows:

 

Configure listener of Oracle server

Modify the & lt; installation directory & gt;/network/admin/directory listener.ora and tnsnames.ora Two files. among

The listener file is as follows:

# listener.ora Network Configuration File: /oracle/app/product/10.1.0/db_ 1/network/admin/ listener.ora

# Generated by Oracle configuration tools.

SID_ LIST_ LISTENER =

(SID_ LIST =

(SID_ DESC =

(SID_ NAME = PLSExtProc)

(ORACLE_ HOME = /oracle/app/product/10.1.0/db_ 1)

(PROGRAM = extproc)

)

(SID_ DESC =
(GLOBAL_ Dbname = Demo1) # Demo1 is the Oracle instance name
(Oracle)_ HOME = /oracle/app/product/10.1.0/db_ 1)
(SID_ NAME = demo1)
)

)

LISTENER =

(DESCRIPTION_ LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(address = (protocol = TCP) (host = 192.168.0.100) (Port = 1521)) # IP address is Oracle server

)

)

tnsnames.ora The document is amended as follows:

# tnsnames.ora Network Configuration File: /oracle/app/product/10.1.0/db_ 1/network/admin/ tnsnames.ora

# Generated by Oracle configuration tools.

Demo1 =
(description =
(address = (protocol = TCP) (host = 192.168.0.100) (Port = 1521))?The IP address is Oracle server
(connect)_ DATA =
(SERVER = DEDICATED)
(SERVICE_ Name = Demo1) # Demo1 is the Oracle instance name or service name, which can be modified by netmgr or netca
)

)

EXTPROC_ CONNECTION_ DATA =

(DESCRIPTION =

(ADDRESS_ LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

(CONNECT_ DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

The above modifications can also be configured through netmgr and netca.

After modification, restart the listener

After modifying these configurations, you can connect to the server.

 

Feeling: I haven’t really used PL/SQL and Oracle before, but this time I have a problem I felt that the problem of crouching trough was very advanced. At that time, I was a little flustered. My first reaction was to ask others, but I was not willing to. So I went to the Internet to find a solution. Baidu didn’t find a feasible solution for a long time. Later, I asked in the classmate group, and no one knew the solution. Some students asked Google, so I became a living horse doctor and tried it I searched for it, and it turned out…. The result is what you see now ~ some things can’t only look at the surface, and can’t be scared by the problems. When you work hard to solve them, you will find that they are all small cases. Ha ha, it’s not bad. I haven’t solved a problem that makes me feel successful for a long time. come on.

Python uses CX_ Oracle batch insert error report ora-01036 error solution

Recently, in the process of using Python to write data import program, CX is used_ When the Oracle database was imported into Oracle database, there was an error of “ora-01036: illegal variable name/number”. After querying the data and trying, the problem was solved.

The Error statement is:

sql = ‘insert into \”mytable_ a\” values(%s,%s,%s)’

cursor.executemany (sql, data)

As a result, the error “ora-01036: illegal variable name/number” appears.

resolvent:

Change the place holder of parameter transfer to “: 1,: 2,: 3”,

The modified statement is as follows:

sql = ‘insert into \”mytable_ a\” values(:1, :2, :3)’

cursor.executemany (sql, data)

Execute again and solve the problem.

Oracle data file space release

When the data of Oracle database takes up a large space, and the data stored in it does not take up such a large space, the reason may be that the user has deleted some data, but the size of the data file will not automatically shrink. At this time, if you want to reduce the size of the data file, you can use the following methods.

1、 Use the following statement to query data files that can free up space:

select a.file#,

a.name,

a.bytes/1024/1024 CurrentMB,

ceil(HWM * a.block_ size)/1024/1024 ResizeTo,

(a.bytes – HWM * a.block_ size)/1024/1024 ReleaseMB,

‘alter database datafile ”’ || a.name || ”’ resize ‘ ||

ceil(HWM * a.block_ size)/1024/1024 || ‘M;’ ResizeCmd

from v$datafile a,

(SELECT file_ id, MAX(block_ id + blocks – 1) HWM

FROM DBA_ EXTENTS

GROUP BY file_ id) b

where a.file# = b.file_ id(+)

And (a.bytes – HWM * a.block_ size) >0

and rownum < 10

View the data file that belongs to the system table space, and reset it.

2、 Find out the data file that needs to be reset, and execute the reset statement

An error is reported because the reset data file size needs to be set to an integer.

Adjust the size of resize to 16GB, 16384mb;

3、 View free disk space

The size of the system file is reduced to 16GB, the remaining space of the root disk is greatly increased to 19.6gb, and the utilization rate is reduced to 78%.

Oracle stops a job

1. Related tables and views

　dba_ jobs 　　all_ jobs 　　user_ Jobs contains all the job information DBA of the login user_ jobs_ Running contains information about running jobs. Note that you must use the sys user of Oracle to log in to the database to view the DBA_ jobs_ Running, V $process, V $session table. At the same time, when logging in to the operating system, the Oracle user should be used.

2. Problem description

Solve a problem for colleagues, when the network connection is poor, execute a very long time SQL insert operation.

Since the network condition is not good, we choose to use disposable job to complete the insertion operation. After the job was executed for a period of time, I found that there was something wrong with the inserted table (I’m sorry, I didn’t check it first at that time). I’m going to stop job, because when job is running, all my modifications will report the error that system resources are busy.

It is not feasible to forcibly kill session, because the job will be restarted later. If the executed SQL is also killed, the restarted job will be executed again.

3. Solutions

The better way to do it is to do it;

1. First determine the job number to stop

In 10g, it can pass DBA_ Jobs_ Running to confirm.

Find running job:

    select sid from dba_ jobs_ running;

 

Find the SPID of the running job:

    select a.spid from v$process a ,v$session b where a.addr=b.paddr  and b.sid in (select sid from dba_ jobs_ running);

 

2. Break your confirmed job

Pay attention to DBMS_ Job package to identify your job as broken.

    SQL> EXEC DBMS_ JOB.BROKEN (job#,TRUE);

Note: after executing the command, the job you selected is still running.

 

3. Oracle session corresponding to kill

If the job is required to stop immediately, you need to find the corresponding session (SID, serial #) of the job, and then execute the following command:

    ALTER SYSTEM KILL SESSION ‘sid,serial#’;

Or directly kill the session of the corresponding operating system. If you use alter system kill session to execute for a long time, you can use the OS command to quickly kill the session

     For Windows, at the DOS Prompt: orakill sid spid

     For UNIX at the command line> kill –9 spid

 

4. Check if your job is still running

Check whether the job you want to stop is still running. In most cases, it should have stopped. In particular, the execution of the third step of the “killer” command. If it really doesn’t stop, we have to do it again from the first step and the third step.

 

5. Change the number of job queue processes to 0

First, confirm the current number of job queue processes

    SQL> col value for a10

    SQL> select name,value from v$parameter where name =’job_ queue_ processes’;     

Then change the number of job queue processes to 0

    SQL> ALTER SYSTEM SET job_ queue_ processes = 0;

Make sure all jobs stop.

 

6. Modify anything you want to modify, even the content in the job.

 

7. After the modification is completed, stop the broken state of the job.

SQL>EXEC DBMS_ JOB.BROKEN (job#,FALSE):

 

8. Resume job_ queue_ The original value of processes

     ALTER SYSTEM SET job_ queue_ processes = original_ value;

At this point, the entire stop and modify job completed

    

However, it should be noted that when mark is in a broken state, it may take a while for job to execute SQL internally. Therefore, you must consider it carefully when creating a job. At the same time, if possible, you can add some parts to judge the “stop signal” in your PL/SQL code. To perform the above steps.

After all, Oracle is very stubborn when executing jobs

 

4. Annex: usage of orakill

Oracle’s lock table solution “ora-00031: session marked for kill” often encounters a session that is always active, making the CPU always in use. Although it kills, it can’t make the thread end. Kill session is just to kill the process, but the thread is always active. It needs a real kill thread to solve the problem of high CPU utilization. OS: Windows 2003 orakill uses orakill Sid SPID, where sid is the instance name and SPID is the thread number. How to get this SPID?The following is a statement to find SPID. Select SPID, osuser, s.program from V $process P, V $session s where p.addr = s.paddr and s.sid = XXX; — Note: XXX is the SID of session (not database SID). You can input it yourself.
orakill instance_ Name SPID can successfully solve the problem of high CPU utilization in this way