Tag Archives: mysql

Solutions for invalid MySQL service name and install / Remove of the server denied

Why can’t you stop buying 618?From the technical dimension to explore>>>

When configuring wnmp (window + nginx + PHP + MySQL) , MySQL is used for testing. Here, MySQL borrows MySQL from the integrated environment xampp – lazy

1. Under the command-line mode of CMD, enter the directory of MySQL (the integrated environment used here, D:// xampp/MySQL/bin)
start MySQL: Net start MySQL and display “the service name is invalid”, that is, the MySQL service is not registered
register MySQL service: mysqld — install, display “Install/Remove of the Service denied!”

The reasons for these errors are: the current user is not an administrator enter the command line mode as an administrator: (1) right click “start” in the lower left corner of the system desktop and select “command prompt (administrator)”（ 2) Right click the shortcut of cmd.exe to run as an administrator both methods can enter the command line of administrator mode. Then re-enter the MySQL directory, register the MySQL service first, and then start mysql, as shown in the following figure:

To stop the MySQL service, enter the command net stop MySQL:

2. View MySQL version: Enter MySQL – V in command line mode, or enter status in MySQL

mysql connection reports java.math.BigInteger cannot be cast to java.lang.Long exception

The following error occurs when using hibernate

java.sql.SQLException: java.lang.ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.ConnectionImpl.buildCollationMapping(ConnectionImpl.java:1074)
    at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3590)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2531)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2301)
    at com.mysql.jdbc.ConnectionImpl. < init > (ConnectionImpl.java:834)
    at com.mysql.jdbc.JDBC4Connection. < init > (JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:317)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:215)
    at zttc.itat.user.util.DbUtil.getConnection(DbUtil.java:12)
    at zttc.itat.user.util.AbstractDbUnitTestCase.init(AbstractDbUnitTestCase.java:28)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Later, it was solved by replacing mysql-connector-java-5.1.24.jar with mysql-connector-java-5.1.46.jar.

 

[Solved] Error logging into mysql under ARM64 architecture: mysql: error while loading shared libraries: libncurses.so.5: cannot open share…

Error when logging into MySQL under Linux: MySQL: error while loading shared libraries: libncurses. So. 5: cannot open shared object file:

Execute command: MySQL – U root – P

Error: MySQL: error while loading shared libraries: libncurses. So. 5: cannot open shared object file:

Analysis: lack of libncurses. So. 5

Solution:

Normal 64 bit or 32-bit Linux versions can use the following commands:

yum installlibncurses.so.5

But because I use arm64 architecture development board environment, so the command execution will prompt that no file is found

I found a file on the Internet, but also did not find the arm architecture file

Solutions for aarch64 architecture development board:

I am using an EAIDK-610 development board, and I see that the system has the libncurses.so.6.1 file in the /lib64 folder of the board.
So I use a soft connection to create a connection from libncurses.so.6.1 to libncurses.so.5, a shared library that is backward compatible.
Refer to the image example below for details.

Linux: MySQL Start Error: Starting MySQL.Manager of pid-file quit without updating file.[FAILED]

Just after installing mysql, the following error will be reported at startup:

StartingMySQL.Managerofpid-filequitwithoutupdatingfile.[FAILED]

Solve the starting mysql.manager of PID file quit without updating file. [failed]

Try various methods found from Google in turn, and find that you can solve the problem by executing the following MySQL initialization command:

/usr/local/mysql/bin/mysql_install_db--user=mysql

The following is a collection of solutions to this problem found from Google. Maybe the causes of the problems encountered by everyone will be different for reference:

1、 View error log

The error log is the direction light for us to solve the problem. Without the direction light, we have no direction

I had an error in the installation log once, and there was such an error:

/usr/local/mysql/bin/mysqld:errorwhileloadingsharedlibraries:libstdc++.so.5:cannotopensharedobjectfile:Nosuchfileordirectory

Obviously, the library file libstdc + +. So. 5 is missing. At this time, we can search for the package installation related to libstdc

2、 Maybe it’s SELinux

If it is a CentOS system, SELinux will be turned on by default. At this time, you can turn it off first, open/etc/SELinux/config, change SELinux = enforcing to SELinux = disabled, save the disk, exit and restart the machine

3、 Residual data

It may also be the second time that MySQL is installed on the machine, and some residual data affects the startup of the service. Go to the data directory/data of MySQL, and if there is mysql-bin.index, delete it as soon as possible. It is the culprit

4、 Determine the permissions of the data directory

The data directory is generally & lt; prefix>/ Data, check its ownership. If it is not the owner of MySQL, users and groups of MySQL will be created when installing mysql. At this time, users and groups should be modified

chown-Rmysql:mysql/var/data

5、 No data directory specified

