I tried restarting the Mysql server but its not working for me. I am sure the server is running and the permissions on the folder /var/lib/mysql/ are correct (777). When i tried to open the file mysql.sock, its giving me an error saying the file may be corrupt. Please help me to log on to the mysql server.

I have migrated my Database from Windows to Linux server. In many places, query has been written without focusing their cases (uppercase / lowercase). So, these queries are not getting executed and generating errors. As a result, functionalities based on these queries are breaking. Below is the example query to run in Windows and Linux: Select A.id,A.title from tablename a; **This query will work in Windows while never work in Linux.** - Is there any way through which I can avoid these case sensitivity in MySQL queries and no need to check and fix every query ?

I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those files like mysql-bin.000123, mysql-bin.000223 etc. I have checked the mysql replication by doing show master status and show slave status they are using some mysql-bin files at certain positions, but I guess all the other bin files are **leftovers** which will not be used anymore. In this case is it safe to delete all those mysql-bin files except the ones that replication is currently pointing at ? If it is safe to delete, then is there anything I could do to **automatically** delete those files once they are not in use ?

I have 3 MySQL tables: products, orders and users. I want to show all ordered products where not all products are "pre-orders" or "discounts". Note: one user can have multiple orders. I use a subquery to count products with "discount" or "preorder" in the title and compare that with a count of all ordered products. If the result is not the same, I show all products. Working query, but slow ----------------------- This query works just fine. But it's very slow, while the query without the comparison (subquery) was very fast. There are 215303 rows in the orders table and 8406 rows in the products table. SELECT * FROM products p1,orders o1 LEFT JOIN users ON users.id = o1.user_id WHERE o1.barcode = p1.barcode AND (SELECT count(orders.barcode) FROM orders, products WHERE orders.barcode=products.barcode AND order_id=o1.order_id AND (title LIKE('%preorder%') OR title LIKE('%discount%'))) (SELECT count(barcode) FROM orders WHERE order_id=o1.order_id) ORDER BY order_date Sample data ----------- table : users +------+-----------+ | id | name | +------+-----------+ | 1 | User 1 | | 2 | User 2 | | 3 | User 3 | +------+-----------+ table : products p1 +-----------+-----------------------+ | barcode | title | +-----------+-----------------------+ | A | 7 Wonders: Architects | | B | PREORDER - Frosthaven | | C | Discount 10% | | D | Lords of Waterdeep | +-----------+-----------------------+ table : orders o1 +-----------+----------+-----------+--------------+ | order_id | user_id | barcode | order_date | +-----------+----------+-----------+--------------+ | 1 | 1 | A | 2023-02-11 | | 1 | 1 | C | 2023-02-11 | | 2 | 2 | A | 2023-02-07 | | 2 | 2 | B | 2023-02-07 | | 2 | 2 | C | 2023-02-07 | | 3 | 3 | B | 2023-02-10 | | 3 | 3 | C | 2023-02-10 | | 4 | 1 | D | 2023-02-05 | | 5 | 2 | B | 2023-02-04 | +-----------+----------+-----------+--------------+ Desired outcome --------------- I want to show all ordered products where not all products are "pre-orders" or "discounts", orderd by order_date. +-----------+----------+-----------------------+--------------+ | order_id | user_id | title | order_date | +-----------+----------+-----------------------+--------------+ | 5 | 1 | Lords of Waterdeep | 2023-02-05 | | 2 | 2 | 7 Wonders: Architects | 2023-02-07 | | 2 | 2 | PREORDER - Frosthaven | 2023-02-07 | | 2 | 2 | Discount 10% | 2023-02-07 | | 1 | 1 | 7 Wonders: Architects | 2023-02-11 | | 1 | 1 | Discount 10% | 2023-02-11 | +-----------+----------+-----------------------+--------------+

I have a MySQL server running on my work computer, which runs Mac OS X Maverick. I want to access it from home computer, which also has Mac OS Maverick. From terminal, if in 1 tab, I ssh into my work computer and just let the ssh session sit there without being idle, then on another tab, I can access MySQL server. However, without the ssh session running, I will get following error > **ERROR 2003 (HY000):** Can't connect to MySQL server on 'SERVER_IP_ADDRESS' (60) I've tried to modify my.cnf file on the server by commenting out bind-address or assigning it to 0.0.0.0 but it doesn't work. Why is it like that? How can I configure the server so that it allows remote access without having to run ssh on the client? Can someone please help?

