Is it possible to do flashback after truncate?

Sadly no. At least not by default and not directly. For this you need to read about Flashback Data Archives . By default they are disabled for all tables, but it can be activated, and will preserve your data even after a truncate. Moreover, even if you have the FDAs, you cannot just flashback the table, because TRUNCATE is a DDL operation, and will throw you an error that the table definition has been changed.

What you can do though, is to select the data to a point from the flashback table, and insert it back in yours:

insert into table1
select * from table1 as of timestamp to_timestamp(<your_time>)

Note! This can be done only after you have flashback data archives for that table.

Hope this helped!


Query single column, all rows in Postgres 2D Array

You can use the functions array_upper() and array_lower() to identify array dimensions dynamically:

select array_column[1:array_upper(array_column, 1)][2:2] from array_test;

While being at it, if arrays can have non-standard dimenstions, it would be safer to use

SELECT array_column[array_lower(array_column, 1):array_upper(array_column, 1)][2:2]
FROM   array_test;

One subtle difference: While your code returns an empty array for an empty array in the base table, my alternatives returns NULL since array_lower() and array_upper() return NULL. (For NULL in the base table, all return NULL.

I suggest to use a more revealing test case, so you don't miss corner cases:

INSERT INTO array_test (name, array_column) VALUES
  ('rowa', ARRAY[[1,2,3],[4,5,6]])
, ('rowb', ARRAY[[4,5,6],[7,8,9],[10,11,12]])
, ('rowc', ARRAY[[1,2,3]])
, ('rowd', ARRAY[[1],[2],[3]])
, ('rowe', '{}')
, ('rowf', NULL);

There is another "hack" to do exactly the same as your original, but never cut off elements. Use the maximum possible array bounds (which are integer numbers):

SELECT array_column[-2147483648:2147483647][2:2] FROM array_test;

Inspired by Daniel's answer on my related question:

Normalize array subscripts for 1-dimensional array so they start with 1


Oracle ROWID, can we assume it is sequential in a insert-only scenario?

You have essentially no guarantees about the sequence of ROWIDs.

From the ROWID Pseudocolumn docs:

  If you delete a row, then Oracle may reassign its rowid to a new row inserted later.

So the delete scenario has a potential for not being sequential. The ROWID encodes a relative file number and block number. There is no guarantee that these will "increase" when a new extent is allocated to your table, so you might see a jump back if you get allocated to a different file or a block somewhere nearer the beginning of a file (e.g. if something else freed up blocks).

Also note that anything like a move (on a table or partition) will change ROWIDs (since rows are very likely to change blocks at least). Row migration between partitions on update will change ROWIDs too. (ROWIDs probably aren't stable in IOTs either because of block splits. Not entirely certain how that is handled though.)

For your last question, from the same page in the Oracle docs:

  Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.


Is there a way to find my.cnf in shared hosting?

In a shared hosting environment, you cannot access my.cnf

You can see the variables see other ways:

SHOW VARIABLES

SHOW VARIABLES; will show all variables that are set. Some can be dynamically changed within you session with the SET command. Some can be set in the server so that new DB Connections going forward can use the newly set value.

SHOW VARIABLES LIKE 'innodb%' shows all innodb settings
SHOW VARIABLES LIKE '%timeout' shows all timeout setting
SHOW VARIABLES LIKE '%buffer%' shows all buffers

INFORMATION_SCHEMA

You can also access the variables by means of the INFORMATION_SCHEMA database. The tables to access those values are

INFORMATION_SCHEMA.GLOBAL_VARIABLES
INFORMATION_SCHEMA.SESSION_VARIABLES

The same requests using these tables are

SHOW VARIABLES; becomes SELECT * FROM information_schema.global_variables;
SHOW VARIABLES LIKE 'innodb%' becomes SELECT * FROM information_schema .global_variables WHERE VARIABLE_NAME LIKE 'innodb%';
SHOW VARIABLES LIKE '%timeout' becomes SELECT * FROM information_schema .global_variables WHERE VARIABLE_NAME LIKE '%timeout';
SHOW VARIABLES LIKE '%buffer%' becomes SELECT * FROM information_schema .global_variables WHERE VARIABLE_NAME LIKE '%buffer%';

The reason INFORMATION_SCHEMA.SESSION_VARIABLES exists is to see the setting your session has, in the event you change a variable during the session to something different form the global setting.

Examples

SET GLOBAL max_connections = 1000; will now allow 1000 DB Connections
SET sort_buffer_size = 1024 * 1024 * 8; sets your session sort_buffer_size to 8M
SET GLOBAL sort_buffer_size = 1024 * 1024 * 8; sets session sort_buffer_size to 8M for all incoming DB Connections going forward

CAVEAT

Please keep in mind that not all settings can be dynamically changed. Some values, such as sort_buffer_size (session or global) and max_connections (global only [session is N/A]), such as innodb_buffer_pool_size and lower_case_table_names, can only be changed by setting it in my.cnf and restarting mysqld.

Click here for the List of Variables, their defaults, and their session chacateristics.


Every PostgreSQL row version (“tuple”) has a number of system columns and other data that are not displayed by SELECT * (see the documentation for details. Among them are

xmin and xmax
extra flags that contain the “hint bits” and the “frozen” flag.
ctid, the tuple's physical address.

These would account 23 bytes, not for four, so you'd have to look at tge source for more. The only of these items that occupies 6 bytes is ctid, so maybe that.


Why is this explicit cast causing problems only with a Linked Server?

So, I was able to reproduce the error after realizing that the CAST was being done locally, not on the remote instance. I had previously recommended moving up to SP3 in the hopes of fixing this (partially due to not being able to reproduce the error on SP3, and partially due to it being a good idea regardless). However, now that I can reproduce the error, it is clear that moving up to SP3, while still probably a good idea, is not going to fix this. And I also reproduced the error in SQL Server 2008 R2 RTM and 2014 SP1 (using a "loop-back" local Linked Server in all three cases).

It seems that this problem has to do with where the query is executing, or at least where part(s) of it are executing. I say this because I was able to get the CAST operation to work, but only by including a reference to a local DB object:

SELECT rmt.*, CAST(NULL AS UNIQUEIDENTIFIER) AS [GUID]
FROM [Local].[database_name].[dbo].[table_name] rmt
CROSS JOIN (SELECT TOP (1) 1 FROM [sys].[data_spaces]) tmp(dummy);

That actually works. But the following gets the original error:

SELECT rmt.*, CAST(NULL AS UNIQUEIDENTIFIER) AS [GUID]
FROM [Local].[database_name].[dbo].[table_name] rmt
CROSS JOIN (VALUES (1)) tmp(dummy);

I am guessing that when there are no local references, the entire query is shipped off to the remote system to be executed, and for some reason NULLs cannot be converted to UNIQUEIDENTIFIER, or perhaps the NULL is getting translated by the OLE DB driver incorrectly.

Based on the testing that I have done, this would appear to be a bug, but I'm not sure if the bug is within SQL Server or the SQL Server Native Client / OLEDB driver. However, the conversion error occurs within the OLEDB driver, and so is not necessarily an issue of converting from INT to UNIQUEIDENTIFIER (a conversion which is not allowed in SQL Server) since the driver is not using SQL Server to do conversions (SQL Server also does not allow for converting INT to DATE, yet the OLEDB driver handles that successfully, as shown in one of the tests).

I ran three tests. For the two that succeeded, I looked at the XML execution plans which show the query that is being executed remotely. For all three, I captured any Exceptions or OLEDB events via SQL Profiler:

Events:

Errors and Warnings

Attention
Exception
Execution Warnings
User Error Message

OLEDB

all

TSQL

all except:

SQL:StmtRecompile
XQuery Static Type

Column Filters:

ApplicationName

NOT LIKE %Intellisense%

SPID

Greater than or equal 50

THE TESTS

Test 1

CAST(NULL AS UNIQUEIDENTIFIER) that works

SELECT TOP (2) CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
             , (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
FROM [Local].[TEMPTEST].[sys].[objects] rmt;

Relevant portion of the XML execution plan:

          <DefinedValue>
            <ColumnReference Column="Expr1002" />
            <ScalarOperator ScalarString="NULL">
              <Const ConstValue="NULL" />
            </ScalarOperator>
          </DefinedValue>
  ...
<RemoteQuery RemoteSource="Local" RemoteQuery=
 "SELECT 1 FROM &quot;TEMPTEST&quot;.&quot;sys&quot;.&quot;objects&quot; &quot;Tbl1001&quot;"
 />

Test 2

CAST(NULL AS UNIQUEIDENTIFIER) that fails

SELECT TOP (2) CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
         --  , (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
FROM [Local].[TEMPTEST].[sys].[objects] rmt;

(note: I kept the subquery in there, commented out, so that it would be one less difference when I compared the XML trace files)
Test 3

CAST(NULL AS DATE) that works

SELECT TOP (2) CAST(NULL AS DATE) AS [Something]
         --  , (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
FROM [Local].[TEMPTEST].[sys].[objects] rmt;

(note: I kept the subquery in there, commented out, so that it would be one less difference when I compared the XML trace files)

Relevant portion of the XML execution plan:

          <DefinedValue>
            <ColumnReference Column="Expr1002" />
            <ScalarOperator ScalarString="[Expr1002]">
              <Identifier>
                <ColumnReference Column="Expr1002" />
              </Identifier>
            </ScalarOperator>
          </DefinedValue>
 ...
<RemoteQuery RemoteSource="Local" RemoteQuery=
 "SELECT TOP (2) NULL &quot;Expr1002&quot; FROM &quot;TEMPTEST&quot;.&quot;sys&quot;.&quot;objects&quot; &quot;Tbl1001&quot;"
 />

If you look at Test #3, it is doing a SELECT TOP (2) NULL on the "remote" system. The SQL Profiler trace shows that the datatype of this remote field is in fact INT. The trace also shows that the field on the client side (i.e. where I am running the query from) is DATE, as expected. The conversion from INT to DATE, something which will get an error in SQL Server, works just fine within the OLEDB driver. The remote value is NULL, so it is returned directly, hence the <ColumnReference Column="Expr1002" />.

If you look at Test #1, it is doing a SELECT 1 on the "remote" system. The SQL Profiler trace shows that the datatype of this remote field is in fact INT. The trace also shows that the field on the client side (i.e. where I am running the query from) is GUID, as expected. The conversion from INT to GUID (remember, this is done within the driver, and OLEDB calls it "GUID"), something which will get an error in SQL Server, works just fine within the OLEDB driver. The remote value is not NULL, so it is replaced with a literal NULL, hence the <Const ConstValue="NULL" />.

Test #2 fails, so there is no execution plan. However, it does query the "remote" system successfully, but just can't pass back the result set. The query that SQL Profiler captured is:

SELECT TOP (2) NULL "Expr1002" FROM "TEMPTEST"."sys"."objects" "Tbl1001"

That is that exact same query that is being done in Test #1, yet here it is failing. There are other minor differences, but I cannot fully interpret the OLEDB communication. However, the remote field is still showing as INT (wType = 3 = adInteger / four-byte signed integer / DBTYPE_I4) while the "client" field is still showing as GUID (wType = 72 = adGUID / globally unique identifier / DBTYPE_GUID). The OLE DB documentation doesn't help much as GUID Data Type Conversions, DBDATE Data Type Conversions, and I4 Data Type Conversions show that converting from I4 to either GUID or DBDATE is unsupported, yet the DATE query works.

The Trace XML files for the three tests are located on PasteBin. If you want to see the details of where each test differs from the others, you can save them locally and then do a "diff" on them. The files are:

NullGuidSuccess.xml
NullGuidError.xml
NullDateSuccess.xml

ERGO?

What to do about it? Probably just the work-around I noted in the top section, given that the SQL Native Client -- SQLNCLI11 -- is deprecated as of SQL Server 2012. Most of the MSDN pages on the topic of SQL Server Native Client have the following notice at the top:

  Warning

  SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

For more info, please see:

SQL Server Native Client
Installing SQL Server Native Client

ODBC ??

I set up an ODBC Linked Server via:

EXEC master.dbo.sp_addlinkedserver
  @server = N'LocalODBC',
  @srvproduct=N'{my_server_name}',
  @provider=N'MSDASQL',
  @provstr=N'Driver={SQL Server};Server=(local);Trusted_Connection=Yes;';

EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname=N'LocalODBC',
  @useself=N'True',
  @locallogin=NULL,
  @rmtuser=NULL,
  @rmtpassword=NULL;

And then tried:

SELECT CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
FROM [LocalODBC].[tempdb].[sys].[objects] rmt;

and received the following error:

  OLE DB provider "MSDASQL" for linked server "LocalODBC" returned message "Requested conversion is not supported.".
  Msg 7341, Level 16, State 2, Line 53
  Cannot get the current row value of column "(user generated expression).Expr1002" from OLE DB provider "MSDASQL" for linked server "LocalODBC".

P.S.

As it relates to transporting GUIDs between remote and local servers, non-NULL values are handled via a special syntax. I noticed the following OLE DB Event info in the SQL Profiler trace when I ran CAST(0x00 AS UNIQUEIDENTIFIER):

<RemoteQuery RemoteSource="Local" RemoteQuery=
 "SELECT {guid'00000000-0000-0000-0000-000000000000'} &quot;Expr1002&quot; FROM &quot;TEMPTEST&quot;.&quot;sys&quot;.&quot;objects&quot; &quot;Tbl1001&quot;"
 />

P.P.S.

I also tested via OPENQUERY with the following query:

SELECT TOP (2) CAST(NULL AS UNIQUEIDENTIFIER) AS [Something]
     --, (SELECT COUNT(*) FROM sys.[data_spaces]) AS [lcl]
FROM   OPENQUERY([Local], N'SELECT 705 AS [dummy] FROM [TEMPTEST].[sys].[objects];') rmt;

and it succeeded, even without the local object reference. The SQL Profiler trace XML file has been posted to PasteBin at:

NullGuidSuccessOPENQUERY.xml

The XML execution plan shows it using a NULL constant, same as in Test #1.


How to relate LatLong and State columns on PowerBI

Without ever using PowerBI, without even knowing what it is, no. Highly unlikely.

You'll have to pull in Shapefiles that represent the states from the Census or like data-source. Then you'll have to construct points representing the cords and run point-in-polygon on the result.

Check out

Geographic Information Systems GIS.StackExchange


Need help with a table relationships and over-all design

The question to ask yourself is simple: Can a single Transcode record be associated with more than one Movie or Episode?

If Yes, you need a linking table that links Movies and Transcodes and one that links Episodes and Transcodes to create a Many-To-Many relationship.

If No, you simply add transcode_id and a foreign key to the Movies and Episodes tables.

You need to apply that same logic to some of your other tables/relationships. For example, can a Movie really only be associated with a single user as per the current design? What is that relationship - the user who uploaded it, a user who watched it, a user who recommended it?

A few others:

Shouldn't poster_id on Episodes actually be on Seasons? A season may have a different poster, but surely not an individual episode?
Can users have multiple roles? If so, you need a linking table.
Can Movies or Shows have multiple Genres (Action & Thriller, Drama & Comedy for example)? Again, if so, linking table.

Check out this Wikipedia page which covers off some basics on database/ERD design. You need to identify the various entities within your data model (a Movie, for example, is an entity, as is a user) then define the relationship between each entity (Entity A can have only one Entity B associated, and Entity C can have many associated Entity D records etc) and that will help you determine and design your ERD.


Select Column data, from sys.column info

You can use dynamic SQL, a cursor and a global temporary table to collect this information. Note that performance won't be great (cursors are generally a bad idea) but if you only need to run this as a once-off then it should be fine.

CREATE TABLE ##GlobalTemp
(
  TableName VARCHAR(255),
  ColumnName VARCHAR(255),
  Min INT,
  Max INT,
  Avg INT
)

DECLARE @SqlCmd NVARCHAR(MAX),
    @TableName NVARCHAR(255),
    @ColumnName NVARCHAR(255),
    @Type BIT -- 0 = integer, 1 = character

DECLARE C1 CURSOR FOR
    SELECT QUOTENAME(s.[name]) + '.' + QUOTENAME(t.[name]), c.[name],
        CASE
            WHEN ty.[name] IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN 1
            WHEN ty.[name] IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'bit', 'decimal', 'numeric', 'smallmoney', 'bigint') THEN 0
        END
    FROM sys.columns c
    INNER JOIN sys.tables t ON t.object_id = c.object_id
    INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
    INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id

OPEN C1

FETCH NEXT FROM C1 INTO @TableName, @ColumnName, @Type

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @SqlCmd = 'INSERT INTO ##GlobalTemp (TableName, ColumnName, Min, Max, Avg)
    SELECT ''' + @TableName + ''', ''' + @ColumnName + ''', ' +
        CASE @Type
            WHEN 0 THEN 'MIN(' + @ColumnName + '), MAX(' + @ColumnName + '), AVG(' + @ColumnName + ')'
            WHEN 0 THEN 'MIN(LEN(' + @ColumnName + ')), MAX(LEN(' + @ColumnName + ')), AVG(LEN(' + @ColumnName + '))'
        END + '
    FROM ' + @TableName + ';'

    PRINT @SqlCmd
    EXEC sp_executesql @SqlCmd

    FETCH NEXT FROM C1 INTO @TableName, @ColumnName, @Type
END

CLOSE C1
DEALLOCATE C1

SELECT *
FROM ##GlobalTemp

DROP TABLE ##GlobalTemp


failed with the following error: "Write on "....bak" failed: 27(error not found) BACKUP DATABASE is terminating abnormally

Standard options for me to check would be the following, but this has been done by you or does not match exactly because the backup at least starts:

Disk space sufficient? (If possible do a chkdsk)
Write Access granted to SQL Server?

Next step would be to check throughput of backup drive because SQL Server backup is sensible in this area, some timers in the background are watching. Backup to a USB-Drive / Stick is usually a very bad idea because of this.

Now for the more exotic stuff:

H:-Drive is NTFS formatted? Otherwise File size >4 GB might be an issue.
As we are dealing with a VM here: Is there some kind of disc overcommitment happening on Hyper-V or SAN level? Just because you see a e.g. 1 TB drive does not guarantee that this is covered by physical disks somewhere.
Any I/O errors in windows system event log? If something really bad is happing on I/O level you can find it there.


Can I stop replication of create/drop index statement on one slave of Mysql 5.6?

I don't believe there is a way to ignore specific DDL (or any DDL?).  If creating an existing index, it should throw an error, which you should be able to ignore on your analytics-slave.

Alternatively, don't replicate Index Creations.

set log_bin=0;
create index name on table;
set log_bin=1;

Create a wrapper script to apply to master and slave as needed.


A way to reference the ID in a multi insert transaction? (postgres)

There are different ways to do this.

The easiest way is to use the lastval() function which will return the value generated by the "last" sequence nextval.

START TRANSACTION;
INSERT INTO entity ...;
INSERT INTO t2 (eid, ...) VALUES (lastval(), ...), (...), (...);
COMMIT;

If you know the name of the sequence for the entity table you could also use the currval function:

START TRANSACTION;
INSERT INTO entity ...;
INSERT INTO t2 (eid, ...) VALUES (currval('entity_eid_seq'), ...), (...), (...);
COMMIT;

This can be written in  a more general way by using the pg_get_serial_sequence() function, avoiding to hardcode the sequence name:

START TRANSACTION;
INSERT INTO entity ...;
INSERT INTO t2 (eid, ...) VALUES (currval(pg_get_serial_sequence('entity', 'eid')), ...), (...);
COMMIT;

For more details, please see the manual: http://www.postgresql.org/docs/current/static/functions-sequence.html


Estimated vs Actual rows differences (actual much smaller than estimated) - sort

There is no statistics generated on XML columns. The estimates is guessed based on the expressions used when querying the XML.

Using this table:

create table T(XMLCol xml not null)
insert into T values('<root><item value = "1" /></root>')

And this rather simple XML query:

select X.N.value('@value', 'int')
from T
  cross apply T.XMLCol.nodes('root/item') as X(N)

Will give you one row returned but the estimated rows returned is 200. It will be 200 regardless of what XML or how much XML you stuff into the XML column for that one row.

This is the query plan with the estimated row count displayed.

A way to improve, or at least change, the estimates is to give the query optimizer some more information about the XML. In this case, because I know that root really is a root node in the XML, I can rewrite the query like this.

select X2.N.value('@value', 'int')
from T
  cross apply T.XMLCol.nodes('root[1]') as X1(N)
  cross apply X1.N.nodes('item') X2(N)

That will give me an estimate of 5 rows returned.

The rewrite of the query will probably not speed up the shredding of the XML but if the estimates are better, chances are that the query optimizer can make smarter decisions for the rest of the query.

I have not found any documentation on what the rules are for the estimates other than a presentation by Michael Rys where he says:

  Base cardinality estimate is always 10’000 rows!
  Some adjustment based on pushed path filters


Here is what I did:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';

In the php.ini:
mysql.default_port = <private instance port>

In the forum settings, set database host to:
127.0.0.1


Difference between "Table-Incremental" and "Table-Update" for "Population Type" when scheduling Full Text Index Population

By default the Full-Text Engine uses automatic population on a full-text index and changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. The full-text index is updated in the background.

For incremental population your table must have a column of the timestamp data type. At the end of a population, the Full-Text Engine records the largest timestamp value and will use that for next incremental population

Table-Update will use change tracking to maintain a full-text index after its initial full population. The SQL Server will then maintain a record of the rows in the base table or indexed view that have been modified by updates, deletes, or insert and will use this information to populate the Full Text Index. This comes with an overhead.

In most cases you are better off using Automatic updates, but if you have a table that's frequently updated and you have a timestamp column a manual Table-Incremental can help. (https://technet.microsoft.com/en-us/library/ms142575%28v=sql.100%29.aspx)


Can STRING_AGG be used multiple times with different delimiters?

This is a bug. This answer indicates that it is fixed in CU17 but I just tested that and found it negative (and there is no indication that the fix was intended to be backported here).

I tried

SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG(Member, '::')
FROM (VALUES (1, 'a'), (1, 'b')
           , (2, 'c'), (2, 'd')) [v] ([Group], Member)
GROUP BY [Group]
OPTION (QUERYTRACEON 3604 -- Output info to client
      , QUERYTRACEON 8607 -- Optimization output tree, before post-optimization rewrite phase.
      , QUERYTRACEON 7352 -- The final query tree after Post-optimization re-write.
      , RECOMPILE);
;

In 2017 CU17. This gives plan

On 2019 there is no compute scalar and the Stream Aggregate has defined values

[Expr1010] = Scalar Operator(STRING_AGG([Union1009],', ')),
[Expr1011] = Scalar Operator(STRING_AGG([Union1009],'::'))

On 2017 the Stream Aggregate has defined value

[Expr1010] = Scalar Operator(STRING_AGG([Union1009],', '))

And there is an additional Compute Scalar with expression

[Expr1011] = Scalar Operator([Expr1010])

The output of trace flag 8607is the same on 2017 and 2019 but 7352 differs so I assume this is a faulty post optimisation rewrite.

As a workaround you can change the expression to do a no-op as below to prevent this faulty optimisation.

SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG('' + Member, '::')


How to find description of error in error log?

Errors labelled [MY-XXXXXX] are MySQL 8.0 errors. You can find the definition for this particular error here https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html#error_er_reading_table_failed.


horizontal partitioning or multiple tables?

I just ran across this old question.  In general, administering 1 table would be easier than administering 10-100 tables.  Imagine trying to ALTER TABLE across 100 tables!

Just because you have millions of rows doesn't mean you'll need multiple servers.  Good queries, good indexes, good disks, etc., are all performance items.  Fewer servers are also easier to maintain.

What did you end up doing?


How to reclaim the storage space used by a heap?

If your table doesn't have a clustered index, then deletes don't deallocate empty pages by default.

Your options are:

ALTER TABLE dbo.MyTable REBUILD - which will take your table offline in Standard Edition, building a new copy of it with everything packed in nicely like sardines
Do your deletes with the TABLOCK hint - which can prove problematic for concurrency, since as it indicates, will take out a table lock to do the deletes
Truncate the table - which will deallocate all of the pages, not just the empty ones, so it has the unfortunate drawback of erasing all your data.
Put a clustered index on it - if you frequently update & delete your data, then you should do as Beyonce says: put a clustered index on it. Otherwise, you end up with the empty-space problem that you're having now, plus the forwarded-fetches problem.


Cursoring over sys.databases skips databases

sys.databases is a complex view. On SQL Server 2016 it is:
CREATE VIEW sys.databases AS
    SELECT d.name,
        d.id AS database_id,
        r.indepid AS source_database_id,
        d.sid AS owner_sid,
        d.crdate AS create_date,
        d.cmptlevel AS compatibility_level,
        -- coll.value = null means that a collation was not specified for the DB and the server default is used instead
        convert(sysname, case when serverproperty('EngineEdition') = 5 AND d.id = 1 then serverproperty('collation')
                                 else CollationPropertyFromID(convert(int, isnull(coll.value, p.cid)), 'name') end) AS collation_name,
        iif ((serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x10000000) = 1), cast (3 as tinyint), p.user_access) AS user_access,
        iif ((serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x10000000) = 1), 'NO_ACCESS', ua.name) AS user_access_desc,
        sysconv(bit, d.status & 0x400) AS is_read_only,         -- DBR_RDONLY
        sysconv(bit, d.status & 1) AS is_auto_close_on,         -- DBR_CLOSE_ON_EXIT
        sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on,     -- DBR_AUTOSHRINK
        case when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000020) = 1) then cast (1 as tinyint) -- RESTORING
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000080) = 1) then cast (7 as tinyint) -- COPYING
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000100) = 1) then cast (4 as tinyint) -- SUSPECT
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x08000000) = 1) then cast (8 as tinyint) -- QUORUM_RECOVERY_PENDING
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x04000000) = 1) then cast (9 as tinyint) -- CREATING
             else p.state
             end AS state, -- 7 is COPYING and 4 is SUSPECT state for database copy (UNDO: Need to have a clean way to set states in dbtable for a user db)
        case when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000020) = 1) then 'RESTORING'
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000080) = 1) then 'COPYING'
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000100) = 1) then 'SUSPECT'
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x08000000) = 1) then CONVERT(nvarchar(60), N'QUORUM_RECOVERY_PENDING')
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x04000000) = 1) then 'CREATING'
             else st.name
             end AS state_desc,
        sysconv(bit, d.status & 0x200000) AS is_in_standby,     -- DBR_STANDBY
        case when serverproperty('EngineEdition') = 5 then convert(bit, 0) else p.is_cleanly_shutdown end AS is_cleanly_shutdown,
        sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled, -- DBR_SUPPLEMENT_LOG
        case when (serverproperty('EngineEdition') = 5) then sysconv(tinyint, sysconv(bit,(d.status & 0x00100000)))
             else p.snapshot_isolation_state end AS snapshot_isolation_state,
        case when (serverproperty('EngineEdition') = 5) and (sysconv(bit, d.status & 0x00100000) = 1) then 'ON'
             when (serverproperty('EngineEdition') = 5) and (sysconv(bit, d.status & 0x00100000) = 0) then 'OFF'
             else si.name end AS snapshot_isolation_state_desc,
        sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on,     -- DBR_READCOMMITTED_SNAPSHOT
        case when (serverproperty('EngineEdition') = 5)
             then case
                    when sysconv(bit,(d.status & 0x00000008)) = 1
                        then cast(3 as tinyint)
                    when sysconv(bit,(d.status & 0x00000004)) = 1
                        then cast(2 as tinyint)
                    else
                        cast(1 as tinyint)
                   end
              else p.recovery_model
        end AS recovery_model,
        case when (serverproperty('EngineEdition') = 5)
             then case
                    when sysconv(bit,(d.status & 0x00000008)) = 1
                        then CONVERT(nvarchar(60), N'SIMPLE')
                    when sysconv(bit,(d.status & 0x00000004)) = 1
                        then CONVERT(nvarchar(60), N'BULK_LOGGED')
                    else
                        CONVERT(nvarchar(60), N'FULL')
                  end
              else ro.name
        end AS recovery_model_desc,
        p.page_verify_option, pv.name AS page_verify_option_desc,
        sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on,         -- DBR_AUTOCRTSTATS
        sysconv(bit, d.status2 & 0x00400000) AS is_auto_create_stats_incremental_on,    -- DBR_AUTOCRTSTATSINC
        sysconv(bit, d.status2 & 0x40000000) AS is_auto_update_stats_on,        -- DBR_AUTOUPDSTATS
        sysconv(bit, d.status2 & 0x80000000) AS is_auto_update_stats_async_on,  -- DBR_AUTOUPDSTATSASYNC
        sysconv(bit, d.status2 & 0x4000) AS is_ansi_null_default_on,            -- DBR_ANSINULLDFLT
        sysconv(bit, d.status2 & 0x4000000) AS is_ansi_nulls_on,                -- DBR_ANSINULLS
        sysconv(bit, d.status2 & 0x2000) AS is_ansi_padding_on,                 -- DBR_ANSIPADDING
        sysconv(bit, d.status2 & 0x10000000) AS is_ansi_warnings_on,            -- DBR_ANSIWARNINGS
        sysconv(bit, d.status2 & 0x1000) AS is_arithabort_on,                   -- DBR_ARITHABORT
        sysconv(bit, d.status2 & 0x10000) AS is_concat_null_yields_null_on,     -- DBR_CATNULL
        sysconv(bit, d.status2 & 0x800) AS is_numeric_roundabort_on,            -- DBR_NUMEABORT
        sysconv(bit, d.status2 & 0x800000) AS is_quoted_identifier_on,          -- DBR_QUOTEDIDENT
        sysconv(bit, d.status2 & 0x20000) AS is_recursive_triggers_on,          -- DBR_RECURTRIG
        sysconv(bit, d.status2 & 0x2000000) AS is_cursor_close_on_commit_on,    -- DBR_CURSCLOSEONCOM
        sysconv(bit, d.status2 & 0x100000) AS is_local_cursor_default,          -- DBR_DEFLOCALCURS
        sysconv(bit, d.status2 & 0x20000000) AS is_fulltext_enabled,            -- DBR_FTENABLED
        sysconv(bit, d.status2 & 0x200) AS is_trustworthy_on,               -- DBR_TRUSTWORTHY
        sysconv(bit, d.status2 & 0x400) AS is_db_chaining_on,               -- DBR_DBCHAINING
        sysconv(bit, d.status2 & 0x08000000) AS is_parameterization_forced, -- DBR_UNIVERSALAUTOPARAM
        sysconv(bit, d.status2 & 64) AS is_master_key_encrypted_by_server,  -- DBR_MASTKEY
        sysconv(bit, d.status2 & 0x00000010) AS is_query_store_on,          -- DBR_QDSENABLED
        sysconv(bit, d.category & 1) AS is_published,
        sysconv(bit, d.category & 2) AS is_subscribed,
        sysconv(bit, d.category & 4) AS is_merge_published,
        sysconv(bit, d.category & 16) AS is_distributor,
        sysconv(bit, d.category & 32) AS is_sync_with_backup,
        d.svcbrkrguid AS service_broker_guid,
        sysconv(bit, case when d.scope = 0 then 1 else 0 end) AS is_broker_enabled,
        p.log_reuse_wait, lr.name AS log_reuse_wait_desc,
        sysconv(bit, d.status2 & 4) AS is_date_correlation_on,      -- DBR_DATECORRELATIONOPT
        sysconv(bit, d.category & 64) AS is_cdc_enabled,
        case
            when (d.id = db_id('tempdb')) then sysconv(bit, p.is_db_encrypted)
            else sysconv(bit, d.status2 & 0x100)                    -- DBR_ENCRYPTION
            end AS is_encrypted,
        convert(bit, d.status2 & 0x8) AS is_honor_broker_priority_on,               -- DBR_HONORBRKPRI
        sgr.guid AS replica_id,
        sgr2.guid AS group_database_id,
        ssr.indepid AS resource_pool_id,
        default_language_lcid = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(smallint, p.default_language) else null end,
        default_language_name = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(sysname, sld.name) else null end,
        default_fulltext_language_lcid = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(int, p.default_fulltext_language) else null end,
        default_fulltext_language_name = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(sysname, slft.name) else null end,
        is_nested_triggers_on = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(bit, p.allow_nested_triggers) else null end,
        is_transform_noise_words_on = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(bit, p.transform_noise_words) else null end,
        two_digit_year_cutoff = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(smallint, p.two_digit_year_cutoff) else null end,
        containment = sysconv(tinyint, (d.status2 & 0x80000)/0x80000), -- DBR_IS_CDB
        containment_desc = convert(nvarchar(60), cdb.name),
        p.recovery_seconds AS target_recovery_time_in_seconds,
        p.delayed_durability,
        case when (p.delayed_durability = 0) then CAST('DISABLED' AS nvarchar(60)) -- LCOPT_DISABLED
             when (p.delayed_durability = 1) then CAST('ALLOWED' AS nvarchar(60)) -- LCOPT_ALLOWED
             when (p.delayed_durability = 2) then CAST('FORCED' AS nvarchar(60)) -- LCOPT_FORCED
             else NULL
             end AS delayed_durability_desc,
        convert(bit, d.status2 & 0x80) AS
        is_memory_optimized_elevate_to_snapshot_on,             -- DBR_HKELEVATETOSNAPSHOT
        sysconv(bit, d.category & 0x100) AS is_federation_member,
        convert(bit, isnull(rda.value, 0)) AS is_remote_data_archive_enabled,
        convert(bit, p.is_mixed_page_allocation_on) AS is_mixed_page_allocation_on
    FROM sys.sysdbreg d OUTER APPLY OpenRowset(TABLE DBPROP, (case when serverproperty('EngineEdition') = 5 then DB_ID() else d.id end)) p
    LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0  -- SRC_VIEWPOINTDB
    LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state
    LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access
    LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state
    LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model
    LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option
    LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait
    LEFT JOIN sys.syssingleobjrefs agdb ON agdb.depid = d.id AND agdb.class = 104 AND agdb.depsubid = 0 -- SRC_AVAILABILITYGROUP
    LEFT JOIN master.sys.syssingleobjrefs ssr ON ssr.class = 108 AND ssr.depid = d.id -- SRC_RG_DB_TO_POOL
    LEFT JOIN master.sys.sysclsobjs  ag ON ag.id = agdb.indepid AND ag.class = 67 -- SOC_AVAILABILITY_GROUP
    LEFT JOIN master.sys.sysguidrefs sgr ON sgr.class = 8 AND sgr.id = ag.id AND sgr.subid = 1 -- GRC_AGGUID / AGGUID_REPLICA_ID
    LEFT JOIN master.sys.sysguidrefs sgr2 ON sgr2.class = 9 AND sgr2.id = ag.id AND sgr2.subid = d.id -- GRC_AGDBGUID
    LEFT JOIN sys.syspalvalues cdb ON cdb.class = 'DCDB' AND cdb.value = CASE WHEN (d.status2 & 0x80000)=0x80000 THEN 1 ELSE 0 END
    LEFT JOIN sys.syslanguages sld ON sld.lcid = p.default_language
    LEFT JOIN sys.fulltext_languages slft ON slft.lcid = p.default_fulltext_language
    LEFT JOIN sys.sysobjvalues coll ON coll.valclass = 102 AND coll.subobjid = 0 AND coll.objid = d.id  -- SVC_DATACOLLATION
    LEFT JOIN sys.sysobjvalues rda ON rda.valclass = 116 AND rda.objid = d.id AND rda.valnum = 0 -- SVC_STRETCH & STRETCH_DB_IS_STRETCHED
    WHERE d.id < 0x7fff
        AND has_access('DB', (case when serverproperty('EngineEdition') = 5 then DB_ID() else d.id end)) = 1

