Change Data Capture in Mysql I'm trying to implement Change Data Capture in MySQL. I have seen that using Maxwell I can implement CDC but I'm confused where to start. Please help me with the detailed steps to implement it. And also tell me the prerequisites.

Firstly I want to introduce three tools for use CDC in MySQL.

They are about three popular CDC for MySQL/MariaDB.

Two of them are Free and one of them is commercial.

MariaDB Maxscale is commercial tools for Routing, Qeury Cache, Load balancing and also has CDC for use in MariaDB (I don't know is it work on MySQL or not)

Another Tools are Maxwell and Debezium. They are Free and Open source.

Debezium developed by RedHat Team and It has MySQL/Oracle/PostgreSQL and MongoDB Connector But its performance on the MariaDB database is not clear (Until now). Look at this link: https://issues.jboss.org/browse/DBZ-357

Usually CDC is used for Push changes data (producer) to Message Broker like Kafka and Pull from Another Tools such as Apache Solr orElastic and etc to use in other tasks.

for Maxwell, It has --producer switch To specify the place where the changes are made. You can set this to stdout (usually for test), Kafka and RabbitMQ

For Prepare MySQL to use for Maxwell

you need to Enable Binary Log and set to Row format.
Then Create a user which have grant SELECT, REPLICATION CLIENT and REPLICATION SLAVE
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE on . to 'maxwell'@'%';
Clone maxwell from github https://github.com/zendesk/maxwell
Start Maxwell and your producer bin/maxwell --user='maxwell' --password='XXXXXX' --host='127.0.0.1' --producer=stdout

You can config maxwell to Produce Specific Table or Database to specific Topic (kafka) or Channel (RabbitMQ).

You can start multiply Maxwell to Produce Multiply Tables to different Topic.

Pay attention Currently Maxwell doesn't have any Failover Solution and you should to handle this.

More Information:

http://maxwells-daemon.io/quickstart/

https://www.youtube.com/watch?v=eMCZAlGsNSA


How to change table with innodb/myisam engine to federated In my.ini I added

[mysqld]
federated

When I type in a shell show engines; I get this result:

But when I try to change one of this tables which are on innodb engine to federated I get an error. When I try to create/alter table with sqlyog there is no federated engine

Can anyone help me? And if I change innodb/myisam table with data to federated table, will I lose any data? I dont use transactions in table

An example from the manual:

  First, you must have a table on the remote server that you want to
  access by using a FEDERATED table. Suppose that the remote table is in
  the federated database and is defined like this:

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

  The example uses a MyISAM table, but the table could use any storage
  engine.

  Next, create a FEDERATED table on the local server for accessing the
  remote table:

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

  (Before MySQL 5.0.13, use COMMENT rather than CONNECTION.)

  The basic structure of this table should match that of the remote
  table, except that the ENGINE table option should be FEDERATED and the
  CONNECTION table option is a connection string that indicates to the
  FEDERATED engine how to connect to the remote server.


SQL Server Availability Group does not update DB owner automatically I learned a hard lesson this week. It turns out what while table updates/ schema updates do get replicated across databases on an Availability Group, changing the DB Owner on the primary replica does not change the DB owner on the secondary replica automatically.

Thus if you perform a failover, the database owner could now be out of sync with the master table's SID.

Should my plan of action when e.g. making a security related change be to

1. Apply change to primary replica
2. Fail over the Availability Group
3. Apply change to the new primary replica

Am I correct in making these statements? Is it only DBOwner/ Schema changes that I must be aware of when I am making changes.

Yes, you need to make sure that databases have the same owner, schema changes will travel along the availability group. This gets problematic when there are changes in database owner as you need to reapply those changes too to the secondary nodes.

If it is possible, you could change owner to SA on every database, then make this change every night and when fail-over happens. When you capture the fail-over you can run this to make SA owner for every database:

EXEC sp_MSforeachdb 'ALTER AUTHORIZATION ON DATABASE::? TO SA';

EDIT:

If you don't wish to go SA as a owner, you could create table containing the database owners

CREATE TABLE DatabaseInAvailabilityGroup.dbo.dbownersMaintenance (
id INT IDENTITY(1,1),
dbname NVARCHAR(100) NOT NULL,
username NVARCHAR(100) NOT NULL
);

Filling up the table (make this as a job and schedule it once a hour or something)

DELETE FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

INSERT INTO DatabaseInAvailabilityGroup.dbo.dbownersMaintenance
SELECT db.name, sl.name
FROM master.sys.databases db
INNER JOIN master.sys.syslogins sl ON db.owner_sid = sl.sid;

As soon as the fail-over has happened, this needs to be run on the new active node:

DECLARE @maxRow INT, @currentRow INT, @sql NVARCHAR(MAX), @dbname NVARCHAR(100), @owner NVARCHAR(85);
SELECT @maxRow = MAX(id), @currentRow = MIN(id) FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

WHILE @maxRow >= @currentRow
BEGIN
SELECT @dbname = dbname, @owner = username FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance WHERE id = @currentRow;
SET @sql = 'ALTER AUTHORIZATION ON DATABASE::'+@dbname+' TO ['+@owner+']';
EXECUTE (@sql);
SET @currentRow = @currentRow+1;
END


MySQL Query Optimization : Indexing and Pagination I'm adding pagination to my 'latest news' php script and running in to an issue.

SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit

I have indexes on approved, time columns.

The problem is when I do an EXPLAIN on the query, it shows it is using the 'approved' index - but it still has to scan all 1000 rows to get the 10 new entries I want to display.

Anyway to optimze the query so that doesn't happen?

At this point, you will have to create an index on three columns

ALTER TABLE news ADD INDEX authored_time_sid_ndx (authored,time,sid);

You must then refactor the query just slightly to reduce two things the MySQL Query Optimizer is expected to handle:

the number of rows read
the amount of data collected

Here is your original query:

SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit

What you can do is gather all the keys first:

SELECT sid FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit

This will perform a full index scan rather than a full table scan because all three columns are in the index. That's a lot less baggage to walk around with for temp table generation because you are scanning just 3 columns in a smaller resource (the index) as opposed to 6 columns as from a bigger resource (the table) as well as not sticking url and bodytext (which are probably VARCHAR(300) and TEXT fields) into the mix just for the sake of gathering keys.

Next, make the SELECT sid query into an inline table and connect them back to the news table using only the fetched keys. EXAMPLE : Suppose your LIMIT variables are 200,10. This means you want to move to the 201st row of the news table and get 10 keys from that point. This means that no matter which page you are on, you want to collect 10 keys at a time, and only 10 keys at a time.

Here is the new and improved query:

SELECT A.sid, A.title, A.time, A.bodytext, A.author, A.url
FROM
news A INNER JOIN
(SELECT sid FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit) B
USING (sid);

The only real cost here is doing a full index scan for keys rather than a full table scan. The benefit is that once you get the 10 keys from the inline SELECT, then only 10 titles, bodytexts, authors and urls are retrieved.

Give it a Try !!!

CAVEAT

As a rule of thumb, anytime you index a column whose cardinality is very low (i.e., Male/Female (2), Single/Married/Divorced/Widowed (4), True/False (2), 0/1 (2)) and the number of rows for any one value exceeds 5% of the total rows in the table, the MySQL Query Optimizer will rule out any and all indexes and you will perform can full table scan or a bad index gets chosen and you end up with a full index scan. This is why it is very imperative that you find the right column distribution, or at the very least perform index scans instead of table scans.

UPDATE 2011-08-08 11:13 EDT

What was I thinking ? This was my original propsed index:

ALTER TABLE news ADD INDEX authored_time_sid_ndx (authored,time,sid);

The field should be approved not authored. This is what it should be:

ALTER TABLE news ADD INDEX approved_time_sid_ndx (approved,time,sid);

The original query you gave was

SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit

The index I am proposing (approved_time_sid_ndx) will include approved, time, and sid.

The answer that you just submitted has this query :

SELECT sid, title, time, bodytext, author, url FROM news WHERE approved = 1 AND sid > 0 ORDER BY sid desc LIMIT 500, 10;

This being the case, the index you need now should be this:

ALTER TABLE news ADD INDEX approved_sid_ndx (approved,sid);

Both approved and sid should be together. IF they are not together, the MySQL Query Optimizer may decide to perform an internal index merge of the primary key and an index where approved is the first (or only) column. In fact, your new query should be refactored as follows:

SELECT A.sid, A.title, A.time, A.bodytext, A.author, A.url
FROM
news A INNER JOIN
(SELECT sid FROM news WHERE approved=1 and sid > 0 ORDER BY sid desc LIMIT 500,10) B
USING (sid);

Remember, you want an index that encompasses as many DB columns as possible that are embedded in WHERE and ORDER BY clauses.


Clock change on SQL Server With the clock change ahead in Europe - how does SQL Server deal with the clock change? Are there known issues related to a clock change?

SQL Server just uses the operating system's time. However, if your application code relies on functions like GETDATE(), then your code needs to be able to handle jumps forward (or backward) in time.

For example, if your have code that does something like this:

SET @StartDate = GETDATE()
(Do some work)
IF DATEDIFF(MM, @StartDate, GETDATE()) > 60 ....

That code can fail. Long term, consider using GETUTCDATE() instead and storing dates with DATETIMEOFFSET - but that's a big code change that you can't really do quickly before changing the date on a server.


mysqlslap number in sql statement to change with iteration I am trying to figure out how to increment a number in a mysqlslap query with every iteratiom.

Let me explain what I exactly mean:

I got a query:

SELECT age FROM member WHERE id = [some number]

is there a way to write mysqlslap parameter, so it iterates the [some number] while executing the query multiple time ? I do not want to keep doing the same query, as it caches.

I went through the documentation on http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html
but did not really find the straight answer to my question.

If there is not mysqlslap solution, maybe there is a way to achieve this with some other tool.

Cheers for help !

If you are worried about the performance effect of caching the result in the query cache, you can disable caching in your query by using SQL_NO_CACHE:

SELECT SQL_NO_CACHE age FROM member WHERE id = [some number]


How to prevent "ibdata files do not match the log sequence number"? I am dealing with a very large set of databases that are all innodb.

I've enountered this on mysql restart too many times for my comfort:

ibdata files do not match the log sequence number

But I've clearly watched mysql shutdown properly just before the restart when that message happens.

Then it "repairs" right up to the original sequence number with nothing lost.

What is the best approach to deal with and fix this permanently?

Using Percona with innodb_file_per_table=1

Example log:

InnoDB: Initializing buffer pool, size = 80.0G
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 475575972691 and 475575972691 in ibdata files do not match the log sequence number 925369860131 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: Percona XtraDB started; log sequence number 925369860131

Note how the final log sequence number now matches what it thought was wrong in the first place, so there was 100% recovery?

So why is the log sequence not being properly written to ibd?

Is it possible shutdown is incomplete somehow?

Thank you for any advice.

ps. I always wonder if I should be asking this on serverfault or here? Is it okay I asked here?

I have faced the same problem: MySQL restarts too many times and ibdata files do not match the log sequence number.

This is happening because one of the tables is corrupted.
In order to fix this issue, take a backup of the database and perform the mysqlcheck operation:

1.

mysqldump -u root -p dbname > /tmp/dbname.sql

2.

mysqlcheck -u root -p dbname

If you find the corrupted table then you can remove it, I know corrupted tables can't be deleted but we can restore the database from the corresponding production database.


Update query to Reset counter when date column changes column1   column_sec attempt      date    counter
1250       1250_A     1        16-06-2016    1
1250       1250_B     1        16-06-2016    1
1250       1250_C     1        16-06-2016    1
1250       1250_A     2        16-06-2016    2
1250       1250_B     2        16-06-2016    2
1250       1250_C     2        16-06-2016    2
1250       1250_A     3        06-06-2016    1
1250       1250_B     3        06-06-2016    1
1250       1250_C     3        06-06-2016    1
1250       1250_A     4        06-06-2016    2
1250       1250_B     4        06-06-2016    2
1250       1250_C     4        06-06-2016    2

I want to write an update query when my date changes it will reset the counter column as shown above,
Can anyone help ?

So far i have tried this, But it increments counter by 1 till the end of the table.It did not help.

set @v1 := '', @v2 :='',@v3 :='', @num := 1;

select column1,column_sec,attempt,date,
   @num := if(@v1 := date,if(@v2 := column_sec,if(@v3 := column1,@num + 1,1),1),1) as counter
from table_name where date is not null
order by column1,column_sec,attempt;

Do it like this:

select column1,column_sec,attempt,date,
@num := if(@v1 != date OR @v2 != column_sec OR @v3 != column1, 1, @num + 1) AS counter,
@v1 := date,
@v2 := column_sec,
@v3 := column1
from table_name
cross join (select @v1 := '', @v2 :='',@v3 :='', @num := 0) var_init_subquery
where date is not null
order by column1,column_sec,attempt;

The key is, that you have to check first, then assign the value of the current row. In the query you tried, you never assigned anything to the variables. When the next row (and the therefore the select clause) is processed, the variables hold the value of the previous row in the check, then the value of the current row is assigned to the variables.


How to create projection for proper using of UPPER function? we've faced the situation when the operation WHERE UPPER(columnName) IN ('PRED1', 'PRED2', 'PRED3') works very bad. If I remove UPPER then it performs good. I've tried to play with projections and could not get it working properly. How can I solve that issue (maybe the problem is not in projection...)
I've tried following code:

CREATE TABLE public.ProjectionsTest (
id int primary key,
a varchar(2000)
);

INSERT INTO public.ProjectionsTest values (1,'a');
INSERT INTO public.ProjectionsTest values (2,'b');
INSERT INTO public.ProjectionsTest values (3,'c');
INSERT INTO public.ProjectionsTest values (4,'d');
INSERT INTO public.ProjectionsTest values (5,'e');

SELECT ANALYZE_STATISTICS('public.ProjectionsTest');
SELECT REFRESH('public.ProjectionsTest');

CREATE PROJECTION public.ProjectionsTest_lower_ob
(
 id,
 a
)
AS
 SELECT id,
        lower(a) a
 FROM public.ProjectionsTest
 ORDER BY lower(a), id
SEGMENTED BY hash(id) ALL NODES ;

explain SELECT id, lower(a) FROM public.ProjectionsTest WHERE lower(a) = 'a' ORDER BY a, id;

-- ROLLBACK
DROP PROJECTION public.ProjectionsTest_lower_ob;
DROP TABLE  public.ProjectionsTest;

And I am getting following result (the projection "public.ProjectionsTest_lower_ob" is not used):

------------------------------
QUERY PLAN DESCRIPTION:
------------------------------

explain SELECT id, lower(a) FROM public.ProjectionsTest WHERE lower(a) = 'a' ORDER BY a, id

Access Path:
+-SORT [Cost: 36, Rows: 4] (PATH ID: 1)
| Order: ProjectionsTest.a ASC, ProjectionsTest.id ASC
| +---> STORAGE ACCESS for ProjectionsTest [Cost: 35, Rows: 4] (PATH ID: 2)
| | Projection: public.ProjectionsTest_super
| | Materialize: ProjectionsTest.a, ProjectionsTest.id
| | Filter: (lower(ProjectionsTest.a) = 'a')

Here is small example of how you can improve the query speed.

One way i do it is by using bucket columns based on default value at load time.(this can make your load slower thou).

see sample code

drop table test_tbl;
CREATE TABLE
    test_tbl
    (
        id INT,
        name VARCHAR(20),
        bkt_col int default(
        CASE
            WHEN name=upper(name)
            THEN 1
            WHEN name!=upper(name)
            THEN 0
        END)
    );

Create the projection to attend you predicate, segmented on replicated is up to you table type/size

CREATE PROJECTION public.test_tbl
(
 id,
 name,
 bkt_col
)
AS
 SELECT test_tbl.id,
        test_tbl.name,
        test_tbl.bkt_col
 FROM public.test_tbl
 ORDER BY test_tbl.bkt_col,
          test_tbl.id,
          test_tbl.name
SEGMENTED BY hash(test_tbl.bkt_col, test_tbl.id, test_tbl.name) ALL NODES KSAFE 1;

Insert some data

insert into  test_tbl(id,name)  values (1,'BOB') ;
insert into  test_tbl(id,name)  values (1,'bob') ;
id  name  bkt_col
--  ----  -------
1   bob   0
1   BOB   1

To query it use this sql, this way you avoid upper at run time and your stats will be well used.

SELECT
    *
FROM
    test_tbl where bkt_col = 1 and name IN ('BOB')


SQL Server 2008: Diff table data on different servers with exact schema I'm trying to find a good open source tool that simply compares data in a database on one server to a table (of the same schema) on another server.  Then I want to generate INSERT statements for the rows that are different, for either the source or destination/target table.

I've tried downloading several tools, and I can't get any of them to work because they all had bugs.  I can be more specific with the errors I received if you need me to be.

I've tried:

TableDiffGui.zip
SqlDiffFramework setup 1.0.3.0.exe
SqlTableCompare.zip
DaBCoS.0.3.2163.zip

If there is a manual way to do this, please let me know.  My last resort will be to simply write a program that does it.

Is creating a linked server on one of those an option?  If so you could probably do this using something similar to the following query (untested):

INSERT INTO Server1.db1.dbo.Table1
SELECT * FROM dbo.Table2
EXCEPT
SELECT * FROM Server1.db1.dbo.Table1

If not, then maybe take a look at SQL Server replication


AttributeError: 'NoneType' object has no attribute 'value' - pgAdmin4 I Ctrl+C'd out of the initial pgAdmin4 setup aka. python pgAdmin4.py and now if I try to rerun the install, I get a mountain of errors. I'm using pgAdmin3 for now but I would like to know how to fix this. The full traceback is below:

Traceback (most recent call last):
File "pgAdmin4.py", line 37, in <module>
    app = create_app()
File "/home/sean/.virtualenvs/pgadmin/lib/python2.7/site-packages/pgadmin4/pgadmin/__init__.py", line 244, in create_app
    db_upgrade(app)
File "/home/sean/.virtualenvs/pgadmin/lib/python2.7/site-packages/pgadmin4/pgadmin/setup/db_upgrade.py", line 25, in db_upgrade
    flask_migrate.upgrade(migration_folder)
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/flask_migrate/__init__.py", line 244, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/alembic/command.py", line 254, in upgrade
    script.run_env()
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/alembic/script/base.py", line 421, in run_env
    util.load_python_file(self.dir, 'env.py')
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/alembic/util/compat.py", line 75, in load_module_py
    mod = imp.load_source(module_id, path, fp)
File "/home/sean/.virtualenvs/pgadmin/lib/python2.7/site-packages/pgadmin4/pgadmin/setup/../../migrations/env.py", line 94, in <module>
    run_migrations_online()
File "/home/sean/.virtualenvs/pgadmin/lib/python2.7/site-packages/pgadmin4/pgadmin/setup/../../migrations/env.py", line 87, in run_migrations_online
    context.run_migrations()
File "<string>", line 8, in run_migrations
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/alembic/runtime/environment.py", line 817, in run_migrations
    self.get_context().run_migrations(**kw)
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/alembic/runtime/migration.py", line 329, in run_migrations
    step.migration_fn(**kw)
File "/home/sean/.virtualenvs/pgadmin/lib/python2.7/site-packages/pgadmin4/migrations/versions/fdc58d9bd449_.py", line 34, in upgrade
    if get_version() != -1:
File "/home/sean/.virtualenvs/pgadmin/lib/python2.7/site-packages/pgadmin4/pgadmin/setup/db_version.py", line 21, in get_version
    if int(version.value) > int(config.SETTINGS_SCHEMA_VERSION):
AttributeError: 'NoneType' object has no attribute 'value'

I performed the same steps and ran into the same problem.  I found that the problem was resolved by removing the ~/.pgadmin directory (created during installation) and then re-running "python pgAdmin4.py"


Is it possible to deny RESTORE permissions to login with db_owner role membership? Is it possible to deny RESTORE permissions to a login with db_owner role membership?

Is dbcreator required to RESTORE over an existing DB with a BACKUP of that DB? Or is that only needed when creating a new DB from a BACKUP?

I'm trying to prevent a user with db_owner membership from accidentally restoring to the wrong server/instance.

The Microsoft RESTORE statement Docs say this about database permissions:

If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.
RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

I'd suggest providing the person who requires db_owner level permissions be a separate login from actual database owner.  Membership in the db_owner database role prevents that user from performing a restore.
Logins can be prevented from performing RESTORE DATABASE by denying them CREATE ANY DATABASE permission.
As a test, I did this on my SQL Server 2008 R2 instance.
Create a test login:
USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.server_principals sp WHERE sp.name = 'Test_db_owner')
BEGIN
    CREATE LOGIN Test_db_owner WITH PASSWORD = 'awsdaqwer0987)(FA&897afda2345';