In my Database I have a **table**: Employee with recursive association, an employee can be boss of other employee. The Table Description: mysql> DESC Employee; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | SSN | varchar(64) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | designation | varchar(128) | NO | | NULL | | | MSSN | varchar(64) | NO | MUL | NULL | | +-------------+--------------+------+-----+---------+-------+ Present State of Employee Table is: mysql> SELECT * FROM Employee; +-----+------+-------------+------+ | SSN | name | designation | MSSN | +-----+------+-------------+------+ | 1 | A | OWNER | NULL | | 2 | B | BOSS | 1 | | 3 | C | WORKER | 2 | | 4 | D | BOSS | 2 | | 5 | E | WORKER | 4 | | 6 | F | WORKER | 1 | | 7 | G | WORKER | 4 | +-----+------+-------------+------+ 7 rows in set (0.00 sec) Following is hierarchical relation among the rows in table: A / \ B F / \ c D / \ G E **I wanted to impose following constraints on INSERT**: - An employee can't BOSS of himself. Hence query like. INSERT INTO Employee VALUES ("8", "H", "BOSS", "8"); Should be declined. - New new OWNER can be inserted. As I am working with MYSQL version previous than 5.5 (doesn't supports signal). So I am using a **my_signal()** as a stored procedure. Written Like this: > CREATE PROCEDURE my_signal(in_errortext VARCHAR(255)) > BEGIN > SET @sql=CONCAT('UPDATE ', in_errortext, ' SET x=1'); > PREPARE my_signal_stmt FROM @sql; > EXECUTE my_signal_stmt; > DEALLOCATE PREPARE my_signal_stmt; > END// And **to Apply constraints** I written a Trigger as I came to know that check constraints are not yet implemented in MySQL! > DELIMITER $$ > CREATE > TRIGGER employee_before_insert BEFORE INSERT > ON Employee > FOR EACH ROW BEGIN > CASE > WHEN NEW.designation = 'OWNER' THEN > CALL my_signal('Error: can not insert new OWNER !'); > > WHEN NEW.SSN = NEW.MSSN THEN > CALL my_signal('Error: Row can not reference itself!'); > END CASE; > END$$ > DELIMITER ; It was successfully compiled and loaded in database. But when I tried to insert: mysql> INSERT INTO Employee VALUES ("12", "K", "BOSS", "12"); ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger I Learn here and here that - SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them). After some effort I can write an another trigger as below. **Working fine** as per my requirement. > mysql> CREATE > -> TRIGGER employee_before_insert BEFORE INSERT > -> ON Employee > -> FOR EACH ROW BEGIN > -> IF UCASE(NEW.designation) = 'OWNER' THEN /*UCASE*/ > -> UPDATE Error: can not insert new OWNER ! set x=1; > -> END IF; > -> IF (NEW.SSN = NEW.MSSN) THEN > -> UPDATE Error: Row can not reference itself! set x=1; > -> END IF; > -> END$$ > Query OK, 0 rows affected (0.08 sec) > > mysql> DELIMITER ; > mysql> INSERT INTO Employee VALUES ("12", "K", 'owner', "11"); > ERROR 1146 (42S02): Table 'dumy.Error: can not insert new OWNER !' doesn't exist > mysql> INSERT INTO Employee VALUES ("12", "K", 'Ajay', "12"); > ERROR 1146 (42S02): Table 'dumy.Error: Row can not reference itself!' doesn't exist But I am already using my_signal() in many procedures and I need to write many new Stored functions and triggers where I would need my_signal() functions again. **Can someone suggest me other way to write my_signal() through which I can print customized error message?** I tried as follows: > DELIMITER $$ > CREATE PROCEDURE my_signal(in_errortext VARCHAR(255)) > DECLARE sql varchar(512); > BEGIN > SET sql=CONCAT('UPDATE ', in_errortext, ' SET x=1'); > UPDATE sql SET x =1; > END$$ But useless :( . Please help me on this.I will be very thankful! I am not good at MYSQL specially @ Stored Procedures. If you would like to try on your system here you can quickly find commands to build this database.

I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data). Since the databases size is several times smaller than the available memory, how can I make full usage of my server's resources? 1. What settings should I attempt configure in my.cnf? 2. Can I force data + indexes to stay in memory?

How do I create a table with a formula column in MySQL version 5? For example: I have a table named Product, which has 5 columns: id int, amount int, sold_out int, faulty int, remain_amount is (amount-(sold_out+faulty)) I will not use a trigger or a view. I am looking for a solution similar to what, in SQL Server, is called a computed column.

