postgresql 12.3 convert existing DB to use case-insensitive collation?

First, a warning: there used to be problems with collations on Alpine Linux, so consider using something else.
The only way to change a a database to use a case insensitive collation throughout is to create such a collation and then use
ALTER TABLE ... ALTER ... TYPE text COLLATE your_coll;

for all string columns in the database.
ICU collations cannot (yet) be used as the default collation for a database.


Experience using ScaleArc in test or production?

HA HA HA HA What a coincidence. I was asked to evaluate it myself.

Here is what has been done so far...

My supervisor installed it
I created two EC2 instances
I have created Master/Slave status between them
I create the Cluster on ScaleArc EC2

I submitted the Master EC2 as a ScaleArc Master
I submitted the Slave EC2 as a ScaleArc Slave

I adjusted the Security Options for port 3306 on the Cluster

Here is what the rudimentary test I performed: I decided to connect to MySQL, not using the Master EC2, and not using the Slave EC2, but using only the ScaleArc EC2. Here is a script to wrote up to test connectivity:

[redwards@lw-lts-155 ec2]$ cat master_select
EC2_DNS=<ScaleArc-EC2-DNS Hidden>
MYSQL_USER=<<Username Hidden>
MYSQL_PASS=<Password Hidden>
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql -h${EC2_DNS} ${MYSQL_CONN} -ANe"show variables like 'hostname'"

Here is the result:

[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-205-21-233 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-205-21-233 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-205-21-233 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+

Hey, not bad. Slave was reached 7 out of 10 times.

At the very least, that is what one would want: Using just one address, connect to MySQL and have some roving intelligence decide which DB Server to connect to. This has been the pipe dream of all avid prognosticators of MySQL Proxy:

Dec 17, 2012 : MySQL proxy to replication configuration
Apr 19, 2012 : What's the latest on MySQL Proxy?
Jan 24, 2012 : Using Replication for Scale-Out

Whether they implemented MySQL Proxy using LUA or scripted it in some proprietary way, the bottom line is this : ScaleArc has achieved this and has done the heavy lifting for us all (at least in the AWS Universe).

I would say : Go ahead and play with it as follows:

Setup one Master and three Slaves
Register the Master and Slaves in ScaleArc
Write up some benchmark against the Cluster
Use the charts provided and see if it intelligently does the load balancing

Sorry, I am no expert with ScaleArc and this not a full answer for you. Test drive it and kick the tires some.

UPDATE 2013-01-14 16:32 EDT

I downloaded the sakila database and loaded it into the Cluster.

wget http://downloads.mysql.com/docs/sakila-db.tar.gz

Then, I ran this query

SELECT actor_id FROM sakila.actor
ORDER BY MD5(CONCAT(last_name,first_name))

and stored the results in actor_id.txt

Next, I constructed the following script to launch mysql many, many times, in testbeds..

EC2_DNS=<ScaleArc-EC2-DNS Hidden>
MYSQL_USER=<Username Hidden>
MYSQL_PASS=<Password Hidden>
MYSQL_CONN="-h${EC2_DNS} -u${MYSQL_USER} -p${MYSQL_PASS} -Dsakila"
NUMBER_OF_TESTBEDS=${1}
PARALLEL_TESTS=${2}
if [ "${1}" == "" ] ; then NUMBER_OF_TESTBEDS=1 ; fi
if [ "${2}" == "" ] ; then PARALLEL_TESTS=1     ; fi
ACTOR_LIST=""
for ACTOR_ID in `cat actor_id.txt`
do
        ACTOR_LIST="${ACTOR_LIST} ${ACTOR_ID}"
done
SQLPREFIX="SELECT F.film_id,F.title FROM actor A"
SQLPREFIX="${SQLPREFIX} INNER JOIN film_actor FA USING (actor_id)"
SQLPREFIX="${SQLPREFIX} INNER JOIN film F USING (film_id) WHERE A.actor_id="
for (( X = 1 ; X <= NUMBER_OF_TESTBEDS ; X++ ))
do
        echo -n "Launching TestBed #${X} ("
        for (( Y = 1 ; Y <= PARALLEL_TESTS ; Y++ ))
        do
                echo -n " ${Y}"
                for ACTOR_ID in `echo "${ACTOR_LIST}"`
                do
                        SQLSTMT="${SQLPREFIX}${ACTOR_ID}"
                        mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" /dev/null 2>/dev/null &
                done
        done
        echo -n ")"
        wait
        echo
done

Hope This Helps !!!


Best approach to delete large table with foreign key constraints

Try this:

Insert 3 month data in new table with similar structure:

declare @CurDate datetime
select *
into NewTable
from ExistingTable
where datecol >= @CurDate and datecol <= dateadd(month,3,@CurDate)

Rename old table to anything unique or drop table as per requirement
Rename new table to existintg table
Insert into NewChildTable with similar structure which match New parent table of Step 3
Rename or drop existing child table
Rename new child table to existing child table


Don't use \q. Use CTRL + D. This sends End-of-file (EOF). It works on all interactive terminals (at least the ones built with readline, afaik)

  readline returns the text of the line read. A blank line returns the empty string. If EOF is encountered while reading a line, and the line is empty, NULL is returned. If an EOF is read with a non-empty line, it is treated as a newline.

Most of the interactive terminals and REPL utilities you use, use some variation of readline or libedit. They all run in a loop that process lines. When you type CTRL+D your terminal inserts the EOF signal, the loops get a null pointer, and the program reads that by convention as a desire to quit.

You can't make exit nor quit an alias in psql, because psql doesn't have aliases like bash; but, both of your other utilities that use exit and quit should work with CTRL+D. It's a good muscle memory to develop.

Two minor footnotes...

If you're on DOS or the newer graphical version, use CTRL+Z to send EOF. That's also the MS Word hotkey for Undo.
I'm not actually describing what happens in UNIX either. I'm keeping it simple. CTRL+D really sends End-of-Transmission (EOT) which causes the terminal to send EOF.


Weird planning time data dependency in Postgres

The reason is an optimization in PostgreSQL, weird as it sounds.

When estimating result counts ow WHERE conditions like the one in your query, PostgreSQL uses a histogram, which is an approximation of the distribution of the values in the column.

Now these histograms are only re-calculated when 10% or more of the table has changed. With data like time-series, that can lead to a considerable skew, because the maximum (or minimum) of the values keeps increasing (or decreasing). So even if only a small percentage of the data change, the minimum or maximum can become inaccurate rather quickly.

To improve estimates, PostgreSQL commit 40608e7f949 has added a feature: when the cut-off value is in or beyond the last histogram bucket (as it is in your case), and if there is an index on the column, the optimizer will scan the index for the actual minimum or maximum and use that instead. This improves the quality of the estimate considerably.

However, there is a drawback. If the extreme values of the index all belong to deleted (“dead”) table rows, the index scan has to scan way more entries than it bargained for the get the actual minimum or maximum. This has been improved with commit 3ca930fc39c in version 11, which allows such an index scan to “kill” those index tuples, so that the next index scan can quickly ignore them.

I conclude that you must use a version older than v11.

There are two remedies:

Upgrade to v11 or better.
Set autovacuum_vacuum_scale_factor low (0.01 or lower) for that table, so that it is processed by autovacuum more often. VACUUM gets rid of those dead index entries, which will make query planning fast again.


Why isn't oracle using an index for distinct query ?

The reason for this behaviour is that rows where LD is NULL cannot be found in the index. Therefore Oracle has to scan the full table. If the table is created with LD as a NOT NULL column then the optimizer uses this information and does an INDEX FAST FULL SCAN. If you add a "CHECK(LD is not null)" constraint to the table that has not NOT NULL defined for the column LD then the optimizer does not use the information provided by the constraint and makes a full table scan again, even if you gave him a hint. Jonathan Lewis wrote about this behaviour.

The following scripts demonstrate this behaviour for Oracle 11.2.0.3.0

*create_table.sql* inserts data into the table and creates index and statistics

set autotrace off
drop table objects
/
create table objects(
  object_id number,
  owner varchar2(30),
  object_name varchar2(128),
  object_type varchar2(19)
)
/

insert into  objects(
  object_id,
  owner,
  object_name,
  object_type
)
select
  object_id,
  owner,
  object_name,
  object_type
from dba_objects
/

create index idx_object_id on objects(object_id);

exec dbms_stats.gather_table_stats(user,'objects', cascade=>true)

Now run the following script:

spool output
set feedback off
set linesize 300
set trimout on
set trimspool on

@create_table
set autotrace traceonly explain

prompt
prompt 1. plan for query with no constraints:
select distinct object_id
from objects;
rem ---------------------------------------------------

@create_table
alter table objects add constraint nn_object_id check(object_id is not null) validate;
set autotrace traceonly explain

prompt
prompt 2. plan for query with check constraint
select distinct object_id
from objects;
rem ---------------------------------------------------

@create_table
alter table objects modify object_id not null;
set autotrace traceonly explain

prompt
prompt 3.plan for query with NOT NULL column
select distinct object_id
from objects;
rem ---------------------------------------------------

@create_table
create bitmap index bidx_object_type on objects(object_type)
/
set autotrace traceonly explain

prompt
prompt 4.plan for query with bitmap index
select distinct object_type
from objects;
rem ---------------------------------------------------

spool off

This gives the following output

1. plan for query with no constraints:

Execution Plan
----------------------------------------------------------
Plan hash value: 4077265613

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         | 59063 |   288K|   139   (3)| 00:00:02 |
|   1 |  HASH UNIQUE       |         | 59063 |   288K|   139   (3)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| OBJECTS | 59063 |   288K|   136   (0)| 00:00:02 |
------------------------------------------------------------------------------

2. plan for query with check constraint

Execution Plan
----------------------------------------------------------
Plan hash value: 4077265613

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         | 59063 |   288K|   139   (3)| 00:00:02 |
|   1 |  HASH UNIQUE       |         | 59063 |   288K|   139   (3)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| OBJECTS | 59063 |   288K|   136   (0)| 00:00:02 |
------------------------------------------------------------------------------

3.plan for query with NOT NULL column

Execution Plan
----------------------------------------------------------
Plan hash value: 4172758181

---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               | 59063 |   288K|    40   (8)| 00:00:01 |
|   1 |  HASH UNIQUE          |               | 59063 |   288K|    40   (8)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 59063 |   288K|    37   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

4.plan for query with bitmap index

Execution Plan
----------------------------------------------------------
Plan hash value: 2970019208

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |    43 |   387 |     6  (34)| 00:00:01 |
|   1 |  HASH UNIQUE                 |                  |    43 |   387 |     6  (34)| 00:00:01 |
|   2 |   BITMAP INDEX FAST FULL SCAN| BIDX_OBJECT_TYPE | 59063 |   519K|     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Summary

If there is a normal B*-tree index on the column NULL values are possible in the column, then the optimizer cannot rely only on the information of the index to do the 'select distinc' and makes a TABLE ACCESS FULL.

If there is a normal B*-tree index  and a NOT-NULL check constraint on the column the optimizer also does not rely on the information of the index and makes a TABLE ACCESS FULL.

If there is a normal B*-tree index  and the column is defined NOT NULL then the optiomizer relies on the information of the index and does a INDEX FAS FULL SCAN.

If there is a bitmap index on the column then the optimzer knows that all information is in the index and does a BITMAP INDEX FAST FULL SCAN


Based on:

WHERE [Entity_Identifier] = @EntityIdentifier
AND [Status] = 'In_Progress' AND [Process_Id] = @ProcessId
ORDER BY [Message_Sequence] ASC

...the best thing is to have an index on

([Entity_Identifier], [Process_Id], [Message_Sequence]) WHERE [Status] = 'In_Progress'

As Status is filtered with a constant, a WHERE clause is going to be useful for this, so that the index only holds the "In_Progress" rows.

But you need to find the rows that match the Entity and Process, and to have those rows in Message_Sequence order (to save getting all the Message_Sequence values and then sorting them when the query runs).

The composite index like this will let the database engine seek into the appropriate Entity and Process, knowing that the first row it finds is the smallest Message_Sequence.

So the advice about indexing on Message_Sequence is kinda right, so long as you're only indexing on Message_Sequence within an index that's on Entity and Process, because those columns are more important.

If you only had an index on Message_Sequence, you'd be searching through the index for the first one that matched Entity and Process, which is only good for non-equality predicates (oh - I'm talking about this at the PASS Summit in Seattle this October).


