Which design to choose in this case? I am designing a database for a video game. I have some data structures like {x, y, z, dimension} used across multiple tables.

For instance :

Table player_death

id: int
player_id: int
death_x: int
death_y: int
death_z: int
death_dimension: int

Since {x, y, z, dimension} represents a geographic vector in the video game, a lot of table contain those columns. My question is : should I create a table named vector containing a pool of all the vectors used in my tables (and then use reference to them) or should I keep adding {x, y, z, dimension} columns to each table using a vector ? What is the best practice ? I am a beginner in database design, sorry if the question sounds stupid.

should I create a table named vector containing a pool of all the vectors used in my tables?

No

Creating such a table and referencing to it would only be helpful If you know that many, many players are going to die in the same spot. Assuming that death of players will be in random locations, creating another table will not help, and your select queries will have to carry an unnecessary overhead of joins. If your queries will mainly look like this, then this option is best for you :

SELECT * FROM player_death WHERE player_id = <playerid>

On the other hand, if your game largely depended on coordinates of places rather than death places of players, creating another table would have been relevant. For example, if you want to spawn monsters only where players have died , or if you have to paint the death place red, and some other place yellow and rest all green, these all are relevant to  on position in the map, not death place of players, and it'd be better to have a separate table.


How to make sqlplus prompt longer than 50 chars? I've set a prompt in my sqlplus glogin.sql a little bit long, mainly because of some internal variables and control sequence. Starting sqlplus I get the following error:

String beginning "truncated_prompt..." is too long. Maximum size is 50 characters.
SQL>

...and yes, my prompt is really truncated to 50 characters.

What to do? Is it possible to somehow make this limit higher? (I already tried SET LONG and SET LONGC without any change.)

(Reacting comment)
I have this long prompt, because I have some control sequences in it, which make it so beautiful. And I am calling it through rlwrapper, which makes the normal keys (page up/down, history search, tab completion, etc) also working, just as in a good command line application.

Finally, I found an answer!

The trick is, that the 50 char limit is for the string before the variable substitution! The size of the substituted string isn't limited. In my case, shorting _connect_identifier to a temporal variable _ci was enough:

DEFINE _ci=&_connect_identifier
SET SQLPROMPT "(long things)_user'@'_ci'(long things)"


How to GROUP_CONCAT between given rows? I have a table as

id  diary
1   breakfast
2   walk
3   start
4   office works
5   office projects
6   end
7   taxi
8   start
9   preparing for meeting
10  doing the meeting
11  end
12  night

I want to CONCAT rows between start and end to product table:

id  type      diary
1             breakfast
2             walk
3   concated  office works, office projects
7             taxi
8   concated  preparing for meeting, doing the meeting
12            night

I think I need to use GROUP_CONCAT within a IF STATEMENT, but have no idea how to implement it.

