Create the table manually on the slave. This should allow replication to continue, assuming you are using STATEMENT-based replication. There's a chance replication will break again when the slave processes the 'CREATE TABLE' statement, unless you did 'CREATE IF NOT EXISTS'. If replication breaks here, simply skip it and continue on.

If you are using ROW-based replication (to check, on master: SELECT @@binlog_format), you'll have to dump (using mysqldump) or export (http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html) the table from the master and copy to the slave. Why? Because ROW-based replication transmits a BEFORE image of the row. If the row doesn't exist, then replication breaks.

Once replication has caught up, you can use pt-table-sync to bring over any other missing data for this one table.


How to load geometry point tables in sql spatial

Its very clunky but you need to use stpointfromtext
geometry::STPointFromText('POINT(xxxxx yyyyyy)', SRID)
where x andf y are the co ordinate values and SRID is the grid identifier for the projection you are using. Wrap it all up with a cursor like the following example
https://support.esri.com/en/technical-article/000012317


SQL Server Express 2008: Can I store database files (.mdf, .ldf) in directory other than the default DATA directory?

You can place your database files anywhere you want, you just have to specify these locations within SQL Server itself.  There are two ways to relocate those files.

Create in a new location

When you create a database or add a file to the database, you can specify the new location and the file will be created there.  For example, if I wanted to add a new data file to my database, I would do the following

ALTER DATABASE [foo]
ADD FILE (name=foo_data_02, filename='X:\YourCustomLocation\foo_data_02.ndf');

As long as your SQL Server Service Account has access to the path, whatever you put after 'filename' will be where your file is created.

Move to a new location

You can also move an existing data file to a new location.  This requires a couple steps.  The first is to change the path within SQL Server first, like so:

ALTER DATABASE [foo]
MODIFY FILE (name=foo_data_01, filename'X:\YourCustomLocation\foo_data_01.mdf`)

By modifying this, the next time this database starts up it will look for the file in that location.  Once you've changed it, you will actually need to move the file.  To do this, take the database offline:

ALTER DATABASE [foo]
SET OFFLINE WITH ROLLBACK IMMEDIATE;

Once the database is offline, then copy the file to the new location using your preferred method.  Once it has been copied, bring your database online:

ALTER DATABASE [foo]
SET ONLINE;

At this point, your database will now be running with your files in the new location.


Do MySQL (5.6) triggers support 4 byte emojis?

As in the github issue was recommended to change the settings on the following:

connection_charset: utf8mb4
connection_collation: utf8mb4_unicode_ci
charset: utf8mb4
collation: utf8mb4_unicode_ci

I suppose you could change

"collation_connection","utf8mb4_general_ci"

on

"collation_connection","utf8mb4_unicode_ci"


Reattaching MDF files recovered from RAID 5

Is there any way at all to restore these files?

No.

The long answer is like this:

you first will have to fix the MDFs. Attempt to attach it with ATTACH_REBUILD_LOG
as you encounter errors (like incorrect pageid (expected 1:134; actual 0:0). It occurred during a read of page (1:134)) you have to edit the file and fix it. This, of course, requires you to know in fairly much detail the (undocumented) MDF format. Anatomy of  Page is a decent starting point, but it really just skims the surface. I really hope your MDF boot page is correctly 'restored'.
after you fix all the errors, try to evaluate the consistency of the MDF, if possible with a CHECKDB. Fix any error, some will require again manual repair.
in theory you can try to attempt to see how much of the recovered LDFs can be repaired and applied. I estimate this task something 10x the complexity of fixing the MDF, due to the complexities of the LDF format and because of the LSN chain requirement. I would abandon the idea of trying to rebuild the 'restored' log, just try to 'rebuild' the restored data.

You can use OrcaMDF to attempt to browse the MDFs 'cold' and see if OrcaMDF complains about structure issues, then attempt to fix them.

It is critical that the 'restore' process kept the MDF file size, and restores 0s for bad sectors. If they simply removed the unrecoverable sectors you stand no chance.


Foreign Key problem, ORA-00907: missing right parenthesis on creating table & ORA-01735: invalid ALTER TABLE option

Oracle database does not support ON UPDATE CASCADE clause.

references_clause::=

The below works:

CREATE TABLE Members (
 MemberID NUMBER GENERATED AS IDENTITY START WITH 1 INCREMENT BY 1,
 MemberFirstname VARCHAR2(50) NOT NULL,
 MemberMiddlename VARCHAR2(50),
 MemberLastname VARCHAR2(50) NOT NULL,
 MemberDOB DATE NOT NULL,
 Email VARCHAR2(60) NOT NULL UNIQUE,
 Mobile VARCHAR2(11) NOT NULL UNIQUE,
 Telephone VARCHAR2(13) UNIQUE,
 MemAddID NUMBER,
 MemTypeID NUMBER,
 CONSTRAINT MEM_ID_PK PRIMARY KEY (MemberID),
 CONSTRAINT MEM_ADD_FK FOREIGN KEY (MemAddID) REFERENCES MemberAddress (MemAddID) ON DELETE CASCADE,
 CONSTRAINT MEM_TYPE_FK FOREIGN KEY (MemTypeID) REFERENCES MemberType (MemTypeID) ON DELETE CASCADE
);


Why does backup cause two media sets in msdb.dbo.backupmediaset?

There is nothing in SQL Server protects you to reuse 'MEDIANAME'.
Taking your query and reusing for a a different database I will give you an example and that is the same reason you are seeing 2 records with same name.

BACKUP DATABASE [WideWorldImporters]
TO  DISK = 'C:\MaintenanceTest\Maintenance1.bak',
    DISK = 'C:\MaintenanceTest\Maintenance2.bak'
WITH FORMAT
, INIT
, MEDIANAME = 'Maintenance'
, NAME = 'Chapter15-Full Database Backup'
, COMPRESSION ;
GO

Once backup is done :

 SELECT
    media_set_id,
    media_uuid,
    media_family_count,
    name,
    description
 FROM [msdb].[dbo].[backupmediaset]

SELECT bm.media_family_count, bm.[name],bmf.physical_device_name
  FROM [msdb].[dbo].[backupmediafamily] AS bmf
  JOIN [msdb].[dbo].[backupmediaset] AS bm
  ON bmf.media_set_id=bm.media_set_id

Now doing the same again but changing the backup folder name to 'MaintenanceTest1' (different physical location).

BACKUP DATABASE [WideWorldImporters]
TO  DISK = 'C:\MaintenanceTest1\Maintenance1.bak',
    DISK = 'C:\MaintenanceTest1\Maintenance2.bak'
WITH FORMAT
, INIT
, MEDIANAME = 'Maintenance'
, NAME = 'Chapter15-Full Database Backup'
, COMPRESSION ;
GO

For backupmediaset:

To see physical location:


Don't see it : Integration Services Catalog, and open the [All Executions] report

Right click on the SSIS Catalogue, highlight Reports, and select All Executions.

The Simple Talk article, SSIS 2012 Projects: Deployment Configurations and Monitoring by Feodor Georgiev, includes the following illustration:

If you are not observing the Reports submenu, try running Setup again and choose Management Tools – Complete. Try executing SSMS as Administrator.


MySQL-5.1 is not updating some rows with a simple UPDATE query

It turns out that there are duplicate matches for many cases of the join between A and B, and MySQL is choosing the C.desc in an undefined but consistent manner from the duplicates. When performing an UPDATE, it chooses one of the duplicates, but when SELECTing it chooses another. So selecting the ones that are different will return a result, but updating rows will indicate no changes.

Moral of the story: Don't forget to check for duplicates, because MyISAM doesn't.


Depends on the client you're using really, but this should work in SQL*Plus, SQL Developer etc.

set serveroutput on;

declare
   call_charge VARCHAR(10);
   MSG_OUT     VARCHAR(50);
BEGIN
   FOR REC IN (SELECT *FROM dulguunbaatar.stl_mbc_cdr_1062) LOOP
   pk_test_duba.recharge_idd(REC.Called_Number,
                               rec.call_date||rec.call_time,
                                                         rec.duration,
                                                         CALL_CHARGE,
                                                         MSG_OUT);
    DBMS_OUTPUT.PUT_LINE('charge: '||CALL_CHARGE||' message: '||MSG_OUT);
    END LOOP;
end;


Try something along the lines of:

UPDATE Table2
INNER JOIN Table1 ON Table2.[My Values] >= Table1.Start AND Table2.[My Values] < Table1.End
SET Table2.Bucket = Table1.Bucket;

Essentially, we get the value from Table1 by comparing the My Values field in Table2 to see if it is between Table1.Start and Table1.End

This will update the entire Table2 in a single statement, and shows the power of set-based approaches to data vs what is referred to as "row by agonizing row" or RBAR.  See https://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/ for a great explanation of that.


"Recheck Cond:" line in query plans with a bitmap index scan

As @Chris commented correctly on the referenced question:

a little investigation seems to indicate that the recheck condition is
always printed in the EXPLAIN, but is actually only performed when
work_mem is small enough that the bitmap becomes lossy. Thoughts?
http://www.postgresql.org/message-id/464F3C5D.2000700@enterprisedb.com

While this is all true and the core developer Heikki Linnakangas is a first class source, the post dates back to 2007 (Postgres 8.2). Here is a blog post by Michael Paquier with detailed explanation for Postgres 9.4, where the output of EXPLAIN ANALYZE has been improved with more information.
The Recheck Cond: line is always there for bitmap index scans. The output of basic EXPLAIN won't tell us more. We get additional information from EXPLAIN ANALYZE as can be seen in the second quote in the question:
Heap Blocks: exact=693 lossy=3732

From a total of 4425 data pages (blocks), 693 stored tuples exactly (including tuple pointers), while the other 3732 pages were lossy (just the data page) in the bitmap. That happens when work_mem is not big enough to store the whole bitmap built from the index scan exactly (lossless).
The index condition has to be rechecked for pages from the lossy share, since the bitmap only remembers which pages to fetch and not the exact tuples on the page. Not all tuples on the page will necessarily pass the index conditions, it's necessary to actually recheck the condition.
This is the thread on pgsql hackers where the new addition was discussed. The author Etsuro Fujita provides a formula for how to calculate the minimum work_mem to avoid lossy bitmap entries and ensuing condition rechecks. The calculation is not reliable for complex cases with multiple bitmap scans, so it was not used to output actual numbers from EXPLAIN. It can still serve as an estimate for simple cases.
Additional line BUFFERS:
In addition, when running with BUFFERS option: EXPLAIN (ANALYZE, BUFFERS) ... another line is added like:
Buffers: shared hit=279 read=79

This indicates how much of the underlying table (and index) was read from the cache (shared hit=279) and how much had to be fetched from disk (read=79). If you repeat the query, the "read" part typically disappears for not-too-huge queries, because everything is cached now after the first call. The first call tells you how much was cached already. Subsequent calls show how much your cache can handle (currently).
There are more options. The manual about the BUFFERS option:

Specifically, include the number of shared blocks hit, read,
dirtied, and written, the number of local blocks hit, read, dirtied,
and written, and the number of temp blocks read and written.

Read on, there's more.
Here is the list of output options in the source code.


48 hours ALTER to a 12 billion rows table is not out of the ordinary in InnoDB.

You could:

Use Percona online schema change \ Facebook php online schema change tools to ALTER the table online.
Switch to TokuDB engine which does these alters online and also improves performance when scaling up from InnoDB.

Both solutions are free.


SQL Server AlwaysOn AG Secondary Replica is not joined

I found the problem. It is my authentication method problem. I changed my account and it is resolved.


you have to put your query in CTE for it you have to use  ROLLUP

;WITH CTE AS
(
-- Your Query
)

then

SELECT
  RowLabels = ISNULL(RowLabels, 'Grand Total'),
  FacetoFace = SUM(FacetoFace),
  Phone = SUM(Phone),
  GrandTotal = SUM(GrandTotal)
FROM CTE
GROUP BY ROLLUP(RowLabels);

SAMPLE DEMO

declare @temp table (RowLabels nvarchar(max), FacetoFace int, Phone int, GrandTotal int)

insert into @temp values ('Craig Irwin  ',73,6  ,79)
insert into @temp values ('Holly Murphy ',27,124,151)
insert into @temp values ('Julie Brown  ',37,28 ,65)
insert into @temp values ('Kevin Borth  ',52,35 ,87)
insert into @temp values ('Linda Krueger',46,23 ,69)
insert into @temp values ('Robert Bell  ',66,21 ,87)

SELECT
  RowLabels = ISNULL(RowLabels, 'Grand Total'),
  FacetoFace = SUM(FacetoFace),
  Phone = SUM(Phone),
  GrandTotal = SUM(GrandTotal)
FROM @temp
GROUP BY ROLLUP(RowLabels);


I think the problem you're trying to solve has been solved for Access 2016, but nothing for Access 2013 (AFAIK). Check What's new in Access 2016 - July 2016:

July 2016
ODBC connection retry logic
When Access loses the ODBC connection to an external data source, users have to close and re-open their Access databases in order to continue working with objects that use data from those external data sources. Access doesn't attempt to re-establish the dropped connection even if the connection is valid again and there is nothing that users can do to make Access try and reconnect to the external data source.
We've improved this experience so that now if Access loses the ODBC connection to an external data source, Access tries to reconnect to the data source automatically. In this scenario, you'll now see the following experience:
If Access loses the ODBC connection to an external data source you're trying to use, you'll see an error message describing the failure.
Access now attempts to reconnect to the data source automatically when performing operations that require a connection to an external data source. If the retry is successful, you can continue working with the data and database objects without having to close and re-open Access.
If the retry fails, you won't be able to work with objects that use the dropped external data sources but you can continue working with other areas of your database that aren't dependent upon the unreachable data sources.
If you attempt to use data or other objects again that rely on the external data connections, Access tries again to reconnect to the external data source.


MySQL high CPU usage (MyISAM table indexes)

SUGGESTION

Your key_buffer_size is only 16MB, but your MyISAM indexes total 59MB. In a highly-trafficked website, I can see index pages getting kicked out of and pushed into the MyISAM keycache frequently. This could have an affect on indexes being updated and overall write performance on the MyISAM tables.

You could just bump up key_buffer_size to 64 MB.

You must add this to /etc/my.cnf

[mysqld]
key_buffer_size=64M

You don't even need to restart mysql. Just run this:

SET @MB = 1024 * 1024;
SET GLOBAL key_buffer_size = @MB * 64;

You can also convert the row format of all your MyISAM tables. This will just about double the disk space used by will increase read performance 20-25%. I wrote about this before as well:

Which is faster, InnoDB or MyISAM?
Best of MyISAM and InnoDB

Here is a script to convert all your MyISAM tables to Fixed Row Format

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ROW_FORMAT=Fixed;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN ('information_schema','mysql','performance_schema')"
mysql -uroot -p -A --skip-column-names -e"${SQLSTMT}" > /root/MakeFixedRows.sql
mysql -uroot -p < /root/MakeFixedRows.sql

ALTERNATE SUGGESTION

Given you have a total of 488 MB of of MyISAM data and indexes, I would like to suggest the following: Switch Everything to InnoDB. I have two reasons:

REASON # 1

MyISAM only caches indexes. InnoDB caches data and indexes. See my post : What are the main differences between InnoDB and MyISAM?

This will reduce disk I/O for reading data and indexes. In your particular case, you can easily handle an InnoDB Buffer Pool of 512M and basically fit everything into RAM.

REASON # 2

The MyISAM storage engine cannot access multiple CPUs. InnoDB can. In fact, I have many posts on tweeking InnoDB for this:

Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance

EPILOGUE

I would like you really consider the conversion from MyISAM to InnoDB

When to switch from MyISAM to InnoDB?
Any gotchas at all with converting from MyISAM to InnoDB?
InnoDB vs MyISAM with many indexes


Use a Script Component to perform the conversion!  Be sure to set your read / write variables of course!  It's quite easy for use specific locale settings with the .NET framework, so a Script Component should be superior than trusting the database to correctly do the conversions for you.

Using a Script Component to Parse a DateTime Value from a String

    using System.Globalization;

    ...

    public void Main()
    {
        String str = ( String )Dts.Variables[ "StringVariable" ].Value;

             // Good candidate for another variable, this is just an example.
        String format = "ddMMMyyyy";
        CultureInfo provider = CultureInfo.InvariantCulture;

        DateTime dt = DateTime.Parse( str, format, provider );
        Dts.Variables[ "DateTimeVariable" ].Value = dt;

        Dts.TaskResult = ( int )ScriptResults.Success;
    }

And in VB, if that's How You Roll™

    Imports System.Globalization

    ...

    Public Sub Main()
            Dim str As String = Dts.Variables("StringVariable").Value
            Dim format As String = "ddMMMyyyy"
            Dim provider As CultureInfo = CultureInfo.InvariantCulture
            Dim dt As DateTime = Date.ParseExact(str, format, provider)
            Dts.Variables("DateTimeVariable").Value = dt
            Dts.TaskResult = ScriptResults.Success
    End Sub


Can I design a table that will use some columns just in certain times?

Instead of storing number of repeating DocType names you store the ids of the DocType descriptions from the reference table. This is a common practice called normalization.

The sparsed table with nullable attributes is good enough while the number of possible attributes isn't too big. Sometimes it's more efficient to store all the attributes in the aux table docid -- attrname -- value containing only not-null values and join them whenever you need.

SELECT ud.*, da.*
  FROM UserDocuments  AS ud
  JOIN DocsAttributes AS da ON da.doc_id = ud.id
 WHERE ud.ID = 12345      -- for certain document only

The only difference is that by join you'll get not the single row but the table like that:

            UserDocuments      |       DocsAttributes
 ------+----------+------------+--------+----------+-----------
    id | FK_users | FK_DocType | doc_id | attrname |     value
 ------+----------+------------+--------+----------+-----------
 12345 |      456 |          7 |  12345 | RGNumber |       9876
 12345 |      456 |          7 |  12345 |  RGEDate | 2019-05-01
 12345 |      456 |          7 |  12345 |   RGEOrg |       7777
 12345 |      456 |          7 |  12345 |   CPFNum |      88888

This table can be easily parsed on the app side.

An advantage of that approach is that you can add as many attributes as you want. Attributes names can be separated into the reference table for the normalization reasons.

The main disadvantage is that all attributes should be converted into the same type (VARCHAR() for example) and some functionality can be lost - like datetime arithmetics and/or timezone conversions.

Having a number of tables for each type of document isn't a good design.

There is no universal recipe and you have to decide what approach is suitable for you. Sometimes single table with hardcoded list of attributes is preferrable. Sometimes an expandable table of EAVs (entity-attribute-value) fits better.


MySQL: Does access to table data not in an index via a join causes a file-sort when ordered

The temporary table is caused because you have a dependent subquery in your join to calculate the MAX(startdate).

I was able to improve the EXPLAIN by adding an index:

ALTER TABLE feature
  ADD INDEX item_startdate_category (item_id, start_date, category_id);

And changing the query to avoid the dependent subquery:

EXPLAIN EXTENDED SELECT i.id, f1.id, c.id
FROM item i
LEFT JOIN feature f1 ON (f1.item_id = i.id)
LEFT JOIN feature f2 ON (f1.item_id=f2.item_id AND f1.start_date < f2.start_date)
LEFT JOIN category c ON (c.id = f1.category_id )
WHERE f2.item_id IS NULL
ORDER BY i.id DESC LIMIT 0, 1;

This join between f1 and f2 is meant to ensure that f1 has the largest start_date for its respective item_id. If the LEFT JOIN to f2 finds no other row with the same item_id and a greater start_date, then f1 must have the greatest start_date.

Here's the EXPLAIN I got:

+----+-------------+-------+------------+--------+--------------------------------------------------------+-------------------------+---------+---------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys                                          | key                     | key_len | ref                 | rows | filtered | Extra                    |
+----+-------------+-------+------------+--------+--------------------------------------------------------+-------------------------+---------+---------------------+------+----------+--------------------------+
|  1 | SIMPLE      | i     | NULL       | index  | NULL                                                   | PRIMARY                 | 4       | NULL                |    1 |   100.00 | Using index              |
|  1 | SIMPLE      | f1    | NULL       | ref    | tbl_feature_id_item_id_foreign,item_startdate_category | item_startdate_category | 5       | test.i.id           |    2 |   100.00 | Using index              |
|  1 | SIMPLE      | f2    | NULL       | ref    | tbl_feature_id_item_id_foreign,item_startdate_category | item_startdate_category | 5       | test.f1.item_id     |    2 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY                                                | PRIMARY                 | 4       | test.f1.category_id |    1 |   100.00 | Using index              |
+----+-------------+-------+------------+--------+--------------------------------------------------------+-------------------------+---------+---------------------+------+----------+--------------------------+

I tested on MySQL 8.0.0-dmr on my Macbook.


how to increase column values by 20 percent using mysql

Simply calculate 20% of actual price:

update your_table
set price = price + (price * 20.0 / 100.0)
where id = (some condition)

or a shorter way:

update your_table
set price = price * 1.2
where id = (some condition)


How to check if a query (CRUD) was made on a database

I would have thought that the Audit functionality would meet your requirements.
If you search for SQL Server Audit you'll get lots of hits - it is very configurable but too big to describe fully here.
Have you looked at that ?


Try this:

SELECT c.*, e.*
FROM company AS c
  INNER JOIN employee AS e on e.ID = c.employee_id
  INNER JOIN multinational AS m ON (m.company_ID = c.id and m.market = 'EMEA')
ORDER BY salary DESC
LIMIT 0, 24


Fabric - Operation CREATE USER failed for 'fabric_server'@'%''

You can try to execute on your secondary:

mysql> drop user 'fabric_server'@'%';

mysql> stop slave;

mysql> start slave;

Worked for me.

Best Rgds,
Jeferson Santana
DBA


How to reduce the mdf file size in this database which I have 50 GB reserved space

You probably used the default in the GUI, which resulted that the GUI added the TRUNCATEONLY option. This means "don't move any data" and you probably had a situation where the last page in the database was in use. Hence the file didn't shrink.

By using the "Reorganize ..." option, SQL Server will move pages towards the beginning of the files, thus making the end of the file free and the file can be shrunk. Such page movements can take a terrible long time, fragment your indexes etc etc, so don't skrink unless you have a high enough payback. My article on shrink.


Transaction log growing on a mirrored principal

Mirror, Synchronized / Restoring... is the typical state for the mirror instance.

You can determine why the log file is growing by issuing the following command in a SQL Server Management Studio query window:

SELECT name, LOG_REUSE_WAIT_DESC
FROM sys.databases
WHERE name='your_database_here';

This will show several possible choices, such as:

NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT

You may need to perform at least one transaction log backup, among other actions depending on the LOG_REUSE_WAIT_DESCRIPTION shown in the query above.  For instance, there may be a long-running transaction that is forcing the log files to not be re-used.


Mysqldump with ansible for database backup

I am really sorry to post this one. AS i later realized, this was more of a ansible solutions i was looking for rather than mysqldump.

I tried many alternatives and finally this one worked for me.

What this playbook does is, it on the first part takes the database backup using mysqldump and creates the backup in remote_server.

In the later part, it copies the backup from the remote_server to local server(the one hosting the ansible) with fetch command to local directory.

Once it is in the local directory, this can be copied to which ever server we want.

Hope  this one helps to someone else as well.

Thank you everyone for the help.


According to the PostgreSQL Wiki this sort of thing isn't possible.

  PostgreSQL has no facilities to limit what resources a particular
  user, query, or database consumes, or correspondingly to set
  priorities such that one user/query/database gets more resources than
  others. It's necessary to use operating system facilities to achieve
  what limited prioritization is possible.

As suggested in the text above, you could try using operating system facilities to achieve limits such as that. For example, ulimit can be used to create process-specific resource limits.


To what extent can we monitor jobs/suspended queries as DBO

Though logging into the machine and analyzing the wait chain on the Resource Monitor could give some extra info (for instance if sqlservr.exe thread is blocked waiting for lsass.exe it could mean it is blocked at a authentication validation), what you'd really want is to be granted the VIEW SERVER STATE permission so you can view the DMVs that might help you, like this ones, for instance:

SELECT sqltext.TEXT,
 req.session_id,
 req.status,
 req.command,
 req.cpu_time,
 req.total_elapsed_time
 FROM sys.dm_exec_requests req
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
order by cpu_time desc


Your options are

to use stored procedures for data access and write log of changes,
or to use triggers on specified tables to write log of changes

and after it you have periodically poll the log tables to reflect to the tables changes

See related answers:

https://stackoverflow.com/questions/8998545/notifications-from-sqlserver-to-java/8998579#8998579

https://stackoverflow.com/questions/8895595/sql-server-track-table-inserts/8895683#8895683


Populate a table that should have an X amount of rows with default values with a big insert query? Or can it done somehow by default?

you can use generate_series() for that:

insert into field (textfield, idcal)
select '', c.idcal
from calendar c
where idcal = 42
  cross join generate_series(1, c.numberoffields) as g(f)


SHOW GLOBAL VARIABLES LIKE 'binlog_format';

I believe you will find the server will tell you that it is configured for STATEMENT since there's not another good reason why you'd get this error.

The error message is referring to the server where you're trying to restore the files as currently configured, not the format the server may have been in when the log was created.

MySQL has two logging formats for replication, ROW, which logs the actual changes made to individual rows, and STATEMENT, which logs the queries that made the changes.  The MIXED format isn't a different format, but instead is a configuration setting that allows the server to select the format for each query based on internal rules.

When a server has binary logging enabled and executing queries from an incoming binlog, it has to write the queries to its own binlog, but it doesn't just copy the incoming log entry into the outgoing log... if the server is configured for ROW, it will always translate incoming STATEMENT events into ROW events.  If it is configured for MIXED, then it may log incoming STATEMENT events as outgoing STATEMENT events or it may log them as ROW... but if it is configured for STATEMENT then it is unable to log incoming ROW events, and the error you're seeing would be what is returned.

 -- server receives  -- server global   -- server writes
 -- incoming event   -- binlog_format   -- outgoing event

    STATEMENT           STATEMENT          STATEMENT
    STATEMENT           MIXED              STATEMENT or ROW
    STATEMENT           ROW                ROW
    ROW                 STATEMENT          XXX NOT SUPPORTED XXX
    ROW                 MIXED              ROW
    ROW                 ROW                ROW

If SHOW GLOBAL VARIABLES LIKE 'binlog_format'; really does say ROW I'll be surprised, but if not, then your fix should be this:

SET GLOBAL BINLOG_FORMAT = 'ROW';


Rather than trying to explain why most of the partitions seem empty, let me argue against that flavor of PARTITIONing.

To put it bluntly, PK & BY KEY(id, sha1) gains no performance, nor any other benefit that I can imagine.

Note that to get "partition pruning", you have to specify both the id and the sha_checksum.  Performance will be essentially the same as having a non-partitioned table with the same PRIMARY KEY.

Since the records are randomly scattered around, I can think of no use for "transportable tablespaces".

What version of MySQL are you using?

What character set are you using?  (I'm thinking of space considerations, especially for the CHAR column.)

Please describe id and the application; I can help you devise a better PARTITION scheme, or continue to argue against it.

Another thing to note:  More than about 50 partitions slows things down for a variety of reasons, one of which is that all partitions are opened when an INSERT occurs.  (Very new versions have 'fixed' this failure to 'prune'.)


Sync two postgres databases from two sperate locations with no network

I think a good way to do that is to have sequences with an increment of 2 instead of 1. That way your primary keys will be uneven on one site and even on the other one. Then you can merge without worrying.

create sequence seq_uneven increment by 2 start with 1;
create sequence seq_even increment by 2 start with 2;

And then you use seq_uneven on one site and seq_even on the other.


Best solution for cross-datacenter MySQL master-slave replication

There are three(3) suggestions I have that you may want to look into...
SUGGESTION #1
You could use MySQL 5.5 both locally and in AWS. You would also setup Semisync Replication. That way, every SQL statement executed on the Master has its latest recorded SQL statement sent over to the Slave.
In my past post (Jul 26, 2012 : Mysql database replication on different vlan/subnet/another site) I had recommended SemiSynchronous Replication over a geographic distance. Interestingly, @AaronBrown points out by experimentation that High Latency would degrade Semisynch Replication (See his blog on this one).
SUGGESTION #2
If all your data is InnoDB, you could use Percona XtraDB Cluster (preferable to have two Slaves in AWS but one would do). Why Percona XtraDB Cluster? You have synchronized writes. You only major bottleneck would strictly be network communication (again High Latency would have an adverse affect)
Here are my past posts on using Percona XtraDB Cluster

Aug 26, 2011 : MySQL Master/Slave Master/Master setup?
Jul 25, 2012 : Scaling Percona datacenters: setup and replication

SUGGESTION #3
If you want to strengthen each data center's local copy of the data, I would suggestion using DRBD, not over a geographic distance, but having a DRBD cluster in each data center. In your case, at least have DRBD in the local data center.
Here is my past post on this suggestion :

Mar 29, 2011 : MySQL high availability, failover and replication with Latency
Aug 29, 2011 : MySQL Replication : 1 Slave / Multiple Masters


How to split table into two tables in one transaction or query?

I cannot understand how to insert each row of one table into another
  table and update the same row of first table with returned id

You don't have to use an intermediate table. It can be done in one statement, with a writeable CTE:

WITH t as
 (INSERT INTO media(origin) SELECT DISTINCT avatar FROM profile RETURNING id,origin)
 UPDATE profile SET avatar_id=t.id FROM t WHERE avatar=t.origin;

(assuming that media.id is automatically set by its DEFAULT clause).

Or the same as two statements in a transaction, using a different form of update:

BEGIN;
INSERT INTO media(origin) SELECT DISTINCT avatar FROM profile;
UPDATE profile SET avatar_id=(SELECT id FROM media WHERE profile.avatar=media.origin);
COMMIT;

If you know that profile.avatar is unique, DISTINCT can be omitted, otherwise it's required.


federated tables? synchronization? replication? [mysql]

You are asking for pain by having the data split among machines is a haphazard way.

Consider Galera (PXC, etc) as a multi-master approach.  If one node goes down or is isolated by a network outage, it is self healing after things come back.

Yes there are multiple solutions (now) for multi-sourcing a slave.  MariaDB has had an offering for some time.  Oracle's MySQL has one.  Galera allows each node to be slave to some 'external' Master.


Table partitioning and updates / inserts

Yes it works. The right partitions are automatically populated when inserting new rows or updating existing records.


Once a record is created with a primary key, you really shouldn't be updating the primary key.  The point of a PK is that it uniquely identifies that row.  That's why you should be careful when creating a PK with a composite key (multiple fields that comprise the PK), because if those fields need to change, then you're changing your PK and any depending records need to change as well.

Now in a table that has a Foreign Key to another table, it's ok to update the FK if/when necessary.

So really, when the data needs to change, create the PK on an auto-increment field and let people modify the rest of the data, or delete the entire record when necessary.


Where does the logins users are stored in the SQL server “in which data base “

First please note logins and users are both different thing. You need a valid login for a user to make changes into database. User is anyone authorized to perform ceratin actions on database while login is credentials they use to Log into the SQL Server.

  In the SQL server 2012 how can I manage to back up my logins since the moved the database form one machine to another

There are two authentications

Windows authetication

The credentials for which are not stored in SQl Server database and managed by windows/AD. There would be entry for windows autheticated logins in master database with respective SID but password would be with Active directory.

SQL Server authetication.

For 2nd we have password stored in hash format you can see it from sys.sql_logins. The information about SQl server logins are stored in master database and each login has SID repective to it. Only SA login has same SID no matter what server it is. That is why when you move database by backup restore mechanism  users are moved not logins and you finally have to create logins(if already not there) and map it to users. This is generally called as troubleshooting orpahned users

You can use sys.syslogins and sys.server_principal to gain more information about logins. Note that sys.syslogins is a legacy view.

If you also want to move your logins you have to take backup of master database and restore it on destination server. And this would be possible only if you have same version and SP on destination machine.


Targeting MSX with job on Multi-Server Administration

Based on my understanding, the master server (MSX) cannot be a target server (TSX) that receives jobs from another master server i.e You cannot list one MSX server into another MSX, you have to defect it and make sure it is not enlisted with any other master server.
Its a simple two-level hierarchy: a server is a master server, a target server, or neither. Cannot be both at the same time.
FROM BOL :

When creating a multiserver environment, consider the following:

Each target server reports to only one master server. You must defect a target server from one master before you can enlist it into a different one.
The master and target servers must be running on the Windows NT 4.0 or Windows 2000 operating system.
When changing the name of a target server, you must defect it before changing the name and reenlist it after the change.
If you want to dismantle a multiserver configuration, you must defect all the target servers from the master server.

You may also want to read up : SQL Server Multiserver Administration: Master and Target Servers


Exporting table from MySQL 5.7 to MySQL 5.6 (Error)

It seems that the id column is varchar(255) so from your config file enable the innodb_large_prefix on this instance of MySQL to resolve the problem but per the below, this explains why this is occurring from my interpretation.

You can run show variables and see if the innodb_large_prefix if ON or OFF.

Maximums and Minimums

  By default, the index key prefix length limit is 767 bytes. See
  Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit
  this limit with a column prefix index of more than 255 characters on a
  TEXT or VARCHAR column, assuming a utf8mb3 character set and the
  maximum of 3 bytes for each character. When the innodb_large_prefix
  configuration option is enabled, the index key prefix length limit is
  raised to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED
  row format.

  Attempting to use an index key prefix length that exceeds the limit
  returns an error. To avoid such errors in replication configurations,
  avoid enabling innodb_large_prefix on the master if it cannot also be
  enabled on slaves.

  The limits that apply to index key prefixes also apply to full-column
  index keys.


Regarding running reports from an Availability Group secondary server, you will need to license that instance of SQL Server.  Since the AG requirements require identical equipment, your secondary's licensing cost will reflect the primary server's licensing cost

Using the secondary server strictly for High Availability/Disaster Recovery does not require licensing the secondary server since it is only in use when the primary fails.

Regarding the brief spikes in CPU utilization, is it causing you a problem now?  Since it is hard for you to see who is responsible, consider loading sp_whoisactive from Adam Machanic's site: http://whoisactive.com  This stored procedure has numerous options to control what information you can query.

Brent Ozar also has an article on using sp_whoisactive to track issues within your system, logging the output to a file for later review.  http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

Perhaps these tools will help you find out what is happening.


Any Tricks to Better Identify Disabled SSIS Elements?

You could always look at the underlying XML:

  DTS:Disabled="True"


Postgres cannot be restarted after disk ran out of space

Maybe just try to rename the file?
If it's empty anyway, there should not be any loss of data.


create column name from other table rows mySQL

Using same stored procedure from previous answer, you can generate a new columns names, casting ans_question_xx with the name of each question.

create procedure getStudentAnswers(StudentId int)
begin

    set @cols := (select group_concat('ans_question_',
                                       cast(question_id as char(20)),
                                       ' as ',
                                       question separator ', ')
                  from questions);

    set @sql = concat('select student_id,  ',
                      @cols,
                      ' from   answers
                        where student_id = ', cast(StudentId as char(20)),
                      ' order by student_id;');

    PREPARE dynamic_statement FROM @sql;
    EXECUTE dynamic_statement;
    set @sql := null;
    set @cols := null;
    DEALLOCATE PREPARE dynamic_statement;

end \\

call getStudentAnswers(11);

call getStudentAnswers(12);

| student_id | Gender | Area_code | Another_question |
|------------|--------|-----------|------------------|
| 11         | Male   | 23        |                  |

| student_id | Gender | Area_code | Another_question |
|------------|--------|-----------|------------------|
| 12         | Female | 21        | null             |

Rextester here


SSAS Tabular Model or SQL Relational Database for new Datawarehouse? Is SSAS still a good to learn technology?

In my opinion, yes - it is still a technology that is completely valid. Even more so if we are discussing Tabular rather than Multi-Dimensional SSAS implementations. For example, VertiPaq (the database engine that runs Tabular Models) is the same engine that can be found in PowerBI and any skills you learn using SSAS/TOM/DAX will help you if you ever decide to learn that as well. Its also good to note that PowerBI, Excel, SSRS and many other tools can connect to SSAS/Tabular to obtain data to build dashboards and reports.

I personally have been using SSAS/Tabular since early 2014 and I have already rolled out 3 large-scale implementations (I'm currently working on my 4th). My current company uses SSAS/Tabular as the Analytics Engine for its core product. It can be integrated with many other products other than just SQL Server by using PowerQuery to obtain data from other sources.

If all you were planning to do was to have a data warehouse with a star schema (or even snowflake schema) then Tabular is a good choice as it offers fantastic performance (hardware dependent) and amazing compression. You should be aware though that it is a pure in-memory analytics engine, so if you choose to go down that route, be prepared to procure lots of RAM. To estimate how much RAM you will need, a typical Tabular installation will be 1/10th the size of the originating data (subject to good modelling principles).

I have a Tabular Model which contains circa 800 million rows of data in the main fact table and around 16 million rows in each of the dimension tables. It takes up about 48GB of RAM, but queries against it are sub-second when applying a filter and less that 30 seconds when just dumping the entire table. Unlike SQL Server, partitioning in a Tabular Model does not speed up the query as the data is not on disk, but it does speed up your ETL process as you can load individual partitions.

With all that being said, I still have a SQL Server data warehouse which stores data that I don't want in my Tabular Model. As with any technology, choose the right tool for the job. If you want something that can give you high performance analytics Tabular is a great choice, but does require you to learn a few more technologies (C# & PowerShell are a must in my opinion as they help with deployment, management and builds). However, if you don't have the budget, resources or need for the features that a Tabular installation offers, then a traditional data warehouse or a multi-dimensional SSAS installation may be the better way to go.


Assuming we are talking about tabular models in SSAS 2012 or 2014 (since 2016 has a new capability around bi-directional crossfiltering) crossfiltering only flows over relationships in one direction. So filtering Status will crossfilter Part, but not Item or Manufacturer. Filtering Manufacturer will crossfilter Item and Part but not Status.

Any calculation you embed in a Tabular model must be written in DAX. So if you want to filter on Status and have it impact the ItemStock measure you need to code that:

ItemStock :=
IF(
  ISCROSSFILTERED('Status'[Id]),
  CALCULATE(
    SUM( 'Item'[Stock] ),
    SUMMARIZE(
        'Part',
        'Item'[Id]
    )
  ),
  SUM( 'Item'[Stock] )
)

More theory on many-to-many formulas in DAX:

Optimize Many-to-Many Calculation in DAX with SUMMARIZE and Cross Table Filtering by Marco Russo
Whitepaper: The Many-to-Many Revolution 2.0 by Marco Russo and Alberto Ferrari


Is there a way to find the least recently used tables in a schema?

Given the following:

Database mydb
datadir is /var/lib/mysql

Database with all MyISAM Tables

SELECT * FROM
(
    SELECT
        table_name,
        IFNULL(update_time,create_time) LastTimeTouched
    FROM
        information_schema.tables
    WHERE
        table_schema = 'mydb'
) A
ORDER BY LastTimeTouched LIMIT 20;

Database with all or some InnoDB Tables

This is impossible to do with innodb_file_per_table disabled because all the files reside in a single system tablespace (ibdata1).

Besides data and index pages, there are other things written in the system tablespace:

Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of the system tablespace ibdata1

With innodb_file_per_table disabled, there is no way to tell. As far as I know, the InnoDB Storage Engine does not update the UPDATE_TIME column at all.

With innodb_file_per_table enabled, once an InnoDB table exists outside of ibdata1, you can only check the timestamp of the .ibd from the OS.

NEEDED SCRIPT

The following is a script that can tell you the LastUpdated Time for MyISAM and InnoDB tables together in one Database (provided you are using innodb_file_per_table)

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
FILEDAT=/tmp/GetFileTimestamps.txt
FILESQL=/tmp/GetFileTimestamps.sql
DATABASE_TO_SEARCH=${1}
cd /var/lib/mysql/${DATABASE_TO_SEARCH}
ls -l --time-style=+%s *.[iM][bY][dDI] | awk '{print $7,$6}' > ${FILEDAT}
NUM=1
SELECT_TAG="SELECT"
HEADER1=" Filename"
HEADER2=" LastUpdated"
echo "SELECT * FROM (" > ${FILESQL}
echo "SELECT TableName,MAX(LastUpdated) LastUpdated" >> ${FILESQL}
echo "FROM (SELECT REPLACE(REPLACE(REPLACE(FileName,'.ibd','')" >> ${FILESQL}
echo ",'.MYD',''),'.MYI','') " >> ${FILESQL}
echo "TableName,LastUpdated FROM (" >> ${FILESQL}
for TAG in `cat ${FILEDAT}`
do
    (( NUM = 1 - NUM ))
    if [ ${NUM} -eq 0 ] ; then FILENAME=${TAG} ; fi
    if [ ${NUM} -eq 1 ]
    then
        UNIXTIME=${TAG}
        SQLSTMT="${SELECT_TAG} '${FILENAME}'${HEADER1},"
        SQLSTMT="${SQLSTMT} FROM_UNIXTIME(${UNIXTIME})${HEADER2}"
        echo ${SQLSTMT} >> ${FILESQL}
        SELECT_TAG="UNION SELECT"
        HEADER1=""
        HEADER2=""
    fi
done
echo ") AAA) AA GROUP BY TableName) A ORDER BY LastUpdated" >> ${FILESQL}
mysql ${MYSQL_CONN} --table < ${FILESQL}

This script is designed to check the timestamp of every table. The only parameter the table needs is the database.

For MyISAM, it uses the more recent timestamp of one of the following:

.MYD file
.MYI file

For InnoDB, it uses the .ibd timestamp


Impact of changing the DB compatibility level for a Published replicated DB from 90 to 100

You can follow below steps :

Make sure that the Log Reader Agent is running for the database. By default, the agent runs continuously.
Stop user activity on published tables.
Allow time for the Log Reader Agent to copy transactions to the distribution database, and then stop the agent.
Execute sp_replcmds to verify that all transactions have been processed. The result set from this procedure should be empty.
Execute sp_replflush to close the connection from sp_replcmds.
Change the compatibility level of the database.
Start the Log Reader Agent.


Risks regarding simple recovery model and importance of log file backup

When we take SQL database backup with Simple recovery mode and restore it on a test server, is it correct that we have a blank log file on the test server but the latest database file?

Yes, that's normal.  When using the Simple recovery model, the transaction log isn't used for recovering the database (for instance, during a restore).  It's essentially just a performance optimization.  Data changes are written efficiently to the log first, and modified data pages are written back to the data files periodically during a process called a CHECKPOINT.

In our current backup scenario, what is the benefit of Full recovery mode as we already have replication every 30 minutes and our RPO is agreed as 1 hour for every client?

If that meets your RPO needs, then log backups are really unnecessary for your situation.
However, it's important to make sure that these every-30-minute, full server backups are "SQL Server aware."  See Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment

SQL Server supports virtualization-aware backup solutions that use VSS (volume snapshots). For example, SQL Server supports Hyper-V backup.
Virtual machine snapshots that do not use VSS volume snapshots are not supported by SQL Server. Any snapshot technology that does a behind-the-scenes save of a VMs point-in-time memory, disk, and device state without interacting with applications on the guest using VSS may leave SQL Server in an inconsistent state.

They should be freezing I/O, otherwise the backed up database files could be in an inconsistent (corrupted) state.  Verify this with your vendor or their documentation.  A SQL Server backup triggers the CHECKPOINT process mentioned above, which means all data that's been modified in memory gets written back to disk before the backup.
One advantage of log backups is that you can restore to a specific point in time.  This can help when recovering from "oops" DBA actions (deleting or updating data accidentally - for instance, by forgetting a WHERE clause).

Can anyone highlight if I need to improve on this point?

The fact that you're thinking about RPO at all probably puts you ahead of a lot of folks in your situation   Double check that the VM backups are compatible with SQL Server, but otherwise it sounds like you're on the right track!


Add column with a sum total to crosstab() query in PostgreSQL 9.0

Proper crosstab query
First of all, the presented query would not work while you still have char(n) in your table definition - like we discussed under your previous question. You would need to convert to text or varchar first.
This works with your current table definition:
SELECT * FROM crosstab(
   'SELECT postcode_nospace_::text, ageband::text, count(ageband) AS ct
    FROM   adult_social_care.activities_in_localities_asc
    GROUP  BY 1, 2
    ORDER  BY 1'

   ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64')
           , ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER')$$
   )
AS t("postcode" text
   , "18-24" bigint, "25-34" bigint,"35-44" bigint, "45-54" bigint, "55-64" bigint
   , "65-74" bigint, "75-84" bigint, "85-94" bigint, "95 AND OVER" bigint);
I am also using bigint instead of numeric to save another unnecessary type conversion (count() returns bigint).
Detailed explanation:

PostgreSQL Crosstab Query

Better table definition
But better ALTER the data type in your table to text like @dezso suggested. Or, if you want to keep the length limit, add a CHECK constraint or use varchar(n). Never use char(n).
ALTER TABLE activities_in_localities_asc
   ALTER COLUMN ageband TYPE varchar(12)
 , ALTER COLUMN postcode_nospace_ TYPE varchar(8);

 ALTER TABLE gazetteers_and_addresses.unit_postcode
   ALTER COLUMN pc_area TYPE varchar(10);

Then you don't need to cast original column values any more:
SELECT * FROM crosstab(
   'SELECT postcode_nospace_, ageband, count(ageband)
    ...

Ideally, though, ageband should be an enum or (my preference) an ID referencing a lookup table, not plain, error-prone text ... key word: normalization.
Add total count
Building on your table, but with text or varchar() columns.
Wrap your original query into a CTE and call the the result twice in a UNION query - the 2nd call adds totals per postcode:
SELECT * FROM crosstab(
  $$WITH cte AS (
      SELECT postcode_nospace_, ageband, count(ageband) AS ct
      FROM   adult_social_care.activities_in_localities_asc
      GROUP  BY 1, 2
      )
   TABLE  cte  -- original results
   UNION ALL   -- add total per postcode
   SELECT postcode_nospace_, 'total' AS ageband, sum(ct) AS ct
   FROM   cte
   GROUP  BY 1
   ORDER  BY 1$$  -- dollar-quotes to include single quotes easily

   ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64')
           , ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER'), ('total')$$
   )
AS t("postcode" text
   , "18-24" bigint, "25-34" bigint,"35-44" bigint, "45-54" bigint, "55-64" bigint
   , "65-74" bigint, "75-84" bigint, "85-94" bigint, "95 AND OVER" bigint, "total" bigint);
About the TABLE command:

Is there a shortcut for SELECT * FROM in psql?

Related CTE examples:

Combine results of two queries, where the second relies on the first
Get n grouped categories and sum others into one


How to connect to remote MySQL from a specific IP?

Let me answer in more formatted form:

MySQL server do not manage client ip on server, and not manage outgoing on client. Server could select IP address for listen only.

Server just compare client IP with permission tables and allow or reject access.
I do not know method how to tell mysql client (including library to use dedicated outgoing ip).

what You can do:

use wildcards, like - user@192.168.12.%
use domain names, like - user@serverB.yourdomain.com

Wildcard make list of IP addresses wider than necessary, but at the same time using DNS resolving:

Slowdown performance under high loading
Could completely crash work with may network related issues

You can combine wildcard + firewall rules for reduce number of allowed IP.


how determine the best path that satisfies a condition

Congratulations, you have a subset sum problem! It's an NP-complete problem. Arriving at the solution is going to call for brute force. i.e. Generate and test all possible subsets. WARNING: This may involve the sketchy Cartesian products your mother warned you about.

Depending on the size of your actual data domain, in SQL that is going to range from "computationally expensive but it works" at best to "who jammed the kernel to 100% and crashed the database server?" at worst.

As a computationally-intensive problem, it is more advisable to solve it in a dynamic programming language than in a SQL DBMS.


Removing Database from sharded cluster in Mongo DB

Unfortunately there is some stuff that will not be cleaned up through dropDatabase. On the other hand if you delete it by hand may not will be harmful. Some data like statistics will reside on your nodes anyway till the restart of mongods. (Try it out first! Not on the production), as the configuration database is relatively small and does not have huge requirements in terms of performance, that maybe even not a problem, just to be on the safe side and leave them as they are.

Also something to check out how to make the manual actions:
https://stackoverflow.com/questions/9407838/mongodb-dropdatabase-not-working

and as it is mentioned on this question the dropDatabase in such case a bit bugy:https://jira.mongodb.org/browse/SERVER-4804


Postgres: Are secondary indexes included in ACID?

Are non-unique indexes / indices covered under the Consistency clause in aCid?

Yes. Any violation of that would be considered a bug in PostgreSQL.

The docs you quoted are cases where postgres might have to temporarily scan the heap instead of doing an index-only scan or otherwise do extra work to get a consistent result.

For example, both BRIN and GIN indexes accumulate batches of pending changes and then do batch updates. When the indexes are used in queries this queue is also scanned to make sure that a current, up-to-date and consistent view is seen.

If an index is currently invalid it'll be skipped by the planner and won't be used by queries.

Redshift isn't really PostgreSQL, it just happens to share the same front-end and protocol. Drawing comparisons based on Redshift will typically just create confusion.


Are many NULL columns harmful in mysql InnoDB?

The only harm I could see is doing COUNT queries against large tables.

Doing SELECT COUNT(*) FROM mytable on an InnoDB table should produce a full table scan. However, think of what COUNT() actually does. The * represents a whole row. COUNT() can determine if non-NULL columns are present. The easiest way for that to happen it to make sure PRIMARY KEY columns are the first columns in ordinal position. PRIMARY KEY columns are always NOT NULL by definition. Thus, SELECT COUNT(*) FROM mytable would be just as fast as SELECT COUNT(1) FROM mytable.

I would not be worried about excessive columns as InnoDB places a cap on the number at 1000. Of course, IMHO having 20-30 columns in a table (regardless of Storage Engine) is too high, which indicates either poor design (begging for normalization) or column data that is just too big.

PostgreSQL kind of solves that be having TOAST tables. TOAST stands for The Outside Attribute Storage Technique. That manages column data that is way too big for normal row storage.

InnoDB has no mechanism like TOAST, so I expect row chaining of some sort in the .ibd files or within ibdata1. Notwithstanding, NULL columns will prevent the physical manifestation of oversized row data. Anyone could live with that.

As long as InnoDB tables are properly indexed, NULL columns would not be an issue. Besides, all non-unique indexes have internal rowids back to the Clustered Index (aka gen_clust_index). Therefore, well-tuned queries will always be accessing data via the Clustered Index.


Querying system health data becomes slow as event volume increases

You should check out This is how to shred Extended Event XML faster.

The gist is to

Put the XML into a variable.
Shred the XML rows into a temp table.

Also, see below answer by Martin Smith

Optimising plans with XML readers


What would be a good name for a job schedule?

It seems to me that using a job name in the schedule name prevents it from being re-used, so I've chosen to focus on a shorthand for the schedule contents. I'm now using the following naming convention:

times per week - days of the week - run time

So for the examples in the question the new names would become:

Run every night -> 7xPW - MTWTFSS - 22.00
Every 4 weeks on Monday -> 0xPW - Mxxxxxx - 6.15
21.00hr -> 5xPW - MTxTFSx - 21.00
DAILY (which then runs only 6 days a week) -> 6xPW - MTWTFSx - 0.00

This seems to work for now because we have lots of weekly and once-a-day jobs. I'm not utterly happy with the 0xPW for schedules that occur every 4 weeks or monthly (what are we gonna do with the job that runs every 4th day of every month?), but it's not a big deal and the 0xPW at least tells us to look to the description for more info.

The daily jobs that run every xx minutes are not straightforward to name in the above manner - although 2016xPW - MTWTFSS for a once-every-5-minutes schedule is pretty telling.


Job completes successfully and logs the errors to a text file

Create a notification for the Job failure. The below email will help you with the steps:

Step 1 : Configure database mail and do a test if thats working fine.

Step 2 : Change Agent properties
         Under Mail session, select the option to Enable mail profile. Ensure the correct Mail profile is selected.

Step 3 : Create Operator.
         Under the SQL Server Agent, right-click Operators and select New Operator and configure accordingly.

Step 4 : Under the job properties for the backup, go to Notification tab and select email when the job fails:

Step 4 :

Notification for job failure


What are the privileges required to execute a trigger function in PostgreSQL 8.4?

Trigger functions behave just like other functions as far as privileges are concerned. With a minor exception:

  To create a trigger on a table, the user must have the TRIGGER
  privilege on the table. The user must also have EXECUTE privilege on the trigger function.

UPDATE
After feedback in the comments I did some research. There is an open TODO item in the Postgres Wiki:

  Tighten trigger permission checks

Linked to this thread on Postgres hackers. Currently, EXECUTE privileges on a trigger function are only checked at trigger create time, but not at runtime. So revoking EXECUTE on the trigger function has no effect on a trigger once created. Your observation seems to be correct.

This does not grant any additional privileges to manipulate objects. If the calling role lacks privileges needed to execute (parts of) the function body, the usual exception is raised. To pave the way, you could make a privileged user OWNER of the function and use the

SECURITY DEFINER

clause, as documented in the manual here. It causes the function to be run with the permissions of the owner instead of the invoker (default).

If the owner is a superuser, you need to be extra careful who you grant the EXECUTE privilege and what the function can do to avoid abuse. You may want to

REVOKE ALL ON FUNCTION foo() FROM public;

to begin with and use SET search_path for the function.
Be sure to read the chapter on Writing SECURITY DEFINER Functions Safely.

Find a code example in this related answer on SO.


You don't need to use Powershell for this, which would require xp_cmdshell in a stored procedure. Just use xp_logininfo. As long as you add the group to SQL Server (you don't have to give it any permissions), you can query the members of it.

EXEC master.dbo.xp_logininfo 'Builtin\Administrators', 'members'


SQL Insert Into New Table Or Else Insert Overwrite Into Existing Table

Since you are not going to specify the column names you are going to have to go with SELECT * it is not ideal, but works if the tables are going to be new.

You could do it with the query below, but you will have to change your grouping columns each time. Unless you can simply use DISTINCT.

If your tables are big, You should look into splitting it in batches.

More info on batches

Test TableA

CREATE TABLE  dbo.tbl_A(ID INT, Val varchar(255));

INSERT INTO  dbo.tbl_A(id,val)
VALUES(1,'bla'),(1,'bla'),(2,'bla'),(2,'ugh'),(3,'bla');

Query with group by

IF (EXISTS (SELECT *
                 FROM INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_SCHEMA = 'dbo'
                 AND  TABLE_NAME = 'tbl_B'))
BEGIN
    TRUNCATE TABLE dbo.tbl_B
    INSERT INTO  dbo.tbl_B
    SELECT *
    FROM dbo.tbl_A
    GROUP BY ID,VAL;
END
ELSE
BEGIN
    SELECT *
    INTO dbo.tbl_B
    FROM dbo.tbl_A
    GROUP BY ID,VAL;
END

Query with distinct

  IF (EXISTS (SELECT *
                     FROM INFORMATION_SCHEMA.TABLES
                     WHERE TABLE_SCHEMA = 'dbo'
                     AND  TABLE_NAME = 'tbl_B'))
    BEGIN
        TRUNCATE TABLE dbo.tbl_B
        INSERT INTO  dbo.tbl_B
        SELECT DISTINCT *
        FROM dbo.tbl_A;
    END
    ELSE
    BEGIN
        SELECT DISTINCT *
        INTO dbo.tbl_B
        FROM dbo.tbl_A;
    END

DB<>Fiddle


View with parameters in Sybase SQL Anywhere

Solution is to create procedure with use of dynamic query:

CREATE PROCEDURE view_with_parms( in str1 varchar(255), in str2 varchar(50) )
RESULT(
  myID integer,
  someName varchar(255))
BEGIN
    EXECUTE ( 'select myID, someName from ' || str1 || ' order by ' || str2 || ' asc' );
END

then:

select * from view_with_parms('test_table','myID');

Tested with:

SQL Anywhere 9
SQL Anywhere 12


What is the significance of root and page level fragmentation in a clustered b-tree index?

Fragmentation in non-leaf levels is typically not an issue due to the difference in the number of pages between the leaf and non-leaf levels.  For example, here are the number of pages per level in a fairly ordinary 8 million row table with an int primary key:

index_level page_count
0           156623
1           653
2           5
3           1

The 659 non-leaf pages have a very good chance to remain in buffer pool because any seek into the table starts at the root and walks down the tree.  Even if they did need to be pulled from disk and it was completely random 8k I/O, they still make up less than one half of one percent of the table.


what you can do, is to write two stored procedures with the following logic:

Build a stored procedure which does the case thing. This may be optional but the resulting sql would look better and easier to debug/develop.
Define a stored procedure which has as input the tablename
Read out the columns of the table - maybe here can be done some grouping/distinct which columns fall into the case CONFIDENTIALITY_IND
Build up the sql statement dynamically by iterating over the result of 3. When the column is your special case column - apply the stored procedure of 1. Otherwise select it. How to build up dynamic sql look here: http://www.dba-oracle.com/t_oracle_execute_immediate.htm - execute immediate is your friend here.

Hope I understood your problem correct. This would be my way


Failure during 2 backup jobs for one database

There are two possibilities that come to mind:
 1. The full backup and the transaction log backup are trying to use the same file.

  'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorksLT_from_Azure.bak'

Something else is locking the file i.e. tape backup
I have had tape backups cause this problem for me. I had to wait until the tape backup finished then my SQL Agent backup job would work.


Very slow queries selecting @@ session variables.

Sometimes things like this happen because of other things going on at the same time.  Can you spot such in the slowlog?
The probably will probably be solved in version 8.0, when a number of things get turned into real InnoDB tables.
Some 3rd party products issue an awful number of seemingly spurious and redundant information queries.  Let's see the output from SHOW GLOBAL STATUS LIKE 'Com%';
Set long_query_time = 0.1 and see if you catch a lot more of these queries, plus other things.


You can drop export/import job using the following command
First of all find the name of the job table under 'DataPump>Import Jobs or Export Jobs'.

Use the drop command to drop that table by the user who has created that import/export job. In my case it was created by SYSTEM user.

SQL>conn system/password
SQL>DROP TABLE EXPORT_JOB_SQLDEV_41;
Table dropped.


Implementing a degenerate dimension with an alphanumeric varchar(30) at the transaction level?

Kimball's guidance touching on performance and space use needs to be taken with a grain of salt when using modern, columnar storage. And for SQL Server data warehouses, fact tables should typically be stored as Clustered Columnstores. Low-cardinality attributes in columnar storage are much cheaper than in uncompressed row stores.

And wide and high-cardinality attributes in columnstores do have a large impact on storage size, but not on query performance, as columns are only fetched and scanned when needed.  You don't have to fetch, cache, and read the whole row.


No, installing ProxySQL is not mandatory
Do you mean all three nodes close down suddenly rather than close down gracefully? What version of Percona XtraDB Cluster are you using? If it is a version 5.6.19 or later, then the nodes will read the last state on startup and will try to restore primary component once all the members again start to see each other. This makes the PXC cluster to automatically recover from being powered down without any manual intervention. This information came from https://www.percona.com/blog/2014/09/01/galera-replication-how-to-recover-a-pxc-cluster/ and you may find other useful blogs around this subject and/or webinars on the Percona site.

Disclosure: I am from Percona


Does MariaDB-Galera Cluster support LAST_INSERT_ID and mysql_info?

I know this is a really old question, but yet both are supported just as they are in standard mysql.


Looks like you are confusing the JDBC trace and the CLI trace. To enable the JDBC trace in the JCC driver, which is the only one available in DB2 10.5, you need to set the JDBC connection properties as described in the manual, for example, via the JDBC URL:

String url = "jdbc:db2://my.host.com:50000/mydb" +
             ":traceFile=/tmp/jcc.trc;" +
             "traceLevel=" + com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRDA_FLOWS + ";";

Various trace levels are described here.

Only the Type 2 JDBC driver will read db2cli.ini, so setting CLI properties has no effect if you are using Type 4 connectivity. Type 2 connectivity is deprecated an is not recommended for use.


I found the problem and fixed it.
I grant usage on table to role/user

grant usage on schema schema_name to role/user;

Thank you for all input.


Auto vacuuming high-write, high-update, and mostly read table types

That question is too broad, but here are some pointers about configuring autovacuum:

For tables that receive bulk inserts, run an explicit VACUUM afterwards or use PostgreSQL v13.

For tables with many updates, a fillfactor of 70 to 90 (depending on row size) is a good thing if no updated columns are indexed. 10 or 20 is just a crazy waste of space.

There is no need to care about autovacuum for tables that are only read.

With mass deletes, there is nothing much you can do. If you can use partitioning, that pain may vanish completely.

Of course low fillfactor will have a negative impact on query performance; that is the price you are paying for more efficient data modifications:

For sequential scans, the impact is clear: you have to read all that empty space too.

For index scans that read only a single row, there will be no performance impact.

Index scans that read several rows are somewhere in the middle, since they will have to read more blocks if fillfactor is low, because the rows will be spread across more blocks.

Don't forget the impact on caching efficiency: If your blocks consist mostly of air, the RAM used for caching will also mostly contain dead space.


How to benchmark Switchable Optimizations in MySQL

(I realize that this does not "answer" the Question.  But it is too long for a Comment.)
Good luck.  re "Almost identical" -- Probably because they were "identical" except for random noise in the timing.  The switchable optimizations are, in general, "binary" and "optional".
Consider an obvious situation:  You have an index that could be used in a query.  Still, the Optimizer will deliberately consider whether to ignore the index and simply scan the table.  It may be more costly to bounce between the index and the table, hence using the index would slow down execution.  This is a "binary" decision -- use vs ignore index.  The Optimizer cannot know the exact cutoff between the choices.  It is influenced by HDD vs SDD, buffer_pool_size, other activity, statistics, etc, etc.
That is, a benchmark would be complicated by having more than 2 choices (an index exists or not):

FORCE INDEX vs IGNORE INDEX vs 'allowed' the Optimizer to choose
for "allowed" the Optimizer picks the better choice vs fails to

Any of these could change whether the Optimizer would pick a particular optimization.  Also, the timings would change by a relatively unpredictable amount in cases such as these:

Adding more rows to a table
Changing the size of a 'range'
ANALYZE TABLE will sometimes lead to changing things significantly.
OPTIMIZE TABLE -- ditto.

Over the years, I occasionally see "index merge intersect".  (INDEX(a), INDEX(b) with WHERE a=1 AND b=2)  In every case, a composite index would be better (INDEX(a,b)).
Change that AND to OR and no index is useful -- except when "index merge union" is used.  This almost never happens.  It takes two index range scans, a merge, and then something like a "join".
EXPLAIN does not show much info.  EXPLAIN FORMAT=JSON is better.  The Optimizer Trace shows a different set of info.
I don't know of a test set like what you seek.  I see the writing of even one test to be quite a challenge (see above).  Furthermore, interpreting the results would be challenging.
If you do succeed with your goal, please blog about it.


Query not returning result if the join fails (how to get it to return zero)

Before going into the main problem, there are a few more issues with the query:

the use of string literals ('1907' '1')  for values that are compared with columns that seem to have integer type (SiteID, UtilityID). If the columns are integers, use integers, not strings:

WHERE ... t.SiteID = 1907 AND t.UtilityID = 1

using BETWEEN and LAST_DAY() for datetime/timestamp comparisons. This - assuming that the w.Timestamp column is indeed a TIMESTAMP will give you incorrect results, unless all your timestamps have 00:00:00 time part. The LAST_DAY('2016-01-01') will be '2016-01-31 00:00:00' and you lose the whole last day of the month, except the very first second.
If you change that to BETWEEN '2016-06-01' AND '2016-07-01 is somewhat better but still wrong as you you'll get a few results from the next day (in July)!
One way that works correctly and with all datatypes (DATE, DATETIME, TIMESTAMP) is to use inclusive-exclusive range checks, with >= and <:

WHERE ... w.TimeStamp >= '2016-06-01' AND w.TimeStamp < '2016-07-01'

If the t.Timestamp column is of DATE type, then ok, BETWEEN can be used although I prefer the consistency of the above suggested code.
using old ANSI syntax without JOIN. This is not an error but it's harder for debugging when there are many tables and error-prone (we might forget a joining clause):

FROM Daily w, Transmitter t
WHERE ... AND w.TransmitterID = t.TransmitterID AND ...

It's better (in my opinion) to use the new (since 1992 ;) explicit JOIN syntax. It's also easier to change an INNER join to a LEFT or RIGHT outer join:

FROM Daily w JOIN Transmitter t
     ON w.TransmitterID = t.TransmitterID
WHERE ... AND w.TransmitterID = t.TransmitterID AND ...

Now for the main problem, the solution is to start from the Trasmitter table and then LEFT JOIN the details (Daily) table:

SELECT
    t.TransmitterID,
    SUM(w.amount)                        AS MonthlyTotal,
    COUNT(w.TransmitterID)               AS Days,
    DATEDIFF('2016-07-01', '2016-06-01') AS DaysInMonth
FROM Transmitter t LEFT JOIN Daily w
               ON  w.TransmitterID = t.TransmitterID
               AND w.TimeStamp >= '2016-06-01'
               AND w.TimeStamp  < '2016-07-01'
WHERE t.SiteID = 1907
  AND t.UtilityID = 1
  AND NOT t.deleted
GROUP BY t.TransmitterID ;


PostgreSQL: How to list all stored functions that access specific table

The body of a function is just stored as string. There is no list of referenced objects. (That's different from views, for instance, where actual links to referenced tables are saved.)

This query for Postgres 10 or older uses the system catalog information function pg_get_functiondef() to reconstruct the CREATE FUNCTION script for relevant functions and searches for the table name with a case-insensitive regular expression:

SELECT n.nspname AS schema_name
     , p.proname AS function_name
     , pg_get_function_arguments(p.oid) AS args
     , pg_get_functiondef(p.oid) AS func_def
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  NOT p.proisagg
AND    n.nspname NOT LIKE 'pg_%'
AND    n.nspname <> 'information_schema'
AND    pg_get_functiondef(p.oid) ~* '\mbig\M';

It should do the job, but it's obviously not bullet-proof. It can fail for dynamic SQL where the table name is generated dynamically and it can return any number of false positives - especially if the table name is a common word.

Aggregate functions and all functions from system schemas are excluded.

\m and \M mark the beginning and end of a word in the regular expression.

The system catalog pg_proc changed in Postgres 11. proisagg was replaced by prokind, true stored procedures were added. You need to adapt. Related:

How to drop all of my functions in PostgreSQL?


Does PostgreSQL benefit from more cores or faster cores for READ heavy applications?

Memory related settings

You've already addressed the key bottleneck for read heavy applications, that is, having sufficient RAM for caching.  Just make sure you've set appropriately high values for shared_bufferes, work_mem, maintenance_work_mem, and effective_cache_size within your postgresql.conf file.

Actually, there's a litany of good info in this DBA.SE thread.

Also, here's a link to the Postgres Docs about these settings, amongst others.

Processor choice

As for choice of processors, it is important to keep in mind that Postgres is not capable of using more than one CPU per database process.  If there will be many concurrently connected users, a multi-core CPU can spread out the user queries amongst the cores, but cannot parallelize on a per-query basis. Effectively, you will only get to truly leverage a benefit from multiple cores when there are multiple connected users.

I guess, to answer succinctly, that if you have only one (or a small number) of users, favor faster cores, whereas if you'll have many concurrent users, you could benefit from a little of both.

Refer to this DBA.SE thread which covers some of this info.

If you are going to have many concurrently users, I would suggest you also look in to connection pooling.


Programmatic way to rewrite a query with the view definitions as sub query?

I've never heard of or seen anything of this ilk.
You're most likely better served rethinking how you utilize views and refactoring them in a way that isn't an impediment to development, instead of building a tool to do this.
Several layers of nested views are (to me) an anti-pattern and make it harder for developers and SQL Server alike. They're also very likely to hide bad code and obscure issues.
Also see:

The Seven Sins Against TSQL: Indulging in Nested Views


How to get more information from dm_exec_query_stats

First, do your have columns of data type TEXT or NTEXT in your database? If

select
c.TABLE_NAME,c.COLUMN_NAME
from INFORMATION_SCHEMA.columns c,
INFORMATION_SCHEMA.tables t
where c.DATA_TYPE in ('ntext','text')
and t.table_name=c.table_name
and t.table_type='BASE TABLE'

returns any rows, you probably lost a lot of data during this attack, as the code truncates the existing values to 8000 characters. In that case you need to restore from a backup.

To answer your question, there is no direct way to get to that information from SQL Server. However, using the T-SQL code that was originally intended to be executed (the code that the above code was injected into), you should be able to identify the place in your app where the injection happened. From there you can look through the web site logs to find submissions to that particular page or form that took a long time or that submitted an unusual amount of characters. You can use the columns creation_time and last_execution_time in sys.dm_exec_query_stats to narrow down the time of the attack(s).

Important, once you have the place identified that allowed for the injection to happen, you need to close that gap immediately. You should also review the rest of the application to make sure that no other vulnerable spots lurk in your code. There is a little more information about what to look out for when injection-proofing your app here: http://sqlity.net/en/2547/sql-injection/


It's a horrible idea to store jpegs in the database.

That said, if you want to get it out of the database, you can use psql. This gets it out as hex.

psql -t -A -o "/tmp/imagetest.jpg" -c \
  "SELECT photo FROM users_data_circulation LIMIT 1";

You may also want to check out the large object.


The reason you are not seeing any results is that the FOR loop is not returning any rows as there is no owner called 'O'.
To select all the table for a user who is not the logged in user you could use;

FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner <> USER)

However you will get a failure almost immediately as MAX(DATE) suggests that every table has a column called DATE. Which is not true.

It looks like you probably want a list of all the tables for a specific list of users (not SYS or SYSTEM or any other 'system' account) with the latest date that a row was added or created or something else involving a date.
To do that EVERY table for the schema must have the column you want to check.

DECLARE
my_sql VARCHAR(1000);
BEGIN
    FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner IN ('SCHEMA1', 'SCHEMA2','SCHEMA3')
    LOOP
        my_sql := 'SELECT MAX(update_date) FROM ' || t.owner || '.' || t.table_name ;
        dbms_output.put_line('executing: ' || my_sql);
        EXECUTE IMMEDIATE my_sql;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('ERROR!! -- '  || SQLCODE || '-- ' || sqlerrm || ' --' );
END;

SCHEMA1 / 2 / 3 are the users you know have the tables you are interested in and it assumes that each table has a'update_date' column.


One MySQL Table keeps losing auto-increment after restart

Try inserting dummy ID

select max(ID) fromtable1into @IDmax;
set @IDmax = @IDmax + 1;
insert intotable1setID= @IDmax;
delete fromtable1whereID= @IDmax;

This bug usually when trying to insert a duplicated value on a key.


Azure server names vs Azure database names

Your database name can be same on both instances with in the same subscription. See the picture below. I have same database in 2 different server but in the same subscription.


Locking the database to a single user so that other user wont change it

Mark the database file a read-only during the schema change, or run the schema change as sql commands from your app.


SQL Server's Equivalent in MySQL's binary log

SQL Server's transaction log is for SQL Server's internal uses, and Microsoft doesn't really give us an API to use it for other purposes.

For SQL Server 2012, the closest built-in tool is the undocumented fn_dump_dblog function (linking to a Paul Randal post - former developer at Microsoft), but make sure you read about the gotchas in there about hidden worker threads that don't go away. I wouldn't rely on that for regular data change polling.

If you describe your business goal in more detail (what you want to do with the data changes), you'll get better answers about the right feature or third party tool to use instead. For example, if you're looking for compliance auditing, there's one set of tools, but if you're looking to debug an application, I'd recommend a different set of tools.


How to copy auto-increment id value from one table to another?

Your schema doesn't even make sense. You need to have an authoritative bookid somewhere.

CREATE TABLE book (
  bookid  serial PRIMARY KEY
  -- stuff
);

CREATE TABLE book_title (
  bookid  int REFERENCES book,
  title   text
);

CREATE TABLE book_price (
  bookid  int REFERENCES book,
  price   float
);

That said, if everything is 1:1 with book, just put it all on the same table. You don't need a separate table for every column:

CREATE TABLE book (
  bookid  int serial,
  title   text,
  price   numeric(10,8)
);


Simple way to Drop Test databases and restore from Prod databases on 2008r2

As a recommendation, I would suggest switching to Ola Hallengren maintenance solution for the backups and some other administrative tasks, is really easy to configure. Run the main script Maintenance Solution, schedule required jobs and off you go. I hope that backups are copied to a different server than production server.

Then, schedule restore jobs from the test server to restore those daily backups. The job would have minimum 2 steps, drop database and restore it. Maybe you will want to add some cleaning step to clean up or mask some critical data that shouldn't be available for testing, replacing emails to avoid sending emails to clients and stuff like that. Maybe even deleting some data from bigger tables to have smaller databases. Heck, maybe for some cases is enough to just have the skeleton of the databases.

If there are few databases, you can handle it manually. If not, you could make use of dbatools as commented, is a set of powershell scripts really helpful for administrative tasks. Or you can search online, there are plenty of options available, 3rd party tools, scripts that you will need to tune for your needs, etc.


Then you need an < or > instead of a <>.
That is similar to when you try to find dupes in your data

CREATE TABLE dbo.t
(
    n varchar(100) NOT NULL
    , i geometry NOT NULL
);

INSERT INTO dbo.t (n, i)
VALUES ('poly1', geometry::STGeomFromText('POLYGON ((1 2, 1 4, 1 5, 4 6, 1 2))', 4326))
     , ('poly2', geometry::STGeomFromText('POLYGON ((1 2, 1 3, 2 5, 4 6, 1 2))', 4326))
     , ('poly3', geometry::STGeomFromText('POLYGON ((7 9, 8 7, 9 6, 7 9))', 4326))

SELECT t1.n
    , t2.n
FROM dbo.t t1
    INNER JOIN dbo.t t2 ON t1.i.STIntersects(t2.i) = 1
WHERE
    t1.n < t2.n;
GO

n     | n
:---- | :----
poly1 | poly2

db<>fiddle here


Why does INSERT SELECT into an unrelated table block when SELECT doesn't? Mysql 5.6

Actually, INSERT INTO .. SELECT does lock the SELECT table

I wrote about this before

Apr 25, 2016 : MySQL INSERT ... SELECT Performance
Aug 08, 2014 : MySQL consistent nonlocking reads vs. INSERT ... SELECT (More Details)


I need help migrating data through databases

It depends on how different the various tables are.  Without knowing how different the tables are between the databases, I would probably expect a combination of scripts and data compare-import tools such as Red Gate's Data Compare tool.

Using Red Gate's Data Compare, you can work around minor differences in table structures.

For splitting tables or significant changes within a table, you may be better off writing an import script from a local copy of your old database or a live connection to the old database.


Load balancing with Windows Clustering and AlwaysOn Availability Groups

QUICK ANSWERS TL/DR

Sort of, you will double the resources but every instance will only be active on one node at the same time.
Any SQL Server instance will have exclusive access to it's storage on the node where it's currently active - If all the storage for multiple instances comes from the same SAN one instance can affect the others
No you cant, sorry

You can create a 3 node cluster. But in SQL Server terminology active/active clustering means that you can run separate instances on any of the cluster nodes which then can fail back to any of the other nodes as clustering is for redundancy or high availability and nothing is shared between the database services.

You can use Always On the create read only replicas of the data which can be used to take some read only load of the server. All of this is explained in details in the High availability whitepaper

Now if you want or need to create a distributed load balanced setup you can try to implement peer to Peer transactional replication replication, you can create multiple distributed databases, using in memory objects in SQL Server to handle the transactional load and offload the data to other objects/databases. But the only transactional RDBMS that can, out of the box,  run a single database in shared everything mode on multiple hosts is Oracle RAC (or maybe ScaleDB) and that comes with it's own set of problems.


create insertion rule on view returning id

Without knowing your table and view structure, I created a test case for myself:

CREATE TABLE private_table (
    id serial,
    col1 text
);

CREATE OR REPLACE VIEW public_view AS
    SELECT id, col1
      FROM private;

CREATE OR REPLACE RULE insert_private AS
    ON INSERT TO public_view DO INSTEAD
        INSERT INTO private_table (col1)
        VALUES (new.col1)
        RETURNING id, col1; -- apparently, this does not work with fewer columns
                            -- or different ordering

So far, everything works fine, and there is an answer for your question: you have to add all columns of the view in the RETURNING clause of the rule.

What this does not achieve, though, is turning the INSERT into the view into something that returns the id:

INSERT INTO public_view (col1)
    VALUES ('blah');

INSERT 0 1
-- no id returned!

This absolutely makes sense, and can be avoided by adding the RETURNING clause to the actual INSERT, as usual:

INSERT INTO public_view (col1)
    VALUES ('blah2')
    RETURNING id;

 id
────
  2
(1 row)

INSERT 0 1


select from mulitple values using ANY in PostgreSQL

No there is no difference

CREATE TABLE incoming_requests ("status" character varying(16) )

✓

select FROM incoming_requests WHERE COALESCE(TRIM(status), '') = ANY (array['', 'OK', 'ERROR']);

✓

db<>fiddle here


Trying to connect a PostgreSQL database to Eclipse

This line shows the problem:

Caused by: java.net.SocketTimeoutException: connect timed out

As a rule of thumb, typically in Java stacktraces, the first "Caused by" (printed last) is what you want.

Your Win10 can't connect to the postgresql on your CentOS. PostgreSQL uses typically the TCP port 5432.

It gets a connection timeout error, which means your win10 absolutely doesn't get an answer. Nothing. If your postgresql wouldn't allow the connection, or if your postgresql wouldn't listen on your tcp port 5432, you had got "authentication error" or "connection refused" error.

But your win10 doesn't get anything, not even an error. As if your server wouldn't even exist.

You can check the availability of your postgresql with a

telnet your.centos.ip 5432

command.

The most probably cause of your problem is that the default firewall configuration of your centos simply filters out the 5432 port. Enable it.

The second most probable cause is that you simply typed in a false IP.

If you solved that problem, then

your telnet command won't hang up
your java software will connect, or at least it will have a different error (authentication error or connection refused are the most likely).

Making postgresql listening on the network requires also some different settings, beside your firewall configuration:

You have to set up your postgresql to listen on all IP addresses, not only on localhost. This can be done with a listen_addresses line of your postgresql.conf.
You have to set up some authentication (md5 password is the most likely) in the pg_hba.conf.

First connect your postgresql with some client, for example with pgadmin or with the command line psql tool (it comes also with cygwin). After it works, only then play to connect it from Java.


Management Studio won't recognize other file extensions

According to this Connect item, this bug was fixed in Service Pack 1.

However, if the issue persists, you can try this convoluted workaround posted over at SQLServerCentral by Marios Philippopoulos:

  OK, got it to work with help from http://www.progtown.com/topic374931-sql-server-2012-rtm.html.

  Opened regedit.exe as Administrator.

  Then:

  On [HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\FileExtensionMapping\PRC] I changed the Default key value to {B5A506EB-11BE-4782-9A18-21265C2CA0B4}.

  On [HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\Default Editors\PRC] I changed the Custom key value to {B5A506EB-11BE-4782-9A18-21265C2CA0B4}.


Does The Stored Procedure sp_removedbreplication Remove Rowguids

Try this:

UPDATE dbo.sysmergearticles SET preserve_rowguidcol = 1 WHERE type = 10 and name = 'MyTable'

It will preserve the rowguid column for the article.


Classic ASP Connection to SQL Server 2014 without TLS 1.0 using ADODB.Connection

You might possibly be trying to use 32 bit legacy drivers.

Try running C:\Windows\SysWOW64\odbcad32.exe to create a DSN that connects to your SQL Server database using the 32 bit SQL Server driver. First test the connectivity using the GUI.

Then try specifying your DSN in your connection string like so:

DSN=myDsn;Uid=myUsername;Pwd=;

The 64-bit version is located in:
C:\Windows\System32\odbcad32.exe

P.S. I've not tried this but the provider name for the 32-bit SQL Server is actually "SQL Server". It'd be nice if all you had to do was replace SQLNCLI11 with SQL Server. Here's my file DSN I created with the 32 bit version--notice the driver...

[ODBC]
DRIVER=SQL Server
UID=sa
WSID=P13-0000
APP=Microsoft® Windows® Operating System
SERVER=mcdba1


How to fix CRC error when doing a SQL Server database backup?

I'm having is that I'm getting an error when trying to backup the database

That's potentially bad.

I have not done a Transaction Log Backup ... I'm in Full Recovery Model

That's actually good. Then backup the log.  Somewhere safe.

In my test server I restored the backup that I have from 3 weeks ago

Almost home.  On your test server restore that old full with NORECOVERY, then restore the log backup.  If that works, your data is safe.  And even if your database is corrupt you can restore from the old full, the big log backup, and a Tail Log backup from the production server.