Multi-row insert vs multiple single row inserts

I think @a_horse_with_no_name is right about that the index is updated once per statement, because if the statement has not completed its execution the data will not be visible since it is in transaction. And the definition of a statement includes having multiple values

And accoriding to the docs here index creation/update works more efficient with batches than single statements.

There is also a nice article from Peter Manis about the performance of various Insert methods which I can recommend.

Another thing to keep in mind is the FILLFACTOR of the index as it does have an impact on performance as Fabien Coelho describes here.


Basically, you have to install SP1 and then CU6 (as mentioned in my comment)

10.50.2811 ==> 2679367 Cumulative update package 6 (CU6) for SQL Server 2008 R2 Service Pack 1
10.50.1765 ==> 2489376 Cumulative update package 6 (CU6) for SQL Server 2008 R2 note this is for RTM


SSIS: To run the package outside of SQL Server Data Tools you must install expression task of integration services or higher

When you run the package from msdb it executes on the machine that you are running it from.

(Unlike packages stored in the 2012+ SSISDB catalog where executing them spawns an execution process on the Server itself).

You must have installed SQL Server Integration Services onto your own machine from the SQL Server installation media, whereas your colleague hasn't. If Integration Services isn't installed you are restricted to running packages in SSDT. Executing packages that run locally with dtexecui will fail.


Does a zero value in innodb_index_stats.rows_per_key mean the index is over-specified?

Comparing these:

INDEX(a,b,c,d,e)
INDEX(a,b,c,d)

Each can handle the same queries (assuming that e is not in the query).
If exactly those 5 columns exist in the query, then the longer one has an advantage of being "covering".  This might give you a 2x speedup.
Yes the longer one takes more disk space.  This impacts the speed of it only slightly.  (Obviously, the impact depends on the size of e.)
There is no hard-and-fast rule saying you should stop with 4 versus have all 5.
My Rule of Thumb says "don't go past 5".


How to update a (very) large table without locking in MySQL

You can delete or update by one row in loop statement. In this case there will be no blocking but it will work a little slower. Use limit 1 to do this in procedure:

  PROCEDURE myProcedure()
    BEGIN
    DECLARE c int; -- to calculated affected rows
    set c = 0;

      ml:LOOP
          UPDATE player_targets t
          INNER JOIN player_uuids u1
           ON u1.username = t.player
          INNER JOIN player_uuids u2
           ON u2.username = t.target
          SET
           t.player_id = u1.id,
           t.target_id = u2.id
          WHERE
           t.player_id IS NULL
           OR t.player_id IS NULL
           LIMIT 1;

          -- check if the loop has completed
          IF  ROW_COUNT() = 0 THEN
              LEAVE ml;
          END IF;

          set c = c + 1;

          IF c MOD  100  = 0 THEN
              SELECT CONCAT(c, ' row(s) updated');
          END IF;
      END LOOP;

      SELECT CONCAT(c, ' row(s) updated; The statement has completed');
    END

And call procedure:

Call myProcedure();


I cannot figure out what exactly is the problem, but I suggest that you add something like this after the update of table_a:

IF NOT FOUND THEN
   RAISE EXCEPTION 'row % in table_a not found!', NEW.recida;
END IF;

Then at least you can keep the inconsistency from happening.

If the trigger fails, all the effects of the statement and the trigger will be rolled back, because they are always in a single transaction.


Changing column type - view doesn't change

Dependent objects are invalidated when their parent changes:

select status from user_objects where object_name = 'TEST_VW';
-------
INVALID

Invalid objects are automatically validated when they are next used:

select * from test_vw;

SELECT table_name, column_name, data_type, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'TEST_VW';

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ----------- --------- -------------- ----------
TEST_VW    AMOUNT      NUMBER                19          2

Alternatively, you can recompile the object yourself:

alter view test_vw compile;

It is worth noting that no data or precision is lost after modifying the table, but before re-validation:

SELECT table_name, column_name, data_type, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'TEST_VW';

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ----------- --------- -------------- ----------
TEST_VW    AMOUNT      NUMBER                10          2

insert into test (amount) values (123456789012345);

select status from user_objects where object_name = 'TEST_VW';
-------
INVALID

select * from test_vw;
---------------
123456789012345

select status from user_objects where object_name = 'TEST_VW';
-------
VALID


From here, you can do something like this

mysql> CREATE TABLE autoinc (id MEDIUMINT NOT NULL AUTO_INCREMENT, sample INT, PRIMARY KEY(id));
Query OK, 0 rows affected (0.34 sec)

mysql> INSERT INTO autoinc VALUES(NULL, 5);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.01 sec)

mysql>

[EDIT - in response to OP's comments]

"To start with an AUTO_INCREMENT value other than 1, set that value with

CREATE TABLE or ALTER TABLE, like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;"

Also, this might help


mysql query performance... got to be a better way

this is josh.  Try the following

SELECT  b.minID, a.AssociatedObjectID
FROM    tableFromAbove a
        LEFT JOIN
        (
            SELECT AssociatedObjectID, AssignmentSubType, AssignmentValue, OwnerUsergroupID, MIN(AssociatedObjectID) minID
            FROM tableFromAbove
            GROUP BY AssignmentSubType, AssignmentValue, OwnerUsergroupID
        ) b
            ON a.AssignmentSubType = b.AssignmentSubType AND a.AssignmentValue = b.AssignmentValue AND a.OwnerUsergroupID = b.OwnerUsergroupID
            WHERE minID <> a.`AssociatedObjectID`;


$addToset do not work in update if record not exists

I resolved the issue. I made an error in the update statement. The correct version is:

db.TestColl.update({
    "tags" : "silk" ,
    "host" : "host.com"
  }, {
    "$set" : { "cat" : "MOVIE", "publishTime" : 1370427540000 },
    "$addToSet" : {
      "articleId" : { "$each" : [ "20442242" ]}} ,
      "$inc" : { "count" : 1}
  },
  true, false)


The short answer is no, it cannot be turned off.
GO is a 'batch terminator'.  When SSMS executes 'statements' they are 'batched' together.  This could be a single query, an entire script with no 'GO's or could be the code between 'GO's.
That is why you are getting the errors when you are trying to add a column and update it in one hit.  They need to execute as separate batches.
It is also possible that, say you had 3 statements separated into batches that a single batch in a script could fail but not the entire script.
Query1 -- success
GO
Query2 -- failed
GO
Query3 -- success
GO

GO is not a Transact SQL Statement, it is a command recognised by SSMS and sqlcmd.  See here on MSDN for more details.
You will also find that variables do not persist past a GO statement and must be re-declared or your statement/batch sequence amended.
Incidentally, you don't have to use 'GO' as a batch terminator, you could use anything.  It can be amended from the Tools-->options menu, under query execution.  Be aware though, changing it and using something different will only work for the copy of SSMS you are using.  If you are working in a team, and every member of the team has not changed to use 'NewBatch' keyword then the script will fail in their copy of SSMS (as I'm fairly confident it does in sqlcmd too!).


How do I create "groups of users" and set permissions in SQL Server?

What you can do in sql server is to create a group of permission.
They are called Database Roles. You can use pre-build roles or create some new.
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15
About the group of users you can't with the sql authentication but if you use the windows authentication you can relay on local user groups (uased as login in sql server) or active directory user groups (add them as login in sql server)
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-login?view=sql-server-ver15


How can I make this query more efficient?

You could try simplifying and using LATERAL for joining the laps table:

SELECT
  races.*, tmptimers.last_start_time, tmplaps. last_updated_at
FROM
  races
LEFT JOIN
  timers AS tmptimers
    ON  tmptimers.user_id = 1
    AND tmptimers.race_id = races.id
LEFT JOIN LATERAL
  ( SELECT
      updated_at AS last_updated_at
    FROM
      laps
    WHERE
      user_id = 148
      AND updated_at > '2014-06-13'
      AND race_id = races.id
    ORDER BY
      updated_at DESC
    LIMIT 1
 ) AS tmplaps
     ON TRUE
WHERE
  races.account_id = 5
  AND races.id IN (29, 30, ..., 74)
ORDER BY
  last_updated_at DESC ;

I'm not sure what index will be best for the laps table though. Perhaps an index on (user_id, race_id, updated_at). I tested a similar query on SQL-Fiddle and it used the index. The plan showed:

  Index Only Scan Backward using "index_name" on laps


What are the arguments in favor of using ELT process over ETL?

lots of Discussions about ETL vs ELT out there.

The main difference between ETL vs ELT is where the Processing happens
ETL processing of data happens in the ETL tool (usually record-at-a-time and in memory)
ELT processing of data happens in the database engine

