How do I allow remote mysql access to all users?

I highly recommend that you do not run

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';

This user has the SHUTDOWN privilege, which can allow the user shutdown mysql remotely with

mysqladmin -hIP_of_DB Server -uusername -p shutdown

You also do not want the SUPER privilege given remotely to just anyone. Why ?

  The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

If you have multiple users and want them to access specific databases, you need to create the user with database-level privileges. The SHUTDOWN and SUPER privileges are not part of database-level grants (See my post MySQL user without SUPER privilege as to why)

Suppose you have a database called mydb and you want to access it remotely. Do this:

GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'%';

I have a better idea for you. You should restrict remote users by their netblocks

GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.20.%';
GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.20.30.%';
GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.20.30.40';

Note that you will have to drop 'username'@'%' first before creating DB-level user access.

See my post MySQL error: Access denied for user 'a'@'localhost' (using password: YES) for full clarification on MySQL's User Authentication Paradigm

After you have created the needed users, make sure your firewall has port 3306 open.


modeling data dimensions as columns - some dimensions are highly variable while others have low variability

I would suggest creating a separate dimension for your high variability columns. This is a Type 4 mini dimension according to Kimball methodology.

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-4-mini-dimension/

Hope this helps.


Is it possible to retrieve the specific databases selected in a maintenance plan step?

I don't believe there is a way.  If you create a Maintenance Plan, it is going to be an SSIS package.  Querying the msdb.dbo.sysjobsteps table, you can view the actual command, but for an SSIS package execution it won't be indicative to the particulare databases you are backing up:

select
    step_name,
    subsystem,
    command
from msdb..sysjobsteps

This will give you something similar to the following command:

/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\MaintenancePlan" /set "\Package\BackupDBs.Disable;false"


Master Key vs Password Certificate Encryption in SQL Server

We can encrypt certificates in SQL both by the database master key, and the other option is by password.

You can do both at the same time, which is preferable.

  I want to know the advantages and disadvantages of DMK encryption.

The big two are:

If you forget the password or can't use the SMK (service master key), you can still open the Certificate (think AG, DBM, etc.)
You can use Transparent Decryption which removes the need to specifically open by password


When to make changes to the cost threshold for parallelism

Using MAXDOP = 1 can be a help, but it is a big gun.  It may be that the actual problem is the usefulness of the indexes.  Perhaps a new or a different index would resolve the problem.

Following Mr Denny and Aaron Bertrand's comments, did you discover what other waits in that connection were likely the cause of the CXPACKET waits?

Jonathan Kehayias suggested a query that might help you assess you parallelism experience and make a more thoughtful decision.  But you should also read the conversation between Jonathan and Paul White.

https://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/


Is there a way to use a Case statement to read from one field and change another field?

I don't see why you'd even need a case statement for this. Assuming your table is called myTable in the dbo schema (you didn't specify, but it isn't particularly important) and we're talking about updating the field in the same row.

UPDATE dbo.MyTable
SET Form_Factor = 'Server'
WHERE Category LIKE '%Server%'

Msdn link: Update with Where

I don't see how any database professional can be stumped by this though.. unless the problem is much more complex than you are letting on.


Ternary Relationship in an ERD: What would the cardinalities look like in this situation?

Somewhat unintuitively you should drop the reference to course and just leave the references to user and topic in the Undergoing table. To answer directly your question: there should be only two participating entities so it is not a ternary relationship.

This is one of those interesting cases in normalization where Third normal form (3NF) is fulfilled but Boyce–Codd normal form (BCNF) is not. Both are desirable forms in normal database formalization. What breaks the BCNF is the dependency topic_id->course_id. In plain English: if you know the topic_id you can deduce the course_id also so you don't need an explicit reference.

When you have user_topics relation with references only to user_id and topic_id you can easily fetch the courses of a user with a regular JOIN (via module to course).

Here is more formally put the dependency that is allowed in 3NF but not in BCNF (Third normal form - Wikipedia):

  Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each attribute in A-X is contained in some candidate key)

Boyce–Codd normal form (Wikipedia):

  Only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF. Depending on what its functional dependencies are, a 3NF table with two or more overlapping candidate keys may or may not be in BCNF.


Using a trigger will not affect the execution speed significantly -- in the end, the database system is executing the same operations.

But if you are sure that every insert mandates the updates then triggers are a great way to ensure database integrity, so by all means do it.


Can an AlwaysOn Availability Group be used to mitigate downtime when there's replication syncing issues on the primary server?

In general, no, this is not going to work because the databases in the availability groups are read-only on the secondary.  If SQL Server somehow allowed other processes (the replication) to change data on the secondary, the database on the secondary would no longer be in sync with the primary, right?  And the way SQL Server AG synchronization (mirroring) works, it would have to be about completely rewritten to even think of supporting something like this.

However, if the subscriber is read-only, you could use a process similar to the following to accomplish what you want, but I do not recommend doing so as it is getting away from What It Is Designed To Do, and the longer you're in IT, the more you'll know why that is a problem.

Once the situation occurs where you need to resync:

Fail over to Server B
Remove the database(s) from the availability group that are part of the Server Z replication scheme
Bring the databases on Server A out of recovery and let your replication resync from Server Z
Once the data on Server A is in sync with Server Z, fail back to Server A
Drop the databases that are not in the availability group from Server B
Add the databases back into the availability group

Again, this would work only if Server B is used for read-only during this time.  Any changes made to the databases that were removed from the availability group while Server B is primary will be lost.


node is not in primary or recovering state

You've configured this node as a replica set member:

 replication:
     replSetName: "rs0"

On startup a new MongoDB server does not have a replica set configuration yet. The server will be in an
unknown replica set member state
until you either add this member to an existing replica set configuration or
use rs.initiate() to establish it as the first member of a new replica set.

You can confirm the current state using rs.status(). A server without any
configuration will report something similar to the following in MongoDB 4.0
(the exact output may vary by server version):

> rs.status()
{
    "operationTime" : Timestamp(0, 0),
    "ok" : 0,
    "errmsg" : "no replset config has been received",
    "code" : 94,
    "codeName" : "NotYetInitialized",
    "$clusterTime" : {
        "clusterTime" : Timestamp(0, 0),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : NumberLong(0)
        }
    }
}

Once added to a replica set, you can use rs.status() and rs.conf() to check the current replica set status and configuration respectively.


Mysql closing immediately after new installation

The error (2003) Can't connect to MySQL server on 'server' (10061) indicates that the network connection has been refused. You should check that there is a MySQL server running, that it has network connections enabled, and that the network port you specified is the one configured on the server.

Start by checking whether there is a process named mysqld running on your server host. (Use ps xa | grep mysqld on Unix or the Task Manager on Windows.) If there is no such process, you should start the server.

If a mysqld process is running, you can check it by trying the following commands. The port number or Unix socket file name might be different in your setup. host_ip represents the IP address of the machine where the server is running.

shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h `hostname` version variables
shell> mysqladmin -h `hostname` --port=3306 version
shell> mysqladmin -h host_ip version
shell> mysqladmin --protocol=SOCKET --socket=/tmp/mysql.sock version

EDITED

Please go through the link for windows once

EDITED 2

1. Run your cmd as administrator.

2.What you will see is like this
c:\windows\system32>

3.Go to your bin location by using cd..
like C:\mysql\bin(my location of bin in my computer is what you are seeing so chose yours correctly)

4.C:\mysql\bin>mysql --install
  Service successfully installed.

5.C:\mysql\bin>NET START MySql
 The MySql service is starting
 The MySql service was started successfully

Then last step is

6.C:\mysql\bin>mysql -u root - p admin

It will ask for password don't enter anything first time because it will use blank, n just press enter you are done.

N later you can set password too...:)


Do I need to index a key that I partition by?

You should create an index that involves lastname and maybe state,lastname.
Think of it. What would obligate a Query Optimizer to plan a query to search by lastname within a partition? The only course of action would be a full table scan within the correct partition.
Let's assume 50 U.S. States (no Puerto Rico, Guam, US Commonwealths, or Protectorates)
If you have 1 million rows, that would be average out 20,000 rows per state. A table scan of 20,000s for a lastname may not be that bad. However, we are talking about the United States. Huge numbers could live in New York, California, Texas and Florida. Smaller numbers will reside in Rhode Island, Idaho, Wyoming, and Montana. Search times via full table scans will vary greatly. In light of this, it would be beneficial to index names within each state.
I suggest two indexes

LastName : The second index will get the Query Optimizer to search for LastName in every partition. Doing 50 index lookups would sure beat a 50 full table scans any given day. You mentioned you have an index on phonenumber only and the performance was great. The same would have to apply to a LastName-only index.
State,LastName : The first index will get the Query Optimizer to search for LastName only down one partition, the state you selected (Maybe unnecessary if the Query Optimizer is intelligent enough to examine the query and select the correct partition).

UPDATE 2012-10-09 16:21 EDT
In all fairness to MySQL's partitioning, if the Query Optimizer is smart enough to navigate data retrieval from just one partition base on the WHERE clause, then indexing (state,lastname) may be unnecesaary. Nevertheless, you must still index on lastname. That way, each partition is indexing on lastname.
My original suggestion of indexing on (state,lastname) is actually based on how I used to index MyISAM tables that were combined under the MERGE storage engine.
Here were my past examples on MERGE tables:

Jan 04, 2012 : get column from too many tables in mysql
Jan 15, 2012 : Separate tables or partition one huge table?

Since Table Partitioning is implemented, indexing on lastname should suffice for you. In fact, recall you stated that the phonenumber is indexed and performance was great, So, I say for the second time, indexing lastname should also be as good for you.


administrator permissions on a SSAS database

The SSAS permissions are completely independent from SQL Server permissions.

If you don't have permissions, there is one hope:

  By default, members of the local Administrators group are also granted
  administrative rights in Analysis Server. Although the local group is
  not explicitly granted membership in the Analysis Services server
  administrator role, local administrators can create databases, add
  users and permissions, and perform any other task allowed to system
  administrators. The implicit granting of administrator permissions is
  configurable. It is determined by the BuiltinAdminsAreServerAdmins
  server property, which is set to true by default.