How can we restore mysql database with different name from mysqldump file. I dont't want to open dump file and edit it. Any other better methods?

Structure of my table mysql> show create table t_group_tag_relation\G *************************** 1. row *************************** Table: t_group_tag_relation Create Table: CREATE TABLE t_group_tag_relation ( id int(11) NOT NULL AUTO_INCREMENT, domain_id int(11) NOT NULL, group_tag_id int(11) NOT NULL, resource_id int(11) NOT NULL, resource_type int(11) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY group_tag_id_resource_id (group_tag_id,resource_id), KEY domain_id (domain_id,group_tag_id,resource_type,resource_id), KEY domain_id_resource_type (domain_id,resource_type) ) ENGINE=InnoDB AUTO_INCREMENT=1613462 DEFAULT CHARSET=latin1 1 row in set (0.03 sec) Explain plan of my query mysql> explain select * from t_group_tag_relation WHERE resource_id = 575868070 AND domain_id = 476 AND resource_type = 2; +----+-------------+----------------------+------+-----------------------------------+-----------+---------+-------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------------+------+-----------------------------------+-----------+---------+-------+-------+--------------------------+ | 1 | SIMPLE | t_group_tag_relation | ref | domain_id,domain_id_resource_type | domain_id | 4 | const | 28400 | Using where; Using index | +----+-------------+----------------------+------+-----------------------------------+-----------+---------+-------+-------+--------------------------+ 1 row in set (0.57 sec) It is using indexes already but performance is not good. How to improve performance?

I'm curious is there a way to find my.cnf in shared hosting or is this file can be configured just by DBAs and no one can set his/her own settings?

I am using MySQLi for my webapp but whenever I want to visit some particular page, I get mysqli_connect() [function.mysqli-connect]: (42000/1203): User ***_user already has more than 'max_user_connections' active connections. I tried already to close all connections but this does not improve the situation. Is there a way to know exactly what connections are open at any particular moment or any other useful data that can help me resolve this issue ? BTW, I'm using PHP 5.2.17 and MySQL 5.1.

Suppose you have the following table and data: create table t ( k int, v int, index k(k) ) engine=memory; insert into t (k, v) values (10, 1), (10, 2), (10, 3); When issuing select * from t where k = 10 with no order by clause, how does MySQL sort the records by default?

I've been developing a C# Bus System app using SQL, and I need some advice. I don't know if asking on here is the right thing to do, but I essentially need someone to analyse the .SQL file I have, where I have designed my database schema. Primary questions are really, is this designed in the right way (what is the right way?), are having UUIDs as primary keys for EVERY table necessary? Should it just be for the institution table? etc. I don't know if there is anywhere I can go to pay an hour of their time to analyse it.... The SQL file is 430 lines long so, i've pasted it below, I apologise if this is not the right thing to do on here! Thanks ^.^

-- phpMyAdmin SQL Dump
-- version 5.0.2
-- https://www.phpmyadmin.net/ 
--
-- Host: edu-route-test-db.cqobdbj3xwim.us-east-1.rds.amazonaws.com:3306
-- Generation Time: Feb 28, 2021 at 01:45 PM
-- Server version: 8.0.20
-- PHP Version: 7.3.21

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: EduRouteDB
--

-- --------------------------------------------------------

--
-- Table structure for table addresses
--