By not specifying specific cursor options, you are implicitly requesting a local, dynamic, updatable, optimistic, forward-only cursor over this view.
SQL Server cannot produce a dynamic cursor plan, so it converts the cursor to a local, keyset, updatable, optimistic, forward-only cursor.
Keyset means the minimal keys needed to locate rows in the underlying system tables are stored in tempdb when the cursor is opened. If any of these key values change in the underlying system tables, a row will not be returned and @@FETCH_STATUS will return -2. Your loop will prematurely exit because @@FETCH_STATUS = 0 fails, and no further databases from the cursor will be processed. An example that could cause a key change is altering the ALLOW_SNAPSHOT_ISOLATION state for a database.
A keyset cursor is unwise when concurrent key changes are possible. We don't control the underlying tables, so using a keyset cursor on sys.databases is asking for trouble. It is also important to check @@FETCH_STATUS for failures, rather than assuming success until the end is reached.
Optimistic cursors use checksums to detect changes to rows since the cursor was opened, so updates are not lost. This is not directly important here, because you are not updating rows through the cursor, but SQL Server doesn't know that. It has to build a plan that collects, stores, and compares checksums.
Side note: Much of the work done in the open and fetch execution plans is redundant because you are only interested in database names and the state_desc column. Most of the redundant system table accesses cannot be removed because the optimizer doesn't have sufficient key relationship information to allow outer join removal. You can work around this by adding DISTINCT name (so the outer joins cannot duplicate rows), but that also forces a cursor downgrade to static (snapshot). Nevertheless, you might be interested to look at the effect on the execution plans of adding DISTINCT.
The lesson here is to specify the type of cursor you want explicitly, and not rely on the defaults. This use of a cursor mandates a static (snapshot) type:
DECLARE db_cursor CURSOR
    LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR ...


