Database file location change on standby site after restore standby database

There are 2 ways you can fix this.

As @ramakant mentioned, SQL Server is using the default paths setup on the server. To change this, right click on the server in Object Explorer>Properties>Database Settings>Database Default Locations. Changing these values will automatically restore all databases to those directories unless different paths are specified.
You need to restore the database via a script, and that way you can specify where the files will be.
That will restore a database from a local location to the D: drive. Alter the script as required with regards to the locations.

RESTORE DATABASE [MyDatabase] FROM
DISK = N'C:\Work\MyDatabase.bak' WITH  FILE = 1,
MOVE N'MyDatabase' TO N'C:\MSSQL\Data\MyDatabase.mdf',
MOVE N'MyDatabase_log' TO N'C:\MSSQL\Log\MyDatabase_log.ldf',
NOUNLOAD,  STATS = 5
GO

More Info : RESTORE Statements (Transact-SQL)

Either one of these methods will work for you.


What is the difference between uniqe index/unique/unique key/unique constraint keywords?

There are no differences between all the SQL statements, except the third one you're using a different name for the unique index, that's all.

All of them creates a unique key/index, constraint md5_constraint unique (query_md5) has the same effect.

Just pick the name you prefer for the unique key and feel free to use any of your queries. :)


Blocked Process Report - which process does lockmode apply to?

I just tried this out in my local copy of the Stack Overflow sample database.  I ran the following query in one session:

BEGIN TRANSACTION;

UPDATE dbo.Users
SET Reputation = 0
WHERE Id < 1000;

Which was granted several X key locks in the users table (from EXEC sp_WhoIsActive @get_locks = 1):

<Lock resource_type="KEY" index_name="PK_Users_Id" request_mode="X" request_status="GRANT" request_count="752" />

I then ran this query in another session, which got blocked:

SELECT *
FROM dbo.Users
WHERE Id = 1;

According to the same sp_WhoIsActive query, this is now blocked and waiting on and S key lock from the Users table:

<Lock resource_type="KEY" index_name="PK_Users_Id" request_mode="S" request_status="WAIT" request_count="1" />

After 5 seconds, this produced a blocked process report event.  The "blocked process" node has a lockmode of "S" so this seems to prove that the lockmode is the lock being requested by the blocked process (not the lock being held by the blocking process).

Here's the full report XML for reference.  Unfortunately, it doesn't look like there's any way to tell the lock mode of the blocking process, other than consulting the lock compatibility matrix and inferring from that.

<blocked-process-report monitorLoop="587">
 <blocked-process>
  <process id="process25a0d94a108" taskpriority="0" logused="0" waitresource="KEY: 6:72057594039173120 (8194443284a0)" waittime="5279" ownerId="61463" transactionname="SELECT" lasttranstarted="2019-12-18T08:41:40.603" XDES="0x259bbe81ac0" lockMode="S" schedulerid="3" kpid="41876" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2019-12-18T08:41:40.600" lastbatchcompleted="2019-12-18T08:41:40.600" lastattention="1900-01-01T00:00:00.600" clientapp="Microsoft SQL Server Management Studio - Query" hostname="redacted" hostpid="39076" loginname="redacted" isolationlevel="read committed (2)" xactid="61463" currentdb="6" currentdbname="StackOverflow2010" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <executionStack>
    <frame line="1" stmtstart="24" stmtend="104" sqlhandle="0x02000000f1f6b108d092143d5a978eea9570bb0e96dc06ea0000000000000000000000000000000000000000" />
    <frame line="1" stmtend="74" sqlhandle="0x02000000669ce70e2be50eb44c883b0c891f586643da33df0000000000000000000000000000000000000000" />
   </executionStack>
   <inputbuf>
SELECT *
FROM dbo.Users
WHERE Id = 1;   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="53" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-12-18T08:39:20.630" lastbatchcompleted="2019-12-18T08:39:20.630" lastattention="1900-01-01T00:00:00.630" clientapp="Microsoft SQL Server Management Studio - Query" hostname="redacted" hostpid="39076" loginname="redacted" isolationlevel="read committed (2)" xactid="41842" currentdb="6" currentdbname="StackOverflow2010" lockTimeout="4294967295" clientoption1="671098976" clientoption2="390200">
   <executionStack />
   <inputbuf>
SET STATISTICS XML OFF   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>


Since a use case for a mongodb replica set is a base for analyses and learning cases (i.e a read-only database), is it possible/advisable to, in a replica set mongodb network, have an instance running on my laptop that will sync when connected to the net and then act as an offline read only set when not?

This configuration is possible, but generally not advisible.

  1) will this replica set be stable when the laptop is put to sleep/opened/etc?

The normal rules for fault tolerance apply, so assuming your replica set has a majority of configured voting members available it's fine for one or more members to go offline.

The replica set node on your laptop will also still be usable as a readonly secondary (assuming you use a secondary read preference with your driver or rs.slaveOk() in the mongo shell).

  2) will the intermittent availability of this set jeopardize an otherwise robust replicated database (say, with one primary, two arbiters and always on secondaries)?

This is where the approach heads into "not advisible" territory. In theory you can get a workable configuration with a core of stable voting members in your replica set configuration plus your laptop configured as a hidden, non-voting secondary.

However, there are a number of caveats to consider:

All replica set members need to be visible to each other via the same details as configured in your replica set configuration (hostnames and ports). You need to ensure your laptop knows what its expected hostname should be (otherwise it may not be able to find itself in the replica set config) and you need to consider how all the members will securely communicate with each other (eg. VPN).
Replica set members will be noisy about failed communication with unavailable members so you may have a lot of log noise that could potentially mask real networking problems.
If you fudge hostname resolution (eg. by editing /etc/hosts) it's very easy to introduce typos or forget to include this when updating or adding other members in the replica set.
If your laptop has incomplete or conflicting views of the network connectivity to other members, this may cause issues with elections/failover depending on your version of MongoDB and how you have configured your replica set.
Depending on the write activity for your replica set and how long your laptop remains offline, the laptop rejoining may cause noticeable load when catching up on old data that isn't in the current working set.
If your laptop remains offline for too long, it may require a full resync if there are no longer any oplog (operations log) entries in common with another member of the replica set.

Recommended approaches

1) Setup a hidden secondary in your hosted environment, instead.

2) If you really want a locally replicated copy and your replica set environment is anything other than development, I would suggest an alternative approach that sidesteps some of the potential issues with networking and visibility: you could consider using Mongo Connector. This is not an officially supported tool, but what it provides is replication-like behaviour to one or more target systems. It also doesn't remove caveats about possibly adding additional load or requiring a resync. For more background, see: Mongo Connector - Usage with MongoDB.

  an otherwise robust replicated database (say, with one primary, two arbiters and always on secondaries)?

FYI, you should only have zero or one arbiter. The arbiter is a member that only exists to potentially cast a tie-breaking vote where you have an even number of voting replica set members. The presence of one (or especially more than one!) arbiter already compromises robustness in the event of failover. An arbiter helps with voting majority but cannot contribute to write majority -- this leads to asymmetric failover scenarios. For example, in a Primary-Secondary-Arbiter configuration if either data bearing node fails you will still have a Primary but no longer have active replication or an ability to acknowledge more than a w:1 write concern.


Is there a way to automatically update a view based on daily created tables?

Look to table partitioning. Ideally, use the latest version of Postgres (currently Postgres 11) since there have been major improvements in Postgres 10 and 11.

Postgres 11 allows RANGE, LIST and HASH partitioning. One big partition per day would suggest LIST partitioning based on a date column like:

CREATE TABLE foo (
  foo_date date NOT NULL
, foo_id   bigint NOT NULL GENERATED ALWAYS AS IDENTITY
, data     text
, PRIMARY KEY (foo_date, foo_id)
)
PARTITION BY LIST (foo_date);

CREATE TABLE foo_20181226 PARTITION OF foo FOR VALUES IN ('20181226');
CREATE TABLE foo_20181227 PARTITION OF foo FOR VALUES IN ('20181227');
-- etc.

(Alternatively, you might have a timestamp or timestamptz column and use RANGE partitioning for that.)

Then you can query the master table directly to automatically include all partitions:

SELECT * FROM foo;

Or (since your main concern seems to be short syntax):

TABLE foo;

See:

Is there a shortcut for SELECT * FROM?

Various optimizations are possible, with constraints, indexes, column defaults etc. depending on requirement details.

Be sure to read the linked chapter of the manual to understand various pros and cons. Yours should be the perfect use case (unless undisclosed requirements are in the way). In particular, you can easily and very quickly add and remove partitions with minimum interference with the rest of the table.

There are still limitations in the current implementation. In particular, partition pruning is great to improve performance, but there is room for improvement (currently in development). That said, it's probably going to nuke the performance of your view for queries that don't need to involve all tables (partitions), since the view is going to consider all union'ed tables every time. A potentially faster alternative for certain queries is (currently) picking relevant tables (partitions) in a custom UNION ALL query.

And don't use names consisting of only digits like in your example 20181225. Use legal, lower-case names starting with a letter, or you have to double quote the identifier at all times.

  SQL identifiers and key words must begin with a letter (a-z, but also
  letters with diacritical marks and non-Latin letters) or an underscore
  (_). Subsequent characters in an identifier or key word can be
  letters, underscores, digits (0-9), or dollar signs ($). Note that
  dollar signs are not allowed in identifiers according to the letter of
  the SQL standard, so their use might render applications less
  portable. The SQL standard will not define a key word that contains
  digits or starts or ends with an underscore, so identifiers of this
  form are safe against possible conflict with future extensions of the standard.

Related:

Are PostgreSQL column names case-sensitive?


Why does mongodb virtual memory get so large

MongoDB uses memory mapped files for performance I'll point you to a longer answer from me on this here but in essence large amounts of virtual memory usage are not something to worry about. In the case of virtual memory you'll see double usage with journaling, however this is less drastic then it sounds as the space is not actually used until there is an update. It is marked to the OS as being used despite being potentially empty when you start.

These aspects are all elements of how MongoDB uses the memory subsystem to provide performance, similarly to how it preallocates disk space to ensure these resources are marked and ready to serve your connections when they occur.

There's an interest presentation on MongoDB's storage layer that explains how the storage is managed and allocated, aggressively in most cases which tends to bump up these numbers.


Bulk grant execute permissions for scalar functions & stored procedures

