Tag Archives: database

[Solved] SQLite Error: SQLite error near “@table“: syntax error

In my previous post on c# operation SQLite, I found that sometimes the following errors occur when running:

SQLite error near “@table”: syntax error

The error code is as follows:

public static Tuple<bool, DataSet, string> GetBarcode(string barcode)
{
    if (string.IsNullOrEmpty(barcode))
    {
        return new Tuple<bool, DataSet, string>(false, null, "The passed parameter cannot be empty");
    }

    string sql = "SELECT * FROM @table WHERE Barcode = @barcode";
    SQLiteParameter[] parameter = new SQLiteParameter[]
    {
        new SQLiteParameter("table", "Database Table Name"),
        new SQLiteParameter("barcode", barcode)
    };
    return SQLiteHelpers.ExecuteDataSet(sql, parameter);
}

 

Solution:

I found that the database table name cannot be added to the SQLiteParameter parameter list, the other column names are fine, and the SQL statement will not report an error if you change to the string connection method, as follows.

public static Tuple<bool, DataSet, string> GetBarcode(string barcode)
{
    if (string.IsNullOrEmpty(barcode))
    {
        return new Tuple<bool, DataSet, string>(false, null, "The passed parameter cannot be empty");
    }

    string sql = string.Format("SELECT * FROM {0} WHERE Barcode = @barcode", "Database Table Name");
    SQLiteParameter[] parameter = new SQLiteParameter[]
    {
        new SQLiteParameter("barcode", barcode)
    };
    return SQLiteHelpers.ExecuteDataSet(sql, parameter);
}

[Solved] canal Startup Error: error while reading from client socket java.io.IOException: Received error packet:

canal startup error: error while reading from client socket
java.io.IOException: Received error packet: errno = 1236, sqlstate = HY000 errmsg = Could not find first log file name in binary log index file

canal server is canal-deployer

Use tail -f canal_server/logs/example/example.log or vi canal_server/logs/example/example.log

2022-02-18 02:09:30.403 [destination = example , address = /127.0.0.1:3306 , EventParser] ERROR c.a.o.canal.parse.inbound.mysql.dbsync.DirectLogFetcher - I/O error while reading from client socket
java.io.IOException: Received error packet: errno = 1236, sqlstate = HY000 errmsg = Could not find first log file name in binary log index file
        at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102) ~[canal.parse-1.1.5.jar:na]
        at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:238) [canal.parse-1.1.5.jar:na]
        at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$1.run(AbstractEventParser.java:262) [canal.parse-1.1.5.jar:na]
        at java.lang.Thread.run(Thread.java:748) [na:1.8.0_312]
2022-02-18 02:09:30.403 [destination = example , address = /127.0.0.1:3306 , EventParser] ERROR c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - dump address /127.0.0.1:3306 has an error, retrying. caused by
java.io.IOException: Received error packet: errno = 1236, sqlstate = HY000 errmsg = Could not find first log file name in binary log index file
        at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102) ~[canal.parse-1.1.5.jar:na]
        at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:238) ~[canal.parse-1.1.5.jar:na]
        at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$1.run(AbstractEventParser.java:262) ~[canal.parse-1.1.5.jar:na]
        at java.lang.Thread.run(Thread.java:748) [na:1.8.0_312]
2022-02-18 02:09:30.403 [destination = example , address = /127.0.0.1:3306 , EventParser] ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:example[java.io.IOException: Received error packet: errno = 1236, sqlstate = HY000 errmsg = Could not find first log file name in binary log index file
        at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
        at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:238)
        at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$1.run(AbstractEventParser.java:262)
        at java.lang.Thread.run(Thread.java:748)
]

Solution:

There are three files in the instance directory

ubuntu03@ubuntu03:~/canal_server/conf/example$ ls -l
total 140
-rw-rw-r-- 1 ubuntu03 ubuntu03 135168 Feb 18 02:11 h2.mv.db
-rw-rw-r-- 1 ubuntu03 ubuntu03 135168 Feb 18 02:11 meta.dat
-rwxrwxr-x 1 ubuntu03 ubuntu03   2106 Feb 17 11:12 instance.properties