Data is same and end results of data can be achieved in both methods.

it very much depends on you and your environment
If you have a strong Database engine and good hardware and you can do heavy processing on it, ELT is good for you,
If you have a busy datawarehouse engine and you need to free it from processing go for ETL.

notice that having an ETL tool gives you both the options, like ETL(T), you can do the Transformation in the ETL tool and you can do transformation in the Database engine as well

but ELT you only have the option of transformation in the database engine, but you should know that Databases are better at set based operations than record-at-a-time ETL tools.

similar question asked on SO but supporting ETL and also
a nice Article comparing ETL vs ELT, but favoring ELT


This example does not contain a partial dependency to me.

A partial dependency would be if a table column functionally depends on part of the table's primary key, like in this denormalized table:

CREATE TABLE student_activity (
   studentid bigint NOT NULL,
   activity character varying(30) NOT NULL,
   fee numeric(10,2) NOT NULL,
   PRIMARY KEY (studentid, activity)
);

where fee depends on activity.

The solution would be to split this table in two parts like in your question.


Continuously backup/restore selected tables

You have few options to consider but first I hope you're not troubling your production server but may be a slave of it to dump out things? (things may get bad there.)

If you are only needing data that's changed since last backup, and your tables does have modified date as one of the columns (consider to add if you can to ease the process?) you can:

mysqdump --where=' modified_date_column>="Last_bkup_dttime"' XYZ tabll1 table2 table3 > tables.sql

How about setting a slave replicating only tables of choice?
Did you know about mydumper/loader? That can take faster dumps than mysqldump.
Are you backing up your data daily? If yes then why don't you setup an automatic nigtly restore process that restores your dev with latest backup?
You may also choose to play the binary logs from production and discard other tables:

Generate sqls from each of the binlogs:
mysqlbinlog --db=dbofyourchoice binlog.000xxx > binlog.000xxx.sql

Play that all those sqls with --force to ignore errors.
mysql dbofyourchoice --force < binlog.000xxx.sql

and ofcourse partial backups from xtrabackup is there...


How to restore a database from SQL Server 2000 to SQL Server 2012

SQL Server 2012 no longer supports restoring SQL Server 2000 databases. You need to take an intermediate step:

backup your SQL Server 2000 database
restore it to SQL Server 2005 / 2008 / 2008 R2
change the compatibility level to at least "90" (SQL Server 2005)
backup again from that intermediate server
then restore that second backup on SQL Server 2012

Or depending on size and complexity of your SQL Server 2000 database, it might be easier to just script out the structure and data into SQL scripts and then run those on SQL Server 2012.


Best practice for multiple 0,1 to many relationships

This will really depend on many factors, beginning with your choice of RDBMS, and followed by your application language and framework. The choice will be very different if you use some form of RAILS and mysql (where database design is driven by class design in your application), than PHP and PostgreSQL (where you can choose to use create a CONTACT composite type, and keep contacts as an array of contacts in a column on each of your databases, without a separate table for it).


Why is DistinctSort followed by TopNSort in my query plan?

I can reproduce the plan that you describe on SQL Server 2012 (on prem) by running the DDL in your question and then fiddling the stats so SQL Server thinks that the table is much larger than reality.

UPDATE STATISTICS [dbo].[JobItems] WITH ROWCOUNT = 10000000, pagecount = 10000000

And then running the query with OPTION (MAXDOP 1, CONCAT UNION, ORDER GROUP).

This is an index union plan. The concatenation operator implements UNION ALL. The Distinct Sort changes the semantics to a UNION operation to prevent the same row being returned multiple times. (In the event the table had no index key to act as a unique row identifier the physical rid would have been used here to avoid incorrectly de-duping different rows that happen to have the same column values)

An example of where this might be needed is in the query below. (note the two parameters are set to the same value so an index union plan there would seek the same rows twice)

DECLARE @ItemState1   INT = 5
        , @ItemState2 INT = 5

SELECT ItemId
FROM   JobItems
WHERE  ItemState = @ItemState1
        OR ( ( ItemState = @ItemState2 )
             AND ( DATEDIFF(SECOND, ItemRefreshTime, GETUTCDATE()) > 14 ) )

The Top N Sort then re-sorts the data to implement the TOP 1.

In your case the Distinct Sort is logically not required for multiple reasons. The ItemState = 5 and ItemState = 11 branches are mutually exclusive (and this could be determined at compile time) and additionally the TOP 1 ... ORDER BY ItemPriority ASC, ItemCreationTime ASC semantics wouldn't be affected even if there were erroneous duplicates.

An alternative way of writing the query (that gives a better plan using the indexes to avoid any sorts at all) is

SELECT TOP(1) ItemId
FROM   (SELECT ItemId,
               ItemPriority,
               ItemCreationTime
        FROM   JobItems
        WHERE  ItemState = 5
        UNION ALL
        SELECT ItemId,
               ItemPriority,
               ItemCreationTime
        FROM   JobItems
        WHERE  ( ( ItemState = 11 )
                 AND ( DATEDIFF(SECOND, ItemRefreshTime, GETUTCDATE()) > 14 ) )) T
ORDER  BY ItemPriority ASC,
          ItemCreationTime ASC

You could consider adding ItemRefreshTime as an included column to the index to avoid the key lookup if in practice quite a few may be required before finding a single row satisfying the residual predicate.


Can I do VACUUM FULL to pg_largeobject table?

You can run that, no problem:

VACUUM FULL ANALYZE pg_largeobject;

Might even remove some dead rows. Details:

VACUUM returning disk space to operating system

But it's probably not going to solve your actual problem.

When using the large object facility of Postgres, large objects ("blob": binary large object) themselves are broken up in chuncks of binary data stored in the system table pg_largeobject. The PK consists of two columns (loid, pageno), loid is the oid that is used to refer to the blob in user table(s). The same blob can be referenced by OID many times.

Deleting rows in user tables does not remove the blob. For one, the same blob might be referenced more than one times. It is your responsibility to keep track and actually delete "unlinked" blobs yourself. One way would be to use lo_unlink():

SELECT lo_unlink(173454);  -- deletes large object with OID 173454

Since you already deleted rows with the oid reference, you need to be a bit more creative to identify unlinked blobs. Assuming you do not reference blobs from any other places, you can use this query to fix:

SELECT lo_unlink(l.loid)
FROM   pg_largeobject l
GROUP  BY loid
HAVING (NOT EXISTS (SELECT 1 FROM table1 t WHERE t.oid = l.loid))
AND    (NOT EXISTS (SELECT 1 FROM table2 t WHERE t.oid = l.loid));

You need to be superuser to access pg_largeobject directly. Assuming the column name in table1 and table2 is oid. Simpler query based on pg_largeobject_metadata in Postgres 9.3 or later (like @Daniel commented):

SELECT lo_unlink(l.oid)
FROM   pg_largeobject_metadata l
WHERE (NOT EXISTS (SELECT 1 FROM table1 WHERE t.oid = l.oid))
AND   (NOT EXISTS (SELECT 1 FROM table2 WHERE t.oid = l.oid));

pg_largeobject_metadata is publicly readable. But I don't see the OID of the blob in the system table in versions before pg 9.3 (incl. pg 9.1) - at least not in the manual, I don't have an old version to test right now. So you probably must use my first query.

Compare before and after:

SELECT count(*) FROM pg_largeobject;
SELECT pg_size_pretty(pg_table_size('pg_largeobject'));

You might run VACUUM FULL now, and test again:

VACUUM FULL ANALYZE pg_largeobject;

You'll be interested in the additional module lo, that's available for Postgres 9.1. The manual has an accurate description for your problem:

  ... a table entry can reference a large object by OID, but there can be
  multiple table entries referencing the same large object OID, so the
  system doesn't delete the large object just because you change or
  remove one such entry.

Bold emphasis mine. The module offers a solution, too:

  The lo module allows fixing this by attaching a trigger to tables that
  contain LO reference columns. The trigger essentially just does a
  lo_unlink whenever you delete or modify a value referencing a large object.

For use cases where each blob is referenced exactly once in your whole DB.

And obviously also (like @Daniel mentioned) vacuumlo:

  vacuumlo is a simple utility program that will remove any "orphaned"
  large objects from a PostgreSQL database.


How to store short stories for access to individual sentences?

The best solution heavily depends on the definition of "best". You may be looking for simplicity, reliability, performance or disk space optimization or other things.
Specifications

infinite amount of stories, and about ~40 sentences per story

Few sentences, possibly many stories.

user can input a word or phrase

We need phrase search. (Maybe across sentence boundaries?)
Also assuming:

"Sentences" are substrings simply separated by dot and space('. ' ).
Resources (RAM, disk space) may be a limiting factor.
Retrieving the whole body and searching for individual sentences are equally frequent tasks.

Solution
Postgres 9.6 introduced exactly what you need. The release notes:

Full-text search can now search for phrases (multiple adjacent words)

The latest Postgres release and a text search index will go a long way for performance in either case.
You already considered to:

slap the body of the story onto a 4th column in the Story model

But an array adds 24 bytes + 1 or more bytes per sentence and makes indexing much more complicated. (As well as pretty much all other operations.) I would exclude the array solution.

Indexing an array for full text search

An additional storyline table with one row per sentence makes indexing simple again, but adds ~ 40 bytes per sentence. Finding and retrieving sentences is simple. But all write operations are more complicated.
About storage size in Postgres:

Configuring PostgreSQL for read performance

Consider a single table with body as plain text column. Splitting the string into sentences on the fly is fast (with a simple definition of "sentence"). Smaller total storage size may be more important for performance with big tables (fewer pages to fetch). And we only need to index one column. In short:

dead simple storage and handling
minimum disk space
still a hot contender for best performance

Table:
CREATE TABLE story (
  story_id  serial PRIMARY KEY
, author_id int REFERENCES author  -- NOT NULL?
, title text                       -- NOT NULL?
, body  text                       -- NOT NULL?
);

FTS index, assuming English language:
CREATE INDEX story_body_english_fts_idx ON story USING GIN (to_tsvector('english', body));

Query to return whole story:
SELECT *
FROM   story
WHERE  to_tsvector('english', body) @@ phraseto_tsquery('english', 'Lewis Carroll');

Query to return sentences:
SELECT story_id, sentence_nr, sentence
FROM   story, unnest(string_to_array(body, '. ')) WITH ORDINALITY x(sentence, sentence_nr)
WHERE  to_tsvector('english', body)     @@ phraseto_tsquery('english', 'Lewis Carroll')
AND    to_tsvector('english', sentence) @@ phraseto_tsquery('english', 'Lewis Carroll');

