mysql update sed: -e expression #1, char 17: unknown option to `s' When running the mysql rpm mysql-community-server-5.7.14-1.sles12.x86_64.rpm I get this error:

  sed: -e expression #1, char 17: unknown option to `s'

That far everything works but I noticed immediately after the update there were 712MB! less on the system disk and I suspect the clean up process did not work well because of the error.

My question:

Has anybody an idea how to fix the problem even it is a rpm file I download directly from MySQL?
Where can I remove manually the temporary files created while the update process?

Info:

Linux: SLES 12 SP1

MySQL: 5.7.14

Clarification:

I do not want to know what the problem means in the sed program. I do not have any access to the sed parameters [I did not write it; it is delivered by the MySQL organization] due to the fact it is ran by the rpm program controlled by the rpm file.

Oracle confirmed it as a bug and it will be fixed in the next relase.


SELECT query slow even with nonclustered Index and a full-text index I am attempting to query between 0 and 65,000 rows from a table.
The server is using Microsoft SQL Server 2014, and I have no way to change the hardware on the server.
Schema
[Id] (PK)        INT
[varchar1]  VARCHAR(4)     Normal Cardinality
[varchar2]  VARCHAR(250)   Normal Cardinality
[varchar3]  VARCHAR(250)   Normal Cardinality
[varchar4]  VARCHAR(100)   Normal Cardinality
[date1]     DATETIME       High Cardinality
[varchar5]  VARCHAR(100)   Low Cardinality
[varchar6]  VARCHAR(1000)  Normal Cardinality
[varchar7]  VARCHAR(100)   Normal Cardinality
[varchar8]  VARCHAR(20)    Normal Cardinality
[varchar9]  VARCHAR(100)   High Cardinality
[xml1]      XML            Low Cardinality

Query
The following query is part of a stored procedure (the rest of which is irrelevant since it has trivial impact on the stored procedure's performance).  The column names have been replaced with the column type and a number:
SELECT   [varchar1]
       , [varchar4]
       , [date1]
       , [varchar5]
       , [varchar6]
       , [varchar7]
       , [varchar8]
       , [varchar9]
       , [xml1]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [varchar1] = '0'
AND   ([date1] >='2014-1-1' AND [date1] <= '2017-1-1')
AND   [varchar8] = 'someText'
AND   [varchar9] LIKE '%a%'
ORDER BY [varchar1] ASC, [date1] DESC
OFFSET 0 ROWS
FETCH NEXT 65000 ROWS ONLY

Execution Plan
Execution Plan XML: https://gist.github.com/BlackyWolf/046856518065bfe5293cad78f73340e9
But the information it gave so far is:
Query1: Query cost (relative to the batch): 100%
                               Index Seek [NonClustered]
 SELECT            Top          [Table].[i_table_index]
Cost: 0%         Cost: 4%              Cost: 96%

I am not using a PK in this search. This query has the following durations depending on the columns removed:
All columns in select statement =   18s-27s
Without [xml1] =                     8s-11s
Without [xml1] and [varchar4] =      4s-6s
The total execution from Web to DB and back needs to be within 10s. Preferably this query needs to be within 4s.
Return Size (MB)
I am looking to get anywhere between 290-310 MB worth of data back, with a margin of error being 20 MB, for 65,000 records total.
Index
There is a clustered index on the Primary Key.
There is a non-clustered index defined as:
CREATE NONCLUSTERED INDEX [i_table_index]
ON dbo.[table] ([varchar1], [date1] DESC, [varchar8], [varchar9])
INCLUDE ([varchar4], [varchar7], [xml1]);

Unfortunately I can't really remove [xml1] or [varchar4] or I would. The index didn't seem to help much, to be honest, even though the execution plan showed it was using it.
My SQL Experience is limited to what I do with C#. I appreciate any help or guidance offered (even links), and if you need more information please let me know, I'll do my best to get it.

Each of the elements in the where clause are using functions. When functions are used sql cannot determine what index to use. It cannot determine what fields are in the where clause causing a table scan.

Converge the date between to date => and date =<
Is the database really case sensitive? Can you get rid of the lower()
Can you turn the contains into a patindex


Trigger to apply user mapping to logins on database creation Does anyone have any example triggers that apply user mapping on database creation?

Here's a very quick example that will create a user blat given an existing login blat in all new databases created (this won't work for attach/restore). You could extend this to adding them to roles, granting permissions, even building the list based off of some query from sys.server_principals in master, sys.database_principals in another database, or your own users table. Note that it doesn't have any error handling.

CREATE TRIGGER DBCreate_MapUsers
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
  DECLARE @db NVARCHAR(510);
  SET @db = CONVERT(SYSNAME, EVENTDATA().query('(/EVENT_INSTANCE/DatabaseName[1]/text())'));
  SET @db = QUOTENAME(@db) + N'.sys.sp_executesql ';
  EXEC @db N'CREATE USER blat FROM LOGIN blat;';
END


How does the Microsoft tablediff utility work? Our team is curious about the architecture of tablediff utility. We want to find the difference between table rows.

Microsoft mentions tablediff here, however does not state how it works. Does tablediff take the checksum or hash of every row and compare tables? What is the internal algorithm method?

https://docs.microsoft.com/en-us/sql/tools/tablediff-utility?view=sql-server-2017

create table dbo.CustomerTransaction
(
    CustomerTransactionId int primary key,
    CustomerName varchar(50),
    ProductName varchar(50),
    QuantityBought int
)

Using tablediff example:

So row values Table 1: (1,'Bob','Table',8) is the Same as Table 2: (1,'Bob','Table',8)

These are different (1,'Bob','Table',8), (1,'Bob','Chair',8), different on the primary key.

I know it requires the source table to have primary Key/Identity/rowguid column  to compare, and was originally based on replication technology.

I'm not aware of authoritative documentation of the fact that the current version of the tablediff utility uses a checksum to identify data differences. However, I was able to verify this by capturing the queries executed by the utility using a sql_batch_completed event trace. The trace showed a query is run against each table, returning only the primary key value(s) along with the T-SQL BINARY_CHECKSUM over all table columns.

This is the T-SQL script I ran on my local SQL instance to the create tables and trace:

USE tempdb;
DROP TABLE IF EXISTS dbo.table1;
DROP TABLE IF EXISTS dbo.table2;
CREATE TABLE dbo.Table1(Col1 int NOT NULL CONSTRAINT PK_Table1 PRIMARY KEY, Col2 int);
CREATE TABLE dbo.Table2(Col1 int NOT NULL CONSTRAINT PK_Table2 PRIMARY KEY, Col2 int);
INSERT INTO dbo.Table1 VALUES
      (1, 1)
    , (2, 2)
    , (3, 3);
INSERT INTO dbo.Table2 VALUES
      (1, 1)
    , (2, 0)
    , (4, 4);

While viewing the trace in SSMS (Object Explorer: right-click Management-->Extended Events-->Sessions-->sql_batch_completed and select Watch Live Data), I ran this from the command prompt:

"C:\Program Files\Microsoft SQL Server\140\COM\tablediff" -sourceserver "." -sourcedatabase "tempdb" -sourceschema "dbo" -sourcetable "Table1" -destinationserver "." -destinationschema "dbo" -destinationdatabase "tempdb" -destinationtable "Table2"

These were the relevant queries executed by tablediff:

SELECT [dbo].[table1].[col1],BINARY_CHECKSUM([dbo].[table1].[col1],[dbo].[table1].[col2],[dbo].[table1].[col3]) as MShash_54267293 FROM [dbo].[table1] WITH (READUNCOMMITTED) ORDER BY [dbo].[table1].[col1]

SELECT [dbo].[table2].[col1],BINARY_CHECKSUM([dbo].[table2].[col1],[dbo].[table2].[col2],[dbo].[table2].[col3]) as MShash_54267293 FROM [dbo].[table2] WITH (READUNCOMMITTED) ORDER BY [dbo].[table2].[col1]


PostgreSQL/PostGIS 9.6 broke my compound index In PostgreSQL 9.2 I had no problem creating the an index that had both a geography (postGIS) type and and integer as a compound index. But now (9.6) it complains on creation of the index and I don't understand the hint it is providing:

The columns and data are all created properly, Postgres is complaining on the create index.

ERROR: data type integer has no default operator class for access method "gist"
HINT: You must specify an operator class for the index
      or define a default operator class for the data type.
********** Error**********
ERROR: data type integer has no default operator class for access method "gist"
SQL state: 42704
Hint: You must specify an operator class for the index
      or define a default operator class for the data type.

The schema definition is as follows:

- Table: portal.inventory

-- DROP TABLE portal.inventory;

