New answers tagged mariadb
0
I have tried all connections timeout and max connections configurations but it didn't help. My problem is when the bad application is making connections attempts and if it reaches threshold, maxscale just put the backend servers under maintenance, it doesn't differentiate that the attempts are from single user or many users collectively.
To solve the ...
0
On a separate machine with perhaps 2TB of disk space free:
mysqldump -u ... -p... -h mysql_server ... > dumpfile.sql
It will take time. Be sure to get the correct parameters -- routines, etc may not be dumped by default.
I suggest 2TB on the target machine because sometimes the dump is bigger than the database. (Sometimes it is smaller.) A "0&...
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 ...
0
Changing the wsrep_sst_method does not change this behaviour - it all depends on whether the remaining node thinks it's the primary partition or not. A partition is the segment of cluster members that can talk to each other.
The cluster knows how many members it should have currently and if any member leaves or joins it updates its internal map of members. A ...
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 ...
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....
0
I don't think you need CTEs. See if something like this will work:
SELECT d1.*, d2.*
FROM data d1
JOIN data d2 ON d2.x BETWEEN d1.x - 2 AND d1.x + 2
AND d2.y BETWEEN d1.y - 2 AND d1.y + 2
AND d2.z ... (I don't understand the criteria here)
AND d2.act_time < d1.act_time
Indexes for the optimizer ...
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
...
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 ......
0
You might want to think outside the box here. Your main issue isn't related to a database solution that will somehow replicate the clients that are unable to connect to the central database, but more of a conceptual issue of how to replicate database clients that can be OFFLINE (not connected to the main server) or ONLINE (connected to the main server) and/...
0
(This is a partial answer.) (My terminology uses the newly-preferred, politically correct, terms Primary and Replica.)
Currently: P1->R2->R3
Stop writing to the Primary-1
Wait for replication to get through to the Replicas. (This is likely to be finished "immediately".)
Make Replica-2 the Primary-2 by allowing writes.
Change clients to ...
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 ...
0
Plan A: Dual-Primary with two Replicas
R2 <- P1 <=> P3 -> R4
Plan B: Use Galera and allow writing to all 3 (or 4) nodes.
Note: Galera gives you easier HA, since the recovery after failure is better automated.
Potential problem: Replicas (which includes all nodes in either Plan) reach out to their respective Primary. You should verify ...
0
Alright... for those who have the same problem,
Run this if you haven't yet:
# yum update ca-certificates
If you get No packages marked for update, it may have been already run automatically - in my case, it was on Sep 24:
Sep 24 05:00:16 Updated: ca-certificates-2021.2.50-72.el7_9.noarch
And then run in your MySQL instance:
> FLUSH SSL;
This resolved my ...
0
This has been an issue since 2003 but without any solution to reduce the size of an InnoDB tablespace.
It applies to any InnoDB tablespace, not just ibdata1. What OPTIMIZE TABLE does is copy data to a new tablespace and drops the original tablespace. It does not shrink the existing tablespace.
The workaround, as you no doubt know, is to dump all your InnoDB ...
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, ...
Top 50 recent answers are included