The query demonstrates a phrase example. But it works for single words as well.
The repeated WHERE condition is logically redundant but necessary to use the index on body. This retrieves only stories with qualifying sentences, then the recheck on unnested sentences (repeating the same condition) is cheap.
About unnest(string_to_array(body, '. ')) WITH ORDINALITY:

How to preserve the original order of elements in an unnested array?
What is the difference between LATERAL and a subquery in PostgreSQL?

Note: The check on the whole text body can find phrases across sentence boundaries - unlike searching individual sentences. But the recheck on sentence excludes such matches. You can fine-tune one or the other way ...
There are many more options with FTS: prefix matching, other languages, no language (no stemming or thesaurus), phrases with words in between ...

Get partial match from GIN indexed TSVECTOR column


Selecting default value query returns blank

I think you're going about this all wrong.

You want to wrap IFNULL around the actual field.

SELECT IFNULL(as_title, 'lorem ipsum') FROM all_scheduled_messages WHERE id = 1 LIMIT 1;

However, if you're returning a blank, rather than a NULL from your query, IFNULL will not work. You'll need to NULL the value first:

SELECT IFNULL(NULLIF(as_title, ''), 'lorem ipsum') FROM all_scheduled_messages WHERE id = 1 LIMIT 1;


When reading the configuration file, the mysql command line client only reads configuration directives found in the [client] and [mysql] sections, while mysqldump only uses configuration directives found in the [client] and [mysqldump] sections.

If your socket directive in the config file is in the [mysql] section but not the [mysqldump] section then you should move it to the [client] section, and this should solve your problem.

So where does /var/run/mysqld/mysqld.sock come from?  I remember very distinctly, once upon a time, beating my head against the wall repeatedly over that one.

It turns out, Ubuntu compiles their MySQL components from source, and when they do that, they compile them to use /var/run/mysqld/mysqld.sock unless the configuration file specifies otherwise or the socket is overridden on the command line... so if your socket directive isn't in [client] that would be one explanation of this behavior.


Which is more efficient, trigger or PHP code?

I wonder where "at least 4 times faster" came from?  There are too many variables at play to even come up with numbers like that, and in any event, the performance difference (however slight) should be in the other direction.

The presence of a trigger on a table does come with a performance penalty, but if your trigger is intelligently-written, the word "negligible" should apply.  A well-behaved trigger does only what it needs to do and then gets out of the way, and in the case of triggers that test multiple conditions, the tests are nested so that as few logical comparisons are done as possible for each run of the trigger.

Some developers seem to resist triggers on the principle that they're "black magic" -- "Hey, something changed in the database, and the application didn't change it. That's bad."  Granted, the presence of triggers is something developers and DBAs alike have to keep in mind when troubleshooting, and that's especially true if they are uncommon in your environment.  If the structure of one of the tables modified by the triggers were to change -- say, a renamed column -- then updates to the discharge flag suddenly start throwing errors about columns that weren't even referenced in the update query, someone who doesn't have the possibility of "there might be triggers involved" at least in the back of their minds may be completely flummoxed.  I've been on that side of it, too, and I was the one who originally wrote -- then forgot about-- the trigger in question.  That's one of those lessons you learn the hard way, hopefully just once.

The issue you bring up is, I would say, as much as matter of principle as performance: as one of the answers to this question points out, the database should be in charge of looking after the database... not the application.  The application shouldn't be in charge of referential integrity, so why should it be in charge of operations which -- like this one, from what I can tell -- should be atomic?

If, by definition, when the discharge flag is set, the EMR data should thenceforth be in the history tables, then I would argue on principle that the setting of the flag should be what puts the data there.  That requires either a trigger... or a call to a stored stored procedure that both sets the discharge flag and migrates the records before returning.

I'm imagining that no matter how you end up migrating the records, the actual queries executed to do the migrating would be the same... so in truth it's hard to imagine the difference in performance being substantial, since the execution time for the queries that move the records is not going to be different, whether the queries are run from a trigger, run from a stored procedure, or run separately from the application... so, almost comically, the most significant factor I'd expect to find would likely be small: the round trip time between the system running PHP and the system running MySQL, multiplied by the number of separate queries that need to run, will likely be the determining factor in just how much faster a trigger (or stored procedure)-based implementation would be... and on a LAN with sub-millisecond RTT... not much.


Could I control which protocol will ODBC uses to connect MSSQL on "integrated windows authentication"

SQL Server will always prefer Kerberos over NTLM, however, NTLM is used as a fallback by SQL Server to ensure connectivity. You cannot disable NTLM authentication at the SQL level, this can only be done at the domain level, however, if you have Kebreros authentication properly configured then SQL Server will use that over NTLM.
See this article for information on properly configuring Kerberos in SQL Server:
https://www.sqlservercentral.com/articles/configuring-kerberos-authentication


Selecting specific table for each subscriber when using merge replication in SQL Server with multiple subscribers

yes, that is possible - you create 3 different publications - and in each publication you add only the respective tables you want for each server.

for example - only 2 tables for server1

The other tables will be in other publications, not this one.

Alternatively, within the merge replication you can set up filters and each subscriber would see only a specific partition as you define it.

This example below comes from this article:

Merge SQL Server replication parameterized row filter issues

add tables to merge replication with parametrized row filters.

Follow the steps in the referenced article to configure merge publication and adding tables to the publication

At Add filter step add below filter.

SELECT <published_columns> FROM [dbo].[UserDetails] WHERE [UserOrg] = HOST_NAME()

Once the publication setup is completed, generate the snapshot for merge replication. After generating the snapshot navigate to publication, right click and click on Properties -> Data Partitions

Click on Add and enter the partition value i.e. ORG1 and click ok. As of now I am adding only one partition ORG1. We can add more based on subscribers. For example, if I have a subscriber with partition ORG2 then we can add partition for ORG2 here and generate a partitioned snapshot which will be applied on specific subscriber.

Now click on generate the selected snapshot now and click OK.

Right click on publication in merge replication, launch SQL Server replication monitor and make sure the partitioned snapshot is also completed.

Now add subscription. Please follow steps in the following article to add subscriber: Merge Replication – What gets replicated and what doesn’t

While adding the subscriber specify the HOST_NAME() value of the subscriber, so that the rows with UserOrg value ORG1 only will be replicated to the subscriber.


ok, I solved this by:

select * from
(
    select id,id_operator,denumire,loc_den,str,
           row_number() over (partition by
                     id_operator,loc_den,str order by id_operator) as no
    from rap
    where is_punct_lucru=1
    order by id_operator
)
where no>1


Hi managed to clear it up using:

pg_dump data -f

I am able to connect to the server now.


Ok,
So after trying a lot of things, I found the solution.

The issue was hiding in our use of the JDBC library.

We had a long connection with auto commit set to false:

connection.setAutoCommit(false)

During that time we were doing a lot of small queries and a few queries with a cursor:

statement.setFetchSize(SOME_FETCH_SIZE)

In JDBC you create a connection object, and from that connection you create statements. When you execute the statments you get a result set.

Now, every one of these objects needs to be closed, but if you close statement, the entry set is closed, and if you close the connection all the statements are closed and their result sets.

We were used to short living queries with connections of their own so we never closed statements assuming the connection will handle the things once it is closed.

The problem was now with this long transaction (~24 hours) which never closed the connection. The statements were never closed.
Apparently, the statement object holds resources both on the server that runs the code and on the PostgreSQL database.

My best guess to what resources are left in the DB is the things related to the cursor. The statements that used the cursor were never closed, so the result set they returned never closed as well. This meant the database didn't free the relevant cursor resources in the DB, and since it was over a huge table it took a lot of RAM.

The only thing I don't understand is why PostgreSQL didn't save some of the resources on the DISK (not swap) when it was failing to allocate memory.

Hope it will help some one in the future with similar problem.


Tricking SQL server into thinking it's a different date

The short answer: no. Ask the developers to fix the problem.

The long answer: it is possible to detour system calls. See for example Modifying application behavior with Detours (for Application Compatibility reasons). This allows you to modify the result of a system call for a specific process. In the linked example it is the GetSystemTime call and the author modifies it to return a date in the past. You can do similar and load sqlservr.exe in a detour environment and return the date of your choice.

However, I would not call this method 'easier' than modifying the system clock. And for sure is riskier. Not to mention, I'm not even sure the GETDATE translates to a call to GetSystemTime...

You could also try RunAsDate

  RunAsDate is a small utility that allows you to run a program in the date and time that you specify. This utility doesn't change the current system date and time of your computer, but it only injects the date/time that you specify into the desired application.

Look at your service parameters (eg. sc.exe qc MSSQLSERVER). You must match the startup parameters (what sc.exe reports as BINARY_PATH_NAME) and the startup user (ie. SERVICE_START_NAME). If you don't run as the correct user you simply won't have access to the required files (master, tempdb, logs etc).

See How to: Start an Instance of SQL Server (sqlservr.exe)


You should have an IF ELSE in your for loop:

for($i=0; $i<$fields_num; $i++)

You are assigning $field to the string WATTAGE here:

$field = mysql_fetch_field($result);
echo "{$field->name}	";

before you get to the code

echo "{$field->name}	";
if ($field ="WATTAGE")
{
  CONTINUE;
}

WATTAGE has has already been assigned to $field before you get to if($field = "WATTAGE") code.

Put an IF/ELSE in your for loop and then put the CONTINUE; bit in the IF and the assignment in the ELSE part. That way you'll never get to the $field assignment if the fieldname is WATTAGE.


Update with default values in case clause

You can't.

The relevant bit of the grammar for update is

SET column_name = { expression | default | null }

You can only use the default keyword in place of an expression, not inside an expression. So you would either need to just use the value of the default directly (could be looked up from the system views to be dynamic) or spilt it into two update statements with appropriate mutually exclusive where clauses - one using default and the second using an expression - if you really want to use that keyword.


Anaylzing binlogs may not give you an accurate picture because binlogs contains queries that were completed and are inserted in the binary logs like a FIFO queue.

What you really need to look for is what does the historgram of actively running queries looks like. In other words, you need to catch the processlist in the act of revealing what queries and what performance was like when the unique circumstances came into existence.

I highly recommend using the pt-query-digest but you need to use itt differently. Instaed of have the query digest process the binlog entries, have it process the processlist LIVE !!!

I wrote a past post (Nov 24, 2011) on how to use the pt-query-digest (My post uses mk-query-digest) as a replacement for the slow query log (I have posted the actual script I used plus how to read the query digest output) : MySQL general query log performance effects. My past post was based on a YouTube Video showing how to do it. I just simply emulated myself using mk-query-digest.