CREATE TABLE portal.inventory
(
  type character varying,
  pid integer,
  size bigint,
  date timestamp without time zone,
  path character varying,
  outline geography(Polygon,4326)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE portal.inventory
  OWNER TO postgres;

-- Index: portal.inventory_compound_idx

-- DROP INDEX portal.inventory_compound_idx;

CREATE INDEX inventory_compound_idx
  ON portal.inventory
  USING gist
  (outline, pid);

-- Index: portal.inventory_icompound_idx

-- DROP INDEX portal.inventory_icompound_idx;

CREATE INDEX inventory_icompound_idx
  ON portal.inventory
  USING gist
  (pid, outline);

You need to install a specific EXTENSION in your database:
CREATE EXTENSION btree_gist ;

According to PostgreSQL documentation on btree_gist:

btree_gist provides GiST index operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, char, varchar, text, bytea, bit, varbit, macaddr, inet, and cidr.
In general, these operator classes will not outperform the equivalent standard B-tree index methods, and they lack one major feature of the standard B-tree code: the ability to enforce uniqueness. However, they provide some other features that are not available with a B-tree index, as described below. Also, these operator classes are useful when a multicolumn GiST index is needed, wherein some of the columns are of data types that are only indexable with GiST but other columns are just simple data types. Lastly, these operator classes are useful for GiST testing and as a base for developing other GiST operator classes.

(emphasis mine)
btree_gist is part of the standard (current) PostgreSQL installation, so, you don't actually need to install any files in your system.
After installing this extension, you can execute all these instructions on a clean install of PostgreSQL 9.6.2, without a glitch:
-- If there is not there, create extension PostGis as well
CREATE EXTENSION IF NOT EXISTS postgis ;

-- Create Schema `portal`
CREATE SCHEMA IF NOT EXISTS portal ;

And execute all your CREATE statements without a glitch.
CREATE TABLE portal.inventory
(
  type character varying,
  pid integer,
  size bigint,
  date timestamp without time zone,
  path character varying,
  outline geography(Polygon,4326)
);

CREATE INDEX inventory_compound_idx
  ON portal.inventory
  USING gist
  (outline, pid);

CREATE INDEX inventory_icompound_idx
  ON portal.inventory
  USING gist
  (pid, outline);

NOTE: This was also needed for version 9.2, according to comment from @Erwin Brandstetter. So, most probably, if you make a dump of the version 9.2 databse, the CREATE EXTENSION btree_gist ; statement should appear.


Xml stored as varbinary I'm wondering if I have to
store a XML information inside a column (just storing, not for querying ) (SQL SERVER)
which Datatype is better to use XML or Varbinary? I did an example:

DECLARE @ClientList XML
SET @ClientList =
'<?xml version="1.0" encoding="UTF-8"?>
<!-- A list of current clients -->
<People>
<Person id="1234">
<FirstName>Juan</FirstName>
<LastName>Perez</LastName>
</Person>
<Person id="98765">
<FirstName>Alfredo</FirstName>
<LastName>Domiguez</LastName>
</Person>
</People>'

Declare @ClientListBinary Varbinary(max)
set @ClientListBinary = CONVERT (varbinary(max),@clientlist,1)
print datalength(@ClientList)
print datalength(@ClientListBinary)

and the result is that Varbinary uses more space... any thoughts on that

If your RDBMS has an xml datatype, why would you not use it?

For example, in SQL Server, you should put XML data in an xml column, because:

The XML data can be compared to an XML Schema Collection, to give the functionality of typed XML.
The XML data can be shredded with XQuery later.
The XML data can be indexed with a number of different XML index types, which help the XQuery shredding.

As far as I can see, the only reason to use varbinary(max) over xml is if you expect to be putting more than 2GB of data into one field. (Of course, there may be other reasons, which I'm sure I'll learn from the posts here. :-).


Why MySQL having clause omitting zero My fiddle is here.

Work around :
My table structure is

create table test (grIds int(1), amount int(1));
insert into test values
(1,10),
(1,20),
(1,15),
(2,0),
(3,8),
(3,25),
(3,16),
(4,0),
(4,21);

I have two select clause

select grIds, max(amount) from test
group by grIds having max(amount);

select grIds, max(amount) from test
group by grIds;

My question is why select with having keeps omitting row having grIds = 2 ?

HAVING clauses can place extra restrictions on the output rows of an aggregate query – just like WHERE clauses do in record-by-record queries. A HAVING clause can impose conditions on any output column or combination of columns, aggregate or not.

As far as NULL is concerned, the following two facts are worth knowing (and hardly surprising, I would guess):

Rows for which the HAVING condition evaluates to NULL won't be included in the result set. (“Only true is good enough.”)

“HAVING  IS [NOT] NULL” is a legal and often useful condition, whether  is aggregate or not. (But if  is non-aggregate, you may save the engine some work by changing HAVING to WHERE and placing the condition before the “GROUP BY” clause. This goes for any condition on non-aggregate columns.)

Remarks

MAX ignores any null values.
For character columns, MAX finds the highest value in the collating sequence.


Add missing rows to table via checking relations between other tables I have a set of tables: tender, product, product_fix, products_m_products (parent child relationship), product_m_tender. product_m_tender is missing some rows. These rows can be constructed through the use of the other tables.

Basically with tender.id i can find all products that belong to the tender via product_m_tender.tender_id. All the product id's are given by product_m_tender.product_id and product_m_tender.product_fix_id.  Parent child relation is defined only for product_fix.

Is it possible to construct a insert query that inserts all the product_m_tender relations if they are not already in the table.

Something like this:

insert into product_m_tender(tender_id, product_fix_id)
values(tender_id, product_fix_id if not present in product_m_tender and (parent in product_m_tender or child in product_m_tender))

EDIT

tender

CREATE TABLE public.tender
(
    id bigint NOT NULL DEFAULT nextval('tender_id_seq'::regclass),
    CONSTRAINT tender_pk PRIMARY KEY (id)
)

id
__
1

product_fix

 CREATE TABLE public.product_fix
    (
        id bigint NOT NULL DEFAULT nextval('tender_id_seq'::regclass),
        CONSTRAINT tender_pk PRIMARY KEY (id)
    )

id
_
1
2
3

products_m_products

CREATE TABLE public.products_m_products
(
    parent_product_id bigint NOT NULL,
    child_product_id bigint NOT NULL,
    CONSTRAINT "pmp:pk" PRIMARY KEY (parent_product_id, child_product_id)
)

parent_product_id | child_product_id
1                 | 3
2                 | 3

product_m_Tenderer

CREATE TABLE public.product_m_tender
(
    id bigint NOT NULL DEFAULT nextval('product_m_tender_id_seq'::regclass),
    tender_id bigint NOT NULL,
    product_fix_id bigint,
    CONSTRAINT product_m_tender_pk PRIMARY KEY (id)
)

id | tender_id | product_fix_id
1  |   1       |       1
2  |   1       |       3

Expected result:

product_m_tender

    id | tender_id | product_fix_id
    1  |   1       |       1
    2  |   1       |       3
    3  |   1       |       2

INSERT INTO product_m_tender (tender_id, product_fix_id)
SELECT tender_id, pairs.child_product_id
FROM product_m_tender
JOIN (SELECT parent_product_id, child_product_id FROM products_m_products
      UNION
      SELECT child_product_id, parent_product_id FROM products_m_products) pairs
  ON product_m_tender.product_fix_id = pairs.parent_product_id
ON CONFLICT (tender_id, product_fix_id) DO NOTHING;

fiddle (nextvals absence fixed by a constant of zero).

Pay attention that

CONSTRAINT tender_id_product_fix_id UNIQUE (tender_id, product_fix_id)

is added to product_m_tender table.

If possible products chain can be longer than 2 records, than execute this query until zero records affected, or move pairs subquery to recursive CTE.


Postgres: Use result of group by as column names I have a messages table in a database, which include a sender id and a message type (and of course many more columns not relevant for this question). I try to create a query which counts how many messages of each type a user have send.

e.g. if I have the following table:

---------------------------
id | user_id | message_type
---------------------------
1  | 1       | private
2  | 1       | public
3  | 1       | private
---------------------------

Then I want to get the following:

---------------------
id | private | public
---------------------
1  | 2       | 1
---------------------

So in fact I want to group by message_type and user_id, but instead of generating multiple rows per user, I want to create multiple columns, one for each message_type

Can I achieve this without hardcoding the message types in my query?

If you have a limited number of values that you want to convert into columns, then this can easily be implemented using an aggregate function with a CASE expression:

select user_id,
  sum(case when message_type = 'private' then 1 else 0 end) private,
  sum(case when message_type = 'public' then 1 else 0 end) public
from yourtable
group by user_id

See SQL Fiddle with Demo

PostgreSQL has the ability to use a crosstab() that can be used by installing the tablefunc module.  This will perform a similar data transformation from rows into columns. Creating a dynamic version of the query is not a straight forward process, here is a great solution for a dynamic crosstab on StackOverflow.


MySQL Master-Slave Replication Cross Database I am setting up Replication for cross Database as i have set 2 variables in Slave' s my.cnf as replicate-do-db=database_name and
Replicate-wild-do-table=database_name.% ,But i am not able to get cross Database updates from master to slave . Please Help me to resolve the issue.

I addressed something like back on June 14, 2012 : Configured MySQL replication but its not working
The basic problem may be the queries you are using
If you have a query that looks like this
INSERT INTO db1.tb SELECT * FROM db2.tb;

You could be potentially blocking queries of this nature
Queries like this:
USE db1
INSERT INTO tb SELECT * FROM db2.tb;

should work if you have replicate-do-db=db1 and Replicate-wild-do-table=db.%
To be sure, check out the replication filtering rules to make sure your queries are allowing your current settings to work on the Slave.
According to the replication filtering rules

To make it easier to determine what effect an option set will have, it
is recommended that you avoid mixing “do” and “ignore” options, or
wildcard and nonwildcard options. An example of the latter that may
have unintended effects is the use of --replicate-do-db and
--replicate-wild-do-table together, where --replicate-wild-do-table uses a pattern for the database name that matches the name given for
--replicate-do-db. Suppose a replication slave is started with --replicate-do-db=dbx --replicate-wild-do-table=db%.t1. Then, suppose that on the master, you issue the statement CREATE DATABASE dbx.
Although you might expect it, this statement is not replicated because
it does not reference a table named t1.

Perhaps you need to get rid of one of those rules on the Slave and restart mysql on the Slave.


Identity versus Composite values for Primary Keys in Many-To-Many Tables I am in the middle of an interesting "Data Architecture" discussion at a place I am contracting & need some input.

As a habit, I always create a Surrogate Key as the Primary Key in my tables - be they Guid (UUID) or Identity values.  IMHO the synthetic-nature of a Surrogate Key has the distinct value of identifying a row without intruding on "real" or "natural" keys-or-values that may otherwise be useful Alternate Keys.

To me...it just seems natural - but maybe I am wrong.

Where I happen to be at the moment...they argue this good for "core" Entity tables, but is completely incorrect for Many-to-Many (relationship) tables.  While I will happily to oblige the customer...I disagree.

Thoughts behind my approach are:

A primary key value must be unique
The primary key should be as compact as possible
Primary key value should be stable
Primary Key identifies a tuple...not the data INSIDE the tuple

MY QUESTION: Which is either correct or better?  And Why?
- Please provide reasoning etc.

NOTE:
Please Ignore the ID column naming in the samples below...that is how they do it here.

A Many-to-Many mapping table must have a unique index on the foreign key columns.  And should normally have an additional unique index on the foreign key columns in the reverse order.

So while adding an additional synthetic key doesn't do any great harm, it's just not very useful.


Inserting into table depending on conditions I'm trying to insert data into table but depending on the content it is supposed to be done different ways. My question is whether there exists such function that will work like follows:

WHEN Condition1 THEN INSERT INTO data1 SELECT * FROM table1

WHEN Condition2 THEN INSERT INTO data2 SELECT * FROM table2

...

ELSE SELECT 'done'

You should use IF...ELSE (Transact-SQL), ther is the complete example below:

declare @conditional_table table(col int);
insert @conditional_table values(1), (3), (4);

declare @data1 table(col varchar(100));
declare @data2 table(col varchar(100));

declare @table1 table(col varchar(100));
insert @table1 values('table1');
declare @table2 table(col varchar(100));
insert @table2 values('table2');

declare @cnt int = 0;
--WHEN Condition1 THEN INSERT INTO data1 SELECT * FROM table1
-- let's say Condition1 is "there is 1 in @conditional_table"
if exists(select * from @conditional_table where col = 1)
begin
    insert into @data1 select * from @table1;
    set @cnt = @cnt + 1;
end;

--WHEN Condition2 THEN INSERT INTO data2 SELECT * FROM table2
-- let's say Condition1 is "there is 2 in @conditional_table"
if exists(select * from @conditional_table where col = 2)
begin
    insert into @data2 select * from @table2;
    set @cnt = @cnt + 1;
end;

--ELSE SELECT 'done'
if @cnt = 0
select 'done';

-------------------------
-- there was no 'done' selected because @count > 1, some conditions were true
select * from @data1; -- condition1 was true, 'table1' will be returned
select * from @data2; -- condition2 was false, no data is returned


Postgres 9.2 - postgres - FATAL: XX000: relation mapping file "global/pg_filenode.map" contains incorrect checksum We have a ~400GB Postgres database  (v9.2.6) (running on CentOS 6.5) that had a problem with free space and with every command (ls, pwd etc) we were seeing a segmentation fault error.  We were able to read the disk and so we copied the pg_data directory and rebuilt the server.  In an attempt to place the pg_data back and start the Postgres service we saw the error:

postgres - FATAL:  XX000: relation mapping file "global/pg_filenode.map"
contains incorrect checksum

I know the easiest answer would be restore the latest backup.  Well, as you would know it, the last pg_base_backup was just over 3 weeks ago and we only retain 1 weeks worth of WAL logs.  So I do not have enough logs to bring the backup up to date.  I know this is a flaw here, I know I learned some lessons.

I tried to use the pg_filenode.map file from the 2 week old backup, but it resulted in the same checksum error.   I will note that a FULL VACUUM was done over this past weekend on one of the larger tables but completed before the failure.

Is there any chance I can recover from this error? Any ideas on possibly reading some of the smaller table data / schema / functions / views to help me in the rebuild process of a new clean slate database.

Before I did anything, I would make a copy of the cluster as is and keep it safe, following the directions here:

https://wiki.postgresql.org/wiki/Corruption

Once you have your master copy safe, you might try a fresh initdb in another directory, and copy over the pg_filenode.map file, as suggested here, and see if that helps start your database:

http://tapoueh.org/blog/2013/09/16-PostgreSQL-data-recovery

If that doesn't work, or you encounter more errors, I would try pgsql-general@postgresql.org for more suggestions, or engaging a PostgreSQL consultancy to try and recover your data.

If it does work, I would do a pg_dump immediately, then initdb a new cluster on the new machine, and reload the pg_dump, just to make sure there isn't anything else lurking that could cause problems.

My sympathies on your issue, and best of luck getting it resolved.


Performance benefits of multiple .ndf files Is there a benefit to having multiple mdfs with SSDs? I assume that with multiple cores there is, since there is less contention?

You should never have more than one .mdf for a database.
SQL Server doesn't care about the file extension. Best practice, though, is to have a single .mdf for the one and only primary file (boot file for the database) and .ndfs for the other files.
I understand that the question is really about having several data files (one .mdf and x number of .ndf), but I wanted to make the above clear first.
As for contention, you might be thinking of the tempdb, and how we have several data files for the tempdb. The contention we talk about here is when SQL server needs to find free space, and needs to look at page number 1,2 or 3 at the beginning of the file to find out where there is free space.
It need to protect from other doing the same thing at the same time so ot places a "latch" on the page, like a lock. Having more files allow more than one thread find free space simultaneously.
So the question is whether your particular load is limited by one data file when it need to find free space? How common is it that you have several threads that at the same time need to find free pages/extents? Not nearly as common as it is for tempdb, I would bet.
You can of course monitor this by grabbing snapshots of what we're waiting for to see if you are waiting for latches on these pages (1, 2, 3) for your database. Or use XEvents to find out.
There's no thread limitation per file in SQL Server. I.e., SQL Server can push the disk subsystem as hard with one file as with several files. But of course, your disk subsystem capacity might increase with several files (more I/O channels etc).
Then there is the topic about having more than one file group, but I'm not addressing that here since that wasn't in the question.


how to change this dynamic rows to columns I have following columns (it is dynamic, more rows in table with same ID).Type can be duplicate.

+--------------+---------+------------
| ID           | TYPE    | VALUE     |
+--------------+---------+------------
|            1 | A     | one         |
|            1 | B     | gas         |
|            1 | C     | age         |
|            1 | D     | two         |
|            1 | E     | gas         |
|            1 | F     | age         |
|            2 | G     | env         |
|            2 | H     | mas         |
|            2 | I     | sat         |
+--------------+---------+-----------+

I am trying to get following output

ID | A  | B | C | D | E | F | G| H | I
1    one  gas age two gas age null null null
2    null null null null null env mas sat

How it is possible in Mysql

Following is the solution for static version:

SELECT id,
group_concat(DISTINCT IF(type='A',value,null)) 'A',
group_concat(DISTINCT IF(type='B',value,null)) 'B',
group_concat(DISTINCT IF(type='C',value,null)) 'C',
group_concat(DISTINCT IF(type='D',value,null)) 'D',
group_concat(DISTINCT IF(type='E',value,null)) 'E',
group_concat(DISTINCT IF(type='F',value,null)) 'F'
group_concat(DISTINCT IF(type='G',value,null)) 'G',
group_concat(DISTINCT IF(type='H',value,null)) 'H',
group_concat(DISTINCT IF(type='I',value,null)) 'I'
FROM test
GROUP BY id;

To implement dynamic version you must implement it via Stored Procedure or  from your programming language implementing solution based on your logic.


Join or Replace colum value based on query I have this query:

SELECT h.name, i.name, i.delay, i.delay_flex, i.status, i.templateid, i.params, i.description
FROM items as i
LEFT JOIN hosts as h
ON i.hostid = h.hostid
AND i.type = 11
AND i.status = 0
WHERE h.name LIKE "ABC%" OR h.name LIKE "123%"
ORDER BY templateid DESC
LIMIT 1;

Result is:

h.name | i.name | i.delay | i.delay_flex | i.status | 43206 | i.params | i.description |

I need to replace value 43206 with this query:

SELECT name
FROM hosts
WHERE hostid = (SELECT hostid
                FROM items
                WHERE itemid = (SELECT '43206'));

The result is: Template XPTO

And expected result is:

    h.name | i.name | i.delay | i.delay_flex | i.status | Template XPTO | i.params | i.description |

You can use an scalar subquery in this way:

SELECT    h.name,
          i.name,
          i.delay,
          i.delay_flex,
          i.status,
          (SELECT name
           FROM hosts
           WHERE hostid = (SELECT hostid
                           FROM items
                           WHERE itemid = i.templateid)),
          i.params,
          i.description
FROM      items as i
LEFT JOIN hosts as h
ON        i.hostid = h.hostid
AND       i.type = 11
AND       i.status = 0
WHERE     h.name LIKE "ABC%"
OR        h.name LIKE "123%"
ORDER BY  templateid DESC
LIMIT 1;

Or you can add both tables hosts and items to the JOIN section:

SELECT    h.name,
          i.name,
          i.delay,
          i.delay_flex,
          i.status,
          h2.name,
          i.params,
          i.description
FROM      items as i
LEFT JOIN hosts as h
ON        i.hostid = h.hostid
AND       i.type = 11
AND       i.status = 0
LEFT JOIN items as it
ON        it.itemid = i.templateid
LEFT JOIN hosts as h2
ON        h2.hostid = it.hostid
WHERE     h.name LIKE "ABC%"
OR        h.name LIKE "123%"
ORDER BY  templateid DESC
LIMIT 1;


Select value in group based on every/any conditions I have a table entity_translations, which relate O->M to string_translations via entity_translation_fields. Each row in string_translations has an additional status that can be obtained via a join with current_string_translation_status.
I need to add an additional column to the result of a select from entity_translations that will be determined based on the following conditions:

If all string_translation records that correspond to an entity_translation record have status = 'READY', then that entity_translation gets status 'READY'.
Else, if any string_translation records that correspond to an entity_translation record have status = 'FAILED', then that entity_translation gets status 'FAILED'.
Else, if any string_translation records that correspond to an entity_translation record have status = 'IN_PROGRESS', then that entity translation gets status 'IN_PROGRESS'.
Else, that entity translation gets status 'ADDED'.

What I want
Given schema and data in this fiddle, I would like to have the following result:
|  id | entity_id | entity_type |      status |
| --- | --------- | ----------- | ----------- |
|   1 |         1 |        post |      FAILED | <--- Because status for string translation 2 is "FAILED"
|   2 |         2 |        post | IN_PROGRESS | <--- Because status for string translation 5 is "IN_PROGRESS"
|   3 |         3 |        post |       READY | <--- Because status for string translation 6 is "READY"

Here's the query I used to get the data that I would like to aggregate into the above desired result:
SELECT *
FROM `entity_translations`
JOIN `entity_translation_fields`
    ON (`entity_translation_fields`.`entity_translation_id` = `entity_translations`.`id`)
JOIN `string_translations`
    ON (`string_translations`.`id` = `entity_translation_fields`.`string_translation_id`)
JOIN `current_string_translation_status`
    ON (`current_string_translation_status`.`string_translation_id` = `string_translations`.`id`)
ORDER BY `entity_translations`.`id` ASC, `string_translations`.`id` ASC;

Markdown tables weren't rendering for some reason, so I just added it as code.
What I tried

One way could be to join entity_translation with string_translation (and its status view), then group by entity_translation's PK. But I don't know how I could then determine the values from the status column that is not in GROUP BY. If ANY() and ALL() were aggregate functions, I could use them in multiple nested IF conditions; but they're not.
Perhaps, another way is to use a subquery to add a column to string_translation. But I don't understand how to then use ANY or ALL with yet another subquery.

Here is the whole setup. It has some extra things that are not relevant to this current problem. The query that is relevant is the one that says the following on top:
-- Get data for all entity translations, including their current status <----- PROBLEM

If I understand the task correctly, then
SELECT subquery.id,
       subquery.entity_type,
       subquery.entity_id,
       CASE WHEN SUM(subquery.status != 'READY') = 0 THEN 'READY'
            WHEN SUM(subquery.status = 'FAILED') > 0 THEN 'FAILED'
            WHEN SUM(subquery.status = 'IN PROGRESS') > 0 THEN 'IN PROGRESS'
            ELSE 'ADDED'
            END status
FROM (SELECT et.id, et.entity_type, et.entity_id, stl.status
      FROM entity_translations et
      JOIN entity_translation_fields etf ON etf.entity_translation_id = et.id
      JOIN string_translations st ON etf.string_translation_id = st.id
      JOIN string_translation_log stl ON stl.string_translation_id = st.id
      JOIN (SELECT string_translation_id, MAX(changed) changed
            FROM string_translation_log
            GROUP BY string_translation_id) stl1 ON stl1.string_translation_id = stl.string_translation_id
                                                AND stl1.changed = stl.changed
      ORDER BY et.id, etf.string_translation_id, stl.changed ) subquery
GROUP BY subquery.id,
         subquery.entity_type,
         subquery.entity_id;

fiddle


Configured values different to Running values, even after full restart I am trying to change the FILESTREAM Access Level property on a SQL Server Express database. The Configured values section of the Advanced tab of my Server Properties dialogue box looks like so:

The Running values section looks like this:

This is before and after a full restart of the computer. Am I right in thinking that SQL Server Express just doesn't support this option, or could there be another reason this is happening?

Thanks

Jakub Hromadík originally wrote in a comment:

  This just happened to me. No matter what I did, the running value was always Disabled. What finally did it for me was repairing the SQL Server installation. I'm running SQL Server 2016 SP1

This also worked for me.

Note also:

From the documentation, filestream access level Server Configuration Option

  Before this option has any effect, the Windows administration settings for FILESTREAM must be enabled

and be sure to follow all the steps in How to: Enable FILESTREAM


drop datafile in RECOVER status I have a datafile in a recover state:
SQL> select status from v$datafile where file#=201;

STATUS
---------------------
RECOVER

I'm not really sure how it was damaged, looks like something went wrong right after its creation time.
SQL> select creation_change#,creation_time,checkpoint_change#,checkpoint_time,last_change#,last_time
from v$datafile where file#=201;

CREATION_CHANGE# CREATION_TIME       CHECKPOINT_CHANGE# CHECKPOINT_TIME      LAST_CHANGE# LAST_TIME
---------------- ------------------- ------------------ ------------------- ------------- -------------------
    141311481693 29/08/2019 16:54:57       141311481694 29/08/2019 16:54:57  141311481738 29/08/2019 16:55:15

I've checked every table in a tablespace this file belong to and everything seemd OK. First I tried
SELECT /*+ FULL(t) */ count(*) from table_name; for every table, then I tried ANALYZE TABLE table_name VALIDATE STRUCTURE;. No errors.
I'm pretty sure there is no option to drop an offline/recover datafile. Or is there?
I have one thought in my mind: create a new tablespace, move all objects there and then recreate the TS with a failed datafile in it. Then move everything back. Is this OK thing to do? Or am I missing something?
Please advice.

I moved all my data to new tablespace and then recreated old one and moved everything back. Everything seems in order, no data loss.


Database migration - Simply change connection string? We are migrating our database (MySQL/MariaDB) onto a dedicated server.  Previously, our web server hosted the database.  I've already set up master-slave replication between the web server and the new database server, and the actual replication is up-to-date (0 Seconds_Behind_Master).

It would seem that the only remaining step is to change the connection string in our app code to connect to the new database server instead of the old DB hosted on the web server.  Is this correct?

Of course, this assumes that we have not enabled the flags on the database server to prevent writes to the slave (we did not enable this flag, so we should be good there).

To ensure writes from the Master are complete, do the following on the Master:

SET GLOBAL read_only = 1;
FLUSH TABLES;
FLUSH LOGS;

Go to the Slave and run the following:

STOP SLAVE;
RESET SLAVE ALL;

Then, make sure there is no Slave setup

SHOW SLAVE STATUS\G

If it comes back with nothing, setup the new connect string and you're done !!!

GIVE IT A TRY !!!


Can't default date to CURRENT_TIMESTAMP in MySQL 5.5 I am not able to set Current_timestamp as default value. My Mysql version is 5.5.47.

Query is

ALTER TABLE `downloads` ADD `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ;

While it is working fine on my local DB with mysql V5.6.56.

From the MySQL 5.5 manual:

  You cannot set the default for a date column to be the value of a
  function such as NOW() or CURRENT_DATE. The exception is that you can
  specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

Therefore, what you want to achieve will work in MySQL 5.5 if you add a TIMESTAMP column instead of a DATE column.

The changes in 5.6.x that allow the functionality are documented here, and I'll quote the relevant summary for completeness:

  As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically
  initializated and updated to the current date and time (that is, the
  current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and
  for at most one TIMESTAMP column per table.


How do I truncate performance_schema tables on a read replica? On occasion, you have to truncate events_statements_summary_by_digest, when it's hit the row limit. On a write master, this is easy, but on a read replica, there doesn't seem to be a way to do it. The read-only enforcement extends to the performance_schema tables. Since the data are created locally, it seems to me that there'd be a way that I could exempt those tables (or even the entire performance_schema DB) from the read-only enforcement?

I have already set the performance_schema_events_statements_history_long_size to 10000, which is getting pretty large. I could set it larger (well, if it weren't read-only), but the size of that table affects overall system performance, and I'd rather not just crank the limit to something obscene - especially when so many of the entries in the history haven't been used in a mont or two. It's better to toss out the garbage.

Connecting to the Slave as root (or other SUPER user) bypasses readonly, thereby allowing you to do that 'truncate'.

(Caveat: this Answer is theoretical; I have not tested it.)


Indexed view doesn't exist on the subscriber I am configuring transactional replication in SQL Server. The subscription is configured as push from 2008R2 publisher (distributor is the same server) to 2012 subscriber.

The object I want to replicate is an indexed view. The base tables exist only on the publisher.

The replication fails due to the following error:

  Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber

It is true that the view doesn't exist in the subscription database. How can i create it without the base tables?

The clue to this is in the error, which is telling you that it can't create the view on the subscriber, because the subscriber is missing tables and/or columns that the view relies on.

You can't create the view without the base tables, so make sure you replicate the tables and/or columns involved in the view to the subscriber too, and then the view itself can be replicated.


Dropping historic Partitions that are no longer required while in P2P replication The Company I work for has a set of databases (largest just below 1Tb) on different servers - 2 in the US, 2 in Europe.

We run full peer-to-peer replication per database between the 4 nodes - so they can all take transactions (Insert/Update/Delete) and all have the data that the other nodes have gathered (within a variable latency - worst connection is on average about 30-40 secs).

The largest database carries data from early 2008 to today. All of this data is further replicated to Reporting nodes which hold ALL data.

I need to remove data at the Transactional nodes, up to 2013, to remove the Drive space deficit at the Transactional nodes, and therefore historic data will only be available at the Reporting nodes.

What is the best way to do this? Data is relatively manageable in that it is well partitioned (monthly - by partition, and then Yearly into separate Files/Filegroups). However, there is the problem of not being able to drop the Partitions while they are involved in replication and reading up on Partition switching - this is not allowed with that either. (Switching Partitions Prerequisites - Point 18)

As a full Production Environment I am trying to avoid anything that will affect replication - including resynchronisation (lot of data to resync, over large distances).

Does anyone have any good suggestions for how to perform this task?

So, no answers from here, but after a certain amount of discussion and thought, I came up with a plan some months ago.

I will make this answer concise for this forum (you may not agree that I have!), to try to assist anyone that needs to perform a similar task in the future, feel free to ask questions if I miss anything out - though the method is straight-forward.

So, the primary concern is to remove the data without significant impact on Production traffic at the nodes we are replicating to/from. The easiest way to do this is to segregate a node that you want to work on, removing the data from that node whilst leaving all others unaffected (including the Reporting nodes).

Best way to do this (remember you can't drop partitions and any/most operations are replicated and therefore create a large amount of traffic and a large amount of row changes), is to create a new SP and setup a Publication around this one SP. It should therefore be available at all nodes. The important bit is to set Replication to replicate the Execution of the SP - NOT the result (i.e. replicate the EXEC Sp_delete call NOT the delete WHERE ID = 1, delete WHERE ID = 2 - row level changes). This is set in Right click your new Publication (before setting up the other nodes in the Topology) > Properties > Articles > Click the SP_Delete you have setup > Article Properties button > Set Properties of Highlighted Stored Procedure Article > Replicate line = EXECUTION of the stored procedure. Complete your P2P topology.

BUT MHSQLDBA, you might be saying, that's just going to separately delete the rows at every node via the SP. - This is why you set the SP to only do the deletions:

IF @@SERVERNAME='The current server you want to affect'

Follow it with your delete procedure.

Thus, when this EXEC call is picked up at the server(s) that you don't want to perform the Deletes, it will be ignored as @@Servername will not equal the Server you have selected.

You may think - why not just create an SP at only the Server you are interested in and run that? - this is because if you do this, replication will break down the changes into how they affect the Article (table) rows and replicate the actual changes - you have to replicate the SP so that you can specify that the EXEC of the SP be replicated rather than the resultant changes.

This is the suggested order of events in my opinion/experience:

Create SP with delete code that specifies that it will only run the delete code if @@SERVERNAME = your desired server
Setup a new publication that Replicates this 1 SP with the Replicate = Execution of Stored Procedure within Article Properties
Run the SP at your desired server and be happy that you haven't brought down the whole estate with thousands of replicated DELETE commands

Points of Note:

This is still a laborious task. By using this method you have diminished your effect on all servers apart from the one you are working on. You have not diminished the workload for you, in fact you have made it worse - you are going to have to run this same SP at each node (with the IF line changed to the server you are targeting), effectively increasing the work you have to do, by the number of servers you have to affect. It is massively safer though as you will have minimal effect on all other nodes (I'm presuming you've failed traffic away from the node  you're working on of course!)
By using this method, you have created inconsistency between your nodes - you really need to be sure that the data you're removing IS NOT going to change before you can  finish performing the same operation on all nodes that require work. If a row you have deleted at 1 node is changed within the rest of the Estate you are going to end up with consistency errors.
You are likely going to put your normal replication expected SLA's behind by the amount of time it takes to perform the deletes at the node you're working on (I highly recommend you read up on batching the deletes) - therefore you need to be aware that once the operation completes, you won't have the node back in action until normal replication has caught back up after your Delete operation's locks have released. If you are replicating over high latency lines, I seriously suggest you check out using PULL agents instead of PUSH - it makes a humongous difference.

There is probably a better way of moving the data away in the SP than using delete - maybe moving it to another table that is not involved in replication and then dropping the 'new' table - or the reverse, if the data you want to keep  is less than the amount to delete, move the data you want to keep to a new table, drop the old one then rename your new table - there is lots of advice out there from these perspectives - I work in an environment where it was easier to fight for deletion than promote a concept that some personnel won't understand, so I'm describing the painful but basic way.

DISCLAIMER: All of the above is dangerous. If done hastily without appropriate forethought, you can seriously mess up a replication topology, your Company's data and probably your employment. Please take the above method and develop your own battleplan - create a test environment to prove the concept, test test and retest, do not take this task lightly. With enough consideration you will achieve your task - but it's not worth doing on Friday afternoon after a couple of lunchtime beers. Do it right, do it once (for real-test as much as you can), do it properly.

I hope this helps someone else. - I'm adding this bit as it is what I would have searched for if I wanted this answer:

Delete large amount of data from a Peer-to-Peer replication topology


Grant PG permissions to not existing tables I would like to have a dedicated user in the schema, that can have access to tables/objects by pattern like foo* (starts with foo).

He should have permissions to create new tables like foo_table1 and perform insert/update on existing tables.

It is possible with MySQL, but not sure about PostgreSQL (easily, without stored procedures or other magic).

Is there a way to achieve this?

Without claiming to address your 'by pattern' requirement, granting privileges on not-yet-extant objects is addressed by Alter Default Privileges

Create schema foo;

Grant Usage On Schema foo to foouser;

Alter Default Privileges In Schema foo
  GRANT SELECT, INSERT, UPDATE, DELETE, Truncate ON TABLES To foouser;

Alter Default Privileges In Schema foo
  Grant EXECUTE on FUNCTIONS To foouser;

Or simply

Alter Default Privileges In Schema foo GRANT All Privileges To foouser;

As @a_horse_with_no_name observes, the schema is the usual way to group objects for privileges. Using schemas for security is much less error prone than using naming patterns, so I think we should recommend you go this way anyway!

foouser only needs to use a dot in their statements:

create table foo.mine ( id int)
select * from foo.mine

PS Why are name-patterns an error-prone way of doing security?

Because you never know whether a year later you're going to

create table food_for_top_secret_party

which matches pattern foo*, and so can be not only read, but also accidentally dropped, by foouser.


MySQL - INSERT INTO ... SELECT ... UNION I'm having an issue with a fairly complex query where I need to store the ids from one table (based on heavy criteria) into a narrow, alternate table for future reference, but I keep running into a bad sql construct error.

If I run the following query by itself it works fine:

(SELECT DISTINCT a.id FROM accounts AS a
WHERE a.createdate >= date_sub(now(), INTERVAL 1 YEAR)
AND a.email NOT LIKE '%gmail.com')
UNION DISTINCT
(SELECT DISTINCT a.id FROM accounts AS a
WHERE a.createdate < date_sub(now(), INTERVAL 1 YEAR)
AND a.email NOT LIKE '%yahoo.com') AND a.email NOT IN
(SELECT email FROM inactive_clients));

However, if I try to run same query with like this:

INSERT INTO my_backup_table (account_id) VALUES ($aboveQuery);

I get the following error:

MySQL server version for the right syntax to use near 'UNION DISTINCT (SELECT DISTINCT a.id FROM accounts at line 1

I tried any number of parenthetical permutations and still received the same error.

You cannot use the INSERT INTO tblname (...) VALUES ... syntax

You must use the INSERT INTO tblname (...) SELECT ... syntax

Try the Following:

INSERT INTO my_backup_table (account_id)
SELECT id FROM
(
    (SELECT DISTINCT a.id FROM accounts AS a
    WHERE a.createdate >= date_sub(now(), INTERVAL 1 YEAR)
    AND a.email NOT LIKE '%gmail.com')
    UNION DISTINCT
    (SELECT DISTINCT a.id FROM accounts AS a
    WHERE a.createdate < date_sub(now(), INTERVAL 1 YEAR)
    AND a.email NOT LIKE '%yahoo.com') AND a.email NOT IN
    (SELECT email FROM inactive_clients))
) A;


What is the most convenient indexing strategy for two foreign key constraints? Which of the two following options is more convenient for indexing the  relevant foreign keys?

Option A:

Create Table table3(
  t3_id int not null auto_increment,
  t1_id int not null,
  t2_id int not null,
  primary key (t3_id),
  index IX_index (t1_id, t2_id), // this is my concern
  constraint FK_t1 foreign key (t1_id)
  reference table1(t1_id),
  constraint FK_t2 foreign key (t2_id)
  reference table2(t2_id));

Option B:

Create Table table3(
  t3_id int not null auto_increment,
  t1_id int not null,
  t2_id int not null,
  primary key (t3_id),
  index IX_t1 (t1_id), //this is my concern
  index IX_t2 (t2_id), //and this
  constraint FK_t1 foreign key (t1_id)
  reference table1(t1_id),
  constraint FK_t2 foreign key (t2_id)
  reference table2(t2_id));

I'm using InnoDB. I don't have a broad understanding how a btree index works but, as far as I know, the first table will save the indexes on a single area while the second will not (correct me if I am wrong).

What are the pros and cons of defining a single- or a multi-column index?

The create table script 2 is correct.

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.


How does a memcached bucket behave when fully loaded? What is the default ttl of a key in memcached bucket?

Also, what happens when a bucket is full?

I created a 256MB RAM Bucket and tried to overload it by sending infinite set queries till error is thrown.
But to my surprise, it keep accepting inputs, but total items now fixed to 2.8M with 204M RAM used.

Meanwhile, when I tried to fetch the first key it says key not found. Moreover, I keep calling a specific key infinitely it doesnt get expired.

So how is it exactly working? Please guide.

If you do not set a TTL, the object will never expire.

The latter part of your questions sounds like you are running into memcached's cache eviction strategy where it will throw out of the objects that have least recently been used (LRU) to make room for newer items.


Null column for username and password when using Login with *social network* I want to know what are the conventions for columns like username and password when your application supports login with a third party, like login with facebook for instance.

Given the fact that when you Log with a 3rd party, you don't really need to specify a username, you'll just specify the 3rd party user id, in database design, you can expect either a blank field or a null value.

Since usernames are UNIQUE, I'm guessing going for nullable is the way to go, since it allows multiple null columns.

The real issue is that the user needs to specify either a username OR a 3rd party id. Which means both columns can be null, but not at the same time

So what's the convention for handling this design pattern?

I'd rather separate user and authentication mechanism/provider. Application may allow more than one authentication provider (say , logins with facebook, openid, etc). Adding columns/constraints every time you add a new authentication option doesn't sound like a very good idea to me (even though that doesn't happen often, such an action usually requires lots of changes on application side).
Something like

user(user_id);
authenticator (authenticator_id, name, description, authentication_type);
user_authentication(user_id, authenticator_id, authentication_identity, authenticator_token) ;

seems more general to me.

** In case of username/password authentication you can keep username in authentication_identity and password (surely not plain text) in authenticator_token. If you feel the pair authentication_identity/authenticator_token doesn't cover all the cases you can add couple more tables that store information specific to particular authentication_type


How do I move a MySQL data directory to an external hard drive? I tried moving MySQL data directory to an external Hard disk (which has been formatted using NTFS).
I followed the steps given in the answer to the question How to change MySQL data directory?

Stop MySQL
Copy MySQL data directory to new location
Edit the MySQL configuration file (update datadir variable).
Update the new path in apparmor
Reload apparmor
Start MySQL (/etc/init.d/mysql restart)

But at the 6th step, when I restart MySQL it's failing. When I look into /var/log/mysql/error.log I get the following message:

2015-05-17 12:07:05 4317 [Warning] Can't create test file /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/    neo.lower-test
/usr/sbin/mysqld: Can't change dir to '/media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/' (Errcode: 13 -     Permission denied)

What should I do to fix this problem?
I guessed that it is because the mysql user does not have permissions to write to the directory on the external hard drive. So I tried to change the ownership of the entire path (chown mysql:mysql -R /media/prasanth/SeagateExpansionDrive/MWDatabase/mysql/). But it didn't get reflected.
So I tried mounting that partition again with sudo mount -t ntfs -o rw,auto,user,fmask=0022,dmask=0000 /dev/sdb1 /media/prasanth/SeagateExpansionDrive/ with no luck.
Setup Details:
Host - Ubuntu 14.04
External Drive: NTFS FileSystem, 4TB single partition
MySQL: mysqld  Ver 5.6.19-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64

The new directory must be accessible by mysqld.  In step 2, did you copy all ownership and permissions?
But probably you started mysqld as a user without permissions to the new directory?  Note that it can't even do a cd:  "Can't change dir".
Check for SELinux or AppArmor being in the way.


Database design for part-number generator I'm working on a generator for part numbers, where each space will have a meaning and each space will have a number of possible values. What is a "correct" database design for this?

Example part number layout: A B C D - ####(0000-9999)

A = Fabric backer (set length of 1 digit, 5 different options)

B = Stitching type (set length of 1 digit, 10 different options)

C = Stitching color (set length of 1 digit, 3 different options)

D = Trim color (set length of 1 digit, 10 different options)

#### = can be any 4-digit long number (allows preceding or following zeroes) that corresponds to a fabric color that we use (not all numbers between 0000-9999 are valid, but it isn't in serial order, either, as there are gaps)

We will be creating new part number generators in the future, and they will have different layout, different possible values, and different lengths. I was thinking of doing something like this:

TABLE: GENERATOR (the overall instance of generator)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
id, int (autoincrement), 5
name, str, 100
is_active, bool, 1
notes, text, 999

TABLE: GENERATOR_VALUESET (connects the generator with the spaces for valuesets, basically sets the space order and valueset used for each space)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
gen_id, int, 5 (correlates to GENERATOR.id)
valueset_id, int, 5 (correlates to VALUESET.id)
ordinal, int, 3
is_active, bool, 1

TABLE: VALUESET (collection of associated values to form a coherent valueset relevant to a particular product option)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
id, int(auto increment), 5
name, str, 100
is_active, bool, 1

TABLE: VALUESET_VALUE (connects values to the valueset and sets order)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
valueset_id, int, 5 (correlates to VALUESET.id)
value_id, int, 10 (correlates to VALUE.id)
ordinal, int, 3
is_active, bool, 1

TABLE: VALUE (the actual values, includes a displayed value as well as a long-format name)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
id, int(auto increment), 10
name, str, 100
displayed_value, str, 5
is_active, bool, 1

First, I don't see any problem with doing this in the db.  In fact we are moving this direction with LedgerSMB.  The big caution is that you want to be prepared for business rule changes.  Of course your old partnumbers will be stable.  but SKU's are stable for a long time usually unless you are Frito Lay and then it just makes trouble for everyone else (they change UPC's it seems every time a word on their packaging changes).

What we've done in LedgerSMB is to essentially store a template, and then we preprocess and increment.  That isn't quite what you are looking at doing, but it may help to see how we have looked at this in the past and where we are going.

Our templates look something like:

<?lsmb field_name ?>somechars<?lsmb another_field?>0001102

Leading zeros are preserved on increment.  Inside the db, we have a stored procedure which increments the number, keeping leading zeros and passes the rest up to the application.  I don't see how we can do full preprocessing of this sort using invoice numbers and the like but for partnumbers the goal eventually is to move all that preprocessing into the database.

Now in your case, you probably want to have the parts tied together for easier management.  I understand the arguments against this.  I would suggest putting all these fields you want to aggregate in the table representing the parts.  Then you can integrate these with a user defined function or something (that's how I'd do it in PostgreSQL) which you can use to populate a missing part number.

In this case your table structure will look very different:

product_group(id-- 0000-9999, etc)
fabric (id ....)
etc....
part (partnumber, productgroup_id, fabric_id, etc)

I don't see another sane way of doing it.  You are going to have to track each of these things you want to use separately, put them in the parts table, and then generate your partnumber based on that.


Definitive list of steps for SQL Server baseline testing? Before running a performance test / baseline for an app that uses SQL Server, I want to be able to set the instance to a "clean" state, without restarting the instance. There are steps I tend to follow, but I want to build a definitive list that is in the correct sequence, and has no redundant steps.

Does this list of steps accomplish setting SQL Server to a "clean" state?

Is the sequence logical / correct?

Are there any redundant steps?

CHECKPOINT              -- Write all dirty pages

DBCC DROPCLEANBUFFERS   -- All should be clean after checkpoint?

DBCC FREEPROCCACHE      -- Clear the plan cache

DBCC FREESYSTEMCACHE    -- Is this necessary after FREEPROCCACHE?

DBCC FREESESSIONCACHE   -- May not be necessary if distributed queries aren't used, but want to catch all scenarios

EXEC SP_UPDATESTATS     -- Refresh stats

'BEGIN TESTING!'

First, I'd step back and ask what measurements you plan to collect during the test.  If you're counting logical reads by query, for example, then you don't need to free the cache.  I'm a big fan of using logical reads because it's independent of whether the data is cached or on disk - and in production, it's hard to guess whether a query's data will be cached or not (unless you cache the entire database in memory).  If you tune to minimize logical reads, then the app will go faster whether the data's in cache or not.

Next, I'd question what's changing between runs.  For example, by running EXEC SP_UPDATESTATS in each database as you've suggested, you're going to resample stats for tables that have been updated.  However, unless you're updating statistics with fullscan, you're getting random rows from the table - that's not too repeatable, and I don't think you really want to do that.  Instead, you might want to restore the databases between each run so that you're always testing exactly the same data.  If your tests are doing inserts/updates/deletes, they might have different performance profiles on each run if you're not restoring the database (because they're adding/changing data, plus changing statistics on the data) - and even worse, you could have queries fail if they try to insert repeated data for the same values.


Insert into table select - Replication lag - Percona Server 5.6 I have two MySQL instances (Percona server 5.6.31) in Master-Slave replication setup.

I have set the below configuration:

ROW based replication is set.
Transaction Isolation is set to read-committed.

Today, there was a insert going on in my Master. It was in the format

INSERT INTO table1 SELECT * FROM table2

Table 2 has 200 million rows.

Though the number of insert records was only 5000 but the operation lasted for 30 mins. I observed replication lag during the insert operation.

I have load infile disabled due to security concerns. Hence I can't insert using that as well.

I went this article from Percona which says that this can be resolved if txn isolation is used as ROW and versions above 5.1 that this is resolved.

In what way I can make my slave to be in sync with Master in such conditions?
Why does the slave lag here?

The semantics of that statement is to do it all in a single transaction.  This may well exceed innodb_lock_wait_timeout and/or cause other operations to exceed it.

With RBR, each of the 200M rows will be separately fed through the replication stream; this is a huge amount of data (more than the table size, itself).

The Slave cannot handle any other replication during the execution of the 200M individual inserts.

What to do?  First, explain more about the situation.

Does table2 change during the operation?
Does table2 exist on the slave?
Let's see SHOW CREATE TABLE table.  (Need to see indexes, FKs, etc)
Is the schema for table1 identical to table2?
Can you switch to SBR for this one statement?  That will solve the replication stream bloat, but not the other comments.
Will it be OK to copy the rows over in chunks of, say, 1000 rows?  Each chunk being one transaction?  (That is, if there is a crash, the table may be partially copied.)  If so, adapt the tips in http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks .


Feasible to have thousands of foreign data wrappers in Postgres? We are creating enterprise-grade SaaS where we will have many 1,000s of customers. We are considering creating a database in the Postgres server for every customer to ensure enterprise-grade security/access.

Each of these customer databases will have some access to a few tables in a root database via foreign data wrappers using the postgres_fdw extension. Also highly probable that we'll have a few triggers on the local tables that are referencing the foreign tables in the root db (I'm not sure how the root database syncs with the databases which could be a scaling issue).

Will we find any unexpected issues if we have 1,000s of databases using postgres_fdw to connect to a root database in the same postgres instance?

As your comment rightly suspects, that would lead to many connections to the shared database, so that you'd probably need a connection pooler in between.

It might be better to put the data into different schemas in a single database. An alternative would be cascading logical replication to keep consistent copies of the shared data in all databases.


Filtering data from a mysql table using a date range where date format is VACHAR ('AUG-18') Sample Data

TRAINING_DATE    GENDER
AUG-18             M
AUG-18             F
SEPT-18            F
OCT-18             M
OCT-18             M
NOV-18             M
NOV-18             F

Expected Output.
Number of people trained between SEPT-18 and NOV-18

   F      M
   2      3

You should store your data differently. Storing year and month separately might be a good idea. You can do that like this:

ALTER TABLE your_table ADD COLUMN training_year int, ADD COLUMN training_month tinyint;

UPDATE your_table SET
training_year = YEAR(STR_TO_DATE(TRAINING_DATE, '%b-%y')),
training_month = MONTH(STR_TO_DATE(TRAINING_DATE, '%b-%y'));

When you don't need your TRAINING_DATE column anymore, you can drop it.

Now you could query your table like this:

SELECT gender,
       COUNT(*)
FROM your_table
WHERE training_year = 2018
  AND training_month BETWEEN 9 AND 11
GROUP BY gender;

read more about the STR_TO_DATE() function and some more here


One time database shrink catch-22 solution? I am developing a plan for SQL Server 2012 EE to properly downsize and type (nchar to char types) some needlessly Unicode nvarchar(max) fields and would like to optimize the database size as part of the downtime by doing a one time shrink. Experiments have shown a 50% allocated space savings which is 11G of data.

After reading and experimenting, it's evident that shrinking a database causes index fragmentation and rebuilding indexes causes the database to expand. A real Catch-22 situation. I don't want to leave 50% free space in the database which is 11G of disk storage in this case.

Would the following be a decent approach for a one time shrink that would allow ending up with non-fragged indexes and up to date index statistics?

o   Backup w/ verification and duplicate backups.

o   Drop all indexes.

o   Rebuild any too fat tables via copy into new tables, then drop and rename tables. This is currently working well.

o   Shrink the database leaving a reasonable amount of free space.

o   Recreate all indexes that were dropped.

o   Validate the database and check fragmentation.

Pointing out any caveats, suggestions, gotchas or alternatives to consider much appreciated.

Thx, Dave

Simple recovery model doesn't "turn off the log" - so generally, unless you're going to break any of these activities into very small chunks, just pre-size the log large enough to deal with the largest rebuilds, and don't bother messing with the recovery model.

Also, and again generally, unless your data will never grow again, freeing up the space temporarily is of little benefit (given that I don't know what you mean by "reasonable"). You're going to need to leave free space on the drive, in case the database grows again, right? Why shrink it only to have it grow again? Are you going to provide short-term rental on that space until the database grows? Are you just trying to avoid free space % alerts?

Suggestion: give these posts a good read...

Why Does the Transaction Log Keep Growing or Run Out of Space?
Sql Server - Best Practices for Growing Database Files
SHRINKFILE best practices and experience
I Need to Shrink My Database - I just freed a lot of space
Reducing the size of a database
When is it OK to shrink a Database?
Database running out of space


Can I bulk insert into an empty page-compressed table and get full compression? I have a lot of large tables (around 10 million wide rows) which need to be regularly loaded into SQL Server 2016 for read-only reporting. I would like these tables to be as small as possible on disk, and this matters more than performance improvements in either loading or querying.

Here is what I have been doing for the tables which require no further indexing:

Create the table with DATA_COMPRESSION=PAGE.
Use bcp to bulk insert the data from a flat file into the new table.

Column types in the tables are varchar (never more than 512, not max), float, tinyint, or date (not datetime). All columns are created as nullable and no primary or foreign keys are defined -- they don't matter for the querying and the tables are never updated directly. Default collation on everything is SQL_Latin1_General_CP1_CI_AS.

When I do this, I can see in sys.allocation_units that page data compression has been applied to the heap and I can see in sys.partitions that the fill factor is correctly 0 (100%). Since the tables are much smaller than uncompressed tables would be, I thought the compression was accomplished.

However, if I then rebuild with the same option DATA_COMPRESSION=PAGE, the supposedly-already-compressed table gets about 30% smaller! It looks like it's going from about 17 rows per data page to 25 rows per page. (Only once, though. Rebuilding again after that doesn't make it any smaller than the first rebuild did.)

The questions

So my questions are: (a) what is going on here? and (b) is there a way to get this extra-small compressed size directly as I load the table without having to rebuild after the data is loaded?

@HandyD is  entirely correct, I only want to highlight some other methods to get compression while inserting into a heap.

From the same document

  When a heap is configured for page-level compression, pages receive
  page-level compression only in the following ways:

  Data is bulk imported with bulk optimizations enabled.
  Data is inserted using INSERT INTO ... WITH (TABLOCK) syntax and the    table does not have a nonclustered index.
  A table is rebuilt by executing the ALTER TABLE ... REBUILD statement    with the PAGE compression option.

According to this, you could leverage minimally logged bulk inserts or use INSERT INTO ... WITH (TABLOCK)  to get PAGE compression without having to do rebuilds.

  (a) what is going on here? and (b) is there a way to get this
  extra-small compressed size directly as I load the table without
  having to rebuild after the data is loaded?

There are rules to get PAGE compression when inserting into a heap, add -h "TABLOCK" to your bcp command to get compression.

ROW compression works without these prerequisites and is the least amount of compression used in below examples, thanks @DenisRubashkin for pointing that out!

Testing

Example start data & BCP out command

--Tested on SQL Server 2014 SP2

CREATE TABLE dbo.CompressedHeap_Source( Val varchar(512),
                                 Datefield Date,
                                 Tinyfield TinyINT,
                                 Floatfield float)
WITH (DATA_COMPRESSION = PAGE);

INSERT INTO dbo.CompressedHeap_Source
(
Val,Datefield,Tinyfield,Floatfield)

SELECT 'Bla',cast(getdate() as date),1,1.2412
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;

--bcp TEST.dbo.CompressedHeap_Source out E:\Data\HeapData.bcp -c -T

The ROW compressed and Uncompressed size

The  data size is at  132272 KB when doing a standard insert into the heap, this is ROW compressed but not PAGE compressed.

The data size without any compression is ~ 176216 KB for our test.

exec sp_spaceused 'dbo.CompressedHeap_Source'

name                    rows                    reserved    data      index_size    unused
CompressedHeap_Source   6365530                 132296 KB   132272 KB   8 KB    16 KB

INSERT INTO ... WITH TABLOCK

Inserting WITH TABLOCK gives us the PAGE compressed data size, 69480 KB.

INSERT INTO dbo.CompressedHeap_Source2  WITH(TABLOCK)
(
Val,Datefield,Tinyfield,Floatfield)

SELECT 'Bla',cast(getdate() as date),1,1.2412
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2

BULK INSERT

Now when we create a destination heap table that is also page compressed, and do a bulk insert with tablock:

CREATE TABLE dbo.CompressedHeap_Destination( Val varchar(512),
                                 Datefield Date,
                                 Tinyfield TinyINT,
                                 Floatfield float)
WITH (DATA_COMPRESSION = PAGE);

bulk insert dbo.CompressedHeap_Destination

from 'E:\Data\HeapData.bcp'  with (TABLOCK)

The data gets page compressed and is also at 69480 KB:

name    rows    reserved    data    index_size  unused
CompressedHeap_Destination  6365530                 69512 KB    69480 KB    8 KB    24 KB

BCP IN WITH TABLOCK

You can get the same results as the BULK INSERT WITH TABLOCK by using BCP IN with the -h "TABLOCK" hint. This makes sense, they do the same internally

--bcp TEST.dbo.CompressedHeap_Destination2 IN E:\Data\HeapData.bcp -c -T -h "TABLOCK"

With the resulting size being 69480 KB

BCP IN WITHOUT TABLOCK

Using BCP to load data from the same file in a copy of the destination table

And a standard bcp command results into non compressed data:

--bcp TEST.dbo.CompressedHeap_Destination2 IN E:\Data\HeapData.bcp -c -T

With the data size at 132272 KB (row compressed).


MySQL #1054 - Unknown column 'masterdata_hyung_event.eventId' in 'on clause' on deep join tables So, I have this query
SELECT * FROM hyung

LEFT JOIN masterdata_hyung_description ON hyung.descriptionId = masterdata_hyung_description.hyungDescriptionId

CROSS JOIN masterdata_hyung_description ON masterdata_hyung_description.eventId = masterdata_hyung_event.eventId

which return #1054 - Unknown column 'masterdata_hyung_event.eventId' in 'on clause'.
I have those three tables, they are (i think) is already related with foreign key. More less like:
table hyung fk with masterdata_hyung_description, and masterdata_hyung_description fk with masterdata_hyung_event. What I want is how can I relate all of them so I can grab data from masterdata_hyung_event from table hyung?. Thank you
EDIT: here's the link for dump sql. Thank you.

After full days my noob brain tried to work hard, I've found my solution.
SELECT * FROM hyung, masterdata_hyung_description #this

JOIN masterdata_hyung_event ON masterdata_hyung_description.eventId = masterdata_hyung_event.eventId #and this

WHERE hyung.Taket = '1822171' AND masterdata_hyung_description.hyungDescriptionId = hyung.descriptionId

Not tested in multiple results tho (but it should), but hopefully it works too. Thanks


Get into SQL Server 2008 with no management studio and no sqlcmd I got office program. This program stores his data in SQL Server 2008 Express, but there is no management studio and no sqlcmd. How can i get into pure SQL tables, to use them in my plugin.

You can download SSMS 2012 or 2014 which is a full seperate download and then use it to access tables, etc

link: ssms 2012

can't comment on what/ how your plugin is.


Permission Requirement to migrate MongoDB to ApsaraDB for MongoDB I want to migrate my existing MongoDB 3.6 to ApsaraDB for MongoDB. What type of permission required for Migration.
Is there any steps defined for MongoDB migration

DTS allows you to migrate data from on-premises MongoDB to ApsaraDB for MongoDB. With the data change replication function, you can perform the migration without stopping services.

Migration types

For data migrations from on-premises MongoDB to ApsaraDB for MongoDB, DTS supports the following migration types: migrate existing data and replicate data changes. The features and restrictions of these migration types are as follows:

Migrate existing data

This migrates all existing data in the source instance to the target instance.
Replicate data changes

If you also choose to replicate data changes, the data changes made to the source instance during the migration will be synchronized to the target instance, so that eventually the source instance is dynamically synchronous with the target instance. This allows you to perform the migration while on-premises MongoDB remains operational.

Migration functions

The supported MongoDB versions are 3.0 and 3.2.
MongoDB existing data migration supports:

Database migration
Collection migration
Index migration

MongoDB data change replication supports:

Synchronization of create, delete, update operations on documents
Synchronization of create and delete operations on collections
Synchronization of create and delete operations on databases
Synchronization of create and delete operations on indexes

Migration procedure

The following describes the procedure of configuring a migration task from on-premises MongoDB to ApsaraDB for MongoDB.

Create accounts
When configuring a migration task, you need to provide the accounts of the source and target instances. The accounts must have the permissions listed in the preceding Migration permissions section.

If the source or target MongoDB instance does not have an account with required permissions, create one as follows:

db.createUser({user:"username",pwd:"password",roles:[{role:"rolename1",db:"database_name1"},{role:"rolename2",db:"database_name2"}]})

Parameter description:

username: Username of the account to be created
password: Password of the account to be created
rolename1/rolename2: Roles to be assigned to the username. Example roles are read and readWrite.
database_name1/database_name2: In the preceding example, role1 and role2 of database_name1 and database_name2 are assigned to the username.

You can check this link for more detailed information


Startup NOMOUNT and Startup MOUNT on dataguard oracle I have a question here about Oracle data guard.

After performing a switchover, we shutdown the new standby database and startup it.
At this stage, sometimes I find DBA performance in 2 different steps:

startup mount;
startup nomount; then
alter database mount standby database;

What's the difference between above commands? Could somebody tell me?

Thanks for helping me and forgive me about my English writing.

From the documentation:

  startup_clauses

  The startup_clauses let you mount and open the database so that it is accessible to users.

  MOUNT Clause

  Use the MOUNT clause to mount the database. Do not use this clause when the database is already mounted.

  MOUNT STANDBY DATABASE

  You can specify MOUNT STANDBY DATABASE to mount a physical standby database. The keywords STANDBY DATABASE are optional, because Oracle Database determines automatically whether the database to be mounted is a primary or standby database. As soon as this statement executes, the standby instance can receive redo data from the primary instance.

startup mount is the same as startup nomount; alter database mount.

So there is no practical difference between the two approaches you showed, if the commands are run consecutively (i.e. you are not doing anything else while it is started but not mounted). However, including STANDBY DATABASE in the command clearly shows intent, so I think it's generally better to include it, particularly if you're scripting this as part of a switchover process.


Writing a database analysis document I've been tasked with writing an analysis of a database that is currently used on a small scale. The intent of the document is to show to the business (as well as technically) that the database can scale to support other business divisions. I've struggled to find any database documentation standards on the web. It seems as though most databases are not documented; let alone have a basic ER diagram.

I'm more specifically concerned with the technical side and this is the outline I've come up with:

Non-functional Requirements

Storage needs
Server reliability

Schema Design & Normalization

Relational Integrity
Domain Integrity

Data types
(FOREIGN KEY, DEFAULT, NOT NULL constraints)

Referential Integrity

Parent/child references

Query Design

Is there any aspects that I'm missing or should focus more on?

I would actually verify how it scales up: set up a test environment, expose to to the workload you are expecting, and see what happens. That done, describing your tests would be easy.

If you skip this essential step, your write-up might look good on paper and be completely useless.


group by and display contents of last row for each result I've got some data of two objects and I want to retrieve the last row of both the objects, preferably in one query.

Table structure:

CREATE TABLE data
(
  pid serial NOT NULL,
  dt timestamp without time zone,
  object_id integer,
  info_1 numeric(10,8),
  speed numeric,
  CONSTRAINT dat_pid PRIMARY KEY (pid)
)

Sample data:

1, 2014-04-29 12:02:56, 8, ....
2, 2014-04-29 12:02:10, 8, ....
3, 2014-04-29 12:01:02, 8, ....
5, 2014-04-29 12:01:32, 6, ....
.....

I want a row per unique object ID, each row being the newest one available. I tried to achieve it by using this query, but it wants pid to be in the group by clause. But I don't want to group by the PID, but at the tracker id....

SELECT *
FROM data
GROUP BY object_id

You can use DISTINCT ON for this type of queries:

SELECT DISTINCT ON (object_id) *
FROM data
ORDER BY object_id, dt DESC ;


HAProxy + Patroni - Configure reads from a master when no slaves available I have a HAProxy + Patroni setup with split reads and writes.
The problem is, when both my replicas go down, only my writes are supported, the reads stop because both replicas are down.
How is this problem generally handled in this setup? Is there a way to have HAProxy direct the read traffic to the master in case no read servers are available?

This pattern of defining a frontend will use the replica as the main target, but if the read service is dead, then it will route traffic to the write servers - users should use 5911 for connecting to the read replica service.
frontend a_listen_fe
    bind *:5911
    acl is-read-service-dead nbsrv(cluster-a-slave) lt 1
    use_backend cluster-a-master if is-read-service-dead
    default_backend cluster-a-slave

listen cluster-a-master
    bind *:5011
    option httpchk OPTIONS /master
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgresql_a1 127.0.0.1:6011 maxconn 100 check port 8011
    server postgresql_a2 127.0.0.1:6012 maxconn 100 check port 8012
    server postgresql_a3 127.0.0.1:6013 maxconn 100 check port 8013

backend cluster-a-slave
    option httpchk OPTIONS /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgresql_a1 127.0.0.1:6011 maxconn 100 check port 8011
    server postgresql_a2 127.0.0.1:6012 maxconn 100 check port 8012
    server postgresql_a3 127.0.0.1:6013 maxconn 100 check port 8013


Is a two column primary key the most performant way to go if (a,b) should be unique but I will only be querying against a? I have a table with two columns, A and B. Each pairing (a, b) should only occur once in the table. When I query this table, I will only be interested in those rows where A has a certain value.  Is a two column primary key (A,B) the way to go even though my queries will only search on A?

If you define the primary key as (a,b), then the index will be used when handling queries using a OR a AND b, but not b alone.

If you define the primary key as (b,a), then the index would be used when handling b, b AND a, but not a alone.


How to inspect a view's structure in information schema in PostgreSQL? Is there a way to discover the structure of a view in a similar way to how tables are described by entries in information_schema.columns? I can also use the pg_* tables.
In other words, similar to how psql has \d which describes the structures of both tables and views, I'd like to access this information via SQL queries.

information_schema.columns already includes views; you can join with information_schema.views if you need to filter to only columns that are part of a view.


How to migrate MySQL database to SQL Server on a separate machine? I have a MySQL datadump file from the database to be moved. From the suggestions I've read so far, most migration tools only work with databases that are hosted on the same server. What steps can I take to convert the MySQL datadump file to an SQL Server database on a separate machine?

Use Microsoft SQL Server Migration Assistant for MySQL to migrate data from MySQL into SQL Server.

The steps involved consist of:

Mout/Restore/Load the datadump file to a new MySQL server.
Use SSMA for MySQL to migrate to SQL Server.  SSMA does not require MySQL to be installed on the SQL Server machine, however it requires a running database engine to communicate with.


What is an efficient storage method to store Graph Data from GraphX (Spark)? I generate graph data using Spark's GraphX library and I need an efficient way to store graph data. I have access to Apache Cassandra and ideally I want to store the graph data in there but the issue is I don't know how to efficiently store the graph structure in Cassandra. I have looked at Titan's backend storage in Cassandra but I could not find a detailed explanation of how the data (edges and properties) is formatted and stored. I am giving Titan as an example, any existing alternative or methodology is welcome. The persistence layer doesn't even have to be Cassandra necessarily. I mostly need an efficient way to store the graph that also enables me to do filtering on the backend. I.e. if I store on HDFS, I have to load the whole DB content on Spark to analyze it and perform transformations, and for big data that is unfeasible so I need a kind of storage that enables me to filter server-side what I need before loading it onto my memory.

Edit: My requirements is that this is a distributed graph database that can handle Big Data, is scalable and has to have a Spark connector of sorts (or a way to connect with Spark functionalities).

TitanDB stores graphs in adjacency list format which means that a graph is stored as a collection of vertices with their adjacency list. The adjacency list of a vertex contains all of the vertex’s incident edges (and properties).

They used VertexID is the partition key, PropertyKeyID or EdgeID as clustering key and property value or edge properties as normal column.

Their Cassandra Table schema :

CREATE TABLE edgestore (
    key blob,
    column1 blob,
    value blob,
    PRIMARY KEY (key, column1)
) WITH COMPACT STORAGE AND CLUSTERING ORDER BY (column1 ASC);

Where

key     => VertexID
column1 => PropertyKeyID or DIR+OtherID+EdgeID
value   => PropertyValue or Pair of EdgePropertyKeyID and EdgePropertyValue


How can I enable Transparent Data encryption (TDE) on MySQL 5.6 I am looking to perform real-time I/O encryption and decryption on MySQL 5.6 Database. I have few questions to understand Transparent Date Encryption (TDE):
Note: I am using AlibabaCloud

Can I Activate / Deactivate TDE any time
Is there any key / Certificate required for Encryption / Decryption
Is there any effect on Server Memory / CPU Usage

I appreciate any assistance with this

Transparent Data Encryption (TDE) can be used to perform real-time I/O encryption and decryption on instance data files. To increase data security, you can enable TDE to encrypt instance data. Data is encrypted before it is written to disk and decrypted when it is read from disk. TDE does not:

Increase the size of the data file.
Require developers to make any code changes to use TDE.
Alibaba Cloud ApsaraDB for RDS fully supports TDE for MySQL. In this article, we will look at setting up TDE for MySQL on Alibaba Cloud.

Note: TDE is currently only applicable to SQL Server 2008 R2 and MySQL 5.6. To view or modify TDE settings, you need to log in with an Alibaba Cloud account rather than a RAM account.

Setting Up TDE on ApsaraDB for RDS

To enable TDE on Alibaba Cloud:

Go to the RDS Management Console, select the appropriate RDS instance.
Under Security Control, TDE tab you will be able to find the option to enable TDE.

This feature will allow encryption of data while at rest. The encryption of data while in transit is still the responsibility of the user and is outside the purview of this document.

Encrypting Tables

Log in to the database and execute the following command to encrypt the table to be encrypted.

alter table  engine＝innodb block_format=encrypted;

Decrypting Tables

If you want to decrypt the TDE encrypted table, execute the following command.

alter table  engine＝innodb block_format=default;

You can find detailed TDE documentation here


Avoiding "too many active scans" on MySQL Cluster I'm running some deletions against tables in MySQL Cluster, and keep hitting the "Too many active scans" error. I'm aware of the article by Johan Andersson, however in this case the parameters are already set very high - I'm assuming the error occurs due to the way I'm accessing the system. I'd like to fix my code, not tweak my database config any more!

The query I'm running is along the lines of:

delete from mytable where mytable.id in (<big long list of 1000s of IDs>)

Would I be better off batching this up into smaller chunks? Would that help avoid the error?

What does "Too many active scans" actually mean, and how can I avoid it in this case (without setting parameters to even higher values)?

"Too many active scans" error means that at a given moment there are too many queries running which are scanning the tables while trying to get answers.

Scanning means for example range scan queries (WHERE val > 20). And if I remember right scan also means fetch queries if index is not has.

SELECT * FROM tbl WHERE id=20;

Even such query counts as scan if id field has btree index.

In your case we almost all DELETE queries are running in loop with 'LIMIT 1000' or similar value.


Tool to test RDBMS SQL dialect for ANSI SQL standard compliance We are developing an interface to our internal data storage and manipulation tool that would allow people to use SQL dialect.

We decided that it would be perfect to make our SQL dialect as close to the standard as possible.

Currently I'm trying to find a tool that would test our implementation of query language and make a verdict about its compliance and list missing details.

Have anyone ever encountered anything like this?

Finally after looking at SQLite unit tests and postgreSQL unit tests we decided to use these tools

https://github.com/anse1/sqlsmith
https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki


Log Shipping -Monitor Server Down - Copying happening but not restoring on secondary I have set up a few log shipping instances, but I am not a DBA.

This weekend something happened for the first time - the log shipping Monitor Server went down temporarily due to electrical issues outside of my control.

When I looked at the primary and secondary machines during this time- both were up and running
and
the .trn files were still being copied from the primary to the secondary - as expected.
Yet, the secondary instance did not restore the .trn files at all. I received a notification:

  The log shipping primary database _________ has backup threshold of 60
  minutes and has  not performed a backup log operation for 82 minutes.
  Check agent log and logshipping monitor information.

Is this expected? The primary and secondary were both running - shouldnt the monitor be an "optional" SQL instance which should have no effect on the log shipping? It was preventing the secondary instance from applying the backups even though I later explicitly ran the restore job.
When the monitor server came back on - everything sorted itself out.

I have a lot to learn I know, any advice on whether I can check some settings or whether this is expected would be appreciated please.

As per error it  does not necessarily indicate a problem with log shipping. The message indicates that the difference between the last backed up file and current time on the monitor server is greater than the time that is set for the Backup Alert threshold. Log shipping is out of synchronization beyond the backup threshold.

Instead, this message might indicate below problem:

  The backup job is not running. Possible causes for this include the following: the SQL Server Agent service on the primary server instance is not running, the job is disabled, or the job's schedule has been changed.

The following list includes some of these reasons:

The date or time (or both) on the monitor server is different from the date or time on the primary server. It is also possible that the system date or time was modified on the monitor or the primary server. This may also generate alert messages
When the monitor server is offline and then back online, the fields in the log_shipping_primaries table are not updated with the current values before the alert message job runs. This seems to be the cause of LS going out of sync in you're case.
The log shipping Copy job that is run on the primary server might not connect to the monitor server msdb database to update the fields in the log_shipping_primaries table. This may be the result of an authentication problem between the monitor server and the primary server.

  It was preventing the secondary instance from applying the backups even though I later explicitly ran the restore job. When the monitor server came back on - everything sorted itself out.

When the monitor server instance goes offline and then comes back online, the log_shipping_monitor_primary table is not updated with the current values before the alert message job runs. To update the monitor tables with the latest data for the primary database, sp_refresh_log_shipping_monitor on the primary server instance ran and sync you're LS status.


REGEXP in a where clause I store s3 bucket's path for files in my database. Files can be either pictures(jpg) or pdfs.

Let's suppose I have uploaded photo.jpg to my database via application, path will be like:

/{s3bucket}/filesuser/client/27801123/attachment/4510/file/1113/small/photo.jpg
/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/small/photo.jpg
/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/main
/{s3bucket}/filesuser/client/27801125/attachment/4512/file
/{s3bucket}/filesuser/client/27801126/attachment/4513/main

For every file, I have 2 root paths:

/{s3bucket}/filesuser/client/27801125/attachment/4512/file
/{s3bucket}/filesuser/client/27801126/attachment/4513/main
/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/main

I need to get, via where clause only the root = 'main'.

Example:

select REGEXP_REPLACE(path_name, '.*/', '') as col2 from seg_table limit 10;

The query works, I get file, main, photo.jpg as result.

How can I include that regexp in a there clause?

Example:

WHERE (select REGEXP_REPLACE(path_name, '.*/', '') as col2
from seg_table) = 'main'

http://sqlfiddle.com/#!15/6d65d

There is an example. Please note the col2 returns not only 'main'. I need it to return only main and I need to put it in a where clause. I can't use ~ operator because I will need to do something like:

FROM test1 t1
JOIN another_view AS s
WHERE s.full_path = substr(t1.full_path, char_pos '/file..' to get /filesuser/client/.../attachment/.../) || 'main'

Can't use '/main$'; as it will give me:

/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/main

instead of

/{s3bucket}/filesuser/client/27801124/attachment/4511/main

~ POSIX Regex Operator

The easy way to do this is to use the ~ operator.

SELECT *
FROM ( VALUES
  ( '/{s3bucket}/filesuser/client/27801123/attachment/4510/file/1113/small/photo.jpg' ),
  ( '/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/small/photo.jpg' ),
  ( '/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/main' ),
  ( '/{s3bucket}/filesuser/client/27801125/attachment/4512/file' ),
  ( '/{s3bucket}/filesuser/client/27801126/attachment/4513/main' )
) AS t(path_name)
WHERE path_name ~ '/main$';

You can always be more specific with your regex..

WHERE path_name ~ '/filesuser/client/\d+/attachment/\d+/main$';