5
You should always store dates using the DATE type. It should use less storage and will generally be faster when searching than if you stored the same date as a VARCHAR.
Additionally, if you stored it as VARCHAR then the format is baked into your data, which will make it harder for you to use different date formats in the presentation layer. For example, ...
3
Create a separate table named account_owner with columns account_id and user_id.
Have account_id be the primary key and both account_id and user_id should reference their respective parent tables. Since only one record can be entered per account_id then you ensure there is always at most one owner.
Alternatively, just add a owner_user_id column in the ...
3
In MySQL multi-column unique constraints are implemented such that they allow multiple null values. You can make use of this by using nulls to represent the non-owners:
create table account_membership (
account_id int not null references accounts (id),
user_id int not null references users (id),
is_owner boolean null check (is_owner in (1, null)),
...
2
I think the problem has to do with the definition of CHAR(..). It includes trailing blanks to fill out the space indicated. But the actual string is does not have any trailing spaces. So ' ' becomes ''.
So, change from str CHAR(1) to str VARCHAR(1).
1
MAMP seems to handle the root username differently than WAMP. I had to include the root password as 'root' in my PHP script, instead of leaving it blank, as such:
$link = mysqli_connect('localhost', 'root', 'root');
1
1/What are you doing wrong : This is the easy part, you moved problematic subqueries to a left join still doing the subqueries. Then you removed the function grouping the results as Akina pointed in the comments, resulting in multiple lines joining with multiple lines
2/What to do to solve the immediate issue at hand (bad idea) : just put back the sum ...
1
You should leave the innodb_buffer_pool_chunk_size "as is".
Last year, Percona published some nice formulas to figure out the best chunk size
determine_best_chunk_size{
if innodb_buffer_pool_size / innodb_buffer_pool_instances < innodb_buffer_pool_chunk_size
then
innodb_buffer_pool_chunk_size = roundDownMB(innodb_buffer_pool_size / ...
1
MariaDB does not have a datatype of JSON. Instead it gives you LONGTEXT.
Be aware that there are a lot of differences between MySQL and MariaDB in the area of JSON. Furthermore, different versions of each have different levels of functions, etc.
1
Something like this..?
Table structure and sample data
CREATE TABLE sample (
id int,
seq int,
allocation FLOAT(10, 2),
template_id int,
headcount_id int,
cost_center_id int,
country_id int,
total int
);
INSERT INTO sample
VALUES
(1, 1, '1.00', 1, 1, 1234, 1, 10),
(2, 1, '1.00', 1, 1, 1234, 1, 12),
(3, 1, '1.00', 1, 1, 1234, ...
1
The ROLLBACK must finish in order to get the database back to a table state.
(Without knowing the details of the load, I cannot advise on autocommit.)
1
The basic syntax for this generally involves inserting into one table by selecting from another, which would look something like:
INSERT INTO t1 (a1, b1)
SELECT a2, b2
FROM t2
WHERE a2 > 0
AND
b2 > 0
;
There is some flexibility in that, although perhaps not as much as you are looking for. It might be worth ...
1
The quick fix is to provide a decent 'composite' index for
SELECT MIN(zz.id)
FROM item_ledger_entry AS zz
WHERE zz.docNr=ile.docNr
AND zz.serialNo=ile.serialNo
AND zz.productNr=ile.productNr
Namely:
INDEX(docNr, serialNo, productNr, ...
1
Your theory is correct about your cloudSQL since it is a Google managed service.
If you would like to have full access over your mysql database, you can use it through a Compute Engine instance in which you will have super user privileges.
Nevertheless, about your current issue please fill a bug here which is under a private component and you can share ...
1
You are probably defaulted to CHARACTER SET utf8 (or utf8mb4), in which case (255) would need 2 bytes for the length.
But the real problem is with temp tables (pre-8.0): If the SELECT is so complex that it needs to generate a temp table (eg, for sorting), it will try first to use a MEMORY table. In doing so, it will turn VARCHARs into CHARs, thereby ...
Only top voted, non community-wiki answers of a minimum length are eligible