How to migrate a database from a local to a remote server?

The two configuration files apply to the server, so both your local and remote server already have those files and their contents is specific to the installation.

Assuming your remote server is already up and running, all you need to do is dump the database you want to deploy to a file and then load the dump file in your remote server. You do not have to do anything with the configuration files.

You can use pg_dump to dump the local database to file, doing something like this:

pg_dump -U postgres -Fc my_db > my_db.db

But check all the options of the pg_dump program.

Loading the database on your remote server is just as easy with pg_restore:

pg_restore -h <ip remote server> -p <port remote server> -U postgres -C my_db.db

On the remote server you need to specify a user who can create databases, if you do not have access to the postgres role.

If you are on some flavour of *nix you can even pipe pg_dump output into pg_restore input and do it all in one go:

pg_dump -U postgres -Fc my_db | pg_restore -h <ip> -p <port> -U postgres -C

Note that pg_dump will ask for a password on the local server (if at all) and pg_restore will for a password on the remote server (if at all).

Both programs have a lot of options. Be sure to read the documentation fully and use options to match your situation before coming back to tell me that it doesn't work.


Quorum question for SQL Cluster (Always On)

At our primary site we are looking at installing three servers and one at the secondary site. These server will then be added to a cluster (and we'll add the DB's in some Availability Groups accross these servers)

With the current configuration there is no way you can keep WSFC running if primary site, which has 3 nodes out of 4 nodes WSFC, goes down due to power outage or some other disaster. With such configuration adding Fileshare witness would be useless because even if you add one there will be 5 voting members and for WSFC to be online there should be at least 3 online and in your case when primary DC goes down 3 voting members will be down bringing WSFC down.

  Does anyone have any experience or a solution for this?

The solution would be to change the architecture, IF you do not want WSFC to come down when primary DC goes down. What you can do is move the 3rd node from primary to secondary DC and configure FC witness in cloud or at some third location which is not secondary DC or Primary DC but accessible to both, so even if primary DC is down there are 3 voting members 2 from secondary DC and one FS witness to keep WSFC up and running.
The other solution is, if you can afford WSFC downtime, let the architecture be as it is and when disaster strikes at primary DC use Forcequorum to bring WSFC online. This seems like bad choice at first but believe me with little practice it works like charm and within 10-15 mins or even less WSFC would be online on DR node and with failover with data loss you could bring database instance online as well. This method is quick with little practice all you need is few commands to be run to bring WSFC and databases online.

Similar readings

Force Start a Windows Server Failover Cluster without a Quorum to bring a SQL Server Failover Clustered Instance Online
Recover WSFC using Forced Quorum for SQL Server AlwaysOn Availability Group


To schedule jobs from the db server we'll need to enable trust authentication in pg_hba.conf for the user running the cron job.
We'll also need to either run UPDATE cron.job SET nodename = '' to make pg_cron connect via a local (unix domain) socket or add host all all 127.0.0.1/32 in pg_hba.conf to allow access to the pg_cron background worker via a local TCP connection.

As a basic sanity check to see if logging is enabled, we run SELECT cron.schedule('* * * * *', 'SELECT 1') which will run SELECT 1 at the start of every minute and should show up in the regular postgres log.


How to block user access to perform database application update?

There are couple of ways that you can restrict access to a database :

Using LOGON Trigger - but only temporarily (enable before upgrade and then disable it). Refer to my answer
here for more details including a script.
You can shutdown IIS on the webservers so that no connections are made using the application. This is called "Applicaiton downtime"
Keep database in single user mode using (Note this will be risky as if there is any other connection to the database then you might
end up waiting or refused connection.)

alter database databasename
set single_user with rollback immediately

You are better off using Option 2 as a safe and planned upgrade during your maintenance window.

EDIT:

Restricted user - only users with dbo rights on database allowed (e.g. db_owner,
dbcreator, sysadmin). This means that multiple users can still be logged into the database, as long as they are DBO.

Single user - only one connection allowed i.e. first come, first served.

Due to the fact that Single user will be first come, first served -- it will be more risky in case of error or somehow your connections gets terminated.

When dealing with Logon Trigger, as @AaronBertrand pointed out, that it will not work for existing sessions, but you can over come that by first killing all the sessions and then enabling the trigger so that all the new incoming connections have to go through the trigger.

I can't think of any other way of restricting the connections to the database.


My guess is that these indexes are very small.

Aaron Bertrand comments here

  Ola's solution is set to ignore indexes with less than 1000 pages (see
  the @PageCountLevel parameter). You can override that so that it cares
  about indexes with fewer than 1000 pages, but why? Wasted effort IMHO.

  I would stop worrying about small tables like this - let Ola's
  solution do its job, and worry about fragmentation when you can
  actually prove it is causing a tangible performance problem for a
  specific index. "Fragmentation is high" is not a problem on its own.

Additonally, Thomas Stringer comments here

  If an index is very small (I believe less than 8 pages) it will use
  mixed extents. Therefore, it'll appear as if there is still
  fragmentation remaining, as the housing extent will contain pages from
  multiple indexes.

  Because of this, and also the fact that in such a small index that
  fragmentation is typically negligable, you really should only be
  rebuilding indexes with a certain page threshold. It is best practices
  to rebuild fragmented indexes that are a minimum of 1000 pages.


"Too many connections" when running MySQLDump

If you convert all your tables to InnoDB, then you can perform your mysqldumps without disturbing any of DB Connections.

Here is a script to mass convert all MyISAM tables into InnoDB.

mysql -uroot -p -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql

Just execute /root/ConvertMyISAMToInnoDB.sql during a full maintenance. This needs to be done only once. Once all your tables are InnoDB, you could probably mysqldump your databases in parallel. I wrote an earlier post back in April 2011 on multiple ways to perform mysqldumps. You need option 2. You will also need to add the --single-transaction option to each mysqldump so that all the mysqldumps are launched from the same point-in-time.

However, you could still experience performance problems anyway because each table that is being mysqldump'd will push old data out of the InnoDB Buffer Pool to push in table data for each table being mysqldump'd.

Operations must slow down because queries being executed must share the InnoDB Buffer Pool with the mysqldumps that are thrashing it.

As for upping the max_connections you can do this without restarting mysql.

Please add this to my.ini

[mysqld]
max_connections=1000

You do not have to restart mysql. Just run this command in the mysql client:

mysql> SET GLOBAL max_connections = 1000;

You may need more RAM on the box to accommodate additional DB Connections as well as a Decent Sized InnoDB Buffer Pool.


Can I activate PITR after the DB has been used

No need to recreate the database.  Set up archive_mode and archive_command, restart the server, create a base backup, and there you go.  (Details vary depending on version.)

It is quite common to "upgrade" from pg_dump to PITR backups as the database grows.


ORA-39002, ORA-39168 on IMPDP, Object path GRANT was not found

This happened because you specified EXCLUDE=GRANT on export as well.

This is expected behavior - according to Oracle.

The workaround is to not specify the same EXCLUDE in expdp and impdp.

See the below (with support access):

DataPump Import (IMPDP) Fails With ORA-39168 (Doc ID 430702.1)

I have just tested this on version 18c and 19c as well, in those newer versions it worked fine without any error.


SQL equivalent to CouchDB for 2 way replication

The way CouchDB's replication mechanism is created and integrated is kind of unique. This has to do with the historical situation, that CouchDB should be able to live in a distributed world from the beginning. That said, "older" databases like MySQL or PostgreSQL do not have these features, because there was never a need for it.

It is very important to understand, that MySQL and PostgreSQL do offer replication but not in the way CouchDB does. There is MySQL Percona and PostgreSQL BRD but one has to go through a more time consuming setup process in comparison to CouchDB where you simply add a replication document with the source and target in the _replicator database.

So as far as I know, it will be difficult to find something comparable to the mechanisms and features you are looking for.


Is it possible to have a deadlock in this situation

Assuming you're working with read committed isolation level (=the default), then the deadlock will happen when a reads the row, then b reads the row, then a tries to update it and then b tries to update it.