Source: https://docs.microsoft.com/en-us/sql/analysis-services/instances/grant-server-admin-rights-to-an-analysis-services-instance?view=sql-analysis-services-2017

I suggest you to read the link above and try to run your Management Studio using an account that is member of Local Administrators group of your SSAS Server


MySQL SELECT query hangs - Opening Tables INNODB

Yes, WordPress uses the default storage engine (which is InnoDB since MySQL 5.5).

I'm not sure I understand the problem. Can you execute the SELECT using the mysql command client? If you can, the problem is in the GUIs you use.

If you cannot (which means that you will receive an error, I think) you should investigate the error log. The only possible problem I can think now is a damaged table (REPAIR TABLE should solve the problem), but the error log should give you the exact answer.


What are the minimum matching criteria recommended for reliable demographic based patient matching?

There's this great essay (in spanish, sorry) written by Pablo Pazos, a CS Engineer from Uruguay who has been working on Healthcare IT since 2006 and has made some great contributions to the field, in which he describes an algorithm for doing this.

You can run the article through a translator, but the gist of it is that the basic info to determine a person's identity are their given and family names (both from father and mother), sex, and date of birth. Interestingly enough, he specifically excludes id numbers like SSN from his identity matching algorithms, since "any kind of identifier is NOT part of his identity" (I guess this point could be debatable, though). Also, he excludes attributes like street address, phone numbers, etc. since they aren't really related with the identity of someone, they aren't associated with "who someone actually is".

Also, he assigns different "weights" to each of the former attributes, like this:

First name: 17.5%
Middle name: 17.5%
Family name (father): 17.5%
Family name (mother): 17.5%
Sex: 10%
DOB: 20%

With the matches found on every one of these attributes, he describes a methodology to obtain a composite "concordancy match index" with which comparisons between records can be possible. Also, "partial" matches on the name attributes are possible by using algorithms like Levenshtein's distance.

Good read, IMO. Sorry it's in spanish, but I hope I was able to convey its main ideas.


database schema for a product with multiple categories and hierarchical categories

What you are proposing is a good solution for your requirement of M:N products to categories and hierarchical categories.

To avoid exposing yourself to numerous updates:
You need to do two things to ensure that you don't have a lot of updates in your intersection table.

First, you need to be sure that your categories have a stable, persistent primary key.

Second, you need to link food items to leaf categories.  Don't join Cherry to Red, Healthy, Fruit and Food - just join it to Red and Healthy.  Your nested sets take care of all of the secondary (and higher level) associations.


This is normal when performing an incomplete recovery without specifying where recovery should stop.

If sequence 1 is the last log sequence that was backed up, you can avoid the above error by using instead of:

recover database;

this:

recover database until sequence 2;


Database design where % of rows have unused attributes/columns

The question of 'when should nulls be used?' is touchy. See this post as an example. I haven't heard of a principle that is based on the percentage of null values. How would the threshold be decided?

To avoid nulls completely, I also thought of the same 'join table' solution you did: for every nullable column, convert it to a one-to-zero-or-one lookup table. I use that pattern occasionally in practice.


SQL Server collation mismatch reducing the amount of data visible to an application?

will the data visible to users via an app be limited because of the mismatching collation?

Assuming you are speaking of scenarios that do not error (because an error does not cause people to wonder if something is missing), but instead just return data that might not be ALL of the expected data, then it depends on how the queries are being done. If ALL of the following conditions are true, then yes, some data can be filtered out now when it wasn't before:

a query uses a temporary table (local or global)
the Collation for string columns in the CREATE TABLE #TempTable statement did not explicitly specify the Collation via the COLLATE keyword
the query is filtering at least one such string column in the temporary table on a string literal or local variable
there is an accent mismatch between the data in that column and the string used for filtering (in the literal or variable)

Meaning, previously someone could have executed:

SELECT * FROM #TempTable WHERE Name = 'a';

and gotten back rows containing the following: A a À Á Â Ã Ä Å à á â ã ä å. That same query, with the new tempdb Collation, will now return only rows containing: A a.

Also, outside of the unexpected filtering that is happening in your user-facing code, there are other potential problems waiting for you. If master is a different Collation, then names of system-level entities (e.g. Databases, Logins, etc) will behave outside of expectations and differently than the rest of the system and could cause odd behavior similar to what your users are reporting. Meaning, if you have accents in Database and/or Login and/or variable/parameter/cursor names and have code that works because it is expecting accent-insensitive comparisons, then you / your application might experience what your users are experiencing (though it could take a while to notice, and even longer to debug). AND, it is assumed that all four system DBs are the same Collation, and some stored procedures and views in msdb JOIN to master on string columns and do break in this scenario. Hence this does really need to be fixed.

