i need to change storage engine from 'InnoDB' to 'MyISAM' on some tables of different databases. So i made a script file to do so: #!/bin/bash DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")') for D in $DATABASES do TABLES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select table_name from information_schema.tables where table_schema="'$D'" and engine="InnoDB"') for T in $TABLES do echo "ALTERING TABLE $T" mysql -u XXXXX -pXXXXX -e "USE $D; ALTER TABLE $T ENGINE=MYISAM" done done It works on some tables, but on those with foreign keys, displays the following error message: ERROR 1217 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails Is there a way to change storage engine on those tables?

In the [docs](https://learn.microsoft.com/en-us/sql/relational-databases/reading-pages?view=sql-server-ver16) it says we can share index scans (Advanced Scanning). Now [here](https://www.mssqltips.com/sqlservertip/4472/sql-server-enterprise-advanced-scan-aka-merrygoround-scan/) they used wait stats and statistics and i did not find an extended events session that could directly identify it. The question is - how can i know that an advanced scan has been used? I know about ordered false property, but that just says that it did not use the order in the index key?

I'm developing a web site for a model agency. I have a "model" table with the details about each model (this is just an example): CREATE TABLE model { model_id INT PRIMARY KEY, name VARCHAR(100), hair_color_id TINYINT, eyes_color_id TINYINT, skin_color_id TINYINT, city_id TINYINT, another_id TINYINT, blabla_id TINYINT, ... } The hair_color_id is a FK of the following table: CREATE TABLE hair_color { id INT PRIMARY KEY, name VARCHAR(100), visible TINYINT } The same applies to eye_color_id and most of the other fields. This way I keep my "main" table small while I can use the other tables to store the available options and list them dinamically in the HTML dropdown lists. They are also handy when adding new models to the "model" table, I don't want people can store wrongs ids either by accident or maliciously, so the constraints keeps my model table with valid data. Of course, when I want to show the models profile I have to make many many JOINs and this will happen a lot. Having that in mind, my question is: which storage engine would you recommend me to use for these tables (eye colors, hair colors, etc)? I'm using InnoDB (because of its cache function) but I think that maybe it's better to use MEMORY storage engine. Or MyISAM because they will be practically read only. Do you think that I should change this schema and store these values in a PHP file so it's not neccesary to make the JOINs?

I was going through Database storage engines and found out about LSM trees. I had also read about Kafka architecture and know that Kafka internally is a commit log. I want to know if Kafka internally uses LSM data structure for append-only store or uses some other data structure for storing data?

I need to store a big amount of data (about 2.5 billion of new data rows per month), but also I need a very fast way to retrieve the latest value per group on specific time point. The data looking very simple: | ParameterId | Value | DateTime | |--------------|-------|---------------------| | 1 | 12.5 | 2023-04-21 14:35:03 | | 2 | 56.81 | 2024-03-01 16:21:17 | | 1 | 12.5 | 2024-05-22 14:35:03 | | 1 | 71.4 | 2024-05-31 18:27:03 | For example, we need the latest values by each parameter on 2024-04-31 17:40. The result will be as follows: | ParameterId | Value | DateTime | |--------------|-------|---------------------| | 1 | 12.5 | 2023-04-21 14:35:03 | | 2 | 56.81 | 2024-03-01 16:21:17 | This looks like it can be solved by a simple database storage, but I've some restrictions: 1. The disk storage is limited. That's why indexes cannot be used as it's almost an x2 of the data space 2. The max query time for any request is 5 seconds. 3. We have only 1 server I've already tried to use TimescaleDB (table partitioning), but because I've only right datetime condition (<= dt) it's very inefficient to search for the value in old chunks from the first one. Technically it's possible, because we have an old software developed by third-party company 10 years ago and it still working, but nobody knows how...

I need to clarify the keyword "engine" and what functionality stands behind it. I assume engine is a smaller component of a bigger thing - database. Like a 'car engine' is inside a car. So dwh engine can be plugged into bigger datawarehouse? Olap engine can be used as component of actual olap db? I found this data landscape image on Medium.com with 2 different categories: olap db and olap engine. What is the difference since they have overlapping functionality?

SQL Server tracks page allocation in various internal bitmaps . Among these are the Global Allocation Map (GAM) and Page Free Space (PFS) pages. We know that GAM pages occur at set intervals of 511232 pages and that PFS pages occur at set intervals of 8088 pages. Given a large enough data file this repetition will eventually lead to one page being next in line for both GAM and PFS. Doing the math, this happens after 1,011 GAMs or 63,904 PFS pages at page number 516,855,552. This equates to a single OS file of just under 4TB. As the maximum size of a single data file is 16TB (source ) this has been allowed for. My question: when a single data file reaches 4TB which page type is page 516855552 - GAM or PFS? Where does the the other one go? This comment from Paul Randal suggests it is shunted to one of the otherwise unused pages in the GAM extent: >GAM extents except the first one have GAM, SGAM, DIFF_MAP, ML_MAP. Every 4TB the GAM extent will also have a PFS page. I've found this referenced here but not categorically explained: >

>-- There may be an issue with the ML map page position
>-- on the four extents where PFS pages and GAM pages live
>-- (at page IDs 516855552, 1033711104, 1550566656, 2067422208)
>-- but I think we'll be ok.
>

No other meaningful results show for me on the googles. The databases I have access to have several files so none meets the size requirement.

For interest, I was reading about the internal structures of a Microsoft SQL Server file. It's fairly obvious how IAM pages are connected. However, it is not clear to me where later GAM pages are to be found. It is written that the first GAM page is page 2 in each file. DBCC PAGE confirms this. The links above state there is another GAM page in "4GB" or after "64,000 extents." When I look in those places (they are not the same number) I do not find a GAM page. How many pages away from the first GAM page will I find the second GAM page (the "stride" of a GAM interval) in a SQL Server data file?

MariaDB 10.7 service suddenly stopped in Windows 10 after a crash and the service is not starting. The pop up shows 'windows cannot start mariadb service). The windows event viewer gives the following data: - InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. - InnoDB: Page [page id: space=0, page number=366] log sequence number 424180275 is in the future! Current system log sequence number 408454168. - InnoDB: unsupported undo header type 16 - InnoDB: Plugin initialization aborted with error Data structure corruption - Plugin 'InnoDB' init function returned error. - Plugin 'InnoDB' registration as a STORAGE ENGINE failed. - Unknown/unsupported storage engine: InnoDB - InnoDB: Missing FILE_CREATE, FILE_DELETE or FILE_MODIFY before FILE_CHECKPOINT for tablespace 251 Cannot restart the MaraDB service.

I'm unable to find out if there is a pagesize in a WiredTiger MongoDB storage engine. I mean some analog of the innodb_page_size for MySQL.

I have a MariaDb database which consists of 2 tables and which is around 10Gb of the size. One is dependendant on the other via a foreign key. How can I change the engine type of that particular db from InnoDb to MyISAM?

I have Mysql 5.5.18 and upon show engines:

show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+

as you see federated engine is not one of the list. i need to enable it!! any idea of why BLACKHOLE, FEDERATED, and ARCHIVE engines are missing from the list? and how can i add/enable them? **EDIT** I have ubuntu 11.10 and installed mysql from ppa ... i have added the following to /etc/apt/sources.list

deb http://ppa.launchpad.net/nathan-renniewaldock/ppa/ubuntu  oneiric main
deb-src http://ppa.launchpad.net/nathan-renniewaldock/ppa/ubuntu  oneiric main

Thanks for your help

"Fundamental of Database Systems", 3rd ed. by Elmasri and Navathe, page 136 says: "We can physically order the records of a file on disk based on the values of one of their fields [...]" How could this "physical ordering" possibly enforced, especially with regard to fragmentation? Most databases use system calls to store their data in the file system of the OS, only very few are capable of doing raw i/o.

When I bulk INSERT into an Aria table, it creates a copy of the INSERT in a series of log files (aria_log.%). It can lead to full disk and crash of MySQL. As I understand, Aria keeps a redo log for TRANSACTIONAL=1 only, and the default behaviour is TRANSACTIONAL=0. I also created a table specifically with TRANSACTIONAL=0, but the same behaviour. I use Aria for a sort of backup, as it is faster than InnoDB on HDD, and do not need transactional/crash-safe behaviour. How can I disable Aria logging and only writing into the table?

So today a professor told us that when the database has to make an update, internally (at low level) it makes a delete and then an insert with the updated fields. He then said that this is something made across all databases and then I started a discussion telling that I thought that it had no sense but I didn't had enough resources to support my position. He seems to know a lot but I can't understand why would dbs do that. I mean, I know that if you update a field and you need more space for that row, then it may delete the row physically and put it at the end with the new data. But if for example you reduce the space used, why would it delete and re insert it at the end? Is this even true? What are the benefits?

I can set a (MyISAM) table's engine to InnoDB using the query: alter table tablename engine=InnoDB which takes some time to complete for large tables. It appears though that if I run the same query again on the same table, it takes some time again (much less but still considerable). I would expect the query to be instantaneous, since the engine is already set to innodb. My questions are: - Is there a single query that conditionally sets the engine, for example alter table tablename engine=InnoDB - Why does the second query have such a delay? (out of curiosity mostly)

On my Ubuntu 18.04 machine using mysql 5.7 I have installed sphinxsearch 2.2 and now I'd like to add SphinxSE as an engine so that I can connect to it without language-specific medium. The docs on sphinix is rather old (for MySQL 5.1.x) and involves compiling mysql from source which I shy away from as it may break the existing stuff. So I'm wondering if there is any straightforward solution for this which does not require source compilation?

If Aria storage engine (previously called Maria) is the "new" MyISAM, which supports transaction and automatic crash recovery: - Why still use MyISAM ? - Should changing storage engine from MyISAM to Aria be a problem? (lose index or something)

I want to access MongoDB collections through MariaDB 10.4.17. Both are installed on my local machine running Windows 10. |Software|listening port| |--------|-------| |MongoDB | 27017 | |MariaDB | 15501 | I installed the MariaDB Connect Engine using INSTALL SONAME 'ha_connect'; on the MariaDB CLI The collection I want to access is named receptors and has documents like these

{
  "_id" : 1,
  "taste" : "Umami",
  "receptor_name" : "mGluR4",
  "uniprot_id" : "Q14833"
}

this is the create table statement I used on mariadb

CREATE TABLE receptors (
  _id varchar(24) NOT NULL,
  taste varchar(64) DEFAULT NULL,
  receptor_name varchar(64) DEFAULT NULL,
  uniprot_id varchar(64) DEFAULT NULL
) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4
  CONNECTION='mongodb://localhost:27017'
  table_type=MONGO tabname='receptors' data_charset=utf8

The table was created, but when I execute SELECT * FROM receptors I get this error:

ERROR 1296 (HY000): Got error 174 'Error 126 loading module jvm.dll: The specified module could not be found. ' from CONNECT

I fixed it by giving a path to JVM like this

set global connect_jvm_path="C://Program Files//Java//jdk-12.0.1//bin//server"

Next error I got is this which I am unable to figure out

ERROR 1296 (HY000): Got error 174 'ERROR: class wrappers/Mongo3Interface not found!' from CONNECT

I have the JavaWrappers file here: C:\Program Files\MariaDB 10.4\lib\plugin It should contain the Mongo3Interface but does not. I have no idea what I am doing wrong, and the MariaDB documentation isn't much help.

I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB). The theories say: - Both work with BTree indexes, but MyISAM can work with FULLTEXT index also. - InnoDB is more strict in data integrity while MyISAM is loose. - InnoDB has transactions while MyISAM does not. - InnoDB has foreign keys and relationship contraints while MyISAM does not. - MyISAM are faster to read but slower to write (I'm not sure about this one). When I create a table, should I always sacrifice something if I want to keep integrity and I want to speed up the query making search by "text"? How do you decide which engine to use if you need - integrity ? - speed ? - constraints ? - search by text ? - transactions ?