/* Day 12: Data persistence
Tim Pierson, Dartmouth CS61, Spring 2026
*/

-- SLIDE 5
-- find where data is store on disk
SELECT @@datadir; -- /usr/local/mysql/data on my MAC
-- use sudo ls sudo ls -la /usr/local/mysql/data -- has directory for each schema
-- ls -la /usr/local/mysql/data/nyc_data to see nyc_data schema


SHOW ENGINE INNODB STATUS ;
/*
=====================================
2026-05-15 12:53:46 0x16d51b000 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 37 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3331 srv_active, 0 srv_shutdown, 2104466 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 122029
OS WAIT ARRAY INFO: signal count 128072
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2026-04-30 17:05:33 0x16cebb000 Transaction:
TRANSACTION 107476, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 501, OS thread handle 6122352640, query id 164561 localhost ::1 root updating
DELETE FROM Orders
	WHERE OrderID = 1001
Foreign key constraint fails for table `lab3_db`.`orderlines`:
,
  CONSTRAINT `orderlines_ibfk_1` FOREIGN KEY (`OrderID`) REFERENCES `orders` (`OrderID`)
Trying to delete in parent table, in index PRIMARY tuple:
DATA TUPLE: 5 fields;
 0: len 4; hex 800003e9; asc     ;;
 1: len 6; hex 00000001a3d4; asc       ;;
 2: len 7; hex 02000001660593; asc     f  ;;
 3: len 3; hex 8fd162; asc   b;;
 4: len 4; hex 800001f5; asc     ;;

But in child table `lab3_db`.`orderlines`, in index PRIMARY, there is a record:
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 800003e9; asc     ;;
 1: len 4; hex 80000064; asc    d;;
 2: len 6; hex 00000001a3c1; asc       ;;
 3: len 7; hex 81000001ad0110; asc        ;;
 4: len 4; hex 80000001; asc     ;;
 5: len 5; hex 800000db63; asc     c;;

------------
TRANSACTIONS
------------
Trx id counter 108397
Purge done for trx's n:o < 108397 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281480352862104, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281480352861312, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281480352860520, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281480352859728, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (read thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (read thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
I/O thread 10 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:
Pending flushes (fsync) log: 0; buffer pool: 0
12241129 OS file reads, 5167490 OS file writes, 227694 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 57, seg size 59, 1096 merges
merged operations:
 insert 79988, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          29903072534
Log buffer assigned up to    29903072534
Log buffer completed up to   29903072534
Log written up to            29903072534
Log flushed up to            29903072534
Added dirty pages up to      29903072534
Pages flushed up to          29903072534
Last checkpoint at           29903072534
Log minimum file id is       9128
Log maximum file id is       9131
3032249 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2646182
Buffer pool size   8191
Free buffers       1024
Database pages     7167
Old database pages 2625
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7762759, not young 429252614
0.00 youngs/s, 0.00 non-youngs/s
Pages read 12241541, created 1328229, written 2027414
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7167, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=612, Main thread ID=0x16c387000 , state=sleeping
Number of rows inserted 56139126, updated 5917410, deleted 113, read 1186910002
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 104273, updated 331950, deleted 95035, read 1659873
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
*/

SHOW TABLE STATUS WHERE Name='Restaurants';


-- SLIDE 29
USE nyc_data;
SELECT * 
	FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'nyc_data';
        
SELECT * 
	FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'nyc_data'
		AND TABLE_NAME = 'Restaurants';
        
SELECT *
	FROM Restaurants
    WHERE Boro = 'Manhattan';
    
-- SLIDE 44
SELECT *
	FROM Restaurants
    WHERE RestaurantID = 30075445;
    
-- SLIDE 54
SELECT *
	FROM Restaurants
    WHERE RestaurantID = 40359480;
    
-- SLIDE 58
SELECT *
	FROM Restaurants
    WHERE RestaurantID BETWEEN 40359480 AND 40362264;
    
-- SLIDE 63
SELECT *
	FROM Restaurants
    ORDER BY RestaurantID;
    
-- SLIDE 67
CREATE INDEX idx_boro ON Restaurants(Boro);

DROP INDEX idx_boro ON Restaurants;