mysqld upgrade 5.5 -> 5.6 mysql Error: Failed while fetching Server version! Could be due to unauthorized access

Please check if you are using correct root password. You can also use another login which has all privileges. Check it by logging to mysql:

Successful login example:

After checking return to server try again mysql_upgrade


Oracle RMAN Incremental update or merge questions

1: Incremental image copy update is only supported on disk

2: Updating the image copy is done using a differential backupset.  So you need the diff before you can update the image copy.  A good description of the process is in the Oracle docs

For both answers, see the RMAN Backup and Recovery User's Guide: http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmbckba.htm#BRADV8186

Table 9-2 explains the process in detail.


CMS (Central Manamenent Server) help/guidance?

There is no real best practice, but if you want to use a job server as well you should choose your CMS as a server which is a standard edition, if not you can even use a SQL express edition as your CMS.

If you are managing a multi server/instance environment, there are no real cons/drawbacks, this will enhance the way you can monitor & query your environment.Also if you have multiple dba's in your environment you can work together on the same CMS, and see the newly installed instances from your Collegue.

You can build your groups the way you like and want, I have environments where I have 2005 & 2008 & 2012 & 2014 together because they are production. The only thing that is a drawback with that is that some dmv's are different in 2005 then in 2008/2012/2014. So some multiserver-queries will fail if you don't make them 2005 compatible.