When MySQL does not specify a configuration file, it will use the/etc/my.cnf configuration file. Please open this file to see if there is a specified data directory (dataDir) under the [mysqld] section. If not, set this line under [mysqld]:

datadir=/usr/local/mysql/data

6、 And the MySQL process is running

If this is the second time to install MySQL on the machine, it is very likely that this situation will occur. At this time, there is likely to be a MySQL process running on the system. You can execute the following commands to view the data:

ps-ef|grepmysql

If the result is more than one line, kill it as soon as possible

7、 What’s wrong with the skip federated field

You can check the/etc/my.cnf file to see if there are any skip federated fields that have not been commented out. If so, comment them out immediately

8、 The error log directory does not exist

Maybe you don’t know where the MySQL error log is right now?At this time, you can execute the command to view:

cdmysql/bin
./mysql_safe

At this time, an error will be reported, and the error will be displayed in the error, and the error will be written in the file of the directory. Here, check whether the directory and the file are in, and then solve it according to the log instructions. If not, create the directory and modify the permissions and ownership. Generally, it will be/var/lib/MySQL/MySQL. Error. Note that mysql. Error does not need to be created

cd/var/lib
mkdirmysql
chmod777mysql
chownmysql:mysqlmysql

Now try again. If you still can’t, leave a message for you to see

9、 Maybe it’s data/mysql-bin.index

Antecedent: because the hard disk is full, MySQL can’t start. Log in to have a look and find out why. Delete the log file of MySQL, restart mysql, and find an error: starting mysql.manager of PID file quit without updating file. [failed]

There are many explanations for this reason on the Internet, but none of them are what I want to say. The reason I want to say is actually idiotic: data/mysql-bin.index is not deleted. Data/mysql-bin.index is the file where the index of log file is stored. It is obviously impossible to delete the log file without processing the index file of log file

Delete the data/mysql-bin.index file, and then service mysqld start

 

When installing MySQL database on MAC, the system prompts MySQL: command not found

Geeks, please accept the hero post of 2021 Microsoft x Intel hacking contest>>>

After we install mysql, we enter the MySQL command in the terminal and find that we can’t see the database we installed. This is because you don’t configure the environment variables

Install MySQL database in OS system and save it in/usr/local/MySQL by default

So how do we configure environment variables

1. Open terminal, enter: sudo Su enter to enter the system administrator management directory (of course, you need to enter your user password to enter)

2. Enter the command: VIM/etc/profile, and then enter the VIM editing interface

3. In the VIM editing interface, press I to enter the editing state. Then, you can add the path name and enter the command: export path = $path/usr/local/MySQL/bin

4. Press the ESC key to exit the editing state

5. Enter the command: WQ（ Indicates save and force exit)

6. Then I’ll call back to my previous terminal interface

7. Then enter the command: source/etc/profile in this interface to indicate that the settings will take effect immediately. Of course, it’s the same for you to restart after closing

At this time, you can enter: MySQL — version enter to test

What if the system still prompts MySQL: command not found

1. Apple – > System Preferences – > At the bottom, click Mysql to close the MySQL service in the pop-up page

2. Enter the terminal input: CD/usr/local/MySQL/bin

3. After entering, log in the administrator permission sudo Su (enter your computer password)

4. Enter the following command to disable the MySQL verification function./mysqld_ safe –skip-grant-tables & （ Note that mysqld

5. After entering, MySQL will restart automatically (you can see in the preferences that the state of MySQL will change to running)

6. Then enter the command./MySQL

7. After entering, you can see the following effect:

The comfortable MySQL > Prompt

8. Here’s how to change the password

Then enter: flush priorities

After entering, enter: set password for ‘root’ @’localhost ‘= password (‘123’)

9. Re open a terminal and enter: MySQL – uroot – P123 to enter MySQL data:

When mysql creates a table, set timestamp DEFAULT NULL error 1067-Invalid default value for’updated_at’

Problem background:

The structure of the exported database in the mysql server on the online linux server. Want to create a test version locally

After exporting and running on local mysql, it reports error 1067-Invalid default value for

 

The timestamp column needs to be used in the mysql database to store the creation time and update time of the data

For example, to create a background management menu table, only look at the two fields created_at and updated_at