SO, since it is just the system DBs that are "incorrect" (it is best to have master and msdb match as there are some stored procedures that JOIN between them and error when their Collations don't match), you should change only those back to the expected Collation of SQL_Latin1_General_CP1_CI_AI. You can do this easily enough by using SETUP.EXE /ACTION=Rebuilddatabase. It allows you to change just the system DBs and does not touch user Databases.

Also see the MSDN documentation for: Rebuild System Databases and Set or Change the Server Collation.

At the very least the command-line should look like (but all as one line, or you need to use ^ at the end of each line in order to continue the command on the following line):

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME={InstanceName}
      /SQLSYSADMINACCOUNTS={accountsToAddToSysadminRole}
      /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI [/otherOptions]

For this situation you do not need to use the other recommendation of the undocumented -q switch for sqlservr.exe. That option rebuilds ALL databases, and there is no need to make modifications to databases that are already correct. The less you touch (especially with an undocumented feature) the better off you are.


"Should I add more memory to the server" is a tough question to answer. Your box might be coping "just fine" with what it has, or you may find that tuning the system in other ways (expensive queries, for example) would be more effective. There are a number of resources that you should consider, including Jon Kehayias' How much memory does my SQL Server actually need, as suggested by Aaron in the comments.

Your system may have other bottlenecks apart from memory, particularly if there are a lot more databases, such as disk reads/writes. Writing to log files, reading from data files - it all adds up. But as far as memory is concerned, if you think your current system could cope with less RAM, then it should be able to cope with more databases and the same RAM.


Importing BACPAC file from azure to SQL creating an empty database

I ran into that problem a few minutes and found a way to remove the fulltext stuff from a bacpac.

I found this powershell script (but fixed my backup by hand, because it was faster than trying to understand and use the script)

https://gist.github.com/JohnLBevan/6a8876f71a25aa600254d7127cf1a819

Rename your backup to zip
extract model.xml and Origin.xml
open model.xml with a text editor and look for "fulltext"
Delete all fulltext elements in the xml
set fulltext to false in the header
Calculate the SHA256 hash of the edited xml (I used hashtab for that)
Fix the hash/checksum in origin.xml
Add the updated files back to the zip
Rename back to bacpac and import
"Worked on my machine" - I hope it helps


Percona server Master-Slave version query

yes, it will work. generally it is recommended that slaves be the same or of higher version than its master.


If you ALTER a table that is referenced by a view, or even DROP a table, the view is marked as invalid and can't be accessed even if the changes to the table(s) would not make the view's code incorrect.

In that case the view needs to be re-compiled:

ALTER VIEW some_view COMPILE;

More details in the manual:
https://docs.oracle.com/database/121/SQLRF/statements_4004.htm#SQLRF01104


MySQL: records, inserted via phpMyAdmin, do not replicate

I do not use phpMyAdmin so maybe i'm out but take a look on a potential autocommit option in the UI... Maybe your PMA's session isn't in autocommit (test with : SHOW VARIABLES LIKE  "autocommit").

Other thing, do you have filtered replication ? (do-replicate, do-ignore...)

Max.

Best regards.


Data are not saved to database - Auto creating new datafile in oracle

In your example, you have Autoextend on with 4M as the next extension, but you also have MAXSIZE set to 8G, which means that Oracle is being told that once a tablespace reaches 8G, it will no longer automatically add space.

This is for safety purposes, as sometimes administrators want to know when a datafile is growing very large very quickly.

In your case, you could issue alter tablespace indx2 autoextend on maxsize <some number>G;, which would allow this tablespace to automatically extend to that maximum size, depending on how much space you have on your server! If you run out of actual physical disk, nothing in the Oracle database will protect you from this.

Additionally, you'll still want to periodically monitor the size of this tablespace, as you will (theoretically) still eventually reach the maximum size of the autoextend. Even if you set the maxsize to unlimited, there are still factors that determine the maximum size of a tablespace that are out-of-scope for your particular question. Just know that there is always a maximum upper limit to a tablespace, and someone will have to monitor it to take appropriate action.


MongoDB CSV Export: One Array/Column & Array Index on rows, or vice
  versa

I will suggest to you, if you are unable to import or export collections data from MongoDB CLI. Then you install the Compass, From MongoDB Compass you can easily import and export the collection data.
As per MongoDB documentation here MongoDB Compass works with MongoDB Server version 3.0 and higher, both Enterprise and Community editions. It runs on 64-bit versions of Mac OS X, Windows, Ubuntu 14.04+ and RHEL 7+.

  Note: As of MongoDB Server version 3.6, MongoDB packages a platform-specific Compass installation script with MongoDB
  Server downloads.

For example

I have a database test and collection articles having the records such as

> use test
switched to db test
> show collections
alpha
articles
books
oplog
people
sales
sample
team
users

> db.articles.find().pretty();
{ "_id" : 1, "subject" : "coffee", "author" : "xyz", "views" : 50 }
{ "_id" : 2, "subject" : "Coffee Shopping", "author" : "efg", "views" : 5 }
{ "_id" : 3, "subject" : "Baking a cake", "author" : "abc", "views" : 90 }
{ "_id" : 4, "subject" : "baking", "author" : "xyz", "views" : 100 }
{
        "_id" : 5,
        "subject" : "Café Con Leche",
        "author" : "abc",
        "views" : 200
}
{ "_id" : 6, "subject" : "Сырники", "author" : "jkl", "views" : 80 }
{
        "_id" : 7,
        "subject" : "coffee and cream",
        "author" : "efg",
        "views" : 10
}
{ "_id" : 8, "subject" : "Cafe con Leche", "author" : "xyz", "views" : 10 }
>

The collections records are appearing in MongoDB Compass in Table format such as

Now I am going to export the collections records through MongoDB compass tool. After collecting with your host computer (either local or remote) with MongoDB compass tool . Select the database (here I have taken test database) then click on the collections name (here I have been taken articles) then click on the Collection menu tab. It will appears like that

According to your desire you click on the option, As here I want to export the collection data in .csv format then I will click on export command and then the screen will be appears like that

Then browse the file location as per your desire and export the collection records in .csv format.

After exporting the collections records from the compass in .csv format the appearance in .csv of data will be look like as follows:


ERROR: Arithmetic overflow error converting float to data type numeric

The error seems to indicate that the DECIMAL datatype is not defined with the correct precision and scale. The default is DECIMAL(18,0), but something may have happened with your defaults? When I run the query below as you have (just changed to a table variable and added the precision and scale), I am not receiving any error.

DECLARE @test TABLE
([Parcel Number 2(max)] FLOAT)

INSERT INTO @test
        ( [Parcel Number 2(max)] )
VALUES  ( '1.4515020001e+012'  -- Parcel Number 2(max) - float
          )

SELECT CAST(CAST(CAST([Parcel Number 2(max)] AS FLOAT) AS DECIMAL(18,0)) AS NVARCHAR(255))
FROM @test WHERE [Parcel Number 2(max)] = '1.4515020001e+012'

As an FYI, I do get an arithmetic overflow if I try DECIMAL(12,0).


Fastest way to insert data into mysql from csv

I would normally use the following statement:

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\Users\\Desktop\\nameoffile.csv' REPLACE INTO TABLE `tmp_table` CHARACTER SET latin1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

You should take a look at this page too.
http://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql


What's the risk of keeping a table with row size > 8126 in MySQL 5.7?

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  -- Or COMPRESSED

That is what was needed in 5.6, I think.  Since Barracuda and file_per_table are defaults in 5.7, most of this goes away.  However, the upgrade may have left the table Antelope and/or not file_per_table.

So, I recommend throwing those 3 commands and see if it "fixes" your problem for good.  (Yeah, keep some kind of backup somewhere first.)

You mentioned utf8; perhaps you want utf8mb4, too?

ALTER TABLE tbl CONVERT TO utf8mb4;

(I don't now if replication will be a problem.  Presumably after the above changes it won't be a problem on a 5.7 Slave.)

Edit

But... Being the default (for 5.7) does not mean that you got those values for an existing file after an upgrade.

Look in information_schema.INNODB_SYS_TABLES to see what the file_format and row_format are.  The column space indicates file_per_table if the value is > 0.

A drastic way to deal with the problem:  Change the InnoDB page size.  The default is 16KB, but 32KB is possible.  If I am not mistaken, all tables on the system have to be converted to the new page size, so it is a non-trivial undertaking, and may have other ramifications.

The handling of VARCHAR in DYNAMIC/COMPRESSED

When is the string stored in the main part of the record, versus stored in some other block?

Small size (string is < 40 bytes):  The entire string is stored with the record.  2+N bytes.
Medium size:  Either 'small' or 'large', depending on "record not too big".
Large size: 2+20 bytes in record (length+pointer); entire text stored separately.

"record too big":  When the row is too long, the longest columns are stored in off-page storage.  That is, some times a column will be entirely off-page, sometimes on-page, depending on the makeup of the entire row.

VARCHAR and TEXT and BLOB are handled the same for DYNAMIC or COMPRESSED.

The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page ("all or none").

COMPRESSED = DYNAMIC + compression. KEY_BLOCK_SIZE controls how much column data is stored in the clustered index, and how much is placed on overflow pages.

5.7 reference and older, more detailed, discussion; and other pages.


Database migration

Database migration or schema migration are the evolving terms for the process of managing and deploying changes to the structure of your database.

The idea is that you never make ad-hoc changes to your database. Instead, you make a disciplined practice of accumulating scripts (SQL, XML, etc.), and optionally executable code, to implement each stage of your database’s evolution: adding tables, adding columns, altering columns, etc.

Tools such as Flyway and Liquibase automate the process of managing and executing your collection of scripts and optional code. If you deploy such a tool along with your database or app, the tool can automatically detect which scripts and/or executables need to be run to update the currently-used database to the latest and greatest.


How to prevent specific applications from connecting to Oracle?

You could use a logon trigger for that, something along these lines:

create or replace trigger logon_trigger
  after logon on database
declare
  v_prog varchar2(50);
  v_sid number;
begin
  v_sid := to_number(sys_context('USERENV', 'SID'));
  select program into v_prog FROM GV$SESSION where sid = v_sid;
  if lower(v_prog) like 'access' then
      raise_application_error(-20000, 'Login not allowed');
  end if;
end;
/

PS. Not tested.


This script that I have pulled from my script library (probably a Paul Randal script) gives you the file group information.

It also shows you the ranges of values between each boundary so all you would have to do to see which partition is next would be to get the MAX of your partitioning column (Probably the PK).

With this you could see which is the active partition and depending on your function being a left of value or right you could easily see which partition is next. At least I think that works in all situations.

SELECT      ps.name AS PartitionScheme
            , fg.name AS [FileGroup]
            , prv.*
            , LAG(prv.Value) OVER (PARTITION BY ps.name ORDER BY ps.name, boundary_id) AS PreviousBoundaryValue

FROM        sys.partition_schemes ps
INNER JOIN  sys.destination_data_spaces dds
            ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN  sys.filegroups fg
            ON dds.data_space_id = fg.data_space_id
INNER JOIN  sys.partition_functions f
            ON f.function_id = ps.function_id
INNER JOIN  sys.partition_range_values prv
            ON f.function_id = prv.function_id
            AND dds.destination_id = prv.boundary_id


MongoDB: why does a DB with 0 connections still get "commands" and "getMores"?

Since this is a replica set, the getMores are likely due to replication. Even with no write activity, replica set members will poll for updates on the oplog.rs collection.

The commands are probably related to monitoring (for example, serverStatus to get the opcounters that are being plotted).


MariaDB: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

Obviously this doesn't happen on all foreign keys so we suspect there was some corruption affecting that foreign key.

The solution was to drop and recreate the foreign key.


"Y can be determined by X" is not clear. "X maps to one and only one Y" is not clear. You are not clearly saying what a FD is or what it means to functionally determine in R.

For attribute sets X & Y, a FD is an expression of the form "X -> Y". For attribute set S and attribute A, "S -> A" is another way of saying "S -> {A}" and "A -> S" is another way of saying "{A} -> S".

"FD 'X -> Y' holds in R" and "X -> Y in R" and "X functionally determines Y in R" are ways of saying "all the tuples with the same subtuple value for the attributes of X have the same subtuple value for the attributes of Y in R".

"X is a superkey of R" is another way of saying "X functionally determines every attribute in R".

Your question seems to be, does X -> Y in R imply that X a is superkey of R. No, it doesn't.


How can I disable the fade in and fade out effects in pgAdmin 4 when opening windows?

I could not disable the fade completely, but I got to remove the zooming effect (which also executes faster) improving the user experience. The zooming effect is added by AlertifyJS.

Edit the file alertify.pgadmin.defaults.js file located in C:\Program Files (x86)\pgAdmin 4\v1\web\pgadmin\static\js

Then change

alertify.defaults.transition = "zoom";

to

alertify.defaults.transition = "fade";

After making these changes, restart pgAdmin4 or click on File > Reset Layout.

Hopefully this step assists someone to find a better solution...

One can also set the above option to none, in this case it seems to default to the pulse transition.


PostgreSQL: From vague "DB is slow" to concrete actions

To resolve overall DB slow, these are the things you can look into in Postgres:

1-

 -- Lock monitoring
 SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;

-- To find waiting queries
SELECT pid, datname, usename, now() - query_start AS runtime, wait_event,
wait_event_type, state, query FROM pg_stat_activity WHERE wait_event
is NOT NULL AND state = 'active';

-- To look at what all is running
select * from pg_stat_activity;

-- Once you have identified the session that is blocking you, you can kill it as below, make sure you don't kill someone's else work,
-- check with DBA if unsure (replace blocking_pid with pid that is blocking your session)

   SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'your_db'
  AND pid <> pg_backend_pid() and pid='blocking_pid';

2- Make sure autocommit is enabled in your client (This is especially an issue in DBeaver)

3- If there is no lock, let's generate an explain plan to see what is the cost of the query and places where we can improve. How to generate an explain plan

4- You might have to vacuum the tables if there have been large DML, it is also advisable to collect stats after any large DML, by default vacuum and stats collection happen automatically. (See link above for info n VACUUM, too).

5- Regarding your combined index, that will depend on the slow queries, there is no automated way to check that, you can however make sure that your foreign keys are indexed and that you can check using information_schema views.


Advanced design question related to compound primary keys

This is opinion based, or at least quite subjective in the real world with the best solution varying depending on a number of factors the relative importance of each different people with disagree on, unfortunately.

The question is whether you should ever create a surrogate key when there is an apparently perfectly suitable candidate key (compound or not) in the existing data. http://en.wikipedia.org/wiki/Surrogate_key has a fuller run down on the advantages and disadvantages, but I tend to err on the side of surrogate keys (integers or UUIDs depending on circumstance) in such circumstances for the following reasons:

Storage efficiency: The PK values are going to be found in all tables that refer to it in an FK, and in any indexes defined over those FKs: a surrogate key is likely to be smaller (at 4 bytes for a 32-bit integer, 8 (64 bits) if you need a larger range than that, 16 for a UUID) than any compound candidate key.
Data Consistency and Changes: a primary key should be unique, immutable, and always known, but many of the things people often assume fit those rules actually don't: people can change names, due to mistakes elsewhere there are people with duplicate identification numbers (such as National Insurance Numbers in the UK (note: you should not use NINos as a key for other reasons too)) so at some point the affected people might have that identifying number changed, and so on. ON UPDATE CASCADE or equivalent can help a lot in the changes scenario but that is not supported by all DMBSs (and can be very expensive depending on your data size).
Later Design Changes: No matter how immutable your client says the data and the rules around it are, they will at some point need to change that. Changes to the assumptions upon which you have chosen a natural candidate key to be the primary key for a table can have a much more significant effect on your design than if you had used a surrogate key.
Consistency in design/coding style, which can help understanding and maintainability later.

This is not hard and fast: there are circumstances where I'll prefer a natural candidate key over a surrogate key.

One thing you must remember when talking to academics is that they are usually talking from the point of view of having a perfect implementation of the theory they are talking about as a starting point, and there is absolutely no relational database implementations out there that can be considered to be that so there are practical considerations that an academic discussion may completely ignore due to this sort of "perfect world" assumption (in theory there is no difference between theory and practise, but in practise there usually is). I don't intend this to be disparaging to the academics here: I'm just meaning that we need to be very careful to define the context of such discussion to avoid talking at crossed purposes with us both being completely correct within the confines of the context we think we are talking about but quite wrong within the context the other party is framing the discussion in.


How to solve log file sync wait in oracle 12c

If your archiver is not keeping up. you should probably create smaller online redo log files, hence they will be faster to archive, and more online redo log groups. Try having 10 250MB online redo log groups and see if that works better.

You may want to check the IO sub system to see what kind of throughput you are getting. In 11gR2 and 12cR1 Orion is already installed. You should be able to take a copy of a datafile and trash it with Orion to find out what the reads and writes look like.

Using Orion Calibration to check I/O numbers

As a separate thought, can you create a new volume for the temp and online redo logs with mirroring? It could be that no one here can help you if your issue is related to your storage sub system, or code that commits way too often. You should look into changing the things that you said that you can't change, since that could be where the issue is.

I would not say that you can't do anything. You can look for poor performing SQL and see if you can add an index. You can look at the large insert statements and see what constraints, indexes and most likely triggers exist and see if there is something there that is causing an issue. How many tables are getting inserts, and do they have row by row triggers. If so start tuning there.


Choosing primary keys: Scientific names of species or system-assigned numeric identifiers?

I would use my own identifier.

The species name may be unique but
- it's too long
- it's a string

For example, in SQL Server, if used as the clustered primary key it will be used in non-clustered indexes, thus repeating the long string. And typically foreign keys omn child tables will go to the primary key, thus repeating it again

As a string, you have the overhead of sorting and comparison (case, accents, etc).

Using a surrogate numeric key avoids these problems: but you must create a unique non-clustered index on the species name.

Is species name a good long term identifier too? Not my area of expertise but don't many species have alternate names, or controversies, or get reclassified, or "maybe this species"?

Example: how many Giraffe species are there? 9? 2? 6? 8? 4?


Azure IaaS is a different animal than traditional on-premise servers (virtual with a SAN or purely physical).  You will get at best 500 IOPS per drive attached.

General recommendation is to use storage pools to stripe multiple drives to increase throughput.

With only four drives available on a D series, I would do the following.

Put TempDB on the D:\, it's an SSD.  Don't put anything else there, make sure that a startup script will check that drive for any folders that are necessary and fix permissions if required.
Stripe the four disks you have into a single driver and put both your data and log files on there.

If you had 8 disks available to attach then I would recommend doing two drives of four disks each and putting log on one and data on the other.


how can i rename all the tables in a database so the first character of each table's name is removed

Easy enough to do with SQL. You can send the output to a file then execute it once it's verified or just copy and paste the DDL statements back into your client to execute manually.

mysql> show tables;
+---------------------+
| Tables_in_mysqlslap |
+---------------------+
| tb1                 |
| tb2                 |
| tb3                 |
| tb4                 |
+---------------------+
4 rows in set (0.00 sec)

mysql> select
  concat("RENAME TABLE ",table_name, " TO ", substring(table_name,2), ";") INTO OUTFILE '/var/lib/mysql-files/rename_tables.sql'
  FROM information_schema.tables
  WHERE table_schema = 'mysqlslap';
  Query OK, 4 rows affected (0.00 sec)

+--------------------------------------------------------------------------+
| concat("RENAME TABLE ",table_name, " TO ", substring(table_name,2), ";") |
+--------------------------------------------------------------------------+
| RENAME TABLE tb1 TO b1;                                                  |
| RENAME TABLE tb2 TO b2;                                                  |
| RENAME TABLE tb3 TO b3;                                                  |
| RENAME TABLE tb4 TO b4;                                                  |
+--------------------------------------------------------------------------+

mysql> RENAME TABLE tb1 TO b1;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+---------------------+
| Tables_in_mysqlslap |
+---------------------+
| b1                  |
| tb2                 |
| tb3                 |
| tb4                 |
+---------------------+
4 rows in set (0.00 sec)


Does Compatibility Level allow unsupported functions?

Is this a bug, or am I not understanding Compatibility levels as much as I thought?

Not a bug.  The Compatibility Levels are intended solely to make upgrading easier.  IE to prevent errors or changes in behavior when you upgrade to a newer version of SQL Server.

After you upgrade to a new version of SQL Server, you might want to take advantage of new features and capabilities without changing the Database Compatibility Level, so where possible, new TSQL language features are not blocked by your lower Compatibility Level.


Select rows with different id but same values

First you have to filter the userid that appears more than one time:

select
userid
from table
group by userid
having count(1) > 1

This will return:

userid
------
     5
     8

Now using this as a subquery you can select the complete result you want:

select
*
from table
where userid in (
  select
  userid
  from table
  group by userid
  having count(1) > 1
)


How can I create a filegroup on SQL Server attaching a bare filename?

You can't construct a path inside the ALTER DATABASE command, so you're going to have to use dynamic SQL.

If you want to just put the new data file in the default instance path, you can do as Scott suggests - use the server property for default data path:

DECLARE @dbname sysname = N'myapp_db';

DECLARE @path nvarchar(511) =
  CONVERT(nvarchar(511), SERVERPROPERTY('InstanceDefaultDataPath');

DECLARE @sql nvarchar(max) = N'ALTER DATABASE $db$
  ADD FILEGROUP data_fg;

ALTER DATABASE $db$ ADD FILE (
    NAME=data_fg,
    FILENAME=''data_fg.ndf'',
    SIZE=32MB,
    MAXSIZE=4096MB,
    FILEGROWTH=32MB
) TO FILEGROUP data_fg;';

SET @sql = REPLACE(REPLACE(@sql, N'$path$', @p), N'$db$', @dbname);

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

If, however, you want to base this off an existing database (maybe the one you're altering, if they pass that by a parameter), then you could do this:

DECLARE @dbname sysname = N'model';

DECLARE @p nvarchar(511);

;WITH x AS (SELECT TOP (1) p = physical_name, type_desc
  FROM sys.master_files
  WHERE database_id = DB_ID(@dbname)
  AND type_desc = N'ROWS'
  ORDER BY file_id
)
SELECT @p = SUBSTRING(p, 1, LEN(p)-CHARINDEX('/',REVERSE(p))+1)
FROM x;

DECLARE @sql nvarchar(max) = N'ALTER DATABASE $db$
  ADD FILEGROUP data_fg;

ALTER DATABASE $db$ ADD FILE (
    NAME=data_fg,
    FILENAME=''data_fg.ndf'',
    SIZE=32MB,
    MAXSIZE=4096MB,
    FILEGROWTH=32MB
) TO FILEGROUP data_fg;';

SET @sql = REPLACE(REPLACE(@sql, N'$path$', @p), N'$db$', @dbname);

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

You may even want to allow your users to override with a specific path (think of the case where the current drive is getting crowded and they want to start this new file in a totally new location).

It also strikes me as weird that you'd bother creating a data file that is only 32 MB. That's barely big enough to hold the metadata about the file, and will surely start a cycle of autogrowths as soon as it becomes active.


Think of an index as "table of contents"... that is an ordered list of pointers to positions in a file, aka offsets. Say that you have millions of records stored in a table, rather than search the table for matching criteria, it's much faster to reference an ordered list for matches, then stack the pointers to the specific matching rows. A perfect example of an index is a tables primary key field, most typically its "id" field. If you want row id # 11234566, it's much faster ask the index for a pointer to the data than it is to scan the data source for position 11234566.

Here's a not so obvious use of indexing:

CREATE TABLE activity_log (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
activity_type_id SMALLINT UNSIGNED NOT NULL,
datetime_created DATETIME
KEY(activity_type_id),
PRIMARY KEY(id)
);
CREATE TABLE activity_log_to_date_key (
activity_log_id INT UNSIGNED NOT NULL,
date_created_key  INT UNSIGNED NOT NULL REFERENCES dim_datetime(id),
UNIQUE KEY(activity_log_id),
KEY(date_created_key)
);
CREATE TABLE dim_datetime (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
date_hour DATETIME NOT NULL,
PRIMARY KEY(id),
KEY(date_hour)
);

Your operation can create your log record, but then create a reference to an indexed datetime that is faster to search/sort than your log table. Then join back your log table on its own primary key. If you need me to expand on this, let me know. I hope this makes sense.

Sample query:

SELECT a.activity_log_id, al.activity_type_id, al.datetime_created
FROM activity_log_to_date_key a
INNER JOIN dim_datetime d ON (d.id = a.date_created_key)
LEFT JOIN activity_log al ON (al.id = a.activity_log_id)
WHERE d.date_hour BETWEEN '2009-01-01 00:00:00' AND '2009-06-01 12:00:00';


error 1118: row size too large. utf8 innodb

There are two issues you need to look at

ISSUE #1 : Characters Vs Bytes

Since UTF-8 uses 3 bytes per characters, you may not have allocated enough for character fields.

ISSUE #2 : InnoDB Row Length

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

  When you attempt to specify an index prefix length longer than
  allowed, the length is silently reduced to the maximum length. This
  configuration option changes the error handling for some combinations
  of row format and prefix length longer than the maximum allowed. See
  innodb_large_prefix for details.

  The InnoDB internal maximum key length is 3500 bytes, but MySQL itself
  restricts this to 3072 bytes. This limit applies to the length of the
  combined index key in a multi-column index.

  The maximum row length, except for variable-length columns (VARBINARY,
  VARCHAR, BLOB and TEXT), is slightly less than half of a database
  page. That is, the maximum row length is about 8000 bytes. LONGBLOB
  and LONGTEXT columns must be less than 4GB, and the total row length,
  including BLOB and TEXT columns, must be less than 4GB.

  If a row is less than half a page long, all of it is stored locally
  within the page. If it exceeds half a page, variable-length columns
  are chosen for external off-page storage until the row fits within
  half a page, as described in Section 14.3.12.2, “File Space
  Management”.

  Although InnoDB supports row sizes larger than 65,535 bytes
  internally, MySQL itself imposes a row-size limit of 65,535 for the
  combined size of all columns:

  mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
      -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
      -> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB; ERROR 1118 (42000): Row size too large. The maximum row size for the used table
  type, not counting BLOBs, is 65535. You have to change some columns to
  TEXT or BLOBs See Section E.10.4, “Table Column-Count and Row-Size
  Limits”.


This turned out to be the system lacking a localhost user as well as a remote (any host) user.

Once the additional user had been added for localhost everything worked as expected.


CASE statement and SARGability - specific use case

From experience I would expect the index not to be used in the query against the view. However, to determine if something is SARGable you just need an example query against a data set for which the index is beneficial. So, best to test it.

First I'll create some test data:

CREATE TABLE dbo.tblStates (
StateAbbreviation VARCHAR(2) NOT NULL,
FLUFF VARCHAR(10) NOT NULL
);

-- insert all possible abbreviations to future-proof table
INSERT INTO dbo.tblStates WITH (TABLOCK)
SELECT CHAR(t1.number) + CHAR(t2.number), REPLICATE('Z', 10)
FROM
(
    SELECT number
    from master..spt_values
    WHERE number BETWEEN 65 and 90
) t1
CROSS JOIN
(
    SELECT number
    from master..spt_values
    WHERE number BETWEEN 65 and 90
) t2;

CREATE INDEX IX_TBL_STATES ON dbo.tblStates (StateAbbreviation);

When running this query we can see that the index is used to do a seek:

SELECT StateAbbreviation
FROM dbo.tblStates
where
StateAbbreviation in ('AK', 'TX');

However, if we create a view:

CREATE VIEW STATE_VIEW
AS
select
  StateAbbreviation
  , IsBig = case when StateAbbreviation in ('AK' , 'TX') then 1 else 0 end
from tblStates;

The equivalent query no longer uses the index to seek:

SELECT StateAbbreviation
FROM STATE_VIEW
where IsBig = 1;

db fiddle link is here.


Is there a way to actually limit InnoDB data dictionary?

1 million tables ? !!  That, alone, is likely to choke MySQL.  What are you doing?

Creating a table for each 'user' or 'company' or whatever?  That has to stop.
Creating lots of partitions in tables?  That is inefficient.

Run this; it might give a clue of whether it is tables, partitions, or what-not:

( select * from information_schema.INNODB_SYS_TABLES
      order by rand() limit 33 ) ORDER BY name;

Patterns of database or table names may be obvious.  Names ending like #p#p200805 are partition names.
If it's tables or databases, let's discuss how to do that.  Please provide some hints of what your tables are.  Pending that, I will provide some generic suggestions.

Instead of having one-table-per-entity, have one table.  In it have an id that indicates which entity is involved.  ("Entity" is "user", "company", or whatever you are creating new tables based on.)

Then, to get away from one-table-per, you will need a lot of disk space and downtime to copy the million tables into a handful, adding the id as you go.

If it's partitions, let's discuss that.  More than about 50 partitions in a single table leads to inefficiencies.  Tables with fewer than a million rows should not (in general) be partitioned.  Please provide SHOW CREATE TABLE, since there are many possible issues.  I give some answers here .

Caveat:  Much of what I have said will be invalid with version 8.0 and it's integrated Data Dictionary.  Lots of databases or tables or partitions is not much of a problem.


ERROR WSREP`: gcs/src/gcs.cpp:gcs_open():1379:` Failed to open channel 'test_cluster'

We should start the first node without below parameter in my.cnf :
    wsrep_cluster_address="gcomm://192.168.120.107,192.168.120.111,192.168.120.117"

Then start the other nodes normally :

/etc/init.d/mysql start

The first node should start normally like the others.


Dynamic auditting trigger without temporary tables

Obviously I can hard code in the columns myself into the trigger and audit tables, but I'd rather have a solution which doesn't need updating every time there is a schema change on the table.

That is nearly the correct approach.  Write a stored procedure to generate your triggers and run it any time you change the schema.


You can't parameterize entity names (tables, columns, views, etc). You need to do it the riskier way:

        DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT * FROM ' + QUOTENAME(@TblName) + N' WHERE 1=1'
        + CASE WHERE @Condition IS NOT NULL THEN
        ' AND ' + @Condition ELSE N'' END

        DECLARE @params NVARCHAR(MAX) = N'
            @Condition VARCHAR(MAX)';

        PRINT @SQL

        EXEC sys.sp_executesql @SQL, @params,
            @Condition

QUOTENAME() is typically enough to protect from dangerous execution (leading, potentially, to SQL injection), but to make it a little more secure you should consider (a) prefixing the table name with the correct schema prefix (e.g. ...FROM dbo.' + QUOTENAME(@TblName) + ... and (b) checking for existence first:

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @TblName)
BEGIN
  RAISERROR(N'Nice try, robot.', 11, 1);
  RETURN;
END

DECLARE @SQL ...


Defining a column as nullable in a view (but not in the referenced table)

Yes, it's possible:

select Id, -- Remains as in table
  cast(MandatoryIntCol as int) as [MandatoryIntCol], -- Makes nullable
  isnull(OptionalIntCol, 0) as [OptionalIntCol] -- Makes mandatory
from dbo.MyTable;

It's worth noting that there are some restrictions with this approach:

The changed columns are not writeable in the view, so if the OP's application tries to write to them, it would fail.
The application must not try to distinguish between tables and views. Most apps do not, but annoyingly, some product applications do.


mysqldump changes primary key to unique key?

In MyISAM, there is no implementation difference between PRIMARY and UNIQUE.  (I don't know why it would do what it did.)

You should upgrade to InnoDB.


Primary key which is foreign key in an relationship is specified NOT NULL. Why?

A foreign key null means that there may not be an entry on the other side. In a master-detail relationship it means a detail can exist without a master.


Can the SQL Server 2012 service be running while applying CU 8 to SP 3?

You will definitely need to have users log off during the patch application; in theory, the service will be available for a while during the process, but there's no way to tell when the service will be stopped, and user activity could impact the patch application.

Be safe, plan for a downtime, apply the patch. Make sure you have proper backups and recovery plans in place in case of problems.


Declaring cursor to loop over some values in SQL Server 2008 R2

The MSDN/BOL topic for DECLARE CURSOR contains example syntax for cursor usage. That said, as I had 5 minutes to spare on the train this morning...

-- Create temporary table for Business Units (replace with the "real" table)
DECLARE @BusinessUnit TABLE (BU NVARCHAR(MAX))

INSERT @BusinessUnit
SELECT 'India' UNION SELECT 'International' UNION SELECT 'US' UNION SELECT 'UK'

DECLARE @K TABLE (BU NVARCHAR(MAX),K NVARCHAR(MAX),Y MONEY,A MONEY,D MONEY,YP MONEY)
DECLARE @FY INT
DECLARE @BU NVARCHAR(MAX)

SET @FY = 2012

DECLARE BU_cursor CURSOR FAST_FORWARD FOR
SELECT BU FROM @BusinessUnit

OPEN BU_cursor
FETCH NEXT FROM BU_cursor INTO @BU

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT @K (BU, K, Y, A, D, YP)
    EXEC dbo.SP_Report @FY, @BU

    FETCH NEXT FROM BU_cursor INTO @BU
END

CLOSE BU_cursor
DEALLOCATE BU_cursor

SELECT * FROM @K


How to install Barman with pg_receivewal and pg_basebackup without install PostgreSQL server?

Actually the given URL also says in the System requirements section:

Linux/Unix
Python >= 3.4
Python modules:
    argcomplete
    argh >= 0.21.2
    psycopg2 >= 2.4.2
    python-dateutil
    setuptools
PostgreSQL >= 8.3
rsync >= 3.0.4 (optional for PostgreSQL >= 9.2)

It looks like you need to install PostgreSQL server.


Database Model for Various Type of Sensors

Depending on how you plan on using the reading values, I would lean towards option 2 for this. Since you have different categories of sensors, with different types of values being used for each, then it would make sense to to have a master table that stores all the sensors and their type and status (online/offline) and location. There would then be a table for each of the types. This would make it easier to process the data over time, such as average temperature over time.

If you just want to store the data without much processing, then going the one-table route would be best, and store the values as VARCHARs. This would make adding a type later on easier, but would make manipulating the data in the future more difficult.


There are several SQL statements you cannot use ROLLBACK  as MySQL Implicitly Commits those Statements such as:

CREATE / ALTER / DROP DATABASE
CREATE /ALTER / DROP / RENAME / TRUNCATE TABLE
CREATE / DROP INDEX
CREATE / DROP EVENT
CREATE / DROP FUNCTION
CREATE / DROP PROCEDURE

For Such Statements it doesn't matter weather you are using explicit Commit or not the statement will Commit implicitly ass soon as you execute it.


How do I rollback a dropped table using log?

Third party software are :

ApexSQL Log. It has free trial as well.

Toad for SQL Server - It has Log Reader management which Rolls back transactions in the transaction log without the need to restore from a backup

Native to SQL Server are below methods :

you can restore your Database from a backup, and then RESTORE Transaction LOGS to a point in time with the STOPAT = '6/10/2013 12:30AM' argument.

Read this excellent post from Paul Randall - Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN


What version of MySQL are you using? I tested the following with 10.1.16-MariaDB, and it appears to be working:

CREATE TABLE Campagne
( id_campagne INT NOT NULL AUTO_INCREMENT
, PRIMARY KEY (id_campagne) ) ENGINE=INNODB;

CREATE TABLE ChildTable
( id_child INT(9) NOT NULL AUTO_INCREMENT
, id_campagne INT NOT NULL
, PRIMARY KEY (id_child)
, CONSTRAINT FK_campagne_id_campagne
      FOREIGN KEY (id_campagne)
      REFERENCES Campagne (id_campagne) ON DELETE CASCADE
) ENGINE=INNODB;

insert into Campagne (id_campagne) values (DEFAULT);
insert into ChildTable (id_child, id_campagne) values (DEFAULT, 0);
select * from Campagne;
+-------------+
| id_campagne |
+-------------+
|           0 |
+-------------+

select * from ChildTable;
+----------+-------------+
| id_child | id_campagne |
+----------+-------------+
|        0 |           0 |
+----------+-------------+

delete from Campagne where id_campagne = 0;
select * from ChildTable;
Empty set (0.00 sec)


Differences between .dump and .sql in pg_dump PostgreSQL

The file extension means nothing. At all. It's just a part of a file name.

If you want a custom-format dump for use with pg_restore use -Fc as an argument to pg_dump.

pg_dump defaults to generating SQL-format dumps for use with psql.

See the manual for more details.


Similar hardware, Same DBconfiguration and Database but different status variables result between Windows and Linux server

Since you started both servers, you have executed (approximately) 162509 + 33073 + 11291 = 206,873 queries on the Linux server and 44648032 + 6866308 + 994889 = 52,509,229 queries on Windows. Why would you expect similar numbers when one has done more work than the other?

The ratios, however, are similar with:

162509 / (162509 + 33073) ~= 83%
44648032 / (44648032 + 6866308) ~=87%

80-90% ratio of query cache hits. You should only compare statistics over the same period of time, for example, by using pt-mext -- mysqladmin ext

In order to answer specific questions about size, you should provide the output of SHOW GLOBAL VARIABLES like 'query_cache%'; and if the OSs and mysql versions are 32 or 64 bits.

UPDATE: You have complete different configuration on Linux and Windows, window's query cache is 30x larger. Why would you expect equal or similar statistics from both, when you are not even executing the same queries? I do not understand why you are surprised that you get different "numbers" when you input different ones: free blocks is the amount of blocks that are not in use by query results. The Windows one is not yet full, or it has deallocated more than the Linux one.


Can SQL Server full-text search tokenize words within words?

Looks like there is no such feature in SQL Server.


How to indicate what will be measured with a NONCLUSTERED COLUMNSTORE INDEX?

It is not necessary to distinguish between measures and dimensions when defining your columnstore. All columns are stored in the same manner. All columns included in any query should be in the columnstore, in most cases it is best to put every column in the table into the columnstore.


backup postgresql database to submit with project

Use pg_dump -Fc and tell them to load it with pg_restore.

If you've created user accounts and other global objects you'll want to also provide a pg_dumpall --globals-only to them.


Connect to PostgreSQL from different host

The issue is probably due to this line:

host    all     all   192.168.0.0/24  md5

This (the 24) basically tells Postgres to allow connections from any machine that has IP of the form: 192.168.0.x. So, your 192.168.10.195 does not match the criteria. You can replace it with:

host    all     all   192.168.0.0/16  md5     -- any 192.168.x.x

or

host    all     all   192.168.10.0/24  md5    -- any 192.168.10.x

or

host    all     all   192.168.10.195/32  md5  -- only 192.168.10.195

depending on what range of IPs you want to allow.


Is this many to many relationship correct?

The physical model is what is created in most physical implementations of a many-to-many relationship.  However, it does not correctly replicate the rules in the logical model.

The physical model allows for zero or more in both directions.  The logical model indicates one or more from project to employee.

The reason that many physical implementations of any relationships do this is that in many DBMSs the SQL declarative referential constraints (i.e. foreign key constraints) don't allow for the rule to be suspended while data is inserted into two tables at one time.  The SQL standard provides for "deferrable constraints" and some DBMSs implement these, but many don't and the ones that do haven't always, so not everyone uses them, even if they are available.  Old habits can die hard.

For the ones that don't implement deferrable constraints, think about it this way: For there to be any kind of mandatory relationship in both directions (i.e. 1:1,M or even 1:1) then you would have to insert the parent and the child at the same time.  While you can create a transaction that ensures the parent and child are created in one logical unit of work, the referential integrity rules aren't built to "suspend judgement" until the whole transaction finishes.

This is why in practice, physical implementations without deferrable constraints are essentially always optional at the child end.  This lets you insert the parent and then insert the child.

The practical way around this is generally to write additional application logic to ensure that there is at least one child per parent when 1:1,M is the rule or to just leave it and hope that users do the right thing.  These work-arounds aren't necessary if you do happen to have access to deferrable constraints.


There is no relation between those. Chunk size is just partition size in the storage. When you read data from chunk, you read just those documents to the memory, what your query requires.


You can do this by piping the query into psql, then grepping the output, like so:

postgres@winterspring:~$ cat foo.sql
\timing on
select * from foo;
postgres@winterspring:~$ psql < foo.sql | grep "^Time:"
Time: 0.505 ms
postgres@winterspring:~$

Redirect just the timing to a file if needed:

postgres@winterspring:~$ psql < foo.sql | grep "^Time:" >> timing.txt

The grep will have a slight performance impact.


When is it better to create STATISTICS instead of creating an Index?

You question revolves around - When is it a good thing to just create statistics vs create index (which create stats).

From my sql server internals notes (SQLSkills class- IE1 and IE2) and SQL Server internals book, below is my limited understanding :

SQL Server statistics are nothing but system objects that contain vital information about the index key values and regular column values.

SQL Server uses a cost based model to choose a "good enough" execution plan as fast as possible. Cardanility estimation (estimating no. of rows to be processed on each step of the
query execution) is the most important factor in query optimization which inturn affects the join strategy, memory grant requirement, worker thread selection
as well as choice of indexes when accessing data.

SQL Server wont use nonclustered indexes when it estimates that a large no. of KEY or RID loopup operations will be required, so it maintains statistics on indexes (and on columns) which
will help in such estimations.

There are 2 important things about stats :

Histogram stores info about data distribution for the leftmost statistics (index) column ONLY. It also stores info about multi column density of the key values. So essentially,
histogram stores data distribution for the leftmost statistics column only.
SQL Server will retain at most 200 steps in histogram irrespective of table size. The intervals covered by each histogram step increase as the table grows which leads to "less accurate" stats for large tables.

Remember that index selectivity is a metric which is inversely proportional to density i.e the more unique values a column has, the higher its selectivity.

When particular queries do not run very often, you can select to create column-level statistics rather than an index. Column-level statistics help
Query Optimizer find better execution plans, even though those execution plans are suboptimal due to the index
scans involved. At the same time, statistics do not add an overhead during data modification operations, and they
help avoid index maintenance. This approach works only for rarely executed queries.

Refer :

Column Statistics Give the Optimizer an Edge by Kalen Delaney
Statistics, Damned Lies, and Statistics – What is Statman? by Conor Cunningham

Note: Someone like Paul White or Aaron Bertrand can chime in to provide more color to your good question.


Options for Index Defragmentations For Critical Tables

I would not recommend the procedure that you mention in your question. If you did that, the  newly built table would end up suffering the same amount of fragmentation as the original and you would be back to square one.

I would recommend using Ola Hallengren's Index and Maintenance scripts to reduce the fragmentation of your indexes:-
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Ola's script are tried and tested, I've used them for a while now and have had absolutely no problems with them.

So, taking example H from Ola's page.

Rebuild or reorganize all indexes with fragmentation on the table Production.Product in the database AdventureWorks:-

EXECUTE dbo.IndexOptimize
@Databases = 'AdventureWorks',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'AdventureWorks.Production.Product

This will REORGANIZE all indexes on the Production.Product table with fragmentation > 5% and < 30%. It will then REBUILD_ONLINE all indexes on the table with fragmentation > 30%.

The values are based on Microsoft's recommendation here:-
http://msdn.microsoft.com/en-us/library/ms189858.aspx

REORGANIZE is an online operation, leaving the table available for read-write operations.

REBUILD ONLINE can be run as you have Enterprise Edition. Quoting from BOL:-

  "The ONLINE option allows concurrent user access to the underlying
  table or clustered index data and any associated nonclustered indexes
  during these index operations. For example, while a clustered index is
  being rebuilt by one user, that user and others can continue to update
  and query the underlying data"

But be aware, it should be called REBUILD NEARLY ONLINE as a S-lock or Sch-M lock is held at the end of the operation which can block certain operations. Also online rebuilds cannot be performed on indexes containing the following data types:- image, ntext, and text.

Have a read through of Microsoft's guidelines for online operations:-
http://msdn.microsoft.com/en-us/library/ms190981.aspx

Once you get familiar with the scripts on a DEVELOPMENT system you can look at implementing on your PRODUCTION server. A proper schedule should be setup in order to keep the amount of REORGANIZE/REBUILD that have to be run to a minimum.


How to use composite index in range query in mysql

You need a composite index on the columns (they are not fields!), but in reverse order, on (field2, field1) for this query.

When a condition has equality checks (=) and range/inequality checks (>, >=, <, <=, IN), then the columns involved in the equality checks should be first in the index and the inequality columns afterwards.

alter table test
    add index ix_field2_field1
    (field2, field1) ;

Additional notes:

A query with LIMIT and without ORDER BY will yield indeterminate results. Always use ORDER BY with LIMIT unless you don't care if you get different results per execution:

select *
from test
                          -- no need to force anything
where field2 = 2          -- the order here doesn't matter
  and field1 > 100        -- but is useful for developers
order by field2, field1
limit 200 ;

Really, choose some proper names for the columns, not "field1", "field2", etc.
Your table does not have a PRIMARY KEY defined. This is not good both from a theoretical and a practical point of view. Assuming that the table uses the InnoDB engine, then by not defining a primary key, InnoDB adds a  6-byte hidden column that is used as the clustered index. This also means that all your indexes have this 6-byte column attached, making them wider. I suggest you have a - preferably narrow - PRIMARY KEY in all tables.


sys.dm_db_partition_stats - incorrect row count for InMemory tables

Details about In-Memory Tables cannot always be seen using the "normal" dynamic management views, or DMVs, such as sys.dm_db_partition_stats.

Instead, you'll need to use the In-Memory views detailed on MSDN.

For instance, this page talks about how to calculate row and table size for In-Memory tables.

MSSQLTips has a decent starting list of In-Memory related DMVs.


Sql server 2008 R2 installation fails with Error: 17204

This is a known issue which has several causes:

The first thing to check is your permissions on local machine:
Are You a local Administrator?
Is there a group policy that might prevent you from installing \ debugging?

If these do not resolve the problem in your case, and you have verified the required permissions, please provide more info:

What parameters do you use during installation (beside the default ones)?
Which Edition of SQL Server are You installing?
Do You use Default instance name?
On which O/S are You installing?

EDIT:
Next steps:
1. open the folder ":\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log
2. Find a file named Detail.txt
3. This file has a complete log, You need to find the FIRST error that occured during installation.
4. Paste it here, only the part with the error.

EDIT:
Added Solution to comments, waiting to hear if the problem is solved


Deny drop permissions for all other users except actual administrator - SQL Server 2008

Use a DDL trigger to prevent the drop if the login name doesn't match what you expect. Modify the database name and login name as appropriate.

USE myDB
GO

CREATE TRIGGER no_drop ON DATABASE FOR DROP_TABLE
AS
IF SUSER_SNAME() != 'DOMAIN\username'
BEGIN
    RAISERROR('No table dropping.', 16, 1)
    ROLLBACK TRANSACTION
END


SSRS2016 external parameters for mobile report

Microsoft help suggested:

.../<report-name>?<dataset-name>.<field-name>=<parameter-value>

Its seems SSRS does support parameters in the url,
here is a starting point


Can't change ORACLE_SID for container-registry.oracle.com/database/enterprise

Sure, you need to change pfile/spfile and set ORACLE_SID before the startup of the database to change the name of the instance. Meaning you need to change how the docker image starts the database, if you want this done automatically.
Simply passing some environment variables through docker after the database was started will not change anything.


Determine percentage from count() without cast issues

SELECT (count(refinst) * 100)::numeric / NULLIF(count(*), 0) AS refinst_pct
    -- count(refinst) * 100.0 / NULLIF(count(*), 0) AS refinst_pct  -- simpler
FROM   patients;

Do not use a subselect. Both aggregates can be derived from the same query. Cheaper.

Also, this is not a case for window functions, since you want to compute a single result, and not one result per row.

Cast to any numeric type that supports fractional digits, like @a_horse already explained.
Since you want to round() to two fractional digits I suggest numeric (which is the same as decimal in Postgres).
It's enough to cast one value involved in a calculation, preferably the first. Postgres automatically settles for the type that does not lose information.
Or, simpler yet: since we multiply anyway, use a numeric constant that's coerced to numeric automatically because of the decimal point (100.0).

It's generally a good idea to multiply before you divide. This typically minimizes rounding errors and is cheaper.
In this case, the first multiplication (count(refinst) * 100) can be computed with cheap and exact integer arithmetic. Only then we cast to numeric and divide by the next integer (which we do not cast additionally).

NULLIF(count(*), 0) prevents division by zero (raising an exception). We get NULL as (unknown) percentage if there are no rows at all.

Rounded to two fractional digits:
SELECT round((count(refinst) * 100)::numeric / NULLIF(count(*), 0), 2) AS refinst_pct
FROM   patients;


You can use SQL's EXCEPT clause.
https://msdn.microsoft.com/en-us/library/ms188055.aspx

Values in the first table (the left query) that are not in the second (the right query) will be shown.  You can change the table order to achieve the opposite.

select * from @Test1
except
select * from @Test2

card4       d1         amt
----------- ---------- ----------------------
1111        2015-01-01 55.1
2222        2015-01-01 23.12


Getting around the constraint "column must appear in the GROUP BY clause or be used in an aggregate function"

seharusnya seperti ini(supposed to be like this):

SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person
  JOIN license ON license.person_id = person.id
  JOIN car ON car.owner_id = person.id
WHERE person.name = 'Charles Bannerman'
GROUP BY person.name, person.id, license.expiry_date, car.car;


Sql Server audit could not write to the security log

It is a little bit silly, but after restarting the machine audit was enabled without any problem.


How long is the output of EncryptByPassPhrase(), relative to the input?

As far as my testing goes (using SQL Server Express 2014, SP1 and SQL Server Developer 2012 SP2, both 64-it), the formula when not using an authenticator for the return value (VARBINARY) length of:

ENCRYPTBYPASSPHRASE('_My_PassPhr@zE_yo_', {anything})

is:

28 + (8 * (DATALENGTH(@ClearText) / 8))

Try the following:

DECLARE @ClearText VARCHAR(8000);
SET @ClearText = 'testdfdf gkdj flkgjdlfkgjdlf gjlf gklf TE%$%^&^%HFGHFhg fkgh jfgkhæ';

SELECT LEN(ENCRYPTBYPASSPHRASE('_My_PassPhr@zE_yo_', @ClearText)) AS [ActualLength],
       28 + (8 * (DATALENGTH(@ClearText) / 8)) AS [EstimatedLength];

Returns:

ActualLength    EstimatedLength
92              92

And, if you change the datatype of @ClearText to instead be NVARCHAR(4000), and run it again, it returns:

ActualLength    EstimatedLength
156             156

Note: the formula looks like it can be reduced by cancelling out the 8s. However, that will cause it to not work correctly since it is banded-ranges of datalengths that fit into a "bucket":

input bytes    result length
-----------    -------------
 1 -  7        28
 8 - 15        36
16 - 23        44
24 - 31        52

So the (DATALENGTH(@ClearText) / 8) portion of the formula is enforcing that decimal values are ignored rather than the value be rounded. And that is accomplished by default behavior of dividing two INT values ;-).

UPDATE:

The testing done above does not use an option available to ENCRYPTBYPASSPHRASE: specifying an "authenticator". Doing so adds 16 bytes to the minimum length, and then while the increments are still in 8-byte steps, and while the banded-ranges are still 8 bytes each, the initial range is only 4 bytes so the ranges are offset by 4 as compared to the range boundaries when not using an authenticator. To help illustrate, the following chart shows the ranges and their corresponding result lengths:

input bytes    result length
-----------    -------------
 0 -  3        44
 4 - 11        52
12 - 19        60
20 - 27        68

The formula when using an authenticator for the return value (VARBINARY) length of:

ENCRYPTBYPASSPHRASE('_My_PassPhr@zE_yo_', {anything}, 1, {anything_1-128_bytes})

is:

44 + (8 * ((DATALENGTH(@ClearText) + 4) / 8))

NOTES:

The length of PassPhrase has no effect on the result length
An empty string for PassPhrase has no effect on the result length
In order for an Authenticator value to have any effect, the value of Add_Authenticator (the 3rd input parameter) must be set to 1
The length of Authenticator has no effect on the result length as long as it is at least 1.
An empty string for Authenticator will have an effect on the result length, and that effect is the same as setting Add_Authenticator to 0.
If either Add_Authenticator is set to 0, or Authenticator is an empty string or NULL, then the formula is the same as if there was no "authenticator".

The following is an expanded and improved test that shows both with and without an "authenticator", and makes it easier to change the @PassPhrase value:

DECLARE @ClearText NVARCHAR(4000),
        @Authenticator sysname,
        @PassPhrase VARCHAR(100);

SET @PassPhrase = '_My_PassPhr@zE_yo_';
SET @ClearText = 'testdfdf gkdj flkgjdlfkgjdlf gjlf gklf TE%$%^&^%HFGHFhg fkgh jfgkhæ';
SET @Authenticator = REPLICATE(N' ', 128);

SELECT LEN(ENCRYPTBYPASSPHRASE(@PassPhrase, @ClearText))
                                         AS [ActualLengthSansAuthenticator],
       28 + (8 * (DATALENGTH(@ClearText) / 8)) AS [EstimatedLengthSansAuthenticator];

SELECT LEN(ENCRYPTBYPASSPHRASE(@PassPhrase, @ClearText, 1, @Authenticator))
                                         AS [ActualLengthWithAuthenticator],
       44 + (8 * ((DATALENGTH(@ClearText) + 4) / 8)) AS [EstimatedLengthWithAuthenticator];


Looking for a possible multi-column index

No wonder you get stuck. You are facing contradictory requirements, which a b-tree index cannot easily reconcile. You can hardly optimize the search for date and score at the same time. If you order by date, the scores are spread out arbitrarily - or vice versa.

There is a way though. You can make use of a set of partial indexes.
Consider the high-end solutions under this closely related question.


You can ship the logs to as many sources as you want. Just add a new secondary server on the primary one. The impact will be on the Korean server as it will have to copy the logs from the backup directory and replay them.


Unable to get actual execution plan for SQL Server 2014 SP3

There's a lot of information in that blog post, and it looks like you've mixed up a couple of the sections.

The query_thread_profile XE available on SQL Server 2014 SP2 and higher puts the actual runtime metrics in the event output.  The cached plan is just a normal, cached (estimated) plan.

Pedro points out that you are responsible for the tedious task of mapping execution plan nodes (and their estimated rows) to runtime stats stored in the extended event output:

  And now comparing with the XE output, I can see the graphical plan and the actual and estimated rows that were read by that Index Scan:
  ...
  It can be an arduous task to do the above...

Later in the blog post, when discussing other XE available in newer versions (query_plan_profile and query_post_execution_plan_profile), Pedro mentions being able to get plans with a subset of the "Actual Plan" runtime metrics in them.  However, these aren't stored back in the plan cache - they are stored in the XE events.


According to the manual, "If multiple events are specified, the triggered action must be a compound SQL (compiled) statement (SQLSTATE 42601)".

BEGIN ATOMIC indicates an inlined compound SQL statement. You'll have to replace it with BEGIN to make it compiled.

It might be worth noting the difference between the inlined and compound SQL in case of a trigger. An inlined compound statement becomes part of the triggering statement execution plan, while the compiled compound statement is invoked much like a stored procedure would be, with the associated overhead. Subsequently, triggers with inlined statements might perform a bit better, which may become noticeable on a heavily loaded system.


Database Replication/Mirroring for Reporting Purposes

What you are looking for is called Transactional Replication, which is very commonly used and a good solution for avoiding contention on a transactional database. There are three databases in a transactional replication system, which are the publisher, the distributor and the subscriber. The publisher is the source database, the distributor is where all changes to the publisher are sent for distribution to one or more subscribers. The distribution database can be on the same server as the publisher or a different server. When you create a publication, you choose the articles to be included, such as tables, views, procedures, etc, and whenever a change is made to any of the objects and gets recorded in the transaction log, an agent, which is an executable, reads the transaction log, packages the change and sends it to the  distribution database. Then a separate agent, or executable, takes the packages from the distribution database and applies them to the subscriber.

You will be setting up a publication and subscriber on servers 1,2 and 3 and have all subscriptions point to a database on server 4. There are several articles on MSDN that have good descriptions of transactional replication.


Replication Error when adding small table as new article

This is a bug with Replicating BLOBS in transactional replication. when the snapshot agent ran, something updated the BLOBs currently in the publication and this broke ALL replication streams.

After removing the initial table from the publication it moved onto another table containing BLOB data.

To resolve this I separated all tables containing BLOB data into their own Publication and removed them from my main publication. The Log Reader Agent then kicked back in.


Postgres SUM over PARTITION BY "tolerate" null values

Your image from the comment (which should be text in the question) shows that you run the query with this WHERE clause:
WHERE ... AND ("SupplierFacilityId" IS NULL OR "SupplierFacilityId" = 647)

So you can just drop "SupplierFacilityId" from PARTITION BY to achieve what you want: rows with "SupplierFacilityId" IS NULL are treated as equal peers of the partition:
sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId"
                              ORDER BY s."DueDate" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "RunningSum"

Also, since the default framing option is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and assuming that (s."LocationId", s."PartId", s."DueDate") is defined unique (which makes RANGE and ROWS equivalent in this context), you can just drop the custom framing options:
sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId"
                              ORDER BY s."DueDate") AS "RunningSum"

Without said WHERE clause, your goal would be unachievable with a simple window function as you would have to use the same rows with NULL values in multiple partitions. You would have to multiply those rows first ...


I don't have any current database certifications; I only have the ones that my place of work requires me to have for being a sysadmin, and even those will likely lapse as I was declared to "not be a sysadmin" a couple of months ago.

Certification might help to differentiate you between different people when you're looking to get a job -- it's basically someone vouching for you, claiming that you have a given set of skills.

But if it came down to a person who's been doing stuff for years, vs. someone with a piece of paper that said they could pass a test, I'd go with the person with experience.  I've seen way too many certified Oracle DBAs who had no clue what they were doing.  (and when their systems started lagging like hell, refused to even look at their database until I "proved" that it wasn't something the application was doing wrong ... of course, we had to show them how to get statistics out of their database, as they had no clue -- they were glorified operators, making sure backups ran, and occassionally adding accounts for new users).

At one point, my work had set me up with an account on BrainBench, as I was required to get third-party certification avery few years in addition to my place of work's annual computer security tests.  In a few weeks I had managed to pass over 70 tests, which then meant I got 'job role certificates' in over 2 dozen roles.  I even managed to pass tests for software I had never used before (eg, Checkpoint Firewall-1), and some of the tests I really wonder about (why does a test on websever administration ask questions about SMTP headers, but none about HTTP headers?).

...

So, to confuse matters -- I'm a contractor for the US Federal government.  My employeer (the contracting company) wants me to have lots of certifications to make me look impressive as they use my resume to try to get more work for the company.  My place of work (government) just wants me to be able to do the job, and couldn't care less if I was certified or not.  If I need a new skill, I can get the time from my place of work, and the funding from one of the two to take classes ... but I haven't needed to in 6+ years.

If I want to take classes, just for the hell of it, so long as I can justify it benefitting my work, my employeer will pay out of my 'education benefit' (which right now is halved as we're within 12 months of our contract's end date), but I'd have to either get separate permission from my ATR (the civil servant I report to) if he's willing to cover my time, or if it's okay to take off during the middle of the day (like I did when I was working on my Master's degree)