DROP TABLE IF EXISTS addresses;
CREATE TABLE IF NOT EXISTS addresses (
  AddressId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  BuildingName varchar(256) NOT NULL,
  StreetName varchar(256) NOT NULL,
  Town varchar(256) NOT NULL,
  County varchar(256) NOT NULL,
  PostCode varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  Country varchar(256) NOT NULL,
  PRIMARY KEY (AddressId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table bookings
--

DROP TABLE IF EXISTS bookings;
CREATE TABLE IF NOT EXISTS bookings (
  BookingId binary(16) NOT NULL,
  StudentId binary(16) NOT NULL,
  StartDate date NOT NULL,
  EndDate date NOT NULL,
  TimeBooked time NOT NULL,
  RouteId binary(16) NOT NULL,
  StopId binary(16) NOT NULL,
  UserId binary(16) NOT NULL,
  InstitutionId binary(16) NOT NULL,
  PRIMARY KEY (BookingId),
  KEY StudentId_Bookings_FK (StudentId),
  KEY RouteId_Bookings_FK (RouteId),
  KEY StopId_Bookings_FK (StopId),
  KEY InstitutionId_Bookings_FK (InstitutionId),
  KEY UserId_Bookings_FK (UserId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table buses
--

DROP TABLE IF EXISTS buses;
CREATE TABLE IF NOT EXISTS buses (
  BusId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  BusName varchar(256) NOT NULL,
  VehicleRegistrationNumber varchar(10) NOT NULL,
  Capacity int NOT NULL,
  InstitutionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (BusId),
  KEY InstitutionId_Buses_FK (InstitutionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table buses_routes
--

DROP TABLE IF EXISTS buses_routes;
CREATE TABLE IF NOT EXISTS buses_routes (
  BusId binary(16) NOT NULL,
  RouteId binary(16) NOT NULL,
  InstitutionId binary(16) NOT NULL,
  KEY BusId_BusesRoutes_FK (BusId),
  KEY RouteId_BusesRoutes_FK (RouteId),
  KEY InstitutionId_BusesRoutes_FK (InstitutionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table drivers
--

DROP TABLE IF EXISTS drivers;
CREATE TABLE IF NOT EXISTS drivers (
  DriverId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  DriverName varchar(256) NOT NULL,
  BusId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  InstitutionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  UserId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (DriverId),
  KEY BusId_Drivers_FK (BusId),
  KEY InstitutionId_Drivers_FK (InstitutionId),
  KEY UserId_Drivers_FK (UserId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table institutions
--

DROP TABLE IF EXISTS institutions;
CREATE TABLE IF NOT EXISTS institutions (
  InstitutionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  InstituteName varchar(256) DEFAULT NULL,
  InstituteLogoFullPath varchar(256) DEFAULT NULL,
  InstituteJoinDate date DEFAULT NULL,
  IsActive tinyint(1) DEFAULT NULL,
  PrincipalName varchar(256) DEFAULT NULL,
  AddressId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (InstitutionId),
  KEY AddressId_FK (AddressId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table parents
--

DROP TABLE IF EXISTS parents;
CREATE TABLE IF NOT EXISTS parents (
  ParentId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  FirstName varchar(256) NOT NULL,
  MiddleName varchar(256) NOT NULL,
  LastName varchar(256) NOT NULL,
  InstitutionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  UserId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (ParentId),
  KEY InstitutionId_Parents_FK (InstitutionId),
  KEY UserId_Parents_FK (UserId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table permissions
--

DROP TABLE IF EXISTS permissions;
CREATE TABLE IF NOT EXISTS permissions (
  PermissionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PermissionCategory varchar(256) NOT NULL,
  PermissionName varchar(256) NOT NULL,
  PRIMARY KEY (PermissionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table roles
--

DROP TABLE IF EXISTS roles;
CREATE TABLE IF NOT EXISTS roles (
  RoleId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  RoleType varchar(256) NOT NULL,
  InstitutionId binary(16) NOT NULL,
  PRIMARY KEY (RoleId),
  KEY InstitutionId_Roles_FK (InstitutionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table roles_permissions
--

DROP TABLE IF EXISTS roles_permissions;
CREATE TABLE IF NOT EXISTS roles_permissions (
  RoleId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PermissionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  InstitutionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  KEY RoleId_Roles_Permissions_FK (RoleId),
  KEY PermissionId_Roles_Permissions_FK (PermissionId),
  KEY InstitutionId_Roles_Permissions_FK (InstitutionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table routes
--

DROP TABLE IF EXISTS routes;
CREATE TABLE IF NOT EXISTS routes (
  RouteId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  RouteName varchar(256) NOT NULL,
  Mon tinyint(1) NOT NULL DEFAULT '0',
  Tue tinyint(1) NOT NULL DEFAULT '0',
  Wed tinyint(1) NOT NULL DEFAULT '0',
  Thu tinyint(1) NOT NULL DEFAULT '0',
  Fri tinyint(1) NOT NULL DEFAULT '0',
  Sat tinyint(1) NOT NULL DEFAULT '0',
  Sun tinyint(1) NOT NULL DEFAULT '0',
  StartDate date NOT NULL,
  EndDate date NOT NULL,
  StartTime time NOT NULL,
  EndTime time NOT NULL,
  CurrentNumberOfBookings int NOT NULL,
  InstitutionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (RouteId),
  KEY InstitutionId_Routes_FK (InstitutionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table routes_stops
--

DROP TABLE IF EXISTS routes_stops;
CREATE TABLE IF NOT EXISTS routes_stops (
  RouteId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  StopId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  Time time NOT NULL,
  InstitutionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  KEY RouteId_RoutesStops_FK (RouteId),
  KEY StopId_RoutesStops_FK (StopId),
  KEY InstitutionId_RoutesStops_FK (InstitutionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table staff
--

DROP TABLE IF EXISTS staff;
CREATE TABLE IF NOT EXISTS staff (
  StaffId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  InstitutionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  FirstName varchar(256) NOT NULL,
  MiddleName varchar(256) NOT NULL,
  LastName varchar(256) NOT NULL,
  UserId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  KEY UserId_FK (UserId),
  KEY InstitutionId_Staff_FK (InstitutionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table stops
--

DROP TABLE IF EXISTS stops;
CREATE TABLE IF NOT EXISTS stops (
  StopId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  StopName varchar(256) NOT NULL,
  Longitude decimal(9,6) NOT NULL,
  Latitude decimal(9,6) NOT NULL,
  AddressId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  InstitutionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (StopId),
  KEY AddressId_Stops_FK (AddressId),
  KEY InstitutionId_Stops_FK (InstitutionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table students
--

DROP TABLE IF EXISTS students;
CREATE TABLE IF NOT EXISTS students (
  StudentId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  FirstName varchar(256) NOT NULL,
  MiddleName varchar(256) NOT NULL,
  LastName varchar(256) NOT NULL,
  DateOfBirth date NOT NULL,
  InstitutionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (StudentId),
  KEY InstitutionId_Student_FK (InstitutionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table students_parents
--

DROP TABLE IF EXISTS students_parents;
CREATE TABLE IF NOT EXISTS students_parents (
  StudentId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  ParentId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  InstitutionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  KEY StudentId_StudentParent_FK (StudentId),
  KEY ParentId_StudentParent_FK (ParentId),
  KEY InstitutionId_StudentParent_FK (InstitutionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table users
--

DROP TABLE IF EXISTS users;
CREATE TABLE IF NOT EXISTS users (
  UserId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  Email varchar(256) DEFAULT NULL,
  Password char(60) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  InstitutionId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  RoleId binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (UserId),
  KEY InstitutionId_FK (InstitutionId),
  KEY RoleId_Users_FK (RoleId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Constraints for dumped tables
--

--
-- Constraints for table bookings
--
ALTER TABLE bookings
  ADD CONSTRAINT InstitutionId_Bookings_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT RouteId_Bookings_FK FOREIGN KEY (RouteId) REFERENCES routes_stops (RouteId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT StopId_Bookings_FK FOREIGN KEY (StopId) REFERENCES routes_stops (StopId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT StudentId_Bookings_FK FOREIGN KEY (StudentId) REFERENCES students (StudentId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT UserId_Bookings_FK FOREIGN KEY (UserId) REFERENCES users (UserId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table buses
--
ALTER TABLE buses
  ADD CONSTRAINT InstitutionId_Buses_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table buses_routes
--
ALTER TABLE buses_routes
  ADD CONSTRAINT BusId_BusesRoutes_FK FOREIGN KEY (BusId) REFERENCES buses (BusId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT InstitutionId_BusesRoutes_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT RouteId_BusesRoutes_FK FOREIGN KEY (RouteId) REFERENCES routes (RouteId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table drivers
--
ALTER TABLE drivers
  ADD CONSTRAINT BusId_Drivers_FK FOREIGN KEY (BusId) REFERENCES buses (BusId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT InstitutionId_Drivers_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT UserId_Drivers_FK FOREIGN KEY (UserId) REFERENCES users (UserId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table institutions
--
ALTER TABLE institutions
  ADD CONSTRAINT AddressId_FK FOREIGN KEY (AddressId) REFERENCES addresses (AddressId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table parents
--
ALTER TABLE parents
  ADD CONSTRAINT InstitutionId_Parents_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT UserId_Parents_FK FOREIGN KEY (UserId) REFERENCES users (UserId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table roles
--
ALTER TABLE roles
  ADD CONSTRAINT InstitutionId_Roles_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table roles_permissions
--
ALTER TABLE roles_permissions
  ADD CONSTRAINT InstitutionId_Roles_Permissions_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT PermissionId_Roles_Permissions_FK FOREIGN KEY (PermissionId) REFERENCES permissions (PermissionId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT RoleId_Roles_Permissions_FK FOREIGN KEY (RoleId) REFERENCES roles (RoleId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table routes
--
ALTER TABLE routes
  ADD CONSTRAINT InstitutionId_Routes_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table routes_stops
--
ALTER TABLE routes_stops
  ADD CONSTRAINT InstitutionId_RoutesStops_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT RouteId_RoutesStops_FK FOREIGN KEY (RouteId) REFERENCES routes (RouteId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT StopId_RoutesStops_FK FOREIGN KEY (StopId) REFERENCES stops (StopId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table staff
--
ALTER TABLE staff
  ADD CONSTRAINT InstitutionId_Staff_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT UserId_FK FOREIGN KEY (UserId) REFERENCES users (UserId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table stops
--
ALTER TABLE stops
  ADD CONSTRAINT AddressId_Stops_FK FOREIGN KEY (AddressId) REFERENCES addresses (AddressId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT InstitutionId_Stops_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table students
--
ALTER TABLE students
  ADD CONSTRAINT InstitutionId_Student_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table students_parents
--
ALTER TABLE students_parents
  ADD CONSTRAINT InstitutionId_StudentParent_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT ParentId_StudentParent_FK FOREIGN KEY (ParentId) REFERENCES parents (ParentId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT StudentId_StudentParent_FK FOREIGN KEY (StudentId) REFERENCES students (StudentId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table users
--
ALTER TABLE users
  ADD CONSTRAINT InstitutionId_FK FOREIGN KEY (InstitutionId) REFERENCES institutions (InstitutionId) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT RoleId_Users_FK FOREIGN KEY (RoleId) REFERENCES roles (RoleId) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

I wish to fetch all rows from table after the nth Position as i given offset value my table like

id | Value
1  | A |
2  | B |
3  | C |
4  | D |
5  | E |

...

N  | R |

I wants the 3rd row Data to Nth Rows How to i Retrive

Sorry, I seen similar threads but I still couldn't find it addressing my issue plus, I needed some more info on this. **Requirement:** To create an exact replica 'db4' of an existing DB 'db3'. **Procedure followed:** * mysqldump -uuser -ppass db3 > db3.sql (size is 6G) * mysql -uuser -ppass db4 < db3.sql (db4 was a newly created blank database) The 2nd step throws in the error: ERROR 1062 (23000) at line 5524: Duplicate entry '600806' for key 1" I ran the 2nd step again with --force. The restore completed but with 2 additional similar errors: ERROR 1062 (23000) at line 6309: Duplicate entry '187694' for key 1 ERROR 1062 (23000) at line 6572: Duplicate entry '1567400' for key 1 On completion when I queried certain tables of db4 database, I was able to see missing records. **Question:** 1. Does this indicate a corrupted/problematic db3 database? 2. How to proceed to create a 'consistent/working' replica (db4) of db3? 3. If (2) fails, how to possibly troubleshoot and find the reason behind why it occurs? Thanks,

I have a large-ish (several GB) mysql DB that I'm planning to import via mysql -u root -p mydb < mydb.sql in a SSH session. What would happen if my SSH connection drops? Is it safer to run something like nohup mysql -u root -p mydb < mydb.sql Or does mysql not care? Using the following - MySQL 5.1.45 - CentOS 5.x

I have designed my database model with entities and they have relations along with normalization. Now I want to implement my design in my database engine. Before implementing I am puzzled with the order of table creation. Which pattern should I follow? 1. the tables with no foreign keys first 2. the tables with foreign keys Or would it be better to follow this order: 1. tables without any foreign keys 2. tables which are connected with that table I am new to database design. So forgive me if i am asking this awkward question. I am implementing my design via mysqlworkbench 8.x

I have run the query in mysql which consists of approximately 33million records when i have executed the query - select population1 < 200000 from city; it shows like this after execution ---------- 34459393 rows in set (16.04 sec) My question is what is 16.04 sec because in normal time it has taken morethan 30min but it shows 16.04 sec. How can i calculate the actual time.

Upgrading MySQL database to 5.5 and it seems that the root user has disappeared. I'm trying to create it again using this commands: javier@javier-mbp:~$ sudo pkill mysqld [sudo] password for javier: javier@javier-mbp:~$ sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables& 1927 javier@javier-mbp:~$ 111028 16:37:44 mysqld_safe Logging to '/var/log/mysql/error.log'. 111028 16:37:44 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data Then I do: javier@javier-mbp:~$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> GRANT CREATE, DROP ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement Any help? EDIT: Solved, well… I just should do a backup of the user root since I'm removing the database before upgrading to 5.5.