5
Yes, SELECT is a locking select in this case.
If you use SELECT in any query that changes data, it is implicitly a locking query as if you had used SELECT ... LOCK IN SHARE MODE. This puts a shared lock on the rows it reads.
Any other statement that changes data acts the same way. This includes cases like:
INSERT ... SELECT ...
CREATE TABLE ... AS SELECT ......
3
Disclaimer: I work for MariaDB
As of 10.7 you will be able to use the new functions JSON_EQUALS and JSON_NORMALIZE. See related tickets/implementation at:
https://jira.mariadb.org/browse/MDEV-23143 (Add JSON_EQUALS function to check JSON equality)
https://jira.mariadb.org/browse/MDEV-16375 (Add JSON_NORMALIZE function to normalize JSON values)
https://...
3
There is no special date math built in to make date + number mean add a number of days to the date. Instead, it is converting your date implicitly to a number and then doing the math, returning a number.
Instead, you should use the date_add function:
select date_add(curdate(), interval -2 day);
DbFiddle to demo
3
This is a classic gaps-and-islands problem.
There are many solutions. A standard method is to define starting or ending points for each section, then use a windowed conditional COUNT to number the islands. Then you simply group by this grouping number.
There are added complications here
We need to check if the previous row is negative but this is not
You ...
2
Here is an optimized, bug-fixed query. Fiddle for demonstration.
WITH cte1 AS (
SELECT *, (X-2) AS X_START, (X+2) AS X_END,
(Y-2) AS Y_START, (Y+2) AS Y_END, (Z*1.2) AS Z_MAX,
DENSE_RANK() OVER (PARTITION BY ID1, ID2 ORDER BY ACT_TIME) AS `DENSE_RANK`
FROM data
),
cte3 AS (
SELECT
c.ID
FROM cte1
JOIN cte1 c ON (cte1.ID <> c.ID and cte1....
2
XAMPP(V.3.2.2) is really old. As is MariaDB 10.1.10 (Dec 2015) which it includes. The entire 10.1 series is now unsupported by the upstream MariaDB.
Recommend:
A physical copy of C:/xampp/mysql/data to a different directory while MariaDB is shut down. This is in case you need the upgrade doesn't work and you need to revert. If you have time in reverting, ...
1
It's probably merging the change buffer into the index pages. That manual page shows some ways to monitor the change buffer size and its progress.
The deferred I/O caused by the change buffer merges is normal.
You could disable your change buffer (see the linked manual page) before you do the big INSERT, but then your INSERT would take hours because all ...
1
"are in the same server as virtual machines" -- This means that they are competing for CPU and I/O, especially during the backup. Is this just a test? I would not deploy Galera with all nodes on a single machine.
When SST is involved, 2 of the 3 nodes are involved. The donor is busy fetching and sending data; the recipient cannot receive ...
1
VIEWs are syntactic sugar, not a way to speed up anything.
This will create a new table from a select:
CREATE TABLE foo
SELECT ....;
This creates a TEMPORARY table that will go away when you disconnect:
CREATE TEMPORARY TABLE foo
SELECT ....;
To rebuild the table daily, this will repopulate it:
TRUNCATE TABLE foo;
INSERT INTO foo
...
1
why the float is interpreted as 123457000
The outout formatting process did that. (Since you have not specified what tool was used to do the SELECT, I cannot address it specifically.)
Any output process has to decide what to do with imprecise data.
How many decimal places should it show for SELECT 1/3;?
Since FLOAT has only about 6-7 significant digits, ...
1
A possible answer. Let me provide a scenario and its solution; I don't know if it applies to your situation:
sync_binlog = 0.
The Primary crashes.
The Primary comes back online and starts a new binlog.
The Replica grumbles.
Then
You find the new binlog.
On the Replica set the position to the beginning of that new binlog. ("Beginning" is pos=0 ...
1
The maximum row size is limited to about half of a page size, hence the error you got.
Switching to 4KB page size may be beneficial to a table with these characteristics:
Mostly random accesses, AND
The table is bigger than will fit in the buffer_pool (cf innodb_buffer_pool_size), AND
The disk is HDD (as opposed to SSD, which tends to be fast enough to mask ...
1
Your Strike node:
2021-09-23 8:29:42 0 [Note] /usr/sbin/mariadbd (initiated by: unknown): Normal shutdown
So it looks something it just sending a SIGTERM to shut the service down at this time.
The other nodes logs are after Strike shutdown.
Note all nodes have non-primary. If you are starting after a power failure, see the Restarting the Cluster ...
Only top voted, non community-wiki answers of a minimum length are eligible