In addition to deadlocks you might have another problem if you're dealing with the same rows and the process isn't that fast, since the second process will be stuck until the first one completes the whole transaction (if deadlock doesn't happen).

If the blocking doesn't matter, you can take an exclusive lock on the row when you're selecting the data by specifying lock hints, most likely the correct ones in this case would be UPDLOCK and HOLDLOCK, see https://msdn.microsoft.com/en-us/library/ms187373.aspx


Migrate / Copy Huge data from Oracle to SQL Server

Separate the two tasks.

One of the good ETL rules to follow is to get in and out of the source system as fast as possible. Dumping the Oracle data to flat files should be orders of magnitude faster than inserting direct, as should the subsequent import of those files to SQL Server.

Export from Oracle to flat file. There are several approaches detailed at How Can I Unload Data to a Flat File.
Import to SQL Server. bcp or BULK INSERT should yield the fastest raw loading speed. There is a useful list of bulk loading references in Optimising BCP performance for BLOB data.

I've found BULK INSERT and bcp to be less troublesome (i.e. I create and encounter fewer errors) if you forget CSV, avoid format files, and utilise a non-standard delimiter instead. I've got "~¬" in use on a bcp based ETL job at the moment and so far so good, the data dog I'm fetching from has yet to contain that string.


Get the last row that was left outside of a timeframed SQL query

The simplest (I think so) solution is

  ( SELECT *
    FROM A
    WHERE `timestamp` BETWEEN @from AND @till )
UNION ALL
  ( SELECT *
    FROM A
    WHERE `timestamp` < @from
    ORDER BY `timestamp` DESC LIMIT 1 )
ORDER BY `timestamp` ASC

Alternative:

WITH cte AS ( SELECT `timestamp`, `value`,
                     LEAD (`timestamp`) OVER (ORDER BY `timestamp`) `next`
              FROM A )
SELECT `timestamp`, `value`
FROM cte
WHERE (`timestamp` BETWEEN @from AND @till)
   OR (`next` BETWEEN @from AND @till)
ORDER BY `timestamp` ASC

One more:

WITH cte AS ( SELECT `timestamp`, `value`,
                     CASE WHEN `timestamp` >= @from THEN 1
                          ELSE ROW_NUMBER() OVER (ORDER BY `timestamp DESC`)
                     END grp
              FROM A
              WHERE `timestamp` <= @till )
SELECT `timestamp`, `value`
FROM cte
WHERE grp = 1
ORDER BY `timestamp` ASC

One more else:

WITH cte AS ( SELECT `timestamp`, `value`,
                     COALESCE(LEAD(`timestamp`) OVER (ORDER BY `timestamp DESC`),
                              `timestamp`) grp
              FROM A
              WHERE `timestamp` <= @till )
SELECT `timestamp`, `value`
FROM cte
WHERE grp >= @from
ORDER BY `timestamp` ASC


How to check if several tables exist in database, and how to show it in Mysql

Perhaps this is better solution for you:

SELECT COUNT(*) AS tables_found_count
FROM `information_schema`.`tables`
WHERE `TABLE_SCHEMA` = 'database_name' AND
`TABLE_NAME` IN ('table1', 'table2')

It returns the count of found tables. Please list the tables in brackets and specify your database name instead of database_name.


There is no specific status variable for queries per user in MariaDB. You can only monitor Queries and Questions status variables on your monitoring system.


What configurations do I need to fix the "unable to begin a distributed transaction" error when trying to run a remote procedure?

On the Linked Server properties have you tried setting "Enable Promotion of Distributed Transactions" to False?


I finally determined the lookups have the be setup in Access, they won't automatically be created when attaching a sql server table as a link.


How to Reclaim Data Free in InnoDB Tables?

Everything you have done is correct in order to drastically defragment innodb tablespaces (there are other ways, but exporting, deleting files and importing certainly works).

Trying to optimize the tables (which, by the way, the correct way to do it for innodb is running ALTER TABLE mytable ENGINE=InnoDB- OPTIMIZE table just calls this, and REPAIR table does nothing for InnoDB) just after an in-primary-key-order import is useless. The way InnoDB works is optimising for access and write performance, not for data size. You will always have overhead in disk space (even if free space says 0), as innodb reserves spaces in whole extend beyond a certain size. Also, some data types, like blobs and random inserts and deletions can lead to extra fragmentation, but I presume that the size you are showing is less than 10% of the table size, so you should no worry. After a proper import, it is the least fragmented way innodb can work with the default options. Under normal operation, the file size should be constant when inserting new data until no more free space is available.

Of course, you can try to change parameters like extent or page sizes, but if you are concerned about saving disk space over performance I would recommend you to try compression (InnoDB Barracuda file formant has it) or use a different engine. However, please note that fragmentation and having huge files with lots of free space is usually not a concern with innodb_file_per_table = 1 under normal loads.


Practicing AlwaysOn on SQL Server 2014 - Windows 10 Home

SQL Server 2016 will allow for Availability Groups without a domain, but sorry, I don't think your operating system is going to cut it.

To support your scenario, you could put an eval of a proper server OS in a virtual machine or dual boot scenario, but to really test AGs, you'd need multiple copies of Windows in order to understand failover, availability modes, etc. And if you're trying to beef up your skills for a potential job, this edge case scenario is probably not the type of thing a company serious about HA/DR would have any interest in, because they're unlikely to be avoiding domains...


Service Broker: Queue Monitor is dropped after poison message

When a transaction rolls back five times, Service Broker disables all queues that the transaction received messages from, even if you have poison message handling turned off. This would mean recreating the event notifications.  So, adopt try .. catch and handle your poison messages.  You get four tries before SB will handle it for you.


Why can't I access IIS Manager on my server with Reporting Services installed?

As of SQL Server 2008, IIS is no longer used by SQL Server Reporting Services. It utilizes the HTTP.sys API now. So you will not see SSRS show up as a site or application pool.

The tip you linked to deals with SQL Server 2005, which required IIS in order to use SSRS. This tip speaks of the slowness you might be seeing and how to address it in SQL Server 2008/2008 R2. I would test to see if the same results are seen in 2012 version.


You can create a synonym to the object, however you cannot currently create a synonym to a database. This is a popular request on Connect:

Expand synonym to other entities (database, linked server)
Allow CREATE SYNONYM for <database>

So, as you've stated in your question, you'd need to run CREATE SYNONYM for each object affected. Like this, in the dev instance:

CREATE SYNONYM MyObjectSyn
FOR Database1Dev.dbo.MyObject;

And like this in the prod instance:

CREATE SYNONYM MyObjectSyn
FOR Database1.dbo.MyObject;

Then the stored procedure code references MyObjectSyn instead.

Another way to get around this problem, that I don't recommend, is to use a linked server connected to each database.  The linked server can be repointed on dev to Database1Dev, and Database1 on prod.

Or, as Scott Hodgin suggested: use dynamic SQL and a configuration table on each server that would 'map' <database1> to a specific database on that server.  The stored procedure would read the configuration table and REPLACE <database1> with the name retrieved from the configuration table.


mySQL trigger to prevent insert won't work

Change EXISTS( SELECT ... ) to EXISTS( SELECT ... FOR UPDATE ).

(Caveat:  I am guessing that this may solve it.)


You should download only the combo patch - in essence it is the two other - DB and OJVM patches bundled in one archive. After that the patching sequence is as follows:

Apply the DB PSU, but do not run any post-install steps
Apply the Java PSU
Run the Java PSU post-install steps
Run the DB PSU post-install steps

Take a look at the following support note:

Oracle Recommended Patches -- "Oracle JavaVM Component Database PSU" (OJVM PSU) Patches (Doc ID 1929745.1)

In it is more detailed information about the OJVM patches.


Why can not I get any row in a MySQL query from SQL Server?

As I'm obviously not even close to proficient in MySQL, my answer won't really focus on that side of your question.  If the view returns what you are expecting from within MySQL, I suspect the issue is on the SQL Server side.  What driver are you using?  If it's the built-in Microsoft driver for MySQL, I would strongly recommend downloading the MySQL Driver from their site and installing that as a new provider on your SQL Server.  When connecting to other RDBMS systems, I have had significantly better luck using the driver provided by the vendor of that application rather than using the generic driver provided by Microsoft.


How to apply transpose method in MYSQL, Getting rows as collumns

As yu will have different evaluators ver the time a dynamic approach is called for
if you have only a static, you can select the variable @sql and get ten result of the dynamc query

CREATE table Toy_ideas  (IDEA int, COMMENTS varchar(50), EVALUATOR_ID int)

INSERT INTO Toy_ideas VALUES(1,'coment1',1),
(1,'coment2',2),
(1,'coment3',3),(1,'coment4',5),
(2,'coment5',2),
(2,'coment6',4),(2,'coment7',5),
(3,'coment8',2),(3,'coment9',5)

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(EVALUATOR_ID = ',
     EVALUATOR_ID,
      ', `COMMENTS`,NULL)) AS `',
      CONCAT('EVALUATOR ',EVALUATOR_ID), '`'
    )
    ORDER BY EVALUATOR_ID
  )
  INTO @sql
FROM Toy_ideas;
SET @sql = CONCAT('select IDEA,',@sql,' from Toy_ideas
group by `IDEA`');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

IDEA | EVALUATOR 1 | EVALUATOR 2 | EVALUATOR 3 | EVALUATOR 4 | EVALUATOR 5
---: | :---------- | :---------- | :---------- | :---------- | :----------
   1 | coment1     | coment2     | coment3     | null        | coment4
   2 | null        | coment5     | null        | coment6     | coment7
   3 | null        | coment8     | null        | null        | coment9

db<>fiddle here


Download SQL Server Profiler for SQL Server Management Studio

SQL Profiler does not come with SQL Server Express 2008 R2*.  You can get it with the installation media for Datacenter, Enterprise, Standard, Web, Developer, or Workgroup licenses.

If you have the installation media, you can get Profiler that way.  Otherwise, Developer Edition is available for $50.

There are some other tools available, and you could even create your own server-side traces (for example) without Profiler.  Then, use a tool like Qure Workload Analyzer to review the trace files.  With 2008 R2, you could in addition use Extended Events to track activity.  The advantage is that both of these are less of a performance hit than running Profiler would be.

If you're looking at a point-in-time solution and can install procedures, Adam Machanic's sp_whoisactive could be something to look into.

* Profiler is available in SQL Server 2012 Express Edition SP1 onward. You need to select SSMS (Complete) in the installation options.


The solution to this lies in the table creation itself.  If I set IGNORE_DUP_KEY = ON on the index on my person ID column, the duplicate values are simply ignored and the rest are inserted appropriately.


How can I find databases with a large number of virtual log files?

Here is a slightly simpler approach that avoids the cursor and nested exec:

SET NOCOUNT ON;

CREATE TABLE #to
(
  DBName SYSNAME,
  FileCount INT
);

DECLARE @v INT;
SELECT @v = CONVERT(INT, PARSENAME(CONVERT(VARCHAR(32),
  SERVERPROPERTY('ProductVersion')), 4));

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'CREATE TABLE #ti
  (
    ' + CASE WHEN @v >= 11 THEN 'RecoveryUnitId INT,' ELSE '' END + '
    FileId int
    , FileSize nvarchar(255)
    , StartOffset nvarchar(255)
    , FSeqNo nvarchar(255)
    , Status int
    , Parity int
    , CreateLSN nvarchar(255)
);';

SELECT @sql = @sql + N'
  INSERT #ti EXEC ' + QUOTENAME(name)
    + '.sys.sp_executesql N''DBCC LOGINFO WITH NO_INFOMSGS'';
  INSERT #to(DBName,FileCount) SELECT N''' + name + ''', COUNT(*) FROM #ti;
  TRUNCATE TABLE #ti;'
FROM sys.databases
WHERE database_id > 4 AND [state] = 0;

EXEC sp_executesql @sql;

SELECT DBName, FileCount FROM #to -- WHERE FileCount > [some threshold];

DROP TABLE #to;


A tuple relational calculus equivalent for the given SQL query

A tuple relational calculus expression should be written with the usual language of first order logic.

In this case, for instance, you could write:

{ [e.Name, d.Name] | Ǝ e ∈ Employee, Ǝ d ∈ Department . e.Dept_no = d.No }

(a part from minor variations on the notation used, like “:” instead of “.”).

The idea is that you obtain the result of the query (a well formed formula of a first order logic), by finding the elements of the relations that, substituted to the free variables of the formula, make the formula true.

In this case, we have two variables, d and e, that range on Departments and Employees respectively, a condition that must be true (e.Dept_no = d.No), and from every pair of tuples of the relations that satisfy this condition, you add to the resulting set a tuple formed by the Name of e and the Name of d (in other words you perform a simple join).

Note that the structure of the expression can be easily mapped to the SQL query (and actually this language is the origin of the SQL language, together with other additions). This can be a suggestion on how to write other queries similar to this one.


Temporal database design, with a twist (live vs draft rows)

Cross-posting the answer as well:

I think I nailed it. Basically, you add a (unique) draft field to the relevant tables, and you work on the drafts as if they were a new post/tag/etc.:

posts (
 id pkey,
 public,
 created_at stamptz,
 updated_at stamptz,
 updated_by int,
 draft int fkey posts (id) unique
)

post_revs (
 id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (id, created_at)
)

tags (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by,
 draft fkey tags (id) unique
)

tag_revs (
 id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (id, created_at)
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id),
 public,
 created_at,
 updated_at,
 updated_by,
 pkey (post_id, tag_id)
)

