How to improve MySQL Server Performance..?

Finding Duplicate Indexes

Back in January 2012, @gbn answer a question about duplicate indexes where he presented 2 views that came from Ronald Bradford's blog. I combined the two views into a single query to present duplicate indexes as follows:

SELECT
    ndx1.TABLE_SCHEMA,ndx1.TABLE_NAME,
    CASE
        WHEN ndx1.COLUMNS = ndx2.COLUMNS
        AND (ndx1.IS_UNIQUE = ndx2.IS_UNIQUE)
        THEN GREATEST(ndx1.INDEX_NAME, ndx2.INDEX_NAME)
        ELSE ndx1.INDEX_NAME
    END REDUNDANT_INDEX_NAME,
    GROUP_CONCAT(DISTINCT
        CASE
            WHEN ndx1.COLUMNS = ndx2.COLUMNS
            AND (ndx1.IS_UNIQUE = ndx2.IS_UNIQUE)
            THEN LEAST(ndx1.INDEX_NAME, ndx2.INDEX_NAME)
            ELSE ndx2.INDEX_NAME
        END
    ) INDEX_NAME
FROM
(
    SELECT
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,
        IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE,
        GROUP_CONCAT(CONCAT('`',COLUMN_NAME,'`')
        ORDER BY IF(INDEX_TYPE='BTREE',SEQ_IN_INDEX,0), COLUMN_NAME
        ) COLUMNS
    FROM
        information_schema.STATISTICS
    GROUP BY
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,NON_UNIQUE
) ndx1 INNER JOIN
(
    SELECT
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,
        IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE,
        GROUP_CONCAT(
        CONCAT('`',COLUMN_NAME,'`')
        ORDER BY IF( INDEX_TYPE = 'BTREE'
        , SEQ_IN_INDEX
        , 0)
        , COLUMN_NAME
        ) COLUMNS
    FROM
        information_schema.STATISTICS
    GROUP BY
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,NON_UNIQUE
) ndx2
ON ndx1.TABLE_SCHEMA = ndx2.TABLE_SCHEMA
AND ndx1.TABLE_NAME = ndx2.TABLE_NAME
AND ndx1.INDEX_NAME != ndx2.INDEX_NAME
AND ndx1.INDEX_TYPE = ndx2.INDEX_TYPE
AND CASE
WHEN ndx1.COLUMNS = ndx2.COLUMNS
AND (ndx1.IS_UNIQUE = 'NO'
OR ndx1.IS_UNIQUE = ndx2.IS_UNIQUE)
THEN TRUE
WHEN ndx1.INDEX_TYPE = 'BTREE' -- when BTREE
AND INSTR(ndx2.COLUMNS, ndx1.COLUMNS) = 1
AND ndx1.IS_UNIQUE = 'NO'
THEN TRUE
ELSE FALSE
END
GROUP BY ndx1.TABLE_SCHEMA,ndx1.TABLE_NAME,REDUNDANT_INDEX_NAME
;

Obviously, the indexes with the least column per grouping need to be eliminated

Find unused indexes on the basis of selectivity.

I have not done much with unused indexes in my developer days. I try to make only necesssary indexes that match the following clauses:

WHERE
GROUP BY
ORDER BY

In the event you have to cleanup a database by hunting down unused indexes, please read these:

Use Percona-Server rather than MySQL because Percona-Server has addition information_schema tables that record index usage since mysql startup.

http://www.mysqlperformanceblog.com/2009/06/26/check-unused-keys-a-tool-to-interact-with-index_statistics/
https://stackoverflow.com/a/3243517/491757

A very ancient tool called mysqlidxchk

Monitor the Server Parameters (What should be important parameters)