END
GO

Create a test database:
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'Test_db_owner')
BEGIN
    ALTER DATABASE Test_db_owner SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE Test_db_owner;
END
CREATE DATABASE Test_db_owner;
GO

Create a user in the test database for the login above, and add the user to the db_owner database role:
USE Test_db_owner;
CREATE USER [Test_db_owner] FOR LOGIN [Test_db_owner];
EXEC sys.sp_addrolemember @rolename = 'db_owner', @membername = 'Test_db_owner';
GO

Create a backup of the database:
USE master;
BACKUP DATABASE Test_db_owner TO DISK = 'D:\SQLServer\MV\Backup\Test_db_owner.bak' WITH INIT;

Test the restore statement as the login:
EXECUTE AS LOGIN = 'Test_db_owner';
RESTORE DATABASE Test_db_owner FROM DISK = 'D:\SQLServer\MV\Backup\Test_db_owner.bak'
    WITH REPLACE, RECOVERY;
REVERT;

Output:
Msg 3110, Level 14, State 1, Line 25
User does not have permission to RESTORE database 'Test_db_owner'.
Msg 3013, Level 16, State 1, Line 25
RESTORE DATABASE is terminating abnormally.
Cleanup:
DROP LOGIN Test_db_owner;
DROP DATABASE Test_db_owner;