post2tag_revs (
 post_id,
 tag_id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (post_id, tag_id, created_at)
)


First off, you do not want to use char(50). Use varchar(50) or just text. Read more:

Any downsides of using data type “text” for storing strings?

Assuming the following rules:

Basic slugs never end with a dash.
Duplicate slugs are suffixed with a dash and a sequential number (-123).

Note that all of the following methods are subject to a race conditions: concurrent operations might identify the same "free" name for the next slug.
To defend against it, you can impose a UNIQUE constraint on slug and be prepared to repeat an INSERT upon duplicate key violation or you to take out a write lock on the table at the start of the transaction.

If you glue the suffix to the basic slug name with a dash and allow basic slugs to end in separate numbers, the specification is a tiny bit ambiguous (see comments). I suggest a unique delimiter of your choice instead (which is otherwise disallowed).

Efficient rCTE

WITH RECURSIVE
  input AS (SELECT 'news-on-apple'::text AS slug)  -- input basic slug here once
, cte   AS (
   SELECT slug || '-' AS slug  -- append '-' once, if basic slug exists
        , 1 as suffix          -- start with suffix 1
   FROM   article
   JOIN   input USING (slug)

   UNION ALL
   SELECT c.slug, c.suffix + 1  -- increment by 1 ...
   FROM   cte     c
   JOIN   article a ON a.slug = c.slug || c.suffix  -- ... if slug-n already exists
   )
(
SELECT slug || suffix AS slug
FROM   cte
ORDER  BY suffix DESC  -- pick the last (free) one
LIMIT  1
)  -- parentheses required
UNION  ALL  -- if the basic slug wasn't taken, fall back to that
SELECT slug FROM input
LIMIT  1;

Better performance without rCTE

If you worry about thousands of slugs competing for the same slug or generally want to optimize performance, I'd consider a different, faster approach.

WITH input AS (SELECT 'news-on-apple'::text  AS slug
                    , 'news-on-apple-'::text AS slug1)  -- input basic slug here
SELECT i.slug
FROM   input        i
LEFT   JOIN article a USING (slug)
WHERE  a.slug IS NULL  -- doesn't exist yet.

UNION ALL
(  -- parentheses required
SELECT i.slug1 || COALESCE(right(a.slug, length(i.slug1) * -1)::int + 1, 1)
FROM   input        i
LEFT   JOIN article a ON a.slug LIKE (i.slug1 || '%')  -- match up to last "-"
                     AND right(a.slug, length(i.slug1) * -1) ~ '^\d+$' -- suffix numbers only
ORDER  BY right(a.slug, length(i.slug1) * -1)::int DESC
)
LIMIT  1;

If the basic slug isn't taken yet, the more expensive second SELECT is never executed - same as above, but much more important here. Check with EXPLAIN ANALYZE, Postgres is smart that way with LIMIT queries. Related:

Optimize a query on two big tables

Check for the leading string and the suffix separately, so the LIKE expression can use a basic btree index with text_pattern_ops like

CREATE INDEX article_slug_idx ON article (slug text_pattern_ops);

Detailed explanation:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Convert the suffix to integer before you apply max(). Numbers in text representation don't work.

Optimize performance

To get the optimum, consider storing the suffix separated from the basic slug and concatenate the slug as needed: concat_ws('-' , slug, suffix::text) AS slug

CREATE TABLE article (
   article_id serial PRIMARY KEY
 , title text NOT NULL
 , slug  text NOT NULL
 , suffix int
);

The query for a new slug then becomes:

SELECT slug
    || COALESCE((
          SELECT '-'::text || (max(suffix) + 1)::text
          FROM   article a
          WHERE  a.slug = i.slug), '') As slug
FROM  (SELECT 'news-on-apple'::text AS slug) i  -- input basic slug here

Ideally supported with a unique index on (slug, suffix).

Query for list of slugs

In any version of Postgres you can provide rows in a VALUES expression.

SELECT *
FROM   article
JOIN  (
   VALUES
     ('slug-foo'::text, 1)
     ('slug-bar',7)
   ) u(slug,suffix) USING (slug,suffix);

You can also use IN with a set of row-type expressions Which is shorter:

SELECT *
FROM   article
WHERE (slug,suffix) IN (('slug-foo', 1), ('slug-bar',7));

Details under this related question (as commented below):

the <set clause>'s <multiple column assignment>

For long lists, the JOIN to a VALUES expression is typically faster.

In Postgres 9.4 (released today!) you can also use the new variant of unnest() to unnest multiple arrays in parallel.

Given an array of basic slugs and a corresponding array of suffixes (as per comment):

SELECT *
FROM   article
JOIN   unnest('{slug-foo,slug-bar}'::text[]
            , '{1,7}'::int[]) AS u(slug,suffix) USING (slug,suffix);


I seriously doubt that it ever held true for InnoDB.  Even for MyISAM (which really can have FIXED length rows) it was rarely true.

Roughly speaking, this is the order (most overhead first)

Disk hit(s).
Locate the record(s).
Parse the record (split it into columns).
Apply functions and evaluate expressions.

If the data is bigger (as in CHAR being padded), the #1 is impacted.  The rest pales into insignificance.

Note: There are 4 flavors of InnoDB ROW_FORMAT.

For REDUNDANT, a CHAR(100) CHARACTER SET utf8mb4 always takes 400 bytes.
For COMPACT or DYNAMIC, it takes between 100 and 400 bytes.
For COMPRESSED, the padding spaces are highly compressible, but still take some space.

Allegedly, the "fixed" size of a CHAR (which, is not all that fixed), tends to prevent block splits.  But I kinda doubt it.

My Rule of Thumb:  CHAR should almost always be restricted to truly fixed length strings, and most of them are hex or ascii:

country_code CHAR(2) CHARACTER SET ascii
british_postal_code CHAR(6) CHARACTER SET ascii
zip5 CHAR(5) CHARACTER SET ascii
md5 CHAR(32) CHARACTER SET ascii  -- UNHEX() into BINARY(16) is tighter
base64 CHAR(?) CHARACTER SET ascii COLLATION ascii_bin  -- need case-significance

Note:  latin1 works equally well as ascii, the 8.0 default of utf8mb4 does not.

(See also my answer to the first link.  It starts "Most of the answers in this thread are 5 years old, written before InnoDB and utf8 were defaults. So, let me start over..")


It does return 'Null' if you've only got a default instance. I tried it in SQL 2005 and SQL 2008R2.
Otherwise, @@SERVERNAME should be the same as ServerProperty('MachineName') + '\' + ServerProperty('InstanceName')
Tested this also in SQL 2005 and SQL 2008R2.


Granting specific permissions on ALL tables

If you're not specifying the table's schema, and you're not, then you can use ALL TABLES IN SCHEMA and rely on the default schema being PUBLIC or in the event you've changed your search_path just write the schema name below. From the docs on GRANT

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

So that looks like

GRANT INSERT, SELECT, UPDATE
  ON ALL TABLES IN SCHEMA public
  TO snort;
GRANT ALL
  ON ALL TABLES IN SCHEMA public
  TO base;


USE test
DROP TABLE IF EXISTS pi;
CREATE TABLE pi
(
    num BLOB
) ENGINE=MyISAM;
INSERT INTO pi VALUES
('3.14159265358...');
SELECT LOCATE('12345',num) FROM pi;

Here is the output
+---------------------+
| locate('12345',num) |
+---------------------+
|               49704 |
+---------------------+
1 row in set (0.03 sec)

I had to go to http://www.piday.org/million/ and scroll/Ctrl-F dozens of times until my web browser found it. Then, I pasted about 60000 characters into that line. Then, I ran the code using MySQL 5.6.21 on my laptop in Windows 8.1.
Have Fun !!!
UPDATE 2015-03-14 15:52 EST
BTW I just discovered this website : 100,000 Digits of Pi.
This displays pi to 100,000 digits instantly. 12345 is also there.
UPDATE 2015-03-14 15:57 EST
I tried the INSTR() function
+--------------------+
| instr(num,'12345') |
+--------------------+
|              49704 |
+--------------------+
1 row in set (0.00 sec)

It works as well !!!


How to avoid DROP TABLE for a specific MySQL table

prevent DROP TABLE from being executed ONLY FOR THIS TABLE

But dropping some another table in the same database must be available?

If so the task cannot be solved using MySQL privilege system. Because some privilege granted to or revoked from some object affects on all its subobjects. Revoking DROP on some database revokes it for all tables in this database. No separate table  privilege.

But the task can be solved using FOREIGN KEY subsystem.

Create some fake table. Set some field in this table as referenced to the table in question. And server will forbid your table drop until this fake table (or FK from it to your table) drop.


Table size grows indefinitely after deleting and then inserting the same data

If you have access to My Oracle Support:

Space Used By Tables Are Not Being Re-used With Repeat Delete/insert Operation (Doc ID 1601805.1)

So, without just copy-pasting the contents from there, the above note states:

the space management algorithm prefers allocating more space over reusing already allocated but free space
this is not a bug, but a feature, this is how it was designed
you can use shrink to compact the segment, as you have already discovered


Covering Index Changes Execution Plan but is not used

1) Is the covering index required or is there a better way to improve the
  performance

Best index

The best index would be the most covering, selective index for the queries accessing the table.

Take for example in your table, you have 50000 rows where the firstname = John , but only one where the last name = 'McClane', Should you create the index with John as the First key value or McClane?

Answer:

It depends... If you are always searching for John Mcclane, then its an open and shut case of indexing the lastname first. But what if there are also queries searching for Constanthin Smith? You could have over 5000 Smiths, but only five Constanthin's.

As a result, it depends on your queries and what you are seeking on, how much they are executed, ....

If your queries are going to always seek on both the firstname and lastname, then it is the simple case of picking the more selective one as the first key column.
Keeping in mind that read performance improving should stay bigger than write performance declining.

Ofcourse, nobody restricts you to create two indexes, one with (firstname,lastname )and one with (lastname,firstname).