This is a transaction per instance, the result is returned as one result, but it will be a transaction for every instance.

I hope this answered all your questions.


Import Oracle full dump file to fresh Oracle installation

If you are using the old imp method the users and tablespaces must be created first.  This includes grants, database links and any advanced queues you may be using. A sample script could look like this in Windows
imp a_DBA_user/apassword LOG =C:\dump\logs\import.log file =user01.dmp fromuser=user_name touser=user_name

The newer datapump method works wonderfully.  You don't need to create the user but I would create any custom tablespaces you may have on the original.

A sample data pump call requires you to  create a database link to the original database  and a directory. This creates a directory

CREATE OR REPLACE DIRECTORY
DATA_PUMP_DIR AS
'C:\app\Oracle\product\11.2.0\dbhome_1\rdbms\log\';

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO EXP_FULL_DATABASE;

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO auser;

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO IMP_FULL_DATABASE;

In your script to import set these variables
NLS_LANG

ORACLE_SID= your database name

impdp a_user_who_exists/your database name directory=DATA_PUMP_DIR network_link=original database name schemas= user1,user2 LOGFILE=DataPump.log TABLE_EXISTS_ACTION=REPLACE


Is it possible to allow missing value inserts in mysql instead of requiring "NULL" in the query?

No, it's not possible.

It would be interesting to see how your script looks like. Maybe you should consider writing the default value ('null') when writing the script? Or do you receive it as it is?


Track daily view count of youtube videos

Well, the biggest thoughts I have are regarding the application and data collection.  For example, why are you storing the entire address?  You really should only store the "v=" option of the querystring.  That's the part of the web address to tells youtube to go to the video.  If you store the entire address, then you may have one video stored multiple times when new attributes are added to the querystring (such as &feature=youtube_gdata_player changes to &feature=embedded_player or something).  I would just pull of the string between "v=" and the next "&" to get a "VideoId" column.

Secondly, I would include a main table "Video" as such:

CREATE TABLE Video
(
    VideoPK INTEGER PRIMARY KEY IDENTITY,
    VideoId VARCHAR(100),
    PreviousViewCount BIGINT,
    CurrentViewCount BIGINT
)

This would allow you to do the screen scrape from Youtube, get the VideoID ("v=" parameter from the query string), set the PreviousViewCount to the CurrentViewCount and set the CurrentViewCount to whatever you find from the youtube screenscrape.  If you wanted to get fancy, you could add in a "SnapshotDate DATETIME" column to insure that at least 20 hours has passed since the last snapshot.

Then you could run a query against the Video table, something like:

SELECT VideoId
FROM Video
WHERE CurrentViewCount - PreviousViewCount > 1000

That would work out well for your purposes.

If you really wanted to log historical data, I would throw in a second table to do that:

CREATE TABLE VideoHistory
(
    HistoryID INTEGER PRIMARY KEY IDENTITY,
    VideoPK INTEGER
        FOREIGN KEY REFERENCES Video(VideoPK),
    SnapshotDate DATETIME,
    ViewCount BIGINT
)

Then, when you did the screenscrape, add a new entry into the VideoHistory table.

At least, that's how I would approach it.

Edit:  First, I didn't realize this is SQLite, so that syntax won't work for you.
Secondly, I misunderstood the question

Since you want to view all videos that have had a count of 1000 or more for the past N days, I would stick with something like this:

CREATE TABLE VideoHistory
(
    HistoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    VideoId VARCHAR(100),
    SnapshotDate DATETIME,
    ViewCount BIGINT,
)

The query for pulling the data is going to be slow (due to large amounts of data and correlated subqueries).  But a covering index should help out.

Also, I wouldn't put SearchTerm in the table since your search term probably won't change from one video to another.  I would add a secondary table, such as:

CREATE TABLE SearchTerm
(
    SnapshotDate DATETIME PRIMARY KEY,
    SearchTerm VARCHAR(100),
)

It will help remove the duplicate data.


Execution plan caching of ITVF called with inline vs dynamic SQL

The sp_executesql plan with OPTION (RECOMPILE) should be fully optimized for the specific parameter values on each call, with parameter embedding also enabled.

Taking the AdventureWorks example from Itzik Ben-Gan's comment on the article referenced in the question:

CREATE FUNCTION dbo.F
(
    @NameLike nvarchar(50),
    @Sort tinyint
) RETURNS TABLE
AS
RETURN
    SELECT TOP (5)
        ProductID,
        Name
    FROM Production.Product
    WHERE
        @NameLike IS NULL
        OR Name LIKE @NameLike
    ORDER BY
        CASE WHEN @Sort = 1 THEN ProductID ELSE NULL END ASC,
        CASE WHEN @Sort = 2 THEN ProductID ELSE NULL END DESC,
        CASE WHEN @Sort = 3 THEN Name ELSE NULL END ASC,
        CASE WHEN @Sort = 4 THEN Name ELSE NULL END DESC;

The execution plan for:

SELECT *
FROM dbo.F(N'K%', 3);

...shows the specific parameter values being inlined (so the @NameLike IS NULL is optimized away, enabling a seek) but parameter embedding does not take place so there is a Top N Sort for the ORDER BY clause:

If we add the recompile query hint, a plan is generated for the specific values with parameter embedding enabled (so no sort):

SELECT *
FROM dbo.F(N'K%', 3)
OPTION (RECOMPILE);

If we wrap the query in sp_executesql we get neither inlining nor embedding, since the values are explicitly parameterized:

