Error while executing query using send attachment when i am executing the below query, I am getting error as:-

Msg 22050, Level 16, State 1, Line 4
Failed to initialize sqlcmd library with error number -2147467259.

This is my query:-

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail Alerts',
@recipients = 'v-pusah@microsoft.com',
 @subject = 'Overlapping Subscriptions',
@body = 'kaise ho',
 @query = 'select b_id from tp_sub',

@attach_query_result_as_file = 1,
@query_attachment_filename = 'result.txt',
@query_result_separator=',',@query_result_width =5000000,
@query_result_no_padding=1

the solution was to specify the database prefix in the sql query in the @query parameter. After lot of scratch on my head i realized without prefixing DB name+Schema in Exec @query it will not work.

Did not work: @query = 'SELECT fields FROM table'
Works: @query = 'SELECT fields FROM database..table'


Insert into table if column  "Text" for each project I have created two triggers to insert into fact table if a new row is inserted into dim table. One checks if Project exists already and if not, then query will be triggered and adds 12 new rows per project. I have tested this and works fine. Now I need one more trigger make an insert check check if project exists, then don't trigger, but in addition this insert query need to check if Periodcheck is equal to "Later" for each project, if a row like that doesn't exists, then it should add a new row in my fact table. Tried a few different things, but can't seem to put it all together without creating duplicate rows for project and Periodcheck.
dimtable table looks like this (lets pretend project 2 is just added):

ProjectID
Status

1
A

2
A

Factable like this where project 1 is correct, but project 2 is missing the last row wiht Periodcheck = 'Later':

ProjectID
Status
Period
Periodcheck

1
A
202106
NULL

1
A
202107
NULL

1
A
202108
NULL

1
A
202109
NULL

1
A
202110
NULL

1
A
202111
NULL

1
A
202112
NULL

1
A
202201
NULL

1
A
202202
NULL

1
A
202203
NULL

1
A
202204
NULL

1
A
202205
NULL

1
A
205001
Later

2
A
202106
NULL

2
A
202107
NULL

2
A
202108
NULL

2
A
202109
NULL

2
A
202110
NULL

2
A
202111
NULL

2
A
202112
NULL

2
A
202201
NULL

2
A
202202
NULL

2
A
202203
NULL

2
A
202204
NULL

2
A
202205
NULL

This query below is supposed to only add one row with Periodcheck = 'Later' for projet 2, since that row does not exist in my facttable.
CREATE TRIGGER project_insert
ON dimtable
AFTER INSERT
AS
BEGIN
Insert into facttable (ProjectID, Status, Period, Periodcheck)
SELECT
   ProjectID
 , Status
 , Period = '205001'
 , Periodcheck = 'Later'
FROM dimtable d
  WHERE EXISTS(Select ProjectID FROM facttable f
        WHERE d.ProjectID = f.ProjectID)
  and
   NOT EXISTS(SELECT Periodcheck FROM
        facttable f
      WHERE t2.Periodcheck = 'Later')
END

End goal is to add this row to only projects that does not already have this row in my facttable. Any suggestions on how to change the query to do this?
It runs, but I have tested by adding a project to dimtable and this trigger does not add my desired row.
Thank you so much!

You can do everything needed in a single trigger.
For example, given the tables:
CREATE TABLE dbo.Projects
(
    ProjectID integer NOT NULL,
    [Status] character(1) NOT NULL,

        CONSTRAINT [PK dbo.Projects ProjectID]
            PRIMARY KEY CLUSTERED (ProjectID),

        CONSTRAINT [UQ dbo.Projects ProjectID, Status]
            UNIQUE NONCLUSTERED (ProjectID, [Status]),

        CONSTRAINT [CK dbo.Projects Status]
            CHECK ([Status] IN ('A'))
);

CREATE TABLE dbo.ProjectPeriods
(
    ProjectID integer NOT NULL,
    [Status] character(1) NOT NULL,
    [Period] character(6) NOT NULL,
    PeriodCheck varchar(5) NULL,

        CONSTRAINT [PK dbo.ProjectPeriods ProjectID, Period]
            PRIMARY KEY CLUSTERED (ProjectID, [Period]),

        CONSTRAINT [FK dbo.Projects ProjectID, Status]
            FOREIGN KEY (ProjectID, [Status])
            REFERENCES dbo.Projects (ProjectID, [Status]),

        CONSTRAINT [CK dbo.Projects Period]
            CHECK ([Period] NOT LIKE '^[0-9]'),

        CONSTRAINT [CK dbo.Projects PeriodCheck]
            CHECK (PeriodCheck = 'Later')
);

The following trigger creates the twelve periods and the 'Later' entry if they are missing. It also removes any entries that shouldn't be there.
CREATE TRIGGER [dbo.Projects AI]
ON dbo.Projects
AFTER INSERT
AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;

    MERGE dbo.ProjectPeriods AS PP
    USING
    (
        SELECT
            I.ProjectID,
            I.[Status],
            [Period] = ISNULL(CONVERT(character(6), DATEADD(MONTH, Months.n, CURRENT_TIMESTAMP), 112), '205001'),
            PeriodCheck = CASE WHEN Months.n IS NULL THEN 'Later' ELSE NULL END
        FROM Inserted AS I
        CROSS JOIN
        (
            VALUES
                (01), (02), (03), (04), (05), (06),
                (07), (08), (09), (10), (11), (12),
                (NULL)
        ) AS Months (n)
    ) AS PD
        ON PD.ProjectID = PP.ProjectID
        AND PD.[Period] = PP.[Period]
    WHEN NOT MATCHED BY TARGET
        THEN INSERT (ProjectID, [Status], [Period], PeriodCheck)
        VALUES (PD.ProjectID, PD.[Status], PD.[Period], PD.PeriodCheck)
    WHEN NOT MATCHED BY SOURCE
        AND PP.ProjectID IN (SELECT I.ProjectID FROM Inserted AS I)
        THEN DELETE;
END;

Executing:
INSERT dbo.Projects (ProjectID, [Status]) VALUES (1, 'A');
INSERT dbo.Projects (ProjectID, [Status]) VALUES (2, 'A');

or the equivalent:
INSERT dbo.Projects
    (ProjectID, [Status])
VALUES
    (1, 'A'),
    (2, 'A');

Produces the required outcome:

ProjectID
Status

1
A

2
A

ProjectID
Status
Period
PeriodCheck

1
A
202106
NULL

1
A
202107
NULL

1
A
202108
NULL

1
A
202109
NULL

1
A
202110
NULL

1
A
202111
NULL

1
A
202112
NULL

1
A
202201
NULL

1
A
202202
NULL

1
A
202203
NULL

1
A
202204
NULL

1
A
202205
NULL

1
A
205001
Later

2
A
202106
NULL

2
A
202107
NULL

2
A
202108
NULL

2
A
202109
NULL

2
A
202110
NULL

2
A
202111
NULL

2
A
202112
NULL

2
A
202201
NULL

2
A
202202
NULL

2
A
202203
NULL

2
A
202204
NULL

2
A
202205
NULL

2
A
205001
Later

Online demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8f34f1d9eb00aa1b8603498860a9826d


How to open SQL Server 2008 R2 Configuration manager in Windows 8.1 In Windows 8.1, i can't find Configuration manager of SQL Server 2008R2. I have already followed the steps as specified in this question. But Configuration manager is not showing up even after entering 'SQLServerManager10.msc' in Search. Please let me know how to achieve it.

Open run command window using "Windows+R" type mmc.exe after console opened from file menu choose SQLSERVERMANAGER10.

Or you can type "SQLSERVERMANAGER10.msc" in run command window.


MySQL - InnoDB - Partitioning a table with uniqueness Following a previous post I posted: date-based-partitioning

This is my solution for partitioning a table with primary key.

I wanted to add partitioning by months and delete old record.

CREATE TABLE `filesoi` (
  `uniqueness` binary(32) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `sha256` binary(32) NOT NULL,
  `datein` timestamp NOT NULL DEFAULT current_timestamp(),
  `lastSeen` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`uniqueness`),
  KEY `lastSeen` (`lastSeen`)
) ENGINE=InnoDB AUTO_INCREMENT=4102 DEFAULT CHARSET=utf8

Is this a good solution to create the table like so?:

CREATE TABLE `filesoi` (
  `uniqueness` binary(32) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `sha256` binary(32) NOT NULL,
  `datein` timestamp NOT NULL DEFAULT current_timestamp(),
  `lastSeen` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `__month` tinyint(4) NOT NULL DEFAULT month(curdate()),
  PRIMARY KEY (`uniqueness`,`__month`),
  KEY `lastSeen` (`lastSeen`)
) ENGINE=InnoDB AUTO_INCREMENT=4102 DEFAULT CHARSET=utf8
 PARTITION BY RANGE  COLUMNS(`__month`)
(PARTITION `p0` VALUES LESS THAN (3) ENGINE = InnoDB,
 PARTITION `p1` VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION `p2` VALUES LESS THAN (7) ENGINE = InnoDB,
 PARTITION `p3` VALUES LESS THAN (9) ENGINE = InnoDB,
 PARTITION `p4` VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION `p5` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)

This way I can still insert with Insert Ignore.. and the duplicates will be per partition.

In my answer to the other Question, I was assuming you wanted to add PARTITION BY RANGE (TO_DAYS(updated_at)) and then DROP PARTITION to rapidly delete "old" records.

The Question here talks about using PARTITION BY COLUMNS(_month).  There is essentially no difference.  However, what do you expect to happen after you finish with month #12?  Move on to 13?

I don't see a way to get a one-step uniqueness check and have partitioning.  I do provide some non-partition DELETE optimizations here .  That will let you use a UNIQUE key and have some performance in the deletion process.

PRIMARY KEY (uniqueness, __month) provides a uniqueness constraint on the pair, but not uniqueness by itself.  One failing of PARTITIONing is the inability to declare something unique across all partitions (unless the partition key is part of it).

If you ever need to search across a range of months, you can't do it across December to January.


Sum values from child tables and ignore equal rows I have two child tables with a value that must be summed separately and at the end the totals must be subtracted. The problem is that Child Table A and Child Table B can have 0 or more rows associated to the same Parent Table row, then some rows are calculated twice.

Here is the SQL to create the sample DB:

CREATE TABLE Project (
  id int,
  name varchar(50),
  CONSTRAINT PK_Project PRIMARY KEY (id)
  );

  CREATE TABLE ProjectBenefitCost (
    id int,
    project_id int,
    CONSTRAINT PK_ProjectBenefitCost PRIMARY KEY(id),
    CONSTRAINT FK_BenefitCost_Project FOREIGN KEY(project_id) REFERENCES Project(id)
    );

  CREATE TABLE Cost (
    id int identity(1,1),
    project_cost numeric(9,2),
    benefitcost_id int,
    CONSTRAINT PK_Cost PRIMARY KEY(id),
    CONSTRAINT FK_Cost_BenefitCost FOREIGN KEY(benefitcost_id) REFERENCES ProjectBenefitCost(id)
    );

    CREATE TABLE Benefit (
    id int identity(1,1),
    project_benefit numeric(9,2),
    benefitcost_id int,
    CONSTRAINT PK_Benefit PRIMARY KEY(id),
    CONSTRAINT FK_Benefit_BenefitCost FOREIGN KEY(benefitcost_id) REFERENCES ProjectBenefitCost(id)
    );

    INSERT INTO Project (id, name) values (1, 'Project A');
    INSERT INTO Project (id, name) values (2, 'Project B');

    INSERT INTO ProjectBenefitCost (id, project_id) VALUES (1,1);
    INSERT INTO ProjectBenefitCost (id, project_id) VALUES (2,2);

    INSERT INTO Cost (project_cost, benefitcost_id) VALUES (5006.20, 1);
    INSERT INTO Cost (project_cost, benefitcost_id) VALUES (10000.10, 2);
    INSERT INTO Cost (project_cost, benefitcost_id) VALUES (2000.10, 2);

    INSERT INTO Benefit (project_benefit, benefitcost_id) VALUES (3000.00, 1);
    INSERT INTO Benefit (project_benefit, benefitcost_id) VALUES (50000.00, 2);

If I use this SQL:

SELECT P.id, P.name, SUM(C.project_cost) AS TOTAL_COST, SUM(B.project_benefit) AS TOTAL_BENEFIT
FROM
  Project AS P INNER JOIN ProjectBenefitCost AS BC ON
    P.id = BC.project_id
    LEFT JOIN Cost AS C ON
      BC.id = C.benefitcost_id
    LEFT JOIN Benefit AS B ON
      BC.id = B.benefitcost_id
GROUP BY P.id, P.name

It will return 100000.00 as the TOTAL_BENEFIT for the Project B, because this Project has two related rows in the Cost table.

This is just the first step that needs to be solved before I can subtract TOTAL_COST from TOTAL_BENEFIT.

How can I fix this SQL statement?

Use:

SELECT *
 FROM Project AS P
OUTER APPLY
(SELECT SUM(C.project_cost) AS TOTAL_COST
    FROM Cost AS C
    JOIN ProjectBenefitCost AS BC
    ON P.id = BC.project_id
    AND BC.id = C.benefitcost_id) C
 OUTER APPLY
 (SELECT SUM(B.project_benefit) AS TOTAL_BENEFIT
    FROM Benefit AS B
    JOIN ProjectBenefitCost AS BC
    ON P.id = BC.project_id
    AND BC.id = B.benefitcost_id) B
;

You can't have your join make multiple copies of your project row without also making multiple copies of your benefit row. So when you join through to cost and benefit, you need to aggregate the rows there down to a single row per project before the join kicks in.


Sync MySql Replication from Master2 to Master1 after failover Due to Disk space issue Master2 server crashed and it was rebuilt again using mysqldump of Master1.
Both Master 1 and Master2 are in sync and Master2 is in Read_only mode.
During the crash, I stopped the Slave instance in Master1 and it's been over a week now. Now how to restart replication from Master2 to Master1.
Can changing the Master Log position in Master1 to current Master2 status works?

As Master2 is in read-only;
Master2: SHOW MASTER STATUS
Master1:
CHANGE MASTER TO
MASTER_HOST = 'host_name'
MASTER_USER = 'user_name'
MASTER_PASSWORD = 'password'
MASTER_LOG_FILE = 'master_log_name'
MASTER_LOG_POS = master_log_pos;

START SLAVE;

Where the master log file name/pos are from the Master2 SHOW MASTER STATUS
The rapidly changing master status isn't an issue as its binary logs are from Master1 anyway. Master1 won't write its own binary log message back again when received via replication.


MySQL insists on trying to use InnoDB even when told not to, please help We're trying out MySQL 5.5, and have installed it on one of our slave servers as a test run. Our main server is 5.0, and we have zero optimization here for InnoDB, so I want everything to just be MyISAM for now. I've disabled InnoDB everywhere I can think, but the replication is throwing errors like:

Error 'Unknown storage engine 'InnoDB'' on query. Default database: ''. Query: 'CREATE TEMPORARY TABLE ... SELECT ...

The query never specifies the engine is InnoDB. default-storage-engine is set toMyISAM. innodb is set to OFF. Why is it trying to create this termporary table as InnoDB? This is 5.5, not 5.6, so we don't have the default-tmp-storage-engine variable yet. Please help, this is driving me insane.

To disable InnoDB, add this to /etc/my.cnf on the MySQL 5.5. slave:

[mysqld]
skip-innodb

and run this on the Slave:

service mysql restart

According to MySQL Documentation on CREATE TABLE :

  You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:

  CREATE TABLE new_tbl SELECT * FROM orig_tbl;
  For more information, see Section 13.1.17.1, “CREATE TABLE ... SELECT Syntax”.

  Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:

  CREATE TABLE new_tbl LIKE orig_tbl;

  The copy is created using the same version of the table storage format as the original table. The SELECT privilege is required on the original table.

You must specify the ENGINE upon creation

CREATE TEMPORARY TABLE ... ENGINE=MyISAM SELECT ...

to get around this problem


How to multi-match two tables efficiently? I'm relatively new to database-design and want to create a SQLite database for an application. The application needs to handle lots of objects and assign them to a smaller list of categories. Both tables have multiple columns. The problem is, that every object may belong to an indefinite amount of categories and I need to query both efficiently: All objects that belong to a specific category and for any object to which categories it belongs, which is why I can't just use joins.

My idea was to create a third table with just two columns, matching objects to categories. But it looks like an ugly solution to me and I also don't know what indices I should set upon it, if any at all. I might end up with the key column's data being saved four times: In the original table, in its PRIMARY KEY, in the matching table, and in the index on that table.

Is there a better approach or maybe even a standard method to handle such situations?

3-table scheme is the most compatible. 3rd table will look like:

CREATE TABLE jointable
(
    obj_id,
    cat_id,

    PK (obj_id, cat_id),
    FK (obj_id) REF obj (id),
    FK (cat_id) REFcat (id)
);


SELinux is preventing pg_ctl from read access on the file postgresql.conf I am trying to setup a master-slave PostgreSQL 9.4 database on Fedora 22, with the slave database's PGDATA being different from the default /var/lib/pgsql/data/ directory. I wrote this to /etc/systemd/system/postgresql.service

.include /lib/systemd/system/postgresql.service
[Service]
Environment=PGDATA=/mnt/db/data

and ran

semanage fcontext -a -t postgresql_db_t "/mnt/db/pg_data(/.*)?"

However, the database won't start and running journalctl -xe gives the title error. How would I go about fixing it.

It looks like you changed the context of the /mnt/db/pg_data directory, but didn't apply the changes to everything underneath.

You can verify this by running ls -Z on the files in question. If you don't see 'postgresql_db_t', then the changes weren't applied.

restorecon -Rv /mnt/db/pg_data/ should set the SELinux contexts correctly. Forcing a relabel of your entire filesystem with touch ./autorelabel and a reboot would also work.

I also noticed that in your service file, you set the PGDATA variable to:

PGDATA=/mnt/db/data

instead of

PGDATA=/mnt/db/pg_data

As you did in your restorecon command, and that could cause problems as well because /mnt/db/data probably doesn't exist on your server.

Lastly, if pg_ctl doesn't have the correct SELinux context, it won't be able to read the data directory either.

So, verifying the context with ls -Z on pg_ctl and changing it if needed is also a good idea.

You can find nice documentation on these SELinux commands here:

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Security-Enhanced_Linux/sect-Security-Enhanced_Linux-Working_with_SELinux-SELinux_Contexts_Labeling_Files.html

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Security-Enhanced_Linux/sect-Security-Enhanced_Linux-SELinux_Contexts_Labeling_Files-Persistent_Changes_semanage_fcontext.html

Hope that helps. Good luck. =)


Is it possible for it to be inpenetrable? I was wondering if it is possible to create some sort of database that is physically impossible to enter. While creating it you store some information in it and program it to spit that information after a period of time. But not even you can access it even if you wanted to, you have no choice but to wait.

Thanks.

Yes, build it on a physical server.  Unplug the server and put the server in a time lock physical safe.  When the safe opens plug the server back in.

I can't imagine how it would be possible as a coded solution.

You might be able to do something with an encrypted database, if you had some way to tie the encryption to a specific date.  Doing that would be a question for INFORMATION SECURITY

Related time-based encryption algorithm?


limit and then group by in MySQL I have a mysql table having following structure :

payment_id(int auto_increment),
emp_id(int),
chargeTime(datetime),
payment(double)

I need to have three latest (with respect to chargeTime) records of all emp_id. What should be the query?

Can anyone please help me? Without using cursor would be helpful.

Try:

SELECT *
FROM yourtable
ORDER BY chargeTime DESC
LIMIT 3


Which graph database for search engine and gis? I just learned there are "graph databases" out there, which are more suited for two applications "search engine" and especially "gis".

To avoid discussions whether a relational DB is better suited and to make clear what kind of Database is needed, here some more details what I want to use it for:

Application (Search Engine): Handling search engine data in a relational DB is slow because you'll have big tables with enormous indeces. Imagine just a table like 'links' which is just from_pageid INT, to_pageid INT with PRIMARY KEY(from_pageid, to_pageid), INDEX (from_pageid) and INDEX (to_pageid); which would have some 1'000'000 Entries. When using a graph database there are just all "from" and all "to" links for each nodes, with or without index, which are right there without looking up a huge index.
Application (GIS): If you have a map, which consists of nodes (with geographical positions) and links between them (=ways, roads, autoroutes...) you'll have a big "links" table to ask for which links (road, ways,...) can be used at node x. Same problem as for the search engine application.

Ok, now some more specialties:

It should be a free software (at no cost). Reason: I want to use it for research/educational purposes (and I currently have to finance this my own). AND: I possibly want to let it become a commercial product. Therefore a "free for non-commercial use"-solution should be at least effordable, if switching to commercial application...
I would like to use PHP, C/C++ for accessing the DB (preferably both should be possible). If also Java/Android is supported, it would even better.
Operating System: Linux!; I would like to avoid a Java based product, as the javaVMs tends to eat up all RAM (only have very limited server resources) and don't free any - even if they could.

