Snippets Collections
CREATE TABLE test ( 
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);
# If your logged in to mysql (mysql -u root -p) you can create the database
CREATE DATABASE database_name;

# The user can be created with
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';

# This user also needs access to the database
GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

# To make the new access rights available you have to reload them
FLUSH PRIVILEGES;

# In file /etc/my.cnf or /etc/mysql/my.cnf (depending on your operating system), change
# "sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES" or add following

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

# This should take care of the problem. Another way  to change sql_mode directly would be

mysql -u root -p -e „SET GLOBAL sql_mode = ‚NO_ENGINE_SUBSTITUTION‘;“

# But the later option only seems to set the mode temporarely, so I would advise going
# for the my.cnf file.
# /etc/my.cnf
# Raise max size to 4GB
SET GLOBAL tmp_table_size = 1024 * 1024 * 1024 * 4;
SET GLOBAL max_heap_table_size = 1024 * 1024 * 1024 * 4;
# If you are checking the above variables with
SELECT @@max_heap_table_size;
# Or
SHOW VARIABLES LIKE 'max_heap_table_size"

# If already a memory table, the alter will not change anything.
# Only apply the new max size.
ALTER TABLE table_name ENGINE=MEMORY;
select * from externalactivity ea 
join crmcache cc on cc.personid = ea.RecipientPersonID
join person p on p.personid = ea.RecipientPersonID
where ea.successdate is null and externalid = 13342
SELECT ea.externalresponse, ea.recipientpersonid, ea.externalid, ea.customfield01, cc.crmid, concat (cc.firstname, ' ', cc.lastname) FROM externalactivity ea join crmcache cc on cc.crmid = ea.RecipientPersonID WHERE SuccessDate IS NULL
IFNULL(tppp.preferredname, 'WA') AS 'SOR'
FROM person p
JOIN student_program sp ON sp.personid=p.personid AND sp.statusid=1 AND sp.is_deleted=0
JOIN enrollment e ON e.personid=p.personid
JOIN enrollmenttostudentdebit esd USING (enrollmentid)
JOIN studentdebit sd USING (studentdebitid)
LEFT JOIN studentdebit asd ON asd.adjustsdebitid=sd.studentdebitid AND asd.obsolete=0
LEFT JOIN thirdpartypayerengagement tppe ON tppe.tppengagementid=sd.tppengagementid
LEFT JOIN thirdpartypayer tpp ON tpp.tppayerid=tppe.tppayerid
LEFT JOIN person tppp ON tppp.personid=tpp.tppayerpersonid
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_no_math_la$$
CREATE PROCEDURE sp_no_math_la()

BEGIN

...


END $$
DELIMITER ;

CALL sp_no_math_la();
-- these scripts will delete the tables if it's already there

DROP TABLE IF EXISTS StudentEnrollments;
DROP TABLE IF EXISTS Students;
DROP TABLE IF EXISTS Classrooms;


-- create and populate the students table
CREATE TABLE Students
(
	StudentId INTEGER PRIMARY KEY,
	FirstName VARCHAR(200) NOT NULL,
	LastName VARCHAR(200) NOT NULL,
	Nationality VARCHAR(100) NOT NULL,
	DateOfBirth DATETIME NULL
);


INSERT INTO Students
	(StudentId, FirstName, LastName, Nationality, DateOfBirth)
VALUES
	('1','Mickey', 'Mouse', 'American', '1991-05-02'),
	('2','Donald', 'Duck', 'Japanese', '1992-11-12'),
	('3','Goofy', 'Goof', 'American', '1980-04-15'),
	('4','Daisy', 'Duck', 'French', '1985-02-16'),
	('5','Huey', 'Duck', 'French', '1986-05-19'),
	('6','Scrooge', 'McDuck', 'Japanese', '1983-11-11'),
	('7','Minnie', 'Mouse', 'Canadian', '1983-11-30'),
	('8','Louie', 'Duck', 'French', '1985-09-09');

-- create and populate the classroom table
CREATE TABLE Classrooms
(
	ClassroomId INTEGER PRIMARY KEY,
	ClassName VARCHAR(200) NOT NULL,
	Weight DECIMAL NOT NULL
);

INSERT INTO Classrooms
	(ClassroomId, ClassName, Weight)
VALUES
	(1, 'Public Interaction', 0.10),
	(2, 'Pranks', 0.15),
	(3, 'Running', 0.15),
	(4, 'Acting', 0.30),
	(5, 'Making Jokes', 0.30);

-- create and populate the student enrollment table
CREATE TABLE StudentEnrollments
(
	StudentEnrollmentId INTEGER PRIMARY KEY,
	StudentId INTEGER NOT NULL,
	ClassroomId INTEGER NOT NULL,
	Grade DECIMAL NOT NULL,
	FOREIGN KEY(StudentId) REFERENCES Students(StudentId),
	FOREIGN KEY(ClassroomId) REFERENCES Classrooms(ClassroomId)
);

INSERT INTO StudentEnrollments
	(StudentEnrollmentId, StudentId, ClassroomId, Grade)