EXECUTE sys.sp_executesql
    N'SELECT * FROM dbo.F(@NameLike, @Sort)',
    N'@NameLike nvarchar(50), @Sort tinyint',
    @NameLike = N'K%', @Sort = 3;

Adding OPTION (RECOMPILE) enables both optimizations again:

EXECUTE sys.sp_executesql
    N'SELECT * FROM dbo.F(@NameLike, @Sort) OPTION (RECOMPILE)',
    N'@NameLike nvarchar(50), @Sort tinyint',
    @NameLike = N'K%', @Sort = 3;


To take a different approach, what is driving your read workload - Is it the application, or do you have a bunch of reports hanging off the system that are driving the load?  Depending on the nature of your workload you might be able to re-shuffle your hardware or push some of it off onto other machines.

A couple of ideas:

If you have a lot of read workload driven by reports (as opposed to application screens), consider moving those off onto a replicated server (more than one if necessary).  More extremely, if you have the option of doing this, you could create a data mart specificually tuned for the reporting.  Obviously this might depend on how up to date the data has to be.It should be possible to do this without having to interfere in the application code, so it's not as an extreme approach as one might think.  If you need it to be up to date you could do something with change data capture at the expense of extra complexity.
If your DB server is I/O bound but you have plenty of spare CPU capacity (or maybe you can upgrade the CPUs) you might get some mileage from placing some or all of your database on SSDs.

Without looking into the technical specifics of your problem it's hard to see what can actually be done with the workload.


SQL 2005 SP2 Database on SQL 2012 Instance

The Compatibility Level never changes due to a Service Pack upgrade. So SP4 has the same Compatibility Level as SP2, same features and limitations etc, it's just presumably better/faster/more stable/more secure.

SQL2005 databases have Level 90 (unless they were restored from even older versions without updating the Level) and can be restored on SQL 2005 through 2012.

For the complete table of SQL Server versions vs supported Compatibility Levels, see:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level


How do I allow users to add members to a specific database role?

Look at the syntax diagram a little more carefully. Typically, above object level, you must specify the type you are applying permissions to with <entity_type>::<entity_name>.

GRANT ALTER ON ROLE::[NewDBRole] TO [domain\username] WITH GRANT OPTION AS dbo;


Understanding aggregate window functions

I suggest you add a sum to understand what is going on:

SELECT col1
     , keycol
     , SUM(keycol)  OVER (PARTITION BY col1) AS mysum
     , AVG(keycol) OVER (PARTITION BY col1) AS RowAvg
FROM T1
order by col1;

Since you don't have an order by in your window, the aggregate applies to the whole partition:

col1    keycol  mysum   RowAvg
A       2       5       2
A       3       5       2
B       5       23      7
B       7       23      7
B       11      23      7
C       13      72      18
C       17      72      18
C       19      72      18
C       23      72      18

I.e. for partition A, mysum = 2+3 for every row in the partition

If you use an ORDER by clause the aggregate is applied from the beginning to the current row:

SELECT keycol
, col1
, SUM(keycol)  OVER (ORDER BY keycol) AS mysum
, AVG(keycol) OVER (ORDER BY keycol) AS RowAvg
FROM T1
order by col1;

since you don't have a partition, the whole result set is treated as 1 partition:

keycol  col1    mysum   RowAvg
2       A       2       2
3       A       5       2
5       B       10      3
7       B       17      4
11      B       28      5
...

For first row  (according to order by) mysum = 2,    rowavg = 2/1
  , second row                         mysum = 2+3,  rowavg = 5/2
  , third row                          mysum = 2+3+5 rowavg = 10/3
  ...

As you can see the sum(...) becomes a cumulative sum

With both a partition and order, the aggregate applies to each partition, but with the behaviour described above:

SELECT keycol
, col1
, SUM(keycol)  OVER (PARTITION BY col1 ORDER BY keycol) AS mysum
, AVG(keycol) OVER (PARTITION BY col1 ORDER BY keycol) AS RowAvg
FROM T1
order by col1;

keycol  col1    mysum   RowAvg
2       A       2       2
3       A       5       2
5       B       5       5
7       B       12      6
...

For A you get mysum 2, 2+3. For B it restarts so it becomes 5, 5+7,

In addition you can override the default behavior which is:

SELECT keycol
, col1
, SUM(keycol)  OVER (PARTITION BY col1
                     ORDER BY keycol
                     RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as ...
FROM T1
order by col1;

by declaring your own, for example if you want a sliding average over 3 rows:

SELECT keycol
, col1
, AVG(keycol)  OVER (PARTITION BY col1
                     ORDER BY keycol
                     ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as ...

...
13  C   15
17  C   16
19  C   19
23  C   21

The first row gets an average of (13+17)/2 (since there is no preceding row), the second (13+17+19)/3, ... and the fourth row becomes (19+23)/2 (no rows following)

Fiddle with examples


I'd recommend checking for the existence of the rdsadmin database (to be precise, this database exists for any RDS instance, not just an Aurora-based one).   One way to check would be to use the psql command-line tool to output a list of databases.  Just replace [AuroraClusterEndpoint] and [UserName] in the following

psql -h [AuroraClusterEndpoint] -U [UserName] --password -l

Of course, this assumes you haven't created a database named rdsadmin on your non-RDS Postgres cluster.

You could also use the SHOW ALL command within psql to check for the existence of an RDS-based run-time parameter:

With both solutions, just use whatever scripting tool you're comfortable with (Powershell, Bash, etc...) if you want to programmatically parse out the results


Finding available schedules for multiple slots

Updated answer:

select
    Slots.Id,
    Schedule.StartTime,
    Schedule.EndTime
from
    Slots
join Schedule on 1 = 1
left outer join SlotSchedule on Slots.ID = SlotSchedule.SlotId and Schedule.Id = SlotSChedule.ScheduleId
where SlotSchedule.SlotID is null;

... and here is the Fiddle.

This is perhaps not an orthodox approach. It does join the Slot and Schedule tables fully which may have serious performance issues on large tables - but I am guessing the number of rows in Schedule is tiny, and probably in Slot as well.

What this does:

(First, note, I would not use Slots as an alias for SlotSchedules when you also have a table named Slots - that's just way too confusing.)

Start with the Slots table. I then join this to the Schedule table (that's right - totally skipping the SlotSchedule table) on 1=1 - this means every row in Slots will join to every row in Schedule.

The next join is back to the SlotSchedule table and it is an outer join. This time we have conditions - we say that the Slot ID on the SlotSchedule must match our Slot record, and the Schedule ID on the SlotSchedule table must match our Schedule record - but because it is an outer join, if there is no match, we still include the SlotSchedule row, but with a value of null.

This sets us up for the WHERE clause, which is simply - we want all rows where the SlotSchedule information is null, and so we check SlotSchedule.SlotID is null.

In other words, for this combination of Slot and Schedule records, there is no SlotSchedule record, which is what we want.

Original answer:

Your where clause has:

where slots.SlotId is null

Your diagram shows:

slots.id

This is inconsistent.

Your results show slot_id = null (and your query requests this).

I don't understand how you can have slot records with null ID. It looks like you are trying to populate slotschedule with a row for every possible combination of slot and schedule, and to keep the ID null if the slot currently has no schedule.

I would recommend slotschedule only has a row when a slot is assigned to a schedule.

The first thing I would want to understand better is why slot_id is null - I think change this design to resolve the issue.


What will be the relation between Employees and Players, given that some players are also Employees?

can you give an example of exactly why there's a N:N with unique constraints for both Emp_id and player_is?

CREATE TABLE employee (employee_id INT PRIMARY KEY, ...);
CREATE TABLE player (player_id INT PRIMARY KEY, ...);
CREATE TABLE junction (employee_id INT NOT NULL,
                       UNIQUE (employee_id),
                       FOREIGN KEY (employee_id) REFERENCES employee (employee_id),
                       player_id INT NOT NULL,
                       UNIQUE (player_id),
                       FOREIGN KEY (player_id) REFERENCES player (player_id));

Unique constraints forbid one employee to be two separate players and backward.
If a player is not an employee than junction table does not contain a row for him, and backward.


Advice for my DB design and how to handle null / measuring errors

(Others may disagree, but here goes...)

If you have a good "natural" PRIMARY KEY, use it.  In my own tables, I find that is the case about 2/3 of the time.

What makes it good?

Must be UNIQUE (since the PK is, by definition, unique)
Must never be NULL (again, a PK requirement)
'Composite' is OK, as with your example:  (appID, created)
If there are no (or few) secondary keys, then it should be "small".  This is because every secondary index implicitly includes the PK columns.
A 'natural' key is more efficient for scanning.
Since your queries might be WHERE appID = constant AND created BETWEEN..., this order is much better:  PRIMARY KEY(assId, created).

Don't use INT (4 bytes) when MEDIUMINT (3 bytes) will suffice; etc.  If the value is 0..50, then TINYINT UNSIGNED (1 byte) is better.

How did you store 10,00 in INT?  Perhaps you meant DECIMAL(8,2)?

Do not normalize anything you might want to do a range test on, such as price or created.  In general, don't normalize 'continuous' values.  Nor small values.  For example, it is hardly worth normalizing a 5-digit zip code to replace it with a 3-byte MEDIUMINT.


How to distinguish the current operation is done from system or from sql server management studio

In most cases, you should be able to use some of the built-in functions:

SELECT @ModifyUser = SUSER_SNAME();

SELECT @ModifyUser = ORIGINAL_LOGIN();

This will work if they are Management Studio on their own machine and running queries remotely. If they are all using the same SQL authentication login, then in that case you should be able to tell them apart by grabbing the host name or IP address (then you'll have to figure it out based on that):

SELECT c.client_net_address, s.[host_name]
  FROM sys.dm_exec_connections AS c
  INNER JOIN sys.dm_exec_sessions AS s
  ON c.session_id = s.session_id
  WHERE s.session_id = @@SPID
  AND c.session_id = @@SPID;

In some cases, you're not going to be able to tell who it is. For example, if you let everyone log onto a common machine, access the instance through a local copy of SSMS, and they all use the same SQL authentication login, they will all look the same. There is no magic that will give SQL Server information it doesn't have. If auditing is important to you, there's a simple solution: stop letting them do that.


How can i import 150GB database after deleting ibdata1,logfile in MYSQL?

You need to mysqldump all the data (InnoDB and MyISAM) to a local text file and follow your steps.

I have discussed this topic before

Dec 06, 2012 : InnoDB insertion faster
Aug 29, 2012 : Removing/overwriting sensitive data located in already deleted records in ibdata files
Oct 29, 2010 : My Original Post in StackOverflow

You may be concerned about the amount of data to dump. If you have multiple database, perhaps doing a mysqldump of all that data into a single table may not be advisable.

You could perform a mysqldump for each database into separate files

Please read my other posts on how to do separate mysqldumps

Apr 17, 2011 :  How can I optimize a mysqldump of a large database?
Dec 07, 2013 : Is there a Successor or an Alternative to mk-parallel-dump?

UPDATE 2014-04-10 11:21 EDT

Given these facts

You have two databases (call them db1 and db2)
First Database is 140 GB
Second Database is 7 GB

Here are two courses of action

COURSE OF ACTION #1 : Dump Databases Separately

Here is a straightforward mysqldump approach to dump and restore

Dump Databases into Separate Files

USERPASS="-uroot -ppassword"
DBTODUMP=db1
DUMPFILE=${DBTODUMP}_dump.sql
echo "CREATE DATABASE IF NOT EXISTS ${DBTODUMP};" > ${DUMPFILE}
echo "USE ${DBTODUMP}" >> ${DUMPFILE}
mysqldump ${USERPASS} --routines --triggers ${DBTODUMP} >> ${DUMPFILE}
DBTODUMP=db2
DUMPFILE=${DBTODUMP}_dump.sql
echo "CREATE DATABASE IF NOT EXISTS ${DBTODUMP};" > ${DUMPFILE}
echo "USE ${DBTODUMP}" >> ${DUMPFILE}
mysqldump ${USERPASS} --routines --triggers ${DBTODUMP} >> ${DUMPFILE}

Restore Databases (after making new ibdata1 and logfiles)

USERPASS="-uroot -ppassword"
DBTODUMP=db1
DUMPFILE=${DBTODUMP}_dump.sql
mysql ${USERPASS} < ${DUMPFILE} &
DBTODUMP=db2
DUMPFILE=${DBTODUMP}_dump.sql
mysql ${USERPASS} < ${DUMPFILE} &
wait

COURSE OF ACTION #2 : Dump Tables Separately

Here is code to dump all the tables 5 at a time

USERPASS="-uroot -ppassword"
TABLESTODUMP=/tmp/TablesToDump.txt
DUMP_LOCATION=/some/path/you/choose
SQL="SELECT CONCAT(table_schema,':',table_name)"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema IN ('db1','db2')"
mysql ${USERPASS} -ANe"${SQL}" > ${TABLESTODUMP}
COMMIT_COUNT=0
COMMIT_LIMIT=5
for DBTB in `cat ${TABLESTODUMP}`
do
    DB=`echo "${DBTB}" | sed 's/:/ /g' | awk '{print $1}'`
    TB=`echo "${DBTB}" | sed 's/:/ /g' | awk '{print $2}'`
    DUMPFILE=${DUMP_LOCATION}/${DB}_${TB}.sql
    echo "CREATE DATABASE IF NOT EXISTS ${DB};" > ${DUMPFILE}
    echo "USE ${DB}" > ${DUMPFILE}
    mysqldump ${USERPASS} ${DB} ${TB} >> ${DUMPFILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ] ; then wait ; fi

Restore Databases (after making new ibdata1 and logfiles)

DUMP_LOCATION=/some/path/you/choose
cd ${DUMP_LOCATION}
for DUMPFILE in `ls *.sql` ; do mysql < ${DUMPFILE} ; done

CAVEAT

Make sure DUMP_LOCATION has enough space


Joins are a concept of Relational Algebra, you can find some information on wikipedia.
Join is considered an additional operator.


MYSQL Multiple Join Query to Display Unique Column Values in One Field for Single Record

Following what's already written at How to use GROUP_CONCAT in a CONCAT in mysql...

Let's assume you have a schema and sample data equivalent to:

CREATE TABLE properties
(
    id integer PRIMARY KEY,
    property varchar(255) NOT NULL
) ;

CREATE TABLE recommended_providers
(
   id integer PRIMARY KEY,
   recommended_provider_name varchar(255) NOT NULL
) ;

CREATE TABLE properties_recommended_providers
(
   prop_id INTEGER NOT NULL REFERENCES properties(id),
   recommended_provider_id INTEGER NOT NULL REFERENCES providers(id),
   PRIMARY KEY (prop_id, recommended_provider_id)
) ;

INSERT INTO properties
VALUES (1, 'property 1'), (2, 'property 2') ;

INSERT INTO recommended_providers
VALUES (1, 'provider 1'), (2, 'provider 2'), (3, 'provider 3');

INSERT INTO properties_recommended_providers
VALUES (1, 1), (1, 2), (2, 3) ;

You can either have:

SELECT
  p.id AS property_id, property,
  GROUP_CONCAT(recommended_provider_name SEPARATOR ', ') AS provider_names
FROM
  properties p
  LEFT JOIN properties_recommended_providers pr ON pr.prop_id = p.id
  LEFT JOIN recommended_providers rp ON rp.id = pr.recommended_provider_id
GROUP BY
  property_id
ORDER BY
  property_id ;

(Check it at SQL Fiddle)

or

SELECT
  p.id AS property_id, property,
  (SELECT
      GROUP_CONCAT(recommended_provider_name SEPARATOR ', ')
   FROM
       properties_recommended_providers pr
       LEFT JOIN recommended_providers rp ON rp.id =
                                         pr.recommended_provider_id
   WHERE
       pr.prop_id = p.id
   ORDER BY
       /* You can easily order by in this case */
       recommended_provider_name
   ) AS provider_names
FROM
  properties p
ORDER BY
  property_id ;

You can check the second one at SQL Fiddle


Setting up PostgreSQL Connection with DBVisualiser

Did you checked your postgresql configuration, mainly the pg_hba.conf? Can you login to your server with psql? http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html


Oracle Packages, Procedures and Functions Not Respecting Roles

When you execute PL/SQL in a package/procedure/trigger, Oracle disables roles (this is not the case for anonymous blocks). As a result, you have to grant permissions directly, rather than through roles.

Jonathan Lewis explains this well here, plus it's covered in the documentation here.

Invoker/definers rights are documented here.


Apache DdlUtils can help with some of the migration: http://db.apache.org/ddlutils/

You might also have a skim of the tools listed here: http://wiki.apache.org/db-derby/UsesOfDerby to see if any of those have features that help with migration

Derby also has some built-in tools for bulk-importing of data: http://db.apache.org/derby/docs/10.9/ref/rrefimportproc.html


Group by WEEK but show a more readable the result?

You can accomplish this with the DATE_FORMAT function,

SELECT YEAR(datetime) AS Year, DATE_FORMAT(datetime, '%b %e') AS Week, COUNT(*) AS total
FROM table
GROUP BY Year, Week;


Force SQL Server to run query conditions as written?

You are falling into the "Catch-All Query" trap, which is explained very well by Gail Shaw here.

To summarize the problem:  SQL Server optimizes away the significant overhead of query compilation by caching a query plan after compilation, and then later checking the cache for a matching query plan before a later compilation.  The "matching" that occurs here is purely textual, so the actual value of a variable will not affect this.

That's good 99% of the time, but in some cases it's bad.  One case where it's bad is when someone tries to construct a WHERE clause as though its like a short-circuiting IF statement in C, etc.  This doesn't work well, because the SQL compiler has to make one query plan that will work regardless of what the parameter values actually are, and the only way that it can handle these "clever" logical switching-conditions in the WHERE clause is to make a simple brute-force plan that just scans the whole table, filtering the rows as it goes, without leveraging any indexes.

Not suprisingly, this makes them uniformly slow, no matter what the parameter/variable values are.


You probably want to do this on the client side / in your application, not on the database server. This would give your application all information to still reference each row for further action. The unique identifier in your example would be the combination (product_name, colors_available) - this information is lost, when you change your output on the server side.

If you still want to do it in mysql...

Let's assume your_query returns data like this right now:

+---------------+------------------+------------+
| product_name  | colors_available | items_left |
+---------------+------------------+------------+
| JUMPAN TSHIRT | red              |          3 |
| JUMPAN TSHIRT | blue             |          2 |
| JUMPAN TSHIRT | black            |          9 |
| B.BALL CUP    | red              |         10 |
+---------------+------------------+------------+

The following query will suppress output of product_name whenever the current product_name doesn't change to the row before:

SELECT
        -- compare current row's product_name to local variable
    IF( yq.product_name = @last_product_name,
        -- if they match return NULL
        NULL,
        -- if they don't match update local variable to current product_name
        @last_product_name := yq.product_name
      ) AS product_name,
    yq.colors_available,
    yq.items_left
FROM (your_query) AS yq

This will result in the following output:

+---------------+------------------+------------+
| product_name  | colors_available | items_left |
+---------------+------------------+------------+
| JUMPAN TSHIRT | red              |          3 |
| NULL          | blue             |          2 |
| NULL          | black            |          9 |
| B.BALL CUP    | red              |         10 |
+---------------+------------------+------------+

You can change NULL to '' to return an empty string, of course.


It is optional, though you will get a warning during setup.

Here are different config scenarios for MSDTC.


Forbid mongodb not authenticated connect

A new connection to MongoDB has two key aspects:

establishing a connection
authenticating the connection

As at MongoDB 3.4, the source IPs that are able to establish a connection are determined by  your firewall settings. There is also a bindIp setting for MongoDB which determines which network interface(s) mongod listens to.

Once a connection is established, access control and authentication is managed by your MongoDB configuration.

In your example, you are able to connect via the mongo shell because the source IP is not blocked by firewall configuration. Assuming access control is enabled and authentication has been properly configured, establishing a connection to MongoDB does not provide any access to data.

For more information on best practices, see the MongoDB Security Checklist.


These terms are not new to SQL 2016, but they are also not server roles as far as I know.

For all i know these are pre-built Parallel Data Warehouse resource classes.

There isn't much documentation on that except some blog posts.

Have a look at these

PDW, Integration Services and Resource Classes
A teched presentation from Channel 9

I only heard about these in some presentation though, I have never worked with PDW so I can't help you any more than this.

Edit

Just adding in one additional resource (direct link to download PPT)
SQL Server 2012 Parallel Data Warehouse. You are basically controlling who gets more "speed" with processing by what role they are members of, at least from what I gather. The last slide in the deck:

Edit

In the meantime an article on workload management has been posted in the Azure documentation: Concurrency and workload management in SQL Data Warehouse where the following is mentioned (thanks Bob Klimes):

  SQL Data Warehouse exposes four different resource classes in the form
  of database roles as part of its workload management implementation.

  The roles are:

smallrc
mediumrc
largerc
xlargerc


There are several different ways to approach to problem.  One is simply to group by the bookmarks you care about then obtain a list of tags for each - the substring_index function is being used to limit the number of tags to ten which are in ascending order.  Order and the separation character can be changed to fit your purpose.

select bm.bookmark_id
     , substring_index(group_concat(tag.tag order by tag.tag asc separator ','),',',10) as tag_list
  from v_bookmark_tag_assoc bm
     , tag
 where bm.bookmark_id in (123,456,678,890)
   and tag.tag_id=bm.tag_id
 group by bm.bookmark_id


What is the meaning of "select * from relational(owner.table)"?

It appears to be inert. I tested this on XE 11 and found that it does nothing.

CREATE TABLE dbausers AS SELECT * FROM dba_users;

-- Function doesn't affect result set.
SELECT * FROM dbausers
MINUS
SELECT * FROM RELATIONAL(dbausers);

SELECT * FROM RELATIONAL(dbausers)
MINUS
SELECT * FROM dbausers;


Oracle Linked Server in Azure SQL Database Managed Instance

Linked servers in Managed Instance support a limited number of targets. Supported targets: SQL Server and SQL Database Not supported targets: files, Analysis Services, and other RDBMS. For more information, please read this documentation.

You may consider to try with SQL Server installed on Azure VM using a private VNET instead of using a Managed Instance.


Get Table Name from Transaction Log query

You need to ensure you are running the query in the context of the correct database.

Since system views such as sys.objects are context sensitive, they only return rows related to the current database.

For instance, running this code in master would only show tables where the PartitionId exists in master, and those table names would likely be incorrect.

Assuming your database name is OTIS you could work around this by adding the database name into your join clause, such as JOIN OTIS.sys.partitions and JOIN OTIS.sys.objects.

Since you've hard-coded the name of the log file itself, something like this should work more reliably:

SELECT [Transaction ID],
    [Current LSN],
    PartitionId,
    sp.object_id,
    so.[name],
    [Operation],
    [Context],
    [AllocUnitName],
    [Begin Time],
    [End Time],
    [Transaction SID],
    [Num Elements] ,
    [RowLog Contents 0],
    [RowLog Contents 1],
    [RowLog Contents 2],
    [RowLog Contents 3]
 FROM fn_dump_dblog(NULL,NULL,'DISK',1
    ,'D:\Users\Chad\SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\OTIS_LogBackup_2016-02-02_13-36-21.bak'
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL) as logF
    LEFT OUTER JOIN OTIS.sys.partitions sp ON sp.partition_id = logF.PartitionId
    LEFT OUTER JOIN OTIS.sys.objects so on so.object_id = sp.object_id
WHERE
    [Transaction ID] = '0000:00d15747';

Alternately, you could simply place USE OTIS; prior to your select statement, so it executes first.


I know this question is old but I monitore all my updates in postgresql including updates inside transactions.. So I decided to help!
1- First create a sequence named myprogress for example.
create sequence myprogress;

2- Make your update to hit the sequence - now you are free to start a transaction:
For example if you have this update:
update mytable set c=3 where a=0 and b=1;

Just rewrite  it to (plus this and condition):
update mytable set c=3 where ( a=0 and b=1 ) and  NEXTVAL('myprogress')!=0;

**You are ok to run this update inside a transaction, as sequences are affected globally. **
Note that this will not significantly affect performance.
3- Monitore your progress. Now you can just connect in another session (or a transaction) and select your sequence (sequences are affected globally , so you will see the value in the another sessions):
select last_value from myprogress;

And you will see how many lines are affected by your query until now, with that you can estimate how many lines by second are hit by your update.
And in most of the cases how many time you will need to wait...
4- At end just restart the sequence:
alter sequence myprogress restart with 1;

So you can use it again - But carefully, to do not trust this if two users use the same sequence. If you are in doubt better if you have your own progress sequence with permissions only for you.
You can use it for slow SELECTs, DELETEs, and for some INSERTs too !


Check what event called the trigger on PostgreSQL?

Trigger functions can check the value of the TG_OP variable to determine the event which caused the trigger to fire.  There are a number of other special variables as well listed in the documentation.


Is it ever a good idea to denormalize for integrity?

You are getting at something that is missing from standard normalization discussions, namely the constraint dependencies.  In general wider tables provide greater possibilities here than narrower tables.  So my view is that the sorts of questions you are asking in fact highlight good reasons to denormalize.  I would go with your first solution (the one you are leaning towards right now).

In my view good database design generally normalizes as far as possible but ensures that all columns necessary for proper data constraints are included.  You can do some of this with composite foreign keys if you don't mind adding additional unique constraints on the parent tables.  Leveraging data constraints an important part of database design and one should not sacrifice that for the sake of normalization that looks good in theory.


How can I determine what's a better configuration for my app?

The correct configuration depends on your hardware, your database size and your workload, none of which we know.
Tune conservatively at first, setting shared_buffers and work_mem to some reasonable values based on database size and workload, and setting maintenance_work_mem, effective_cache_size, effective_io_concurrency and random_page_cost according to your hardware. Finally,  configure logging.
Adjust these and other parameters based on the findings from a realistic load test.


Most efficient ordering post database design

You can change your post_order column to a float and update the value with a calculation using the post_order values from the posts you want to end up between.

Example:

PostID, PostOrder
1       1
2       2
3       3

If you want to move PostID = 3 to be sorted between 1 and 2 it would be

PostID, PostOrder
1       1
3       1.5
2       2

Here is a SQL Fiddle with some code that moves the posts using a stored procedure where you pass in the PostID of the post to be moved and PostID of the post you want to end up behind.

SQL Fiddle

MySQL 5.5.30 Schema Setup:

create table Post
(
  PostID int primary key,
  PostOrder float unsigned not null unique,
  check (PostOrder > 0)
);

insert into Post(PostID,  PostOrder) values (1, 1);
insert into Post(PostID,  PostOrder) values (2, 2);
insert into Post(PostID,  PostOrder) values (3, 3);
insert into Post(PostID,  PostOrder) values (4, 4);
insert into Post(PostID,  PostOrder) values (5, 5);
insert into Post(PostID,  PostOrder) values (6, 6);
insert into Post(PostID,  PostOrder) values (7, 7);
insert into Post(PostID,  PostOrder) values (8, 8);
insert into Post(PostID,  PostOrder) values (9, 9);
insert into Post(PostID,  PostOrder) values (10, 10);

//

create procedure MovePost(MovePostID int, AfterPostID int)
begin

  declare AfterPostOrder float;
  declare NextPostOrder float;

  set AfterPostOrder = (select PostOrder
                        from Post
                        where PostID = AfterPostID);

  if AfterPostOrder is null then
    -- Move first
    set AfterPostOrder = 0;
  end if;

  set NextPostOrder = (select min(PostOrder)
                       from Post
                       where PostOrder > AfterPostOrder);

  if NextPostOrder is null then
    -- Move last
    set NextPostOrder = (select max(PostOrder) + 2
                         from Post);
  end if;

  update Post
  set PostOrder = (AfterPostOrder + NextPostOrder) / 2
  where PostID = MovePostID;

end

Query 1:

call MovePost (7, 3);    -- Move 7 after 3
call MovePost (8, 3);    -- Move 8 after 3
call MovePost (9, null); -- Move 9 first
call MovePost (2, 10);   -- Move 2 after 10

select *
from Post
order by PostOrder

Results:

| POSTID | POSTORDER |
----------------------
|      9 |       0.5 |
|      1 |         1 |
|      3 |         3 |
|      8 |      3.25 |
|      7 |       3.5 |
|      4 |         4 |
|      5 |         5 |
|      6 |         6 |
|     10 |        10 |
|      2 |        11 |


getting mysql error when running the query alognwith ssh

I may be wrong here - but isn't the echo command running on the server you're reaching via ssh - and the /usr/bin/mysql running on your local machine?

Whereas, both commands are running on the remote machine when issued on the remote machine, without the ssh?

What if you moved the ssh call to after the pipe, on the /usr/bin/mysql command?

NOTE: It's been a while since I used Unix/Linux, but a quick Google check on ssh seems to indicate this is how it works....


You should specify the length of the varchar column e.g. varchar(2000).


In SQL Server 2008 you can control user access to a table and fields also.All tables and fields are available in the security system, and access can be set up individually for each user group working within a company or domain without affecting other user groups. Table and field access is configured when you set security keys (Administration > Setup > Security > User group permissions on the Permissions tab).
For more information visit following link in Microsoft site:
http://technet.microsoft.com/en-us/library/aa834466(v=ax.50).aspx


SlaveThreadCount at 0 for non-root user, should be 2

If you have all privileges on a specific database, you cannot have the PROCESS privilege. Why ? The PROCESS privilege is not a database-level privilege. It's a global privilege. How ?

About 2.5 years ago, I answered How to grant super privilege to the user? Someone wanted the SUPER privilege but only had all privileges on a database. I explained by mentioning how global grants and database-level grants are stored. In short, there is a column called Super_priv in the table mysql.user (where global grants are stored). That column does not exist in mysql.db (where database-level grants are stored).

The same exact thing applies to the PROCESS privilege. There is a column called Process_priv in the table mysql.user. That column does not exist in mysql.db.


How bad is it to delete from a Greenplum AO table?

The main difference between heap table and AO tables is how the visibility data is stored. In heap tables visibility information is the header of each record, AO tables have a separate visibility map structure.

Once there have been UPDATE or DELETE operations on an AO table this visibility map needs to be processed. This will incur a very small addition load for reading the map and processing it. The processing is just a set/not-set check so it's a lot lighter than the range check for MVCC visibility on heap tables.

With a small percentage of rows being dropped the effects on scan operations will be negligible. If you VACUUM an AO table where there have been UPDATE or DELETE operations then the table will be compacted, this will create a lot of disk IO and may require quite a bit of space.


How does subpartitioning actually work and what physical files are created?

Sample in Windows

I ran the following on my Laptop in MySQL 5.6.15

mysql> use test
Database changed
mysql> drop table if exists nums_composite;
Query OK, 0 rows affected (0.70 sec)

mysql> create table nums_composite (
    -> id int, happened datetime not null,
    -> primary key (id, happened)
    -> ) engine=InnoDB
    -> partition by range (hour(happened))
    -> subpartition by hash(id) subpartitions 2
    -> (partition p0 values less than (10),
    -> partition p1 values less than (20),
    -> partition p2 values less than MAXVALUE);
Query OK, 0 rows affected (1.91 sec)

mysql> show create table nums_composite\G
*************************** 1. row ***************************
       Table: nums_composite
Create Table: CREATE TABLE `nums_composite` (
  `id` int(11) NOT NULL DEFAULT '0',
  `happened` datetime NOT NULL,
  PRIMARY KEY (`id`,`happened`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (hour(happened))
SUBPARTITION BY HASH (id)
SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.01 sec)

mysql>

I went to the OS and ran the following:

C:\>cd \MySQL_5.6.15\data\test

C:\MySQL_5.6.15\data\test>dir nums_comp*
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\MySQL_5.6.15\data\test

08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp1.ibd
08/31/2014  04:09 PM             8,594 nums_composite.frm
08/31/2014  04:09 PM                96 nums_composite.par
               8 File(s)        598,514 bytes
               0 Dir(s)  686,691,409,920 bytes free

C:\MySQL_5.6.15\data\test>

Reason you cannot see the files ?

I can see my data files because I have innodb_file_per_table enabled by default in MySQL 5.6.

mysql> show global variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql>

In your case, you must have innodb_file_per_table disabled. The actual table was inside the system tablespace file ibdata1 (usually located in /var/lib/mysql)

My Guess

You are using a version before MySQL 5.6
innodb_file_per_table is set to 0 because of one of the following:

You are running on defaults (no my.cnf)
You have my.cnf with innodb_file_per_table=0

How to fix this:

STEP #1 : Enable innodb_file_per_table

Add this to the my.cnf

innodb_file_per_table=1

STEP 2 : Restart mysql

service mysql restart

STEP 3 : Regenerate the table

CREATE TABLE nums_composite_new LIKE nums_composite;
INSERT INTO nums_composite_new SELECT * FROM  nums_composite;
ALTER TABLE nums_composite RENAME nums_composite_old;
ALTER TABLE nums_composite_new RENAME nums_composite;
DROP TABLE nums_composite_old;

The .ibd files should now be visible

GIVE IT A TRY !!!

UPDATE 2014-08-31 16:37 EDT

You asked me

  I am in ubuntu.Does it change anything?

The only thing that changes is where my.cnf should be. Run this

ls -l /etc/mysql/my.cnf

If the file exists, open it in an editor and add innodb_file_per_table=1 under the [mysql] header

If the file does not exist, run this

echo "[mysqld]" > /etc/mysql/my.cnf
echo "innodb_file_per_table=1" >> /etc/mysql/my.cnf

Then, you can do STEP 2 and STEP 3


Insert self referencing entry into SQL Server

Insert self referencing entry into SQL server

For the general question as per the title, you can add a direct circular reference in a simple insert such as

INSERT node
       (id , name  , parent_id)
VALUES (123, 'Test', 123      )

because the constraint is enforced considering all the new data: as long as the value the FX references exists once the statement is complete all is well. This is the same as inserting several values in a linked list:

INSERT node
       (id , name   , parent_id)
VALUES (101, 'Test1', 100      )
     , (102, 'Test2', 101      )
     , (103, 'Test3', 102      )

or an indirect circular reference:

INSERT node
       (id , name   , parent_id)
VALUES (201, 'Test5', 203      )
     , (202, 'Test6', 201      )
     , (203, 'Test7', 202      )

  So the value in the created_by column should be the auto-incremented value of the id column

This poses a problem because you don't know what the generated is before you insert, in fact there is no reliable way of knowing. For inserting individual rows you can use SCOPE_IDENTITY() to immediately update the new row:

INSERT node
       (name  )
VALUES ('Test')
-- and now make the circular reference
UPDATE node
SET    parent_id = SCOPE_IDENTITY()
WHERE  id        = SCOPE_IDENTITY()

In the above example if parent_id is a required column (declared NOT NULL with no DEFAULT) then you'll need to provide a dummy temporary valid value instead of leaving it out of the initial INSERT statement, like so:

INSERT node
       (name  , parent_id)
VALUES ('Test', 0        )
-- and now make the circular reference
UPDATE node
SET    parent_id = SCOPE_IDENTITY()
WHERE  id        = SCOPE_IDENTITY()

For dealing with inserts of multiple rows (unlikely in the circumstance you describe but common elsewhere) you can use the OUTPUT clause to read the IDs created for each row for further reference. I'll not go over that here as it is overkill for the current question, see https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql for more detail.

  So the value in the created_by column should be the auto-incremented value of the id column

It turns out that this can be done with a SEQUENCE in SQL Server if you have 2012 or above, which is a neater solution than the multi-statement options above, see SQLing4ever's answer for a worked example. Before 2012 this feature was not available, so you'll need to fall back to the methods in this answer if you need to support older instances of SQL Server.


Based on what criteria voting take place in MongoDB?

Replica Set Elections

The MongoDB manual describes Replica Set Elections and the associated election mechanics such as Vetoes in detail.

Broadly speaking, the criteria for electing a node as primary includes:

Is that node currently in PRIMARY or SECONDARY state?
Is that node eligible to become primary (priority > 0)?
Do a majority of the nodes of the replica set see that memory as available (by way of replica set heartbeat)?
Does that node have the least replication lag in relation to the current primary?
Is that node the highest priority eligible node?
Would the node veto itself if elected primary? For example, if you step down a primary it will veto itself for election for 60 seconds by default.

The first node to receive a majority of votes subject to the election mechanics will become primary.

There are some further subtleties to the process depending what triggered the election (i.e. a unplanned failover versus stepping down the current primary).

  Suppose a replication set R1 contains one primary, 2 secondary and one arbiter node.

In order to ensure consensus can be reached by a strict majority of voting nodes, the general recommendation is to have an odd number of voting nodes.  With your example configuration an arbiter node is not required, and you should instead only have the primary and two secondaries.  An arbiter node does not have any special role in the election process aside from being available to cast a vote.

With all things being equal (no replication lag, default priorities, no network issues, etc) your two secondaries in this scenario are equally likely to be elected primary in the event the current primary unexpectedly fails.


Any way to force "garbage collection" in TokuDB?

Have you simply tried "mysql> show processlist;"? TokuDB displays the progress of optimize operations in plain view.


No priviligies to create table in custom database

It looks like you are trying to create a table in a schema called online_banking. In some platforms a schema and a database are synonymous, but in SQL Server, a schema is a container inside a database.

  Instance > Database > Schema > Object

In order to create a table in the online_banking database, you need to use a three-part name or be in the context of that database. And you should always use the schema prefix.

USE online_banking;
GO
CREATE TABLE dbo.test1(i INT);
GO

Or:

CREATE TABLE online_banking.dbo.test1(i INT);


Restore multiple databases quickly - practicing disaster recovery

There are couple of things that you need to make clear

  But in a real disaster situation I would love to be prepared better. How do you manage this?

Dont wait for the disaster to happen. Taking backups is not just enough. You should be able to confirm that the backups you take are able to restore as well. A backup is good ONLY if you are able to restore it (and DBCC CHECKDB returns no corruption errors).

I tend to take my backups with CHECKSUM. To quote Paul Randal

  using the WITH CHECKSUM option and having the backup complete successfully tells you that those pages with page checksums had not been corrupted by the I/O subsystem at the time the backup was taken.

  Do you have a tool or anything which registers the newly created DB's?

Technically, you dont need a tool. All the new databases are in master.sys.databases and msdb - backup tables record the backup history. The script from Paul Brewer - restore Gene takes care of generating the restore commands for you. (I have used it and its an excellent tool/script !)

If you really need to log what databases are created, then you can use server level trigger CREATE_DATABASE and log it in a DBA database.


What is more appropriate sp_updatestats or UPDATE STATISTICS?

Couple of things ..

Stop using the undocumented sp_MSforeachtable
From Erin's blog - Understanding What sp_updatestats Really Updates

  sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows

Note that sp_updatestats in the code sp_helptext sp_updatestats, it runs 'UPDATE STATISTICS '

Recommendation:

Use Ola's Index maintenance solution


You need to have a distinction between varchar and nvarchar. There is a big difference. If I try to create varbinary from your "should work out to" value:

SELECT CONVERT(VARBINARY(64), '201407240005688309')

I get this value, which looks kind of like the value you're converting, but not quite:

0x323031343037323430303035363838333039

So now if I change the input to be nvarchar:

SELECT CONVERT(VARBINARY(64), N'201407240005688309')

Now I do get the value you wanted:

0x3200300031003400300037003200340030003000300035003600380038003300

So if I take that value and try to convert it to nvarchar instead of varchar, we're back where we started:

SELECT CONVERT(NVARCHAR(MAX),
  0x3200300031003400300037003200340030003000300035003600380038003300300039)

Result:

201407240005688309

The online converter you're using must be using Unicode behind the scenes. You need to do that, too.


Three(3) Suggestions
SUGGESTION #1
You have the following clause in the SQL
mainDateTime BETWEEN '2012-06-20' AND '2012-07-07'
If mainDateTime hold s a date and a time you should change the clause to this
mainDateTime BETWEEN '2012-06-20 00:00:00' AND '2012-07-07 23:59:59'
SUGGESTION #2
The whole WHERE clause gives away what index both tables need:
ALTER TABLE tblMainData
ADD INDEX deviceSerial_mainDateTime_ndx (deviceSerial,mainDateTime);
ALTER TABLE tblSecondData
ADD INDEX deviceSerial_mainDateTime_ndx (deviceSerial,mainDateTime);

That way, each SELECT will perform better index scans
SUGGESTION #3
If you want everything ordered by mainDateTIme, you should include it in the SELECT list:
SELECT * FROM (
SELECT dataString,dataInsertDateTime,mainDateTime FROM tblMainData
WHERE deviceSerial='A111' AND mainDateTime BETWEEN '2012-06-20' AND '2012-07-07'
union
SELECT dataString,dataInsertDateTime,mainDateTime FROM tblSecondData
WHERE deviceSerial='A111'  AND mainDateTime BETWEEN '2012-06-20' AND '2012-07-07')
as table1
ordder by mainDateTime;


You can try something like this:

UPDATE Set name = REPLACE(t.name, r.bad, r.good)
FROM yourTable t
INNER JOIN (values
    ('FY15-Q2', 'FY16-Q1')
    , ('FY25-Q2', 'FY36-Q1')
    , ...
) as r(bad, good)
    On t.name like '%'+bad

You can add multiple pattern after FY25-Q2.

It should work fine if you don't have multiple matches. It may not be really efficient if you have tons of rows.

If your pattern is in the middle of the string, add a % at the end as well: On t.name like '%'+bad+'%'.

Before running the full update, you should first replace UPDATE by SELECT and look at what should be done:

SELECT t.name, newname = REPLACE(t.name, r.bad, r.good), r.bad, r.good
...


Multiple concurrent connections to same large SQLite file on a network share

Concurrency is not a problem, as long as you only read. SQLite doesn't allow concurrency on writes, because it never was designed for that. I don't know the internals of the python driver and DBVisualized, but expect both smb, your os and any middleware to cache automatically at least parts of the file, because unless requested so, it is done automatically to speed up filesystem access. Details would depend on os configuration and access details.

I would like to comment on something you didn't ask about, which is the fit of your architecture:

You need to access a database over the network
You need to have certain level of concurrency
You need to control access, because policy reasons (and probably some of those policy reasons could be backups)
You have expectations about performance
You need to control in a more fine-grained way what is on memory and what is on disk
Your database has grown beyond a trivial size

(and those are only the ones that are perceived from your question)

SQLite is a great piece of software, but please consider if it is the right technology for you, as this is almost a paradigmatic case of when not to use it. Sure, you can create a loop device and copy it in memory, and you may get some improvements, but you could also migrate to a distributed system with an integrated cache system (like any other multi-user relational database) and let the technology solve all this issues for you.

Accessing sqlite using a network share is really a hack, and network filesystems are known to have caused data loss due to its limitations towards write consistency.


Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays?

Postgres arrays are 1-based by default. And in typical applications it's best to stick with the default. But the syntax allows to start with any integer number. The documentation:

  Subscripted assignment allows creation of arrays that do not use
  one-based subscripts. For example one might assign to myarray[-2:7] to
  create an array with subscript values from -2 to 7.

Examples:

SELECT ('[0:2]={1,2,3}'::int[])[0];    --> 1
SELECT ('[-7:-5]={1,2,3}'::int[])[-6]; --> 2

You can query any index of an array, if it does not exist you get NULL.

Related:

Normalize array subscripts for 1-dimensional array so they start with 1


Concurrent update queries locking rows when `where` clause doesn't match

A lesson to learn about indexing.  INDEX(a,b,c) is not optimal for WHERE a=1 AND c=2.  This is because only the leftmost column(s) of an index will be used; there cannot be any skipping over columns (b, in this example).

Back to your question.

INDEX(slack_channel_id, message_id)

is needed, and

INDEX(`slack_id`,`slack_channel_id `,`message_id`)

is not useful at all.  slack_id is in the way.

KEY `slack_message_message_id_foreign` (`message_id`),

will be partially useful -- namely for narrowing the search down to those rows with the desired message_id.

A Cookbook on building optimal indexes.