...

So, to answer the questions:

Wilton Cake Decorating, levels 1-3
They might've helped me land my last job that I had a long list, but none were directly applicable to the job I was hired to perform. (at the time, Oracle, Sun iMS, LDAP, etc.)
No, but my employeer would if I wanted to.


I don't really understand what you don't understand in the quote you included (strangely, as a screenshot), as it explains the difference quite clearly, in my opinion.

Locks and latches have different scopes and lifecycles. Locks apply to what you might call database physical model elements -- tables, rows, index entries. Latches protect various memory structures the database server uses when executing SQL statements or performing its housekeeping tasks.

A transaction might hold one, multiple, or no locks at all on the objects it is processing, which signals to other transactions what access they can have to those objects. An object protected by a lock doesn't have to be "in memory"; for example, a table protected by a table-level lock may not even have any of its pages present in the bufferpool.

Worker threads acquire and release latches to strictly prevent other concurrently running threads (that may be executing tasks within the same transaction, other transactions, or on behalf of some server background process) from simultaneously accessing certain memory areas. For example, two transactions may hold locks for different rows on the same bufferpool page, which would not prevent them from concurrently accessing their respective rows, if it weren't for the page latch that ensures the entire page remains consistent for all readers and writers. And then there is a lazy writer process, which couldn't care less about any of those locks but still must acquire a latch before it can write a consistent page out to disk.

