25
votes
Accepted
In the same query INSERT, can two VALUES being NOW() return a different time?
The time returned by NOW(), and other date time functions, is derived from the start time of the query. The THD class here is used to contain all the information for the connection. The NOW() function ...
- 7,585
17
votes
In the same query INSERT, can two VALUES being NOW() return a different time?
Another aspect: SYSDATE() can be different, but NOW() cannot.
mysql> select now(6), sysdate(6), now(6), sysdate(6);
+----------------------------+----------------------------+----------------------...
- 76.3k
9
votes
Allow only unique combination of values for a composite primary key
Since you use MariaDB 10.2, you can add a CHECK constraint, enforcing that the first id is less than the second (id_1 < id_2). CHECK constraints were added in version 10.2.1.
This has the ...
- 96.8k
8
votes
Accepted
Why am I connecting securely to MySQL when I don't have client certificates?
Yes the connection is encrypted. I think you are getting a few things mixed up here. Let me try to simplify it for you:
SSL is a protocol for exchanging data across a secure network, and in order to ...
- 13k
4
votes
Accepted
MariaDB 10.2: Why does Count(*) work, but not Count(t.*)
In MariaDB, the COUNT() function expects an expression (that is to say, a single value). For each row where the expression is not NULL, that row will be counted. With DISTINCT, as you note, it will ...
- 11.6k
4
votes
Accepted
What are some practical use cases for memory tables?
If you are learning MySQL, skipping MEMORY storage engine is not a bad idea. InnoDB is the default storage engine and it is suitable for most cases. Only in edge cases it makes sense to use another ...
- 1,623
4
votes
Accepted
The table is full - MariaDB
Please do not get fooled by the error message.
Note that the error is table is full. It does not say disk is full.
What would create a table is full condition ?
It has to do with the changes that ...
- 181k
3
votes
Dropping a CHECK in mariadb?
You must assign a name to the CHECK constraint during add:
ALTER TABLE `mydb`.`table1`
ADD CONSTRAINT check_json_validity CHECK(JSON_VALID(jsonf));
Then you may freely remove it by name:
ALTER ...
- 19.2k
3
votes
Accepted
Does a MySQL dump that contains triggers double data on import?
If you take time to run mysqldump, even with all default arguments, you will see that triggers in the dump file are created after all insert statements that populate your tables. So no, there will be ...
- 24.8k
2
votes
mariadb galera bootstrapping fails
I made it work.
The problem is how the bootstrap script tries to start the server.
I tried on different machines and all had the same symptoms when trying to bootstrap the cluster using the script ...
- 93
2
votes
json functions: double quotes added when going through a variable
@Evan Carroll is certainly right about the general implementation of JSON in MariaDB.
But if you are still going with MariaDB this might help you:
The behavior you describe is a serious bug which is ...
- 121
2
votes
Accepted
json functions: double quotes added when going through a variable
First, generally speaking, there are two types of JSON,
JSON-validated text.
Binary JSON (Mongo calls it BSON, PostgreSQL calls it JSONB, also Apache Avro).
For reference MariaDB has neither of ...
- 61.4k
2
votes
Alternative to ORDER BY GROUP BY to get first matches per ForeignKey
In MariaDB, you can use a window (ranking) function like ROW_NUMBER() or RANK():
with di as
( select *,
rank() over (partition by ProductModelID
order by Price)
...
- 96.8k
2
votes
The table is full - MariaDB
tmp_table_size=2G
max_heap_table_size=2G
Those are dangerously large. Keep them to no more than about 1% of RAM -- about 300M. Those settings are used in tmp tables inside complex SELECTs; your ...
- 76.3k
2
votes
Accepted
Partitioning increases CPU usage in mariadb
OVERHEAD
What needs to be understood is a partitioned table's physical implementation.
For example, in my old post (Aug 31, 2014 : How does subpartitioning actually work and what physical files are ...
- 181k
2
votes
Dropping a CHECK in mariadb?
You should always (if possible) name your constraints:
CREATE TABLE T ( x int not null );
ALTER TABLE T
ADD COLUMN jsonf JSON DEFAULT NULL;
ALTER TABLE T
ADD CONSTRAINT T_C1 CHECK(...
2
votes
Accepted
mysql : ordering a table in the order of the primary key
ENGINE=MyISAM, that's the reason for the chosen query plan.
If the table was using the InnoDB engine, then the primary key index would be the clustered key of the table, so it wouldn't have to do ...
- 96.8k
2
votes
In the same query INSERT, can two VALUES being NOW() return a different time?
Didn't find the answer from @nbk satisfying enough for my needs (especially "No, as long as you have no micro seconds in your datetime and a really very slow server, it could be possible").
...
- 463
2
votes
Accepted
Missing an inserted row
To summarize and close (at least for now) this question:
Original issue stems from the fact, that MySQL does not guarantee valid and consistent comparison of BIGINT against strings (VARCHAR or not) ...
- 131
1
vote
Accepted
How to use FIRST_VALUE window function in MySQL/MariaDB to get one row per group?
I'm not sure I understand the problem, but I'll give it a shot. This will satisfy your sample data and expected result:
select contact_id, email_id as email_id, email
from (
select contact_id, id ...
1
vote
Accepted
How to clear the InnoDB redo log before starting mysqld for crash recovery
What you should be doing, is
kill mysqld process. Disable the MySQL service (or whatever it is called), if it runs as service.
upgrade to latest 10.2 (it is already on 10.2.24 as of today, you are ...
1
vote
Accepted
Can XtraDB be enabled on MariaDB 10.2
As far as I understand, the answer is no, you can't use XtraDB in MariaDB 10.2. From the Changes & Improvements in MariaDB 10.2 page:
XtraDB in 10.2 is not up to date with the latest features ...
- 6,201
1
vote
Mysql: set variable in mysqldump
I believe you should be able to specify a session variable by placing it in a backup.cnf file:
max_statement_time=10000
And then using the --defaults-file=backup.cnf parameter to mysqldump.
(You ...
- 6,201
1
vote
Accepted
Join against two tables to get single combined result set
In this case, and due Coupon is an specific entry of another table I'd use a UNION.
As a suggestion, I'd try to convert 'Coupon' entry of orders_total, into another product added as another row to ...
- 23.5k
1
vote
MySQL - Classic Date Functions
Test:
WITH cte AS (
SELECT room_no,
booking_date + INTERVAL nights DAY d_start,
DATEDIFF(COALESCE(LEAD(booking_date) OVER (PARTITION BY room_no ORDER BY booking_date),
...
- 19.2k
1
vote
GSSAPI on MariaDB
I see in the comments to the MariaDB tutorial page you linked to that you have set plugin_dir in you my.ini file. However, I think this will only work if it's in a section of my.ini that the mysql ...
- 6,201
1
vote
Can sst_state_transfer be set to rsync with innodb as storage engine in Galera cluster?
Is xtrabackup the only wsrep-sst-method to be used with innodb storage engine?
All Galera clusters are InnoDB, MyISAM (and derivates) not supported (expect mysql database with wrappers for managing ...
- 2,428
1
vote
Which way should be selected when move from mysql to mariadb?
It's very probable that next sequence would be way more fast and painless comparatively to the software upgrading:
mysqldump all the databases
export all accounts with permissions
deinstall mysql
...
- 4,258
1
vote
Which way should be selected when move from mysql to mariadb?
We can't do anything without a problem except point you to the official information,
How to migrate from MySQL to MariaDB on Linux in five steps
Migrating to MariaDB
Generally the further you are ...
- 61.4k
1
vote
Accepted
table_open_cache increase issue - MariaDB 10.2
I can see 3 different reasons why MariaDB might reset this variable. In order of most to least likely explanation:
You have another .cnf file where the table_open_cache variable is being set to 4895. ...
- 6,201
Only top scored, non community-wiki answers of a minimum length are eligible