Delete h2.mv.db and meta.dat is OK

rm -rf canal_server/conf/example/h2.mv.db 
rm -rf canal_server/conf/example/meta.dat

restart canal-server(canal-deployer)

sh canal_server/bin/restart.sh

[Solved] Hbase …ERROR: Unable to read .tableinfo from file:/hbaseData/data/default/table1/xxxx

Solution:

1. Make sure that the table is not less nor more, the judgment scheme gives 2 points

(1) the simplest, compare the number and size of files

(2) compare the contents of the file md5 and directory name, and the number of – (in my computer g:/md5 – to their own words)

Copy the original data away

2. use hbase shell to create 1 new table, the table structure should be the same as the one you had problems with before, and the same name

3. Go to the data directory under your new directory and find the .tabledesc

(local is hidden files, hdfs is not)

4. go into this directory will be . tableinfo.xxxx copy out

5. delete the data in your table, copy the data you copied, and put the tableinfo data into .tabledesc.

6. Execute hbase hbck -repair to repair the table, if one repair is not successful, you can execute it several times

[Solved] Mysql Close safe-updates Mode Error: Error Code: 1175

When this problem occurs in MySQL database:

Error Code: 1175. You are using safe update mode and you tried to update a table without a
WHERE  that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL 
Editor and reconnect.

MySQL has entered the safe mode, which is the safe updates mode

Solution:


SHOW VARIABLES like 'sql_safe_updates';  # Query the status of the current safe-updates mode
SET sql_safe_updates=0; # Turn off safe-updates mode

Redis Error: (error) ERR Errors trying to SHUTDOWN. Check logs.

An error is reported when closing redis

(error) ERR Errors trying to SHUTDOWN. Check logs.

First, we need to understand that when we shutdown, redis will save the data, whether it is rdb or aof depends on your own settings. But when you save the file, you may encounter the save path does not exist, or the save path does not have permission, in the configuration file, the default save path for rdb is . /. So we have a problem because of path permissions.

Modify . / file permissions

[atguigu@hadoop100 bin]$ sudo chown atguigu:atguigu -R /usr/local/bin/redis-config/

Shut down again and stop successfully

[Solved] ERROR: child process failed, exited with error number 14 (error number 1, error number 100)

Problem Description:

Error: 14 when starting mongodb, check mongodb.log for MongoDB – Unable to unlink socket file / tmp / mongodb-27017

Cause analysis:

Check /tmp/mongodb-27017.sock, found that the owner is root

Solution:

Delete the /tmp/mongodb-27017.sock file and restart the mongod process. Check the /tmp/mongodb-27017.sock permissions again and change the ownership to the “mongodb” user.

also started successfully

Summary:

1. Check more logs, mongodb.log
2. The mongodb error column, such as 1100, is mostly due to the wrong specification of the data path and log path in the configuration file. The data path needs to be created in advance, which is a directory, and the log path also needs to be created in advance, which is a file

Java database Druid error: com.alibaba.druid.pool.DruidDataSource error

1. Error information

Feb 02, 2022 10:44:44 AM com.alibaba.druid.pool.DruidDataSource error
WARNING: init datasource error, url: jdbc:mysql://localhost:3306/xuesheng?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
java.sql.SQLException: Access denied for user ''@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1570)
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1636)
	at com.alibaba.druid.pool.DruidDataSource.init(DruidDataSource.java:874)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1246)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1242)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:89)
	at com.qzxiaofeng.jdbc.DruidDemo.main(DruidDemo.java:21)

Feb 02, 2022 10:44:44 AM com.alibaba.druid.pool.DruidDataSource error
WARNING: {dataSource-1} init error
java.sql.SQLException: Access denied for user ''@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1570)
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1636)
	at com.alibaba.druid.pool.DruidDataSource.init(DruidDataSource.java:874)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1246)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1242)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:89)
	at com.qzxiaofeng.jdbc.DruidDemo.main(DruidDemo.java:21)

Feb 02, 2022 10:44:44 AM com.alibaba.druid.pool.DruidDataSource info
WARNING: {dataSource-1} inited
Exception in thread "main" java.sql.SQLException: Access denied for user ''@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1570)
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1636)
	at com.alibaba.druid.pool.DruidDataSource.init(DruidDataSource.java:874)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1246)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1242)
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:89)
	at com.qzxiaofeng.jdbc.DruidDemo.main(DruidDemo.java:21)