DROP TABLE IF EXISTS `admin_menu`;
CREATE TABLE `admin_menu`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL DEFAULT 0,
  `order` int(11) NOT NULL DEFAULT 0,
  `title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `icon` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `uri` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permission` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` timestamp(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;

An error was found after execution

1067-Invalid default value for’updated_at’. The default value of the updated_at field is invalid. The created_ad field type is the datetime date and time type. The default value is NULL and no error occurs.

 At first I thought it was the sql_mode mode problem, but when the sql_mode mode is strict or loose mode, an error will be reported.
The same mode of local and online servers will not work either
sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

 

Next, check the comparison between the online server version and the local version,
Use select VERSION()
Online mysql version: 5.7.22-log
Local mysql version: 5.7.23-log
There is not much difference between the versions, so version problems are excluded. That can only find information.
Solution:
method 1
Since timestamp will report an error, it can be solved by directly using datetime to save the time.
Change the updated_at type timestamp to datetime, the process of creating a table can run normally
Method 2
Since it cannot be NULL, can I change it to a fixed value?
`updated_at` timestamp(0) DEFAULT '1970-01-01 08:00:00',
Set the time to the date corresponding to the unix timestamp of 0 (the default is 1970-01-01 00:00:00, and the Chinese time must be increased by 8).

 

Method 3

Search online, add configuration parameters in the mysql configuration file

[mysqld] Add under node

explicit_defaults_for_timestamp = ON

Restart the mysql database for trial configuration to take effect.

After that, the above data can be executed normally

 

The following is a specific explanation of method three, it is only for record, and it is not necessary to know

Search information online,
mysql5.6.6Previously, the timestamp time type had a default behavior.
TIMESTAMP columns that did not explicitly declare the NULL attribute defaulted to NOT NULL. (And other data types that do not show a declaration of NOT NULL allow NULL values.)
insert inserts a piece of data with a TIMESTAMP column value of NULL, it will automatically store the current timestamp into this timestamp column when it is stored.
In other words, the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes will be automatically assigned.
Each time the record is updated, the timestamp column will be updated to the time value corresponding to the current timestamp
Now after mysql5.6 version, the default behavior of the timestamp field has changed, and there are some more restrictions.
If the timestamp column is set to the default value of NULL,
Default NULL This will give error 1067-Invalid default value for
If you need to make the timestamp column a NULL value when creating a table, you need to set explicit_defaults_for_timestamp to ON
explicit_defaults_for_timestamp is OFF by default, and it can prevent the default behavior of timestamp when it is turned on.

[Solved] MYSQL Startup Error: server PID file could not be found

[root@centos var]# service mysqld stop

MySQL manager or server PID file could not be found![ FAILED]

Delete superfluous

Notes

#log-bin=mysql-bin

If you kill the process, you still can’t

If the log path is not specified, you can see the error information in the localhost.err file in/data/MySQL

If there is no dead process in the thread, you can first look at the service MySQL status to see the status and delete the superfluous things according to the prompts

Go to/data/MySQL, which is your database directory, and delete the unnecessary items, including localhost. Err and other indexes

If you restart service mysql start several times, you can restart successfully

[Solved] MYSQL Error: “ Every derived table must have its own alias”

Every derived table must have its own alias

This statement means that each derived table must have an alias of its own

This error usually occurs when querying multiple tables.

Because the result of the subquery is used as a derived table for the higher level query when doing nested queries, the result of the subquery must have an alias

Change the MySQL statement to: select count(*) from (select * from ……) as total;

The problem is solved, although only an alias total is added which has no effect, but this alias is necessary

select name1 name, java, jdbc, hibernate,total
from (select sc1.name name1, sc1.mark java
from student_course2 sc1
where sc1.course=’java’)as a,
(select sc2.name name2, sc2.mark jdbc
from student_course2 sc2
where sc2.course=’jdbc’)as b,
(select sc3.name name3, sc3.mark hibernate
from student_course2 sc3
where sc3.course=’hibernate’)as c,
(select sc4.name name4,sum(sc4.mark) total
from student_course2 sc4 group by sc4.name)as d
where name1=name2 and name2=name3 and name3=name4 order by total ASC;

The results are correct:

+———-+——+——+———–+——-+
| name| java | jdbc | hibernate | total |
+———-+——+——+———–+——-+
| wangwu|40 |30 |20 |90 |
| lisi|70 |60 |50 |180 |
| zhangsan |100 |90 |80 |270 |
+———-+——+——+———–+——-+
3 rows in set (0.02 sec)

Mysql import Error Code: 2013 – Lost connection to MySQL server during query

First view Win + R, call out services.msc and open the service dialog box

Search for MySQL services, right-click — > Property to view the path of the INI file of MySQL

It’s easy to find the wrong place without using the service to search the MySQL execution file path. I found a mydefault.ini file last time, and the result was wrong

Search for “Max” under the file_ allowed_ “Packet”, add in the search section:

lower_case_table_names = 1
wait_timeout=2880000
interactive_timeout = 2880000
max_allowed_packet = 50M

If there is a function in the imported SQL file, an error may be reported:

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de….

This function is executed in SQLYOG:

set global log_bin_trust_function_creators=TRUE;