In other words, locks are a transaction synchronisation mechanism while latches help synchronise processes or threads.


In Postgresql, is it possible to change the maximum number of columns a table can have?

There's no run-time configuration option that will let you have more than about 1600 columns in a table.  You can redefine values and compile PostgreSQL from source. That might turn out to be harder than it sounds in your case.

A quick look at the source code seems to say the number of columns depends on the values of MaxTupleAttributeNumber and MaxHeapAttributeNumber. These are defined in src/include/access/htup_details.h.

So you might be able to increase the number of columns by

redefining those values, while
paying close attention to how their current values have been determined, then
recompiling.

Details are in source code comments.  Search for htup_details.h in the search dialog there.

I suggest you try building from source without making any changes first. After you can do that well, then try making the changes above. Consider digging around in the source code to determine whether there are any other dependencies that are not as well documented as these two.


What are the optimal mysqldump settings?

You are going to find this shocking, but you only need one major option : --opt

What is --opt ?

  This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.

  Because the --opt option is enabled by default, you only specify its converse, the --skip-opt to turn off several default settings. See the discussion of mysqldump option groups for information about selectively enabling or disabling a subset of the options affected by --opt.

Since --opt is already enabled, you do no need to specify --opt. Notwithstanding, you may need some necessary options that are not included.