(Your update / insert / delete statements might).

Not considering filtered indexes and whatnot, the best index for your example would be:

CREATE NONCLUSTERED INDEX idx_Forename_Surname ON dbo.Customers (Forename,Surname)

  2) Why does the additional covering index cause the change of index in
  the actual execution plan from idx_Forename to idx_Surname?

I don't think that this was just because of the index, but because of the statistics created as a result of the index creation.

Even though these stats are the same as the one in idx_Surname, my guess is that they are with a bigger sample rate (100), as they are created with 'fullscan'.

If an auto update stats occured on the statistics created by the index idx_Surname, they might have had a smaller sample rate, resulting in bad estimates (E.g. 1% Sample rate).

You could try removing the idx_Surname_Covering index and its statisics and updating the stats on dbo.Customers with  100% sample rate (fullscan) to test this theory.

UPDATE STATISTICS dbo.Customers WITH FULLSCAN

Which hopefully changes your plan to use the better seek.

If this is why your query changed, and updating the stats with fullscan on maintenance windows is not a viable option, you could change the sample rate


Setting user permissions for different SQL Server schemas

The basic concept is to use GRANT/DENY Schema Permissions. You can efficiently manage permissions by creating a role and then adding members to it.

Below is an example that will explain you in detail

use master
go
--Create Logins
CREATE LOGIN UserA WITH Password='UserA123';
go
CREATE LOGIN UserB WITH Password='UserB123';

use AdventureWorks2008R2
go
--Create Database Users
CREATE USER UserA;
go
CREATE USER UserB;
go
--Create the Test Schemas
CREATE SCHEMA SchemaA AUTHORIZATION UserA
go
CREATE SCHEMA SchemaB AUTHORIZATION UserB
go

-- create test tables
create table schemaA.TableA (fname char(5))
go
insert into schemaA.TableA (fname) values ('Kin-A')
go

create table SchemaB.TableB (fname char(5))
go
insert into SchemaB.TableB (fname) values ('Kin-B')
go

Now test :

--Test for UserA in SchemaA
EXEC('select * from schemaA.TableA') AS USER = 'UserA'
go
--Kin-A

-- Test for UserB in SchemaB == this should fail
EXEC('select * from SchemaB.TableB') AS USER = 'UserA'
go
--Msg 229, Level 14, State 5, Line 1
--The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Now create Stored Procedures :

CREATE PROCEDURE SchemaB.proc_SelectUserB
AS
    select * from schemaA.TableA;
go
create procedure schemaA.proc_SchemaA
as
    select * from schemaA.TableA

Now Grant execute permissions to UserA on schemaB's SP

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [UserA]
go

Test it .. to see if UserA is able to run SP from schemaB. This will PASS

EXECUTE AS LOGIN='UserA';
    Exec SchemaB.proc_SelectUserB;
    revert;
go
--- Kin-A

But UserA wont be able to see data from SchemaB

EXECUTE AS LOGIN='UserA';
    select * from SchemaB.TableB
revert;
go

--- Msg 229, Level 14, State 5, Line 3
--- The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Alternatively you can use DATABASE ROLE and just add users to it for better manageability of permissions:

EXEC sp_addrole 'SchemaBUsesSchemaAProc'
go
EXEC sp_addrolemember 'SchemaBUsesSchemaAProc','UserA';
go

Below statement will make sure that UserA is able to see schemaA and NOT schemaB. The good thing is that you can just add users to SchemaBUsesSchemaAProc role and they will inherit all the permissions granted to that role.

GRANT SELECT ON SCHEMA::SchemaA TO SchemaBUsesSchemaAProc;
go

If you only want to allow UserA to execute SP's which are owned by SchemaB then below statement will do the job:

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [SchemaBUsesSchemaAProc]
go

This way, UserA is not able to see SchemaB's tables, but still can execute procs from SchemaB.

Below will explain the permission hierarchy :


Is it possible to run VACUUM FULL in Postgres asynchronously?

Do you need VACUUM FULL?

  VACUUM FULL is necessary to reclaim physical space after purging

Yes, if you want to return that space to the OS or use it in other tables.

If you expect to just re-use the space for new rows in the same table, you're better off just running normal VACUUM (or letting autovaccum do its job), so the blocks containing deleted rows are marked as free for re-use. Shrinking and growing tables is a heavyweight operation that's best avoided if you're just going to re-use the space.

VACUUM FULL without locks

Pg doesn't provide a VACUUM FULL CONCURRENTLY.

There's a client-based effort to do the equivalent, called pg_repack. I can't speak for its safety/reliability.

In general we tend to avoid VACUUM FULL in favour of just letting normal VACUUM clear space for re-use within the table.


I followed @ypercube's suggestion and just rewrote both queries - turns out there wasn't a relationship in the database that was needed to tie two tables together, and when there were multiple relationships on a set of columns MySQL did what @JNK said and was non-deterministic and grouped inconsistently.

If you run into something like this, check your table relationships, especially if there are multiple possibilities with what you're joining on and you only need to be joining on one.

Thanks for the help everyone.


Table update is causing front end SQL statement to fail

Turned on sql profiler and learned there was a sequence table that is used to determine the next primary key id. Updated this table and deleted the cache. Performed a restart on the server and the problem was solved. In short it was primary key violation.

Thanks for the sql trace idea that led to sql profiler.


Why my database mirror break down after changing file group settings from RESTRICTED_USER to MULTI_USER?

We found the issue. It is a bug in SQL Server. When we set READ_WRITE the command is not transfeered properly into the mirror DB. When script start change partitions on the mirror server an error occured. After that the syncronization is ruined and the DB on the mirror is locked (in suspended state).

We fix the issue by updating SQL Server to the latest version (our initial version was wiout SP).


Two likely possibilities.
Blocking
Your production machine probably has a lot more going on than your test/dev machines.  You could simply be seeing blocking.  Run your query in one window and this in another.
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0

If you are being blocked you may need to look at what's blocking you and how long it's taking to run.
Configuration differences
This one covers a LOT of possibilities.  It could be a difference in the max memory as Kin suggested or any number of other possibilities. See:
difference in execution plans on UAT and PROD server
For example if the max degree of parallelism is 1 or the cost threshold for parallelism isn't the same on both servers you could be getting a single threaded plan instead of getting a parallel plan.  It could even be the reverse.  I had one case where the parallel plan was quite a bit slower than the single threaded plan.
Compare the results of this query between the two servers:
SELECT * FROM sys.configurations

Check any differences and see if they are affecting your query.


Test password complexity without creating a user

The enforce password policy setting for a SQL Server Login is basically a set of rules. These are as follows:

  Password Complexity

  Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When password complexity policy is enforced, new passwords must meet the following guidelines:

  The password does not contain the account name of the user.
  The password is at least eight characters long.
  The password contains characters from three of the following four categories:

  Latin uppercase letters (A through Z)
  Latin lowercase letters (a through z)
  Base 10 digits (0 through 9)
  Non-alphanumeric characters such as: exclamation point (!), dollar sign ($), number sign (#), or percent (%).

  Passwords can be up to 128 characters long. You should use passwords that are as long and complex as possible.

Reference: Password Policy (Microsoft Docs)

So basically you could program your web form to check for the complexity of a given password according to the above rules.

There are a couple of questions over on Stack Overflow that summaries possibilities of using REGEX or Java to check the password complexity:

Complex password Regular expression and accepted answer
Regex to validate password strength and accepted answer

Good luck.


So I finally fixed it.

I ran upgrade script as described in question.

The logs complained only about the wrong structure of performance_schema which is a system database that contains only performance metrics and a missing proxies_priv table inside other system database.

The errors are gone.

And of course before making any changes I did a backup of all databases with mysqldump.

Thank you all for help! :)


Using NEW on tables with generated columns

That is simple: use an AFTER trigger instead. The column value will have been computed then.
On the other hand, your answers reveals that you need a BEFORE trigger because you want to modify NEW.
So probably the best solution is to use a BEFORE trigger with a WHEN condition that excludes the generated column:
WHEN ((NEW.col1, NEW.col2, ...)
      IS DISTINCT FROM
      (OLD.col1, OLD.col2, ...))


Deadlock - Due to update causing Index page movement within an index

So did some testing and the deadlock is in fact caused by page movement within the non clustered index caused by an update which changes the position of the row in the index.

So initially Session 2 (update) takes out IX lock on Page2 where the index row is originally located.  Since the update would change the value and in turn change the position of the index row within the index, session 2 will also want to take out another IX lock on the target page where the index row will end up after the update.

For understanding the reason why a select would want to hold on to S lock please refer to this question.

REPO

set nocount on
create table deadlck ( a int , b int, c char (3), constraint PKA primary key (a))
create index idx on deadlck(b)
--creating a big enough table so that selects use page lock instead of row lock
declare @a int =1
declare @b int =57800000
while 1=1
begin
insert into deadlck select @a,@b,'111'
if (@a%521) =520 -- to make idx index pages fit one value in each page perfectly,  not necessary, just easier to simulate the issue while testing
set @b=@b-1
Set @a=@a+1
if @a=52100
break
end

ALTER INDEX idx ON deadlck
REBUILD WITH (FILLFACTOR = 90); -- same reason as above, i did the math with a fill factor of 90 to have the pages of idx index have one value per page and and also the pages needed to have some space so when we do the update the index entry can move to another page without have to do a page split

ALTER INDEX PKA ON deadlck
REBUILD WITH (FILLFACTOR = 90);

--session 1
while 1=1
begin
begin tran
update deadlck set b=57799999 where a=2
rollback
end

--session 2
while 1=1
begin
begin tran
select b from deadlck with(paglock) where (b =57799999 or b=57800000)
rollback
end


Using SSAS for retrieval data instead of using SSRS to make a report?

Depending on what type/volume of data you are reporting on, SSAS can possibly make it easier. I would not take that as a blanket statement. The decision to generate reports off of a relational or dimensional database should be made on a case by case basis.

Keep in mind that the development required to build an OLAP database around a subject area is far more time consuming than building a single report (or even several).

The benefits of building out an OLAP database are huge in return. In many cases, your users can connect directly to it with excel and generate their own reports. This eases the SSRS development requirements on IT.

At the end of the day, there are many factors that would contribute to answering your question. There is simply not a one size fits all solution.


How do I back up On Premise Microsoft SQL Server 2008 to Azure cloud

A SSIS package to move the data would be the route I'd go. This SO thread has many suggestions of solutions users tried apart from SSIS, incl 3rd party tools.