Here is the query you need:
set @groupnum = 0;
set @groupon = 0;
set @inc = 1;
select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from
(select
    @inc      := IF(@groupon=0,1,0)           inc,
    @groupnum := @groupnum + @inc             grpn,
    @s_tag    := IF(diary='start',1,0)        stag,
    @e_tag    := IF(diary='end',  1,0)        etag,
    @groupon  := IF(diary='start',1,@groupon) g1,
    @groupon  := IF(diary='end',  0,@groupon) g2,
    (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    id,diary from diary
) A WHERE ord NOT IN (1,1000000) GROUP BY grpn;

First, here is your sample data
mysql> use all_db
Database changed
mysql> drop table if exists diary;
Query OK, 0 rows affected (0.03 sec)

mysql> create table diary
    -> (
    ->     id int not null auto_increment,
    ->     diary varchar(25),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> insert into diary (diary) values ('breakfast'),('walk'),('start'),
    -> ('office works'),('office projects'),('end'),('taxi'),('start'),
    -> ('preparing for meeting'),('doing the meeting'),('end'),('night');
Query OK, 12 rows affected (0.06 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select * from diary;
+----+-----------------------+
| id | diary                 |
+----+-----------------------+
|  1 | breakfast             |
|  2 | walk                  |
|  3 | start                 |
|  4 | office works          |
|  5 | office projects       |
|  6 | end                   |
|  7 | taxi                  |
|  8 | start                 |
|  9 | preparing for meeting |
| 10 | doing the meeting     |
| 11 | end                   |
| 12 | night                 |
+----+-----------------------+
12 rows in set (0.00 sec)

mysql>

Using iterative variable manipulation
set @groupnum = 0;
set @groupon = 0;
set @inc = 1;
select * from
(select
    @inc      := IF(@groupon=0,1,0)           inc,
    @groupnum := @groupnum + @inc             grpn,
    @s_tag    := IF(diary='start',1,0)        stag,
    @e_tag    := IF(diary='end',  1,0)        etag,
    @groupon  := IF(diary='start',1,@groupon) g1,
    @groupon  := IF(diary='end',  0,@groupon) g2,
    (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    id,diary from diary
) A;

Here is what it generates:
mysql> set @groupnum = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @groupon = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @inc = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from
    -> (select
    ->     @inc      := IF(@groupon=0,1,0)           inc,
    ->     @groupnum := @groupnum + @inc             grpn,
    ->     @s_tag    := IF(diary='start',1,0)        stag,
    ->     @e_tag    := IF(diary='end',  1,0)        etag,
    ->     @groupon  := IF(diary='start',1,@groupon) g1,
    ->     @groupon  := IF(diary='end',  0,@groupon) g2,
    ->     (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    ->     id,diary from diary
    -> ) A;
+-----+------+------+------+------+------+---------+----+-----------------------+
| inc | grpn | stag | etag | g1   | g2   | ord     | id | diary                 |
+-----+------+------+------+------+------+---------+----+-----------------------+
|   1 |    1 |    0 |    0 |    0 |    0 |       0 |  1 | breakfast             |
|   1 |    2 |    0 |    0 |    0 |    0 |       0 |  2 | walk                  |
|   1 |    3 |    1 |    0 |    1 |    1 |       1 |  3 | start                 |
|   0 |    3 |    0 |    0 |    1 |    1 |       0 |  4 | office works          |
|   0 |    3 |    0 |    0 |    1 |    1 |       0 |  5 | office projects       |
|   0 |    3 |    0 |    1 |    1 |    0 | 1000000 |  6 | end                   |
|   1 |    4 |    0 |    0 |    0 |    0 |       0 |  7 | taxi                  |
|   1 |    5 |    1 |    0 |    1 |    1 |       1 |  8 | start                 |
|   0 |    5 |    0 |    0 |    1 |    1 |       0 |  9 | preparing for meeting |
|   0 |    5 |    0 |    0 |    1 |    1 |       0 | 10 | doing the meeting     |
|   0 |    5 |    0 |    1 |    1 |    0 | 1000000 | 11 | end                   |
|   1 |    6 |    0 |    0 |    0 |    0 |       0 | 12 | night                 |
+-----+------+------+------+------+------+---------+----+-----------------------+
12 rows in set (0.00 sec)

mysql>

With a few refinements

ignoring ord=1 and ord=1000000
GROUP BY on grpn

Here is the final query first mentioned
set @groupnum = 0;
set @groupon = 0;
set @inc = 1;
select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from
(select
    @inc      := IF(@groupon=0,1,0)           inc,
    @groupnum := @groupnum + @inc             grpn,
    @s_tag    := IF(diary='start',1,0)        stag,
    @e_tag    := IF(diary='end',  1,0)        etag,
    @groupon  := IF(diary='start',1,@groupon) g1,
    @groupon  := IF(diary='end',  0,@groupon) g2,
    (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    id,diary from diary
) A WHERE ord NOT IN (1,1000000) GROUP BY grpn;

Here it is executed:
mysql> set @groupnum = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @groupon = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @inc = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from
    -> (select
    ->     @inc      := IF(@groupon=0,1,0)           inc,
    ->     @groupnum := @groupnum + @inc             grpn,
    ->     @s_tag    := IF(diary='start',1,0)        stag,
    ->     @e_tag    := IF(diary='end',  1,0)        etag,
    ->     @groupon  := IF(diary='start',1,@groupon) g1,
    ->     @groupon  := IF(diary='end',  0,@groupon) g2,
    ->     (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    ->     id,diary from diary
    -> ) A WHERE ord NOT IN (1,1000000) GROUP BY grpn;
+------+-----------------------------------------+
| grpn | itinerary_step                          |
+------+-----------------------------------------+
|    1 | breakfast                               |
|    2 | walk                                    |
|    3 | office works,office projects            |
|    4 | taxi                                    |
|    5 | preparing for meeting,doing the meeting |
|    6 | night                                   |
+------+-----------------------------------------+
6 rows in set (0.00 sec)

mysql>

Give it a Try !!!
CAVEAT
I tried out @a1ex07's query against my sample data
mysql> select d.id1,
    -> CASE
    -> WHEN COUNT(*) >1 THEN 'concatenated'
    -> ELSE NULL
    -> END AS `type`,
    -> GROUP_CONCAT(
    -> case
    -> when diary = 'start' or diary = 'end' then null
    -> else diary
    -> end
    -> order by id
    -> ) as diary
    -> FROM
    -> (
    -> SELECT x.id, x.diary,
    -> CASE WHEN start_id IS NULL OR end_id IS NULL OR start_id > end_id THEN id
    -> ELSE start_id
    -> END as id1,
    -> end_id
    -> FROM
    -> (
    ->     select a.*,
    ->     (select max(id) from diary b where b.diary ='start' and b.id <=a.id) as start_id,
    ->     (select min(id) from diary b where b.diary ='end' and b.id >=a.id) as end_id
    ->
    ->      from diary a
    ->  )x
    -> )d
    -> group by id1,end_id;
+------+--------------+-----------------------------------------+
| id1  | type         | diary                                   |
+------+--------------+-----------------------------------------+
|    1 | NULL         | breakfast                               |
|    2 | NULL         | walk                                    |
|    3 | concatenated | office works,office projects            |
|    3 | NULL         | taxi                                    |
|    8 | concatenated | preparing for meeting,doing the meeting |
|   12 | NULL         | night                                   |
+------+--------------+-----------------------------------------+
6 rows in set (0.00 sec)

mysql>

By George, it works !!! He get's +1 for getting his answer in first.


Unknown table 'table' in field list on referencing temporary table inside a trigger I am creating a trigger BEFORE INSERT that checks a record from another table to see if it exists, otherwise signal sqlstate. The trigger definition shown below
DELIMITER //
DROP TRIGGER IF EXISTS pob_exists_barang_kode//
CREATE TRIGGER pob_exists_barang_kode BEFORE INSERT
ON `PURCHASE_ORDER_BARANG` FOR EACH ROW
BEGIN
    declare msg varchar(128);
    CREATE TEMPORARY TABLE kode_barang_tbl
    SELECT ven.`KODE_BARANG`  as kode_barang
    FroM  (
            SELECT po.`REQ_VENDOR_ID` as rvid
            FROM `PURCHASE_ORDER` po,
                 `PURCHASE_ORDER_BARANG` pob
            WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID`
          ) `asdf`,
          `VENDOR_BARANG` ven
    WHERE ven.`VENDOR_ID` = asdf.rvid;

    CREATE TEMPORARY TABLE dummy1
    SELECT po.`REQ_VENDOR_ID` as rvid
            FROM `PURCHASE_ORDER` po,
                 `PURCHASE_ORDER_BARANG` pob
            WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID`;

    IF NEW.barang_kode not in (kode_barang_tbl.kode_barang) THEN
        set msg = concat('Kesalahan! Tidak ada kode barang ',
                          cast(new.barang_kode as char),
                          ' pada vendor id ',
                          cast(dummy1.rvid as char)
                        );
        signal sqlstate '45000' set message_text = msg;
    END IF;

    DROP TEMPORARY TABLE dummy1;
    DROP TEMPORARY TABLE kode_barang_tbl;
END;//

DELIMITER ;

Trigger created fine, so I ran this script below to test if the trigger works:
insert into `PURCHASE_ORDER_BARANG` values ('po2', '1', 5135, null);

but it returns an error message
Error Code: 1109. Unknown table 'kode_barang_tbl' in field list

Executing the part of the statement works as expected:
SELECT ven.`KODE_BARANG`  as kode_barang
    FroM  (
            SELECT po.`REQ_VENDOR_ID` as rvid
            FROM `PURCHASE_ORDER` po,
                 `PURCHASE_ORDER_BARANG` pob
            WHERE po.`ID` = 'po2'
          ) `asdf`,
          `VENDOR_BARANG` ven
    WHERE ven.`VENDOR_ID` = asdf.rvid;

I'm not really sure which line causes the error, but I am sure I have created the temporary tables and the trigger correctly. I also have no table kode_barang_tbl inside the database except in this trigger. Am I missing something? Any help is appreciated.

The solution is now found by Akina, I made a mistake on IF NEW.barang_kode not in (kode_barang_tbl.kode_barang) THEN which should be IF NEW.barang_kode not in (SELECT kode_barang FROM kode_barang_tbl) THEN. I also made changes to my script which makes the same error on cast(dummy1.rvid as char). So here's my full script in the end. Thanks to Akina.
DELIMITER //
DROP TRIGGER IF EXISTS pob_exists_barang_kode//
CREATE TRIGGER pob_exists_barang_kode BEFORE INSERT
ON `PURCHASE_ORDER_BARANG` FOR EACH ROW
BEGIN
    declare msg varchar(128);
    CREATE TEMPORARY TABLE kode_barang_tbl
    SELECT ven.`KODE_BARANG` as kode_barang
    FroM  (
            SELECT po.`REQ_VENDOR_ID` as rvid
            FROM `PURCHASE_ORDER` po
            WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID`
          ) `asdf`,
          `VENDOR_BARANG` ven
    WHERE ven.`VENDOR_ID` = asdf.rvid;

    SELECT po.`REQ_VENDOR_ID` as rvid
            FROM `PURCHASE_ORDER` po
            WHERE NEW.`PURCHASE_ORDER_ID` = po.`ID` INTO @rvid;

    IF NEW.barang_kode not in (SELECT kode_barang FROM kode_barang_tbl) THEN
        set @errmsg = concat('Kesalahan! Tidak ada kode barang ',
                          cast(new.barang_kode as char),
                          ' pada vendor id ',
                          cast(@rvid as char)
                        );
            DROP TEMPORARY TABLE kode_barang_tbl;
        signal sqlstate '45000' set message_text = @errmsg;
    END IF;

    DROP TEMPORARY TABLE kode_barang_tbl;
END;//

DELIMITER ;


Set simple recovery mode and shrink log files for all user created databases I hope you can point me in the right direction.
I'm not a frequent user of T-SQL, but I did some googleing, and found the script below. I corrected the script a bit.

I want the script to:

To select all databases, except the system DBs.
To set recovery to simple.
To shrink the log files for every db(.ldf), except the system db

The script:

USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)

declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
    begin
    select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
    select @isql = replace(@isql,'@dbname',@dbname)
    print @isql
    exec(@isql)
    select @isql='USE @dbname checkpoint'
    select @isql = replace(@isql,'@dbname',@dbname)
    print @isql
    exec(@isql)
    select @isql='DBCC SHRINKFILE @dbname.ldf'
    select @isql = replace(@isql,'@dbname',@dbname)
    print @isql
    exec(@isql)

    fetch next from c1 into @dbname
    end
close c1
deallocate c1

Use Script to Shrink Log files of all databases other than the system DBs.

USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO

DECLARE @DBName NVARCHAR(255),@LogicalFileName NVARCHAR(255),@DBRecoveryDesc Varchar(200)

DECLARE DatabaseList CURSOR
FOR
SELECT name,recovery_model_desc
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND is_read_only = 0
and database_id>4
ORDER BY name

OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @DBName,@DBRecoveryDesc
WHILE @@FETCH_STATUS = 0
BEGIN

SET @LogicalFileName=(SELECT top 1 name FROM sys.master_files AS mf WHERE DB_NAME(database_id)=@DBName and type_desc='LOG')

If @DBRecoveryDesc='Full'
Begin
     Print('Use ['+@DBName+']
            GO

           ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT
           GO

            DBCC SHRINKFILE ('''+@LogicalFileName+''',10)
            GO

            ALTER DATABASE ['+@DBName+'] SET RECOVERY FULL WITH  NO_WAIT
            GO ')
Print '----------------------------------------------------------- '
END

If @DBRecoveryDesc='Simple'
Begin
     Print('Use ['+@DBName+']
            GO

            DBCC SHRINKFILE ('''+@LogicalFileName+''',10)
            GO
 ')
Print '----------------------------------------------------------- '

END

         FETCH NEXT FROM DatabaseList INTO @DBName,@DBRecoveryDesc
      END
CLOSE DatabaseList
DEALLOCATE DatabaseList


SSRS trying to deploy to wrong server I added a report project to my existing solution in VS2015, built some reports and now I am trying to deploy the project to a report server. I have gotten the URL from the config manager ( http://MyServer/ReportServer_SQLEXPRESS ) and put that in the report project properties as the target server URL.

When I click Deploy I get an error saying "The specified report server http://localhost/reportserver could ot be found.

I have no idea where it is getting this localhost reportserver URL from, that is not what is saved in the properties. The Report project is inside a solution with an MVC project. Do I need to change something at the Solution level?

Turns out I had to run as VS as an administrator.  I guess because of the remote nature of the server.


SQL Server LockMatchID Command I am seeing calls like those shown below in my SQL Server Audit logs but have no idea what it means. A google search turned up a possible relationship with compiles/recompiles. The line (or similar lines) can also be found inside several SQL Server internal sprocs.

  EXEC %%Object(MultiName = @objname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)

Does anyone have an understanding of this process?

It's an internal method, presumably - from the name - used to lock an object based on its object_id and ensure that the executing user has the rights to make further changes to the object. This locking is required to do things like adding/dropping properties, hidden columns, etc., and to ensure that the object can't be dropped or altered by others in the meantime (mere mortals tend to do this by starting a transaction and locking the table through some indirect method, since there is no LOCK TABLE command - but clearly there is internal plumbing to help with system operations).

If you look at any of the procedures where this is used (there is a large list):

SELECT N'EXEC sp_helptext ''sys.' + QUOTENAME(name) + N''';'
FROM sys.all_objects
WHERE [schema_id] = 4
  AND LOWER(OBJECT_DEFINITION([object_id])) LIKE N'%lockmatchid%';

You'll see preceding comments like:

-- Lock the table schema and check permissions

-- Share lock default so it cannot be dropped

-- Since binding a default is a schema change, update schema count
--  for the object in the sysobjects table.

-- Ex-lock and check permission

-- Share Lock rule so that it cannot be dropped

-- Check that table and column exist

-- RESOLVE OBJECT NAME

-- Get exclusive object lock upfront so subsequent %%ColumnEx can proceed

-- Acquire sch-M lock up-front on the published object

-- LOCK PROC & CHECK PERMISSION --

-- LOCK, CHECK PERMISSION, BUMP SCHEMA FOR RECOMPILE  --

-- obtain owner-qual object name

-- Re-acquire schema mod lock to make the code more resilient to changes
-- although this must have been done inside sp_Mrepl_schema

-- LOCK TABLE, CHECK STANDARD TABLE-DDL PERMISSIONS

-- Since binding a default is a schema change,
--  update schema count for the object in the sysobjects table.

-- Update schema count for the object in the sysobjects table.

-- Verify that table exists

(And people say commenting code is a waste of time.)

There also seems to be LockExclusiveMatchID method, though the Exclusive argument seems to have later been added to LockMatchID, which made the exclusive method obsolete.

Not sure how much more specific you need to this to be - if you want to track down where this a high occurrence of these, you need to look at the outer calls, not this specific statement. For example, do you have an application that is renaming objects a lot, or calling sp_autostats directly, changing replication, or anything else you can infer from the list generated above? You'll have to figure out which procedures are getting called to help determine the why. And what you'll do about it (if you can even determine that there is a "problem") won't have anything to do with LockMatchID specifically.


View Default value for User Defined Table Type columns in SQL Server I have a user defined table type in SQL Server 2014 that I need to drop and recreate. However, if I use the script from SSMS, the default value constraints that are on some of the columns, will not be created.

I also have problems viewing the default constraint, so I do not what the default value should be.

Is there a way to view what those default value constraints contain?

CREATE TYPE [Logging].[LogElements] AS TABLE(
[LogMessage] [nvarchar](MAX) NOT NULL,
[MessageTypeId] [smallint] NOT NULL,
[Origin] [varchar](50) NOT NULL,
[JobRunId] [int] NULL,
[InvoiceId] [int] NULL,
[AccountlineId] [int] NULL,
[MSErrorCode] [bigint] NOT NULL,
[LogStatusId] [int] NOT NULL,
[LogUser] [varchar](50) NULL

)
GO

Above is the definition as it is created when generating script in SSMS. As you can see, the default values are not in the script.

Find your user-defined table type:

-- UDTT will have a 'TT prefix'
select * from sys.objects where type = 'TT' and name like '%LogElements%';

In my output, the object was called TT_LogElements_3A4CA8FD and had an object_id of 978102525.

Now we can use a script similar to Ryan Cooper's answer, by plugging that object_id into the WHERE clause, as the parent_object_id:

SELECT o.name AS [Constraint Name],
    cl.name AS [Column Name],
    s.name AS [Data Type],
    cl.max_length AS [Length],
    c.text AS [Default Value]
FROM sys.syscomments c
INNER JOIN sys.objects o
    ON c.id = o.object_id
INNER JOIN sys.columns cl
    ON o.parent_object_id = cl.object_id
        AND cl.default_object_id = o.object_id
INNER JOIN sys.systypes s
    ON cl.system_type_id = s.xtype
WHERE o.type = 'D'
    AND parent_object_id = 978102525;

This is my example output, which is fairly trivial to convert into a T-SQL script for recreating them.

[Constraint Name]               [Column Name]   [Data Type] [Length] [Default Value]
------------------------------------------------------------------------------------
DF__TT_LogEle__LogSt__3B40CD36  LogStatusId     int         4        ((0))
DF__TT_LogEle__LogUs__3C34F16F  LogUser         varchar     50       ('Test')


Pure (My)SQL transformations I'm trying to promote the usage of a database versioning system (http://code.google.com/p/flyway/) on the project I'm currently working on - and to that end when we make changes to the database, we're propagating those changes through a series of deltas. It's still very early doors at the moment, so we've had the luxury of being able to make "destructive" deltas and re-populate the DB post-delta.
We are nearing the point where we need to be able to restructure the DB with production data, and so I want to be able to ideally move data around the database within MySQL alone.

For example - I want to take the column contents of TABLE_1:the_value, create the column on TABLE_2 and then copy the values from TABLE_1 to TABLE_2 WHERE id_org and id_club match

TABLE 1
--------------------------------
| id_org | id_club | the_value |
--------------------------------
|   213  |    213  |    1      |
--------------------------------
|   214  |    213  |    2      |
--------------------------------
|   212  |    210  |    5      |
--------------------------------

-- intervening DDL that adds "the_value" to table 2
-- 2nd row on table 1 not copied across due to absence of matching id's

TABLE 2
--------------------------------------------
| id_org | id_club |    foo    | the_value |
--------------------------------------------
|   213  |    213  |     bar   |     1     |
--------------------------------------------
|   212  |    210  |     bar   |     5     |
--------------------------------------------

I've used the FOR EACH ROW construct with Triggers before, but cannot find documentation on the MySQL site that suggests or demonstrates how to use that to iterate over a result set in order to copy it to the target table. That may be more of a reflection of my inability to locate the appropriate information..

Can anyone offer me any pointers on this matter?

Assuming TABLE 2 already exists with id_org, id_club, and foo, just create the 'the_value' column and issue an UPDATE call using joins

See the UPDATE manual

UPDATE table1 t1, table2 t2 SET t2.the_value=t1.the_value
WHERE t1.id_org = t2.id_org AND t1.id_club = t2.id_club


SQL Change Tracking not tracking multi-record updates I have SQL Server change tracking switched on a table with about 3mil rows.

Switch on change tracking:

ALTER DATABASE FooDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = OFF)

ALTER TABLE [dbo].[fooTable]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF)

If I edit rows individually, all fine, a change is stored for each insert/update/delete.

If I run an update query that updates more than one row, I only get a change record for the SECOND row in the update.

So:

UPDATE fooTable SET name = UPPER(name) where id between 100 and 200

followed by (to ensure that an actual update occurs for each row)

UPDATE fooTable SET name = LOWER(name) where id between 100 and 200

results in a Change record for record 101 only.

SQL Express 2014

I have worked out what is going on.

The bad assumption I have made is that if a change causes 4 rows to be updated at once, that would result in the SYS_CHANGE_VERSION of the table going up by 4. It doesn't. Although there are four changes in the changetable, the version only goes up by 1 (it's a single change that changed multiple records.)

Anyone interested in testing it for themselves, can use the code below.

-- 1. Create Table
CREATE TABLE dbo.fooTable(
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Title] [nvarchar](20) NULL,
    [Surname] [nvarchar](128) NULL,
    [MiddleNames] [nvarchar](128) NULL,
    [Firstname] [nvarchar](128) NULL,
    [CreateDate] [datetime2](7) NOT NULL CONSTRAINT         [DF_CustomerProfile_CreateDate_I]  DEFAULT (getdate()),
    [LastModifiedDate] [datetime2](7) NOT NULL CONSTRAINT     [DF_CustomerProfile_LastModifiedDate_I]  DEFAULT (getdate()),
 CONSTRAINT [PK_fooTable_I] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

-- 2. Insert dummy data
insert into fooTable (Title, Firstname, MiddleNames, Surname, CreateDate,     lastModifiedDate)
values ('Mr','John',null,'Smith',GetDate()-5, getdate())
insert into fooTable (Title, Firstname, MiddleNames, Surname, CreateDate,     lastModifiedDate)
values ('Mrs','Mary','Beth','Jones',GetDate()-5, getdate())
insert into fooTable (Title, Firstname, MiddleNames, Surname, CreateDate,     lastModifiedDate)
values ('Ms','Thanh',null,'Nguyen',GetDate()-5, getdate())
insert into fooTable (Title, Firstname, MiddleNames, Surname, CreateDate,     lastModifiedDate)
values ('Dr','Lee','Evan','Oscars',GetDate()-5, getdate())

-- 3. Turn on Change Tracking
-- Database
ALTER DATABASE fooDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = OFF)

-- Tables
ALTER TABLE dbo.fooTable
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF)

-- 4. Check current version
Select CHANGE_TRACKING_CURRENT_VERSION() -- Should be Zero if you have never     turned change tracking on before.

-- 5. Make a single change
Update fooTable set MiddleNames = 'Arthur' where Surname = 'Smith'

-- 6. Check current version, should have gone up by 1
Select CHANGE_TRACKING_CURRENT_VERSION() -- Should be 1 if you have never turned change tracking on before.

-- 7. See the change, should be Type U on record with ID 1
DECLARE @version bigint
SET @version = CHANGE_TRACKING_CURRENT_VERSION()-1
Select CT.*
    FROM CHANGETABLE(CHANGES dbo.fooTable, @version) CT

-- 8. If everything is OK up to here, that's great. Now we have a problem.
-- Update all the rows in the table
Update fooTable set lastModifiedDate = Getdate() + 5

-- 9. Check current version, I expected it would go up by 4, one update for each record
Select CHANGE_TRACKING_CURRENT_VERSION() -- Should be 5 if you have never turned change tracking on before.

-- 10. View all changes for the table. There should be 4 (the first one will be gone now.)
Select CT.*
    FROM CHANGETABLE(CHANGES dbo.fooTable, 0) CT

-- Observe that each row has the same SYS_CHANGE_VERSION which means I need to deal with it and move on.


mysql picasa like photo tags store and query I want to make a photo website, allow users to upload photos and write 1-10 customs photo tags, like picasa. I use mysql.

Tags could be words or short sentence, but total length can not over 80 alphabets by each. Users can make the tags what they like.

I wanna to ask, what is the best way to design this kind of database? easy for store and query. BTW: Also need a easy query to see which tags are more popular(show 10 populars). Thanks.

If the number of tags allowed will never be greater than 10, then the simplest solution is to have ten 80-character fields attached to the photo table, named tag1, tag2,...

This is a very simple way to do it, but could lead to problems. If you want to have more or less tags in the future (and I see this sort of thing happen very often), you'll have to change the table structure. You also have to think of what happens when a user adds some tags, then removes one, then adds a different one: how do you decide what value to drop into which tag_n fields?

Another approach would be to normalize the tags and have a separate tags table, and then a tags_photos table to keep track of which tags have been applied to a specific photo.

This is slightly more complicated in terms of structures, but if you choose later to allow more tags, nothing needs to be done and if you decide to reduce the number of allowed tags, old photos could be permitted to keep their larger number of tags. You also remove redundancy by moving the tags to a separate table and referencing them by a tag_id.

Personally, I prefer the second approach. It's cleaner and more flexible.


How does table compression affect SQL Server's buffer pool? I'm curious as to how SQL Server's buffer pool is affected by compressed data tables.

Is the data in memory compressed, just as it is on disk, or is it fully decompressed?

If the data is stored compressed in the buffer pool, how much of the transient decompressed data is held in memory at a given time when executing a statement; a row/page, the entire table (assuming there is sufficient free pages to hold the data), or "it depends"?

When data is in the buffer pool it is compressed.  The data within the row/column needs to be decompressed when the row/column is read.  The storage engine team (the guys that wrote this stuff) did a blog post about this which has some good info in it.  (Yes I stole the above line from the blog post.)


Oracle Logical IO / Consistent Reads inconsistent? I use Logical IO to tune my Oracle queries and generally ignore wall-clock times as we have a large Oracle DB in a cluster with a total of about 80Gb SGA and an EMC cluster with tiered storage (Gigs of cache, SSD, Fiber disk and finally SATA)

This measure works for me, but I have noticed that sometimes the first Consistent Read value differs from subsequent ones (say 4000 IOs then 200 IOs where from them on the 200 IO remains consistent).

Whats up with that? Why is the the first request higher? I should be dealing with Logical IO and this should not be affected by whether the blocks we are looking for are in a buffer somewhere or whether we go directly to disk.

:)

To preempt your next question - I am looking at LAST_CR_BUFFER_GETS from Autotrace in SQL Developer.

My guess would be that the first query doing the extra work of delayed block cleanout because some other session at some earlier point in time modified (or inserted) the data without being able to do a commit cleanout.  That causes Oracle to do a bit of extra work the next time the dirty block is read to update its status.  Here is a good example of the impact of delayed block cleanouts on consistent gets.  I'm not 100% certain that delayed block cleanouts are reflected in LAST_CR_BUFFER_GETS from DBMS_XPLAN but I would certainly expect that they are.


Does @os_run_priority in sp_add_jobstep actually work in SQL Server 2008 R2? Does @os_run_priority in sp_add_jobstep actually work, in SQL Server 2008 R2?

It is described as "reserved" or "undocumented". However, I see it in the sp_add_jobstep definition:

@os_run_priority INT = 0, -- -15 = Idle, -1 = Below Normal, 0 = Normal, 1 = Above Normal, 15 = Time Critical)

This is part of the job step definition and you may even see it have values used or defined in other areas.

After taking a look in the source code (I work at Microsoft and have access), while the value is indeed passed as part of the job step information sent to each subsystem, I couldn't find a place that actually set the value as part of the job step execution. There are, however, threads that do run in different priority levels as part of SQL Server Agent and those threads may or may not help with job step functionality or subsystems that fill a specific role.

While I didn't do an exhaustive check, it would be a safe best to assume this value is - as described - "reserved". Just because it doesn't seem to be used doesn't mean it can't be at any other point as the plumbing exists.


LEFT JOIN Producing Inflated COUNT() numbers I have 3 tables that I am using a Right Join on.  The data is setup like below -
and my issue with it is that numbers being returned are inaccurate, such that when I execute the query I get this returned (which is double and triple the accurate value)

vendor     TotalSales   TotalCases
Vendor 1    61.40          6

but if you manually do the math it should be

vendor     TotalSales   TotalCases
Vendor 1    30.70          2

What must I change in my query so that the above results are returned?

  Declare @BBC Table
  (
    vendor varchar(250)
    ,vendorcasenum varchar(100)
    ,casenumdate date
  )

  Declare @AllVendor Table
  (
    vendor varchar(250)
  )

  Declare @TotalSalesAmt Table
  (
    vendor varchar(250)
    ,saleamt decimal(10,2)
  )

  Insert Into @TotalSalesAmt (vendor, saleamt) VALUES
  ('Vendor 1', '10.20'), ('Vendor 2', '10.10'), ('Vendor 1', '.40')
  ,('Vendor 1', '20.10'), ('Vendor 2', '20.10'), ('Vendor 3', '20.00')

  Insert Into @AllVendor (vendor) Values
  ('Vendor 1'), ('Vendor 2'), ('Vendor 3'), ('Vendor 4')
  ,('Vendor 5'), ('Vendor 6'), ('Vendor 7'), ('Vendor 8')
  ,('Vendor 9'), ('Vendor 10'), ('Vendor 11'), ('Vendor 12')
  ,('Vendor 13'), ('Vendor 14'), ('Vendor 15'), ('Vendor 16')
  ,('Vendor 17'), ('Vendor 18'), ('Vendor 19'), ('Vendor 20')

 Insert Into @BBC (vendor, vendorcasenum, casenumdate) VALUES
('Vendor 11',   'A12344',    '2017-01-19')
,('Vendor 10',  'A12311',    '2014-05-12')
,('Vendor 9',   'A12889',    '2015-07-10')
,('Vendor 8',   'A12988',    '2016-07-01')
,('Vendor 7',   'A12931',    '2012-03-07')
,('Vendor 6',   'A12199',    '2011-10-05')
,('Vendor 5',   'E12331',    '2011-10-11')
,('Vendor 4',   'E12391',    '2014-12-16')
,('Vendor 3',   'E12300',    '2011-07-15')
,('Vendor 2',   'E11001',    '2011-06-15')
,('Vendor 1',   'E12301',    '2013-11-06')
,('Vendor 1',   'E12221',    '2013-11-06')

Select
av.vendor
,TotalSales = SUM(ISNULL(tsa.saleamt,0))
,TotalCases = COUNT(bbc.vendorcasenum)
FROM @AllVendor av
LEFT JOIN @BBC bbc
ON av.vendor = bbc.vendor
LEFT JOIN @TotalSalesAmt tsa
ON tsa.vendor = av.vendor
GROUP BY av.vendor
ORDER BY av.vendor ASC

EDIT
I also tried using a CTE to achieve my desired outcome, but came out with the same incorrect results:

WITH tsa As
(
    Select
    vendor
    ,saleamt
    FROM @TotalSalesAmt
)
,BBC As
(
    Select
    vendor
    ,vendorcasenum
    FROM @BBC
)
Select
av.vendor
,TotalSales = ISNULL(SUM(tsa.saleamt),0)
,TotalCases = COUNT(bbc.vendorcasenum)
FROM @AllVendor av
LEFT JOIN TSA tsa
ON tsa.vendor = av.vendor
LEFT JOIN BBC bbc
ON bbc.vendor = av.vendor
GROUP BY av.vendor
ORDER BY av.vendor

If you run the query without aggregates, you'll see what is happening:

Select
    *
FROM @AllVendor av
    LEFT JOIN @BBC bbc
        ON av.vendor = bbc.vendor
    LEFT JOIN @TotalSalesAmt tsa
        ON tsa.vendor = av.vendor
where av.vendor = 'vendor 1'

+----------+----------+---------------+---------------------+----------+---------+
| vendor   |  vendor  | vendorcasenum |     casenumdate     |   vendor | saleamt |
+----------+----------+---------------+---------------------+----------+---------+
| Vendor 1 | Vendor 1 | E12301        | 06.11.2013 00:00:00 | Vendor 1 | 10,20   |
| Vendor 1 | Vendor 1 | E12301        | 06.11.2013 00:00:00 | Vendor 1 | 0,40    |
| Vendor 1 | Vendor 1 | E12301        | 06.11.2013 00:00:00 | Vendor 1 | 20,10   |
| Vendor 1 | Vendor 1 | E12221        | 06.11.2013 00:00:00 | Vendor 1 | 10,20   |
| Vendor 1 | Vendor 1 | E12221        | 06.11.2013 00:00:00 | Vendor 1 | 0,40    |
| Vendor 1 | Vendor 1 | E12221        | 06.11.2013 00:00:00 | Vendor 1 | 20,10   |
+----------+----------+---------------+---------------------+----------+---------+

Due there are 2 rows in @BBC table of Vendor 1:

('Vendor 1',   'E12301',    '2013-11-06')
('Vendor 1',   'E12221',    '2013-11-06')

The aggregated SUM(saleamt) = 61.40

You can use one subquery to calculate SUM(saleamt) and another to calculate COUNT(vendorcasenum):

Select
    vendor,
    (select ISNULL(SUM(tsa.saleamt),0)
     from @TotalSalesAmt tsa
     where tsa.vendor = av.vendor) TotalSales,
    (select COUNT(bbc.vendorcasenum)
     from @BBC bbc
     where av.vendor = bbc.vendor) TotalCases
FROM @AllVendor av
ORDER BY av.vendor ASC;

This is the final result:

+-----------+------------+------------+
|   vendor  | TotalSales | TotalCases |
+-----------+------------+------------+
| Vendor 1  | 30,70      | 2          |
| Vendor 10 | 0,00       | 1          |
| Vendor 11 | 0,00       | 1          |
| Vendor 12 | 0,00       | 0          |
| Vendor 13 | 0,00       | 0          |
| Vendor 14 | 0,00       | 0          |
| Vendor 15 | 0,00       | 0          |
| Vendor 16 | 0,00       | 0          |
| Vendor 17 | 0,00       | 0          |
| Vendor 18 | 0,00       | 0          |
| Vendor 19 | 0,00       | 0          |
| Vendor 2  | 30,20      | 1          |
| Vendor 20 | 0,00       | 0          |
| Vendor 3  | 20,00      | 1          |
| Vendor 4  | 0,00       | 1          |
| Vendor 5  | 0,00       | 1          |
| Vendor 6  | 0,00       | 1          |
| Vendor 7  | 0,00       | 1          |
| Vendor 8  | 0,00       | 1          |
| Vendor 9  | 0,00       | 1          |
+-----------+------------+------------+


How to track failed logon attempts using Unified Auditing I activated a ORA_LOGON_FAILURES policy and I've tried to execute

  select * from unified_audit_trail where unified_audit_policies = 'ORA_LOGON_FAILURES';

But it doesn't work. All my failed attempts to log in weren't audited. So what do I do wrong?

You have enabled ORA_LOGON_FAILURES, but have you enabled Unified Auditing properly?

With the default, mixed-mode auditing, setting audit_trail to none, prevents logon failures to be audited with ORA_LOGON_FAILURES enabled.

This is how it works with everything set to default (audit_trail set to DB by DBCA):

SQL> audit policy ora_logon_failures;

Audit succeeded.

SQL> select count(*) from unified_audit_trail
     where unified_audit_policies = 'ORA_LOGON_FAILURES' and return_code = 1017;

  COUNT(*)
----------
         0

SQL> connect 1/1
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> connect / as sysdba
Connected.
SQL> select count(*) from unified_audit_trail
     where unified_audit_policies = 'ORA_LOGON_FAILURES' and return_code = 1017;

  COUNT(*)
----------
         1

Also, Unified Audit entires can be buffered in the SGA and be written to disk later and not immediately, in that case, flush manually before querying:

EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

Still, if you configured everything properly, you may have hit:

BUG 19383839 - UNIFIED AUDIT - NO LOGON OR FAILED LOGON ACTION CAPTURED

Edit:

Ok, so I had to test it myself, else I would not have believed it. Opposing to what the documentation states (AUDIT_TRAIL), setting the audit_trail to none does have an effect even when using pure Unified Auditing. But this is not intended, it is a bug. The fix is not included in the latest PSU (12.1.0.2.170117), but installing the above one-off patch (19383839) indeed resolved the issue. The problem is, this patch is not available to Windows platform. (I have tested this both on Linux and Windows, because Windows is always a factor you need to consider when working with Oracle.)

Setting audit_trail to DB does not reenable mixed-mode, relinking (renaming the DLL) enables pure Unified Auditing and that overrides this, you can confirm this by selecting from AUD$ or DBA_AUDIT_SESSION. So I suggest that you set audit_trail to DB.


Replication pull job on subscriber stopped running when removing article from publication Version: SQL Server 2014 (Web edition)
Replication: Transactional

Came across an odd issue whereby the replication pull job on 10 of our 45 subscribers stopped running when removing article from corresponding publication.

This was completed through the GUI and didn't see any errors in distributor's SQL log.

Any ideas?

I don't know how your drop the article from publication, but you can assign again
just execute this

exec sp_addsubscription @publication='your_pub',@article='all',@subscriber='your_server'


SQL Server Web transaction replication to Azure SQL In Features for SQL Server 2016, I see that Transactional replication to Azure is supported for Web edition. Following the instructions leads to Configure Publishing and Distribution

In attempting to create the publication, I get This edition of SQL Server cannot act as a Publisher or Distributor for replication., which is expected as my server is Web edition.

So how do I set up Web edition replication to Azure SQL, given that it is a supported feature?

According to Xiaochen Wu (Senior Program Manager at Microsoft), there is an error in the documentation. Web edition does not support transaction replication to Azure SQL.


Many transactions are getting stuck in the "cleaning up" state - what could cause that? I'm trying to track down a locking issue, and found via SHOW PROCESSLIST that when the queries in question are waiting for locks and timing out, there are no other queries active in the process list.  So I looked at SHOW ENGINE INNODB STATUS, which showed that when the transactions in question are waiting on locks, the only other tansactions present that have started (there are many in thenot started state) are ones that have been running for 30 seconds or more, and are in the cleaning up state.

Why would a transaction get stuck in the cleaning up state? Is there any way to tell at that point, what any of the original queries were for that transaction? (The transaction does not appear to be present in the information_schema.innodb_trx table).

Example output:

---TRANSACTION 2670738352, ACTIVE 25 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 158609, OS thread handle 0x2ae9a6bcf700, query id 487681086 10.0.1.246 ownlocal cleaning up
Trx read view will not see trx with id >= 2670738353, sees < 2670727358
---TRANSACTION 2670736929, ACTIVE 40 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 158413, OS thread handle 0x2ae9a5842700, query id 487683091 10.0.1.203 ownlocal cleaning up
Trx read view will not see trx with id >= 2670737972, sees < 2670727358
---TRANSACTION 2670736875, ACTIVE 40 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 158415, OS thread handle 0x2ae9a64b3700, query id 487678962 10.0.1.203 ownlocal cleaning up
Trx read view will not see trx with id >= 2670736876, sees < 2670724054
---TRANSACTION 2670727358, ACTIVE 136 sec
3 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 1
MySQL thread id 158216, OS thread handle 0x2ae9a7679700, query id 487676960 10.0.1.110 ownlocal cleaning up
Trx read view will not see trx with id >= 2670735437, sees < 2670724054

Additional Info:

MySQL Version: 5.6
Storage Engine: InnoDB

I have the same issue on RDS. Some of "cleaning up" transactions blocked my ALTER TABLE query. And as I understood in MySQL 5.6 there is no way to find out which transaction should be killed.

So, my solution was to kill all "cleaning up" transactions. And it helped me.
And more thing: according to AWS doc on RDS you should use CALL mysql.rds_kill(pid) instead of standard KILL.


Is SQL Server's fill strategy the same for multi-file tempdb as multi-file (non-tempdb) filegroups? Will tempdb I/O from a single query be split across multiple tempdb files? (assuming that tempdb is configured to use multiple files, of course!)

For non-tempdb databases, MDSN seems to say that yes, newly-added data will be spread across multiple files in a filegroup:

  Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.

Does this same fill strategy apply to tempdb?

And does this answer depend on the type of query, e.g. parallel vs. non-parallel?  Or is the answer different based on the kind of tempdb I/O, e.g. for temp ables I create vs. tempdb usage by the database engine for worktables or spilling?

Does this same fill strategy apply to tempdb?

Yes it does.

To prove it to yourself you can do a test similar to one described here:

SQL Server Proportional Fill Algorithm Example

i.e. populate temp table with some amount of data and check space used for every tempdb file.

I cite here only the conclusion:

  File groups use proportional fill strategy across all the files within
  each file group and uses a round robin algorithm to write a
  proportional amount to the free space in the file and to each file
  within the file group.

  Understanding the proportional fill and round robin behavior in SQL
  Server is important not only for user databases, but especially in the
  case of tempdb as well. The SQL Server best practice is to have
  multiple tempdb data files to alleviate tempdb PFS and SGAM
  contention. It is easy to imagine when the tempdb data files sizes are
  uneven, then the extent allocations would be heavier on the bigger
  sized data files. This configuration would again cause a hotspot and
  not alleviate tempdb contention.

And here is a further reading:
Correctly adding data files to tempdb by Paul Randal

Among other things, Paul describes how to add files correctly to match existing file sizes, and the reason of doing so is proportional fill algorithm.


How to use --excludeCollectionsWithPrefix in mongodump I would like know how to use the option "--excludeCollectionsWithPrefix" of the mongodump ?

I have some collections name with numbers like this:

   registry.logging_event_15313531
   registry.logging_event_143432432
   registry.logging_event_18979865
   registry.logging_event_905998132

I would like remove of mongodump every collection with "registry.logging "

I don't know squat about MongoDB but after reading the docs something like the following is probably what you're looking for:

mongodump  --db test --excludeCollectionsWithPrefix registry.logging


What are the different ways to Warmup InnoDB Buffer Pool in MySQL 5.6? Warming up a cold MySQL instance is moving all data or the data with the highest probability of getting used to main memory. And, yes, by main memory, I mean InnoDB Buffer Pool. The Buffer Pool is configured as a large chunk of main memory, usually. Getting all the data from disk to main memory will take time. Rather than leaving it for the future, can the buffer pool be loaded in advance with the data most expected to be used at the earliest?

Assuming, you have a 7200 rpm HDD, if you perform random reads and expect the data to be loaded whenever it is required by the application, it will slow down the whole process. Random reads are way slower than sequential reads in case of HDD. So, rather the records must be loaded in sequential access.

It may take a while under a regular workload to store your entire working set data into the buffer pool, after restarting MySQL for instance. You can go with one of these two options:

Manual Warmup - Running SELECT queries against your InnoDB tables will load necessary pages into the memory, i.e., the buffer pool. SELECT COUNT(*) may be very useful, as it will load the whole clustered index into memory (actually as much as available). Secondary indexes may be loaded into the memory with simple queries, for instance by adding a catch-all, i.e., <> 0 WHERE clause on the first column of an index. Using a given index could be forced if needed.
Automatic Warmup - From 5.6, automatic buffer pool content dump at shutdown and restore on startup can be enabled - innodb_buffer_pool_dump_at_shutdown = ON, innodb_buffer_pool_load_at_startup = ON.

Reference - From Dual's article on InnoDB Warmup

Marcelo Altmann's article on MySQL Warmup


Columns names are case sensitive on postgres I have successfully migrated database from MySQL to PostgreSQL. While I am trying to connect with ofbiz, I am getting an error saying that

  "user_id" not found.

When I query against postgres by using

select * from user where user_id='1';

It's throwing the same error. But when I try with

select * from user where "USER_ID"='1';

It's fetching correctly. Table names are not case sensitive here, only column names. Can anyone give me a solution please?

When migrating, did the migration script double-quote the column names when creating tables? That would preserve the case.

You could either fix the migration script and re-import, or rename the columns e.g.

ALTER TABLE my_user RENAME COLUMN "USER_ID" TO user_id;

(note: user was used in the original question but that's a reserved keyword in Postgres so that couldn't have been one of the tables involved)


I am facing issue while decryption (Gives NULL value), While export/Import my Colum level encrypted data I am facing issue while decryption, Please help me if my column encryption/decryption process is wrong.

I encrypted table in Source DB and back up certificate, then exported my encrypted table result to CSV from source DB.

In the destination DB, I Created master Key, and using certificate backup (of my source DB) I created certificate in destination and then my symmetric key.

Now I imported the CSV result into my destination DB table and running my decryption script. But it gives me NULL value. (Decryption works fine in source DB, same is not working in destination).

Below is my sample script,

    ----AT MY SOURCE DB----
CREATE TABLE tbluser
  (
     id          INT,
     NAME        VARCHAR(200),
     encryptname VARBINARY(200)
  )

INSERT INTO tbluser
            (id,
             NAME)
VALUES      (1,
             'Raj'),
            (2,
             'Vimal')

CREATE master KEY encryption BY password = 'M@sterKey123'

CREATE certificate testcert1 WITH subject = 'Test my Certificate';

BACKUP certificate testcert1 TO FILE = 'D:\DESKTOP\Certificate\TestCert1.cer'
WITH private KEY ( FILE = 'D:\DESKTOP\Certificate\pkTestCert1.pvk', encryption
BY password = 'Certific@te123' );

CREATE symmetric KEY symkeytest1 WITH algorithm = aes_256 encryption BY
certificate testcert1;

OPEN symmetric KEY symkeytest1 decryption BY certificate testcert1;

UPDATE tbluser
SET    encryptname = Encryptbykey(Key_guid('SymKeyTest1'), NAME);

CLOSE symmetric KEY symkeytest1;

OPEN symmetric KEY symkeytest1 decryption BY certificate testcert1;

SELECT TOP 5 NAME,
             CONVERT(VARCHAR(50), Decryptbykey(encryptname)) DecryptedName
FROM   tbluser;

CLOSE symmetric KEY symkeytest1

DECLARE @str       NVARCHAR(2000),
        @path      NVARCHAR(200)='D:\DESKTOP\Certificate',
        @TableName NVARCHAR(100)='tbluser'

SET @str = 'BCP Test.dbo.' + @TableName + ' OUT ' + @path + '\'
           + @TableName
           + '.csv  -c -t^| -T -S MAILPTP45\SQL2012'

EXEC Xp_cmdshell
  @str

----AT MY DESTINATION DB----
CREATE master KEY encryption BY password = 'M@sterKey123'

CREATE certificate testcert1 FROM FILE = 'D:\DESKTOP\Certificate\TestCert1.cer'
WITH private KEY(FILE = 'D:\DESKTOP\Certificate\pkTestCert1.pvk', decryption BY
password = 'Certific@te123')

CREATE symmetric KEY symkeytest1 WITH algorithm = aes_256 encryption BY
certificate testcert1;

CREATE TABLE tbluser
  (
     id          INT,
     NAME        VARCHAR(200),
     encryptname VARBINARY(200)
  )

BULK INSERT [tbluser]
  FROM 'D:\DESKTOP\Test\tbluser.csv'
  WITH
    (
      fieldterminator = '|',
      rowterminator = '\n'
    )
OPEN symmetric KEY symkeytest1 decryption BY certificate testcert1;

SELECT TOP 5 NAME,
             CONVERT(VARCHAR(50), Decryptbykey(encryptname)) DecryptedName
FROM   tbluser;

CLOSE symmetric KEY symkeytest1

I think the solution to your problem can be found in this post (highlighting mine):

How to Create Two Identical Symmetric Keys

To summarize:

  SQL Server offers a sophisticated encryption key management system
  allowing for multiple levels of symmetric and asymmetric keys that
  protect each other. (See: The SQL Server Encryption Hierarchy)
  However, there is one use case that is only marginally supported by
  SQL Server: Creating the same symmetric key in two databases or even
  on two separate servers.

  There are only two methods to achieve this. The first is the use of an
  External Key Management solution in the form of an EKM module.
  However, EKM modules are often rather pricey investments and therefore
  not for everyone. The second method, and the only one not requiring
  external components, is a special form of the CREATE SYMMETRIC KEY
  statement. It allows us to specify a key source and an identity value.
  If identical values are used for these parameters, the resulting keys
  will be identical too.

CREATE SYMMETRIC KEY WITH KEYSOURCE Example

The extended syntax of the CREATE SYMMETRIC KEY statement is fairly straight forward, you just need to add the KEY_SOURCE and IDENTITY_VALUE clauses right after specifying the algorithm:

CREATE SYMMETRIC KEY SymmetricKey1
  WITH ALGORITHM = AES_256,
       KEY_SOURCE = '********************',
       IDENTITY_VALUE = 'SymmetricKey1'
  ENCRYPTION BY CERTIFICATE KeyProtection;

The key source is used to generate the actual key bits. It is using some form of key expansion algorithm. However, and that is my biggest concern with this method, which algorithm is used is not published. That means, while we can select the AES 256 algorithm for the key, if we actually get AES 256 level protection is unclear.

To clarify, the encryption algorithm used for an AES 256 key is always AES 256. However, if the key can be guessed easier than a random 256-bit key, you are dealing with a weakness. As we do not know how the key is derived, we also do not know how secure it is.
The Identity Value

The string that is specified in the IDENTITY_VALUE clause is not used for the generation of the actual key. It is only used to generate the key GUID that helps SQL Server identify the key necessary for decryption of an encrypted value. The DECRYPTBYKEY function does not allow us to specify the symmetric key to use for the decryption. Instead, SQL Server stores the GUID of the key that was used to encrypt the value as part of that value. That way, SQL Server will always know which key to use.

When looking at the documentation, you can get the impression, that the IDENTITY_VALUE needs to be kept a secret. However, it being unknown does not in any way increase security. If the KEY_SOURCE is known but the identity value isn't, we can just create a new key with a new GUID and then replace the key GUID within the encrypted value. As that value is just prepended to the actual cipher text, this is a simple string substitution operation.

  Summary

  The CREATE SYMMETRIC KEY statement allows us to specify a string as a
  KEY_SOURCE. That value is used to generate the actual key bits in a
  repeatable way. Therefore, we can use this feature to create the same
  key in different location.


Audit Trail in Oracle 11gR2 I'm using Oracle 11g R2 on RHEL5.

How can I find what is being audited? I want to know what actions (like create, insert, delete, update etc.) are being audited or logged for a user, say scott.

This topic is documented in the Oracle Security Guide at the section Finding Information About Audited Activities.

In your case you should find useful the catalog view DBA_OBJ_AUDIT_OPTS which displays the objects on which auditing options have been enabled.

Here is described the view structure.


Possible RMAN orphan files in backup directory I am trying to scrape together some space in my Oracle 11g backup directory in AIX.  I have deleted obsolete files using RMAN's "DELETE OBSOLETE" command, but I see many (just under 200) files dated from over two months ago although my backup policy should only be retaining backups for two days.  Here is a sample:

-rw-r-----    1 oracle   oinstall 21342912512 Sep 09 03:02 v1pi0pe8_1_1
-rw-r-----    1 oracle   oinstall 8866766848 Sep 09 07:23 v2pi0pe9_1_1
-rw-r-----    1 oracle   oinstall 13993369600 Sep 09 01:29 v3pi0pe9_1_1
-rw-r-----    1 oracle   oinstall 11262451712 Sep 09 00:16 v4pi0pe9_1_1
-rw-r-----    1 oracle   oinstall    1402368 Sep 08 19:15 v5pi0qa2_1_1
-rw-r-----    1 oracle   oinstall    1058304 Sep 08 19:45 v7pi0s2i_1_1
-rw-r-----    1 oracle   oinstall   42467840 Nov 22 22:41 v7po9ir3_1_1
-rw-r-----    1 oracle   oinstall   52970496 Nov 22 22:41 v8po9ir3_1_1
-rw-r-----    1 oracle   oinstall    1512448 Sep 08 20:15 v9pi0tqi_1_1
-rw-r-----    1 oracle   oinstall    9109504 Sep 08 20:45 vbpi0vj0_1_1
-rw-r-----    1 oracle   oinstall    1568256 Sep 08 21:15 vdpi11b4_1_1
-rw-r-----    1 oracle   oinstall  113734144 Nov 23 22:55 vepoc80o_1_1
-rw-r-----    1 oracle   oinstall    1021440 Sep 08 21:45 vfpi133g_1_1
-rw-r-----    1 oracle   oinstall   11592704 Sep 08 22:15 vhpi14rj_1_1
-rw-r-----    1 oracle   oinstall    1103360 Sep 08 22:45 vjpi16jv_1_1
-rw-r-----    1 oracle   oinstall    1811968 Sep 08 23:15 vlpi18c8_1_1
-rw-r-----    1 oracle   oinstall    1074176 Sep 08 23:45 vnpi1a4b_1_1
-rw-r-----    1 oracle   oinstall    1503744 Sep 09 00:15 vppi1bsh_1_1
-rw-r-----    1 oracle   oinstall    1081856 Sep 09 00:45 vrpi1dkr_1_1
-rw-r-----    1 oracle   oinstall    1534976 Sep 09 01:15 vtpi1fd4_1_1
-rw-r-----    1 oracle   oinstall    1088512 Sep 09 01:45 vvpi1h5e_1_1

Is there a way to clean up the old files?  (You can see there are some current files there also.  Those need to stay.)  I cannot find a way to tell whether they are needed for restores, although I don't know why they would be.  I have run crosschecks and everything seems OK.

Maybe those files are not cataloged by RMAN? I would CATALOG them and then DELETE OBSOLETE once again.


Is it okay to use rsync on InnoDB database if the MySQL server is shutdown? Is it okay to use rsync on InnoDB database if the MySQL server is shutdown ?

For example :

/etc/init.d/mysql stop
rsync -av /home/mysql/mydb/ root@newserver.com:/home/mysql

That really depends on what the directory structure underneath /home/mysql/mydb - if that's just the data files for one database, you'll need to extend your reach.

If you're going to use rsync (or take file-based backups in general), you'll need to make sure that your backup includes, at the very least:

Your innodb logfiles (ib_logfile*)
Your main innodb data file(s) (ibdata*)
All additional innodb data files (*.ibd) (if using innodb_file_per_table = 1)
All table definition files (*.frm) for your InnoDB tables

Obviously this only covers InnoDB - presumably you'll want to grab all of any other database files (MyISAM, Archive, CSV, etc.) as well.

MySQL's documentation on the topic also recommends issuing set global innodb_fast_shutdown = 0 before shutting down the server - this shouldn't be necessary, but it makes sense. That setting will force the server to flush your log files to your data files, and will speed up the recovery time significantly if you need to restore from backup.

(Strictly speaking, doing a slow shutdown means you should be able to get away without backing up your log files, but it's still a Good Idea, just in case it turns out that one day the server doesn't shut down cleanly for some reason)

I'm a big fan of Percona Xtrabackup for backups, as it generally makes sure that it copies all of the necessary files plus it can take the backups online, but there are definitely situations where rsync might be more suitable to your requirements.

Update: If you're using MySQL 5.6, you may want to check out InnoDB Transportable Tablespaces. This functionality is probably not going to be useful to you, and would require a bit of scripting if you have a lot of tables, but it can be really helpful if you use innodb_file_per_table and you're trying to avoid copying across all of the other InnoDB baggage.


Possible to ship logs to database with different name, but same structure? I'm struggling here. Using SQL Server 2008 Standard

Our environment has a prod SQL Server and a Test SQL Server. What I'm looking to do is take some processing power off of the production server and create a log shipped instance on the test server for reporting purposes. This database is located on both production and test. The test system is also used to test, so I can't restore the logs to the same database name as production because the database will be in a constant state of restoring or in read only mode and we need full read/write capability. I have another database in test called ProdDatabaseNameReporting that is a carbon copy of the one I'm trying to get the logs shipped from.

Here's what I'm trying to do and what I told it to do

ProdDatabaseName --------> ProdDatabaseNameReporting

Here's what is happening

ProdDatabaseName --------> ProdDatabaseName

Everything looks to be setup correctly. Then the log shipping procedure begins and it starts to overwrite the database with the same name instead of the database I told it to log ship to.

I know I can restore the database manually from production to test and it will restore correctly.

It's been a while since I've used the GUI, however you can do it using TSQL with the command sp_add_log_shipping_secondary

With this command you would specify the name of the secondary database and the primary, which would then restore the transaction logs to the relevant database name.

EXEC master.dbo.sp_add_log_shipping_secondary_database
@secondary_database = N'ProdDatabaseNameReporting'
,@primary_server = N'YourPrimaryDatabaseServer'
,@primary_database = N'ProdDatabaseName'
,@restore_delay = 0
,@restore_mode = 1
,@disconnect_users = 0
,@restore_threshold = 45
,@threshold_alert_enabled = 0
,@history_retention_period = 1440 ;
GO

Going through the full TSQL implementation of Log Shipping using TSQL from https://msdn.microsoft.com/en-us/library/ms190640.aspx would be a good place to start for you.


CONCAT() between different alphabet echoes out '&' ampersand I want to concatenate different alphabet strings and get a weird latin encoding with ampersands after the sixth greek character (I want to concatenate greek and english alphabet strings).

Lets say I have :

COL 1   COL 2
John    Johnson

and instead of John Johnson i get John Johnso&adasdasa

any help ?

You can use COLLATE :

concat(COL1 COLLATE latin1_general_ci,COL2 COLLATE latin1_general_ci)

latin1_general_ci is an example, try to figure it out what collation do u have.

But i think this should work, you could try latin1_general_cs too.


CASE WHEN DATE not providing correct results When using the below query the "Result_field" returns "NO" even if the date is greater than 12/31/2020.  How can I get it to show "YES" when the date is greater than 12/31/2020 and "NO" When Less Than?  Been trying to research this for a while and now need help.  This may be a simple query, but I'm kinda new at this.  Thanks in advance.

SELECT Date_field,
CASE
    WHEN Date_field < '2020-12-31' THEN 'NO' ELSE 'YES'
    end as Result_field
FROM Yearly_Report;

This is the result I get:

Without seeing the table definition, this is kind of an educated guess, but it looks like your Date_Field is a string field (VARCHAR, NVARCHAR etc) instead of a DATE or DATETIME field.

As per this fiddle, when defined as a VARCHAR field the results are all "NO" because the comparison is a string to string and doesn't make any sense (one string cannot be less than another string). When defined as a DATE field, the comparison value is implicitly converted to a DATE value and then can be successfully compared.

The correct solution would probably be to change the data type of the Date_Field column to DATE or DATETIME, however, if this isn't possible you can resolve this in the query alone using TRY_PARSE:

SELECT DateField,
CASE
    WHEN TRY_PARSE(DateField AS DATE USING 'en-US') < '2020-12-31' THEN 'NO' ELSE 'YES'
    end as ResultField
FROM Dates;

You can see the results in the fiddle are as expected:

DateField   ResultField
-----------------------
06/30/2022  YES
06/30/2018  NO
12/31/2022  YES
06/30/2020  NO
09/30/2020  NO
03/31/2020  NO
03/31/2024  YES
06/30/2021  YES


How SQL query are evaluated I ran into an issue on trying to optimise the following two query into 1 single query.

Result 1:

select *
from sip_trunks
where resporg_account_id_id=62
and identifier = (select action
                  from sip_trunk_numbers
                  where number='xxxxxxxxxx'
                  and resporg_id=80 limit 1)
limit 1

Result 2:

select *
from sip_trunks
where resporg_account_id_id=62
and sip_trunks.default=true
limit 1

Final Output = Result 1 + Result 2

Thus far I have managed to achieve this.

Note: I can't perform a UNION since I'm not sure whether RESULT 1 would yield any result or not (hence the flag matcher is used in below query. other than checking the rows returned) I know that Result 2 would always be there.

select *,
        (case
           when identifier=(select action from sip_trunk_numbers where number='xxxxxxxxxx' and resporg_id=80 limit 1)
             then 1
           when sip_trunks.default=true
             then 2
           else 3
         end) as matcher
from sip_trunks
where resporg_account_id_id=62
order by matcher asc
limit 2;

Now I'm stuck.

a: I can't group the result based on the matcher as Postgres complain the '*' is not used in the group.

But First I'm not sure how bad is the subquery is (select action from sip_trunk_numbers where number='xxxxxxxxxx' and resporg_id=80 limit 1)

So does using a subquery as column would make it run for all the rows or it would run for rows for which the where conditions matched.

In other word does the subquery would run for all rows of sip_trunks or it would only run for rows for which the resporg_account_id_id=62 matches.

Why don't you use an OR condition?

select *
from sip_trunks
where resporg_account_id_id = 62
  and (   sip_trunks.default = true
       or identifier = (select action
                        from sip_trunk_numbers
                        where number = 'xxxxxxxxxx'
                        and resporg_id = 80
                        limit 1))

limit 1

If you need to know which condition matched, you can use a UNION:

select *
from (
  select st.*, 1 as source
  from sip_trunks st
  where resporg_account_id_id = 62
    and identifier = (select action
                      from sip_trunk_numbers
                      where number = 'xxxxxxxxxx'
                        and resporg_id = 80
                      limit 1)
  limit 1

  union all

  select st.*, 2 as source
  from sip_trunks st
  where resporg_account_id_id = 62
    and sip_trunks.default = true
  limit 1
)
order by source
limit 1

If source equals one in the result the first query matched, otherwise the second.

Alternatively you could use an outer join:

select st.*,
       stn.action is not null as identifier_matched
from sip_trunks st
  left join (
    select action
    from sip_trunk_numbers
    where number = 'xxxxxxxxxx'
      and resporg_id = 80
    limit 1
  ) stn on stn.action = st.identifier
where resporg_account_id_id = 62
  and (stn.action is not null or sip_trunks.default = true)
limit 1

Note that limit without an order by usually makes no sense.


How do I use "OLD" or "NEW" in a dynamic SQL statement in an on-update trigger? Assuming I have the following function being executed on a row-update:

CREATE OR REPLACE FUNCTION versioned_update()
RETURNS TRIGGER AS $$
DECLARE
     sql TEXT;
BEGIN
    sql := 'INSERT INTO backup_table VALUES (OLD)';
    EXECUTE sql;
END;
$$ language 'plpgsql';

The above example does not work because OLD is unknown in that execution context. So I tried something like:

sql := format('INSERT INTO backup_table VALUES (%L)', OLD);

and

sql := format('INSERT INTO backup_table VALUES (%L)', (OLD));

and

sql := format('INSERT INTO backup_table VALUES (%L)', (OLD.*));

All without any luck.

This issue is part of a larger trigger I am working on. Looked in isolation it does not make much sense, but illustrates the question.

After a lot of trial and error I've finally figured it out. The trick was the notation for composite data types (and more importantly their usage) which eventually led me to this:

CREATE OR REPLACE FUNCTION versioned_update()
RETURNS TRIGGER AS $$
DECLARE
     sql TEXT;
BEGIN
    sql := 'INSERT INTO backup_table VALUES $1.*';
    EXECUTE sql USING OLD;
END;
$$ language 'plpgsql';


how to use with clause results as a temporary table +----+-----------+--------------+-----------+
| id | file_name |  file_value  | file_type |
+----+-----------+--------------+-----------+
|  1 | file1     |  8,624       | 1         |
|  2 | file2     |  0,025       | 1         |
|  3 | file3     |  1,005       | 1         |
|  4 | file4     |  0,655       | 2         |
|  5 | file5     |  1,251       | 2         |
|  6 | file6     |  0,148       | 2         |
|  7 | file7     |  1,146       | 3         |
|  8 | file8     |  2,485       | 3         |
|  9 | file9     |  0,512       | 3         |
+----+-----------+--------------+-----------+

+--------+
| lineal |
+--------+
; WITH vw AS
(SELECT id, UPPER(file_name) [Name], CAST(file_value AS INT) [Value]
 FROM test_example
 WHERE id % 2 = 0 )

SELECT vw2.* , vwt.* FROM vw AS vw2
CROSS APPLY (SELECT SUM(vw.file_value) AS type_total FROM vw AS cavw
             WHERE vw2.file_type = cavw.file_type ) AS vwt ;

+----------------+
| this IS faster |
+----------------+
; WITH vw AS
(SELECT id, UPPER(file_name) [Name], CAST(file_value AS INT) [Value]
 FROM test_example
 WHERE id % 2 = 0 )

 SELECT * INTO #example FROM vw --or @example AS table...

SELECT vw2.* , vwt.* FROM #example AS vw2
CROSS APPLY (SELECT SUM(vw.file_value) AS type_total FROM #example AS cavw
             WHERE vw2.file_type = cavw.file_type ) AS vwt ;

Any way to optimize speed for firt query?

You can write this query without using a CTE or temp table:

SELECT id,
       UPPER(file_name) [Name],
       CAST(file_value AS INT) [Value],
       SUM(CAST(file_value AS INT)) OVER (PARTITION BY file_type) AS type_total,
       AVG(CAST(file_value AS FLOAT)) OVER (PARTITION BY file_type) AS type_avg
FROM test_example
WHERE id % 2 = 0

Is that any better?


ALTER fixed-length COLUMN on compressed table I wonder if compression of a table actually changes ALTER TABLE rules. I found no information on this topic. Let's consider a simple example. I'll create a table and alter one of the columns from INT to BIGINT.

CREATE TABLE dbo.test1
(
  c1 int not null,
  c2 int not null,
  primary key (c1)
)

Physical row looks just like we can suggest, two fields, 4 bytes each.

SELECT pc.leaf_offset, pc.max_length
FROM sys.system_internals_partitions p
  join sys.system_internals_partition_columns pc
    on p.partition_id = pc.partition_id
WHERE p.object_id = object_id('dbo.test1')

leaf_offset max_length
4           4
8           4

Let's change c2 from INT to BIGINT

ALTER TABLE dbo.test1 ALTER COLUMN c2 bigint not null

We increased the length of a fixed-length column so SQL Server adds one more column at the end of the row. The same query shows

leaf_offset max_length
4           4
8           4
12          8

So far so good, everything works just like books and numerous articles describe. Let's try to do the same thing with a compressed table.

CREATE TABLE dbo.test2
(
  c1 int not null,
  c2 int not null,
  primary key (c1)
) with (data_compression = page)

The physical structure looks like this:

leaf_offset max_length
-1          4
-2          4

All 'offsets' are negative. Only variable-length columns suppose to have negative offsets. It surprised me at first, I never seen this documented. It makes sense though, the compressed page can't have fixed offsets from the beginning of the row. Now let's try to alter the column.

ALTER TABLE dbo.test2 ALTER COLUMN c2 bigint not null

The second surprise, the 'new' column has not been added.

leaf_offset max_length
-1          4
-2          8

One more surprise, it looks like on SQL Server 2017 this operation is 'metadata-only', still about to confirm it. SQL Server 2014 handles it in the old way.

My questions

Do we have any document available on how table compression affects ALTER TABLE? Who can shed some light on it?
Does SQL Server 2017 improve this?

I have read this sample chapter from Microsoft SQL Server 2012  Internals. It covers the column descriptor row format for compressed data, but covers only physical structures involved and doesn't touch schema changes at all.

Documentation

There is no documentation I am aware of for metadata-only ALTER COLUMN operations on compressed tables, beyond the very general statement:

  Changes you specify in ALTER TABLE implement immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows.

One could interpret that as meaning that SQL Server may avoid a size-of-data operation if the particular storage format in use means the data does not change. Continuing in that generous vein, one could argue that whether a particular change is metadata-only or not depends on what has been implemented as well as what is logically possible.

Documentation notes were added when SQL Server 2012 added support for adding a NOT NULL column with a runtime constant default value, and the documentation has been updated for new ONLINE operations. If you feel documentation would be useful for metadata-only data type changes on compressed tables, you should submit feedback. I would not personally want to be the person tasked with that effort, or keeping it up to date.

In the meantime, I wrote what I have been able to discern about this behaviour in New Metadata-Only Column Changes in SQL Server 2016.

Does SQL Server 2017 improve this?

From my testing, the new behaviour was enabled in SQL Server 2016. It can be disabled with undocumented startup or global trace flag 3618:

DBCC TRACEON (3618, -1);

A demo:

CREATE TABLE dbo.test1
(
  c1 bigint NOT NULL,
  c2 integer NOT NULL,
  c3 char(4) NOT NULL
  primary key (c1)
)
WITH (DATA_COMPRESSION = ROW);

INSERT dbo.test1 WITH (TABLOCK)
(
    c1,
    c2,
    c3
)
SELECT
    N.n,
    CONVERT(integer, N.n),
    LEFT(N.n, 4)
FROM
(
    SELECT
        n = ROW_NUMBER() OVER (ORDER BY @@SPID)
    FROM sys.all_columns AS AC1
    CROSS JOIN sys.all_columns AS AC2
    ORDER BY n
    OFFSET 0 ROWS
    FETCH FIRST 5 * 1000 * 1000 ROWS ONLY
) AS N;

-- Both metadata only
ALTER TABLE dbo.test1 ALTER COLUMN c2 bigint NOT NULL;
ALTER TABLE dbo.test1 ALTER COLUMN c3 char(8) NOT NULL;

DROP TABLE dbo.test1;


Use flag on each row to mark record as primary or use foreign key on parent table I came across pretty simple and probably very basic problem, and I can't decide what solution is the best.

After simplifiyng my scheme to the bone, I have this:

table of Suppliers (id, name)
table of Parts (id, name)
m to n table PartSuppliers(supplier_id, part_id, price)

I need to mark one of the suppliers as primary supplier for given part. I see two options:

1) (currently used)
Add primary_supplier_id column to Parts table

2) Add column is_primary to PartSuppliers table

Both solutions have some pros and cons. Solution 1) there are problems with validation and CRUD - info about part suppliers is on "two places".  Solution 2 would be easier to use in app, but isn't it breaking normalization?

Which solution you chose is largely a matter of personal preference. I have a strong preference for the foreign key approach, and think I can provide good reasons why.

First of all, check that your requirement is strictly to capture a single "primary supplier", and not (potentially multiple) "preferred suppliers". If you can't guarantee that you'll never have more than 1, the foreign key approach will break and you're better off going with the flag approach from day 1.

If, however, a part always has a single primary supplier I would use the foreign key approach. If a part must have a primary supplier, then this is the only approach where the database constraints guarantee that the data is always correct -- make the foreign key column mandatory (not null) and you will have to provide the primary supplier when adding the part. There is simply no way that the data cannot be correct.

With the flag approach, the primary supplier information can be missing from parts -- unless you want to rely on your application's logic. If that's potentially a problem, the mandatory foreign key approach is the easiest way to ensure this can never happen.

Think also about updates. With the flag approach, you need to clear the flag from other "part supplier" records when you change preferred suppliers.


Is there any open source GUI tool to monitor cassandra? I would like to monitor cassandra database as well as need to check the values in the database.

Could any one suggest me best open source GUI tool ?

Regards,

Arun Raj.

Have you tried Helenos:
https://github.com/tomekkup/helenos

It seems good


To do a refresh of a "on premise" postgres database to a azure postgres database or to do a backup and restore? We have a postgres database hosted on Azure, that we would like to refresh from a "on premise" postgres database.

I'm not sure, what would be the best way to do that?

Through an incremental backup then an incremental restore or through a Slony-I replication although I'm quite sure this feature do not exist for Azure?

Thanks

In general most DB as a service providers make it difficult to do replication to/from them to your own box so replication, at least native postgres replication, likely won't work.

That leaves you with going through replication solutions that are not so tighly integrated such as Slony, Bucardo, pgpool.

My advice would be to first describe your needs and then use that information to decide which approach would be easiest to maintain, which in my opinion is far more important than easy to install.

Do you need to have the data up to date at all times? Then you want some means of replication.
Can the data be behind hours or a day? You can use a process to get the data from the primary to the Azure machine without replication.
Does the data gets updates or is it insert only? Insert only tables are far easier to replicate.
Do you have, or can setup, insert_date and last_update dates/timestamp fields? If you have a way to know what is new and/or updated since your last migration you can do your own process with the advantage that it will work regardless of what provider you use. One, potentially big, disadvantage is that you have to manage this yourself for every single table. If your environment is mostly mature and you are not adding new tables often, this is one potential method.
Is the data for some, or all, of the tables coming from some external source? In a case like that you can double load, were each source gets loaded on both places. Biggest disadvantage is that you have to replicate your processes for the load and monitoring as well as coming up with a way to make sure there are no discrepancies.

Usually the deciding factor for this type of designs are how far behind the data can be and everything, for the most part, usually is based on that.

The other important factor is size. If you are talking a handful of MBs then the process is trivial, no matter which way you go. If you are talking Terabytes then incremental or replication is an absolute must.


Does Percona Xtrabackup support RedHat release 4 I downloaded Percona's Xtrabackup rpm file for RedHAT. I attempted to install but hit into an error. The error seems to point to missing dependencies and libraries. I have no issue with RedHat 6 OS. I could not find the binaries in Percona's website for RedHat4.

Is it supported in RedHat 4? Possible for a workaround?

rpm -Uvh percona-xtrabackup-2.2.9-5067.el6.x86_64.rpm
warning: only V3 signatures can be verified, skipping V4 signature
error: Failed dependencies:
        libc.so.6(GLIBC_2.10)(64bit) is needed by percona-xtrabackup-2.2.9-5067.el6.x86_64
        libz.so.1(ZLIB_1.2.0)(64bit) is needed by percona-xtrabackup-2.2.9-5067.el6.x86_64
        perl(DBD::mysql) is needed by percona-xtrabackup-2.2.9-5067.el6.x86_64
        rpmlib(FileDigests) <= 4.6.0-1 is needed by percona-xtrabackup-2.2.9-5067.el6.x86_64
        rtld(GNU_HASH) is needed by percona-xtrabackup-2.2.9-5067.el6.x86_64
        rpmlib(PayloadIsXz) <= 5.2-1 is needed by percona-xtrabackup-2.2.9-5067.el6.x86_64
    Suggested resolutions:
        /var/spool/up2dateperl-DBD-MySQL-2.9004-3.1.x86_64.rpm

Percona XtraBackup 2.2 does not seem to have support out of the box for Red Hat 4:

  Supported Releases

  The CentOS repositories should work well with Red Hat Enterprise Linux
  too, provided that yum is installed on the server.

CentOS 5 and RHEL 5
CentOS 6 and RHEL 6
CentOS 7 and RHEL 7
Amazon Linux AMI (works the same as CentOS 5)

Try, however, first the generic Linux binary and see if you can at least run the xtrabackup binary.

If not, you can either try to compile it by yourself (it shouldn't be too difficult in terms of dependencies, but Xtrabackup is a monster source and you may en up in dependency hell) or hire their paying support/development so they can make it work for you (they have great engineers).


SQL Server slow response when accessed from a remote client We have a SQL Server in a remote location. If we access the server from the same location (internal IP, same IP as the server is using) the performance is good. We don't face any problem with connectivity.

But whenever we try to connect from a remote client (public IP) we are faced with a timeout error.

I have made an etc/host entry for the server.

What might be the reasons for such problems?

I have connected via ODBC in Management Studio. The connection has been established. I am facing the problem only when executing queries (any query).

By default SQL Server instances don't listen for TCP/IP connections, so verify that this is enabled:

https://technet.microsoft.com/en-us/library/hh231672(v=sql.110).aspx
https://stackoverflow.com/questions/2388042/connect-to-sql-server-2008-with-tcp-ip

Also you need to make sure that firewalls between your client and the server are not blocking the connection. For the default instance under standard configuration TCP port 1433 needs to be open. If you have multiple instances then things get more complicated. See https://msdn.microsoft.com/en-us/library/cc646023.aspx for more details.

These are the most common cuases of complete failure to connect to a remote server. If all this is configured correctly then, as Tom V suggests, we need more information in order to be able to help.

Warning: It is strongly recommended that you do not open your SQL server ports to the Internet at large. If you allow connections in from the outside network then make sure you limit it to specific source IP addresses. If you can not do this because the source address(es) are variable (for example if you need to connect from a laptop that moves between networks a lot) then consider instead setting up a VPN to allow access through the firewall that way instead of directly. Even for a single fixed source address, I would still tend to prefer the VPN option.


Transactional versus Merge Replication (Restore backup after downtime) I have the following transactional replication setup:

Windows Server 2008 with SQL Server 2008 Enterprise

Server A: Master, has a database with a table, publisher
Server B: Slave, subscriber (Backup server)

For backup (and read only access to B during down time), transactional replication is a good solution.  However, during downtime of A, I want the application to read and write to the database on B.  It is possible to backup B and restore the content to A, but is a merge replication a better way?

for backup purposes I would implement database mirroring. if read access is required then using database snapshots could help.

http://technet.microsoft.com/en-us/library/ms175511.aspx

i believe that merge replication would work to eliminate the need to backup/restore from B to A, but the performance penalty would be considerable. if the base is small then it would be fine.


SQL Server CTE problem This question is in reference to my last question: SQL Server 08: Union over while

I'm writing a CTE to do what was described in that question (find available time slots over a given number of days.

I have a CTE written, but I can't quite grasp how to get my date incrementing and limited. As it stands, it only gets data from the 2011-07-30 (@ArrivalDate). Can anybody explain to me how I can get it properly incrementing, and where I'm going wrong?

Update: I've come up with a better query for getting all the available time slots

(SELECT TS.UKTNN, TS.TimeSlotID, TS.TimeSlotGroupID
 FROM FittingCentreTimeSlotGroupTimeSlots TS)
EXCEPT
(Select BFD.UKTNN, BFD.TimeSlotID, BFD.TimeSlotGroupID
 From BookedFittingDates BFD)

And here is the table structure as requested in the comments:

TimeSlotGroup
UKTNN, TimeSlotGroupID, TimeSlotGroupDesc

TimeSlotGroupTimeSlots
UKTNN, TimeSlotID, TimeSlotGroupID, TimeSlotDesc

BookedDates
UKTNN, TimeSlotGroupID, TimeSlotID, Date

Here is what my existing CTE looks like right now

Use[DEV_UKTN_DATA]
GO
DECLARE @UKTNN int;
DECLARE @ArrivalDate date;
DECLARE @NumDays int;
DECLARE @LoopDate date;

SET @UKTNN = 7;
SET @ArrivalDate = '2011-07-30';
SET @LoopDate = @ArrivalDate;
SET @NumDays = 2;

WITH GetTimeSlotsOnDays(TimeSlotGroupID, TimeSlotGroupDesc, TimeSlotID, TimeSlotDescription, AdditionalCost, UKTNN, CurrentDate, Distance)
AS
(
    SELECT TSG.TimeSlotGroupID,
           TSG.TimeSlotGroupDesc,
           TS.TimeSlotID,
           TS.TimeSlotDescription,
           TS.AdditionalCost,
           @UKTNN AS UKTNN,
           @ArrivalDate as CurrentDate,
           0 As Distance
    FROM FittingCentreBranchTimeSlotGroups TSG
    INNER JOIN FittingCentreTimeSlotGroupTimeSlots TS
               ON TSG.TimeSlotGroupID = TS.TimeSlotGroupID AND
                  TSG.UKTNN = TS.UKTNN
    WHERE TSG.UKTNN = @UKTNN AND
          TS.UKTNN = @UKTNN AND
          TS.TimeSlotGroupID = TSG.TimeSlotGroupID AND
          NOT EXISTS(
                SELECT *
                FROM BookedFittingDates BFD
                WHERE BFD.TimeSlotGroupID = TS.TimeSlotGroupID AND
                      BFD.TimeSlotID = TS.TimeSlotID AND
                      BFD.UKTNN = @UKTNN AND
                      BFD.Date = @ArrivalDate)
          --@ArrivalDate <= DATEADD(DAY, @NumDays, @ArrivalDate)

    UNION ALL --Recursion starts here

    SELECT TSG.TimeSlotGroupID,
           TSG.TimeSlotGroupDesc,
           TS.TimeSlotID,
           TS.TimeSlotDescription,
           TS.AdditionalCost,
           @UKTNN UKTNN,
           @ArrivalDate As CurrentDate,
           Distance + 1 As Distance
    FROM FittingCentreBranchTimeSlotGroups TSG
    INNER JOIN FittingCentreTimeSlotGroupTimeSlots TS
               ON TSG.TimeSlotGroupID = TS.TimeSlotGroupID AND
                  TSG.UKTNN = TS.UKTNN
    INNER JOIN GetTimeSlotsOnDays as GTSOD
               ON GTSOD.CurrentDate = DATEADD(DAY, Distance, @ArrivalDate)
)

SELECT *
FROM GetTimeSlotsOnDays GTSOD
WHERE GTSOD.CurrentDate = @ArrivalDate AND GTSOD.Distance = 0
ORDER BY GTSOD.TimeSlotGroupID ASC, GTSOD.TimeSlotID ASC
GO

I found an answer for this!

1) Create a function that returns a table containing the desired dates

2) Do a cartesian product of the timeslots with the dates

3) Find the exceptions between the booked dates and the cartesian product


mysql 5.5 corrupt - missing temporary table One of our mysql instances crashed and failed to start:

120422 10:04:13  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './databasename/#sql-29f6_45.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

I've failed to understand what should I do in order to remove this temporary table from the TABLESPACE.

How should I proceed?

There are those rare times when an InnoDB table gets stuck in the ibdata1  deeply that you must try forecing mysql to start up in spite of it.

Try using innodb_force_recovery in /etc/my.cnf (Option values 1-6)

[mysqld]
innodb_force_recovery = 1

then restart mysql.

This should at least allow a mysqldump

mysqldump -u... -p... --routines --triggers --all-databases > /root/MySQLData.sql

Once you have mysqldumped all data, remove the innodb_force_recovery

Then, perform a complete cleanup of InnoDB as follows

service mysql stop
mv /var/lib/mysql /var/lib/mysql_old
mkdir /var/lib/mysql
mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_old/mysql/* /var/lib/mysql/mysql/.
chown -R mysql:mysql /var/lib/mysql
service mysql start
mysql -u... -p... < /root/MySQLData.sql

After service mysql start, new InnoDB files (ibdata1, ib_logfile0, ib_logfile1) are created

Give it a Try !!!


Stop on error in PL/SQL developer or Oracle SQL Developer At this time I am using TOAD to apply SQL*Plus scripts and there is an option to stop on the first error. After it I can fix the script and apply the remaining part.

Due to license issues we can't use TOAD anymore and we have a choice between Oracle SQL Developer or PL/SQL Developer. Is it possible to set such behaviour there?

For example, Aqua Data Studio just shows all errors at the end of execution, but it doesn't stop on them. Thanks!

When you process a script in SQL Developer using the "Run Script" button (not the "Run Statement" button), it uses SQL*Plus internally to execute your script.  Therefore using a SQL*Plus control setting which stops processing on errors should work. Try adding to the top of your script line like this:

WHENEVER SQLERROR EXIT FAILURE


How to reclaim back space after deleting objects on SQL Server? I have a database runnin on SQL Server 2008 that is now out of space, errors like "Could not allocate a new page for database 'database' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup." are common.

If I query the FILEPROPERTY('SPACEUSED') for the only file on the PRIMARY filegroup I get   less than 10MB which is barely 0.1% space free left.
So, system has some reason in complaining.

I investigated the space occupied by tables by right clicking on database on SSMS, running some reports on disk usage and detected that there was a table consuming lots of space. I fail to understand why, after TRUNCATE ing the table, FILEPROPERTY('SPACEUSED') returns precisely the same (very small) amount. Unless I am mistaken, TRUNCATE works by, under the hood, dropping and recreating the table

The standard reports on disk usage report that table ceased to occupy a big amount of space, though. I expected the FREE space to increase and the ugly "Could not allocate a new page..." message to go away, but not so.

What am I missing here?

I assume the table in question is a heap. You can rebuild the table to reclaim the space, or create a clustered index.

ALTER TABLE dbo.table_name REBUILD;

And/or

CREATE CLUSTERED INDEX cix ON dbo.table_name(column_name);

You can also drop the table and re-create it.


Postgres: how to tell if insert took place I have a (working) stored procedure that incorporates logic disallowing dups. However, I cant determine a way to have the stored procedure tell me if a row was inserted or not. I can think of a terrible hack - the table has a sequence, and compare the sequence current value before and after the insert.  At least I think its a hideous hack...

CREATE OR REPLACE FUNCTION mysp() RETURNS integer AS
$BODY$
DECLARE
BEGIN
    -- only 1 row gets in
    INSERT INTO mytable (locationname) VALUES ('some text')
    WHERE NOT EXISTS (SELECT 1 FROM mytable
    WHERE locationname='some text');
    -- cant figure out how to do something like RETURN ROWSINSERTED();
END
$BODY$
LANGUAGE 'plpgsql' ;

Try this

 CREATE OR REPLACE FUNCTION mysp() RETURNS integer AS
 $BODY$
 DECLARE
 aff_rows numeric;
 BEGIN
     execute 'INSERT INTO mytable (locationname) VALUES (''some text'') WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE locationname=''some text'')';
     GET DIAGNOSTICS aff_rows = ROW_COUNT;
     raise info 'affected rows :: %', aff_rows;
     return aff_rows;
 END
 $BODY$
 LANGUAGE 'plpgsql' ;

Source: https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

(refer section: 39.5.5. Obtaining the Result Status)


Is there a way to setup a user to run SPs, but not access the underlying data tables? We have two database instances on two separate server boxes that are linked. The website user needs to be able to run stored procedures on DatabaseA which query DatabaseB tables, however it should not be able to read directly from tables on DatabaseB.

Is such a thing possible in Sql Server 2005?

Simply creating a Stored Procedure and giving the web user permissions to execute it returns an error of Access to the remote server is denied because no login-mapping exists.

I really don't want to give the website user account free access to DatabaseB tables.

Update

Aaron's suggestion of using EXECUTE AS OWNER does not work because Owner doesn't have access to DatabaseB.

Using EXECUTE AS 'UserWithPermissions' returns an error of Access to the remote server is denied because the current security context is not trusted. Looking this up online leads me to believe this is because the database is not marked as TRUSTWORTHY, so the remote database rejects the connection. I suspect it would work fine if the two database instances were on the same server.

I did not want to set the web database as TRUSTWORTHY since some of the security on it is more relaxed, so I went with Aaron's other option: Create a User on DatabaseB that only has access to a few specific views containing the data the website should access, then go into the Security Properties on the Linked Server object and setup the website user to login as the new limited user when accessing the linked server.

This allowed my website user to have limited access to specific data in the private database, without giving it access to any of the underlying tables.

Couldn't you have procedure A run with execute as owner or execute as 'db user tied to security account associated with linked server'? That way the rights of the caller don't have to transfer...

Or, you can create a login on the remote server that only has the ability to execute the queries you want, and add an impersonation account to the existing linked server on Server A to have the local webserver login impersonate the new login on server B.