This bit of nested dynamic SQL grants exec or select on all the various procedure and function types, that were not shipped by MS, and that exist in any database outside of master, msdb, tempdb and model. It is certainly not easy on the eyes, tedious to reverse engineer, and I haven't thoroughly tested it, but hopefully if you work through it you can figure out what I've done. ALl you really need to specify is the name of the login you want to grant the rights to - of course this assumes that the login has been mapped to a database user with the same name in each database. That was a layer of complexity I wasn't prepared to add, sorry. :-)

DECLARE
  @principal SYSNAME = N'some_server_principal';

DECLARE
  @sql1 NVARCHAR(MAX) = N'',
  @sql2 NVARCHAR(MAX) = N'',
  @sql3 NVARCHAR(MAX) = N'';

SELECT @sql1 += N'
SELECT @sql += N''SELECT @sql += N''''
USE ' + QUOTENAME(name) + ';
GRANT ''''
  + CASE WHEN type IN (''''P'''',''''PC'''') THEN ''''EXEC'''' ELSE ''''SELECT'''' END
  + '''' ON '''' + QUOTENAME(SCHEMA_NAME(schema_id)) + ''''.'''' + QUOTENAME(name)
  + '''' TO ' + QUOTENAME(@principal) + ';'''' FROM ' + QUOTENAME(name) + '.sys.objects
  WHERE is_ms_shipped = 0 AND type IN (''''P'''',''''PC'''',
    ''''FN'''',''''AF'''',''''FN'''',''''TF'''',''''IF'''');'';'
  FROM sys.databases
  WHERE database_id > 4;

EXEC sp_executesql @sql1, N'@sql NVARCHAR(MAX) OUTPUT', @sql = @sql2 OUTPUT;

EXEC sp_executesql @sql2, N'@sql NVARCHAR(MAX) OUTPUT', @sql = @sql3 OUTPUT;

-- you won't be able to validate all of this, since Management Studio will
-- truncate the output quietly. But you should get a good sense of what the
-- end result will be:

SELECT @sql3;

-- When you are happy with the above, uncomment this:

-- EXEC sp_executesql @sql3;

This is kind of like Inception. In order to generate the right output, you have to figure out which layer every element is on. This is why the initial string is so ugly, and littered with escaped apostrophes in sets of 2 and 4. It can be a little unnerving at first, but you can become adept very quickly at writing dynamic SQL for metadata purposes.


how to find the database context of the last sql statement?

The context of this script is my_database. It's just that the query references objects in a different database. Consider a scenario where a single query uses objects in multiple databases. Which database would you want returned?

What are you trying to accomplish?


How can I pivot a view into a join so I get multiple query rows as columns in a single row?

Using the PIVOT clause, you can achieve the result like this:

SELECT
  Vehicle_ID,
  Doors,
  [Engine Size],
  [Tire Size],
  [Color]
FROM
  dbo.YourView
  PIVOT
  (
    MAX(Value) FOR Property IN (Doors, [Engine Size], [Tire Size], [Color])
  ) AS p
;

Now, to join the results to your main vehicle table, you can use the above as a derived table (same as you probably did with the solution you found yourself) – or you can join the main table directly to the results of the PIVOT:

SELECT
  m.Vehicle_ID,
  m.Description,
  m.DateBought,
  p.Doors,
  p.[Engine Size],
  p.[Tire Size],
  p.[Color]
FROM
  dbo.YourView
  PIVOT
  (
    MAX(Value) FOR Property IN (Doors, [Engine Size], [Tire Size], [Color])
  ) AS p
  INNER JOIN dbo.MainVehicleTable AS m ON p.Vehicle_ID = m.Vehicle_ID
;

The above assumes that your view has only the three columns mentioned in your question. If there are more (e.g. time of recording/storing the property or something else), then you will probably need to replace the plain dbo.YourView reference with a derived table where you are pulling only those three columns:

...
FROM
  (
    SELECT
      Vehicle_ID,
      Property,
      Value
    FROM
      dbo.YourView
  ) AS s
  PIVOT
  ...


Why I cannot let EXPLAIN update statements?

MySQL currently doesn't support this (unlike nearly all other DBMS).

I think this will be in 5.6 but I'm not entirely sure.

I don't think there is any workaround for that (except upgrading to a DBMS that does support this)


Do queries called from within a transaction but found within another method act the same?

MySQL does not care about your PHP code structure. One transaction is what is between start and commit/rollback, sent over one connection - thats important, if your $this->db->createCommand()->execute/query creates a separate connection for some reason then it won't work but that should not be the usual case, just mentioning it because it sometimes happen so better to check.

You can have multiple transaction "open" using multiple connections but then you can deadlock yourself so beware (MySQL can automatically resolve deadlocks on the DB layer but not if you lock a record from one connection and then try to access it from some other, because your app is single-threaded (probably) and the deadlock detection expects parallel clients.


Importing different file types to SQL Server

How many is "several"?  If it's three or four, I'd start by writing a simple .NET script in SSIS which sorts out the files into separate directories.  Use several foreach loops, each working in its own directory, with the files in that directory having a consistent format.

If there are a lot of formats, it'd probably be faster to write a little application which parses files and writes a single clean file for subsequent BCP or SSIS import.


Adding a new column to a table will not modify existing data. Records in the database are versioned with a so-called format version at the time the record was written. This format version describes the columns (and other column related metadata).

If you alter a table to add a column, then a new format version is added. When reading records, Firebird will read the row using the original format version, and then - in memory - 'upgrade' the row to the latest format version, eg adding new columns with a null value (or the default value), removing columns that have been dropped, or changing sizes, etc.

In other words, adding columns is cheap. There is one exception: since Firebird 3, adding a column with a NOT NULL-constraint if there are existing records will require a default value, otherwise the addition fails. In earlier versions this would silently add a NOT NULL column containing NULL for existing records, leaving fixing of the data for those rows to the database owner.

Regarding storage itself, Firebird uses a simple form of RLE compression to compress records on disk. Null values written using the new format version do take up some space because of this, but max 2 bytes for most types, and multiples of 2 bytes for column types longer than 127 bytes. As an extreme form of optimization, it might make sense to group columns that are usually NULL together (but the performance benefit is likely very small).

Regarding runtime performance, since Firebird 3 (and assuming a client that supports wire protocol 13 or higher), columns that are NULL are skipped in the wire protocol (meaning that no data is sent for that column), which reduce the number of bytes sent over the network.


max(max(col1), max(col2), ...) does not work

That should be GREATEST(), not MAX(). MAX() is for aggregating over values from rows, GREATEST() is for aggregating over a list of values (from columns, expressions).

You can either find the 3 max values, one for each column and then the greatest of the three:

select greatest(max(col1), max(col2), max(col3))
from mytable ;

or find the greatest value in each row and then the max over all of them:

select max(greatest(col1, col2, col3))
from mytable ;


Why is MySQL is creating so many temporary tables on disk?

mysqltuner rarely provides any useful information.  It uses mostly irrelevant statistics about "hit rates" and puts arbitrary limits on what is an acceptable number of widgets are acceptable.  If you are not facing a performance problem, then you don't actually need to solve any of the problems that it presents to you.  That being said, here's a little background information about temporary tables...

MySQL internally uses the MEMORY storage engine for creating implicit temporary tables.  On disk temporary tables use the MyISAM storage engine.

Temporary tables are created on disk when:

TEXT or BLOB fields are present (because MEMORY doesn't support these types)
the size of the resulting implicit temporary table exceeds the lesser of tmp_table_size or max_heap_table_size
If a column w/ more than 512 bytes is used with either a GROUP BY or UNION or ORDER BY

Read the MySQL Documentation on Internal Temporary Tables for more details.

What can you do about this?  Presuming that it actually represents a performance problem (rather than just bothering you intelluctually):

Avoid TEXT/BLOB fields and instead use appropriately sized VARCHAR or CHAR fields where possible.
If TEXT/BLOB are unavoidable, sequester them to separate tables with a foreign key relationship and JOIN only when you need them.
Treat large columns, more than 512 bytes as you would the above mentioned TEXT/BLOB fields.
Make sure your queries are returning only the result set you need (appropriately selective WHERE clauses, avoid SELECT *)
Avoid subqueries and replace them with joins, especially if they return a large result set
Last resort - raise both tmp_table_size and max_heap_table_size.  Don't do this unless you find that your queries cannot be optimized.

If you are concerned about your MySQL configuration and are not comfortable with the available settings yourself, you might want to check out the Percona Configuration Wizard as a starting point.

  Will changing the db engine from "myisam" to "memory" on tables using
  "group by" fix this? as explained here

No, it won't and it will make it such that your tables are never persisted to disk.  Don't do this.


mysqldump Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

As far as the the Workbench error is concerned what you need to do is disable the "column statistics" argument on export. So, you should write the command adding "--column-statistics=0". This should resolve the first-Workbench issue:

mysqldump.exe --defaults-file=####  --user=#### --host=localhost --protocol=tcp --port=59452 --default-character-set=utf8 --single-transaction=TRUE --skip-triggers --column-statistics=0 "BusinessManager"

Regarding tablespace error, the documentation describes it here:

On the new MySQL instance, recreate the table in a database of the same name using the command CREATE TABLE ...
Discard the tablespace of the newly created table:

ALTER TABLE database.tableName DISCARD TABLESPACE

Copy the orphan .ibd file from your backup directory to the new database directory using shell:

cp /backup_directory/tableName.ibd path/to/mysql-5.6/data/database/

Finally, import the orphan .ibd file. A warning is issued indicating that InnoDB will attempt to import the file without schema verification.

ALTER TABLE database.tableName IMPORT TABLESPACE; SHOW WARNINGS;

This should do it. As a test, you should run the following command in mysql:

    SELECT COUNT(*) FROM database.tableName;

If this executes correctly, tablespace error should disappear.


ORA-00959: Tablespace '$name' does not exist when using impdp

Create the tablespace

or

remap the tablespace to use an existing one, with the remap_tablespace parameter:

impdp ... remape_tablespace=$name:$name2

Where $name2 is the name of an existing tablespace.


Creating Archiving database for old data in MySQL

I would suggest looking at the pt-archiver tool in Percona Toolkit.


MySQL Update Multiple Rows in Large Table

SHOW ENGINE INNODB STATUS\G
and see
https://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded
increasing the lock timeout period could solve the issue.
Apparently default is 50seconds. Which is maybe enough for all of the rows with userid=99, but not enough for rows with 99,22,88 or 7 userid.
Just speculation.


Cannot find aria_s3_copy utility in mariadb 10.5.5

aria_s3_copy is in the mariadb-plugin-s3 package which isn't installed by the mariadb docker library container image.
Extending the docker container and installing this plugin package is possible.


This is a simple group by query.

select status,
       rank,
       count(*) as cnt
from the_table
group by status, rank
order by status, rank;


JOIN ON syntax error in Microsoft Access 2016

Access requires INNER JOIN. The simple JOIN (which should default to INNER JOIN according to the SQL standard) is not allowed in Access.


How to check mysql password change and other events logs

by default MySQL community version not support audit

You have only one build in way - enable general log for log all activity on MySQL server

alternatives for enterprise version - https://www.percona.com/doc/percona-server/5.5/management/audit_log_plugin.html

MariaDB audit plugin You can install and on Linux and on Windows server - https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/


How to do a minor upgrade of PostgreSQL on Windows, e.g. 9.3.0 to 9.3.1?

On Windows, simply stopping the postgresql service, then running postgresql-9.3.1-1-windows-x64.exe on top of the existing 9.3.0 works.  No uninstall necessary.  Of course, a backup is recommended.

Clear, explicit documentation for the update procedure on Windows is absent.  Note that the documentation link provided by @dezso has been moved in the current manual to:
https://www.postgresql.org/docs/current/static/upgrading.html

The PostgreSQL Release Notes typically document migration tips in Appendix E.
 For example,

Appendix E. Release Notes https://www.postgresql.org/docs/current/static/release.html
Section E.1. Release 10.1, subsection E.1.1. Migration to Version 10.1
https://www.postgresql.org/docs/current/static/release-10-1.html#idm46428658121200

The best source of Windows installer information was the Enterprise DB forums.  Here are some posting I found that addressed the question...

2010 April - What's the right way to upgrade to a new version of PostgreSQL
https://web.archive.org/web/20121210220213/http://forums.enterprisedb.com/posts/list/2246.page
2009 September - Upgrade procedure when using one-click installer?
https://web.archive.org/web/20130603094838/http://forums.enterprisedb.com:80/posts/list/1906.page#6791

Note that the upgrade info moves around from release to release.  For example,

Version 10 location:
Chapter 18. Server Setup and Operation, section 18.6. Upgrading a PostgreSQL Cluster
https://www.postgresql.org/docs/10/static/upgrading.html
Version 9.3 location:
Chapter 17 Server Setup and Operation, section 17.6 Upgrading a PostgreSQL Cluster
http://www.postgresql.org/docs/9.3/static/upgrading.html
Version 9.0 location:
Chapter 24 Backup and Restore, section 24.4 Migration Between Releases
http://www.postgresql.org/docs/9.0/static/migration.html

Note that as of June 2017, EnterpriseDB replaced their community discussion forums affecting the links in this answer(https://web.archive.org/web/20171021012954/https://www.enterprisedb.com/news/enterprisedb-announces-new-postgres-rocks-online-user-forum).  I was able to hunt down some of the original posts on the Wayback Machine.  One dead link that I was unable to repair is: "2010 January - Upgrading to 8.4.2 from 8.4.1",
http://forums.enterprisedb.com/posts/list/2115.page#7888.


calling scalar function to assign value to local variable

Two big problems with using functions in a query are that cardinalities are not accurate and that the function code is called once per row. When setting a variable neither of these are a concern. So I would think, generally speaking, there would be no measurable performance impact.


Master-Slave Database. How to activate a secondary database whenever the primary database is down?

In addition to setting up replication, which will copy the data from your master to your slave, you need a failover technology to make the system use the slave when the master is down.

You can do this manually, but you can also use something like MMM (http://mysql-mmm.org/). It is open-source, so you can look at the code to see the logic of what it is doing. Basically, it is moving aliases (like yourdb-master and yourdb-slave) between two physical servers, so the system always connects to a yourdb-master, wherever that is physically.


Best way to replicate MySQL database tables

"Sharding" involves servers running in parallel, not replicating one from another.

The most practical way to copy a schema without the data is via mysqldump --no-data.  However, there is no automation for that.

Bluntly put, you have to reinvent the sharding wheel.  There is very little that addresses the many issues of sharding.

That said, I suggest you look at Spider, Clustrix, NDB Clustering, and perhaps a few others.

I strongly suggest you perfect a way to copy a 'project' from one shard to another.  It should be as close to non-invasive as practical.  Then it could be the main tool for updating hardware, updating software, offloading a busy shard, retiring hardware, etc.  Specifically to your question, you can build a new shard with upgraded Laravel/schema/etc, then migrate projects to it.  Rinse, repeat.


How to change data structure in mysql using mysqldump without deleting files

As long as you don't rename tables and columns and you are not using partitions you might use schemasync .

Basic usage is:

schemasync mysql://user:pass@dev-host:3306/dev_db mysql://user:pass@prod-host:3306/prod_db

This will produce two SQL scripts - one for "patching" the production database, and another one for reverting the changes.

Please note that schema altering will lock the tables being affected, so you will have some downtime anyway. There are tools to avoid this: pt-online-schema-change from Percona and oak-online-alter-table from openarkkit.

Also, as with any possibly destructive changes, test the procedure on a staging (or other non-production use, but production data and schema) database.


How to check SqlSetPos compatibility on database?

You'll need to check the documentation for the specific ODBC driver you are using. I would expect the SqlSetPos function to return SQL_ERROR or SQL_SUCESS_WITH_INFO if the driver doesn't support (or partially supports) the function. In that case, a subsequent call to SQLGetDiagRec should return a SQLSTATE with the error/warning cause, such as IM001 (Driver does not support this function).  See the SQLSetPos function reference for details.


MySQL database takes too long to load page first time

in the case of MySQL 5.6 until 8.0, innodb is ready for caching after you restart or start MySQL server with innodb_buffer_pool_dump_now and innodb_buffer_pool_load_now, with this you could save pages cached into buffer pool and then you could load again, avoiding a query takes too long of time to load

Regards


Can you use the "bit" type instead of "bytea", and use your own user-defined aggregate?

postgres=> CREATE AGGREGATE bitset_or ("bit")
postgres-> ( sfunc    = bitor,
postgres->   stype    = "bit",
postgres->   initcond =   '0'  );

Now bitset_or should work with your query as written (again, assuming bytea -> bit).


Backing up an Oracle 10G Express on Windows 2000 Server

I don't have the 10g version of Express Edition installed, but if it is like the 11g version, then your first question is answered in the comments section of the Backup.bat file itself.

For 11g this means the comments indicate that No cleanup script is needed because the script automatically keeps two backups in the Flash Recovery Area.  Whether there is a better method or not will depend on what your recovery requirements are.


Getting Database Request Statistics on MySQL

Jaime's answer is pretty good (and thanks for the credit). However, that only deals very specifically with "Table IO" (i.e latency at the storage engine handler layer), and file IO, per schema.

I'd like to suggest a slightly alternate answer, which gives exactly what was asked for. This deals with statement latency over all, and counts the numbers of each types of statement:

SELECT schema_name,
       substring_index(digest_text, ' ', 1) AS sql_type,
       COUNT(*) AS count,
       sys.format_time(SUM(sum_timer_wait)) AS latency
  FROM performance_schema.events_statements_summary_by_digest
 GROUP BY schema_name, substring_index(digest_text, ' ', 1)
 ORDER BY schema_name, SUM(sum_timer_wait) DESC;

+--------------------+----------+-------+---------------+
| schema_name        | sql_type | count | total_latency |
+--------------------+----------+-------+---------------+
| NULL               | CALL     |     2 | 584.49 ms     |
| NULL               | SELECT   |     2 | 247.07 ms     |
| NULL               | SHOW     |     1 | 16.82 ms      |
| NULL               | CREATE   |     1 | 9.60 ms       |
| NULL               | INSERT   |     1 | 268.62 us     |
| NULL               | SET      |     3 | 225.49 us     |
| performance_schema | SHOW     |     2 | 243.25 ms     |
| performance_schema | SELECT   |     1 | 52.18 ms      |
| performance_schema | INSERT   |     2 | 335.52 us     |
| sys                | CREATE   |   120 | 19.16 s       |
| sys                | SELECT   |    83 | 1.54 s        |
| sys                | DESC     |     2 | 627.22 ms     |
| sys                | DROP     |    38 | 504.05 ms     |
| sys                | SHOW     |     2 | 139.58 ms     |
| sys                | INSERT   |     1 | 88.10 ms      |
| sys                | SET      |     3 | 421.86 us     |
+--------------------+----------+-------+---------------+

The NULL schema_name is for statements executed without a default database (i.e no "USE dbname").

You will also need sys installed, but could skip the sys.format_time(...), and just select the SUM(...), in which case the value will be in picoseconds for the latency (same as Jaime's).

Since I can't yet comment (my first answer on here!), I'll also note here that performance schema table data does not replicate (even DML against the setup_* tables in performance_schema), and that the above query will work with the default configuration with 5.6 (performance_schema and the statement instrumentation are both enabled by default there).


How to change SQL Server Service Account using PS

The GUI way to do this is to go to SQL Server Configuration Manager.  Go to the properties of the SQL Server and change the account name on the Log On tab.  Click the Browse button.

Than enter the account that you want to SQL Server Log on as.  Click Check Names, to make sure the account exists.  Than click OK on select user or group and OK on SQL Server Properties.

Than restart.

2.This blog Hey, Scripting Guy! Blog, the author Aaron Nelson - SQLvariant shows how to do this in PowerShell.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

$SMOWmiserver = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') "WIN7NetBook" #Suck in the server you want

#These just act as some queries about the SQL Services on the machine you specified.
$SMOWmiserver.Services | select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-Table

#Same information just pivot the data
$SMOWmiserver.Services | select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-List

#Specify the "Name" (from the query above) of the one service whose Service Account you want to change.

$ChangeService=$SMOWmiserver.Services | where {$_.name -eq "MSSQLSERVER"} #Make sure this is what you want changed!

#Check which service you have loaded first

$ChangeService

$UName="DomainName\UserName"

$PWord="YourPassword"

$ChangeService.SetServiceAccount($UName, $PWord)

#Now take a look at it afterwards

$ChangeService


get max serial no. according to sum of its corresponding amount

Another way – that works in versions prior to 2012 – is to use a recursive CTE.

Tested at rextester.com:

WITH ct AS
  ( SELECT TOP (1) s_no, amount, running_total = amount
    FROM tableX
    ORDER BY s_no

    UNION ALL

    SELECT n.s_no, n.amount, n.amount + n.previous_total
    FROM
      ( SELECT t.s_no, t.amount, previous_total = ct.running_total,
               rn = ROW_NUMBER() OVER (ORDER BY t.s_no)
        FROM ct
          JOIN tableX AS t
            ON t.s_no > ct.s_no
        WHERE ct.running_total < 1600
      ) AS n
    WHERE n.rn = 1
  )
SELECT *
FROM ct
WHERE running_total >= 1600
OPTION (MAXRECURSION 0) ;          -- no limit on recursion

The CTE's anchor (the UNION ALL's first leg) selects only the first row. Then in each iteration the CTE's recursive part (the second leg) selects the next row and computes the running total until the sum is 1600 or more.

The ROW_NUMBER() trick and subquery is to overcome the limitation that TOP is not allowed in the recursive parts of CTEs.


Pass additional data in connection for use by PGSQL Trigger

Old question, but still valid, so here is what I ended up using.
On 9.6 and above, to set the values either of the following will work:

select set_config('app.userid', '3', true)
SET LOCAL "app.userid" = 3; (or '3')

To read the value out,

select current_value('app.userid')

I ended up wrapping the lookup for the userid or other field in a function, to handle the type conversion(s) and the fact that it might not be set:
CREATE OR REPLACE FUNCTION app_get_userid() RETURNS INT AS $$
DECLARE userid integer;
BEGIN
    BEGIN
        userid := current_setting('app.userid');
    EXCEPTION
        WHEN OTHERS THEN userid := NULL;
    END;
    return userid;
END;
$$ LANGUAGE plpgsql;


Lost connection to server executing ALTER TABLE

Change specific timeouts value to 6000 it's available in all new versions of MySQL Workbench.

  Open Edit → Preferences → SQL Editor → DBMS connection read time out
  (in seconds): 600

  Changed the value to 6000.

Also unchecked limit rows as putting a limit in every time I want to search the whole data set gets tiresome.


How to quickly increase column size in Oracle?

Your two options are (depending on the use case):

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8753545300346585555

https://asktom.oracle.com/pls/asktom/asktom.search?tag=best-way-to-change-the-datatype-of-a-column-in-a-table-with-20-million


Does wsrep_cluster_address only accept the intranet IPs?

You can use any IP address in wsrep_cluster_address (you can use hostnames too). You need to make sure that all ports required are open for Galera communication (3306, 4444, 4567, 4568). There are many other reasons for "failed to get the other two working" so log files would be very helpful.


How unsafe is it to change the data source of repl_distributor on a publisher

Know this is an old question but we've just been working with this, Last month we had to move our distribution server to a brand new machine, (Setup is two machines in AG stand alone distributor and a reporting server)

The only real way we found to move distributor server is to completely tear down and rebuild replication, this can be done with right clicking replication and generate scripts

bare in mind you'll need to do this on the publishers and the new distributor.

We've just had an issue where our AG group didn't take the distributor change, and since its a read only node we're doing some experiments with different ways to do that,

our tests of updating the [master].[sys].[sysobjvalues] table (which is where the 'datasource' value is actually stored (the sysservers is just a view) have resulted promising results if you want the server to never work ever again.  Tearing things down and rebuilding it is painful, but its by far the safest thing to do.

Ste


Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself

As per the comments, if you're already the db owner of that database, than you don't need to grant any permission for the db.

Now, in order to find out what specific permissions you have, you can use the following queries:

find permissions on specific db:

USE AdventureWorks2008R2;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO

find permissions on all the server:

SELECT * FROM fn_my_permissions(NULL, 'SERVER');
GO

See some other info here: Find out SQL Server Logins and associated Server Roles, on MSDN forum.


Restoring database with less data than the backup file

No, it is not possible to only restore n number of rows from a given table.  The restore process does not even know about the tables, let alone how many rows are in each table, as it works on the database page level.

To accomplish your goal of only getting n number of rows from production to non-production without doing a full restore you'll probably want to set up an ETL process.


You'll want to log onto the distributor and stop the merge agent.  Then open a command line window and navigate to the folder which has the replication binaries in it (c:\Program Files\Microsoft SQL Server\100\COM\ by default).  Run replmerg.exe and paste in all the values from step 2 of the job which is the replication agent.  Then change the -OutputVerboseLevel from 0 to 1 (the parameter may not be there if it isn't just add it was an value of 1).

This should give you the actual error message so you can see what's going on.


What is a staging table and how do you protect data loss when transferring the data?

I believe I have a basic understanding of what a staging table is.
  From my understanding it is just a location to temporarily store data
  before moving that data to its permanent home.

Yes, it is.

  To my knowledge you can't perform this type of operation on a table
  with enabled triggers(used to add a sequenced primary key).

This is a restriction of direct path load, not staging tables.

Direct Path Loads, Integrity Constraints, and Triggers

Triggers fire during conventional path loads.

  How do you protect data loss when transferring data from a staging
  table to it's permanent home(permanent table?)

You don't. Direct path loads do not generate the redo that would make possible recovering the data affected by it in case of a recovery situation (this is to improve performance). Since it is just a temporary location, if it is damaged or lost, you can just start over and repeat the loading process.

If you want it to be protected, then use conventional path load.


How can i improve my count(distinct id) query in mysql laravel 4.2

See if this gives you the right answer:

SELECT  COUNT(DISTINCT userid) AS aggregate
    FROM  enrollments AS e
    WHERE  EXISTS
      ( SELECT  *
            FROM  classes AS c
            JOIN  assigned_courses AS ac ON ac.courseid = c.courseid
            WHERE  ac.oneroster_permission_id = 147
              AND  c.classid = e.classid
      ) AS x

Note:  I tried to avoid the DISTINCT by avoiding any JOINs.  JOINs tend to explode the number of rows, only to force you to use GROUP BY or DISTINCT to get back to what you wanted.  But, I guess there could be multiple occurences of a userid in enrollments.

No matter how it is written, I think the performance will be poor.  Notice how the query needs to come at it from two sides, one to filter on 147, the other to connect to enrollments.classid.


Remotely Connect to MySql from MS Access

Yes, by using linked tables. Here's the MySQL documentation https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-linked-tables.html

Add a User DSN from the ODBC Data Source Administrator, testing the link to your MySQL database to ensure you have the server, user name and password correct.

In Access on the External Data tab select ODBC Database. Choose the Link... option. Select Machine Data Source and the name of the DSN you set up. The tables available will appear. Choose the one(s) you want and click OK.


SQL Server 2000 Replication, Performance, and Query Priorities

Note that You are arguing for an UNSUPPORTED version of sql server.

Apart from that read below :

  but the DBAs are telling us that Replication in SQL Server 2000 doesn't work and/or that it has a severe performance impact on the source server.

This is completely False. Why would T-Rep not work in sql 2000 ? Why would it have a severe performance impact on source server ?

The only impact that I can see is

During the initial snapshot generation as it locks the table while the snapshot is generated and if the table is huge then it will lock it for longer durations. Imagine this causing blocking or possibly timeouts.
when you are replicating the entire database (not only required set of tables/articles) and when you want to reinitialize the replicaiton - meaning new snapshot !
When the n/w bandwithd is not good or there are n/w related issues.

Above holds true even today for sql server 2012 or 2014.

Note that T-Rep on sql 2005 and up has improved a lot, but the argument your DBA has made is baseless. We even has some legacy applications running on sql 2000 and we use replication heavily and is stable in sql 2000 - no issues at all.

  if it is true/not true that queries from Stored Procedures or queries called from a Linked Servers will take precedence over other queries (e.g. dynamic queries from a connecting application).

Again completely baseless or you might have misunderstood it. Stored procedures can have their plan complied and stored in procedure cache and the plan can be reused.

I don't think you can have priority of the kind you have mentioned.


MySQL: SELECT the n-th item from an ordered result

SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id LIMIT n, 1


Aggregate all joined rows with where clause

If I understand your requirements, this consists of selecting all the users that have a project in step 3, and using that list to select all the projects for just those users.
Something like:
SELECT ug.id
     , STRING_AGG(DISTINCT p.step_number, ', ') AS summary
FROM user_groups ug
    INNER JOIN (
            SELECT u.user_group_id
                 , u.user_id
            FROM users u
                INNER JOIN projects p ON u.user_id = p.user_id
            WHERE p.step_number = 3
            GROUP BY u.user_group_id, u.user_id
        ) Step3Projects ON ug.user_group_id = Step3Projects.user_group_id
    INNER JOIN projects p ON Step3Projects.user_id = p.user_id
GROUP BY ug.id;


Tricky selection of grouped rows, selecting based on values of two distinct but related rows

Get qualifying rows only
One way ..
WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
, y AS (
   SELECT *
   FROM   x
   WHERE  x.rn = 1
   AND    date_out >= '2012-10-01'
   AND    date_out <  '2012-11-01'
   )
, z AS (
   SELECT x.*
   FROM   x
   JOIN   y USING (reference)
   WHERE  x.rn = 2
   AND    x.centreid = 1
   )
SELECT y.taskid, y.reference, y.centreid, y.date_out
FROM   y
JOIN   z USING (reference)

UNION  ALL
SELECT taskid, reference, centreid, date_out
FROM   z
ORDER  BY reference, date_out;

Another way:
WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
,y AS (
    SELECT x.*, y.taskid AS taskid2, y.centreid AS centreid2, y.date_out AS date_out2
    FROM   x
    JOIN   x y USING (reference)
    WHERE  x.rn = 1
    AND    x.date_out >= '2012-10-01'
    AND    x.date_out <  '2012-11-01'
    AND    y.rn = 2
    AND    y.centreid = 1
    )
SELECT y.taskid, y.reference, y.centreid, y.date_out
FROM   y

UNION  ALL
SELECT y.taskid2, y.reference, y.centreid2, y.date_out2
FROM   y
ORDER  BY reference, date_out;

I'd expect the second one to be faster. Depends on your data distribution. Test with EXPLAIN ANALYZE.
Get all rows for qualifying references
WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
,y AS (
    SELECT reference
    FROM   x
    JOIN   x y USING (reference)
    WHERE  x.rn = 1
    AND    x.date_out >= '2012-10-01'
    AND    x.date_out <  '2012-11-01'
    AND    y.rn = 2
    AND    y.centreid = 1
    )
SELECT *
FROM   tbl
JOIN   y USING (reference)
ORDER  BY reference, date_out;

-> sqlfiddle
Answer to follow-up in comment
Separate groups if more than 30 days between entries.
WITH a AS (
   SELECT *
         ,lag(date_out) OVER (PARTITION BY reference ORDER BY date_out DESC) AS last_date
         ,CASE WHEN date_out >
                   (lag(date_out) OVER (PARTITION BY reference ORDER BY date_out DESC) - 30)
               THEN 0 ELSE 1
          END AS step
   FROM   tbl
   )
,b AS (
   SELECT *
         ,sum(step) OVER (PARTITION BY reference ORDER BY date_out DESC) AS grp
   FROM a
   )
 ,c AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference, grp ORDER BY date_out DESC) AS rn
   FROM b
   )
,d AS (
   SELECT reference, grp
   FROM   c
   JOIN   c d USING (reference, grp)
   WHERE  c.rn = 1
   AND    c.date_out >= '2012-10-01'
   AND    c.date_out <  '2012-11-01'
   AND    d.rn = 2
   AND    d.centreid = 1
   )
SELECT b.taskid, b.reference, b.centreid, b.date_out
FROM   b
JOIN   d USING (reference, grp)
ORDER  BY reference, date_out

-> sqlfiddle
But while pure SQL is a beaut .. I would solve this procedurally in a plpgsql function. Very similar to this recent answer on SO. Would be faster, because it can be done in a single table scan.


How does Oracle handle composite index lookups?

In Oracle, we have a feature called Index skip-scan that makes it possible to benefit from composite indexes even if the leading (left most) part of the index is not specified in the WHERE condition. This is a 9i New Feature, documented here:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm


As soon as a table is dropped, all grants are gone. There is no way to avoid that.
You should put all your grants into a SQL script, put that into your version control system (e.g. Git) and then run it after you applied all your migration scripts.
Schema migration tools like Liquibase, Flyway or Sqitch help you organize this.


How can I insert if key not exist with PostgreSQL?

Upsert statements used to be planned for 9.1 but have been postponed to 9.2, so until then, your only choice is to test if the value already exists before inserting.

Alternatively, if your intention is merely to have a unique identifier, you could simply use a sequence + nextval.

If you want to create a function to do that, this should get you started:

CREATE OR REPLACE FUNCTION upsert_tableName(arg1 type, arg2 type) RETURNS VOID AS $$
DECLARE
BEGIN
    UPDATE tableName SET col1 = value WHERE colX = arg1 and colY = arg2;
    IF NOT FOUND THEN
    INSERT INTO tableName values (value, arg1, arg2);
    END IF;
END;
$$ LANGUAGE 'plpgsql';


Sorry, you are probably SOL.

Case 1:  Copying the disk while MySQL is actively writing data.  Very likely to be in an inconsistent state -- some things written to disk and copied; some not.

Case 2:  MySQL up during the copy, but not actively writing (UPDATE, INSERT, etc).  This might work since it is identical to a power failure.


How to apply ORDER BY and LIMIT in combination with an aggregate function?

I am applying order by p.name desc in two places ... Is there a way to avoid that?

Yes. Aggregate with an ARRAY constructor in the lateral subquery directly:

SELECT c.identifier, p.persons
FROM   class c
CROSS  JOIN LATERAL (
   SELECT ARRAY (
      SELECT identifier
      FROM   person
      WHERE  class_identifier = c.identifier
      ORDER  BY name DESC
      LIMIT  2
      ) AS persons
   ) p
ORDER  BY c.identifier;

You also don't need GROUP BY in the outer SELECT this way. Shorter, cleaner, faster.

I replaced the LEFT JOIN with a plain CROSS JOIN since the ARRAY constructor always returns exactly 1 row. (Like you pointed out in a comment.)

db<>fiddle here.

Related:

Preserve order of array elements after join

Order of rows in subqueries

To address your comment:

  I learned that order of rows in a subquery is never guaranteed to be preserved in the outer query.

Well, no. While the SQL standard does not offer any guarantees, there are limited guarantees in Postgres. The manual:

  This ordering is unspecified by default, but can be controlled by
  writing an ORDER BY clause within the aggregate call, as shown in
  Section 4.2.7. Alternatively, supplying the input values from a sorted
  subquery will usually work. For example:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

  Beware that this approach can fail if the outer query level contains
  additional processing, such as a join, because that might cause the
  subquery's output to be reordered before the aggregate is computed.

If all you do in the next level is to aggregate rows, the order is positively guaranteed. Any yes, what we feed to the ARRAY constructor is a subquery, too. That's not the point. It would work with array_agg() as well:

SELECT c.identifier, p.persons
FROM   class c
CROSS  JOIN LATERAL (
   SELECT array_agg(identifier) AS persons
   FROM  (
      SELECT identifier
      FROM   person
      WHERE  class_identifier = c.identifier
      ORDER  BY name DESC
      LIMIT  2
      ) sub
   ) p
ORDER  BY c.identifier;

But I expect the ARRAY constructor to be faster for the case. See:

Why is array_agg() slower than the non-aggregate ARRAY() constructor?
convert right side of join of many to many into array


update table with replace string1 string2

See http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks for a technique of chunking a table.  It is aimed at DELETE, but can easily be adapted to UPDATE.

This should avoid the timeouts and hangs.


Multiple Instances in AlwaysOn Availability Groups - Resource Balancing

I am already working on a solution to re-balance the resources in the event of a failover, so no need to account for that in your reply.

That's actually the main thing that drives the reply.  If you don't sharply limit each primary instance it will acquire an unfair share of the server memory, and you may have severely degraded performance of the other instance after failover.
If you rebalance by setting max server memory for both instances in the case of a failover, you may run without limiting it on each node normally.
I would not limit the CPU affinity or maxdop on either instance.  The OS allocates CPU resources very dynamically and fairly, so unless you observe one isntance being starved by excessive CPU utilization from the other instance, just leave those at the default settings.


What does it mean when I see a lock in sys.dm_tran_locks and there's no matching row in sys.dm_exec_requests?

Two more additional filters are necessary for your first query.

1- You need to execute query for user databases only i.e. resource_database_id > 4
and
2- Session status "sleeping" mean, session is currently running no request, so exclude such sessions from your result. i.e. [sessions].status  <>'Sleeping'

select sessions.session_id,* from
   sys.dm_tran_locks locks inner join  sys.dm_exec_sessions sessions
      on locks.request_session_id=sessions.session_id
WHERE locks.resource_database_id > 4
AND [sessions].status  <>'Sleeping'


Please pay attention to the raid status: VERIFYING. The progress is displayed as %V/I/M. You can see the verify schedule with the show verify command parameter. As long as the raid is being verified I assume the io performance isn't great. You should use iostat or vmstat to get a better picture what is happening.

The cache is enabled for your raid unit. You might want to test if the overall performance is better if you only enable the write cache but disable the read cache
Furthermore the bbu status seems to wrong - the battery has 0 hours estimated backup capacity.


PostgreSQL : Restrict (another) superuser from accessing database

francs is entirely correct. superuser means just that. They're all-powerful. They can do anything, including load additional code into the database, modify tables on disk directly, etc. See CREATE ROLE and the documentation on client authentication for more information.

If you don't trust them, don't give them superuser rights. In this case, it sounds like you should've just done a CREATE DATABASE movies WITH OWNER the_other_user and given them a normal, non-superuser login. Or if they need to create their own databases, you could give them CREATEDB rights.

The only way to restrict a superuser is by changing the C code inside PostgreSQL directly. Even then you'd probably be wasting your time, as a determined user could get around restrictions like a ProcessUtility_hook filter if they have superuser access.

Remove their superuser access. Unless they've had the foresight to backdoor your system in a way that'll let them regain access (unlikely, and not trivial) you should be OK.

ALTER USER the_user WITH NOSUPERUSER;

You can add CREATEDB rights if you want them to have the ability to create databases.


How will you map the permission control between two entities?

One approach I thought of is to create a table

producer_consumer_allowed (producer_id, consumer_id).

A consumer is allowed to consume food from a producer if an entry for both this producer and consumer exist in this table.

Whenever we add a new producer, we do not need to do anything since the default state of consumers-producers is No.

Whenever we add consumers, we also do not need to add entires into this table since the default state is also No.


IMHO, I would not gather stats on indexes, I would gather stats on tables with cascade=>TRUE. But in 11g if you enable to maintenance job, it should gather stats for you, so unless you need stats generated a certain way you are probably ok with the default stats jobs that runs in the maintenance program.


MongoDB MMS replica set fails to restart

So, it turned out that issue was in .ns file of one of the database. I had to delete that file and restart the server. Server started successfully however, the database whose .ns I deleted was lost.

For newbies in mongoDB .ns file is the namespace file that mongo creates for each database in its data (/data) directory.


In a one-to-one relationship, where should the foreign key be placed?

It is acceptable to have mutually exclusive attributes in a table.  If you only have one pair of such attributes, this may be the most practical solution.

However, some people may look at your situation as a sub-typing issue.  In this view, you are missing the superset entity.  You have a collection of (something), some of which are employees and some of which are customers and some of which are also users.

Have you also considered what happens if an employee also happens to be a customer?  Is that important to your system or is that something that would be considered coincidental?

If you think its important to treat all users as a feature of one type of thing, then you need to implement the (something) which is currently missing from your table definitions.

Consider the following ERD:

If you supply the missing superset entity, then every employee is one of these, as is every customer, and also therefore every user.  The hard part of this is coming up with a reasonable name for this superset, but it solves the issue of having to figure out which foreign key to follow from user.  It also makes a connection between employee and customer should one happen to exist, and should that connection be of interest.


How to use sequential UUID primary key for range query by date

The code for such is here, but it only works for "Type 1" UUIDs.  It's a matter of shuffling the bits around.  And while you are at it, BINARY(16) is more compact than VARCHAR(36) or whatever you have now.

More

By rearranging the bits of a type-1 UUID, you can take advantage of "locality of reference" when using an index on it.  For example, if you had News articles.  Users tend to look at recent articles and not touch the old ones.  New rows will be cached; old ones will drift into oblivion.

A suitably rearranged UUID would provide the equivalent of ORDER BY datetime.


select duplicated rows by modified_date - PGSQL 9.2

Here is what I did:

CREATE TABLE junk.ja_jobs_WM (
        recurrenceid integer,
        time_job int8,
        time_job_stamp timestamp,
        dupcount integer,
        most_recent_date timestamp
);

INSERT INTO junk.ja_jobs_WM
    SELECT
                recurrenceid,
                time_job,
                TO_TIMESTAMP(time_job),
                COUNT(id) as dupcount,
                MAX(modified_date) AS most_recent_date
        FROM
                ja_jobs
        WHERE
                (NOT (recurrenceid IS NULL))
        AND
                deleted = false
        AND
                (clientid = 15288)
        AND
                creatortype = 'legacyrec'
        AND
                (time_job >= EXTRACT('epoch' FROM statement_timestamp()))
        GROUP BY
                recurrenceid,
                time_job
        HAVING
                COUNT(id) > 1;

This question is now closed.


How is the new system stored procedure sys.xp_delete_files different from sys.xp_delete_file?

To start with, the older xp_delete_file (also undocumented) is quite limited:

It can only delete either backup (.bak / .trn) or report (??) files (I believe it actually scans the first part of them to verify the file "type"
It only deletes by file extension (i.e. not specific files, or whatever matches based on wildcards) and based on being older than the supplied date
It's picky and requires a trailing slash on the directory / folder
It can only delete files, not folders
It can recurse down through subfolders (ok, so this one is not a limitation)

For more info on it, please see:

How to Use xp_delete_file to Purge Old Backup Files
SQL Server xp_delete_file not deleting files

The newer sys.xp_delete_files has the following syntax:
EXEC sys.xp_delete_files 'fileSpec.01' [, 'fileSpec.02' [, ...] ] ;

Notes:

File type / extension does not matter
Works with standard DOS wildcard characters:

* = zero or more of any character
? = exactly one of any character

Does NOT recurse through subdirectories (for deleting files) (this is the only "limitation")
Can specify multiple, fully-qualified path specifications, each being able to handle wildcards
Can remove entire non-empty subfolder structure!! (might need to run a few times to delete everything)
Must be a member of the sysadmin fixed Server Role in order to execute (use Module Signing instead of adding application Login to sysadmin fixed Server Role; please see: Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level )
Backported to SQL Server 2017 (possibly in CU18)

For complete details, please see my post:
sys.xp_delete_files and ‘allow filesystem enumeration’: two new undocumented items in SQL Server 2019


How to get Super Priviledge for a database on an online server

I question whether you asr asking the correct question:

Are you bbamas_cat@localhost?  If so, leave out the DEFINER clause.
Do you really want the VIEW to be run only as bbamas_cat@localhost?  Usually one wants to run things as root or as INVOKER.

To get SUPER, normally you connect to MySQL as the user root.  I say "normally" because that is nothing to force root to be GRANTed SUPER, nor is there anything to prevent other users from being GRANTed SUPER.


Why didn't MySQL keep the innodb_file_format after loading a mysqldump

According to the MySQL Documentation on

innodb_file_format

  The file format to use for new InnoDB tables. Currently, Antelope and Barracuda are supported. This applies only for tables that have their own tablespace, so for it to have an effect, innodb_file_per_table must be enabled. The Barracuda file format is required for certain InnoDB features such as table compression.

innodb_file_format_max

  At server startup, InnoDB sets the value of this variable to the file format tag in the system tablespace (for example, Antelope or Barracuda). If the server creates or opens a table with a “higher” file format, it sets the value of innodb_file_format_max to that format.

Since each of these belong to my.cnf and not the data, the format of InnoDB is agnostic as far as the mysqldump goes. If you want to shift everything to Barracuda on the fly, there are two ways (TEST WHAT I AM ABOUT TO SUGGEST ON A DEV AND STAGING SERVER, PLEASE)

TECHNIQUE #1 : Prepend the Barracuda Option to the mysqldump

Suppose the mysqldump file is called mydump.sql. Do this:

echo "SET GLOBAL innodb_file_format = 'Barracuda';" > mynewdump.sql
cat mydump.sql >> mynewdump.sql

Now, just load the dump

mysql -uroot -p < mynewdump.sql

TECHNIQUE #2 : Set the Barracuda Option Before Loading the Dump

Login to mysql as root@localhost and run this from the mysql prompt

mysql> SET GLOBAL innodb_file_format = 'Barracuda';
mysql> source mynewdump.sql

WARNING

TEST THE SUGGESTIONS ON A DEV AND STAGING SERVER, PLEASE

Give it a Try !!!


How to index a many to many table most effectively

a clustered index on (userid, roleid)
another index on (roleid, userid)

You don't need a surrogate key (unless you use a braindead ORM) and you almost always need the 2nd index


The short answer is yes. It will use a thread per connection by default which you can delegate queries to concurrently, which will utilize multiple cores on the operating system level.

Slave throughput can be dramatically improved by adding cores using parallel replication which is not enabled by default. https://mariadb.com/kb/en/library/parallel-replication/


MariaDB 10 replication strange behaviour

Ensure you have --log-slave-updates and --log-bin enabled in the servers.

  Normally, a slave does not write to its own binary log any updates
  that are received from a master server. This option causes the slave
  to write the updates performed by its SQL thread to its own binary
  log. For this option to have any effect, the slave must also be
  started with the --log-bin option to enable binary logging. Prior to
  MySQL 5.5, the server would not start when using the
  --log-slave-updates option without also starting the server with the --log-bin option, and would fail with an error; in MySQL 5.6, only a warning is generated. (Bug #44663) --log-slave-updates is used when
  you want to chain replication servers. For example, you might want to
  set up replication servers using this arrangement:

  A -> B -> C

  Here, A serves as the master for the slave B, and B serves as the
  master for the slave C. For this to work, B must be both a master and
  a slave. You must start both A and B with --log-bin to enable binary
  logging, and B with the --log-slave-updates option so that updates
  received from A are logged by B to its binary log.

Source: http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_log-slave-updates


Primary filegroup is full SQL Server 2008

Follow these steps:

Identify how much space you want to add to the database storage allocation:

Open windows explorer
Right click on the disk drive that your database files exist on
Select properties
Check how much disk space is available and decide how much of this you want to allocate for the database
(Suggestion: Leave at least 20% disk space free if you house the database files on the same disk as your OS {Sub-Suggestion: Don't do this! Rebuild/migrate your data to it's own disk; you're screwing yourself on I/O.} and leave at least 8% for a pure data disk; these numbers are estimates of what I think the actual percentage suggestions are.)

Update the storage allocation for the database.

Open SSMS
Click the "View" tab
Select "Object Explorer"
Expand the "Databases" folder
Right click the database your trying to bulk insert into
Select "Properties"
Click the "Files" list option from the "Select a page" area at the left of the properties window
Find the "Database files" row with the "Filegroup" as "PRIMARY"
Add whatever number of megabytes you want to add to the database allocation to the "Initial Size (MB)" number
Hit "OK"  (You might also want to consider your "Autogrowth" values while you're here.)

You want to give your database as much storage allocation as you can afford to give it. If it runs out of space you'll receive this error without auto-grow on and if auto-grow is on you'll take a performance hit each time it has to auto-grow. If you are simply out of disk space then that is your answer and you need a bigger disk.


Recursive CTE to get Grandparent, parent, children

I believe this CTE can give you the desired result:
;WITH hier AS
(

    SELECT DISTINCT h1.ParentPart, h1.Component,
        h1.ParentPart AS GrandParent
    FROM #Hierarchy h1
        LEFT JOIN #Hierarchy h2 ON h1.ParentPart = h2.Component
    WHERE h2.Component IS NULL

    UNION ALL

    SELECT h1.ParentPart, h1.Component, h.GrandParent
    FROM #Hierarchy h1
        INNER JOIN hier h ON h.Component = h1.ParentPart

)
SELECT GrandParent, Component
FROM hier
ORDER BY GrandParent, Component;

Since you're only interested on the highest parent in each relationship chain, I used the trick of adding a third column on the anchor member containing what would be the great-great-grandparents. With that I can relate what value was the grandparent for each level of the chain no matter how many levels I descend.


How does one select the histogram / scatterplot / etc. Data Viewers in SSIS 2012

Discontinued Integration Services Functionality in SQL Server 2012

The options to configure a data viewer to display data in a histogram, scatter plot, or column chart have been discontinued in this release.

Data Viewer :

Previous versions of SSIS included four types of data viewer—the Grid, Histogram, Scatterplot, and Column Chart data viewers. Because the Grid data viewer is the most commonly used, the SSIS team removed the other types of data viewers from SSIS 2012.


Do I lose query performance by using same filegroup for all partitions?

At least for SQL Server, using multiple files or multiple filegroups on the same set of disks should have no significant impact on performance.

If you have separate drives mounted and want to spread the IO across them, then multiple files or multiple filegroups can help you do that.  But that's a separate issue.


How to connect to remote MySQL server with sshuser and mysqluser being distinct

First you need to do SSH user@serveripaddress then click enter you will be prompting to enter password. please enter the same.

After that use mysql -u root -p then click enter you need to enter mysql password. If mysql is not installed in your server. Please install then get root password from DBA and then try.


PGAdmin3 connection with non standard SSH port and SSH tunnel

You could setup the ssh connection and tunnel outside pgAdmin and just let pgAdmin connect to the localport.

On windows you could use putty for this on unixes you can use ssh

If you use 15432 as localport then you can let pgAdmin connect to localhost with port 15432 and you leave the SSH tunnel option in pgAdmin OFF!


SQL server windows authentication through network

Do the username and password match between the network pc and the sql machine?  I also found this link regarding, xp home. I am not sure what version of XP you are using.

https://serverfault.com/questions/54761/pass-through-authentication-does-not-work-with-a-specific-account-in-windows-xp


Does partitioning sometimes require denormalization?

Yes, if you want to partition by CustomerID, then CustomerID must exist in the table (even if it is redundant).

But first, IMHO, you need to ask yourself a couple of questions:

Do I need partitioning? What am I using it for? What problem am I solving?
If the answer to 1. is yes, then am I partitioning on the right column? How - specifically - will partitioning on CustomerID help in my environment?

I find that a lot of people turn to partitioning as a knee-jerk reaction to a performance problem of some kind that partitioning simply can't (completely) solve.

Similar to indexed views - people create views on top of views on top of views, then assume that all they need to do is create an index on each view and that will speed everything up. Then they actually learn the hard way about what indexed views are, how they work, and the much more limited set of scenarios that comprise their "sweet spot."


MySQL - How do you enter a record with only whitespace " "?

You should use VARCHAR instead of CHAR for data type, if you want to store spaces. Space (whitespace) retention is documented in MySQL online documentation:

  The CHAR and VARCHAR types are similar, but differ in the way they are
  stored and retrieved. They also differ in maximum length and in
  whether trailing spaces are retained.


Always On Availability Group, Always redirect user to read only instance

I don't have an answer to your entire question (though I gave an answer to a similar question today https://dba.stackexchange.com/a/137844/36812) but you mentioned that using ApplicationIntent=ReadOnly doesn't work properly.

Have you set up read only routing URLs? Because it's not done out of the box and if you don't do it then these settings and that flag won't work. I think if you got that working then you could start to re-evaluate your requirements.

Instructions on MSDN https://msdn.microsoft.com/en-us/library/hh710054.aspx and most easily done in PowerShell.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica


Simulate production workload on test environment

You have a few options. There are third party tools that can help simulate a load against your database. There are open simple utilities like SQL Query Stress which can run whatever queries you supply it in whatever frequency you wish.

There is also the combination of running server side traces and replaying those traces. This can be as simple as gathering scripts from a trace (as Dan rightly pointed out above don't use the Profiler GUI to capture events, but capture the trace to a file using server side trace) and then replaying the workload if you more care about individual query performance if you care more about individual query performance and less about concurrency.

If you need to dig into concurrency and see the workload in real time from multiple angles - you would want to look at using the Distributed Replay features. This allows you to point to a trace in any version of SQL Server back to SQL Server 2005 and run it against any version of SQL Server, also back to SQL Server 2005.  Jonathan Kehayias has a step by step walk through of how to setup Distributed Replay. This would allow you to configure the replay from multiple clients distributing the load - a single profiler replay would never allow you to scale. (Please note - you'll see the tool is a SQL Server 2012 tool. Don't let that scare you, the tool came out then, but can deal with your SQL Server 2008 R2 environment)

All this said - there is complexity to this either way. Taking a trace for replay has some requirements and steps. Replaying it also has some steps to take to make sure you are starting from the same starting point, etc. This complexity is worth understanding, it can help perform upgrade readiness tests, help understand hardware changes on total application, etc. You could use the RML utilities to analyze the before and after traces also which can help show you how things improved or didn't after.

If you just want to see if rebuilding indexes made any changes, you can do simpler tests also. You could take a large repeatable insert and run only that before and after. You could take a few queries with high scans and a few with high seeks and run that before and after. I would make sure you still rebuild statistics either way, though, since rebuilding your indexes does rebuild the index level statistics and that sometimes gives more of a benefit than rebuilding indexes and people attribute the benefit to the index rebuilds rather than the index statistics that got rebuilt. I tend to fall into the "rebuild" camp more often than not, but hardware changes in IO performance, workload patterns, etc all make that not a hard and fast rule necessarily for me.


The total number of locks exceeds the lock table size, even after increasing buffer pool size

Try this in my.cnf file,

[mysqld]
innodb_buffer_pool_size = 80M

And you need to restart the Workbench as well as the Sql service.


How to CREATE TABLE ... LIKE without the NOT NULL Constraints?

Try this:

CREATE TABLE my_table_bk
AS
  SELECT *
  FROM my_table
  WHERE false;

Or, you can append LIMIT 0 instead of the WHERE clause.

This will create a table my_table_bk with the same structure as my_table without constraints and without data.


Will my SQL query use stale data? How can I prevent it?

If you're using the default isolation level in SQL Server (Read Committed), then you certainly can run into all sorts of issues around inconsistent reads. Paul White describes the problems here.

If you want your read queries to read data which is fully consistent to how it looked at a given point in time, I'd recommend that you consider Read Committed Snapshot Isolation (RCSI). With RCSI, your query will return data that is consistent to a single point in time (the start of your query).  If User A starts a SELECT query while User B is concurrently performing updates, User A will read the "old" value because it will read a snapshot of the data, which is consistent to the start of the query.

The catch with RCSI is that it's a database-level setting. Unlike Read Uncommitted, you can't set it as a session-scoped setting. You'll have to consider this change more globally before making the change. However generally speaking, if you require consistent reads for this query, you probably want consistent reads for the entire application.

While the Repeatable Read isolation level may look appealing to solve your problem, but note this detail from the linked post:

  The repeatable read isolation level provides a guarantee that data
  will not change for the life of the transaction once it has been read
  for the first time.

This means that the data can still be changed prior to being accessed, but during the time your query is running. It is also subject to some of the same inconsistent reads as the Read Committed isolation level--notably phantoms.


Meaning of 'In current row format, BLOB prefix of 768 bytes is stored inline.'

If your ROW_FORMAT is REDUNDANT/COMPACT, the prefix (upto 768 bytes) of every blob/varchar/text is stored in the index records and remaining bytes are stored in Off-pages. So there is limitation on No of variable length columns in a table (upto 10 can be created in utf8 file format). Whether in DYNAMIC/COMPRESSED row formats, these fields are fully off-paged (prefix 0 byte). So the limitation is relaxed so that upto ~196 Text/Blob can be created in a single table.

Note :
Verify whether the table row format is DYNAMIC/COMPRESSED

  SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESWHERENAME='dbName/tableName'\G

And verify FILE_FORMAT = Barracuda.


SQL Server Licensing & Clustering for Availability Groups

In SQL Server 2012 and 2014, the Availability Groups feature requires an underlying WSFC (see this post for info on newer versions). You can use FCIs in combination, but it is not required (and in fact often overkill, depending on what you are trying to accomplish and whether you have underlying redundancy as well).
WSFC is the underlying Windows Server Failover Cluster. FCI is just the terminology for a single SQL Server failover cluster instance. An FCI depends on a WSFC, and can use 1-n nodes of the WSFC to provide failover scenarios for the clustered instance. Some good background here from Allan Hirt. Basically, an FCI protects a single, entire instance of SQL Server (when a failover happens, the entire instance moves to the next preferred owner in the WSFC). A WSFC protects an instance of Windows, and any SQL Server instances on it. An Availability Group uses the underlying WSFC to protect a group of databases.
In 2012 / 2014, Standard Edition does not support Availability Groups in any shape or form - Enterprise Edition is required. In SQL Server 2016 and above, there is "Basic Availability Group" for Standard Edition - 2 nodes only, 1 database per group, no formal listener support, no read-only secondaries, etc. Official Documentation...


How to grant xp_delete_file to non-sysadmin login?

Deleting files on NAS should not be done by sql server. You should rather use powershell to do it.

You can use my function Clean-NASOldFiles which uses robocopy to scan files extremely fast and will then delete based on your input.


Which normalization rule violates the column-per-language approach?

I would classify it as breaking NF2.  The language dependent columns are dependent on the Primary key and the Language.  I normally model these in a child table keyed on the Primary key of the parent table, and a language code (integer or ISO language code).

The disadvantage of having a child table is that it is difficult to ensure all the languages are provided.  This can be an advantage as you can save the record before you have all translations available.  In some cases, I have used language preferences to retrieve the best (by preference) alternate language for the description.  (HTTP has a header that allows language preferences to be specified.)


Applying Service Pack 3 in SQL 2012 in Windows 2012 R2

Do I need to remove the possible owner node in cluster while applying SQL Service Pack 3 in SQL 2012 in Windows 2012 R2 .

I suppose you are referring to This support article. The purpose of this support article is if you are trying to install Service pack on cluster without disturbing the current cluster configuration(active nodes) and you want to keep things online.

I suggest take application downtime and apply service pack in that case you don't have to remove any possible owners from the list. I always consider this better way of applying SP, although MS provides you way to update the passive node of cluster without disturbing active node.

This article also helps you when you add node to cluster with RTM version while cluster is on higher SP level, then you have to apply SP on passive node in that case after adding node make sure the added node is not in possible owner list and go ahead and apply patch on passive nodes added so as to make it identical to all nodes.


How to use single domain name to connect to multiple database servers?

Assuming your intent is for the application code to connect and use the proxy as if it were a SQL instance (i.e. connect using a SQL API and executes queries), don't go there.

Consider creating a DNS alias entry (CNAME record) for each database (not instance) and specify that name in the connection string along with the database name. That will provide abstraction over instance hosting the database so database can be moved with no connection string changes.

Example with 2 SQL instances hosting 3 databases:

db1.yourdomain.local -->instance1.yourdomain.local
db2.yourdomain.local -->instance1.yourdomain.local
db3.yourdomain.local -->instance2.yourdomain.local

Example connection string:

Data Source=db1.yourdomain.local;Initial Catalog=db1;Integrated Security=SSPI;


Interpret the CPU usage in SQL trace file

These values are represented in terms of time. This value could either be in millisecond or microsecond. For CPU - it would be number of milliseconds used by this particular process.

This has been explained by Mr. Brad McGehee in a very detailed way with examples.

CPU

  This data column shows the amount of CPU time used by an event (in
  milliseconds). Obviously, the smaller this number, the fewer CPU
  resources were used for the query. Note that CPU data column is only
  captured for the RPC:Completed, SP:StmtCompleted, and the
  SQL:BatchCompleted events.

Reads

  This data column shows the number of logical page reads that occurred
  during an event. Again, the smaller this number, the fewer disk I/O
  resources were used for the event. Note that Reads are only captured
  for the RPC:Completed, SP:StmtCompleted and the SQL:BatchCompleted
  events.

Writes

  This data column shows the number of physical writes that occurred
  during an event and provides an indication of the I/O resources that
  were used for an event. Again, Writes is only captured for the
  RPC:Completed,  SP:StmtCompleted and the SQL:BatchCompleted events.

Please find the below notes from Microsoft on this:

Note

The server reports the duration of an event in microseconds (10^-6 seconds) and the amount of CPU time used by the event in milliseconds (10^-3 seconds). The SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.

Same can be summarized here:

I hope above helps.


Microsoft SQL Server AG Synchronous Mirroring is not synchronous

This is completely expected. I've talked about this before but I'll recap it for you.

There could be multiples reason you are seeing this behavior. The two that most people are confused or inaccurate of how it works are how availability groups synchronize data and how queries on readable secondary replicas work.

Please note the below is ONLY pertinent for SQL 2012 and 2014, and to some extent 2016.

How Data Synchronization Works (brief overview)

There are two types of replica synchronization, synchronous and asynchronous. The way the data synchronization happens in both is exactly the same. The way that SQL Server behaves, though, is different. When synchronous is used we wait for the data to be HARDENED on the secondary replicas. This means it only needs to be acknowledged that it was successfully written to the log, not that the log block shave been successfully redone. Asynchronous does not wait for the status message and just continues.

Thus it is entirely working properly, however there seems to be a misunderstanding as to how it works. AGs ship log blocks, not transactions, thus the entire transaction may not be shipped together and may not even be redone yet.

Querying Readable Secondary Replicas

When you run a query on a secondary replica, the read committed (default) isolation level is silently mapped to snapshot isolation under the covers... whether or not you have SI or RCSI enabled for the database.

Since snapshot works by row versioning and is consistent from the beginning of the transaction, you may not be able to see new data as it comes in per how snapshot isolation works. This is entirely working as intended.

The other point to keep in mind is that just because you have the acknowledgement that the data was hardened to the log on the synchronous secondary replica does not mean that the REDO thread has redone those log blocks yet. Thus, just because you have an acknowledge on the primary doesn't mean REDO has completed on it, only that it has been hardened to the log. Additionally, if your redo thread is blocked it may take a long time (or never) to not be blocked and thus your redo queue size will grow.

In the end I can totally expect the behavior you are witnessing, however it doesn't mean that it isn't synchronous. It just means it doesn't necessarily work the way it was believe or thought to. Hopefully this clears the confusion.


The information seems to be inaccurate.

Oracle has only three numeric data types:

NUMBER: binary coded decimals. Slow, but exact.
BINARY_FLOAT: IEEE single precision floating point number. Fast and quite imprecise.
BINARY_DOUBLE: IEEE double precision floating point number. Fast and less imprecise.

You can use INTEGER, DOUBLE PRECISION, REAL and FLOAT in Oracle, like the SQL standard demands, but you will always get a NUMBER (FLOAT is NUMBER, only the precision is given in binary rather than in decimal digits).

PostgreSQL has more numeric data types:

numeric (or decimal, which is just another name): binary coded decimals. Exact, but very slow.

This corresponds to Oracle's NUMBER.
real (or float4): the platform's native single precision floating point numbers, usually IEEE

This corresponds to Oracle's BINARY_FLOAT.
double precision (or float8): the platform's native double precision floating point numbers, usually IEEE

This corresponds to Oracle's BINARY_DOUBLE.
integer (or int4): four-byte integers

Oracle has nothing like that.
bigint (or int8): eight-byte integers

Oracle has nothing like that.


can any tweaks improve full table scan time in Postgres/Cloud SQL?

Obviously faster storage and more RAM for caching can improve performance of a sequential scan.
Other than that, you can increase max_parallel_workers_per_gather on recent PostgreSQL versions to have more processes work on the query. That reduces the execution time at the expense of more load on the database.


Yes, a PARTITION is a TABLE.  However, it takes some special commands to turn a partition into a table or vice versa.
See "transportable tablespaces" and FLUSH TABLES ptable FOR EXPORT; ALTER TABLE ptable DISCARD TABLESPACE; ALTER TABLE ptable IMPORT TABLESPACE.  The details are different between MySQL 5.6 and 5.7+.
http://mysql.rjweb.org/doc.php/partitionmaint#use_cases_for_partitioning has links to a few places that discuss the techniques.


Deployment Manager Vs DBA Cage Match On Pushing Releases To Prod

Firstly, every environment is different. What works in my environment won't automatically work in yours. (We = DBA Team)

In mine we have the following environments for this process

Dev - Normally we restore a copy of live in preparation for a release and sanitise data as required. We then back that up and enable the dev to restore to that backup at will. They then prepare and test their deployment scripts. We expect the Devs to have tested the upgrade/release process in full successfully before it needs to go to UAT. We will often be required to help and advise at this stage

UAT - Again we would restore the copy of live and sanitise (if we are going to use a QA environment). The release and config manager then provides us with the correct scripts for the upgrade from the developers and we will check them, run them whilst monitoring to be able to provide all info required for CAB regarding loss to service, time to upgrade and any changes required to maintenance/monitoring scripts which we then advise the Release Manager.

Sometimes we use a QA environment, usually when sanitised data has been used in the two environment above and the business are required to QA rather than the test team.

CAB - The Release Manager provides all the information for CAB to make their decision from the proceeding steps to lay out exactly what happens during the release. They are also responsible for storing the scripts used to go from Dev to UAT and providing them to us for release into live. We only use the scripts provided by the Release Manager and referenced in the CRP. We obviously check that the scripts are the same as the ones we used in UAT as I am ultra cautious

Live - We deploy to live exactly following the steps in the Change release Plan authorised by CAB, often co-ordinating with other teams

The reason we do it like this is partly as it is the process that we have to follow here (Using CAB etc) but in answer to your question it is because the DBA team are the ones responsible for the safety and accessibility of the data. Also because we have the skills, knowledge and experience to recover from unexpected problems. What happens to the Live databases is our responsibility and we take it very seriously. No-one else is allowed to perform tasks on the live databases other than the DBA team. We are the guardians and the bouncers (door men/women)

For your update. I bounce that sort of issue back to the developers and require them to fix it before I deploy to UAT. Once the script is finalised (at UAT) we know it will work in live.

For your another example - That is the job of the release manager. If they are providing us with scripts that fail we bounce it bak to them and require them to fix it before we will deploy into UAT. We WILL NOT deploy into UAT anything that requires bodging or changing from the instructions provided and in the few occasions wen this has happened (a developer used the app account to manipulate some data after we had run the scripts to enable the release to pass UAT) I stand up in CAB and explain this and explain that I am not happy to release this to live without a correct test of the release. This usually works :-) I know I am harsh but I am also fair.

I tell the release managers and the devs that the process of passing the release to UAT is the test not only of the release but of how the release will be performed therefore we will follow the exact same steps in both examples (dev - UAT and Live) and that if it fails in UAT it goes back to them to fix.

I hope that helps


If the article_id is the same in both tables, and you are looking for the same article in both tables, then you could use an INNER JOIN to join the two tables, using the article_id column.

SELECT ar.given_rating as rating, sa.saved_by as saved
FROM article_rating  ar
INNER JOIN saved_article sa
ON ar.article_id = sa.article_id
WHERE ar.given_by=? AND sa.saved_by=? AND ar.article_id=?;

DB-Fiddle


Sqlite, DB clients & BLOB's: Practical DB Use

Following up on the comments, one option to simplify this requirement without resorting to developing a user interface to link sound files and a database, and considering your skills in shell programming, is using command line tools to edit/retrieve tags on MP3 files (Here's a QA with plenty of resources and alternatives for reading and manipulating mp3 tags from the command line), and using XMMS2 or similar to build and maintain the library of files, and see or manually edit individual file's MP3 tags.

The benefit of this approach is that XMMS2 is not only very good at handling a library of sounds/music (including creating playlists and so forth) but also in editing MP3 tags, which can be manipulated from command line scripts through a variety of open source well documented and popular tools.


SQLite, update one row with another row data

For MySQL (the question is MySQL-tagged)
UPDATE table t1, table t2
SET t1.field1=t2.field1, t1.field2 = t2.field2, ...
WHERE
      /* copy one record                     */
/*    t1.id = 123 AND t2.id = 456                       */
      /* copy a bunch of consecutive records */
/*    t1.id BETWEEN 111 AND 123 AND t2.id - t1.id = 321 */

For SQLite (the question is SQLite-tagged too)
UPDATE table
SET field1 = ( SELECT field1
               FROM table t
               WHERE t.id = 123 )
   /* or       WHERE t.id - table.id = 123     */
WHERE id = 456
/* or      WHERE id BETWEEN 444 AND 456        */


The REPLACE built-in function does not support patterns or wildcards; only LIKE and PATINDEX do.

Assuming that you really just want the simple single-character replacement as shown in the question, then you can call REPLACE twice, one nested in the other, as follows:

SELECT REPLACE(
               REPLACE('A B x 3 y Z x 943 yy!',
                       'x',
                       'q'),
               'y',
               'q');

Returns:

A B q 3 q Z q 943 qq!

If you do need more complex pattern matching / replacement, then you will need to do that via Regular Expressions, which can only be done via SQLCLR. Several SQLCLR RegEx functions are available in the Free version of the SQL# SQLCLR library (which I wrote), one of them being RegEx_Replace[4k]() (the 4k version is for when you are certain that you will never need more than 4000 characters, hence you can get better performance from not using NVARCHAR(MAX) as an input parameter or return value).

The equivalent of the two nested REPLACE calls would be done as follows (and using the pattern syntax shown in the question):

SELECT SQL#.RegEx_Replace4k(N'A B x 3 y Z x 943 yy!', N'[xy]', N'q', -1, 1, NULL);

Returns:

A B q 3 q Z q 943 qq!

But, since we are talking about the possibility of a more complex pattern, one that cannot be done easily in T-SQL, we can use a quantifier on the pattern to have it replace any number of contiguous x or y characters with a single q:

SELECT SQL#.RegEx_Replace4k(N'A B x 3 y Z xx 943 yyxxyxy!', N'[xy]+', N'q', -1, 1, NULL);

Returns:

A B q 3 q Z q 943 q!

Please note that the input string was changed slightly from the previous two examples to add an extra x after the Z, and to add an extra xxyxy to the yy at the end. And, in both cases, the multi-character fragment was replaced with a single q.

For more info on working with strings and collations, please visit: Collations Info


Correct composite indexing indexing order

I think your approach with nested loops is suboptimal.
Why can't you do something like:
select * from sensors where deviceid =? AND  pcode = ? AND rectime between ? and ?

This would return the whole dataset and you  could process it locally.
Selecting 500 or even more rows in one correct select is better then 500 single row selects.
In this case I would change the the clustered index to
(deviceid,pcode,rectime)

you can still keep the autoId primary key, just make it nonclustered.
In addition, the way your select works, it has to do key lookup afterwards to get one single extra row. MySQL AFAIK doesn't support include on indexes, but even then, it would be a de facto reordered copy of the table so clustered index makes sense.
As for if the order matters.... Well, yes and no. Given your datatypes and size of table, the difference would be minimal.