What DBs would you suggest to me?

Well you could use IBM DB2 LUW (Linux,Unix, Windows) Express-C edition. It is free and has community support. It is the same engine/binaries as DB2 Enterprise Edition, it just has certain features "turned off" and has memory and CPU caps, but for what you are describing, it may suit your needs. If you do find you need more memory/CPU you could always purchase a license from IBM to upgrade it to Express or one of the higher editions.

There is an add on called DB2 Spatial Extender, which has functionality for geo-spatial analysis. According to this chart here, it should be free to download and use, even with Express-C edition. You can download DB2 Spatial Extender here. And here is the Information Center documentation on DB2 Spatial Extender. Use the table of contents to browse.


MySQL: Alter Table on very active table gives "Waiting for table metadata lock" At my work, we've recently upgraded to MySQL 5.5 and since then, have been bitten a few times by the new (and intended) behavior of metadata locking.  Basically, whenever we run an ALTER TABLE statement on a fairly active table, the table becomes inaccessible to both reads and writes and threads are shown hanging, basically forever, with the "Waiting for table metadata lock" message.

I've done a bit of research and found good explanations of this in the MySQL manual and here and see that this is an expected and actually intended behavior.  What I haven't found anywhere online is a graceful way to mitigate the obvious problems this causes in production.  The only way we have been able to run alter table statements on our more active tables since upgrading is by disabling read and write permissions for any usernames our application runs under, killing all processes that are still running, and then running the ALTER TABLE statements.  This works, but results in many errors from our app: both from background processes and our web site.

In the past (ie before 5.5) schema changes were no problem.  I understand the reason for the new behavior, but it causes obvious problems in production and I figure folks in the community must have work arounds.

Any ideas would be appreciated.  Thanks!

You can achive nonbloking AlTER TABLE using tools like http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html and http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html

They are both tested in production and reliable. Percona's tool is very smart about checking slave lag.
Also checkout other percona tools, they are very handy.


I have a year's worth of inventory data, and I want to extract the first and last day of each month's data. What is the best way to do this? Thanks I have a year's worth of inventory data, and I want to extract the first and last day of each month's data. What is the best way to do this?

That is a pretty wide-open description and could use more details if there are complications in the data you are using. But here is a basic idea.

First day of month is easy:  YYYYMM01

Last day of month can easily be calculated by:

   DECLARE @FirstDayOfMonth DATETIME; -- Use DATETIME for older SQL Server versions
   SET @FirstDayOfMonth = '20150601' -- Safest format for dates.
   SELECT DATEADD(DAY,-1,(DATEADD(MONTH,1,@FirstDayOfMonth)));

This trivializes finding the last day of the month.  It is one day before the first day of the next month. No need to calculate leap years, lengths of  months, et cetera.


MySQL Replication: Slave with Different Column From Master I have a Master/Slave replication.

I had to change the name of one of my columns in one of my tables from temp_col to sent_at, this column is not being used yet.

I've issued an alter table on an unused slave in order to change the column name with hope that once the change will finish. I would be able to use this slave (with the desired column name) as a new master.

The thing that I didn't take into consideration is that once the alter table is finished that replication will fail since the query that inserts data into the altered table expects the old column name (temp_col) even if its just inserting null values into it.

Since the alter table took some time, there are a lot of these kind of queries.

Questions

Is there something i can do to repair the replication?
How can I tell the slave to "ignore" the old column name?

thanks

Ran

WOW, I remember your last question !!!

If you kept the s_relations_old on the slave, just switch back and start replication.

STOP SLAVE;
ALTER TABLE s_relations RENAME s_relations_bad;
ALTER TABLE s_relations_old RENAME s_relations;
START SLAVE;

You should perform the conversion of the temp_col on the master so that you let that ALTER TABLE replicate to the slave.

You will have to bite the bullet on this one and have some downtime.


Need help in leveraging MySQL auditing We are setting up password rotation policy to rotate account/password quarterly. We are also planning to use MySQL/Maria/Percona Auditing. Need to figure out the most efficient and feasible way to achieve both with minimum intervention while at password rotation.

  Explanation: I don't want to edit/add/change audit filter rule every
  quarter once we rotate user accounts.

Possible tried solutions:

Tried auditing ROLE with permissions which is attached to application
accounts (subject to rotation) with NO LUCK as auditing filter based
on login accounts.
Configuring exclude_account to (MySQL_schema@localhost) hence logging
for all other FUTURE user accounts by default DOESN'T work as some
process and port monitoring (which runs without mysql user account)
continuously fills up audit logs which is again Problem.
Workable solution is to pre-configure some x accounts with passwords
and enable ALL of them in auditing from Day1. Activate/Deactivate one
account every quarter one-by-one. Reusing same accounts with
different passwords once all accounts have been used.

Looking for More efficient solution or if someone has managed to work out similar issue using audit filtering rules?

Assuming you can find a way for your monitoring software to use an actual account, and further assuming your list of trusted/non-audited users is more static, then using the "exclude user" options should mean less maintenance.

To exclude a list of trusted users from being logged, the MariaDB audit plugin has a server_audit_excl_users option, and similarly the Percona audit log plugin has a audit_log_exclude_accounts.

BTW, regarding rotating users/passwords, you might be interested to know that MariaDB 10.4+ has an expiring password feature.


How can I filter databases by owner using psql? I know I can list my postgres databases with the following command:

# list only the databases
psql -U openerp -q -t -c "select datname from pg_database;" template1

Or

# show a full listing of databases
psql template1 -U openerp -l

How can I filter the databases by owner 'openerp'? datdba field is just a number... how can I map it?

For things like this, you'll want to query pg_database directly. The owner field is a reference to pg_catalog.pg_authid.oid, but this is a superuser-only accessible table. There's a view, pg_catalog.pg_roles that exposes the id as the oid attribute, so you can:

select
  datname,
  rolname AS dbowner
from pg_database d
     inner join pg_roles r on (d.datdba = r.oid);


FATAL: catalog is missing 1 attribute(s) for relid 2662 I have a server with PostgreSQL9.1-903 (Windows 7 Professional). After a power cut my database is inaccessible (I presume that the database is corrupted).

The postgreSQL service starts but the following message is displayed when I try to connect via pg_Admin:

  FATAL: catalog is missing 1 attribute(s) for relid 2662

I have tried to disable the option: 'ignore_system_index' but the database is still inaccessible. I guess that is not a problem with the indexes, but with the fact that some rows of the table pg_attribute have disappeared.

I am interested in recovering the data because my last backup is a bit old. Is there any way to just connect to the database? After that I would use the tool pg_dump to retrieve the data and mount it in a new database.

EDIT

The parameter fsync is commented in the postgresql.conf (I assume that the default value is on). Following, I attach the part of the postgresql.conf which has been tuned:

checkpoint_segments = 32    # in logfile segments, min 1, 16MB each

shared_buffers = 1GB        # min 128kB

work_mem = 8MB

maintenance_work_mem = 256MB

effective_cache_size=1024MB

default_statistics_target = 200

autovacuum_vacuum_cost_delay = 50ms

Upon database corruption, before doing anything else, one should read https://wiki.postgresql.org/wiki/Corruption and take a file system level backup.

You need to restore from backup or, if backup is missing, check corrupted tables for example

select distinct relname
from pg_class
where not exists (select 1 from pg_attribute where attrelid = relfilenode)`

or

select distinct relname
from pg_class
where relfilenode > (select max(attrelid) from pg_attribute)

And then your need to restore missing record in pg_attribute (for example create simm table and

copy with q as
( select attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod, attbyval, attstorage,attalign, attnotnull, atthasdef, attisdropped, attislocal, attinhcount, attcollation, attacl, attoptions, attfdwoptions
  from pg_attribute
  where attrelid::regclass = your_table::regclass) )
 INSERT INTO pg_catalog.pg_attribute select [id from your pgclass missing instead new attrelid], * from q

Then check, that atttypid exists and do vacuum on pg_class and pg_attribute. So, your problem must be solved.


How to re-simulate a long query stuck in mysql queue? BACKGROUND

Occasionally, in my live web app, I'll get a query that locks up the database for 1-2 minutes. Sometimes it is hard to predict which queries will do this until the load gets to a certain point, or a particular user with a particular set of data runs through that query.

The current solution I have for handling this (and being aware of it), is a crude crontab script which checks "Threads_running" in "show global status" of the mysql server. If the number is over 30, then I log out all the queries with "show full processlist"

Im finding that i'll get a long query stuck query about ~ once a week.

THE QUESTION

Once I find this query, sometimes EXPLAIN will help me figure out why its slow. But sometimes, I just want to re-simulate it happening. When I run the query again, it is fast, (because of query cache?). Then I run RESET QUERY CACHE and FLUSH QUERY CACHE, and expect to be able to simulate that slow query, but it is fast still. Why?

ALSO, side note: I'm curious how other people monitor long queries?

Can you please share more on what queries are you running? I think, you can make use of InnoDB engine which will do row level locking instead of table level lock. Also you can make you of slow query log which will log all the queries taking more than n seconds (as specified in config)


Restoring a managed SQL instance in Azure using PowerShell I am trying to figure out how to restore a database from one managed SQL instance to another. I'm following the tutorials, but I keep running into inscrutable error messages.

Here's my command:

Restore-AzSqlInstanceDatabase `
    -Name "SomeDatabase" `
    -InstanceName "our-oltp-dev" `
    -ResourceGroupName "dev-managedsqlinstances" `
    -PointInTime "4/7/2020 12:00:00" `
    -TargetInstanceDatabaseName "SomeDatabase_FROM_DEV" `
    -TargetInstanceName "our-oltp-sandbox" `
    -TargetResourceGroupName "sandbox-managedsqlinstances"

Here's the output:

PS C:\WINDOWS\system32> Restore-AzSqlInstanceDatabase `
    -Name "SomeDatabase" `
    -InstanceName "our-oltp-dev" `
    -ResourceGroupName "dev-managedsqlinstances" `
    -PointInTime "4/7/2020 12:00:00" `
    -TargetInstanceDatabaseName "SomeDatabase_FROM_DEV" `
    -TargetInstanceName "our-oltp-sandbox" `
    -TargetResourceGroupName "sandbox-managedsqlinstances"
Restore-AzSqlInstanceDatabase : Parameter set cannot be resolved using the specified named parameters.
At line:1 char:1
+ Restore-AzSqlInstanceDatabase `
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Restore-AzSqlInstanceDatabase], ParameterBindingException
    + FullyQualifiedErrorId : AmbiguousParameterSet,Microsoft.Azure.Commands.Sql.ManagedDatabase.Cmdlet.RestoreAzureRmSqlManagedDatabase