VALUES
	(1, 1, 1, 91),
	(2, 1, 2, 68),
	(3, 1, 3, 89),
	(4, 1, 4, 60),
	(5, 1, 5, 65),
	(6, 2, 1, 79),
	(7, 2, 2, 85),
	(8, 2, 3, 68),
	(9, 2, 4, 89),
	(10, 2, 5, 80),
	(11, 3, 1, 96),
	(12, 3, 2, 62),
	(13, 3, 3, 78),
	(14, 3, 4, 100),
	(15, 3, 5, 64),
	(16, 4, 1, 81),
	(17, 4, 2, 90),
	(18, 4, 3, 85),
	(19, 4, 4, 95),
	(20, 4, 5, 64),
	(21, 5, 1, 81),
	(22, 5, 2, 73),
	(23, 5, 3, 60),
	(24, 5, 4, 99),
	(25, 5, 5, 70),
	(26, 6, 1, 75),
	(27, 6, 2, 74),
	(28, 6, 3, 69),
	(29, 6, 4, 79),
	(30, 6, 5, 88),
	(31, 7, 1, 60),
	(32, 7, 2, 75),
	(33, 7, 3, 82),
	(34, 7, 4, 66),
	(35, 7, 5, 65),
	(36, 8, 1, 69),
	(37, 8, 2, 81),
	(38, 8, 3, 100),
	(39, 8, 4, 63),
	(40, 8, 5, 62);
CONCAT(',', section_id, ',') REGEXP ',(val1|val2|val3),'"
UPDATE deltadb.transactions_temp
SET ticker =(CASE CEIL(RAND()*2)
              WHEN 1 THEN '3CN:SP'
              WHEN 2 THEN 'AGV:SP'
          END);
SELECT ticker,`datetime`
FROM (
  SELECT ticker, MAX(`datetime`) `datetime`
  FROM Bloomberg_hist_data
  GROUP BY ticker
) t ORDER BY `datetime`
CURRENT_TIMESTAMP	ON UPDATE CURRENT_TIMESTAMP
CREATE TABLE firends (
	id INTEGER,
  	firstname VARCHAR(64)
);
Db::getInstance()->executeS("DELETE FROM product_images WHERE id_product NOT IN (" . join($allProductIds,',') . ")")
SELECT id, account_id, amount, created_at, null as type
 	FROM subscription_invoice
UNION ALL
SELECT id, account_id, amount, created_at, 
	`type` as type
FROM subscription_payment
ORDER BY created_at DESC
ALTER DATABASE database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
sudo killall mysqld
sudo /Applications/XAMPP/xamppfiles/bin/mysql.server start
[root@jiaoyou mysql]# pwd
/var/lib/mysql
[root@jiaoyou mysql]# ls -ls
338256 -rw-rw---- 1 mysql mysql 346030080 2010-04-22 08:08 ibdata1
626812 -rw-rw---- 1 mysql mysql 641222072 2010-01-26 07:17 mysql-bin.000008
316892 -rw-rw---- 1 mysql mysql 324173772 2010-03-25 12:51 mysql-bin.000009
52724 -rw-rw---- 1 mysql mysql  53931666 2010-04-12 12:13 mysql-bin.000010
10136 -rw-rw---- 1 mysql mysql  10359639 2010-04-22 08:32 mysql-bin.000011

mysql> SHOW BINARY LOGS; 
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000008 | 641222072 | 
| mysql-bin.000009 | 324173772 | 
| mysql-bin.000010 |  53931666 | 
| mysql-bin.000011 |  10360680 | 
+------------------+-----------+


Solution

PURGE BINARY LOGS TO 'mysql-bin.010';
star

Sun Jun 13 2021 04:34:51 GMT+0000 (UTC)

#sql #mysql
star

Sun Jun 13 2021 04:23:51 GMT+0000 (UTC)

#sql #mysql
star

Fri Jun 11 2021 14:23:59 GMT+0000 (UTC)

#database #mysql
star

Mon Jun 07 2021 20:53:33 GMT+0000 (UTC) https://www.neovision.ch/wie-sie-den-strict-modus-von-mysql-deaktivieren-konnen/

#mysql #strict #no-default-value
star

Thu May 27 2021 02:07:15 GMT+0000 (UTC)

#mysql
star

Wed May 26 2021 14:47:11 GMT+0000 (UTC)

#mysql
star

Thu May 20 2021 18:48:25 GMT+0000 (UTC)

#mysql
star

Thu May 13 2021 15:59:59 GMT+0000 (UTC)

#mysql
star

Sun May 02 2021 09:41:55 GMT+0000 (UTC) https://static.skillshare.com/uploads/attachment/1192226128/8c1374ad/Create%20the%20Initial%20Tables%20Script.txt

#sql #mysql
star

Fri Apr 23 2021 03:00:36 GMT+0000 (UTC)

#mysql
star

Wed Apr 07 2021 13:17:21 GMT+0000 (UTC)

#mysql
star

Fri Apr 02 2021 14:04:01 GMT+0000 (UTC)

#mysql
star

Wed Mar 31 2021 04:36:51 GMT+0000 (UTC)

#mysql
star

Wed Mar 31 2021 04:36:19 GMT+0000 (UTC)

#mysql
star

Wed Mar 24 2021 11:01:54 GMT+0000 (UTC)

#mysql
star

Fri Dec 11 2020 11:39:30 GMT+0000 (UTC)

#sql #mysql
star

Tue Oct 27 2020 17:03:36 GMT+0000 (UTC)

#sql #mysql
star

Thu Sep 17 2020 09:02:02 GMT+0000 (UTC) https://stackoverflow.com/questions/21267064/mysql-database-wont-start-in-xampp-manager-osx

#mysql
star

Mon Aug 10 2020 07:06:36 GMT+0000 (UTC) https://serverfault.com/questions/134840/what-are-these-files-can-i-delete-them-manually

#mysql

Save snippets that work with our extensions

Available in the Chrome Web Store Get Firefox Add-on Get VS Code extension