II My configuration is as follows
1 Test code

public class DruidDemo {
    public static void main(String[] args) throws Exception {

        //3.Load the configuration file
        Properties prop=new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //4. Get the connection pool object
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5. Get the corresponding database connection
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

//        System.out.println(System.getProperty("user.dir"));
    }
}

2. Configuration of guide package in maven

<!--         druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.12</version>
        </dependency>

3.druid.properties configuration

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/xuesheng?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
user=root
password=123456
# initialize the number of connections
initialSize=5
#Maximum number of connections
maxActive=10
#Maximum waiting time
maxWait=3000

3. Solution

Change the user to username in druid.properties

MySQL: Got error 139 from storage engine [How to Solve]

InnoDB restrictions

1. a table cannot contain more than 1000 columns.
2. The maximum internal key length is 3500 bytes, but MySQL itself limits this to 1024 bytes.
3. except for VARCHAR, BLOB and TEXT columns, the maximum row length is slightly less than half of the database page. That is, the maximum row length is about 8000 bytes. longBLOB and longTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB. innoDB stores the first 768 bytes of VARCHAR, BLOB or TEXT columns in rows, and the rest is stored in scattered pages.
4. The default database page size in InnoDB is 16KB.

Solution 1: replace the engine

ALTER TABLE tableName ENGINE = MyISAM;

[Solved] GBase 8a V95 Replace Node Error: single vc mode does not support ‘–freenode’

The v95 version of gbase 8A supports fast replacement of failed nodes through free nodes (omitting the need to find the installation package and check the installation and configuration process). However, free node is only for the scene with VC enabled. For single VC, that is, the mode compatible with V8 version, free node is not supported, so it does not support this node replacement method. The solution is to use a new node.

Error message
version: 9.5.3.22

install prefix: /opt/gbase/10.0.2.103
execute replace node os user: gbase
replaced nodes: ['10.0.2.104']
freenode: ['10.0.2.106']
Error: replace.py(line 1147) -- single vc mode does not support '--freenode'
[gbase@gbase_rh7_003 gcinstall]$

Reason
the cluster is in single VC mode, compatible with V8, and does not support freenode. Look at the bottom of the gcadmin output. There is really no freenode information.

[gbase@gbase_rh7_003 gcinstall]$ gcadmin
CLUSTER STATE:         ACTIVE
VIRTUAL CLUSTER MODE:  NORMAL

====================================
| GBASE GCWARE CLUSTER INFORMATION |
====================================
| NodeName |  IpAddress   | gcware |
------------------------------------
| gcware1  |  10.0.2.103  |  OPEN  |
------------------------------------
====================================================
|      GBASE COORDINATOR CLUSTER INFORMATION       |
====================================================
|   NodeName   | IpAddress  | gcluster | DataState |
----------------------------------------------------
| coordinator1 | 10.0.2.103 |   OPEN   |     0     |
----------------------------------------------------
=========================================================================================================
|                                    GBASE DATA CLUSTER INFORMATION                                     |
=========================================================================================================
| NodeName |                IpAddress                 | DistributionId | gnode | syncserver | DataState |
---------------------------------------------------------------------------------------------------------
|  node1   |                10.0.2.103                |       1        | OPEN  |    OPEN    |     0     |
---------------------------------------------------------------------------------------------------------
|  node2   |                10.0.2.104                |       1        | OPEN  |    OPEN    |     0     |
---------------------------------------------------------------------------------------------------------
|  node3   |                10.0.2.105                |       1        | OPEN  |    OPEN    |     0     |
---------------------------------------------------------------------------------------------------------
|  node4   |                10.0.2.106                |                | OPEN  |    OPEN    |     0     |
---------------------------------------------------------------------------------------------------------

[gbase@gbase_rh7_003 gcinstall]$

Solution
adopt the scheme of new machines and replace nodes on the original IP.

MAC MYSQL Start Error: The server quit without updating PID

1. System configuration information:

System MacOS bigsur 11.6
MySQL 8.0.26

2. Start MySQL service:

sudo /usr/local/mysql/support-files/mysql. server start

The error message is as follows:

Starting MySQL
.. ERROR! The server quit without updating PID file (/usr/local/mysql/data/jinjideleishen.local.pid).

Solution commands
mac default user is _mysql, under linux the default user of mysql is mysql
Use chown to change the owner of the specified file to the specified user or group
-R : Process all files in the specified directory and its subdirectories

sudo chown -R _mysql:_mysql /usr/local/mysql/

Restart successful:

 sudo /usr/local/mysql/support-files/mysql.server restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL
.................................................... SUCCESS!

3. Stop MySQL service

sudo /usr/local/mysql/support-files/mysql.server stop

4. Restart MySQL service

sudo /usr/local/mysql/support-files/mysql.server restart

Python Use sqlalchemy Error: pymssql.ProgrammingError: (102, b“Incorrect syntax near ‘(‘.DB-Lib error

As shown below, use create in Sqlalchemy_ Engine creates a database connection;

conn = create_engine('mssql+pymssql://' + dbuser + ':' + dbpassword + '@' + dbhost + '/' + database)

Then through pandas’ to_SQL method to the database,

df = pd.read_csv(filename, sep='|', header=0, quoting=3)

df.to_sql(name=filename + ty, con=conn, if_exists='replace', index=True)

Result error:

Traceback (most recent call last):
  File "src\pymssql.pyx", line 450, in pymssql.Cursor.execute
  File "src\_mssql.pyx", line 1064, in _mssql.MSSQLConnection.execute_query
  File "src\_mssql.pyx", line 1095, in _mssql.MSSQLConnection.execute_query
  File "src\_mssql.pyx", line 1228, in _mssql.MSSQLConnection.format_and_run_query
  File "src\_mssql.pyx", line 1639, in _mssql.check_cancel_and_raise
  File "src\_mssql.pyx", line 1683, in _mssql.maybe_raise_MSSQLDatabaseException
_mssql.MSSQLDatabaseException: (102, b"Incorrect syntax near '('.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

Supplementary error reporting exception information:

Traceback (most recent call last):
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1264, in _execute_context
    cursor, statement, parameters, context
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\default.py", line 587, in do_executemany
    cursor.executemany(statement, parameters)
  File "src\pymssql.pyx", line 476, in pymssql.Cursor.executemany
  File "src\pymssql.pyx", line 465, in pymssql.Cursor.execute
pymssql.ProgrammingError: (102, b"Incorrect syntax near '('.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\contextlib.py", line 130, in __exit__
    self.gen.throw(type, value, traceback)
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\io\sql.py", line 948, in run_transaction
    yield tx
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\io\sql.py", line 641, in insert
    self._execute_insert(conn, keys, chunk_iter)
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\io\sql.py", line 616, in _execute_insert
    conn.execute(self.insert_statement(), data)
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1020, in execute
    return meth(self, multiparams, params)
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1139, in _execute_clauseelement
    distilled_params,
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1324, in _execute_context
    e, statement, parameters, cursor, context
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1518, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1264, in _execute_context
    cursor, statement, parameters, context
  File "C:\Users\****\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\default.py", line 587, in do_executemany
    cursor.executemany(statement, parameters)
  File "src\pymssql.pyx", line 476, in pymssql.Cursor.executemany
  File "src\pymssql.pyx", line 465, in pymssql.Cursor.execute
sqlalchemy.exc.ProgrammingError: (pymssql.ProgrammingError) (102, b"Incorrect syntax near '('.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

Mainly look at this line, which literally means [syntax error near ‘(‘):

_mssql.MSSQLDatabaseException: (102, b"Incorrect syntax near '('.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

Generally, we will first check whether the SQL is correct (or whether there are ‘special characters’ in the data), but strangely

When this program runs locally, it reads the same file and imports the same database table. Everything is normal!

At this time, it is suspected that the running environment on the local and remote servers is inconsistent, and the inspection found that it was not surprising; The version of Sqlalchemy on the remote is rather old, only 0.23.0

So I decided to upgrade and run again. Everything was normal~