It's a copy-and-paste from the Azure docs; so I'm not sure what I'm doing wrong. Any help would be appreciated.

This command is somehow not clear for PowerShell and there is problem to determine which version of the method is accurate.

However it should work if you specify -FromPointInTimeBackup switch, I have checked  it and got credentials error which is fine as I am not logged in to proper account.

PS C:\WINDOWS\system32> Restore-AzSqlInstanceDatabase `
>>     -Name "SomeDatabase" `
>>     -InstanceName "our-oltp-dev" `
>>     -ResourceGroupName "dev-managedsqlinstances" `
>>     -PointInTime "4/7/2020 12:00:00" `
>>     -TargetInstanceDatabaseName "SomeDatabase_FROM_DEV" `
>>     -TargetInstanceName "our-oltp-sandbox" `
>>     -TargetResourceGroupName "sandbox-managedsqlinstances" `
>>     -FromPointInTimeBackup
Restore-AzSqlInstanceDatabase : Your Azure credentials have not been set up or have expired, please run
Connect-AzAccount to set up your Azure credentials.
At line:1 char:1
+ Restore-AzSqlInstanceDatabase `
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : CloseError: (:) [Restore-AzSqlInstanceDatabase], ArgumentException
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.ManagedDatabase.Cmdlet.RestoreAzureRmSqlManagedDatabase

Additionaly, be sure that you use the latest version of Az.Sql module.

Update-Module -Name Az.Sql -Force


Do SQL Sharding or Cluster help speed up slow queries, or just concurency? I am having a performance problem with my current single instance database as the volumes of data grow. The application I am working on receives inserts once a month, and the oldest month is deleted at the same time. The number of rows grows in the hundred millions for the most accessed table (one row per item per month per store) and is joined on a table storing the data specific to items regardless of time (like its label).

A normal query acts as follow: Do a aggregation either per month, store or article over a different amount of store/months and categories.

I was looking at some sharding possibilities that would allow me to keep the queries as they are (I can't afford to rewrite everything for now) and run the queries exactly as if I had only one server but in the end that server would run the query on multiple shards/replications to speed things up.

Can I expect any good speed increase on currenly slow queries? So far my understanding of cluster/sharding is mostly to help reduce loads from many read/writes but this is not a problem at all for me at this time since there is not a lot of queries going on (rarely multiple queries at the same time on the server instance).

Thank you

EDIT: I'd like to point out that today, I am coping with ok speeds by having the full table in memory so I've removed the disk read times, so simply storing the data on different disks wouldn't solve all...

Sharding requires a bunch of changes to the infrastructure, connections, etc.  The product "Spider" makes most of that transparent.
Sharding may improve scaling and concurrency for simple queries.  But for table scans and JOINs, it is likely to make performance worse.
Partitioning is excellent for "deleting old data".  See my blog.  Otherwise, the DELETE competes with other queries, making the whole system suffer when you do the purging.  (For 3 years, I would PARTITION BY RANGE(TO_DAYS(...)) and hav 38 partitions.)  DROP PARTITION for the old month is essentially instantaneous, regardless of size.
Loading data can be a bottleneck.  Do you use LOAD DATA?  That is probably the best.  Second best would be batched INSERTs of 100-1000 rows per batch.
If you just need read scaling, then adding Slaves gives you unlimited read scaling.
If you need write scaling, a Galera-based cluster might help some, but Sharding is the only real solution.
A few Summary tables, as you allude to, is an excellent way to get performance from a Data Warehouse setup.  I blog on that, too.
"I can't afford to rewrite everything for now" -- Any shortcut will take just as long.  And then you will still have to "rewrite everything" to take the next step.
Manually splitting data onto different devices is not as good as RAID striping of all the drives into one large drive.
A RAID controller with Battery Backed Write Cache makes writes appear to be instantaneous, without losing data.
Normalization can be used to keep the tables smaller, hence cacheable.  Are you doing a reasonable amount of that, and using reasonably small ids (eg, SMALLINT UNSIGNED instead of BIGINT where appropriate)?
Slow queries -- There are too many variables to make a general statement.  Let's see some specifics.

I would be happy to discuss any of these further.  Since you have not said what you really need, I don't know what to focus on.


SQL server temp table using # - is it only accessible by the containing query for multiple connections with the same login? Say we have 2 completely different queries, that reference a temp table with the same name:

query one
....operates on:
    #tempTableName

query two
....operates on:
    #tempTableName

I've researched this and found out that "#temp is a local temporary table, and therefore not visible to other connections", however I'm not sure what a connection really means in-depth in the context of SQL queries. Does SQL consider there to be a separate 'connection' for every separate query that is run, or is a connection shared somehow?

Specifically, my question is "what's going on if the two queries in my scenario are being accessed by the same login" e.g. multiple .NET applications are using the same .NET connection string and concurrently accessing the database. Are they then going to potentially access the same temp table if both queries in my scenario are being run concurrently? What about locks? Do I need to do anything to prevent unwanted access during operations on the table?

I've looked at this answer, https://stackoverflow.com/questions/466947/are-temporary-tables-thread-safe, however I really need something like a 'pretend I'm 6 yrs old' explanation so that I'm crystal clear on what's going on in my hypothetical scenario in terms of exactly how the SQL queries are utilising connections and determining exclusive access etc. Any diagrams or pointers to resources that can give me a complete command of this would be great!

Local temporary objects are separated by Session. If you have two queries running concurrently, then they are clearly two completely separate sessions and you have nothing to worry about. The Login doesn't matter. And if you are using Connection Pooling that also won't matter. Local temporary objects (Tables most often, but also Stored Procedures) are safe from being seen by other sessions.
While your code has a single, common name for the local temporary objects, SQL Server appends a unique string to each object name per each session (and if needed, per each sub-process) to keep them separate. You can see this by running the following queries in SSMS:
CREATE TABLE #T (Col1 INT)

SELECT * FROM tempdb.sys.tables WHERE [name] LIKE N'#T%';

You will see something like the following for the name (I removed most of the underscores from the middle of the name to prevent the need to scroll here):
#T_______________00000000001F

Then, without closing that query tab, open up a new query tab and paste in those same 2 queries and execute them. You should now see something like the following:
#T_______________00000000001F
#T_______________000000000020

So, each time your code references #T, SQL Server will translate it to the proper name based on the session. It is all handled auto-magically :-).
To illustrate this point. I have worked on systems that are highly transactional (thousands of transactions per second) and were SaaS (Software as a Service) web apps running 24 / 7. All T-SQL code was in Stored Procedures (meaning: same local temp table name for every execution of that code) and we made pretty good use of local temporary tables. Being a web app, the Login was the same across nearly all connections and we definitely used connection pooling. We never had any problems indicating any cross-Session access of same-named local temporary objects. And quite frankly, we would have been shocked and making use of our support contract with Microsoft to get it fixed if that ever did happen.
Additional points to keep in mind regarding local temporary tables:

While their names are unique, their dependent objects are not. You cannot create Triggers or Foreign Keys on temporary tables, so this is really about Primary Keys, Check Constraints, and Default Constraints. This means that naming a Primary Key #PK_#T does not make it a unique name with a behind-the-scenes unique id appended to the name. If you attempt this, you will get "Cannot create object. Object already exists." errors (well, assuming multiple concurrent executions this same code). So, if you need any of these 3 object types for a local temporary table, create the dependent object(s) inline so it/they will get system-generated names which will be unique and not conflict between Sessions.
You don't need to worry about Index names since those are already separated per [object_id].

Given that you get "Cannot create object. Object already exists." errors when creating named dependent objects, that brings up a good point that is most often overlooked regarding this question of "are local temporary objects completely isolated per session": If local temporary tables could be seen by other Sessions, then the CREATE TABLE statement would get an error, right?

A nuance of local temporary tables (that is also a loose counter-point to #2 above) is that if you reference a local temporary table in a sub-process (i.e. EXEC) that was created prior to that sub-process starting, it will be able to see (and even modify) that local temporary table. HOWEVER, if that sub-process creates another temporary table having the same name, then there will be two objects, with separate unique IDs appended to the end, that are accessible via the short name. I recall reading somewhere that there is no guarantee that referencing #name in the sub-process will always resolve to the version of that object that was created in the sub-process. Hence, it is best to have some uniqueness of local temp table names between Stored Procedures that have any possibility of being executed in a chain of nested Stored Procedure calls.


Which areas of a DBA's knowledge should a developer delve into? I must admit the question is quite broad, so I'll try narrowing it down a bit. In our company we are 3-4 developers and have some SQL Server based installations running at our customer's sites (database sizes up to 100GB, up to 100 concurrent users, intranet applications). Nobody of us has real good experience in running/maintaining/administrating (whatever) databases. The customers not even that much. It's working ok till now, but I can't tell for sure if it's because we doing everything right or if we just didn't hit areas/situations we aren't proficient in.

So, I'm looking for the essential things you need to know when running a database from a DBA's point of view. You know the hard facts and know what matters most in your day for day job.

In which subjects should I gather deeper knowledge, what should I've heard of and what can I care of not until I face it for the first time?

I'm aware of the question Software Engineers and DBAs, but it's not quite what I was looking for. There are also lots of books around, but I'd like to hear it from those with practical experience.

The two things I deal with every day.

Disaster recovery.
Performance tuning. (Both for individual queries, and for the dbms itself.)

Your disaster recovery plan needs to be

scripted,
tested, and
practiced.

I'm using scripted in the sense of something an actor would follow, not something written in Python. It should tell everyone who needs to be involved exactly what to do. (And often, exactly what to say, too.)

Performance tuning for queries includes understanding keys, indexes, and normalization. (Often "tuning" problems are actually structural problems.)


Accessing mysql database without logging in (from android java application) (using php server side) Just like all e-commerce applications (like amazon) let you access the database and browse items without logging in or signing up, how can i do this with mysql database (without logging in with username and password)

Applications often allow anonymous user access but relational databases should be accessed using a minimally privileged account as a best practice.

With a public web application, end users do not access the database directly but through the remote web server, middleware, or other service. The data access layer should then connect to the database using a minimally privileged account.

In MySQL, execute CREATE USER and GRANT to setup the service account. Use those credentials to connect to the database in the data access layer.


Attach Database Trigger Is it possible to write a trigger when someone makes a change at the server level such as attaching or creating a database?  I have a dev server where anyone can add a copy of the product database for testing, but there is no cleanup procedure.  I want to add the following extended properties when a database is added.

EXEC sys.sp_addextendedproperty
@name = N'Owner',
@value = N'Username';

EXEC sys.sp_addextendedproperty
@name = N'StartDate',
@value = N'9/1/2011';

You can use DDL triggers for this. There doesn't appear to be a DDL event raised for attach but there definitely is for CREATE DATABASE. You could work around this by denying rights to sp_attachdb and force everyone to use CREATE DATABASE FOR ATTACH instead.

The following example feels like a bit of a hack, so hopefully someone with more DDL trigger experience will propose a neater way of doing this.

IF EXISTS (SELECT NULL FROM sys.server_triggers WHERE name = 'ddl_trig_database')
BEGIN
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
END
GO

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
    DECLARE
        @DatabaseName NVARCHAR(128)
        , @CreatedBy NVARCHAR(128)
        , @CreatedDate NVARCHAR(23)
        , @SQL NVARCHAR(4000);

    SELECT
        @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)')
        , @CreatedBy = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(128)')
        , @CreatedDate = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','NVARCHAR(23)');

    SET @SQL = '
        USE ' + @DatabaseName + ';
        EXEC sys.sp_addextendedproperty @name = N''Owner'', @value = N''' + @CreatedBy + ''';
        EXEC sys.sp_addextendedproperty @name = N''StartDate'', @value = N''' + @CreatedDate + ''';';

    -- PRINT @SQL;
    EXEC (@SQL);

GO

CREATE DATABASE MyTestDatabase;
GO

DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

DROP DATABASE MyTestDatabase;
GO


Change the server Collation I am discussing a controversial subject on the server Collation.

When I prepare the 70-462 exam I learned that if we want to change the default collation of the new coming databases we have to change the collation property in the 'Model' database.

However on the internet all articles said that The collation of system databases cannot be changed. And I validate this when I tried to execute the following query:

Alter DATABASE model COLLATE SQL_Latin1_General_CP1_CI_AS

I get an error telling that it is impossible to change the collation of the database Model because it is a system database.

Is it true? and if yes, is there any way to change the default collation of the server without doing a reinstallation and by t-sql code?

Thanks

Technically yes, you can change the Instance's default Collation without reinstalling by "rebuilding the system databases" via the SETUP program. This is documented in the following two places on MSDN:

Rebuild System Databases
Set or Change the Server Collation

However, as can be inferred from the instructions on those two pages, this is not exactly a simple / low-risk process. And, this only changes the 4 system databases, not any user databases.

There is also the undocumented -q option for sqlservr.exe as linked to in @irimias's answer (please see Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong? for a detailed description of the behavior of this option, as well as the potential scope of impact). This option changes all databases (system and user), but is undocumented, so not guaranteed to work 100% (but it might).

Now, while you did mention the default Collation for new databases, one should still keep in mind that it is easy enough to specify the desired Collation via the COLLATE clause as follows:

CREATE DATABASE [NewDatabaseName] COLLATE Hebrew_100_CI_AS;

Still, that is not 100% the same as the Instance being the same Collation as the Database since some columns of various DMVs, plus tempdbs and msdbs default Collation will still be the same as the Instance default, which increases the chances of getting Collation-mismatch errors. But, those can still be worked-around using the COLLATE clause on any predicates or JOIN conditions on strings fields that get those mismatch errors.


Exporting mysql query result to .txt file I want to export query result into .txt file

The path of .txt file and query is dynamic

 DELIMITER //
 create procedure test(filepath text , _query text)
 BEGIN
 SET @sql = CONCAT(_query, ' into OUTFILE ' ,filepath );
  prepare s from @sql;
   EXECUTE s;
  DEALLOCATE PREPARE s;
  END //
  DELIMITER ;

When call procedure test it shows me:

CALL  test1('C:/Users/Tajamul Hussain/Desktop/file database/test.txt ','select firstname from t1 where id =1');

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'C:/Users/Tajamul Hussain/Desktop/file database/test.txt' at line 1

You need to embrace the filepath in quotes. This works for me (tested on MariaDB 10.4, but should work on older versions as well):

DELIMITER //
CREATE PROCEDURE test(filepath text, _query text)
BEGIN
  DECLARE stmt TEXT;
  SET stmt := CONCAT(_query, ' into OUTFILE \'' , filepath, '\'');
  PREPARE s FROM stmt;
  EXECUTE s;
  DEALLOCATE PREPARE s;
END //
DELIMITER ;

By the way, it's not good practice to use user-defined variables inside stored programs, like @sql in your procedure, because they retain their values between calls. (I think it will be OK in your procedure, though, unless you use @sql elsewhere.) DECLARE your variables instead.


Granting a user a permission to restore a database In a database I created a user with the following roles:

Server Roles

public

Database roles

db_backupoperator
db_ddladmin
db_datareader
db_datawriter

The problem is I don't want to change the user's roles, but I do want grant the permission to restore the database.

Is there a workaround to only grant the permission to restore but not to drop nor alter the database?

Background info:

The company I work for created an app and they want the users of that app to have the permission to backup and restore the database, but not to create another database or drop the existing one. And, they don't want to have another user who has the role of db_owner, which would allow the customer to manipulate things as they please.

The price for the app is based on the numbers of employees a client has, so while installing the app on the client side they use a file to create a database where they specify all the details (including the number of employees), but they fear that someone might change the file and add the database multiple times, so they want to secure the instance by not granting any user the right to do anything. It's complicated but it is what they want.

Yes, this is actually fairly easy to accomplish using Module Signing. With module signing, you create a module (in this case, a stored procedure) and grant the required permission to the module, not to user or login. You only grant the user permission to execute the module. And, by doing this, not only can you be granular enough to effectively only grant the ability to restore, by hard-coding the RESTORE statement, you are essentially granting permission to only restore this particular database.

Detailed instructions, explanation, and walk-through demo can be found here:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level

But to simplify, here are the basic steps:

(we will assume that the stored procedure will be created in the [master] DB, which means we don't need to copy the certificate to another DB. the stored procedure could just as easily be created in a utility database, in which case you just need to copy the certificate to [master] as shown in the instructions in the linked blog post)

Create a stored procedure. This can be as simple as just the RESTORE statement for a specific DB, or it can accept an input paramater for @DBName sysname and construct Dynamic SQL using that parameter value, this allowing for restore of any particular DB, or maybe a @DbToRestore TINYINT parameter that is used in a CASE statement allowing the user to select from a limited set of pre-defined database names to restore.
Create a certificate specifying a password (do not rely on the Database Master Key / DMK). Do not give the end-user / customers this password (including a script containing the password).
Sign the stored procedure (i.e. the "module") using that certificate and its password.
Create a login (not user) from the certificate.
Add the login to the dbcreator fixed server role.
If the end-user / customer does not already have a user in [master] for their login: create a user in [master] for the customer's login.
Grant the customer's user EXECUTE permission on the stored procedure.

For added security: it might be a good idea to prevent them from signing another module, or re-signing this one, if they somehow manage to get the password. This is not always necessary, but is probably a good idea here since you distribute the system to the customer.

Backup the certificate to a file
Remove the private key from the certificate

Without the private key, the certificate can only validate that a module was signed with it, but it can no longer sign anything. Since signing a module applies the permissions of the associated certificate-based login (i.e. the login that was added to dbcreator), this prevents the customer from applying those permissions to their own modules, if they ever managed to create any. ALSO, if the customer manages to alter this stored procedure to do something that you are trying to prevent, the signature is dropped, meaning that the module no longer has the elevated permissions (even if they put the stored procedure back to the original code).

Please also take a look at the following answer of mine, also here on DBA.StackExchange, to a similar question:

Execute Permissions for a Store Procedure that creates databases


Is there a way to tell optimizer not to use index for reads? Of course I can just drop and recreate or disable and rebuild it.
But for large tables it can take time, and for critical tables it can cause issues.
Is there any "more sophisticated" way to tell sql to keep index data(+ handle updates\inserts) without using it?
So if needed it can be "enabled" immediately?

Other than using query hints to force different indexes, or using Query Store to force plans that don't use the index, no.  There's no way to "hide" an enabled index from the query optimizer.


Postgres query plan shows sequential scan instead of a index scan with on the fly data My use case is I am trying to select rows based on a list of known text values. For example, I have known text values as 'Frasier' and 'Cheers' and I want to select some rows that match the values.

EXPLAIN
    WITH lcaseid AS (
        SELECT lower(icaseid)
        FROM (
                 VALUES ('Frasier'),
                        ('Big brother')) AS i (icaseid)
    )
    SELECT *
    FROM products.catalog
    WHERE lower(id)
              in (
              SELECT *
              FROM lcaseid);

Hash Semi Join  (cost=0.10..12.76 rows=2 width=587)
  Hash Cond: (lower(catalog.id) = lcaseid.lower)
  CTE lcaseid
    ->  Values Scan on ""*VALUES*""  (cost=0.00..0.03 rows=2 width=32)
    ->  Seq Scan on catalog  (cost=0.00..12.29 rows=129 width=587)
            ->  Hash  (cost=0.04..0.04 rows=2 width=32)
            ->  CTE Scan on lcaseid  (cost=0.00..0.04 rows=2 width=32)

Why is Postgres doing Sequential Scan here? There is an index on lower(id):

CREATE UNIQUE INDEX lower_case_id ON products.catalog ((lower(id)));

When I do this instead, Postgres shows an Index Scan:

EXPLAIN
    SELECT
        *
    FROM
        products.catalog
    WHERE
            lower(id)
            in('frasier');

Index Scan using lower_case_id on catalog  (cost=0.14..8.16 rows=1 width=587)
  Index Cond: (lower(id) = 'frasier'::text)

The problem with the second approach is that I have to have the text values to be in lowercase because I can use them in the in clause. The first approach solves this problem by lower-casing when creating the temporary table lcaseid.

My guess is the temporary table is to blame for the sequential scan? Because Postgres does not have statistics on the temporary table, it simply does a sequential scan.

Also how should I go about solving the problem of selecting based on lowercase text values optimally?

Table definition:

CREATE TABLE "products"."catalog" (
    "id" text NOT NULL,
    "is_original" bool NOT NULL,
    "image_url_1" text NOT NULL,
    "image_url_2" text NOT NULL,
    "image_url_3" text NOT NULL,
    "image_url_4" text NOT NULL,
    PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX lower_case_id ON products.catalog ((lower(id)));

Postgres version: 9.6.12

Your test table is very small:

  rows=129 width=587

Around 10 data pages of 8 kB. Your index occupies 2 data pages most likely, incl. 1 meta page. (That's unless either is bloated with dead tuples or something, which decreases general performance while typically favoring the use of indexes.)

Postgres estimates that it pays to involve the index for a single input value, but not for two or more, for which case more and more data pages have to be read anyway. The overhead of involving the index won't pay any more. That may explain the different query plans. Hardly important, though, for the small table where either way is extremely fast, as was pointed out in comments already.

The important point of interest here: Postgres will use the index once the table gets big enough. Missing statistics for the implicit temporary table from the CTE won't stand in the way. While you are right that Postgres does not have detailed statistics there, like a MCV list (most common values) etc., it does know and factor in the number of rows - as can be seen in the query plan you posted:

  CTE Scan on lcaseid  (cost=0.00..0.04 rows=2 width=32)

And statistics on the table are typically overwhelmingly more important anyway.
If you have a case where detailed statistics on the input data will make a difference, you can create a temporary table instead of the CTE and ANALYZE it manually. (But I don't see that here.) See:

Are regular VACUUM ANALYZE still recommended under 9.1?

Either way, if your input values won't contain duplicates, you can simplify:

SELECT c.*
FROM   products.catalog c
JOIN  (
   VALUES ('Frasier'),
          ('Big brother')
   ) i(icaseid) ON lower(i.icaseid) = lower(id);

A bit faster because it gets rid of both the CTE and the IN which add extra cost. And less noise.
I don't see anything else to improve for either query or index. If you only need a small selection of columns from a much wider row, there might be potential for an index-only scan. But while you SELECT *, there is not.

There is the additional module citext, which might have useful features for you. But I don't generally recommend it, due to side effects and limitations.


PostgreSQL Export to CSV. Each element of array in new line I have next request

\copy (
  SELECT users.name, ARRAY_AGG(emails.email), ARRAY_AGG(addresses.address)
  FROM users
  LEFT JOIN emails USING(id)
  LEFT JOIN addresses USING(id)
  GROUP BY users.email
) TO 'path/to/file.csv' WITH csv header;

and it exports data in next way

|John Doe|{email1@qq.qq,email2@qq.qq}|{addr1,addr2}|

is it possible to export array in multiline cell? To have next view

-----------------------------
|John Doe|aaaaa1@aa.aa|addr1|
|        |aaaaa2@aa.aa|addr2|
-----------------------------
|Foo Bar |bbbbb1@bb.bb|addr9|
|        |bbbbb2@bb.bb|addr8|
|        |bbbbb3@bb.bb|     |
-----------------------------

I've tried to join array into a string with \r\n, but it doesn't work

Is it possible at all?

Test:

\copy (
  SELECT CASE WHEN users.name = LAG(users.name) OVER (ORDER BY users.name,
                                                               users.email,
                                                               addresses.address)
              THEN ''
              ELSE users.name
              END,
         emails.email,
         addresses.address
  FROM users
  LEFT JOIN emails USING(id)
  LEFT JOIN addresses USING(id)
  ORDER BY users.name,
           users.email,
           addresses.address
) TO 'path/to/file.csv' WITH csv header;


PL/SQL doesnt return rows Please help me debug this:

Working:

When I execute:

set serveroutput on

declare
    middle_name     varchar2(30);
begin
    middle_name := '0000000231';

    for x in (
            select distinct tablespace_name
            from all_tables
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_tab_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_indexes
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_ind_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lobs
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lob_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            order by 1
        )
    loop
            dbms_output.put_line('nir2');
    end loop;
end;
/

I see:

nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2

PL/SQL procedure successfully completed.

But when I execute:

create or replace package "PKG_PROVISIONING" as
    procedure prc_create_tablespace(
        v_datadir in varchar2
    );
end PKG_PROVISIONING;
/

create or replace package body "PKG_PROVISIONING" as
    procedure prc_create_tablespace(v_datadir in varchar2)
    is
        middle_name     varchar2(30);
    begin
        dbms_output.put_line('nir');
        middle_name := '0000000231';
        for x in (
            select distinct tablespace_name
            from all_tables
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_tab_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_indexes
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_ind_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lobs
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lob_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            order by 1
        )
        loop
            dbms_output.put_line('nir2');
        end loop;
    end prc_create_tablespace;
end PKG_PROVISIONING;
/

I see:

exec PKG_PROVISIONING.prc_create_tablespace('test');
nir

PL/SQL procedure successfully completed.

Both are from the same user. this use has DBA privelage.

Many times, when the code is not correctly working iinside package/procedure and is ok in anonymous PL/SQL; it comes from the AUTHID clause restrictions:

http://psoug.org/definition/AUTHID.htm


Updating a compatibility mode 80 database for SQL Server 2014 We have a SQL 2008 R2 server with a database on it that is running in compatibility mode 80. We are interested in upgrading 2008 R2 to 2014, but the upgrade adviser doesn't work because we can't force it to look for old syntax that works in compat 80 mode ( *= JOIN syntax for example), it just assumes the DB is a 2008 R2 DB, it ignores the compatibility mode.

What are our options here? We know it uses some deprecated syntax, and I can write some XQuery to check the cache for queries that use it, but is there anything else we can do that might be a little more concrete?

How about spinning up a SQL Server 2000 or 2005 server.  Then script out the 80 database objects and create them on the earlier version SQL Server.

Then run the SQL Server 2008 R2 upgrade advisor to collect the 2000 and 2005 errors that might exist. Once you fixed those issues on 2008 R2, then you can run the 2014 Upgrade Advisor.


Excluding records from SQL Query I have a table which I am trying to return only records of rendition_type Interactive and no other associated rendition_type. If a records has more than one rendition_type, I need to exclude it from the list.

DOC_ID    RENDITION_ID    RENDITION_TYPE
001       001             Interactive
001       002             Web PDF
002       003             Interactive
003       004             Interactive
003       005             Print PDF
004       006             Interactive
005       007             Interactive
005       008             Web PDF

I'm looking for a query that will return only rows where document_ID has Rendition_type type of only 1 and that type is Interactive.

This seems to give you what you want.

--demo setup
DROP TABLE IF EXISTS DocumentTable;
go
CREATE TABLE DocumentTable
    (DOC_ID int, RENDITION_ID int, RENDITION_TYPE varchar(11))
;

INSERT INTO DocumentTable
    (DOC_ID, RENDITION_ID, RENDITION_TYPE)
VALUES
    (001, 001, 'Interactive'),
    (001, 002, 'Web PDF'),
    (002, 003, 'Interactive'),
    (003, 004, 'Interactive'),
    (003, 005, 'Print PDF'),
    (004, 006, 'Interactive'),
    (005, 007, 'Interactive'),
    (005, 008, 'Web PDF')
;

--solution
SELECT *
FROM DocumentTable a
WHERE RENDITION_TYPE = 'Interactive'
    AND NOT EXISTS (
        SELECT *
        FROM DocumentTable
        WHERE DOC_ID = a.doc_id
            AND RENDITION_TYPE <> 'Interactive'
        )

| DOC_ID | RENDITION_ID | RENDITION_TYPE |
|--------|--------------|----------------|
| 2      | 3            | Interactive    |
| 4      | 6            | Interactive    |


SQL Server Deadlock Log Entries We're analysing a deadlock situation on a SQL Server 2005 database and have set the trace flags 1204 and 1222.

In the resulting logs we're seeing the following line repeatedly:

"Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values."

Can anyone tell me what's likely to be causing this? There's nothing particularly wrong with the queries that I can see (they're generated by Hibernate so are a bit messy and full of aliases but are fundamentally sound).

Is it something I need to worry about or something that can be ignored?

Something wrong with the viewer (looks like the bug in SSMS), but you always can open ERRORLOG in any other viewer, such as notepad and get exactly the same info


How to set "PRAGMA foreign_keys = ON" each time I'm connecting to sqlite db via Datagrip In my pet project I'm working with small sqlite db and I've just came to realization that, in order to ON DELETE CASCADE actually do the thing, I need to set PRAGMA foreign_keys = ON each time I'm connecting to db.

However I'm using Datagrip and since it's quite new product to me I don't what's the best way to set this option every time.

Please, go the SQLite driver options, Advanced tab and set true to foreign_keys parameter.


Changing the port of the postgresql server for security reasons For security reasons, it is common to change the port of Microsoft Sql Server.
The subject is discussed here in Is changing the SQL Server port really that much safer?

my question is: does this apply also to PostgreSQL ? Or is it unrelated ?

Opinions vary, but in my view it's mildly useful to use a non-default port on any Internet-exposed service that isn't for general public use. Changing your port is not security. It adds absolutely no security in normal operation, and is only minimally useful if your security is already compromised by the discovery of a remotely exploitable security hole. At best it's a delaying and mitigation option to give you a slightly higher chance of patching a security hole before a worm exploits it.

Using a non-default port will not protect you from having vulnerabilities exploited, or from any kind of targeted attack. It may help reduce the chances that a simple worm or casual broad-based scanning effort will attempt to exploit a security hole in your service, but it won't slow anyone who's interested in your system specifically down for a heartbeat.

Note that PostgreSQL doesn't have the same kind of history of severe remotely-exploitable security holes that MS SQL Server does. That doesn't mean it won't ever have remotely exploitable holes in it or in plug-in authentication mechanisms like Kerberos, so you should always be ready for the possibility.

For actual security, you want to be thinking about:

Limiting connections to trusted machines. If your DB is on a VLAN that only contains its self and the application server there's no point playing with ports. Similarly, if you don't need the DB exposed to the Internet, make sure it doesn't run on a public host or at least set it to bind only to an internal IP address and firewall it off too.
Banning clear-text auth. Use at least SSL, if not Kerberos or other moderately strong protocol.
Strong or multi-factor authentication; none of this username-the-same-as-password business.
Ensure your apps never use superuser roles and they only have the least required priveleges on the database objects they interact with. Your apps shouldn't connect with the user/role that owns the database or its tables unless absolutely necessary. GRANT them the minimum rights required.
Disallow remote connection by superuser roles in pg_hba.conf
IP connection limits. If all the machine's clients use a particular ISP, use a firewall to drop connections from outside the IP netblocks you know may be used. You can do it with pg_hba.conf too, but if you do it in the firewall you're preventing them from even handshaking with Pg.
... and lots more. There's lots on the 'net about locking down and securing Pg. See, eg, Depesz's article, Bruce Momjian's presentation, and this DeveloperWorks article.

I change the public SSH port on all my machines for a similar reason. It won't stop anybody, but it might slow automated attacks and worms down slightly. More importantly, it reduces the enormous volume of scanning spam in my system logs to the point where the logs become useful again.


EventClass 15 is "Audit Logout" there's no TextData to associate with that. It's simply the connection closing. TextData would only be associated with events like "SQL:StmtStarting", etc...

See here for more info: http://weblogs.sqlteam.com/mladenp/archive/2007/11/09/Map-SQL-Server-Profiler-EventClass-ID-to-its-name-in.aspx

From comments: The duration it shows in the log is the total connection duration.


How do I get Brent Ozar's sp_BlitzIndex to run on Azure? I downloaded the SQL Server First Aid Kit from Brent Ozar's website.  When I tried to run the sp_BlitzIndex script against my master database via Microsoft Sql Server Management Studio while logged in as the Azure database server level admin and I got the following error:

  Msg 262, Level 14, State 18, Procedure sp_BlitzIndex, Line 18 CREATE
  PROCEDURE permission denied in database 'master'.

I successfully created the procedure on the database instance I wanted to test.  When I executed the procedure I got an error stating:

  Msg 50000, Level 16, State 1, Line 1265 Invalid object name
  'mydatabase.sys.partitions'.

Next I tried to be clever and run the stored procedure code directly against the master database without creating the stored procedure and got the following error:

  Msg 50000, Level 15, State 1, Line 1267 Reference to database and/or
  server name in 'mydatabase.sys.indexes' is not supported in this
  version of SQL Server.

I don't feel confident enough to start toying with the inner workings of ~2700 lines of index heuristic logic.  Is there a quick, and easy way to get this stored procedure to work nicely on Azure SQL Database or should I look elsewhere for an index analyzing tool/stored procedure?

Kendra here (the author of sp_BlitzIndex)

First, thanks for being interested in the procedure and trying it out.

Azure doesn't expose all the dynamic management views that we get in the boxed product. I do actually reference sys.dm_db_partition_stats, but there's other info I need to get from sys.partitions for other users. (Is it using compression? What type?)

I haven't had time to write an test an Azure specific version, just because I haven't had a lot of demand for it. But your question does tell me there's more interest than I knew of -- so there's that!

I will at minimum look at implementing some error handling so it lets you know in a graceful way though. (This is a bit of a moving target, because Azure has been expanding the features that are available to users as well.)


TempDB size issue after applying Indexes I added several indexes to a SQL Server 2012 database to help with performance a couple of days ago. Today our partition with TempDB on it ran out of space. TempDB went from less than 10 GB to 38 GB. after running :

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

it looks like I have about 22GB of free space in tempDB. I understand that TempDB was used to do all of the sorting that was done by SQL Server but now it is causing space issues. If TempDB has that much free space why is it complaining about running out of space on the server? What can I do to prevent this problem from happening again?

Even though you have 22 GB of free space in tempdb now, that doesn't mean this space wasn't used before.  Something caused autogrowth to kick in, and once that process ends, there isn't anything to shrink tempdb back to its current size (and I'm not even going to talk about autoshrink...).

It sounds like the thing complaining about running out of space was the drive (or maybe it hit a hard limit that was set in SQL Server--I can't quite tell from your wording, but I'm guessing the drive).  It recognizes that you have a 38 GB tempdb file, but the Windows API has no clue about whether disk space allocated to an MDF or LDF file is "free space" or not; it's all considered in use by Windows, so it all counts.

The best way to prevent this problem happening is to figure out what caused your tempdb to grow and size appropriately.  If it's a one-time event (e.g., creating indexes with sort_in_tempdb on), tempdb will go back to its default size the next time you have to restart SQL Server.  But if it's as a result of a regular job or a change in queries that have caused significantly more tempdb usage (e.g., a marked increase in hash join spillovers), you would need to take this as your new high water mark and try to get some more disk space.

To help figure out which one it is, you can start with Mark Storey-Smith's answer to a previous question of similar nature.


NOSQL Manager for MongoDB throws command failed collection already exists While copying collections from one MongoDB server hosted on A to another MongoDB server hosted on B using Copy collection to another database tool of NOSQL Manager we got an error "Command create failed collection already exists code :48"

The option we specified in NOSQL Manager for copy collections is Replace existing collections.

https://www.mongodbmanager.com/clone-mongodb-collection#copy

Please make sure you have dropCollection privilege on the destination database.


Linking table or column key Simple structure:

have a table PAGES - which stores information about specific landing pages
working on developing a GROUPing concept for the pages, where we are saying that a page may or may not belong to a group, and it will only belong to a single group.

Would a linking table group_page.group_id,group_page.page_id be the proper way to go?  Or
pages.group_id ?

This is rather subjective based on the nature of the data, potential for change, deadlines, longevity of the project, storage space, performance requirements, etc.

However, the determining factor will likely be the probability that you will ever need multiple groupings for pages.   If you will, then implement a join table now.  If not, putting Group_Id inside the pages table is faster to build, less complex, will perform better, and uses less space.


Error "cannot connect to server" installing MongoDB on Ubuntu I'm trying to install MongoDB on Ubuntu 10.04.

I followed these installation instructions. I got the following error:

Error: couldn't connect to server 127.0.0.1:27017 src/mongo/shell/mongo.js:91

What do I need to do to fix this?

Here is the log output:

**** SERVER RESTARTED *****

> Sun Sep 23 22:36:20
Sun Sep 23 22:36:20 warning: 32-bit servers don't have journaling enabled by default. Please use --journal if you want durability.
Sun Sep 23 22:36:20
Sun Sep 23 22:36:20 [initandlisten] MongoDB starting : pid=1916 port=27017 dbpath=/var/lib/mongodb 32-bit host=bruce-ubuntu1004
Sun Sep 23 22:36:20 [initandlisten]
Sun Sep 23 22:36:20 [initandlisten] ** NOTE: when using MongoDB 32 bit, you are limited to about 2 gigabytes of data
Sun Sep 23 22:36:20 [initandlisten] **       see (http://)blog.mongodb.org/post/137788967/32-bit-limitations
Sun Sep 23 22:36:20 [initandlisten] **       with --journal, the limit is lower
Sun Sep 23 22:36:20 [initandlisten]
Sun Sep 23 22:36:20 [initandlisten] db version v2.2.0, pdfile version 4.5
Sun Sep 23 22:36:20 [initandlisten] git version: f5e83eae9cfbec7fb7a071321928f00d1b0c5207
Sun Sep 23 22:36:20 [initandlisten] build info: Linux domU-12-31-39-01-70-B4 2.6.21.7-2.fc8xen #1 SMP Fri Feb 15 12:39:36 EST 2008 i686 BOOST_LIB_VERSION=1_49
Sun Sep 23 22:36:20 [initandlisten] options: { config: "/etc/mongodb.conf", dbpath: "/var/lib/mongodb", logappend: "true", logpath: "/var/log/mongodb/mongodb.log" }
Sun Sep 23 22:36:20 [initandlisten] Unable to check for journal files due to: boost::filesystem::basic_directory_iterator constructor: No such file or directory: "/var/lib/mongodb/journal"
**************
Unclean shutdown detected.
Please visit http://dochub.mongodb.org/core/repair for recovery instructions.
*************
Sun Sep 23 22:36:20 [initandlisten] exception in initAndListen: 12596 old lock file, terminating
Sun Sep 23 22:36:20 dbexit:
Sun Sep 23 22:36:20 [initandlisten] shutdown: going to close listening sockets...
Sun Sep 23 22:36:20 [initandlisten] shutdown: going to flush diaglog...
Sun Sep 23 22:36:20 [initandlisten] shutdown: going to close sockets...
Sun Sep 23 22:36:20 [initandlisten] shutdown: waiting for fs preallocator...
Sun Sep 23 22:36:20 [initandlisten] shutdown: closing all files...
Sun Sep 23 22:36:20 [initandlisten] closeAllFiles() finished
Sun Sep 23 22:36:20 dbexit: really exiting now

You probably just need to delete the lock file in /var/lib/mongodb, and then restart the service.

If you still have problems, you should run mongod --repair (along with the other options, at least the dbpath).


What would be most elegant way to replicate data contents periodically into a MySQL table? I need to parse contents from an API route and insert into a MySQL database. The aim of parsing contents is to replicate the data from the API to my database table.

I need to check API response every day (at a specified time) using scheduled cron jobs and should fetch and insert all available records into the database.There could be changes in the response every time and it should be reflected as provided in the API.

There isn't any fields which could be considered for making a unique ID as well.

I can't search and sort as its not an updating task alone, hence rather than 'updating' of records, we should doing 'replication' of data from the API. We need to consider,

deletion of removed records from the API response,
updating modified record fields and
Also the insertion of new records.

Consider an example:

On day 1,

Let the records available with the API be A1, A2, A3, A4

There could be duplicate entries among this records .

We will be parsing the API response and insert ever record available as it is. The  script will fetch records (A1, A2, A3, A4) and insert it to our table.

Our table will hence contain records :( A1, A2, A3, A4)

Consider on the next day,

Let the records available with API be A1,A2,A3,A5,A6  -

Possible cases:

There could updated field values for each records A1, A2, and A3 than
day 1
A4 is removed from the API response now
New records A5,A6 are added now.

In this case our aim is to update the table such that, it should only have records A1, A2, A3, A5, A6 with their updated values.

The data is expected to be structured.There are specific fields from API,which are to be parsed and fetched.We can't estimate how often the changes would be made,and its asked to make update every day.

The response field values might change but not the structure.The fields for which the values should be fetched will remain same,only the change would affect the field values.

Currently there are about 2000 records ,which could increase up to 5000 soon.

There should not be any down time as the service from this table is used in real time by another application.I am parsing and inserting API response into a table which acts as a master table for another application.That application needs to work without any downtime,i.e; There should be least possible downtime even while we are doing some operations(recreating tables) over the table.

Sample API Response:

[
    {
        "company": "XYZ",
        "company_id": 123,
        "owner": null,
        "owner_id": null
    },
    {
        "company": null,
        "company_id": null,
        "owner": "ABC",
        "owner_id": 321
    },
    {
        "company": "XYZ",
        "company_id": 123,
        "owner": null,
        "owner_id": null
    },
    {
        "company": null,
        "company_id": null,
        "owner": "PQR",
        "owner_id": 100
    }
]

Database table should replicate the API response as given - irrespective of any errors/duplicates available in the API results.The API responses cannot be predicted before hand,also there isn't any direct fields which could be treated as a UNIQUE id.

I am using PHP-MySQL.API response is in JSON format. I had seen a similar question in SO,but it doesn't caters my requirement well and it also doesn't have an accepted answer.

https://stackoverflow.com/questions/1972944/what-is-the-best-way-to-periodically-load-data-into-table

From the above question it seems using some kind of temporary tables would be needed I my case as well.

What would be the most elegant way to approach this problem considering safety,performance and no downtime.What would be best MySQL storage engine to be used in this scenario-(InnoDB/MYISAM)?
Please advice.

If you don't need to retain the data from the previous day when you update the data, you could do this without downtime:

Day 1:

insert data from API into Table1

Day 2+:

create a new table with the same structure as Table1

create NewTable like Table1;

insert API data into NewTable (note: app is still using Table1 at this point)
switch out the tables in one statement

rename Table1 to OldTable, NewTable to Table1;

drop OldTable now if you want to, or keep some archives


In PostgreSQL is there a way to rank/weigh columns so as to order the results? Let's say I am searching through three text columns. I don't just want results that match a given word or phrase, but I also want to weigh them (column A > column B > column C) - thereby ordering the results such that those rows matching the word in column A will come up higher than those matching in column B, and those matching in column B will come up higher than those matching in column C.

Is this possible? Can you provide me with some terminology or suggestions for further reading?

Full text search has detailed weighting mechanisms.

For simple pattern matching:

SELECT *
FROM   t
WHERE  col_a ILIKE '%keyword%' OR
       col_b ILIKE '%keyword%' OR
       col_c ILIKE '%keyword%'
ORDER  BY col_a ILIKE '%keyword%' OR NULL
        , col_b ILIKE '%keyword%' OR NULL
        , col_c ILIKE '%keyword%' OR NULL;

I don't use col_a  ILIKE '%keyword%' DESC, because that would sort NULL columns first. We want to treat NULL just like FALSE, which is achieved this way.


Importance of uniformity of hardware for Galera cluster How important is it for the nodes in a Galera cluster to be identical?

I've found this answer which says they should be the same:
https://dba.stackexchange.com/a/55224/770

I understand that you should not have a node that is really slow compared to the others, but if the nodes use the same disks, the same amount of RAM and similar CPUs, shouldn't that be good enough?

You basically answered your question yourself: the main problem is uniformity of response time- you cluster, as a whole, will be as fast as the slowest of your nodes (due to the roundtrip). This may affect not only your hardware choice (for example, all nodes having SSDs except one will be a bad choice) your network topology too- a remote server will slow down nodes on the same data center. Also, you do not want one of the nodes to run out of space, which may take some time to be desynced.

  the nodes use the same disks, the same amount of RAM and similar CPUs, shouldn't that be good enough?

Yes, "it should be enough", now the question is if it actually is in practice. Makes some tests on latency, specially under load (mysqlslap/sysbench). You never know when a small detail can be a big difference. It is also easier to manage uniform hardware and software (for replacements/fixes and for comparing performance). But you are mostly right.


SQL Server Configuration Manager not showing services My SQL Server 2014 Configuration Manager is empty except for SQL Server Integration Services 12.0. It should show a bunch of other services, Like SQL Server, SQL Browser, SQL Agent, etc. I found a related question, and did the solution listed there. I successfully changed the value, but it didn't fix my issue. Here's the question/solution I tried:

https://dba.stackexchange.com/a/5208/125955

I tried installing another instance of SQL Server but those services do not show up either. The Database Engine is for sure installed, I can connect to the server and do stuff via SQL Server Management Studio.

Start-->Run and type services.msc and check installed SQL Services.  Do you see the installed SQL Server services?
Please refer below articles.

These may help:

SQL Server configuration manager is empty
Why is SQL Server Configuration Manager Missing Services


Is there any way to Rebuild (and Reorganize) the indexes automatically? I have created some non-clustered index on my tables in SQL Server 2014.

Is there any way to Rebuild (and Reorganize) the indexes automatically? I mean is it possible to schedule them to be built daily, weekly, ... ?

I have read (and re-read your question) - Your question is "Is there any way to Rebuild (and Reorganize) the indexes automatically?  I mean is it possible to schedule them to be built daily, weekly, ... ?"

The answer is yes.  You have several options.

You can choose to set up a Sql Maintenance plan (but I would not recommend this as you have very little control over what gets rebuilt/reorgd).  The maintenance plan allows you to specify the scheduling criteria (daily, monthly, whatever) and generate a Sql Server job.

A better solution would be to use a customized script like the one that @Ali Razeghi mentioned (Ola's).  This script (and others available) let you configure options regarding when to rebuild vs reorg and only do index maintenance on the tables 'that need it'.  You can create a custom Sql Server agent job to run the custom script whenever you need to.  If you don't want to use the Sql Agent, you can also use the Windows Task Scheduler to invoke a SQLCMD bat file process to run the index maintenance scripts.

Regardless of whether you choose the maint plan route or custom sql agent job, make sure you alter the 'notification' properties of the job to notify interested parties in the event of a failure.  You'll need to create one or more Sql Operators for the notification to send error emails to.  It's also wise to go into the job step properties (advanced) and check the box to include the step output in history or even log the output to a table.  Including the step output in history may not show you everything because there is a limitation as to how much information can be stored.  Saving the output to table has no limitation (that I'm aware of)

Index maintenance is an intense IO and CPU process and should be scheduled during non-peak-usage times


Does InnoDB allow adding columns to a table with concurrent READ/WRITE of rows? I have a large (3M rows) InnoDB table in MySQL 5.6.21 in production and I want to add some columns.

From the MySQL Documentation on Overview of Online DDL I understand that, even if this is an expensive operation that might degrade performance, concurrent DML is allowed as long as I don't add auto-increment columns, and concurrent queries (SELECTs) are always allowed.

Is this right? Do I have to explicitly state ALGORITHM=INPLACE and LOCK=NONE in the alter operation for this to be possible?

I'll be adding mainly unindexed, not null VARCHAR columns.

I have mentioned ALGORITHM=INPLACE in two other posts

Mar 27, 2014 : When tables are locked does MySQL queue queries?
Feb 12, 2015 : How to evolve MySQL schema while maintaining integrity

In the second post, I named some caveats on using online DDL

  The online schema change feature uses the folder mapped in tmpdir. Make sure the copy of the table can fit entirely in tmpdir. For example, if tmpdir is mapped to /tmp and the table you are changing is 7GB, there has to be more than 7GB free in /tmp. If /tmp is not large enough, please consider mounting /tmp on a large volume.

What I have mentioned my seem like overkill to discuss. Notwithstanding, the point I am making, to answer your question, is yes you can do INSERTs and SELECTs

CAUTION: You should test adding VARCHAR that has a default of NULL, an INT with a default and without a default in a Staging server just to see if you can live with any slippage in performance during INSERTs and SELECTs.

This new feature of online DDL is Oracle's answer to Percona's pt-online-schema-change.


Inner Joins between Subsets of the Same Table All, I am trying to figure out the syntax for doing joins between subsets of the same table.

I have:

Employee ( EmpId PK , EmpFirst, EmpLast, EmpMid, DateHired, SSN, DateBirth, Gender, PhoneNum, ReportsTo)

And I want to find , for each employee, the person they report to.

So I am thinking of using:

select empFirst, emplast, empId as Managers inner join
       (select employeeid, empfirstname, emplastname, reportsTo, from Employee) AS Staff
on Managers.employeeid= Staff.reportsTo.

But it seems I need to do something that does not make sense to me, the part between the ** s:

**select managers.employeeid, managers.empfirstname , managers.emplastname , staffmembers.emplastname , staffmembers.reportsTo **
from (select employeeid, empfirstname, emplastname from employee) AS managers
inner join
(select employeeid , empfirstname, emplastname, reports to from employee) AS Staff
on managers.employeeid = staff.reportsTo

Why do we use the part between the ** s?  I am trying to see the logic behind
the syntax, but I am having trouble getting it. Besides, this  inner join of subsets (I assume each subset is itself a table) does not seem to agree with
the usual definition of the inner join of tables.

Is it possible to just rename the table as, say Staff, keep the original one and then use a join between staff and employee?

You are trying to do self join which is perfectly fine in this case. You can create another identical table and rename it, but is not necessary.
You should write something like this:

    SELECT m.employeeid AS ManId, m.empfirstname AS ManFirstName,
    m.emplastname AS ManLastName,
    s.employeeid AS StaffId, s.empfirstname AS StaffFirstName,
    s.emplastname AS StaffLastName
    FROM Employee s
    JOIN Employee m
    ON s.ReportsTo = m.employeeid


How can I hunt down what is trying to use SA? I'm not sure of the change that caused this (the logs don't show anything specific) but ever since Monday afternoon I've seen the following error repeated:

Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 10.10.220.32]

The IP reported is the SQL Server. Since this is ongoing, is there a way I could figure out what is trying to pass this credential? If I could get an application name or anything I could probably fix the problem.

To track login failure reason SQL Server Profiler would be a good option. One can also use extended events trace to capture login failed events and reason behind the failure.

You need to first launch SQL Server Profiler.Create a new template or you can use existing one if you have already. Please include below when creating the trace

1.Audit Login Failed (under Security Audit)
2.User Error Messages (under Errors & Warnings)
3. Errorlog (under Errors & Warnings)

Then clear the Show All Events tick box and select following columns

TextData
SPID
ClientProcessID
Hostname
LoginName
NTUserName
NTDomainName
ApplicationName
DatabaseName
Error

More details can be taken from This Link and This Link

To read more about SQL Server profiler refer to this Microsoft Online Doc


copy ldf from one server to another I keep getting "file is open in another program" when i try to copy LDFs from one production to dev. Is there another way to copy these ldfs to dev server?

Take a step back and ask, why are we copying log files?  SQL Server LDF files contain transaction log data, but they're not terribly useful by themselves.  To work with a database, you need both the data files and log files.  In order to access those files directly via the file system, though, you'll either need to stop SQL Server or use snapshot technologies like VSS.

If you're trying to synchronize transactions across multiple databases, check out technologies like transactional replication.

If you're trying to back up the database, use the SQL Server BACKUP DATABASE command.