Run this query on your database

SELECT engine,COUNT(1) TableCount
FROM information_schema.tables
WHERE engine IN ('InnoDB','MyISAM')
AND table_schema NOT IN ('information_schema','mysql','performance_schema')
GROUP BY engine;

If you have all InnoDB tables, then you need to specify --single-transaction. This will automatically deactivate --lock-tables and allow you to dump all table in the same point-in-time and allow new writes to come at the same time.

If you have one or more MyISAM tables, then you need to specify --lock-all-tables. This will automatically deactivate --lock-tables, deactivate--single-transaction, lock all tables across all databases, then create the dump. Writes to InnoDB tables can still occur, but they will just get queued up until the locks are released. Any DB Connection attempting writes to any MyISAM tables will actually get suspended until all locks are released.

Run this query : SELECT COUNT(1) Number_Of_Stored_Procedures FROM mysql.proc;

If Number_Of_Stored_Procedures is greater than zero, use --routines.

Run this query : SELECT COUNT(1) Number_Of_Triggers FROM information_schema.triggers;

If Number_Of_Triggers is greater than zero, use --triggers.

CAVEAT : Please Do Not Use --order-by-primary for dumping all databases because it can potentially make BTREE indexes rather lopsided on reload. --order-by-primary should only be used when dumping an individual table that you know has an integer primary key and will have lots of range scans from your application.