FYI, the database owner is changed like this:
ALTER AUTHORIZATION ON DATABASE::[xyz] TO [login_name];

(or with sys.sp_changedbowner, which is deprecated)
Membership in the db_owner role is granted like this:
ALTER ROLE [db_owner] ADD MEMBER [login_name];

Although both statements seem to affect the "database owner", that is not the case; only the ALTER AUTHORIZATION statement can modify which login owns the database.  See this excellent answer by Remus Rusanu.
If you want to allow the login to restore databases in the non-production environment, make them a member of the dbcreator server role:
EXEC sys.sp_addsrvrolemember @loginame = 'Test_db_owner'
    , @rolename = 'dbcreator';


Can connect to SQL Server 2012 via OSQL and SQLCMD but not via SSMS We have individual developer edition SQL Server 2012 databases set up on each of our developers PC's. All PC's are Windows 7, firewall is disabled and everything runs fine.

We're now looking to upgrade to Windows 8.1. We have had some problems with the firewall but have put in exceptions for port 1433 and 1434. We can connect using SQL or Windows authentication when using sqlcmd and osql, however if we try to connect via SSMS we get an error:

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections (provider: SQL
  Network Interfaces, error: 26 - Error Locating Server/Instance
  Specified)(Microsoft SQL Server)

What's confusing me is that we can connect without any problems using osql and sqlcmd. SSMS has no problems when running locally on the computer.

TCP looks to be setup correctly and listening for connections (and accepting connections from osql and sqlcmd).

What else do SSMS need to connect to SQL Server?

Can't explain why, but the firewall seems to be a problem on this computer. Turning the firewall off completely blocked all access (can't even ping or connect to network shares using UNC). We enabled the firewall and removed all of the blocking rules and it now works.


Is there a good reason to put the company name at the beginning of the table names? When developing a new system, for a big corporation.

Technologies:
Web, Java, Spring Boot, Rest Api, Postgres, Hibernate

Company:
Bank

System:
Promissory control, and all others

Company name:
Mycorp

Systems:
Many systems, dozens, dozens...

Is there a good reason to put the company name at the beginning of the table names?

For example, for the users table: mycorp_users

Sometimes I see something like this, the company name + the system name + ...: mycorp_systemx_users

Why not use a schema for this? dbmycorp.systemx.users

Or even not use anything only: someschema.users

As you say in the comments

  I work for a different company, yes, that is building the system for the bank

I suspect this is a requirement of the company you work for and/or the expectations of the purchasing bank.

When the build is complete mycorp is going to be all over in the code.  If you wanted to sell the same product to DifferentCorp it would be a lot of work to remove reference to mycorp.  I assume that by adding mycorp they bank paying you to custom build a product for them, will have some feeling that you will not be going and selling to their competition, as soon as you get it working.

See related

Is adding the ‘tbl’ prefix to table names really a problem?
What are some best practices for using schemas in SQL Server?


Expdp error UDE-22303 I am experiencing the following error, when I am trying to make an export to the database  using expdmp utility:

UDE-22303: operation generated ORACLE error 22303
OCI-22303: type "SYS"."KU$_STATUS1020" not found

Do you have any idea  what can cause this error?

SQL> show parameter aq

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0

SQL> show parameter streams_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0

SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 24G

Solution:

SQL> alter system set aq_tm_processes=1 scope=both sid='*';
SQL> alter system set streams_pool_size = 256M scope = both sid='*';

If you get any error like this:

ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

Reduce the db_cache_size:

SQL> alter system set db_cache_size = 12G scope = both sid='*';

and re-run:

SQL> alter system set streams_pool_size = 256M scope = both sid='*';

Now, you can run the expdp again without issues. Once done, don't forget to roll back all changes.


How do I create a partition using a bit field in SQL Server 2014 I have a table where data is archived. I need to keep this data as it is rarely viewed.

Instead of moving archived data to a separate table, I wanted to create a partition on the Archived field. When I use the designer to partition, the value look all wrong.

The table currently has 42387343 rows (29518148 archived,12869195 active), but this will rapidly increase.

I'm using a Clustered ColumnStore Index

Can / Should / How do I partition on a bit field?

I used this script, but it made the queries much slower:

BEGIN TRANSACTION
CREATE PARTITION FUNCTION [Archived](bit) AS RANGE LEFT FOR VALUES (N'0', N'1')

CREATE PARTITION SCHEME [Archived] AS PARTITION [Archived] TO ([PRIMARY], [PRIMARY],     [PRIMARY])

DROP INDEX [IX_AttributeDataLog] ON [dbo].[AttributeDataLog] WITH ( ONLINE = OFF )

