Temporarily modify the fields of the database table , I met this problem , To put it bluntly, it’s the watch lock , As a result, it cannot be modified successfully .
SSH Remote to server , Log in to MySQL
mysql -uroot -plalalalThen use the following command to see what you are doing:
show processlist;Find a lot of sleep under the same connection
kill id;Kill a few days later , It’s finally normal .
Creating tables and primary keys
Determining whether a table exists
OBJECT_ID
Determine if the primary key exists
SELECT 1 FROM sys.indexes WHERE name = N’PK_LISA_NoUseWebpartReplacement’ AND object_id = OBJECT_ID(N'[dbo].[LISA_NoUseWebpartReplacement]’)
/*
Run this script on a database with the schema represented by:
WASYGSHA01-1020\SQL2014.Test - This database will be modified. The scripts folder will not be modified.
to synchronize it with a database with the schema represented by:
WASYGSHA01-1020\SQL2014.Test
You are recommended to back up your database before running this script
Script created by SQL Compare version 13.4.5.6953 from Red Gate Software Ltd at 11/13/2018 5:18:41 PM
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[LISA_NoUseWebpartReplacement]'
GO
IF OBJECT_ID(N'[dbo].[LISA_NoUseWebpartReplacement]', 'U') IS NULL
CREATE TABLE [dbo].[LISA_NoUseWebpartReplacement]
(
[Id] [int] NOT NULL,
[Category] [nvarchar] (255) NULL,
[WebpartCode] [nvarchar] (255) NOT NULL,
[WebpartName] [nvarchar] (255) NULL,
[WebpartFile] [nvarchar] (255) NULL,
[Replacement] [nvarchar] (255) NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_LISA_NoUseWebpartReplacement] on [dbo].[LISA_NoUseWebpartReplacement]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'PK_LISA_NoUseWebpartReplacement' AND object_id = OBJECT_ID(N'[dbo].[LISA_NoUseWebpartReplacement]'))
ALTER TABLE [dbo].[LISA_NoUseWebpartReplacement] ADD CONSTRAINT [PK_LISA_NoUseWebpartReplacement] PRIMARY KEY CLUSTERED ([WebpartCode])
GO
IF @@ERROR <&> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
BEGIN
DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
EXECUTE sys.xp_logevent 55000, @eventMessage
END
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
IF @@TRANCOUNT &> 0 ROLLBACK TRANSACTION
PRINT 'The database update failed'
END
GOFirst determine whether the stored procedure exists, if it exists, drop it first, and then create it. If it doesn’t exist, create it directly
/*
Run this script on a database with the schema represented by:
WASYGSHA01-1020\SQL2014.Test - This database will be modified. The scripts folder will not be modified.
to synchronize it with a database with the schema represented by:
WASYGSHA01-1020\SQL2014.Test
You are recommended to back up your database before running this script
Script created by SQL Compare version 13.6.3.8160 from Red Gate Software Ltd at 11/14/2018 11:11:52 AM
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR <&> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[pi_NoUseWebpartReplacement]'
GO
IF OBJECT_ID(N'[dbo].[pi_NoUseWebpartReplacement]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[pi_NoUseWebpartReplacement]
GO
IF @@ERROR <&> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[pi_NoUseWebpartReplacement]'
GO
IF OBJECT_ID(N'[dbo].[pi_NoUseWebpartReplacement]', 'P') IS NULL
EXEC sp_executesql N'-- =============================================
-- Author: Chuck Lu
-- Create date: 2018-11-14
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[pi_NoUseWebpartReplacement]
-- Add the parameters for the stored procedure here
@Id INT ,
@Category NVARCHAR(255) ,
@WebpartCode NVARCHAR(255) ,
@WebpartName NVARCHAR(255) ,
@WebpartFile NVARCHAR(255) ,
@Replacement NVARCHAR(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF EXISTS ( SELECT 1
FROM dbo.LISA_NoUseWebpartReplacement
WHERE WebpartCode = @WebpartCode )
BEGIN
UPDATE dbo.LISA_NoUseWebpartReplacement
SET Category = @Category ,
WebpartName = @WebpartName ,
WebpartFile = @WebpartFile ,
Replacement = @Replacement
WHERE WebpartCode = @WebpartCode;
END;
ELSE
BEGIN
INSERT INTO dbo.LISA_NoUseWebpartReplacement ( Id ,
Category ,
WebpartCode ,
WebpartName ,
WebpartFile ,
Replacement )
VALUES ( @Id , -- Id - int
@Category , -- Category - nvarchar(255)
@WebpartCode , -- WebpartCode - nvarchar(255)
@WebpartName , -- WebpartName - nvarchar(255)
@WebpartFile , -- WebpartFile - nvarchar(255)
@Replacement -- Replacement - nvarchar(255)
);
END;
END;
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
BEGIN
DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
EXECUTE sys.xp_logevent 55000, @eventMessage
END
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
IF @@TRANCOUNT &> 0 ROLLBACK TRANSACTION
PRINT 'The database update failed'
END
GOmysql&> select user,authentication from mysql.user;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql&> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.The solution is as follows.
(1) MySQL version 5.7.6 (previous) users can use the following command.
mysql&> SET PASSWORD = PASSWORD('l!vWT#mL93');
(2) Users of MySQL version 5.7.6 (after the start) can use the following command.
mysql&> ALTER USER USER() IDENTIFIED BY 'l!vWT#mL93';
(1) MySQL version 5.6.6 onwards adds the password_expired feature, which allows setting a user's expiration time. This feature has been added to the mysql.user data table, but its default value is "N", which can be changed using the ALTER USER statement.
Enter the following command to force the account password to expire.
mysql&> ALTER USER 'root'@'localhost' PASSWORD EXPIRE;
At this point, the user can log in to the MYSQL server, but cannot run any commands until the user sets a new password for the account, and will get the above error, and can run all commands within the account permissions normally by changing the password. Since the password expiration days cannot be achieved by command in this version, the DBA can set the password expiration time for MySQL users by cron timer task.
(2) Starting from MySQL version 5.7.4, the feature of password expiration time for users has been improved, and a global variable default_password_lifetime can be used to set the password expiration policy, and this global variable can set a global automatic password expiration policy. A default value can be set in MySQL's my.cnf configuration file.
(a) This will cause all MySQL users to have a password expiration time of 120 days, and MySQL will start counting the time from startup.
The my.cnf configuration is as follows.
[mysqld]
default_password_lifetime=120
(b) If you want to set the password to never expire, my.cnf is configured as follows.
my.cnf is configured as follows.
[mysqld]
default_password_lifetime=0
(3) If you want to set individual specific values for each specific user account, you can use the following command (note: this command will override the global policy) in "days", the command is as follows.
ALTER USER 'root'@'localhost' PASSWORD EXPIRE INTERVAL 250 DAY;
(4) If you want the user to restore the default policy, the command is as follows.
ALTER USER 'root'@'localhost' PASSWORD EXPIRE DEFAULT;
(5) Individual users will disable the password expiration function for late trouble, the command is as follows.
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE NEVER;