If you need more creative types of mysqldump backups, see my old post How can I optimize a mysqldump of a large database?.

Please read all the options for mysqldump.

UPDATE 2014-12-29 09:44 EST

  I have updated my mysqldump command (please see my edit). I have a last question though. Do you think it would worth using all of the following arguments as well? --add-drop-database\ --add-drop-table\ --complete-insert\ --delayed-insert\ --tz-utc

Looking at your comment and at your latest edit, let's look at each of these options and see if you need any of them

--opt : I already said you do no need to specify it because it is enabled by default.
--delayed-insert : I adamantly stay away from this with an all InnoDB database. In fact, I adamantly stay away from this PERIOD !!!. Since 1) it is possible for INSERT DELAYED can lose data, 2) it is converted to INSERT for MySQL Replication slaves, 3) there is an open bug report about it use with triggers back in MySQL 5.6 and is not considered a bug, 4) it is deprecated in MySQL 5.6, and 5) Morgan Tocker (well-known MySQL Guru) foresaw its deprecation back in 2012, you should forget this option ever existed. Don't ever, ever (infinity) use it !!!
--complete-insert : This will use INSERT INTO tblname (colnam_1,colnam_2,...colnam_n) VALUES ... instead of INSERT INTO tblname VALUES .... This could bloat the mysqldump if there are many columns in the table definition and many rows in the table. Don't use it.
--add-drop-table : Since --opt enables it for you, you don't need to specify it.
--add-drop-database : If you drop --add-drop-database, it just makes the DROP TABLE IF EXISTS (which was added by --add-drop-table) go faster.  Not using it simply lets DROP TABLE IF EXISTS occur for each table. Thus, using --add-drop-database is a matter of personal choice.
--tz-utc : If you plan to restore data to the same server you backup up from, you do not need --tz-utc. If you restore the data to another data center in a different time zone and ...

if you want to keep the same timezone you backup from, you don't need --tz-utc.
if you want the data to use the time zone of a new data center, you need --tz-utc.
EXAMPLE : Say you backup a database in New York. That's EST. If you have another data center in Seattle, that would be PST. If you want the New York Backup to be restored in Seattle and you want the timestamps in the database to still represent New York, you do not want to use --tz-utc.

--default-character-set

If you are restoring the dump to the same server, you do not need --default-character-set.
If you have an all InnoDB database, you do not need --default-character-set
Since utf8 is the default value, you do not need --default-character-set

Adjusting your latest edit, this is what you particularly need

mysqldump\
--host=localhost\
--port=3306\
--databases ****\
--user=****\
--password=****\
--single-transaction\
--routines\
--triggers\
--events\
--add-drop-database\
> dump/test.sql

Again, I say please read all the options for mysqldump.