CREATE CLUSTERED INDEX [ClusteredIndex_on_Archived_635544995752812167] ON [dbo].[AttributeDataLog]
(
[Archivable]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [Archived]([Archivable])

DROP INDEX [ClusteredIndex_on_Archived_635544995752812167] ON [dbo].[AttributeDataLog]

CREATE CLUSTERED COLUMNSTORE INDEX [IX_AttributeDataLog] ON [dbo].[AttributeDataLog]
 WITH (DROP_EXISTING = OFF, DATA_COMPRESSION = COLUMNSTORE) ON [Archived]([Archivable])

COMMIT TRANSACTION

Every query will include the IsArchived field (except the queries that run to set IsArchived, and some rarely used historical reports). I would normally put it in its own table, but there are changes that can set archived data, to non archived. These changes can happen at any time.

To get partition elimination, your queries need to be explicit about the bit data type, for example:

...WHERE Archivable = CONVERT(bit, 0)
-- NOT
...WHERE Archivable = 0

Without this, the risk of truncation in the implicit conversion means partition elimination is not applied.

The implicit conversion in the CREATE PARTITION FUNCTION statement is not a contributing factor, because SQL Server will do the conversion to bit for you, once, when the function is created. Still, it would be nicer/better practice to write:

CREATE PARTITION FUNCTION [Archived](bit)
AS RANGE LEFT
FOR VALUES
(
    CONVERT(bit, 0),
    CONVERT(bit, 1)
);

It might be easier all round to avoid these potential issues by making the Archived column an integer instead, with a CHECK constraint to enforce the allowed values.


T-SQL - Script all tables creation, contraints, indexes, keys and triggers I'm using SSMS and I was wondering if there is a script that can generate tables creation, constraints, indexes, keys and triggers for a given table names as a single result and another script that will get the INSERT statement for all the data in the tables with the names in a list? I know that I could use right-Click on the database in object explorer, choose Tasks -> Generate Scripts... but I want to do this for multiple tables in the same time, as a script. Is it possible to achieve this?

To clarify.  You want to generate a script for all objects in a database and then for a select set of tables, have it generate the script to create the data?

Just do it in two steps.  One pass with the Generate Scripts as you have described, selecting all the objects you want to generate the create scripts for.

Then do it again, this time for just the tables you want, but make sure to select the Advanced (on the "Set Scripting Options" section) --> Types of data to script == "Data Only"

You can then further select either all scripts to a single file or a single file per object.

If you want to do this on a regular basis (or more dynamcially), you can interact with the Generate Scripts engine through scripts. This is a good article on getting started.  https://www.mssqltips.com/sqlservertip/1833/generate-scripts-for-database-objects-with-smo-for-sql-server/


How to use Join for two tables that have related data but in different column names I have to create an sql code to combine all the tables but when im using join, its creating another instance of the same column that's existing in both tables along with the column that is not. How can i only get the column that is not in table 1?

For example
In table 1 I have

FORMID
STATUS
TITLE
DESCRIPTION

In table 2 I have

HSID
FORM ID

when I use join I get the following output:

FORMID - STATUS - TITLE - DESCRIPTION - HSID - FORMID_1

Eg. CODE:

SELECT *
FROM PROJECT_FORM
JOIN ASSIGNMENT
    ON PROJECT_FORM.FORMID=ASSIGNMENT.FORMID

You have to specifically list the columns you are interested in, rather than simply stating *

The first table can remain like this:
SELECT PROJECT_FORM.*, …
then you list the columns you require from the other table one by one.

SELECT PROJECT_FORM.*, ASSIGNMENT.HSID
FROM PROJECT_FORM
JOIN ASSIGNMENT ON PROJECT_FORM.FORMID=ASSIGNMENT.FORMID


Solving supertype-subtype relationship without sacrificing data consistency in a relational database Trying to get better at designing databases, I noticed I'm always stuck trying to solve variations of the exact same problem.

Here is an example using common requirements:

An online store sells different categories of product.
The system must be able to retrieve the list of all product categories, say food and furniture.
A customer may order any product and retrieve his order history.
System must store specific properties depending on the product category ; say the expiration_date and calories for any food product and manufacture_date for any furniture product.

If it wasn't for requirement 4, the model could be quite straightforward:

Problem is trying to solve requirement 4. I thought of something like this:

In this approach, the relationships product-furniture and product-food are supertype-subtype (or superclass-subclass) associations; the primary key of the subtype is also a foreign key to the supertype primary key.

However, this approach can not guarantee the category referenced via a foreign key to the product will be consistent with its actual subtype. For instance, nothing stops me from setting food category to a product tuple having a subtype row in the Furniture table.

I read various articles about inheritance in modelling relational databases, especially this one and this one which were very helpful but didn't solve my problem for the reason mentioned above. But whatever model I come with, I'm never satisfied with the data consistency.

How can I solve requirement 4 without sacrificing data consistency ? Am I going all wrong here ? If so, what would be the best way to solve this problem based on these requirements ?

One common way is to add a classifier that is "inherited" like:
CREATE TABLE products
( product_id ... NOT NULL PRIMARY KEY
, ...
, product_type ... NOT NULL
,     UNIQUE (product_id, products_type)
,     CHECK (product_type IN ('food', 'furniture'))
);

product_type would typically be a code of some kind. Might be a foreign key to a "lookup" table instead of a check constraint. For the sub-types:
CREATE TABLE food
( product_id ... NOT NULL PRIMARY KEY
, ...
, product_type ... DEFAULT 'food' NOT NULL
,    FOREIGN KEY (product_id, product_type)
     REFERENCES products (product_id, product_type)
,    CHECK (product_type = 'food')
);

and a similar one for furniture. The constraints guarantee that product_type is consistent between super- an sub- tables.
There are products (I've heard) that allow sub-selects in CHECK constraints, but the majority do not. For such product something like:
CREATE TABLE food
( product_id ... NOT NULL PRIMARY KEY
, ...
,    FOREIGN KEY (product_id)
     REFERENCES products (product_id)
,    CHECK (
         (SELECT product_type
          FROM products p
          WHERE p.product_id = product_id) = 'food'
     )
);

could be used.
An alternative to the latter is to use before triggers for insert/update, and signal an exception if the wrong product_type is used. Personally I don't fancy using procedural code for integrity constraints, but I guess it is a matter of taste.


How to join same department with multiple staff? I have 2 tables.

staff

+----+- ----------+-----------+-------------+------------+
| ID | fullname   | email     | countryCode | departCode |
+----+------------+-----------+-------------+------------+
| 1  |  Richard   | richard@  | 1           | 1          |
| 2  |  Tom       | tom@      | 1           | 1          |
| 3  |  Andy      | andy@     | 2           | 2          |
| 4  |  Elsa      | elsa@     | 2           | 3          |
| 5  |  Leo       | leo@      | 1           | 3          |

department

+------------+- ------  ----+
| departCode | departName   |
+------------+--------------+
| 1          |  Management  |
| 2          |  HR          |
| 3          |  IT          |

The 1st method group_cat for my query the output:

select d.departName ,
GROUP_CONCAT(s.fullName SEPARATOR ' <,> ') 'fullName' ,
GROUP_CONCAT(s.email SEPARATOR ' <,> ') 'email' from staff s inner join department d on s.departCode = d.departCode
where s.departCode and s.countryCode=2
group by d.departCode

Gives the output:

+------------+---------------------+--------------------+
| departName | fullname            | email              |
+------------+---------------------+--------------------+
| Management |  Richard <,> Tom    | richard@ <,> tom@  |
| HR         |  Andy               | andy@              |
| IT         |  Elsa <,> Leo       | elsa@ <,> leo@     |

The 2nd Method I use:

SELECT d.departName, s.fullName, s.position, s.site, s.ext, s.did, s.mobile, s.email from staff s
left join department d on s.departCode = d.departCode
left join country c on s.countryCode = c.countryCode
where c.countryCode = 2

Gives the output:

+------------+---------------------+--------------------+
| departName | fullname            | email              |
+------------+---------------------+--------------------+
| Management |  Richard            | richard@           |
| Management |  Tom                | tom@               |
| HR         |  Andy               | andy@              |
| IT         |  Elsa               | elsa@              |
| IT         |  Leo                | leo@               |

How can I use 2nd method to combine the department with multiple staff and result show multiple staff in one department?

Do you mean you want to left join but then show single departments with the staff concatenated?

SELECT d.departName, GROUP_CONCAT(s.fullName SEPARATOR ' <,> '), GROUP_CONCAT(s.email SEPARATOR ' <,> ')
from staff s
left join department d on s.departCode = d.departCode
left join country c on s.countryCode = c.countryCode
where c.countryCode = 2
group by d.departName


Call Powershell command string from Sql Query Publish profile script Is there a way to call powershell in SSMS Sql query window? I know Command prompt can be invoked with xp_cmdshell. Does similar method for powershell exist ? (Note, I know about powershell window , but want to pass a script string). want to call powershell script in our Post Deployment script publish profile.

https://docs.microsoft.com/en-us/sql/powershell/run-windows-powershell-from-sql-server-management-studio?view=sql-server-2017

If you turn on SQLCMD mode (from the SSMS Query menu), you can use SQLCMD commands within an SSMS query window. A line beginning with !! will execute the text that follows as an OS command on the client. For example:

!! Powershell -Command "Write-Host ('This message is from PowerShell running on machine ' + $env:COMPUTERNAME)"

The command will similarly be executed when the script is run with the SQLCMD command-line utility with this technique.

Note that the "script string" mentioned in your question will need to be hard-coded in the T-SQL script.


using IDENTITY column with increment -1 I need to run a sql server logging db with 2 main tables in 2 seperate datacentres writing to both at the same time.

I had the idea of restoring the db to the new datacentre and then reseeding the identity column to -1 and setting the increment to -1, that way there would never be any chance of duplicate id's when the data needs to be combined.  DATACENTRE1 would be positive integers, DATACENTRE2 negative integers

would an increment of -1 cause any issues?

Going backwards just feels wrong to me.

With only two data centers you could also implement identity ranges. Unless you cycle through identity values at an alarming rate, there is no reason you can't have:

-- Data center 1
CREATE TABLE dbo.Table
(
  ID INT IDENTITY(1,1) PRIMARY KEY
  -- , ...
);

-- Data center 2
CREATE TABLE dbo.Table
(
  ID INT IDENTITY(1000000000,1) PRIMARY KEY
  -- , ...
);

This would allow to generate 1 billion (well, 999,999,999) values in data center 1 before there is any danger in collision with data center 2. And you could add a CHECK constraint in data center 1 to prevent overlapping values, depending on how you prioritize errors vs. duplicates. You could also implement a recurring job that periodically checks how close you are to the lower bound of the other data center, if you're concerned that you'll really generate over a billion values in either data center in the app's lifetime (never mind yours).

If ~1 billion is not enough, there are two other alternatives to give more room to breathe:

-- Data center 1
  ID INT IDENTITY(-2147483648,1)

-- Data center 2
  ID INT IDENTITY(1,1)

This would give each data center over 2 billion non-overlapping values, with no danger of collision until data center 1 approached 0. And if that's still not enough:

-- Data center 1
  ID BIGINT IDENTITY(-9223372036854775808,1)

-- Data center 2
  ID BIGINT IDENTITY(1,1)

Or if you want to keep all values > 0, you could split the positive range roughly in half (you can be a little more precise than this if you're more pedantic than me).

-- Data center 1
  ID BIGINT IDENTITY(1,1)

-- Data center 2
  ID BIGINT IDENTITY(4611600000000000000,1)

I don't even know how to say that number, but it's ginormous. And in reality you'd have to work extremely hard on a very fast computer to use up all those values by the time your great great great great great grandchild graduates from college. And if you are on Enterprise Edition you can use data compression so that you don't pay the hit for all 8 bytes, at least in data center 1, until you exceed 2 billion there.

In a system I managed I did this a slightly different way - we had multiple web servers with Express instances that were responsible for generating ID numbers that needed to be unique in the enterprise. So we just set up a sequence generator on each machine (they didn't need to actually store the value) using a BIGINT IDENTITY column. We had < 9 servers, so they were all seeded like this:

-- WEBAPP0001
ID BIGINT IDENTITY(10000000000,1)

-- WEBAPP0002
ID BIGINT IDENTITY(20000000000,1)

-- WEBAPP0003
ID BIGINT IDENTITY(30000000000,1)

...

When the values were used and then merged in the central system, not only were we guaranteed to not have any duplicates, but it was also easy to instantly recognize which web server they came from (which was sometimes useful in debugging) without introducing any composite key requirements. And we had no concerns that any one web server would ever generate more than 10 billion values.

Many people would go with a GUID in this scenario, but I think there are several strong arguments against that approach.


SSRS 2016 Scale out deployment Got 2 Servers in Scale out deployment and connection is through a load balancing URL.

1) SSRS01
2) SSRS02

Everything works fine when the session is running from SSRS02 but keep getting the error from the screenshot below when trying from SSRS01.

I have looked at permissions. They are exactly the same. I can't find anything relevant in the report server logs too.

Any help on this would be really helpful .

The issue was resolved by removing the reportserverurl entry in the config file


INSERT .. ON CONFLICT DO UPDATE SET error When I use the following query :
cur.execute("""INSERT INTO TABLE
(SELECT ID_tmp,First_seen_tmp,Last_seen_tmp,Duration_tmp
FROM tmp_table)
ON CONFLICT (ID)
DO
UPDATE SET Last_seen=tmp_table.Last_seen_tmp,Duration=tmp_table.Last_seen_tmp-First_seen;""")

I keep getting this error :
psycopg2.errors.UndefinedTable: missing FROM-clause entry for table "tmp_table"
LINE X: UPDATE SET Last_seen=tmp_table.Last_seen_tmp,Duration=tmp...

And even if I try to use the special table EXCLUDED that references to the possible inserted values with the following code :
cur.execute("""INSERT INTO TABLE
(SELECT ID_tmp,First_seen_tmp,Last_seen_tmp,Duration_tmp
FROM tmp_table)
ON CONFLICT (ID)
DO
UPDATE SET Last_seen=EXCLUDED.Last_seen_tmp,Duration=EXCLUDED.Last_seen_tmp-First_seen;""")

I get another error :
psycopg2.errors.UndefinedColumn: column excluded.last_seen_tmp does not exist
LINE X: UPDATE SET Last_seen=EXCLUDED.Last_seen_tmp,Duration=EXCLUDE...

Is it impossible to update the values of the original table with values from the tmp table with an INSERT .. ON CONFLICT CLAUSE ?

The excluded record needs to reference columns from the target table, not the source (e.g. a VALUES clause would not have any column names anyway).
It's also good coding practice to always specify the target columns in the INSERT statement.
INSERT INTO the_table (id, first_seen, last_seen, duration)
SELECT ID_tmp, First_seen_tmp, Last_seen_tmp, Duration_tmp
FROM tmp_table
ON CONFLICT (ID)
DO UPDATE
   SET last_seen = EXCLUDED.last_seen,
       duration = EXCLUDED.last_seen - the_table.first_seen;


Postgres table contains 1 column, and that column is PK. How to insert new record? In my application, I have an abstract base class, and 2 derived classes.  The number of derived classed will probably not increase.  If it does, it won't be more than a few.

The class structure looks like this:

abstract class Base
{
    int FirstParameter
    int SecondParameter
    abstract void DoSomeWork();
}

class Foo : Base
{
    int FoosParameter
    override void DoSomeWork(){...}
}

class Bar : Base
{
    override void DomeWork(){...}
}

Essentially, Bar only needs the data provided by Base, but does its own work on the data.  From a data standpoint, Bar and Base are the same.  Foo, on the other hand, has some extra data tacked on to it.

The database has 3 tables, one for Base, one for Foo, and for Bar.  Since the number of subclasses is not going to grow significantly, it makes more sense to give each class its own table.

tblBase:

base_id PK,
first_parameter int not null,
second_parameter int not null,
foo_id int,
bar_id int

tblFoo:

foo_id PK,
foos_parameter int not null,

tblBar:

bar_id PK

tblBar consists of 1 column, which is the auto incrementing primary key.  The primary key is stored in tblBase in column bar_id, which will identify any Base as being a Bar.

Now, for the real kicker:  How does one insert a new record into tblBar, when the only column is the auto incrementing primary key?  I suppose I could do

insert into tblBar(bar_id)
select max(bar_id) + 1
from tblBar

but that seems a bit....clunky.

Doing a insert .. select max() ... is a bad idea to begin with as it doesn't work correctly in a multi user environment. Plus it would get the sequence behind the serial column out of sync with the already generated values.

What you are looking for is:

insert into bar (bar_id) values (default);

Another option would be explicitly call nextval() when inserting.

insert into bar (bar_id) values (nextval('bar_bar_id_seq'));


Pause postgres process already in progress A colleague and I are both working on the same postgres database. We both started memory intensive queries which have now been running for several hours. The machine that hosts the database has limited RAM so both our processes are running extremely slowly. My colleague's query (the creation of a materialized view) typically takes 4 hours to complete but has now been running most of the day.

Since my colleague's query is more important than mine and needs to be completed by a certain time; is there a way I can pause my query thereby increasing the available RAM and allowing my colleague's process to complete?

My query is a large 'update' to a table and ideally I don't want to kill it a have to start all over again. Is there anyway I can reduce or temporarily suspend the resources consumed by my process?

Postgres 9.4/Redhat 6.8

If you have access to the OS account under which postgres runs, you can identify the PID of your process (using top, or ps, or pg_stat_activity), and then do a kill -SIGSTOP <pid>.  Later you would then do a kill -SIGCONT <pid> to get it to run again.

This is not a risk-free thing to do.  If you stop the process while it is holding a spinlock (pretty unlikely, but certainly possible), you could freeze up the entire database, until eventually it would realize something was wrong and then crash itself (terminating your colleague's job).  If you stop the process while it is holding a lightweight lock, it would also freeze the database (or parts of it) and it remain frozen until you continue the process and it releases the lock.

Also, the memory of the stopped process will not be released.  The system will be able to swap that memory out without it constantly fighting to swap back in, and maybe that is good enough.  But it would be better if it were actually released.

All in all, it is probably a better idea to cancel your update and repeat it later.


How is a table locked regarding UPDATE-SELECT queries in PostgreSQL? Is it possible to combine these UPDATE queries?

UPDATE myTable1 SET fld1 = myTable2.value
FROM myTable2
WHERE myTable1.id = myTable2.id AND myTable2.style = 45;
UPDATE myTable1 SET fld2 = myTable2.value
FROM myTable2
WHERE myTable1.id = myTable2.id AND myTable2.style = 46;
UPDATE myTable1 SET fld3 = myTable2.value
FROM myTable2
WHERE myTable1.id = myTable2.id AND myTable2.style = 47;

And also, how is the lock on these two tables?

The question about locks was addressed by a_horse_with_no_name in the comments to the question:

  an UPDATE will never lock a table - it will only lock the rows that have been updated (in the target table). A select never locks any rows

And as for combining the three update statements into one, yes, it is possible, though not necessarily a good idea. Still, if you insist, here is one way:

UPDATE
  myTable1
SET
  fld1 = CASE myTable2.style WHEN 45 THEN myTable2.value ELSE myTable1.fld1 END,
  fld2 = CASE myTable2.style WHEN 46 THEN myTable2.value ELSE myTable1.fld2 END,
  fld3 = CASE myTable2.style WHEN 47 THEN myTable2.value ELSE myTable1.fld3 END
FROM
  myTable2
WHERE
  myTable1.id = myTable2.id
  AND myTable2.style IN (45, 46, 47)
;

Basically, you do the join, combine the three filter conditions with an IN predicate and in in each of the three assignments you use a CASE expression to check if the myTable2.style value matches a corresponding value from the filter, so that you can decide whether to assign the myTable2.value to the column (THEN branch) or keep the old value (ELSE branch).

Your three separate statements look clearer/more readable to me, though.


How to isolate client data on the same application using MySQL I have made an application which has a companies table at the moment. It would work fine for a few clients (~100 companies, according to my scaling calculations). The application is a simple CRUD app accessed on the web using REST protocols. Very few (if none) concurrent transactions take place at the same time.

At the moment the companies table is the most independent table of all. All others like employees, benefits, time_off, etc. are related to each other via the companies table.

Someone suggested that I could use MySql's schemas to isolate one company from another, and clients would see the same exact app. Essentially, the moment I sign up a new company, a new schema would fire up and create the same tables as it has for any other company. How does this work? Could I have multiple schemas on the same database? I'm working with AWS RDS so I don't wish to pay for multiple databases here as the total size of the data even after half a year's use has not exceeded half a GB.

In MySQL schema and database are synonymous: you use CREATE DATABASE or CREATE SCHEMA with equal effect. There's little physical isolation between schemas (databases), workload against one schema can potentially affect all schemas  on the same server. However, there is logical access isolation: you can allow certain user(s) access to a specific schema only using GRANT ... ON schema.* TO ....


How to allow creation and deletion of a database or collections with user and password I created a user with the role of userAdminAnyDatabase

db.createUser({user: "Username", pwd: "password", roles: [{role: "userAdminAnyDatabase", db: "admin"}]})

and also user with the role of userAdmin for a single db in the mongo shell.

db.createUser({user: "Username", pwd: "password", roles: [{role: "userAdmin", db: "myDB"}]})

But when I create or delete collections in myDB it's not asking me for the user name or password. What is the point of creating a user then?

I want the creation and deletion of databases and collections only when I give in the admin credentials.

No, system don't work that way. You must enable authentication and authorization of your mongodb.

After that, you log in with userAdmin credentials and you (only) have authorization to do userAdmin work, not system admin work. But remember to create super-admin user (before enabling authenticaton) or you end up to situation that you don't have superuser (root) who can give other needed rights.


Can STRING_AGG be used multiple times with different delimiters? I was doing some string aggregation and noticed what looked like a bug to me. STRING_AGG was used twice in the same query with different separator arguments. However, both produced the same results (the "first" separator is used in both cases). It seems to happen only in some circumstances. Is this a bug or is this documented behavior?

First, the setup:

CREATE TABLE #Data
  ([Group] INT
 , Member  CHAR(1));

INSERT INTO #Data
VALUES (1, 'a'), (1, 'b')
     , (2, 'c'), (2, 'd');

From a table vs. VALUES constructor. Without WITHIN GROUP ORDER BY, everything is fine. With it, the bug appears

SELECT Commas = STRING_AGG(Member, ', ') --WITHIN GROUP(ORDER BY Member)
     , Colons = STRING_AGG(Member, '::') --WITHIN GROUP(ORDER BY Member)
FROM #Data;

SELECT Commas = STRING_AGG(Member, ', ') --WITHIN GROUP(ORDER BY Member)
     , Colons = STRING_AGG(Member, '::') --WITHIN GROUP(ORDER BY Member)
FROM (VALUES (1, 'a'), (1, 'b')
           , (2, 'c'), (2, 'd')) [v] ([Group], Member);

Adding in a GROUP BY adds a sort and the error is there for both cases now:

SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG(Member, '::')
FROM #Data
GROUP BY [Group];

SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG(Member, '::')
FROM (VALUES (1, 'a'), (1, 'b')
           , (2, 'c'), (2, 'd')) [v] ([Group], Member)
GROUP BY [Group];

Add it's definitely broken... like deep down. Combine both expressions in a HAVING and you get nothing back. Use either one alone, and you get data.

SELECT [Group]
     , Colons = STRING_AGG(Member, '::')
     , Commas = STRING_AGG(Member, ', ')
FROM #Data
GROUP BY [Group]
HAVING STRING_AGG(Member, ', ') LIKE '%, %'
   AND STRING_AGG(Member, '::') LIKE '%::%';

Switching the order of the columns switches the separator used.

I used these trace flags for investigating, but I can't find anything about "optimizing" away the second aggregate.

OPTION (QUERYTRACEON 3604 -- Output info to client
      , QUERYTRACEON 8619 -- Show applied optimization rules
      , QUERYTRACEON 8606 -- Show logical query trees
      , QUERYTRACEON 8607 -- Show physical query tree
      , RECOMPILE);

This is a bug. This answer indicates that it is fixed in CU17 but I just tested that and found it negative (and there is no indication that the fix was intended to be backported here).

I tried

SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG(Member, '::')
FROM (VALUES (1, 'a'), (1, 'b')
           , (2, 'c'), (2, 'd')) [v] ([Group], Member)
GROUP BY [Group]
OPTION (QUERYTRACEON 3604 -- Output info to client
      , QUERYTRACEON 8607 -- Optimization output tree, before post-optimization rewrite phase.
      , QUERYTRACEON 7352 -- The final query tree after Post-optimization re-write.
      , RECOMPILE);
;

In 2017 CU17. This gives plan

On 2019 there is no compute scalar and the Stream Aggregate has defined values

[Expr1010] = Scalar Operator(STRING_AGG([Union1009],', ')),
[Expr1011] = Scalar Operator(STRING_AGG([Union1009],'::'))

On 2017 the Stream Aggregate has defined value

[Expr1010] = Scalar Operator(STRING_AGG([Union1009],', '))

And there is an additional Compute Scalar with expression

[Expr1011] = Scalar Operator([Expr1010])

The output of trace flag 8607is the same on 2017 and 2019 but 7352 differs so I assume this is a faulty post optimisation rewrite.

As a workaround you can change the expression to do a no-op as below to prevent this faulty optimisation.

SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG('' + Member, '::')


How to succesfully run a batch file in an SQL Agent job? I have a SQL Agent Job which generates a specific report in PDF-file and then copies the PDF to a network directory and then deletes the PDF file in the source directory.

The SQL Jobs consists of 2 steps:
1. Generate the report
2. Copy the report to the network location.

For step 2 I made a bat-file which handles the copying and removal of the pdf file.

The bat-file is as follows:

set source_dir=C:\Reports\Energie\Uur
set dest_dir=\\KWS2-WEB-SERVER\Share\Reports\Uur

C:\Windows\System32\Robocopy.exe %source_dir% %dest_dir% /MOV /Z

However, when I run my the Job, it hangs on the second step. The status just stays on "Executing".

This is the line which I stated in the step (location of the bat-file to execute):

cmd.exe /c "C:\Reports\rapport_uur_copy.bat"

My job-settings are as follows:

Step 1

Type: Operating system (CmdExec)
On Success: Go to the next step

On Failure: Quit the job reporting failure

Step 2

Type: Operating system (CmdExec)

On Success: Quit the job reporting success

On Failure: Quit the job reporting failure

Some facts:

I have read/write permissions on the network directory
I run the steps as the Administrator-account (the logged in user, default)
Step 1 succeeds
I run Windows Server 2008 R2 Standard
I have SQL Server 2008 R2
When I run the script from the cmd manually, it works (under Administrator account).

It looks like you need to configure the second step of your job to run as a domain account with permissions to write to the \KWS2-WEB-SERVER\Share\Reports\Uur share as well as read access to the C:\Reports\Energie\Uur folder.

You first will need to add a credential to SQL Server. From the Security > Credentials folder you will need to right click and choose New Credential... Fill in that information and click OK.

Once that is complete you will need to create a Proxy that uses this credential. From the SQL Server Agent > Proxies > Operating System (CmdExec) folder you will need to right click and choose New Proxy... Use the credential you created earlier.

Now you can configure your second job step to use the Proxy you just created using Run As: drop down of the job step page.


Warning Console code page (437) differs from Windows code page (1252). 8-bit characters might not work correctly in PostgreSQL Whenever I access psql, I get the following message:

psql (9.6.2)
WARNING: Console code page (437) differs from Windows code page (1252)
     8-bit characters might not work correctly. See psql reference
     page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

I tried command

cmd.exe /c chcp 1252

before running runpsql.bat file in command line. And there, it runs fine without the error. But accessing psql again after closing command prompt, throws the same warning. How do I solve this warning within SQL Shell?

I am using windows 10 OS.

Edit:

This is what I did:

Microsoft Windows [Version 10.0.10586]
(c) 2015 Microsoft Corporation. All rights reserved.

C:\Users\Sarthak Joshi>E:

E:\>cd PostgreSQL

E:\PostgreSQL>cd scripts

E:\PostgreSQL\scripts>chcp 1252
Active code page: 1252

E:\PostgreSQL\scripts>runpsql.bat
Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (9.6.2)
Type "help" for help.

postgres=#

But after exiting from cmd, and firing up shell like this:

Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (9.6.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

It still throws the same warning...

When you use cmd.exe /c chcp 1252 in the batch file, you start a new command line, change the codepage for that and then that command line is immediately terminated, but the one that runs psql has not bee changed.

Just put:

chcp 1252

(without calling cmd.exe) into the batch file.


Randomly generated password for mysql admin over time? I have this idea of making my database's Admin account secured by creating the following:

Trigger is activated every hour in mysql this trigger activates a stored procedure
Stored procedure generates a new password using md5(date()+pre shared key+rand())
I have a application in vb that generates the same password every hour that I can then log into with.

However this is still just an idea and I don't know if this is possible, do you know if anyone has tried this?

Any links or reference material.

I was inspired by my friend who has a key that is generated in a little device thing that he hangs around his neck when he logs into a workstation, does anyone know what the this security procedure is called?

What are the limitations or problems that could occur? (server crash/restart)

Thanks!

Why would you want to do this?  What risk does it mitigate?

This sounds like a great way to lock yourself out of your database in a way that requires downtime to recover.  Limit your exposure by securing the servers with firewalls and SSH and keep MySQL authentication simple.  If you are worried about brute force attacks from within your trusted network, set max_connect_errors to a low value so the remote host will get blocked.  And don't use "root" as your root user.  Disable that account and create a new super user with a different name.

That device that your friend has was likely built by RSA and is a SecurID tag which uses two-factor authentication to secure workstations.  An enormous amount of R&D  was built into that product.  Your VB script will not have that luxury.

Quite simply, MySQL authentication is not hardened enough to warrant this type of infrastructure.  An attacker worth his salt provided with access to an open MySQL port is going to look for more general exploits rather than waste time brute forcing.

Just off the top of my head, some of the problems with this:

What happens if your clocks get off by a minute or two?
What if the job or trigger fails?  How do you know what state your DB password is in.
How do you generate the same random string within your VB script and on the server?
How do you store the generated password securely?

Don't do it.  Invest your effort into picking strong passwords (not l33tsp34k) and locking down the server itself rather than attempting to reproduce what a company with thousands of employees at their disposal has managed to do.


Increasing column from VARCHAR(255) in master/slave setup We have replication set up on some MyISAM servers, in a master/save scenario, MySQL v5.1.

One of the columns is currently declared as VARCHAR(255). We want to increase this to VARCHAR(512).

We have one master and 4 slaves. What is the best scenario to make the change.

If we do this:

ALTER TABLE item MODIFY url VARCHAR(512)

should that be ok, as long as we update the slaves first and then the master, or may it complain and it's best to do them all at once? Should I stop the slaves?

I've tried the update on a dev machine which takes about 10 mins, but it doesn't have replication set there. So looking for any flaws in my thinking.

Or looking further, does ALTER TABLE get replicated, and that's all I would need to do on the master?

In a replication environment, for an ALTER that takes a long time, I recommend this:

On one slave:  Take out of rotation (keep clients from using it); perform the `ALTER; put back into rotation.
Repeat for other slaves.
SET @@session.sql_bin_log = 0 so it won't replicate; do the ALTER; set that back to 1.

That way, Slave users will not be impacted; Master user may be impacted.

With Galera / PXC, you would use "RSU" (Rolling Slave Update) and have zero downtime (but have to do it manually in a similar manner).

You need to upgrade to 5.6.  With it many ALTERs (probably including your case) can use the fast ALGORITHM=INPLACE.


Point in time recovery with mariabackup I have a full backup dir and a bunch of hourly (incremental) backup directories taken with mariabackup, which is a port of the XtraBackup.

How does one restore the MariaDB up to a specific minute (or second)? The general form applies all events within the incremental snapshot to the base, but what if we need to be more granular than that? I was hoping to be able to set an upper bound on the LSN or datetime.

Ref: https://mariadb.com/kb/en/library/incremental-backup-and-restore-with-mariabackup/

With a full backup you can only restore to the point in time where the backup was taken.

With a incremental backup you can only restore to the point in time where the backup was taken, but you also need the full backup on which the incremental is based upon.

If you want a true point in time recovery, you have to enable the binary log (as described here).
In a restore scenario, you would recover a full/incremental backup and then rollforward to your point in time usinge the 'mysqlbinlog' command as described here.


Who and when were changed Database default location settings? I need to know when and who changed SQL Server Database default location settings. How can I do that?

I don't think you'll be able to find that out. SQL Server doesn't track this information by default (e.g. in the default trace or system health extended event session), and in fact if you go through the UI and click the Script button instead of saying OK, you will see it just generates a script like this, which writes directly to the registry:

USE [master]
GO
EXEC xp_instance_regwrite
   N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'DefaultLog', REG_SZ, N'C:\new path'
GO

This value is then pulled from the registry next time SQL Server starts (so there is no event to trap when the action happens).

Going forward, you may be able to capture calls to xp_instance_regwrite using something like SQL Server Audit or a targeted server-side trace, but that doesn't help you for something that happened in the past, before something like this is set up.

Sounds like a more appropriate action - rather than trying to figure out who did something you didn't want them to do - is to take away their rights to do things you don't want them to do (e.g. people who you can't trust, or who won't own up to doing something like this, shouldn't have sysadmin).


Enable TDE for existing AlwaysOn databases I need to do a little benchmarking of AlwaysOn, both with and without TDE enabled. All the instructions I see cover adding encrypted databases to an availability group, but I see no mention of enabling encryption for a database already in an AG.

Can I just restore the server certificate to all nodes, then enable TDE from the primary? I'm hoping I don't need to remove the database from the AG, encrypt it, then reinitialize all nodes, as the database is around 100 GB. We can do that if needed, but it'll certainly slow down our testing a bit.

Update

I just tried this on some lab VMs. I was able to create a certificate at the primary, restore it on all the secondary nodes, then create a database encryption key at the primary using this certificate. All I had to do after that was ALTER DATABASE AdventureWorks SET ENCRYPTION ON at the primary server, and after a minute or two, sys.dm_database_encryption_keys showed encryption_state = 3 at all nodes.

So, followup question: Is there any reason to think that I shouldn't use this procedure? SQL Server appears to be happy with the results, but I haven't seen this approach discussed anywhere. When dealing with encryption and high-availability matters, I don't want to leave things to chance.

In the absence of any other input, here's what I was able to deduce. Short story: this appears to work fine.

As far as I can see, enabling TDE for a database is a logged operation, but the actual data encryption is not logged (otherwise the transaction log would grow considerably, which it doesn't). I assume there's something like the differential change map, or a flag in the page headers to indicate if a page is encrypted or not, and the storage engine just chews through everything until it's fully encrypted.

When I enable TDE at the primary database, the secondary database ends up encrypted too (I have verified this with a hex editor). While encrypting is in progress, the view sys.dm_database_encryption_keys indicates percent_complete for all the secondary servers is 0, and this value never increases. The primary server updates this column normally. All the replicas tend to finish encrypting at their own pace, at which point encryption_state changes to 3. Seems like everything works normally once it's finished.


postgres restore configuration? I have a database dump that's about 140Gb.  (When loaded, the database is about 350Gb.)  I occasionally load copies of my dumped data into development and testing databases, which takes a while.

Any thoughts on what configurations I might be able to change during the restore in order to speed things along?

So far I have thought of:

in postgresql.conf:

synchronous_commit=off
checkpoint_timeout=1d

Although I'm less sure about that checkpoint timeout value.

in my shell

PGSSLMODE=disable

And at some point I will try copying the dump file to the target system so that pg_restore can run on the same box, and ideally use the unix domain socket, rather than going over a TCP/IP network.

(9.6 on Ubuntu 17.04)

Have you tried parallel pg_restore, i.e. the -j flag?

fsync=off can be better than just synchronous_commit=off.  In fact, the latter might not do much at all because most of the loading should be done in large COPY commands which wouldn't be committing until the end anyway.

You can also try increasing max_wal_size.

Finally, use tools like top, vmstat, sar, perf, or strace to figure out what the bottleneck is, IO, CPU, or perhaps network or something else.


Can I rely on the MSDTC to guarantee a distributed transaction was completed? I've come across a bug using the TransactionScope from the .NET library, where we're getting a TransactionInDoubt exception. After doing a lot of research into something I don't know much about, I've figured this happens when there is a communication error between the database, and we're unsure if the transaction completed.

Trying to handle this error quickly became a pain however, there seem to be a lot of possible problems that arise.

Basically, I want to know if there is a guarantee that a distributed transaction across multiple databases will be committed or rolled-back on ALL databases, not committed on one and not the another.

From my knowledge, the MSDTC controls a distributed transaction and handles this all for me. However reading up on it a little, it seems if there is a communication problem while trying to confirm the commit to one of the databases, then the commit won't be performed for that database, but the others will be committed.

There are plenty of resources online that discuss this, and it's often a lot of conceptual talk, my simple question is: Can I rely on the MSDTC, or do we have to come up with a separate solution to the problem?

Microsoft's DTC developer documentation says

  The application programmer's transaction model is simple—programs either succeed or fail. .. When the program reaches a consistent state, it calls the Commit method. If the commit succeeds, the transaction is durably committed. If the commit fails, the transaction aborts.

The Administration Guide offers some more under-the covers information

  While a component remains prepared but not committed or aborted, it is in doubt about whether the transaction committed or aborted. If a component or transaction manager fails, it reconciles in-doubt transactions when it reconnects.

So, in the event of a failure a resource (e.g. a database) may be "in doubt". When that database can talk to the cluster again DTC works things out on behalf of the application. If the resource never re-joins the cluster then who cares - that resource is gone and whether it committed or rolled back is neither here nor there.

If someone, somehow manages to talk to that disconnected resource independently of the application or cluster then it may report an incongruous state. It has, after all, been severed from a cluster it expects to be part of.


INSERT INTO... SELECT over a LinkedServer in smaller batches I've written the following code to move data from one database table, to another (over a LinkedServer);

SET NOCOUNT ON

DECLARE @ApplicationURL varchar(50),
    @LinkedServer nvarchar(50),
    @DatabaseName varchar(50)

SET @ApplicationURL = 'Test'
SET @LinkedServer = 'ABC123'
SET @DatabaseName = 'Test'

/*--------------------------------------------------------------------
    Table:  Notifications
--------------------------------------------------------------------*/
EXECUTE (
'INSERT INTO dbo.Notifications
(
    [Subject],
    [Body],
    [PriorityId],
    [StartDate],
    [EndDate],
    [IsActive],
    [UserId],
    [SourceNotificationId]
)
SELECT
    [Subject],
    [Body],
    [PriorityId],
    [StartDate],
    [EndDate],
    n.[IsActive],
    [DS.DataMigration].[Migration].[ufnGetNewUserId](n.[UserId]),
    [NotificationId]

FROM ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Notifications] n
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Users] u ON n.UserId = u.UserId
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Applications] a ON u.ApplicationId = a.ApplicationId
WHERE A.ApplicationURL = ''' + @ApplicationURL + ''';')

This works as expected, however some tables are likely to have over a million rows, and I do not want to try and move them all at the same time, in one transaction. In my intestigation, I found this post; Breaking a big insert query into smaller ones

I would like to use this method, but I've been unable to find out how to impliment this with Dynamic SQL. Has anybody done something like this before?

Thanks!

You could split them up in batches inside the dynamic SQL statement.

An example applied to your insert statement, per 1000 rows with the use of OFFSET ... FETCH :

EXECUTE (

'
DECLARE @RowsProcessed int = 0;
DECLARE @Batch int = 1000;
DECLARE @rowcount int = 1;

WHILE @rowcount!= 0
BEGIN

'INSERT INTO dbo.Notifications
(
    [Subject],
    [Body],
    [PriorityId],
    [StartDate],
    [EndDate],
    [IsActive],
    [UserId],
    [SourceNotificationId]
)
SELECT
    [Subject],
    [Body],
    [PriorityId],
    [StartDate],
    [EndDate],
    n.[IsActive],
    [DS.DataMigration].[Migration].[ufnGetNewUserId](n.[UserId]),
    [NotificationId]

FROM ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Notifications] n
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Users] u ON n.UserId = u.UserId
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Applications] a ON u.ApplicationId = a.ApplicationId
WHERE A.ApplicationURL = ''' + @ApplicationURL + '''
ORDER BY n.NotificationId
OFFSET @RowsProcessed ROWS
FETCH NEXT @Batch ROWS ONLY;

SET @rowcount = @@ROWCOUNT;

SET @RowsProcessed += @Batch;
END
')

Note that you have to add an ORDER BY with the OFFSET ... FETCH. I have chosen the NotificationId  field of the dbo.notifications table. You could change this if needed.

Here is a smaller working db<>Fiddle example.


Class- and instance- type rows I have a problem, which I expect is very common, but I am not sure what to search for, as it can be explained in many different ways.

I am designing a database structure for managing invoices, products, prices, currencies, etc.

I am able to manage prices and names of the products (these are expected to change every now and then).

On invoices, products are added by quantity, with discount, vat, unicorns, squirrels, and so on, and then the total of the invoice is calculated.

Now, when a price is updated in the products table, the invoice is not supposed to change. It needs to stay the same forever.

When thinking about this, to me it seems like the product list is a list of "class type" objects, which describe what a product looks like. Or we could call it a "template" or "base" or "abstract", depending on what abstraction we think of.

The products that are added to the invoice are "instances", "implementations", "children", "versioned records" or whatever we'd call them.

How would I structure this in the database? I could split them into two different tables "AbstractProduct" and "Product", but they would have most (possibly all) of their columns in common. In that regard it would seem appropriate to keep them in the same table. But then I would need an extra column to signify whether it is "class" or "instance". That is fine too. This could also be a foreignkey to the class it was created from (or NULL for classes).

I have tried different approaches, but all seems to have annoying caveats.

Is this a known pattern? What is it called? Are there best practices for solving this issue?

It is a well known pattern.  If you want extensive literature on this topic, search on "temporal database".


Is it possible to uninstall SSRS? I installed SQL Server with Reporting Services package by mistake and I will never have a requirement to use the Reporting Services part.

Is it possible to uninstall Reporting Services (and anything else that would have been included/installed because of Reporting Services)?

Go to Programs and Features, find the Microsoft SQL Server 2012 (64-bit) entry, select Uninstall/Change, and then select Remove in the window that pops up.

You select the instance you are working with:

Then check Reporting Services you installed:


Error 42P01: relation does not exist I'm new at creating databases, and this error has me dumbfounded, as I am super new with DB admin things (I mostly do reporting type queries). I created a new database through pgAdmin3 GUI, and I'm trying to create DB objects in there using SQL but am getting a:

ERROR: relation "replays" does not exist
SQL state: 42P01

I looked through the manual but did not find anything very helpful, though I suspect it may have to do with search_path somehow. Here is a screenshot. Any idea what I'm doing wrong please?

What you had originally was a correct syntax - for tables, not for schemas.  As you did not have a table (dubbed 'relation' in the error message), it threw the not-found error.

I see you've already noticed this - I believe there is no better way of learning than to fix our own mistakes ;)

But there is something more.  What you are doing above is too much on one hand, and not enough on the other.

Running the script, you

create a schema
create a role
grant SELECT on all tables in the schema created in (1.) to this new role_
and, finally, grant all privileges (CREATE and USAGE) on the new schema to the new role

The problem lies within point (3.)  You granted privileges on tables in replays - but there are no tables in there!  There might be some in the future, but at this point the schema is completely empty.  This way, the GRANT in (3.) does nothing - this way you are doing too much.

But what about the future tables?

There is a command for covering them: ALTER DEFAULT PRIVILEGES.  It applies not only to tables, but:

  Currently [as of 9.4], only the privileges for tables (including views and foreign tables), sequences, functions, and types (including domains) can be altered.

There is one important limitation, too:

  You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

This means that a table created by alice, who is neither you nor a role than you are a member of (can be checked, for example, by using \du in psql), will not take the prescribed access rights.  The optional FOR ROLE clause is used for specifying the 'table creator' role you are a member of.  In many cases, this implies it is a good idea to create all database objects using the same role - like mydatabase_owner.

A small example to show this at work:

CREATE ROLE test_owner; -- cannot log in
CREATE SCHEMA replays AUTHORIZATION test_owner;
GRANT ALL ON SCHEMA replays TO test_owner;

SET ROLE TO test_owner; -- here we change the context,
                        -- so that the next statement is issued as the owner role

ALTER DEFAULT PRIVILEGES IN SCHEMA replays GRANT SELECT ON TABLES TO alice;

CREATE TABLE replays.replayer (r_id serial PRIMARY KEY);

RESET ROLE; -- changing the context back to the original role

CREATE TABLE replays.replay_event (re_id serial PRIMARY KEY);

-- and now compare the two

\dp replays.replayer
                                   Access privileges
 Schema  │   Name   │ Type  │       Access privileges       │ Column access privileges
─────────┼──────────┼───────┼───────────────────────────────┼──────────────────────────
 replays │ replayer │ table │ alice=r/test_owner           ↵│
         │          │       │ test_owner=arwdDxt/test_owner │

\dp replays.replay_event
                               Access privileges
 Schema  │     Name     │ Type  │ Access privileges │ Column access privileges
─────────┼──────────────┼───────┼───────────────────┼──────────────────────────
 replays │ replay_event │ table │                   │

As you can see, alice has no explicit rights on the latter table.  (In this case, she can still SELECT from the table, being a member of the public pseudorole, but I didn't want to clutter the example by revoking the rights from public.)


Export SQL Agent jobs with steps and scripts I recently saw some kind of export of SQL jobs configured under SQL Server Agent that I would like to achieve as well.
Unfortunately I don't have any trace to author of that report to ask him\her about report creation, hence looking for help here as I had no luck googling it nor searching in here.

Report was in excel (so I guess originally was some csv or maybe directly xls) and contained every single job from SQL instance with one step per row and most importantly containing also script content of each job step and some further details.

As for an example as SQL has default job syspolicy_purge_history only, export will contain 3 rows for each of it's step.

I can't seem to find it as any build in export, so I guess some query maybe or I have missed this somewhere

Anyone aware of any such report?  Any help is welcome

Would something like this work?

USE [msdb]
GO
SELECT
    j.Name
    , j.[Description]
    , js.Step_ID
    , js.Step_Name
    , js.Database_Name
    , js.Command
    --, j.*, js.*
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobsteps js
    ON js.job_id = j.job_id
--WHERE j.enabled = 1
ORDER BY j.name, js.Step_ID


How to get current month and year of the database as a macro in SQL server? I want to get the current month and year of the system into a macro variable and keep using that macro variable to create related new variables. The purpose is that if I run my script every month, I will have a new macro variable for each month. I know how to achieve this in SAS:

%let month = %sysfunc(today(),monyy5.);

%let Updated = Product_&month;

So far, I think in SQL server I can use GETDATE() to get the current date and then use CONVERT to convert in my desired format. I am not sure how the rest of it will be done. Any help/advice/suggestions/thoughts much appreciated.

Use the DATEPART and/or DATENAME functions.
If you want to reuse, you can create a function to return a string.

DECLARE @Updated VARCHAR(20)

-- numeric month
SET @Updated = 'Product_' + CONVERT(VARCHAR, DATEPART(MONTH, GETDATE())) + CONVERT(VARCHAR, DATEPART(YEAR, GETDATE()))
PRINT @Updated

Product_112018

-- 3 letter month
SET @Updated = 'Product_' + CONVERT(VARCHAR, LEFT(DATENAME(MONTH, GETDATE()), 3)) + CONVERT(VARCHAR, DATEPART(YEAR, GETDATE()))
PRINT @Updated

Product_Nov2018


How to group same columns from same two tables, but from two different result sets and/or conditions I am having troubles coming up with a query for something that should be quite simple.

Initially, I had two tables of data that I generated joint result sets from; an events table, and a groups table (think communities or organisations). I would use a query to get event data, such as event name, ID, the group that hosted the event (single ID only), amongst others. Every single event and group has its own unique ID number, given by AUTO_INCREMENT. I would use the following query:

SELECT g.group_id,
       g.name,
       prev_id,
       activity_state+0,
       state,
       first_event_id,
       last_event_id,
       GROUP_CONCAT(e.event_id ORDER BY start_date_time SEPARATOR ','),
       GROUP_CONCAT(e.name ORDER BY start_date_time SEPARATOR '__'),
       GROUP_CONCAT(start_date_time ORDER BY start_date_time SEPARATOR ',') AS first_dates,
       GROUP_CONCAT(end_date_time ORDER BY start_date_time SEPARATOR ',')
FROM events e
INNER JOIN groups g ON e.group_id=g.group_id
WHERE start_date_time BETWEEN ? AND ?
GROUP BY g.name
ORDER BY first_dates

The two question marks represent two bound dates and times in a prepared statement. This query would work perfectly fine.

However, I recently updated the structure of the database to include so-called "joint events", where multiple groups host the same events together. I added a joint_event table, which simply contains an event_id column, and a group_id column. The same event IDs appear multiple times in the table (at least twice), and is used to indicate which groups hosted particular events. Joint events are also added to the event table, but are given an ID of 0 (zero).

I now need a query that is similar in functionality to the one above. The query must include the initial result set from the event table and group table join condition, also any occurrences of joint events matching the same conditions from the joint events table. They must be appropriately grouped together, so that I don't get duplicates of the same data (each group should appear in its own row, and only one row per group).

Separately, the join conditions in SQL-89 syntax would be as follows:

events.group_id = groups.group_id

(gives the bulk of results, at least 98%, plus...)

events.event_id = joint_events.event_id AND joint_events.group_id = groups.group_id

(for the joint events)

I have tried using UNION, but it doesn't appear to support grouping by the entire data set returned whole. I have tried using subqueries, but run into issues when using aliases to describe the same columns across two separate data result sets, as it usually produces a "ambiguous" column name error. Using DISTINCT didn't work, as it would either remove events that have the exact same name as each other (these need to be kept), or create an inconsistency in the grouped columns when not applied to all four columns that need to be grouped. The closest I came to was a query that resulted in the joint event data being separated from the rest, instead of being grouped into the rest; or the data was being included, but event data was being duplicated.

I cannot have NULLs anywhere, or use two completely separate queries, otherwise I would have to refactor my PHP script, and would likely make the operation inefficient using PHP to sort the data out.

Thanks in advance.

Keep in mind that queries like this operate thus:

JOIN all the tables together, generating a 'large' temp table with all combinations that have not been filtered out.
Then do the aggregates (SUM, GROUP_CONCAT, etc)

A common problem is that SUMs and COUNTs are inflated because of all the combinations.  In your case, GROUP_CONCAT may have duplicates; this can be handled by saying GROUP_CONCAT(DISTINCT ...).

A common solution is to focus on the aggregates, using a minimum of JOINs to get the aggregates right, then JOIN to the rest of the tables.

Another solution that sometimes works is to replace a JOIN with a subquery (in the SELECT part) that computes "looks up" a value (such as mapping an id to a name).

You must be using an older version of MySQL; new versions don't like you to say GROUP BY one-column while listing 7 columns in the SELECT.

Sorry, this is only "handwaving", but your question is also missing details, such as details of "when I added...", SHOW CREATE TABLE, specifying which table each column belongs to, etc.


Select records that fit criteria in another record I have a procedure that I want to return only when a second procedure was selected.

Id  Name      Procedure
1  Jim Smith  Procedure1
2  Jim Smith  Procedure2

I would like the first record returned only if Jim Smith had both procedures.

select t1.*
  from table t1
  join table t2
    on t2.name = t1.name
   and t1.procedure = 'Procedure1'
   and t2.procedure = 'Procedure2'


Identify File Growth Events I'm discovering a number of DBs in my portfolio that have been created with default autogrow settings (either 1 MB or 10% increments) that have expanded for an extended period of time. If I wanted to get a gauge of the amount of external fragmentation for each of the DB files, can I get from metadata how many times a DB file has been modified in size (either by autogrow or manually)? To clarify, can I get from metadata the history of DB file modifications for the life of the DB, not just since instance restart?

You can get autogrowth events information from the default trace if it is enabled:

select distinct
    ei.eventid,
    e.name
from sys.fn_trace_geteventinfo(1) ei
inner join sys.trace_events e
on e.trace_event_id = ei.eventid
where name like '%grow%';

You can see from this that the default trace does have the Data File Auto Grow and Log File Auto Grow events capture.  To see if you have the default trace enabled on that instance, you can do the following:

exec sp_configure 'default trace enabled';
go

Note: this is an advanced configuration option, so show advanced options would have to get set to 1 to view this configuration option through sp_configure.  Also, these two events will not be triggered if the file grew manually.

Here is a quick sample query to get these events:

select
    te.name as event_name,
    tr.DatabaseName,
    tr.FileName,
    tr.StartTime,
    tr.EndTime
from sys.fn_trace_gettable('<Trace Path>', 0) tr
inner join sys.trace_events te
on tr.EventClass = te.trace_event_id
where tr.EventClass in (92, 93)
order by EndTime;

And you can get <Trace Path> from the system function sys.fn_trace_getinfo:

select *
from sys.fn_trace_getinfo(1);


quick checkpoints for development mode i'm a programmer, not a dba. i need a mechanism to quickly make a snapshot of a database (oracle 11g XE) before i run migration. migration changes data, grants, DDL (tables, triggers, lexers, views etc). in case migration fails, i want to quickly revert to the saved state. all this will be done on non-production environment. it's just to speed up the development process

i've heard about full backups, flashbacks, snapshots but i have no idea what's the difference and which one is best for my task

The best option would be to use the Flash recovery area option, then set a restore point before you make your change and you can revert to the restore point after you make your change, hence you can rerun the script over and over until you work out the issues. You probably need Enterprise edition to do that, but you can certainly try to make it work. The next best option would be to do an RMAN backup and then run your script and recover the database after the script runs. If it really is a dev system, and if space is tight you might as well turn off archive logging and do a warm backup of the database. hence it is in a consistent state and you don't need to worry about recovering archive logs.

http://docs.oracle.com/cd/E17781_01/server.112/e18804/storage.htm#CIHEHCGI


MySQL keeps shutting down: /usr/sbin/mysqld: unknown variable 'set-variable=key_buffer_size=32M' My MySQL keep shutdown automatically with this error report:

200405 23:31:39 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
200405 23:31:39 [Note] Plugin 'FEDERATED' is disabled.
200405 23:31:39 InnoDB: The InnoDB memory heap is disabled
200405 23:31:39 InnoDB: Mutexes and rw_locks use GCC atomic builtins
200405 23:31:39 InnoDB: Compressed tables use zlib 1.2.8
200405 23:31:39 InnoDB: Using Linux native AIO
200405 23:31:39 InnoDB: Initializing buffer pool, size = 128.0M
200405 23:31:39 InnoDB: Completed initialization of buffer pool
200405 23:31:39 InnoDB: highest supported file format is Barracuda.
200405 23:31:39  InnoDB: Waiting for the background threads to start
200405 23:31:40 InnoDB: 5.5.62 started; log sequence number 14465811850
200405 23:31:40 [ERROR] /usr/sbin/mysqld: unknown variable 'set-variable=key_buffer_size=32M'
200405 23:31:40 [ERROR] Aborting

200405 23:31:40  InnoDB: Starting shutdown...
200405 23:31:41  InnoDB: Shutdown completed; log sequence number 14465811850
200405 23:31:41 [Note] /usr/sbin/mysqld: Shutdown complete

When I try to log into MySQL it says:

  DBI connect failed : Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I try to open /var/run/mysqld/mysqld.sock and can not found mysqld.sock

This happened after I tried to set key_buffer_size=32M by using webmin.

How to make it stop trying to set the key_buffer_size?

in the Module Configuration for mysql you find the path to my.cnf

Edit this  file and remove the line with key_buffer_size=32M and save it.

And then start the server.

Webmin information on mysql


How Can I get multi-Dimensional array by value In PostgreSQL 9.5 I use jsonb column called user_tag. This column has multi dimensional array like this:

[{"id": 1, "tag": "@xang"}, {"id": 2, "tag": "@wang"}]

I want to get when id is equal to 1. I can do this by following query:

SELECT * FROM mydb AS f WHERE f.user_tag #>> '{0,id}' = '1'

But, I have to provide 0 as a key. I want to search all id columns and return matched table rows. If I already know 0, why would i search in first place.

Any idea will be appreciated.

Note: SELECT * FROM mydb AS f WHERE f.user_tag ->> 'id' = '1' is not worked.

I found it myself. I wil llet you guys know so may other people get use it.

SELECT * FROM mydb AS f WHERE f.user_tag @> '[{"id":1}]'

That' s it. I can now get rows contains id=1