Innodb_buffer_pool_pages_dirty*100.0/Innodb_buffer_pool_pages_total : Pct of InnoDB Buffer Pool That Needs to Be Flushed (I would keep my eye on server load if this exceeds 5%)
(100.0 * (Delta(Innodb_buffer_pool_read_requests) - Delta(Innodb_buffer_pool_reads))) / Delta(Innodb_buffer_pool_read_requests : Read Efficiency Of InnoDB
100.0 * (1.0 - (Delta([Key_reads]) / Delta([Key_read_requests])) : Read Efficiency Of MyISAM (should be above 90%, anything less look into caching data with memcached or switching to InnoDB)

This is just a sample of the kind of global status values to monitor. Please read MySQL Documentation on the Server Status Variables.

Execute MySQL Server performance tuning script

Most straightforward script is mysqltuner.pl Just get it and run it

# wget mysqtuner.pl
# perl mysqltuner.pl

Slow logs

Slow logs can be quite helpful in a low-traffic environment. Unfortunately, I have seen too many occurrences of the following

High number of DB Connections
All Connections Running the Same Type of Query
Query in one connections blocking dozens of others needing the same table or row (even with InnoDB due to deadlocking issues associated with the Clustered Index when doing UPDATEs)

Given this scenario, I have queries that work standalone with blazing speed grind to a halt when an inundiation of queries needing common tables.

IMHO the slow query log actually does you no good becase it records completed queries that are regarded as slow. What you really want to do is catch long-running queries in the act of being long-running. Therefore, I would recommend using pt-query-digest to pool the processlist (or tmpdump) for queries running amok. I wrote a post back in December 2011 on how to script a crontab job that polls the processlist every 20 minutes using mk-query-digest (pt-query-digest can be inserted in it place).


SQL server AlwaysOn database not automatically seeding, cannot find logs with reason

On the secondary replica, you should be able to see the status / outcome of seeding attempts in the sys.dm_hadr_automatic_seeding DMV.  This includes columns like current_state, failure_state, and failure_state_desc, as well as timing related columns, that should give you a decent overview of seeding issues.

I have often seen seeding errors written to the SQL Server error log (also on the secondary replica), I don't think you mentioned looking there:

One thing I often forget to do is to run this on the secondary replica:

ALTER AVAILABILITY GROUP yourAgName GRANT CREATE ANY DATABASE;

Note: if you're interested in seeing which replicas have automatic seeding permissions, check out this blog post from Sean Gallardy: Automatic Seeding – Which Replicas Have Permission?

Once you've done that, you can attempt re-seeding by running this on the primary:

ALTER AVAILABILITY GROUP yourAgName
    MODIFY REPLICA ON 'SecondaryReplicaInstanceName'
    WITH (SEEDING_MODE = AUTOMATIC);

That normally gets me up and running.

As far as this question:

  Is there a canonical guide to ALL the places one is supposed to look for AlwaysOn related errors?

There is a great blog post from the SQL Server team here: Failover Detection Utility – Availability Group Failover Analysis Made Easy

It's about troubleshooting failovers specifically, but it lists out the main places where you'll see AG errors and diagnostic information:

SQL error logs
Always On Availability Groups Extended Event Logs
System Health Extended Event Logs
System log
Windows cluster log


Is query efficiency affected by data not relevant to the query?

Summary:  It depends on

Percentage of rows being fetch (at least according to imprecise statistics)
Size of buffer_pool (relative to size of table)
ENGINE
How much off-record blobs, etc, you have.
Whether the data is somewhat ordered by user_id.

Details:

For smaller numbers, I would simply say "No".  But you describe a very large table -- possibly large enough to have issues with caching.

Let me start by explaining a few things...

user_id = 1 is a tiny percentage of the table, so it is very likely that INDEX(user_id) will be used.
I am assuming you are using InnoDB.
The lookup will work this way:  Reach into the index for the first index row with user_id = 1; scan forward until that no longer holds true (1M rows as you say).  For each of those index rows, reach into the data BTree to get * (using the PRIMARY KEY).
If the data is not effectively sorted by user_id, that will be upwards of 1M lookups in the data.  InnoDB blocks are 16KB (by default).  So this might involve 16GB of data being loaded.
You mention blobs, etc.  They are probably stored 'off record', that is, in some other block.  Maybe another 16GB to be fetched?  If you don't need the blobs, don't say SELECT *, specify only what you want -- this is an example of a significant speedup.
If the other rows are full of NULLs, then that changes how many rows are packed in each 16KB block.  This may (but probably won't) mean that you won't need a full 1M blocks for the desired rows.  That is, the caching in the buffer_pool may be useful.
What is the value of innodb_buffer_pool_size?  If it is much less than 32GB, then there will be a lot of I/O.  If it a not more than 32GB, then a second run of that same query will find everything in cache, and run a lot faster.
If it weren't a "tiny percentage", but instead over about 20%, the Optimizer would probably decide that a table scan is cheaper than bouncing back and forth between the index's BTree and the data BTree.

(The EXPLAIN will point out whether the index will be used, but not the rest of the details.)


Given:

Friday is called "Friday"
The 3rd Friday of the month will always
fall from 15th-21st of the month

select thedate
from yourtable
where datename(weekday, thedate) = 'Friday'
and datepart(day, thedate)>=15 and datepart(day, thedate)<=21;

You could also use weekday with datepart(), but it's more readable with a name IMO. String comparisons will obviously be slower though.


What is the difference in performance between a two single-field indexes and one compound index?

I'll answer on the MongoDB side (no Postgres knowledge, sorry). The classic answer is that a query only ever uses a single index, so if you have a compound index, good data cardinality and are not using multiple $or clauses, then the compound index will significantly outperform the two single indexes. Some exceptions to this would be those $or clauses (each clause uses a single index and merges the results, so for small data sets this might be a wash) and a data set where a single index fits in memory but the compound index does not.

MongoDB supports intersection as of version 2.6 but the only reliable way to tell if it will select intersection as a viable strategy is to have a sample data set and indexes and try it out. Compound indexes are a far more reliable strategy for now, and would be my recommendation for searching on two fields generally with MongoDB.


Changed server name, now maintenance plan fails

You need to change the owner of the maintenance plan. It was set up to be owned by an account that no longer exists. Not sure if it is better in your scenario to use sa or the new local admin account. This update assumes you still have the sqladmin account after the rename and that it has been added as a login to SQL Server with admin privileges.

UPDATE msdb.dbo.sysssispackages
  SET ownersid = SUSER_SID('VM-MSSQL-2008R2\sqladmin')
  WHERE ownersid = SUSER_SID('WIN-6QFI9JAK804\sqladmin');

You may also need to reset the owner of the job associated with the maintenance plan, which you can do through the UI or via the following:

EXEC msdb..sp_update_job
  @job_name = N'MaintenancePlan.Subplan_1', -- or whatever it's called
  @owner_login_name = N'VM-MSSQL-2008R2\sqladmin';

If the login hasn't been added, then execute this first:

CREATE LOGIN [VM-MSSQL-2008R2\sqladmin] FROM WINDOWS;
GO
EXEC sp_addsrvrolemember N'VM-MSSQL-2008R2\sqladmin', N'sysadmin';

In the future, be sure to have the machine properly configured / named etc. before installing SQL Server. :-)


Insert-heavy InnoDB table won't use all my CPU

You have to crank up innodb_io_capacity as well.

The default is 200. Raise it to 5000 for starters. I would go to 20000.

You may also want to make sure ib_logfile0 and ib_logfile1 are sufficiently large. The default value for innodb_log_file_size is 5M. I would raise that to 1G for starters.

A larger InnoDB Buffer Pool would also help, perhaps 4G.

To recap, use these additional settings:

[mysqld]
innodb_io_capacity=5000
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

After adding these settings to my.cnf, to resize ib_logfile0/ib_logfile1 do the following

service mysql stop
rm -f /var/log/mysql/ib_logfile[01]
service mysql start

The files ib_logfile0 and ib_logfile1 are recreated. Don't worry, I have done this many times.

You may have to do something out of the ordinary for InnoDB

Try the following:

Full Table Lock on the InnoDB table
Perform the Bulk Load
Release the Lock


Remote access from domain and other pages on that domain

From your example:

GRANT ALL PRIVILEGES ON *.* TO user@example.com IDENTIFIED BY 'password' WITH GRANT OPTION;

That reference to example.com has little to do with the web site example.com and particularly not with users of that site or pages on that site.

Machines with IP addresses are often reachable by hostnames.  Given a hostname, DNS provides you with a way to look up the IP address(es) associated with that hostname.  Reverse DNS provides the opposite -- given an IP address, look up a hostname that can be used to reach that IP address.

MySQL allows user accounts to be associated with an IP address or range (e.g. 'user'@'10.1.1.1' or 'user'@'10.1.%', meaning 10.1..) but it also allows the IP address matching the rule to be derived from reverse DNS.  For 'user'@'example.com' to work, the IP address of the machine connecting to the MySQL Server must reverse-lookup to example.com, and a forward lookup of example.com must then return the connecting machine's IP address.

As you hopefully see, this operating at a layer where the page a user on the example.com web server is completely meaningless, and if other web sites were hosted on that same server, the permission for other sites hosted on that server to access the MySQL Server would not be any different.  The hostname on in a mysql user account serves only to identify the machine making the incoming connection.  MySQL has no idea why that machine might be connecting.


What will happen if I drop SYSAUX tablespace from Oracle 11G

First, it is important to understand the purpose of SYSAUX tablespace. As the name implies, it is intended to support system tablespace, by holding metadata about database components and other frequent changing data, such as reports.

Docs: "If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability."

The startup migrate option was introduced on version 9i, and it's only suitable when migrating oracle across different versions of database. So, why can I drop the sysaux tablespace with "startup migrate"? Only because Oracle will internally take care of the general errors that are expected to happen during migration processes. It will change a bunch of parameters through alter session, enable some trace events, all of these to make sure migration will happen smoothly. Example of parameters:

ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE=MEMORY;
ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE=MEMORY;
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

So do not drop sysaux tablespace if you don't have a reasonable reason to do so. If you need to manage its data, there are a lot of options:

You can query v$sysaux_ocuppants to determine what resources are taking space;
You can move some of its contents to another tablespace through packages/ procedures;


If you are rebuilding a clustered index, you are essentially rebuilding the table, but a non-clustered index is just a subset of the table (usually). If you use the SORT_IN_TEMPDB option, you will offload some of the logging to the tempdb transaction log. Version will also matter, in 2005 online rebuilding is minimally logged but was changed back to fully logged in 2008. You might also want to take a look at this article which might be useful in deciding whether to rebuild or reorganize with the log size in mind.

----Update----

Using Shanky's test setup I performed the same steps, but I added a few extra size checks.
Before the rebuild, I perform a log backup and check the log space and utilization as well as the records in the transaction log for my index. Then I do the rebuild test and recheck the log size and the number of log records:

backup log IndexLogging to disk = 'NUL'
--check log space/usage
dbcc sqlperf (logspace)
--check log records for the index, exclude the where for the total number of logs generated
SELECT LogRecordsGenerated = COUNT(*)
FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName = 'dbo.Logging.IX_LOGGING'
--Perform your rebuild operation and then recheck the same parameters

I tried this about a dozen times and each time I get the same results, the DMV is only telling you for the transaction, but there are several nested transactions that are spawned as a result of the log_test transaction that don't show up including the allocation of extents and pages and the insertion of the data. If you look at the actual contents of the log file, you can see how the offline rebuild is very efficient. It just allocates the pages/extents, formats them and sets them and then deallocates the old ones. An online rebuild is doing a lot more work since it has to keep the index available while it rebuilds.  This is probably more evident in the locking: offline it locks the entire object but online it has to go page by page, key by key.  You can take a look for yourself to compare:

SELECT
OPERATION,
Context,
[Transaction Name],
[AllocUnitName],
[AllocUnitId],
[Page ID],
Description,
[Lock Information], [Transaction ID]
FROM FN_DBLOG(NULL,NULL)

You can reduce the log generated by temporarily switching to BULK_LOGGED while your index maintenance runs, but you lose the ability to do point in time recovery for that period of time.  Apparently it also does not reduce your log backup.

--More info on log usage from DBCC SQLPERF (logspace) and SELECT LogRecordsGenerated = COUNT(*)
FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName = 'dbo.Logging.IX_LOGGING' to show actual log sizes, note the index is only 648KB

This is the log after populating it with data:

Database Name   Log Size (MB)   Log Space Used (%)  Status

IndexLogging    14.99219        70.89956            0

LogRecordsGenerated

10903

Here is the log after a log backup:

Database Name   Log Size (MB)   Log Space Used (%)  Status

IndexLogging    14.99219        5.100313            0

LogRecordsGenerated

0

Log after log backup and then Online rebuild:

Database Name   Log Size (MB)   Log Space Used (%)  Status

IndexLogging    14.99219        22.642              0

LogRecordsGenerated

11160

Log after log backup and then offline rebuild:

Database Name   Log Size (MB)   Log Space Used (%)  Status

IndexLogging    14.99219    10.79338    0

LogRecordsGenerated

140


MongoDB Replication Set Configuration Issue on CentOS 7

mongo --host 10.0.0.7/admin --port 27017 -u user -p mypassword

The above command takes 10.0.0.7 as replica set name and admin as the hostname. That is the reason you are getting the error.

Use any of the following commands to connect to your MongoDB database

mongo 10.0.0.7/admin --port 27017 -u user -p mypassword

mongo admin --host 10.0.0.7 --port 27017 -u user -p mypassword


Note that the location where the trace files are temporarily stored should be accessible from the server where the SQL Server instance you are tracing is running – SQL Server generates the trace and should be able to store the trace data in that location. xSQL Profiler will then grab those files from there and load them into the central repository.
If you still have trouble download and install the latest build from our site and then contact us at the support email so that we can help you.


cannot connect my oracle server to developer

First Logged in SQLPlus and Unlock the user

Run SQLPlus, connect as SYS user (use administrator's password you have entered during installation) . Suppose that here For your test connection unlock HR user and give him a new password:

C:\>sqlplus  sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 24 12:15:51 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter user hr identified by new_password;

User altered.

SQL> alter user hr account unlock;

User altered.

For Creating a Database Connection

The first step to managing database objects using Oracle SQL Developer is to create a database connection.Perform the following steps:

Open Oracle SQL Developer.
In the Connections navigator, right-click Connections and select New Connection.
Enter test for the Connection Name (or any other name that identifies your connection), hr for the Username and Password, specify your localhost for the Hostname and enter ORCL10(in your case, as it showing in your attached picture) for the SID. Click Test.
The status of the connection was tested successfully. The connection was not saved however. Click Save to save the connection, and then click Connect.

For further your ref Here , Here & Here


SQL Server Management Studio 18 won't open (only splash screen pops up)

I just overlaid the Microsoft.VisualStudio.Shell.Interop.8.0.dll in C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\PublicAssemblies with the copy from ..\PrivateAssemblies\Interop and the IDE opens. (Thanks to Mitch for discovering the offending DLL name.)

This looks like a popular Q & A today, so I'm glad to know this wasn't just me.


Set bit in Table A if values are present in Table B

update a
    set a.CHK = 1
from tableA a
join tableB b
    on b.IDA = a.IDA and b.IDB = a.IDB


MySQL 5.6 to 5.7 In-Place Upgrade or Fresh Install?

For such major version change like 5.5 to 5.6 or 5.6 to 5.7 I recommend to export/import rather than in-place.

Why do I prefer fresh-install rather than in-place? :

In MySQL 5.6 you have 29 tables under mysql schema and in 5.7 you have 33 tables under mysql schema.
If you are using GTID then it is not recommended to go for in-place upgrade.
Deprecated commands or data types.
Deprecated UDFs or changed functions.
Chances of password incompatibility.
Deprecated stuff will be thrown out as an error during import, thus gives us alarm. Though we don't go through white paper properly.

How do I proceed? :

Let's say in your case 5.6 to 5.7, on your test bed:

Take mysqldump or mydumper with master status. A consistent backup.
Take all grants out separately into an sql file.
Have fresh installation of 5.7.
Without performance_schema, mysql, information_schema or any system schema, import the dumped data into 5.7 and apply grants. Ensure you verify root and admin users are removed as it is a different server.
Now create slave link and go for down time on 5.6.
Now your test architecture will look like:

APP- > 5.6 SERVERA (Master of) -> 5.7 SERVERB (Master of)  -> 5.7 SERVERC (Slave of SERVERB)

And now do a consistency check of ServerA, ServerB, ServerC by considering frequently updating tables and take the data in chunk sets and verify if the data checksum is same as per its right state of changes.

But before everything we should ask ourselves what is the reason behind to go for different versions. Of course we can make use of new features. But if we verify conflicts in test bed at least we have a ground confidence to do the same to production.


You should enable trace flag 1802 on your database servers.  This will allow SQL Server to retain inheritable permissions when you detach a database so you won't experience this issue going forward:

http://support2.microsoft.com/kb/922804

DBCC TRACEON(1802, -1)

Add it to the startup parameters of the SQL Server service
http://msdn.microsoft.com/en-us/library/ms190737(v=sql.110).aspx


Should all machines which run mongo instances (mongod or mongos) in a Mongo DB cluster have unique hostnames (also at OS level)?

Actually, it is a Very Bad Idea™ to do a load balancing this way with MongoDB. First of all, you can give multiple mongos instances to the driver. If one mongos goes down or becomes unavailable due to overload, the driver will be happy to access the next available mongos. Second, the config servers and mongod instances get unnecessary load for continuously updating the cluster "map". Third, they most likely will initiate and tear down connections continuously, too.

The solution would be to use CNAMEs at the DNS level instead of multiple A records for a host, but maintain unique hostnames on the OS level. That is, the hostname command should return a unique value on every host of the cluster, including the mongos instances.

A word on DNS based round robin load balancing: It's crap when used on it's own. You don't have any control over the load actually sent to each of the balanced servers. In case you have caching DNS servers between you and your biggest group of unique users, you actually might worsen the load for an individual server. You better use a "real" loadbalancer in front of your servers, something like HAProxy, varnish or nginx (depending on your application). In case you need / want to have more than one of those, you can use DNS based round robin load balancing on these, though from my experience, it is better to make one load balancer highly available to prevent a single point of failure.


Changed max_allowed_packet and still receiving 'Packet Too Large' error

The first I thought about was what max_allowed_packet actually controls. Here is what I found:

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Given that definition of max_allowed_packet, I then discovered something else from ServerFault: innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones

Keeping these two things in mind, I would increase innodb_log_file_size in /etc/my.cnf to the max size allowed for it, 2047M. This of course requires the following

service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

This will accommodate any big blobs you may have in your data.


Ensuring no unwanted tables have changed after a risky operation is performed

psql -Atq -c 'SELECT * FROM atable ORDER BY id' | md5sum


How to call modify() on a nodelist in T-SQL?

Three options come to mind.

Use a while loop and modify one node at a time

Shred the XML using nodes(), modify the values and then rebuild using for xml.

Cast the XML to a string and use replace().

Whatever works best in your case depends on the shape of the XML and what kind of modification you need to do. If you add the table structures with sample data and expected output to the question you might get a better answer.


OCI (Oracle Call Interface) is not ODBC. ODBC drivers may use OCI, but not the other way round.

I've never heard of Oracle charging for ODBC drivers/access. In fact, there's no mention of an ODBC component in the global price list (link here).

I would clarify with your vendor - maybe they're trying to extract some cash out of you by supplying/supporting some kind of additional product, or maybe it's just a miscommunication.


Mysql database replication on different vlan/subnet/another site

Being asynchronous, replication works very well across WANs or otherwise remote locations. I have used such topologies many times in the past (e.g. replication from West Coast to East Coast, from US to Middle East etc.).

Just as with normal replication, but somewhat more obvious, you must watch for slave lag if you intend to read from slave and you must have the very latest data updated. Due to latency, bandwidth issues etc. you may find that your remote slave tends to lag more than a local slave. This doesn't have to be the case and I have witnessed remote slaves keeping up just fine with the master.

If you're using a VPN, there's nothing special about setting up this kind of replication. Otherwise, please consider using replication with SSL, so that your traffic is encrypted.


I've got 16GB of ram, how should I configure MySQL Server?

Please keep in mind that prior to MySQL 5.1.38, InnoDB was not designed to access multiple CPUs. You have one of two options to get MySQL to take advantage of multiple CPUs.

OPTION 1 : Install MySQL 5.5
OPTION 2 : Install MySQL 5.1 InnoDB Plugin

There are new options available since MySQL 5.1.38 (InnoDB Plugin Only) that is now fully available in MySQL 5.5

Rather rewrite what I wrote in past posts about this subject, please reads my past posts discussing those new settings:

About single threaded versus multithreaded databases performance
Possible to make MySQL use more than one core?
Multi cores and MySQL Performance

As for tuning all other aspects of MySQL, please refer to my past post about looking into disk space management and storage engine tuning : MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load


SQL Server database generate script how to avoid adding PRINT statements

I would not remove the GO statements that break up large batches.  There are various reasons for breaking large inserts

size (duration) of transactions
memory requirements
transaction log management
mirror synchronicity
error isolation (by batch)
etc...

As for removing the status updates, that can be done easily.  Press Ctrl-H or from the menu, Edit -> Find and Replace -> Quick Replace, fill it in as shown in the image below making sure to tick "Use" Regular Expressions, then click on "Replace All" to zap them all.


copy command in postgresql to append data

Assuming a Unix system

with PostgreQSL 9.3 or newer

Use the PROGRAM clause with cat as the program.

execute 'copy (select * from tableName) to PROGRAM ''cat >>/tmp/result.txt'''

This would append the results to the end of /tmp/result.txt instead of overwriting the file.

with older versions of PostgreSQL

A named pipe can be used by a separate process to concatenate results.
Example:

$ mkfifo /tmp/pgfifo
$ while true; do cat /tmp/pgfifo >>/tmp/result.txt; done

This last command will block. Let it run until all the results are accumulated, then it can be terminated with ^C or kill.

In SQL, the fifo has to be fed with:

execute 'copy (select * from tableName) to ''/tmp/pgfifo'''


Moving TDE database to a new database but having problems with the cert

You will need to create a master key on the new server - if it does not exit already.

USE MASTER;
GO
if not exists (select 1 from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong_Password';

Then you have to restore your certificate:

CREATE CERTIFICATE serverCert  FROM FILE = 'C:\cert_Backups\servercert.cer'
WITH PRIVATE KEY (FILE = 'C:\cert_Backups\servercert.key'
    ,DECRYPTION BY PASSWORD = 'strong_Passw0rD')
GO

But for this to work you will have to make sure that you made a backup of the original certificate with the private key.

Backup certificate ServerCert
 to file = 'C:\cert_Backups\servercert.cer'
 with private key (file = 'C:\cert_Backups\servercert.key',
 encryption By Password = 'strong_Passw0rD')
GO

If you only have a cer file and not a key you will need to backup your certificate again


Transaction log dump - backup log from all database to one Dump file

Just a guess, but I suspect someone likely only wanted to manage one backup file without thinking of the consequences if it became corrupted.

This approach does backup the TLogs, so you can recover from this backup file.  However, shrinking the log is a bad idea for many reasons (as explained by Brent Ozar and Aaron Bertrand).


Is it possible to restrict SQL Server DB_Owner when connecting using SQL Server Mgt Studio?

Changing this EXE program seems to be out of the question

That makes using application roles not an option. However, here's the link anyway since it's good to know: Application roles

Your second option would be using logon triggers. Link for more info
What you do is, once somebody logs in, you check which application name they are using and if it's not your EXE, you deny access by aborting the logon..

However, this is not bullet proof since the "application" string can be spoofed. It might however be "good enough" for stopping the curious people.

Security should be designed in such a way that people that are able to log in to a database can only do what they are allowed to do. So your best option (but the most complicated to implement at this stage) Would be to revoke the DBO permission an give more granular permissions. If you need business logic to be forced your only option is to implement that in stored procedures and only give execute permissions to those stored procedures.. However, that might again mean chaning the EXE.


Error restoring SQL differential database files

To check the LSN number from the backup file do:

restore headeronly from disk='g:\backups\name_of_backup_file.bak'

The result set will have a column which will show you the last LSN number. As gbn said, to restore from a diff backup you need a full + last diff backup from the same backup set.


MySQL use both FULLTEXT and normal index

MySQL very rarely use more than 1 index per table in a query, and never intersect a FULLTEXT index with any other.

Your plan show - for some reasons it choose FT as best choice for this query.

  Index Merge is not applicable to full-text indexes. We plan to extend
  it to cover these in a future MySQL release.

merge optimisation

edited after question:
You can change index

KEY type (type,idElem,name,state);

to the -

KEY type (type,name,state);

and compare - MySQL could start use it, and depending from Your data it could be better (could be not). If MySQL continue use FT index only, You can make tests with index hints - FORCE INDEX ('type').

Remark for comment from Rick James:
not always, in case of range - yes (at least I have not other examples) and agree with You,

but in case (AND = AND = AND =) as in original question (or search by PK) it could take index for direct search.

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  `text` varchar(2048) DEFAULT NULL,
  KEY `idx` (`id`,`value`),
  FULLTEXT KEY `text` (`text`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

populate and simple test:

select * from t1 where (id >2 and id < 4) and MATCH (text) AGAINST ('very')

as predicted by You:

1   SIMPLE  t1      fulltext    idx,text    text    0   const   1   11.11   Using where; Ft_hints: sorted

but:

select * from t1 where id =3  and value = '3' and MATCH (text) AGAINST ('very')

and it take other index:

1   SIMPLE  t1      ref idx,text    idx 262 const,const 1   11.11   Using where

so it always need - make and test


Why does @@dbts increase after backup/restore?

The value for dbi_maxDbTimestamp is stored on the database boot page. (page 9 in the primary data file).

This is not written to every time a timestamp value is allocated. Instead SQL Server reserves a few thousand at a time.

For example if @@DBTS is 2000 and the dbi_maxDbTimestamp is also 2000 then SQL Server updates the value written in the boot page to 6000 the next time it needs a timestamp value.

The values from 2001 - 5999 are allocated in memory and "lost" if the database is set offline and then online again.

The backup contains the copy of the boot page that has been updated to 6000. So upon restoring it the timestamp values will start from this number. It knows nothing about any lost intermediate values.

To see this

CREATE DATABASE DbtsTest

GO

USE DbtsTest

GO

DBCC TRACEON(3604);

CREATE TABLE T (X ROWVERSION)

SELECT CAST(@@dbts AS BIGINT);

DBCC PAGE(DbtsTest,1,9,1)

On my system for a newly created database @@dbts is 2,000. The DBCC PAGE output from above is

I have highlighted the timestamp value. CAST(CAST(REVERSE(0xD007000000000000) AS BINARY(8)) AS BIGINT) = 2000

INSERT INTO T DEFAULT VALUES

SELECT CAST(@@dbts AS BIGINT);
DBCC PAGE(DbtsTest,1,9,1)

Now the @@dbts is reported as 2001 but looking at the page itself.

the timestamp value has changed. CAST(CAST(REVERSE(0x7017000000000000) AS BINARY(8)) AS BIGINT) = 6000.

Running

DBCC DBTABLE('DbtsTest')

at this point to view the DBTABLE structure shows both values

dbt_maxDbTimestamp = 6000
dbt_dbTimestamp = 2001

Finally

BACKUP DATABASE [DbtsTest] TO
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\DbtsTest.bak'
WITH NOFORMAT,
     NOINIT,
     NAME = N'DbtsTest-Full Database Backup',
     SKIP,
     NOREWIND,
     NOUNLOAD,
     STATS = 10

Then looking at the backup shows it is the 6,000 figure that is written.

Restoring the database and querying SELECT CAST(@@DBTS AS BIGINT) returns 6,000 as expected.


Pagination, check number of selected records

Why generate a temporary that drops on disconnect ? You would have to reload the table of ids, which requires extra code to manage the emptying and reloading. If you create a subquery that limits to 100 ids at a time, you could join to the cache_table:
Just create an inline query for the first 1000 rows, then join
SELECT ct.column_names FROM
(SELECT id FROM comments WHERE user_id = 123 LIMIT 1000) c
LEFT JOIN cache_table ct ON c.id = ct.id;

If you have to paginate, adjust in the inline query to paginate on the IDs then join last
SELECT ct.column_names FROM
(SELECT id FROM comments WHERE user_id = 123 LIMIT 0,100) c
LEFT JOIN cache_table ct ON c.id = ct.id;

SIDE NOTE: I highly advise against creating temporary InnoDB tables. They are much harder to cleanup after a crash because of leftover entries in the ibdata1 file (home of the data dictionary). MyISAM temporary tables are easy to cleanup just by simply deleting the .MYD and .MYI files in the /tmp folder.
UPDATE 2014-12-22 18:19 EST
It sounds like you want Dynamic SQL based on the row count of tmp
CREATE TEMPORARY TABLE IF NOT EXISTS tmp ENGINE=MyISAM
SELECT id FROM comments WHERE user_id = 123;
SELECT COUNT(1) INTO @reccount FROM tmp;

SET @pg_origin = ???;
SET @pg_offset = 100;

SET @q1 = 'SELECT column_names FROM comments c JOIN tmp t ON c.id = t.id';
SET @q2 = CONCAT('SELECT column_names FROM comments c JOIN cache_table ct ON c.id = ct.id LIMIT ',@pg_offset,',',@pg_origin,';');
SET @sql = IF(@reccount < 1000,@q1,@q2);

PREPARE s FROm @sql; EXECUTE s; DEALLOCATE PREPARE s;

I still strongly recommend using MyISAM for the temp table since the row count is written in the .MYD header of the file (O(1) execution time to get the count) whereas an InnoDB temp table required physically counting the rows (O(n) execution time to get the count).
As for the pagination, all you would need is to set @pg_origin to set where to begin retrieval.
As for InnoDB temp tables, sometimes they do not properly drop on disconnect and leave pigeon holes in ibdata1. I wrote about this problem about 2.5 years ago

mysql 5.5 corrupt - missing temporary table
MySQL insists on trying to use InnoDB even when told not to, please help
Table is dropped but ibd file still exists (Recent Post)

UPDATE 2014-12-23 13:08 EST
Your last comment

Can you think about some other suggestions except UPDATE 2014-12-22 18:19 EST? Is it possible to somehow eliminate the use of a temporary table?

If you look at the UPDATE 2014-12-22 18:19 EST, you'll notice that it only creates and loads the table once because I use CREATE TEMPORARY TABLE IF NOT EXISTS tmp instead of CREATE TEMPORARY TABLE tmp.
If you know user_id 123 has new comments you would drop, create and load the temp table.
Even if you don't want the temp table at all, you need to get the count anyway. So, go get count, move it around in a session variable from page to page. Then, apply the Dynamic SQL against that session count rather than having the table.


Transactional Replication: Cannot continue the execution because the session is in the kill state

Issue is resolved as of now. Just rebooting the server solved the problem.

Note: Raised EXCEPTION_ACCESS_VIOLATION stack dumps


Creating a normalised Twitter database and foreign keys

So, for each tweet, you can have no more than one hashtag? That doesn't sound right to me.

For tweets and hashtags (and probably for the other four connected tables), I would create a "bridge" table, tweet_hashtag. This table would consist of:

tweet_id (foreign key to the tweets table)
hashtag_id (foreign key to the hashtagstable)
if appropriate, other columns that would apply only to the bridge table, if any (I can't think of any in this case, as I assume a tweet is created at  a single moment in time; if tweets could be edited, then a created_on date/time column could be appropriate)
(Note: the combination of tweet_id and hashtag_id should be the primary key for this table, as you presumably should only note a given hashtag appears in a given tweet once. You can create a surrogate key (probably just an auto-increment integer or bigint value) if you really want to, or if you do need to be able to have multiple entries of the same hashtag for a single tweet for some reason)

This allows a given hashtag to be associated with many tweets, and a given tweet to have multiple (or no) hashtags.

As noted above, the same probably applies to the other four fields associated with the tweets table (user_mentions, urls, media, symbols).


Speed up count queries on a couple million rows

While computing numbers for all or most users, it's much more efficient to use plain subqueries to aggregate counts per user before joining instead of correlated subqueries:

SELECT u.*, p.product_count, c.collection_count
FROM   users u
LEFT   JOIN (
   SELECT user_id AS id, count(*) AS product_count
   FROM   products
   GROUP  BY 1
   ) p USING (id)
LEFT   JOIN (
   SELECT user_id  As id, count(*) AS collection_count
   FROM   collections
   GROUP  BY 1
   ) c USING (id);

The index-only scan and bitmap index we see in the EXPLAIN output only benefit queries for a small subset of rows (LIMIT 100). Your test is misleading in this respect. While computing numbers for all (or most) users, indexes won't help. Sequential scans will be faster.

The Bitmap Heap Scan you see is only the second step needed for the bitmap index scan - which comes as a surprise for just 100 rows. Either your table statistics are out of date or those 100 users have a lot of related products or rows in products are highly clustered (physically, meaning multiple rows for one user reside on the same or few data pages). Postgres only switches to a bitmap index scan if it expects to find multiple rows per data page, which comes as a surprise for 100 users and 52.000.000 products (rows=355 expected and rows=382 found).

Related:

Index usage on a temporary table
Configuring PostgreSQL for read performance


Can two sessions create #temp tables with the same name?

SQL server always append some random number in the end of a temp table name (behind the scenes), when the concurrent users create temp tables in their sessions with the same name, sql server will create multiple temp tables in the tempdb.

I created 3 temp tables called #TempTable in three different sessions in my SSMS, now if I go to the tempdb I can see the temp tables created there with a random (unique) string appended to each temp table's name.


SQL Server : how to create user with access only from specific HOSTNAME or IP addres

You can use a Logon Trigger for that.

CREATE TRIGGER TR_check_ip_address
ON ALL SERVER
FOR LOGON
AS
BEGIN

    DECLARE @ip_addr varchar(48)

    SELECT @ip_addr = client_net_address
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID

    IF ORIGINAL_LOGIN() = 'bob' AND @ip_addr <> '127.0.0.1'
        ROLLBACK;

END

If you try to connect from an unauthorized IP, you'll get an error:

Logon failed for login 'bob' due to trigger execution.

Remember that Logon Triggers are potentially evil and might also end up locking everyone out of the instance. Be careful!

However, I think you don't really need to do that. If you want to enable connections from a list of known addresses, a firewall is the most suitable tool for the job. The worst thing that could happen is the wrong user connecting from a known IP address, which is quite unlikely if users keep their credentials carefully.

Also, take into account that IP addresses can be spoofed, so I'm not sure which degree of additional security you would be providing.


Trigger function check if all NEW.* fields exist in another table

Your attempt fails for multiple reasons. First of all a row is not an array. This construct is just not possible:

FOR field IN NEW LOOP ...

But there is more.

I am not sure I like the general idea. This kind of trigger would run for at least every statement (don't use a row-level trigger for this!), which is quite a bit of overhead. It is also error-prone to put DDL commands in a trigger. Especially if you are a beginner.

That said, here is a proof of concept:

Trigger function:

CREATE OR REPLACE FUNCTION trigger_hv()
  RETURNS trigger AS
$func$
DECLARE
    _sql text;
BEGIN
   SELECT INTO _sql
         'ALTER TABLE ' || quote_ident(TG_RELNAME || '_hv') || ' ADD COLUMN '
       || string_agg(quote_ident(attname) || ' ' || att_type, ', ADD COLUMN ')
   FROM  (
      SELECT attname, format_type(atttypid, atttypmod) AS att_type
      FROM   pg_attribute
      WHERE  attrelid = TG_TABLE_NAME::regclass
      AND    NOT attisdropped   -- no dropped (dead) columns
      AND    attnum > 0         -- no system columns
      ) a
   LEFT   JOIN (
      SELECT attname
      FROM   pg_attribute
      WHERE  attrelid = (TG_TABLE_NAME || '_hv')::regclass
      AND    NOT attisdropped
      AND    attnum > 0
      ) b USING (attname)  -- ignoring data type!
   WHERE  b.attname IS NULL;

   IF _sql IS NOT NULL THEN
      EXECUTE _sql;
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER trg_foo_hv
BEFORE INSERT ON foo
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_hv();

SQL Fiddle.

This is a basic proof of concept. It does not check for matching data types and ignores things like schema search_path, NOT NULL constraints or COLLATION.

Related (with more explanation and links):

Change column type from varchar to text in all tables at once
Update multiple columns in a trigger function in plpgsql
Select rows where value of second column is not present in first column


mismatch between server schema content and exported sql rows

The issue disappeared when I changed the engine from MyISAM to InnoDB. I understand that InnoDB has row locking.


Which should be used after messing up clustered index? Update statistics, Reindex or Reorganize

You may have misunderstood the way a clustered index works - by definition the data is logically ordered in the sequence of the clustering key, although it's possible for the clustered index to become physically fragmented as page-splits occur when new data is inserted.

If you want to reduce fragmentation either REBUILD or REORGANIZE can be appropriate.
The rule of thumb is to do nothing when fragmentation is below 5%, REORGANIZE when fragmentation is between 5% and 30%, and REBUILD when fragmentation is above 30%.

Index fragmentation is recorded in the DMV sys.dm_db_index_physical_stats. See also the MSDN page on REBUILD/REORGANIZE

It's worth noting that unless you are running SQL Server Enterprise edition, REBUILD cannot be performed on-line - meaning that your table will be inaccessible for the duration of the command. REORGANIZE is always carried out on-line.


Standards or Best Practices for Error Handling in SSRS

As billinkc noted in his comment there is no way to propagate the subreport errors to the main reports.

As evidenced in this MSDN post you could try to use data driven subscriptions to drive the subscription, but that could get messy if there is any complexity involved real quick.

If you look at the SQL Agent jobs for a server using SSRS subscriptions you will see a bunch of SQL Agent jobs with a GUID as name, you could modify those jobs to include an extra step running some data validation steps before actually running the report failing the job before the report is sent out (risking your job definition being overwritten by SSRS) or calling the job from another job after validating the data logic.

You could identify the GUID associated with the report using this query:

SELECT
            b.name AS JobName
            , e.name
            , e.path
            , d.description
            , a.SubscriptionID
            , laststatus
            , eventtype
            , LastRunTime
            , date_created
            , date_modified
    FROM ReportServer.dbo.ReportSchedule a JOIN msdb.dbo.sysjobs b
          ON a.ScheduleID = b.name
            JOIN ReportServer.dbo.ReportSchedule c
            ON b.name = c.ScheduleID
            JOIN ReportServer.dbo.Subscriptions d
            ON c.SubscriptionID = d.SubscriptionID
            JOIN ReportServer.dbo.Catalog e
            ON d.report_oid = e.itemid
    WHERE e.name = 'Sales_Report'

(taken from here)

TL/DR: there is no clean solution but you could get away with some hacks, you would need to implement logic in a data driven subscription or create an extra job/modify the generated job

PS: I didn't test any of these approaches


Looks like that is a default setting in postgresql 8.5 onwards to guarantee recovery of higher precisions floats..

http://archives.postgresql.org/pgsql-bugs/2009-09/msg00171.php


How would I move SQL MSSQL.1 and MSSQL.2 location?

First shut down sql server

Then the best option is to robocopy everything from the disk to the new disk with the existing security.

when this is done, remove the old disk, and give the drive letter of that disk to the disk which you robocopied to. After that start SQL Server again.

This way SQL Server will not notice they are now on a new disk.


Default SQL Server 2008 locking behaviour

It really depends on how much you're updating. Locks will escalate as the size of the query increases. If many rows within the same page will be modified, SQL Server will escalate to a PAGLOCK. If many pages will be modified, it will escalate to a TABLOCK.


If I understand you correctly,

{A} determines {C,F}, and {C}determines {D}, so {A} determines {C, D, F}

Also {B} determines {E},

Put together, this means {A, B} uniquely determines the entire row {A, B, C, D, E, F}. Therefore, columns {A, B} is a key of relation R.

Now, 2NF is violated if a non-key field is determined (functionally dependent) by a proper subset of the key, which is obviously the case here for {B} -> {E}. i.e. part of the key {A, B} alone is sufficient to determine a non-key field {E}. This causes all the redundancy issues etc. and violates 2NF. So R is not 2NF.

To normalize R to 2NF, we need to decompose R into

R1 = {A, C, D, F}

and

R2 = {B, E}

R3 = {A, B}

Now, {A} is key for R1 and {B} is key for R2, and they have no proper subsets (non-empty). {A, B} is key for R3 and there is no non-key field in R3. Therefore, R1, R2, R3 are in 2NF.

3NF is violated if a non-key field functionally depends on another non-key field. This is the case for R1 where we have {A} -> {C} and {C} -> {D}, which causes similar problems with the violation of 2NF. To fix/normalize this, we can split R1 = {A, C, D, F} further into:

R11 = {A, C, F}

and

R12 = {C, D}

Now, it's easy to verify that R11, R12, R2, R3 together are in 3NF.


Making dynamic queries for linked server in SQL Server

You can do it this way:

DECLARE @company AS nvarchar(50);
DECLARE @id nvarchar(MAX);
DECLARE @query nvarchar(MAX) = N'SELECT @id = id from company where name = @comp';

exec [LINKSERVER12].[MYDB].sys.sp_executesql @query, N'@comp nvarchar(50), @id nvarchar(MAX) OUTPUT', @comp = @company,@id = @id OUTPUT;

select @id;


The rebuild of the table and indexes probably caused the statistics about the distribution of the values of certain columns to reset thus causing the optimizer to be fed inaccurate information.

You will probably need to change the statistics gathering for some of the columns.  See Oracle Documentation on Histograms


Is it possible to give PDB name to a procedure and select from it?

Sorry, but this is just wrong. A dblink can not be provided as a parameter like that. And a dblink is not required at all for this.
You can access information about all tables in all PDBs using the CDB_TABLES view.
CDB_* Views

For every DBA_* view, a CDB_* view is defined. In the root of a
multitenant container database (CDB), CDB_* views can be used to
obtain information about tables, tablespaces, users, privileges,
parameters, and so on contained in the root and in pluggable databases
(PDBs).

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

SQL> select table_name from cdb_tables t join v$containers c on t.con_id = c.con_id
     where c.name = 'PDB1' and t.owner = 'BP';

no rows selected

SQL> select table_name from cdb_tables t join v$containers c on t.con_id = c.con_id
     where c.name = 'PDB2' and t.owner = 'BP';

TABLE_NAME
--------------------------------------------------------------------------------
T1

SQL>

You can easily provide the PDB name and table owner as parameters to a query like above.


PostgreSQL multi-column unique constraint and NULL values

You can do that in pure SQL. Create a partial unique index in addition to the one you have:

CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL;

This way you can enter for (id_A, id_B, id_C) in your table:

(1, 2, 1)
(1, 2, 2)
(1, 2, NULL)

But none of these a second time.

Or use two partial UNIQUE indexes and no complete index (or constraint). The best solution depends on the details of your requirements. Compare:

Create unique constraint with null columns

While this is elegant and efficient for a single nullable column in the UNIQUE index, it gets out of hand quickly for more than one. Discussing this - and how to use UPSERT with partial indexes:

PostgreSQL UPSERT issue with NULL values

Asides

No use for mixed case identifiers without double quotes in PostgreSQL.

You might consider a serial column as primary key or an IDENTITY column in Postgres 10 or later. Related:

Auto increment table column

So:

CREATE TABLE my_table (
   my_table_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY  -- for pg 10+
-- my_table_id bigserial PRIMARY KEY  -- for pg 9.6 or older
 , id_a int8 NOT NULL
 , id_b int8 NOT NULL
 , id_c int8
 , CONSTRAINT u_constraint UNIQUE (id_a, id_b, id_c)
);

If you don't expect more than 2 billion rows (> 2147483647) over the lifetime of your table (including waste and deleted rows), consider integer (4 bytes) instead of bigint (8 bytes).


Restore a database backup on another server without sysadmin account

I think your main issue is that after restoring the database to your new target user, that your account "WebUser" is effectively orphaned.

You should have your DBA change the SID for WebUser (and any other user that matters) to be the same as in your source server.

Note that this will require dropping the user and re-creating it in the target server.  And any other databases will need to be fixed as well.

But this will allow you to restore the database from the source server, to the target server and not have to worry about permissions when you get done.


http://searchoracle.techtarget.com/answer/Sum-of-two-table-row-counts-as-one-answer

 select ( select count(DISTINCT nom) from prodbiscuit WHERE status =2 )
 + ( select count(DISTINCT nom) from prodganache WHERE status =2 )
 + ( select count(DISTINCT nom) from prodmacaron)
      as total_rows

Finally I've found it at given link


SSMS 2014 Gives an exception when opening the new query window

This issue has been posted as a Microsoft Connect item.

2012 SSMS System.Windows.Media.Fonts Exception On New Query

A possible workaround was posted by the author as follows:

  I have found that if change the font for the "Text Editor" from the default of "Consolas" to something else (e.g. Lucida Console), this problem goes away.


How to break table into two without losing performance?

OPTION #1 : Use INT UNSIGNED instead of BIGINT

If the fields will not exceed 4,294,967,295, change them to INT UNSIGNED

ALTER TABLE part1
    MODIFY COLUMN id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
    MODIFY COLUMN first  INT UNSIGNED NOT NULL,
    MODIFY COLUMN second INT UNSIGNED NOT NULL;
ALTER TABLE part2
    MODIFY COLUMN link  INT UNSIGNED NOT NULL,
    MODIFY COLUMN third INT UNSIGNED NOT NULL;

Smaller datatypes, especially for JOIN keys, will make the same query go faster.

If the fields will not exceed 16,777,215, useMEDIUMINT UNSIGNED for even smaller columns.

OPTION #2 : Use a Bigger Join Buffer

Add this to my.cnf

[mysqld]
join_buffer_size = 16M

Then, login to MySQL and run

mysql> SET GLOBAL join_buffer_size = 1024 * 1024 * 16;

MySQL restart not needed.

See MySQL Documentation about join_buffer_size

OPTION #3 : Make sure link is indexed

Since you have a FOREIGN KEY reference, this is a rather moot point. If you do not have the FOREIGN KEY, make sure link is indexed:

ALTER TABLE part2 ADD UNIQUE KEY (link);

Give it a Try !!!

UPDATE 2014-08-22 17:13 EDT

I created my own version of the Sample Data using this:

DROP DATABASE IF EXISTS GILI; CREATE DATABASE GILI;
USE GILI
create table original
(
    id mediumint unsigned not null auto_increment,
    first mediumint unsigned not null,
    second mediumint unsigned not null,
    third mediumint unsigned not null,
    primary key (id)
);
create table part1 like original;
alter table part1 drop column third;
create table part2 select third from original;
alter table part2 add link mediumint unsigned not null first;
alter table part2 add primary key (link);
insert into original (first,second,third) values (1,2,3);
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into part1 (id,first,second) select id,first,second from original;
insert into part2 (link,third) select id,third from original;
select count(1) from (select * from original) A;
select count(1) from (select * from part1 inner join part2 on part1.id = part2.link) A;

Results are roughly the same as yours: About 3x's slower. The, I read your stackoverflow link. Then I got to thinking: Look at the sample we used. We basically pushed MySQL to its limit to join 1M rows with 1M rows. This is the worst-case equi-join scenario. All things considered, the performance is pretty good.

I remember my college days where I had to create a two-dimensional array using linked lists to construct a sparse array. If a node did not exist in a particular coordinate, the default value for the array was defined as zero in the app. Then, imagine this. Creating a 1000x1000 sparse array where all 1000000 (1 million) coordinates had a nonzero value represented. Now, you had at least 2.002 million pointers mapping all adjacent nodes. This is in additional to the 1 million 4-byte integers for the data. Fetching a single value out of it required more CPU for navigation than the retrieval of the actual data.

Doing a INNER JOIN of 1M rows from part1 to part2 where part2 has absolutely every key requires more resources for navigation (temp table creation, key comparison, value population). Denormalizing can sometimes be demorializing if the right side of a LEFT JOIN isn't very sparse or the left side of the LEFT JOIN is huge. In your case, separating the original into part1 and part2 buys you nothing if they have to be frequently referenced together and in bulk. In other words, separating columns that do not form repeating groups isn't true normalization.

The 3 options I gave would do a world of good for part1 and fetching part2 on a row by row basis.

Think about the following cases:

you only need some of the rows from part1

do you trust the MySQL Query Optimizer
do you refactor the query to get a cross section to part1 before joining to part2 ?
do you retrieve from part2 only one row at a time ?

Using a LEFT JOIN and learning there is no part2 for a given part1

set default value in the app for part2 ?
populate part2 for that part1 ?

Giving more consideration to how you plan to retrieve data, how much data you need in a single query, and how you structure the query will become the determining factor as your seek good performance.

EPILOGUE

You are not going to get better performance because of the cost to make the JOIN happen. It's like trying to turn lead into gold (Theoretically possible, practically and financially impossible).

Your best bet is to leave the table in its original state.


update stored proc ony fields that are passed to the proc and leaving others alone

Something like below if I understand you correctly.

UPDATE MessageDetail
        SET
            IsDraft = ISNULL (@IsDraft, IsDraft),
            IsPreviewed = ISNULL (@IsPreviewed,  IsPreviewed),
            IsRead = ISNULL (@IsRead, IsRead),
            IsFlagged = ISNULL (@IsFlagged, IsFlagged),
            IsDeleted = ISNULL(@IsDeleted, IsDeleted)
            IsArchived = ISNULL(@IsArchived, IsArchived)
        WHERE MessageId = @MessageId


Because you are grouping by contracts.email, there needs to be an aggregate expression over emails_stats.id, which I've chosen as GROUP_CONCAT:

SELECT
    contacts.email, GROUP_CONCAT(emails_stats.id) FROM contacts
LEFT JOIN
    emails_stats ON contacts.id = emails_stats.contact_id
WHERE
    contacts.client_code = 121212121212
GROUP BY
    contacts.email;

As this lookup is by client_code, to enable this to perform, creating an index on client_code needs to be at the first part of the index. As contacts.email is used by the GROUP BY and SELECT results, putting email at the end of the index will save a secondary lookup of the email, and being in the index, its already sorted and hence grouped together.

CREATE INDEX client_code_email ON contacts(client_code,email)

This will enable the WHERE clause to be fast. In Innodb which is used, the contacts.id is the PK at the end of the key, this is use the JOIN which is ideal.

The JOIN to email_stats is on contract_id which already has an index. email_stats.id is the PK already at the end of this index which gets used in the result set.


With MyISAM, there's a neat trick you can do:

CREATE TABLE IF NOT EXISTS test (
  id INT NOT NULL AUTO_INCREMENT,
  name varchar(20) NOT NULL,
  PRIMARY KEY (name, id)
) ENGINE=MyISAM
  DEFAULT CHARSET=latin1
  AUTO_INCREMENT=1 ;

With the above PRIMARY KEY, if all users have different name, they will be given id = 1. If another user comes and chooses name that already exists, he will get id=2, etc.

UPDATE

It seems I misread your question and you only need to have name = 'U243' for user with id=243. If that's the case, you don't have to store the name at all. Use this (myISAM or InnoDB, it will work for both):

CREATE TABLE IF NOT EXISTS test (
  id INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=MyISAM
  DEFAULT CHARSET=latin1
  AUTO_INCREMENT=1 ;

and then, to retrieve the id and name:

SELECT
    id
  , 'U' || id  AS name
FROM
    test ;


the innodb_log_file_size looks rather small for what I assume is bulk loaded  unit test data with lots of writes.

A decent innodb_buffer_pool_size needs to be set that accounts for the active test size.

Because crash safety isn't a concern, like innodb_doublewrite=0  innodb_flush_log_at_trx_commit=0, will speed up write transactions.

max_connections implies you are doing multithreaded clients and if those are connecting/disconnecting maybe set thread_cache_size to the maximum number typically used.

A SHOW GLOBAL STATUS after a test run might give a hint as to what other aspects could be tuned.


SQL Server FCI online with incorrect file path

Yes, it could happen if you add a new disk to the cluster. The new disk could use that same drive letter and force the another cluster disk to use the new letter (which happens to be the disk that holds the system databases). Since the dependency remains the same, changing the disk letter doesn't affect the SQL resources. As a result, SQL services would still be up but the startup parameters would still pointing to the old drive.


Postgresql assign mutliple row query result to variable and continue execution

The usual approach is to return a rowset using returns table.  The client calling the function can then print it.  The function itself continues to run after the return.  For example:

create or replace function script()
RETURNS TABLE (col1 int)
language plpgsql
as $_$ begin
RETURN QUERY (VALUES (6), (7), (6*7));
RAISE NOTICE 'continuing after return';
end;
$_$;
select * from script();

This prints:

NOTICE:  continuing after return
 col1
------
    6
    7
   42
(3 rows)

You can also store the result of the function in a (temporary) table:

create or replace function script2()
returns table (col1 int)
language plpgsql
as $_$
begin
CREATE TEMPORARY TABLE temp_table (id int) ON COMMIT DROP;
INSERT INTO temp_table SELECT * FROM script();
return query (select * from temp_table);
end;
$_$;
select * from script2();

This prints:

NOTICE:  continuing after return
CONTEXT:  SQL statement "insert into temp_table select * from script() s where s.col1 < 42"
PL/pgSQL function script2() line 4 at SQL statement
 col1
------
    6
    7
(2 rows)


Would using timestamps solve consistency issues with snapshot isolation?

I would suggest turning on READ_COMMITTED_SNAPSHOT at the database level - that will get rid of a large amount of the concurrency issues you are seeing with the Access front end.

Having TIMESTAMP fields in the tables that you are having issues with will help Access, but not with the ASYNC_NETWORK_IO waits.  Those waits are showing up from the various combo boxes and list boxes you have in the front-end.  Access does not request the entire dataset for those items until the combo-box or list-box etc actually needs all the data.

TIMESTAMP fields will be used by Access to determine if a record has been updated by some other process between the time Access fetched the record, and the time it (or the user) attempted to update the record.  If there is no TIMESTAMP field, Access must inspect all the fields in the dataset prior to running the update statement to see if anything has changed.


Setup [master] - [master-slave] - [slave] replication

First of all, I dislike your setup. When Server B fails, the Server C will not be in replication anymore. If you want to be safe, that the data is on every server in sync, you should try to use a cluster. A cluster is minimum 3 servers and would fit your environment.

Next question for me is, why don't you use just one master (Server A) and two slaves (B+C)?

To answer your question, you need to activate the relay log, so that all changes to the slave are stored for the other slaves:

# REPLICATION slave to other slaves #
log-slave-updates              = 1
relay-log                      = /var/lib/mysql/relay-bin
slave-net-timeout              = 60


MySQL TIMESTAMP isn't behaving as advertised

Your mysql server's global configuration don't allow you to insert ZEROS into table column.
The global configuration parameter SQL_mode is responsible for this behavior. You can find it in your my.ini file if configured or you can also query mysql server to display its value,

select @@SQL_mode;

It should return you 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE', which needs to be reset by sql command set global SQL_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; or as per your requirement.


how to find which user performed DML operations in a table in oracle?

The answer is auditing.

Monitoring Database Activity with Auditing

Traditional auditing:

audit all on owner.mytable by access;

Unified auditing:

create audit policy mypolicy actions all on owner.mytable;
audit policy mypolicy;

If audit_trail is set to DB or DB,EXTENDED, results can be queried from DBA_AUDIT_TRAIL in case of traditional auditing.
With Unified auditing, results can be queried from UNIFIED_AUDIT_TRAIL.


How to pass table name and column name dynamically in Cursor

If I were you, I'd put the values of the tables being deleted into an array, and then loop through that to do a single delete on that table. E.g.:

DECLARE
  TYPE tab_col_rec IS RECORD (owner all_tables.owner%TYPE,
                              table_name all_tables.table_name%TYPE,
                              column_name all_tab_columns.column_name%TYPE,
                              predicate_value VARCHAR2(4000));

  TYPE tab_col_arr IS TABLE OF tab_col_rec INDEX BY PLS_INTEGER;

  v_arr tab_col_arr;
  v_sql CLOB;

  v_idx PLS_INTEGER;
BEGIN
  v_arr(1).owner := 'owner1';
  v_arr(1).table_name := 'region';
  v_arr(1).column_name := 'code';
  v_arr(1).predicate_value := 'XY';

  v_idx := v_arr.first;

  WHILE v_idx IS NOT NULL
  LOOP
    v_sql := 'DELETE FROM '|| dbms_assert.qualified_sql_name(v_arr(v_idx).owner || '.' || v_arr(v_idx).table_name) || CHR(10) ||
             'WHERE  ' || dbms_assert.simple_sql_name(v_arr(v_idx).column_name) || ' = :predicate_value';

    --dbms_output.put_line(v_sql);  -- useful for checking the sql to be run

    EXECUTE IMMEDIATE v_sql USING v_arr(v_idx).predicate_value;

    COMMIT;

    v_idx := v_arr.next(v_idx);
  END LOOP;
END;
/

I've put what I thought was relevant into the array record type, but you may need more or fewer fields as you see fit.

I've also used a single DELETE statement, since that will be faster than committing every N rows. If you don't have enough UNDO tablespace to do a single delete, I recommend you get it extended, rather than having to commit periodically.


sp_refresh_parameter_encryption fails to refresh objects

Generally, you need to check each object this system routine do not refresh.

It is a good start point to locate routines that are referring the new encrypted columns in not correct way - for example, performing scalar functions on the columns (cast/convert, aggregate, string functions or concatenation, etc.).

If you do not do this, you risk your application to fail when the encryption is applied on the production database.

It also seems, the engine is not capable of checking/refreshing the CLR routines. In my case, I was completely sure that my CLR objects do not work with encrypted data and just ignored them.


Error Updating SSIS package to 2014 due to script component

VS 2010/VS 2012 => SQL Server 2012.

VS 2013 => SQL Server 2014.

VS 2015 => SQL Server 2016 (for now, soon they'll have the ability to target SQL Server 2012+)

So, you either need to redo your imports using SSDT for VS 2013/SQL Server 2014 or wait until they deliver the next release candidate for SQL Server which will have the ability to target specific releases of SSIS


Really, the only things that will cause your tempdb files to shrink are:

Someone used the 'SHRINKFILE` command to manually resize the files
The SQL Service was restarted (due to a reboot, or the service being restarted manually, etc) and the file reverted to it's configured "default size" (which can be found in sys.master_files - note that it's different from current / initial size reported in sys.database_files)

You can audit your system (using the default trace or the file growth reports in SSMS) to see when and why file shrink events happened.  You can find a history of reboots in the system_health XE session.

A side note about this:

  we shrinked and adjusted the file to 120 GB each thus allocating 960 GB to temp drive from that 1.6 TB and disabling autogrowth

Unless you are leaving room for tempdb's log file, you should really size the tempdb files to fill this dedicated drive.  Since you have autogrowth turned off, it's simply wasted space at this point =)


Setting up users and admins with schema privileges

The part that should interest you is the grants. But I figured a "code/picture" is worth a thousand words.

use master
go
create database test1
go
use test1
go
create schema data
go
create schema ref
go
create table data.tbl1(a int);insert data.tbl1 values(1);
go
create table ref.tbl2(a int);insert ref.tbl2 values(2);
go

create proc data.tbl1_select as select * from tbl1
go
create proc data.tbl1_insert(@a int) as insert tbl1 values(@a)
go
create proc data.tbl1_update(@a int) as update tbl1 set a = @a
go
create proc data.tbl1_delete as delete from tbl1
go

create proc ref.tbl2_select as select * from tbl2
go
create proc ref.tbl2_insert(@a int) as insert tbl2 values(@a)
go
create proc ref.tbl2_update(@a int) as update tbl2 set a = @a
go
create proc ref.tbl2_delete as delete from tbl2
go

create role users
go
create role admins
go
grant execute on schema::data to admins
grant execute on schema::ref to admins
grant execute on schema::data to users
grant execute on ref.tbl2_select to users
go

use master
go
create login user1 with password='', default_database=test1, check_expiration=off, check_policy=off
create login user2 with password='', default_database=test1, check_expiration=off, check_policy=off
go
use test1
go
create user user1 for login user1;
create user user2 for login user2;
alter role admins add member user1;
alter role users add member user2;
go

setuser 'user1'
go
exec ref.tbl2_update 3
exec ref.tbl2_select
exec data.tbl1_update 3
exec data.tbl1_select
go
setuser -- To go back to your user
go
setuser 'user2'
go
exec ref.tbl2_update 3
exec ref.tbl2_select
exec data.tbl1_update 3
exec data.tbl1_select
select * from ref.tbl2

Here's the result:

The EXECUTE permission was denied on the object 'tbl2_update', database 'test1', schema 'ref'.
The SELECT permission was denied on the object 'tbl2', database 'test1', schema 'ref'.


The field Sortlen indicates the bytes needed when collating data:

So the collation is used to sort and compare the strings presented, but as the complexity of the rules for a language increases, you need space to sort the string or compare, because you have to store some information on the way.
Rick James is right you can see them,. because they are used on inside, when sorting or comparing and the number of bytes for sorting are set when the rules are made for a specific.


Invalid object when selecting from temporary table

An option which I've used before is to create the temp table using some known fields that will always be the same, then alter the temp table using dynamic SQL to add in the additional columns that you only need in specific cases.  This way the temp table is created within scope, then altered via dynamic SQL.

CREATE TABLE #MyTable (ID int identity(1,1) primary key)

if @Something = 1 then
begin
     set @sql = 'alter table #MyTable add column ...'
     exec (@sql)
end
...
Your dynamic select statement goes down here somewhere.


How to display the query result separately for each item in an IN clause?

That's easy:

Select ACCT, SUM(BALANCE)
from dbo.account_balances
where ACCT IN ( 1000, 1001, 1002...)
GROUP BY ACCT


Columnstore index analysis does not point me to correct columns

The column_id field that is part of the columnstore system catalog view: sys.column_store_segments

column_id int ID of the columnstore column.

Source
Is different from the column_id field that is part of the system catalog view: sys.columns

column_id int ID of the column. Is unique within the object.

Source
If you are ever wondered about what SSMS is running in the background you can always set up an extended event to capture the queries when you select the properties of the index.
The query that SSMS uses that shows the properties of the index and the columns in correct order:
exec sp_executesql N'SELECT
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
clmns.name AS [Name],
ic.is_included_column AS [IsIncluded],
ic.is_descending_key AS [Descending],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed],
CAST(ISNULL(ic.column_store_order_ordinal,0) AS int) AS [ColumnStoreOrderOrdinal]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(i.name=@_msparam_2)and((tbl.name=@_msparam_3 and SCHEMA_NAME(tbl.schema_id)=@_msparam_4))
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'IX_Column_bla',@_msparam_3=N'ColumnstoreTest',@_msparam_4=N'dbo'

Here we see that an intermediate system catalog view, sys.index_columns is used.
Knowing that, your final adapted query could be something like this:
SELECT
    tbl.name AS table_name,
    i.name AS index_name,
    clmns.name AS column_name,
    p.partition_number,
    css.segment_id,
    css.min_data_id,
    css.max_data_id,
    css.row_count
FROM sys.column_store_segments AS css
INNER JOIN sys.partitions AS p
ON css.hobt_id = p.hobt_id
INNER JOIN
sys.tables as tbl ON p.object_id = tbl.object_id
INNER JOIN sys.indexes AS i ON (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON  ic.object_id=i.object_id
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE tbl.name = 'TableName'
AND i.name= 'IndexName'
ORDER BY tbl.name, clmns.name,
css.segment_id;


PostgreSQL pg_dump -E encoding option not working

Forcing a BOM and UTF-16 encoding for redirected output can be done somehow by Windows shells (personally I've experienced this with Powershell and Windows 10).
See e.g. Resolve UTF-8, UTF-16, ASCII inconsistencies for some context.
In the case of pg_dump, using the -f option to specifiy the output file instead of a shell redirection seems like a good idea against that sort of problem. From the manual:

-f file
--file=file
Send output to the specified file. This parameter can be omitted for file based output formats, in which case the standard output is
used.


How to maintain order when modeling outline in graph database?

This is a challenge I have faced multiple times here recently. There are a few ways that I have solved ordering which seems dependent upon the implementation of the graph dbms. Personally, I use Dgraph's Slash GraphQL service. There are some challenges with working with the GraphQL standardized endpoint vs. some database features that are only still available in DQL (Dgraph Query Language extending GraphQL).
Having that said, here are a few ways that I have found to solve such cases:
With a next and previous edge.
This is covered more in detail by MichelDiz on Dgraph's Community post.
DQL has added a @recurse and @normalize directive that helps make the results possible to flatten a deep nest of nest edges. Adding a new one in the hierarchy involves creating a new node with 2 edges to the next and previous nodes and then updating the next edge on the previous node and updating the previous edge on the next node. The query to get a series of edges would look like:
{
  var(func: eq(title, "Series ABC" )){
    G as articles @filter(not has(<~next>))
  }
  q(func: uid(G)) @recurse  {
    uid : uid
    title : title
    next @normalize
  }
}

While this is a brilliant answer, it only works from the DQL endpoint or with a custom query and mutations added to the GraphQL endpoint.
With a parent node holding an array of the order.
At the time of writing this, Slash GraphQL list type [...] functions as a set and not like a JSON array. With this in mind, it does not store duplicates nor preserves order. However, GraphQL can support JSON inside of a string type. (Dgraph has a current feature request to support JSON as a new type as well.) With this fact and the ease of JSON manipulation, we can create a structure to store an order of a list of articles.
type Paper {
  id: ID
  articles: [Article]
  title: String! @search(by: [term])
  order: String
}
type Article {
  id: ID
  content: String! @search(by: [fulltext])
  isDone: Boolean @search
}

With this schema and the auto generated queries and mutations, you would be could add a new article and then update the parent paper's order predicate using your favorite client side JSON manipulation language such as javascript to splice the array of ids inserting your new id as needed.
You can also then make a request to get this order predicate and paginate it on the client side splicing it into chunks and then provide those chunks to the queryArticle query to paginate through your page as needed.


mysql dump a list of query to file and restore to another database

I think if you're going to use a WHERE clause, you need to specify your table in the mysqldump command. You also just need to include the WHERE clause and not the corresponding SELECT - as per follows:

mysqldump -t -u dk2303 -p my_database journal_details  --where="journal_id = 28679"

journal_details is your table.
--h 127.0.0.1 is the default anyway so not necessary
--default-character-set=utf8 (keep this - may be default also?)
--result-file=/home/dk/dump.sql (you can keep this also, otherwise just use the > redirect operator)

See here for more details.


Can I use dbms_redefinition with bulk data transformations for better results?

John,
normally it does not make sense to use dbms_redefinition in your case since you are not redefining a table. You are creating an extra schema. dbms_redefinition is meant for situations where for example your table T1 gets an other layout and this has to be done online. Application can stil use the old form of the table while you are completing the new table. This is done by creating materialized view logs.

I hope this helps.


RESOURCE_SEMAPHORE_QUERY_COMPILER waits in Microsoft SQL Server 2008 Enterprise

Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits?

No. At least not directly, and even indirectly is not fragmentation but could be outdated stats at play (which rebuilding index also fixes). Read Plan Caching and Recompilation in SQL Server 2012, even though is 2012 a lot of it applies to 2008 still. The problem is that your plans are not reused and you suffer from high re-compilations. Read the white-paper, it has tonnes of resources and explains a lot.


Do the type-specific WKT- constructors function differently from ST_GeomFromText?

However cute and undocumented, all these functions map internally to Create_func_geometry_from_text in item_create.cc,. For this reason, I would advise using ST_GeomFromText() which best reflects that.

GEOMCOLLFROMTEXT
GEOMETRYCOLLECTIONFROMTEXT
GEOMETRYFROMTEXT
GEOMFROMTEXT
LINEFROMTEXT
LINESTRINGFROMTEXT
MLINEFROMTEXT
MPOINTFROMTEXT
MPOLYFROMTEXT
MULTILINESTRINGFROMTEXT
MULTIPOINTFROMTEXT
MULTIPOLYGONFROMTEXT
POINTFROMTEXT
POLYFROMTEXT
POLYGONFROMTEXT
ST_GEOMCOLLFROMTEXT
ST_GEOMETRYCOLLECTIONFROMTEXT
ST_GEOMETRYFROMTEXT
ST_GEOMFROMTEXT
ST_LINEFROMTEXT
ST_LINESTRINGFROMTEXT
ST_MLINEFROMTEXT
ST_MPOINTFROMTEXT
ST_MPOLYFROMTEXT
ST_MULTILINESTRINGFROMTEXT
ST_MULTIPOINTFROMTEXT
ST_MULTIPOLYGONFROMTEXT
ST_POINTFROMTEXT
ST_POLYFROMTEXT
ST_POLYGONFROMTEXT


CAL is a legal CONSTRUCT. I say construct because you may not even get a dedicated document, depending on what papers you sign and agree on with Microsoft.
CAL's are not technically enforced. That said, if you ever get audited you will realize that you are still - possibly criminally - liable. It is legal construct and legal protection to software use applies, whether technically enforced or not.


MSSQL - How to support aggregate pushdown on indexed view with SUM(decimal)

If you can change the query itself to use the view + NOEXPAND, this can work:

Table definition and test data

CREATE TABLE [dbo].[myTable](
    [ItemId] [int] PRIMARY KEY NOT NULL,
    [BusinessDate] [date] NULL,
    [Gross] [decimal](18, 6) NULL
    );

INSERT INTO
[dbo].[myTable] WITH(TABLOCK)
(BusinessDate,Itemid,Gross)
SELECT
TOP(5000000) CAST(getdate() AS DATE),
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
11.2142
FROM master..spt_values
CROSS APPLY master..spt_values spt2;

View definition + indexes

CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS
        SELECT
            BusinessDate,
            ItemId
            ,COUNT_BIG(*) AS NumberOfRows
            ,CAST(SUM(Gross) as [decimal](18, 6))  AS Gross
        FROM [dbo].[myTable]
        GROUP BY BusinessDate, ItemId
GO

CREATE UNIQUE CLUSTERED INDEX CX_ItemId
ON dbo.[SalesAggregated](ItemId);

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesAggregated] ON [dbo].[SalesAggregated]
(
[Gross],
BusinessDate

);

Query

SELECT SUM(Gross)
FROM [dbo].[SalesAggregated] WITH(NOEXPAND)
WHERE BusinessDate = '2019-08-14';

Result

Properties of the scan with the locally aggreggated rows & batch mode:

Querying the view + WITH(NOEXPAND) is necessary to make this example work.

There are many other reasons why you should use WITH(NOEXPAND). More reasons in this blogpost by Paul White


Different collation between System and User databases in SQL Server

This question is definitely a duplicate of SQL Server default collation vs database with different collation - potential problems?

However, as that question was purely generic and this question specifies the two collations, I can at least clarify one of the issues raised in that other question:

  do you see any potential data loss?

In this case there is no potential data loss due to the only difference being a sensitivity (accent sensitivity), which affects sorting and comparisons, but not which character set is being used to store VARCHAR data.

Though, depending on how one defines "data loss", it should be pointed out (at least for the sake of thoroughness) that there is a possibility that a temp table is being used that contains a string column that is not being given an explicit collation when the temp table is created (either Danish_Norwegian_CI_AI or DATABASE_DEFAULT, there is no appreciable difference between them here), and is thus using Danish_Norwegian_CI_AS. That could lead to a query classifying two values as being different if they only differ in accents (well, and yes, also case, width, and kana-type), whereas using Danish_Norwegian_CI_AI would classify them as being equal. This can return unexpected results, or cause an error. But, neither case is a character conversion, which is what I was initially addressing in terms of "data loss".


GRANTing ALL permissions for public to the database is mostly redundant (as public has connect, temporary by default, so you'd only be adding CREATE which you probably don't want to do). You probably expected a GRANT ALL on the database to result in a recursive GRANT ALL to contained schemas and tables. GRANT is not recursive, so this doesn't happen; a GRANT ALL on a database just grants CONNECT and TEMPORARY rights to the database, with no effect on contained schemas and tables.

The default GRANTs are, from the docs on GRANT:

  PostgreSQL grants default privileges on some types of objects to
  PUBLIC. No privileges are granted to PUBLIC by default on tables,
  columns, schemas or tablespaces. For other types, the default
  privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP
  TABLE for databases; EXECUTE privilege for functions; and USAGE
  privilege for languages. The object owner can, of course, REVOKE both
  default and expressly granted privileges. (For maximum security, issue
  the REVOKE in the same transaction that creates the object; then there
  is no window in which another user can use the object.) Also, these
  initial default privilege settings can be changed using the ALTER
  DEFAULT PRIVILEGES command.

So you can see you don't need to GRANT anything on the database unless you want the user to be able to create schemas, etc. You need to:

GRANT USAGE ON SCHEMA myschema TO theuser; for any schema other than public. CREATE can be granted if you want the user to be able to make tables, views, etc.
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE sometable TO theuser; for tables. I've omitted the TRUNATE, REFERENCES and TRIGGER rights as you probably don't want to grant them.
GRANT USAGE ON SEQUENCE sometable_somecolumn_seq TO theuser; for any sequences that are used in table defaults, either explicitly or via a SERIAL or BIGSERIAL column.

... etc. See the manual for GRANT linked above for full definitions of what the privileges do, which are available on which objects, etc. Take note of the wildcard ALL TABLES and ALL SEQUENCES options.

If this seems like too much hassle to do for each table, view, schema, sequence, etc, you can in Pg 9.1 and above use ALTER DEFAULT PRIVILEGES to change the default GRANTs on new objects.

The manual for GRANT
The manual for ALTER DEFAULT PRIVILEGES


Alternative to RDBMS: Modeling Exam/Testing data

Using an RDBMS is fine, but the sticky part is how to store the parameters needed to render each type of question.

I would caution against having separate table definitions (or lots of type-specific columns) for each type of question. While these details (such as the list choices for a multiple-choice question) can be seen through a relational lens, it would, IMHO, be overkill to try to do so.

Instead, I would suggest having a simpler Question table design for that is agnostic to the type of question (other than a field indicating the type, of course), and store the information that is specific to the question type (a list of choices for a multiple-choice question and which is correct, etc.) in an xml field (or a varchar() with JSON, HTML, or some other semi-structured format that meets your needs).

This will allow you to add and refine the supported question types without having to change the table definitions, and CRUD operations for every question type will be essentially the same. So you get the flexibility of a NOSQL approach for the portions of your model that are quite variable without losing the relational structure of the tests and questions overall.


MySQL SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded

If you wan't to check the queries/s in live you can use the mysqladmin tool with some options:

[root@db-prod-01 ~]# mysqladmin extended-status -uroot -p -i 1 -r | grep 'Com_insert '
Enter password:
| Com_insert                            | 59863746      |
| Com_insert                            | 1             |
| Com_insert                            | 2             |
| Com_insert                            | 9             |
| Com_insert                            | 2             |
| Com_insert                            | 2             |
| Com_insert                            | 3             |
| Com_insert                            | 2             |
| Com_insert                            | 3             |
| Com_insert                            | 6             |

extended-status: return the result of SHOW GLOBAL STATUS;
-i 1 -r:  Execute the SHOW GLOBAL STATUS; every second (one line by second)
| grep 'Com_insert': Shows only INSERTS statements

Note that Com_insert is a cumulativ counter the first line shows the nominal counter value and at each iteration of mysqladmin, a substraction is made to show you how there were insertions during the time you setted (here -i 1 is each 1 second).

By the way, if you want some infos for your error (1205) it's the InnoDB Engine that should check because it's the only way to see which of your query (or queries) is/are stucking your DB.

You can find infos on current and past transactions that lock your DB with:

SHOW ENGINE INNODB STATUS; -- In a MySQL prompt

Max.


Found a solution. As I looked more into my first DB it had UTF-8 encoding, but all fields in tables was latin1. And data in there was windows-1257. So the solution:

SELECT CONVERT(CAST(pavad as BINARY) USING cp1257) pg_zodynas