Command fails as CmdExec job but succeeds from console I have a SQL Server 2014 Agent Job (Type: "Operating system (CmdExec)", Run as: "SQL Server Agent Service Account", Owner: sa) that executes the following command:

powershell -ExecutionPolicy Bypass -Command "$objServiceManager = New-Object -ComObject 'Microsoft.Update.ServiceManager'; $objService = $objServiceManager.AddService2('7971f918-a847-4430-9279-4a52d1efe18d',2,''); $objService.PSTypeNames.Add('PSWindowsUpdate.WUServiceManager');"

The service account that SQL Server Agent is running under has sysadmin privileges to the instance, but does NOT have Admin access to the server.

If I RDP into the server as the Agent's service account, I can execute the command successfully from a command prompt:

But, if I execute the SQL Server Agent Job, it fails with an "Access Denied" error:

Exception calling "AddService2" with "3" argument(s): "Access is denied.
(Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))"
At line:1 char:79
+ $objServiceManager = New-Object -ComObject
'Microsoft.Update.ServiceManager'; $o ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation

You cannot call a method on a null-valued expression.
At line:1 char:170
+ ... efe18d',2,'');
$objService.PSTypeNames.Add('PSWindowsUpdate.WUServiceManager');
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

I have found that if I set the "Run as" for the job to a proxy account that has sysadmin privileges on the instance AND Admin access to the server, it succeeds, but that's not really relevant to my question.

EDIT: I'm executing everything as CmdExec, and not Powershell, because I need to execute an unsigned Powershell script (not included in my code, since it's not causing a problem).  I get the same error if I change the Type to Powershell.

My question: Why does the same command fail as a job, but succeed from the console when both executed from the same user account on the same server?

Potential Workarounds

Since you're running PowerShell through SQL Server CmdExec from an
agent job, you could try calling a PowerShell script with your saved
PS logic in it rather than running raw PowerShell commands to see if
that makes a difference for a potential workaround.
Try explicitly putting the full OS path on the server to point to
PowerShell.exe and then pass your PowerShell commands after that
in case the issue has to do with environmental variables not working correctly for PowerShell within the CmdExec shell.

  My question: Why does the same command fail as a job, but succeed from the console when both executed from the same user account on the
  same server?

Potential Reasons

It could be an issue when calling PowerShell.exe thru the SQL
Server Agent job with the CmdExec shell and how it interprets
security context of the account it's run from or as (e.g. the service
account) when it tries to run PowerShell.exe that way so it's not able to authenticate the execution access to the EXE due to this.
Check and read up on the version of PowerShell installed, the version of SQL Server installed, and the Windows OS all these are on and run from for "known" issues related to version incompatibilities in this respect - it may be a known issue depending on ALL the specifics in this environment.

A working way I run PowerShell from SQL Agent Jobs

You will need to:

Create the [user account object] service account in AD (<DomainName>\<ServiceAccount>)
Create a SQL login i.e. the service account in #1 (<DomainName>\<ServiceAccount>)
Create a SQL credential with that same (<DomainName>\<ServiceAccount>) account credentials,
Create a SQL Server Agent Proxy account to use for the "Run As" from the SQL Agent job to execute PowerShell

Below is a script of the T-SQL part of this process which I run after doing the above 4 steps, and it always works in my environment to accomplish what seems to be similar to what you've explained that you're trying to accomplish. I also have to ensure the AD account has a strong/complex password, and set it to never expire.

--- // Create login on SQL Instance for domain\PSSQLJobs service account if it does not exist
IF NOT EXISTS (
        SELECT *
        FROM sys.server_principals
        WHERE NAME = N'domain\PSSQLJobs'
        )
BEGIN
    CREATE LOGIN [domain\PSSQLJobs]
    FROM WINDOWS WITH DEFAULT_DATABASE = [master]
        ,DEFAULT_LANGUAGE = [us_english]
END

USE [master]

IF NOT EXISTS (
        SELECT *
        FROM sys.database_principals
        WHERE NAME = N'domain\PSSQLJobs'
        )
    CREATE USER [domain\PSSQLJobs]
    FOR LOGIN [domain\PSSQLJobs]
    WITH DEFAULT_SCHEMA = [dbo]

EXEC sp_addrolemember N'db_datareader'
    ,N'domain\PSSQLJobs'

GRANT CONNECT
    ON DATABASE::[master]
    TO [domain\PSSQLJobs]

USE [msdb]

IF NOT EXISTS (
        SELECT *
        FROM sys.database_principals
        WHERE NAME = N'domain\PSSQLJobs'
        )
    CREATE USER [domain\PSSQLJobs]
    FOR LOGIN [domain\PSSQLJobs]
    WITH DEFAULT_SCHEMA = [dbo]

EXEC sp_addrolemember N'db_datareader'
    ,N'domain\PSSQLJobs'

EXEC sp_addrolemember N'SQLAgentOperatorRole'
    ,N'domain\PSSQLJobs'

EXEC sp_addrolemember N'SQLAgentReaderRole'
    ,N'domain\PSSQLJobs'

EXEC sp_addrolemember N'SQLAgentUserRole'
    ,N'domain\PSSQLJobs'

GRANT CONNECT
    ON DATABASE::[msdb]
    TO [domain\PSSQLJobs]

--- // Create Credential *** Type in password of account into value of SECRET ***
USE [msdb]

CREATE CREDENTIAL PSSQLJobs
    WITH IDENTITY = 'domain\PSSQLJobs'
        ,SECRET = '*******'

--- // Create PowerShell Proxy account and give PSSQLJobs access to it
USE [msdb]

EXEC msdb.dbo.sp_add_proxy @proxy_name = N'ExecutePowershell'
    ,@credential_name = N'PSSQLJobs'
    ,@enabled = 1

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = N'ExecutePowershell'
    ,@subsystem_id = 12

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name = N'ExecutePowershell'
    ,@login_name = N'domain\PSSQLJobs'


Using AWS DynamoDB Geospatial index with Python Amazon's AWS DynamoDB features Geospatial Indexing, which facilitates geo queries:

  Query Support: Box queries return items that fall within a pair of geo
  points that define a rectangle as projected on a sphere. Radius
  queries return items that fall within a given distance from a geo
  point.

The problem is that the boto, the Python library for accessing AWS Service, lacks any reference to geo object. The Java equivalent, on the other hand, has such support.

Is there a way to use the geo features of DynamoDB on AWS using Python?

The "geospatial feature" of dynamodb isn't really anything else then calculating a geohash from the coordinates, storing the hash in the same row, create an index on the hash, and when querying based on a location, calculate the hash of that location and compare to the indexed hashes in the table. The algorithm of geohash is really simple and with a reasonable knowledge it could be coded in a couple of hours in any language. You could even create a lambda function (since October 2015 even in Python!) that is attached to a dynamodb stream and stores the geohash automagically.

But probably it is a much better idea to check out dynamodb streams + lambda + cloudsearch integration that has native support for geospatial queries and boto also has a cloudsearch interface.


Basic trigger in PL/pgSQL, to flip a boolean field if another field has text I want a BOOLEAN field to be set to TRUE when a sibling TEXT field is set to a non-empty string. Vice versa, the BOOLEAN is set to FALSE when TEXT field is set to empty string.

The following function is running, but fails to flip the BOOLEAN field.

CREATE OR REPLACE FUNCTION set_flags_on_recipe_trigger_function_()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$BODY$

DECLARE

BEGIN

    IF (TG_TABLE_NAME = 'recipe_') THEN

        IF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') THEN
            BEGIN
                NEW.is_baked_ = ( length( NEW.baking_instructions_ ) > 0 );
                NEW.is_roasted_ = ( length( NEW.roasting_instructions_ ) > 0 );
            END;
        END IF;

        RETURN NEW;

    ELSE
        RAISE EXCEPTION 'This trigger is being used on the wrong table.';

    END IF;

     /* Should never reach this point. Branching in code above should always reach a call to RETURN. */
    RAISE EXCEPTION 'Unexpectedly reached the bottom of this function without calling RETURN.';

END;

$BODY$;

Is this kind of question about programming PL/pgSQL appropriate to this site? Or should I be posting this to StackOverflow?

Your trigger function can be improved:

CREATE OR REPLACE FUNCTION trg_insup_bef_recipe()
  RETURNS TRIGGER AS
$func$
BEGIN
IF TG_TABLE_NAME = 'recipe_' THEN
   IF TG_OP IN ('INSERT', 'UPDATE') THEN
      NEW.is_baked_   := NEW.baking_instructions_ <> '';
      NEW.is_roasted_ := NEW.roasting_instructions_ <> '';
   END IF;
   RETURN NEW;
ELSE
    RAISE EXCEPTION 'This trigger is being used on the wrong table.';
END IF;
END
$func$ LANGUAGE plpgsql;

Major points

Do not use a separate code block without need. Removed the spurious BEGIN .. END.
NEW.baking_instructions_ <> '' is a 100% identical drop-in replacement for
length( NEW.baking_instructions_ ) > 0. Just shorter and faster.
The plpgsql assignment operator is :=, not =.
Removed multiple parentheses to demonstrate they are just noise.

Simplify

Given the trigger definition in your answer (which should be in your question), you can further simplify the trigger function.

If your trigger function is not intended to be generic but for a certain trigger on a certain table (which is the normal case) I suggest to reflect that in the function name instead of adding code and error messages to the function body. Much cheaper and cleaner (IMHO), but that's a matter of taste and style:

CREATE OR REPLACE FUNCTION trg_insup_bef_recipe()
  RETURNS TRIGGER AS
$func$
BEGIN
NEW.is_baked_   := NEW.baking_instructions_ <> '';
NEW.is_roasted_ := NEW.roasting_instructions_ <> '';
END
$func$ LANGUAGE plpgsql;

And since this trigger only has work to do when any of the involved columns have been changed, you only need to trigger it in those cases:

CREATE TRIGGER insup_bef_recipe
BEFORE INSERT
OR UPDATE OF is_baked_, baking_instructions_, is_roasted_, roasting_instructions_
ON recipe_
FOR EACH ROW EXECUTE PROCEDURE trg_insup_bef_recipe();

Simplify further

Do you really need those flags materialized as additional columns? Could be completely (and more reliably) replaced with simple expressions in your queries:

baking_instructions_ <> '' AS is_baked_

Then you don't need a trigger at all. You could create a view with that expression. Or even "generated field". Detailed instructions:
Store common query as column?
Computed / calculated columns in PostgreSQL


How to see list of databases in Oracle? Is there an equivalent to MySQL SHOW DATABASES statement?

Is it possible to find databases in a cluster? i.e. databases present on the network on some other system?

Could I analyze the files present on an Oracle installation to find the same?

Given complete access credentials to an Oracle system how would you go about enumerating all the databases that exist?

Is there an equivalent to MySQL SHOW DATABASES statement?

There is no such thing. You can query listeners on a machine (lsnrctl status) to see what services are registered there, but that doesn't map one-to-one to database (and there could be multiple listeners on the same machine). Otherwise, the tools commonly used connect to one database instance, and an instance belongs to a single database.

If you're talking about Oracle RAC clusters, then each instance knows of its peers (other instances that service the same database) and you can find the other instances currently started for that database using the gv$instance view.
You can also use the crsctl utility to list the services (including databases) that are registered in the cluster, and their status.
If you're talking about another vendor's clustering software, I'm pretty sure they all have these types of resource management utilities to query.

If you're talking about just a bunch of machines, then no, there's no 100% reliable way of enumerating all databases on a network.

To find active (i.e. started) databases, look for *_pmon_* processes on Unix (there's one per database instance), and Oracle services on Windows.

To locate installations of Oracle database software, look at /etc/oratab on Unix. This should contain all the ORACLE_HOMEs installed. You can look inside each of those in $ORACLE_HOME/dbs for spfile<SID>.ora and/or init<SID>.ora files - there will be one for each database.

(I believe you can find the equivalent of the information in oratab in the Windows registry keys below HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, but I don't know its structure.)

Now, of course, if you've registered all your database with an OEM (Enterprise Manager) server when you installed them, you can find the complete list there - but I guess if you're asking that's not the case.


innodb_lock_wait_timeout cant change it I cannot seem to permanatly set innodb_lock_wait_timeout in the sql database I need to change this to 600.

Its for yetiforce crm system. I am running Ubuntu 16.06, with lamp.

Everything else is set bar this but I cannot see it anywhere in my my.cnf file or php.ini.

Even tried global set in the db itself but it wont change.

Any help would be great.

in-order to change innodb_lock_wait_timeout default value you need to edit you my.cnf file and look for innodb_lock_wait_timeout if the parameter exist then you need to edit the current value to 600 and restart the service, otherwise you need to add the parameter innodb_lock_wait_timeout = 600 and restart the service.
Demo:
my.ini content (I am using windows here, but MySQL) configuration is the same

Re-staring MySQL service and check if the parameter value changed


SQL Server 2005 - Query optimization for fetching large number of rows from table with 750 million rows Brief on application:

This is audio fingerprinting application, being developed in Java with Microsoft SQL Server 2005 database.

I have one application to create fingerprints of original songs and put these fingerprints in database. To store fingerprint in database I have table:

CREATE TABLE [dbo].[fp_core](
    [hashkey] [bigint] NOT NULL,
    [note_id] [int] NOT NULL,
    [timeoffset] [int] NOT NULL
) ON [PRIMARY]

The application processes song and takes 100 sample per second, so around 15000 samples for complete song. These sample values are stored in database, 1 row for each sample as {HASHKEY, NOTE_ID, TIMEOFFSET}. For fingerprint of complete song, I may have around 15000 rows in fp_core table. I am planning to put fingerprints of 50000 songs in database, so around 750 million rows will be in fp_core table.

I have other application to process recordings and detect songs played in it. Process is, create set of HASHKEY from recording audio, same as for creating fingerprint of original song. Recording audio will generate around 20000-30000 HASHKEYs. Then application retrieves rows from fp_core table for all matching HASHKEYs generated by recording audio.

To retrieve data from fp_core table by processing recording, I am doing is, filling these all HASHKEYs of recording in one more table, table is:

CREATE TABLE [dbo].[fp_core_keys](
    [hashkey] [bigint] NOT NULL
) ON [PRIMARY]

then I am joining these two tables to retrieve all matching rows, the query is:

select fp.hashkey, fp.note_id, fp.timeoffset
from dbo.fp_core fp
INNER JOIN dbo.fp_core_keys keys ON fp.hashkey = keys.hashkey

I have following indexes:

CREATE CLUSTERED INDEX [index_fp_core] ON [dbo].[fp_core]
(
    [hashkey] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [IX_fp_core_keys] ON [dbo].[fp_core_keys]
(
    [hashkey] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Problem:

Retrieving data using above query is so slow, taking time around 40 seconds.

Right now, here is stats:

Query:

select count(hashkey) from fp_core
go
select count(distinct(hashkey)) from fp_core

Result:

57177764
13675633

Plan:

Can anybody help me?

If you are determined to retain your existing table structure, the best indexes will be something like:

create index ix1 on fp_core(hashkey, note_id, timeoffset);
create index ix1 on fp_core_keys(hashkey);

You mention that the hashkeys can be different for the same song, but it sounds like you have some mechanism for detecting similar songs - if possible, add an additional column called 'songid' to your fp_core table and then query on this single value using a WHERE clause.


How do I join getting one row from the left table, no matter how many matches i get from the right table? I have two tables - one is a data table and the other is a mapping table. I want to join them together, but only preserve the data from the left table. However, it is possible that the mapping table may contain multiple records that match to a single record in the data table. I cannot use a DISTINCT because there may be identical rows in the data table, and I want to preserve the same number of rows from that table in the result set.

Here is a sample of the data I am working with:

       DataTable                    MappingTable                 Expected Result
+-----+-----+-----+-----+   +------+------+------+------+   +-----+-----+-----+-----+
| ID1 | ID2 | ID3 | ID1 |   | ID1  | ID2  | ID3  | ID1  |   | ID1 | ID2 | ID3 | ID1 |
+-----+-----+-----+-----+   +------+------+------+------+   +-----+-----+-----+-----+
|  1  |  1  |  1  |  1  |   |  1   | NULL | NULL | NULL |   |  1  |  1  |  1  |  1  |
|  1  |  1  |  1  |  1  |   | NULL | NULL | NULL |  1   |   |  1  |  1  |  1  |  1  |
|  2  |  1  |  1  |  1  |   |  3   |  3   | NULL | NULL |   |  2  |  1  |  1  |  1  |
|  3  |  1  |  1  |  3  |   +------+------+------+------+   |  2  |  2  |  1  |  1  |
|  4  |  1  |  1  |  4  |                                   |  3  |  3  |  1  |  3  |
|  2  |  2  |  1  |  1  |                                   |  2  |  1  |  0  |  1  |
|  3  |  2  |  1  |  3  |                                   |  2  |  1  |  0  |  1  |
|  3  |  3  |  1  |  3  |                                   +-----+-----+-----+-----+
|  2  |  1  |  0  |  1  |
|  2  |  1  |  0  |  1  |
|  4  |  3  |  2  |  3  |
+-----+-----+-----+-----+

Below is the join I am using. I wrote a custom function to handle the NULL-matching behavior, which I am including here as well.

SELECT * FROM DataTable P
JOIN MappingTable M ON dbo.fNullMatchCheckIntS(P.ID1,M.ID1,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID2,M.ID2,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID3,M.ID3,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID4,M.ID4,0,1) = 1

CREATE FUNCTION dbo.fNullMatchCheckIntS (
    @Value1 INT
    ,@Value2 INT
    ,@AutoMatchIfValue1IsNull BIT
    ,@AutoMatchIfValue2IsNull BIT
)
    RETURNS BIT
AS

BEGIN

    DECLARE @Result BIT = 0

    SELECT
        @AutoMatchIfValue1IsNull = ISNULL(@AutoMatchIfValue1IsNull,0)
        ,@AutoMatchIfValue2IsNull = ISNULL(@AutoMatchIfValue2IsNull,0)

    IF
        (@AutoMatchIfValue1IsNull = 1 AND @Value1 IS NULL)
        OR (@AutoMatchIfValue2IsNull = 1 AND @Value2 IS NULL)
        OR @Value1 = @Value2
        OR (@Value1 IS NULL AND @Value2 IS NULL)
    BEGIN
        SET @Result = 1
    END

    RETURN @Result
END

The problem with the way the join works is that the first two rows in the DataTable match on the first two rows in the MappingTable, giving me four identical records in the result, but I only want 2. I know that I could add an identity column to the DataTable and then use DISTINCT or PARTITION to get the result I am looking for, but I would like to avoid that route if possible.

EDIT: I figured out a way to do this using EXISTS, but it looks a little ugly in my opinion. Still interested in other answers if anyone has an idea. Thanks!

SELECT * FROM DataTable D
WHERE EXISTS (
    SELECT D.ID1, D.ID2, D.ID3, D.ID4
    FROM MappingTable M
    WHERE dbo.fNullMatchCheckIntS(D.ID1,M.ID1,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID2,M.ID2,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID3,M.ID3,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID4,M.ID4,0,1) = 1
)

DBFIDDLE

SELECT
    d.id1, d.id2, d.id3, d.id4
FROM (SELECT ROW_NUMBER() over (ORDER by d1.id1,d1.id2,d1.id3,d1.id4) as r,
             d1.*
      FROM DataTable d1) d
INNER JOIN MappingTable m on
    (m.id1=d.id1 or m.id1 is null) and
    (m.id2=d.id2 or m.id2 is null) and
    (m.id3=d.id3 or m.id3 is null) and
    (m.id4=d.id4 or m.id4 is null)
group by d.r,d.id1,d.id2,d.id3,d.id4;

output:

id1         id2         id3         id4
----------- ----------- ----------- -----------
1           1           1           1
1           1           1           1
2           1           0           1
2           1           0           1
2           1           1           1
2           2           1           1
3           3           1           3


What is the standard isolation level for a db used for money data? When using a database server for monetary/financial data, I can only assume that using transactions is mandatory.
But what I am not sure is which is usually the isolation level? Is a READ-REPEATABLE used? Or only a serialized level?

The write behavior of all transaction isolation levels is the same. However, they are very different when it comes to reads, particularly repeated reads of the same data within a transaction. There are three main behaviors that you need to be aware of:

Dirty Reads
Non-Repeatable Reads
Phantoms

Wikipedia has a good explanation of these behaviors: http://en.wikipedia.org/wiki/Isolation_(database_systems)#Isolation_Levels_vs_Read_Phenomena

You need to figure out which of these behaviors your application can handle. If it is an absolute requirement, that non of these can occur, than you have to use the Serializable isolation level. However, this is also the level with the least amount of concurrency, as preventing these phenomena requires a great deal of serialization of write processes. (Hence the name.)

In many situations you can get away with less restrictive isolation levels provided you use good coding practices. For example, in general it should be avoided to read the same record twice within the same transaction. That way you don't need to worry about non-repeatable reads.

However, sometimes you need to do things like check if a record exist and create it if necessary. This two step process is only safe when using the serializable isolation level as it is the only one that allows range locks and with that gives a way to prevent inserts into a table area that another transaction has "looked at".

A financial application probably has many situations where one record is read and then based on that one or more records including the original one are altered. Every tim you encounter a situation like that, it is more important that you indicate the write intend on the initial read by requesting a write or update lock. If you do that within the same transaction that executes the writes later on you are not dependent on the isolation level at all. You also will reduce the likelihood of deadlocks.

In the end, the choice of isolation level really depends on your requirements. The type of application is not a good guide to pick an isolation level. Look at each requirement and see if the implementation requires a specific isolation level.

Below is an example of how to use the FOR UPDATE hint in MySQL to request an update lock during a select statement:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE accounts(id INT, balance INT);

INSERT INTO accounts(id,balance) VALUES( 1,42);
INSERT INTO accounts(id,balance) VALUES( 2,16);

GO

CREATE PROCEDURE swap(
  pId1 INT,
  pId2 INT)
BEGIN
  START TRANSACTION;

  SELECT @Balance1 := balance
    FROM accounts
   WHERE id = pId1 FOR UPDATE;

  SELECT @Balance2 := balance
    FROM accounts
   WHERE id = pId2 FOR UPDATE;

  UPDATE accounts SET
    balance = @Balance2 WHERE id = pId1;

  UPDATE accounts SET
    balance = @Balance1 WHERE id = pId2;

  COMMIT;
END;

Query 1:

CALL swap(1,2);

Query 2:

SELECT * FROM accounts

Results:

| ID | BALANCE |
|----|---------|
|  1 |      16 |
|  2 |      42 |

Note, this is an example of how to implement the FOR UPDATE hint. It is not necessarily the best solution to solve the demonstrated problem.


Issue with Storage Engine Just got the below error in one of the slave. I just Googled it and changed the variable myisam_data_pointer_size from 4 to 6.

Somehow, my ALTER TABLE was successful.

Can you please let me know whether my approach was right. I am not sure on this variable?
Will this cause any performance issue. should I roll back it ?

130208  9:40:19 [ERROR] Slave: Error 'The table '#sql-19fc_3f8acc6' is full' on query. Default database: 'registry'. Query: 'alter table enom_log_2012_12_03 add responsedatatype char(1) not null after requestdata', Error_code: 1114
130208  9:40:19 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'pumpkin-bin.008662' position 411952695

Problem Description

Check for the Alerts in Errorlog for any errors and failures. Check if MySQL is up? if not verify if the process was Normal shutdown.

Advice

mysql> SHOW TABLE STATUS FROM registry LIKE 'enom_log_2012_12_03'\G
*************************** 1. row ***************************
           Name: enom_log_2012_12_03
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 1786679
 Avg_row_length: 2403
    Data_length: 4293600396
Max_data_length: 4294967295
   Index_length: 60999680
      Data_free: 0
 Auto_increment: 1786680
    Create_time: 2013-02-06 15:19:07
    Update_time: 2013-02-06 15:22:16
     Check_time: 2013-02-06 15:26:35
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> show global variables like '%tmp%';
+----------------+-----------------------+
| Variable_name  | Value                 |
+----------------+-----------------------+
| bdb_tmpdir     | /usr/local/mysql/tmp/ |
| max_tmp_tables | 32                    |
| tmp_table_size | 8388608               |
| tmpdir         | /usr/local/mysql/tmp  |
+----------------+-----------------------+

mysql> show global variables like '%myisam%';
+---------------------------------+---------------+
| Variable_name                   | Value         |
+---------------------------------+---------------+
| myisam_data_pointer_size        | 4             |--before changing it was 4 .. i changed it to 6
| myisam_max_extra_sort_file_size | 2147483648    |
| myisam_max_sort_file_size       | 2147483647    |
| myisam_recover_options          | OFF           |
| myisam_repair_threads           | 1             |
| myisam_sort_buffer_size         | 4194304       |
| myisam_stats_method             | nulls_unequal |
+---------------------------------+---------------

I have not had much experience switching the myisam_data_pointer_size option from 4 to 6.

Simply put, I think you got it right based on the Documentation. Here is why:

According to the MySQL Documentation 5.5 on myisam_data_pointer_size:

  The default pointer size in bytes, to be used by CREATE TABLE for MyISAM tables when no MAX_ROWS option is specified. This variable cannot be less than 2 or larger than 7. The default value is 6. See Section C.5.2.12, “The table is full”.

The fact that you changed it from 4 shows that the table was created originally in an earlier version of MySQL (default pointer size is 4 in MySQL 4.1).

Looking at the additional MySQL 4.1 reference for “The table is full”:

  You are using a MyISAM table and the space required for the table
  exceeds what is permitted by the internal pointer size. MyISAM creates
  tables to permit up to 4GB by default, but this limit can be changed
  up to the maximum permissible size of 65,536TB (2567 – 1 bytes).

  If you need a MyISAM table that is larger than the default limit and
  your operating system supports large files, the CREATE TABLE statement
  supports AVG_ROW_LENGTH and MAX_ROWS options. See Section 12.1.5,
  “CREATE TABLE Syntax”. The server uses these options to determine how
  large a table to permit.

  If the pointer size is too small for an existing table, you can change
  the options with ALTER TABLE to increase a table's maximum permissible
  size. See Section 12.1.2, “ALTER TABLE Syntax”.

Since you ran the change and MySQL did not keel over in agony, you must have gotten it right.

CAVEAT #1

I noticed the table characteristics you posted

Rows: 1786679 (that's 1,786,679 rows)
Avg_row_length: 2403
Row_format: Dynamic

What disturbs me is the average length. Why? I have easily seen 1 Billion Row MyISAM tables and never had a client switch its pointer size. This may be a good time to reevaluate the table's layout.

Here are some possible ways to shrink the table without touching the pointer size ?

Does the table have a lot of TEXT/BLOB fields ?
Could those TEXT/BLOB field be removed an placed in another table ?
Could the table stand some normalization ?
Do you have INT fields that could be shrunk to TINY, SMALLINT, or MEDIUMINT ?

CAVEAT #2

The next time you get table is full, please do not automatically assume that the table reached its internal limit. Always check the datadir to make sure the disk itself is not full.


Impacts of a change to the Page Verify option There's no doubt about importance of doing regular integrity checks on production databases.
I'm testing the impact of changing the maintenance plans and changing how databases are backed up.
First of all, it is necessary to activate the "checksum" pages verification mode, this changing I think gradually impact the performance because it activates when a certain page is read in memory, changed, and written back to disk.

I have started to test this change with backups. I prepared a backup TSQL script that save the duration of each backup command and I ran 100 times each of the following command:

BACKUP DATABASE [MyDB] TO DISK = N'nul' (ran 100 times)
BACKUP DATABASE [MyDB] TO DISK = N'nul' WITH CHECKSUM (ran 100 times)

MyDB it's a 50gb database with page verify set to "checksum".
The results is:

Backup with checksum is 20% slowler

I started some test with a larger database (400GB) and I noticed that first backups (with empty cache) are generally slower. After the first backups have completed the duration tends to stabilize.

My questions are:

Does the backup process buffers data in cache ? If yes, could this cause a variation of PLE trend or memory pressure ?
If backups with checksum are 20% slower what happens with application queries? I don't think my tests are the absolute truth but..
Is there any kind of waiting time linked to the checksum process?
Do you know if there are people online who have tested the impacts of this change ? Any additional material can be useful.

Q1

  Does the backup process buffers data in cache ? If yes, could this
  cause a variation of PLE trend or memory pressure ?

The backup process does not read data into the buffer pool, it uses separate threads that use buffers to read and write the data pages.
More on that here.

It does create a checkpoint prior to starting the backup. This checkpoint will flush dirty pages to disk.

  A full backup does the following:

  Force a database checkpoint and make a note of the log sequence number
  at this point. This flushes all updated-in-memory pages to disk before
  anything is read by the backup to help minimize the amount of work the
  recovery part of restore has to do. ...

Source

Q2

  If backups with checksum are 20% slower what happens with application
  queries? I don't think my tests are the absolute truth but..

Performance benchmarking with or without checksum has been tested before.

See here.

In that test, the average was 94.1 seconds for an update query on a checksum enabled database & 93.7 seconds for the same update query on a database without any page verify option. I performed some similar tests (1M row updates with different page verify options) and also found negligible differences.

Some test results:

4x 1M rows updated (nvarchar(256), 156 characters) without checksum:

CPU time = 1235 ms,  elapsed time = 1231 ms.
CPU time = 1218 ms,  elapsed time = 1213 ms.
CPU time = 1547 ms,  elapsed time = 1583 ms.
CPU time = 1203 ms,  elapsed time = 1199 ms.

4x 1M rows updated (nvarchar(256), 156 characters)  with checksum:

CPU time = 1312 ms,  elapsed time = 1310 ms.
CPU time = 1282 ms,  elapsed time = 1282 ms.
CPU time = 1343 ms,  elapsed time = 1361 ms.
CPU time = 1282 ms,  elapsed time = 1291 ms.

Also I did several backup tests on a database with a data file of 34GB and I am getting nowhere near 20 percent difference.

The backups to 'NUL' getting faster due to caching does appear to be correct.

4 WITH CHECKSUM

BACKUP DATABASE successfully processed 4224190 pages in 89.307 seconds (369.528 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 89.339 seconds (369.395 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 85.930 seconds (384.050 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 83.255 seconds (396.389 MB/sec).

4 WITHOUT CHECKSUM

BACKUP DATABASE successfully processed 4224186 pages in 75.234 seconds (438.650 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 74.964 seconds (440.230 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 78.338 seconds (421.269 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 77.531 seconds (425.654 MB/sec).

4 WITH CHECKSUM

BACKUP DATABASE successfully processed 4224186 pages in 74.598 seconds (442.390 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 71.718 seconds (460.155 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 71.044 seconds (464.521 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 66.624 seconds (495.338 MB/sec).

4 WITHOUT CHECKSUM

BACKUP DATABASE successfully processed 4224186 pages in 63.593 seconds (518.947 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 65.338 seconds (505.088 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 62.314 seconds (529.599 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 61.529 seconds (536.355 MB/sec).

Q3

  Is there any kind of waiting time linked to the checksum process?

Not sure what the question is here, maybe the following part of a blogpost can give more insight:

  Of course, WHEN you switch a database over to using CHECKSUM there is
  NOT some magical process or operation that goes through and ‘writes’
  CHECKSUMs into your EXISTING pages. If there were, toggling this
  option could obviously cause some ugly problems. But, instead, since
  this is effectively a ‘meta-data’ change that instructs SQL Server to
  start using a new behavior (i.e., CHECKSUM functionality for writes
  and reads of previously CHECKSUM’d pages), you can safely toggle this
  change at pretty much any time without fear of bogging down your
  systems.

  Of course, since this, in turn, means that you may have gobs of
  existing/older data that’s been laying around without CHECKSUM
  configured, that data (or the data on those pages) will NOT end up
  getting CHECKSUM values until that data is finally pulled into SQL
  Server and then re-written. Consequently, if you want to ‘force’
  CHECKSUMs into your pages, you’ll need to do full-blown index rebuilds
  or other modifications that force all data to be pulled in and then
  rewritten.

Source

Checksums are not applied immediately when enabling the setting, the page needs to have been modified to apply the new checksum.

Q4

  Do you know if there are people online who have tested the impacts of
  this change ? Any additional material can be useful.

See answer to Q2.


MySQL: Tree-Hierarchical query SUB-TREE WITHIN A TREE in MySQL

In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor (1)- SuperVisee (∞) ).

Query to Create Table:

CREATE TABLE IF NOT EXISTS `Employee` (
  `SSN` varchar(64) NOT NULL,
  `Name` varchar(64) DEFAULT NULL,
  `Designation` varchar(128) NOT NULL,
  `MSSN` varchar(64) NOT NULL,
  PRIMARY KEY (`SSN`),
  CONSTRAINT `FK_Manager_Employee`
              FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have inserted a set of tuples (Query):

INSERT INTO Employee VALUES
 ("1", "A", "OWNER",  "1"),

 ("2", "B", "BOSS",   "1"), # Employees under OWNER
 ("3", "F", "BOSS",   "1"),

 ("4", "C", "BOSS",   "2"), # Employees under B
 ("5", "H", "BOSS",   "2"),
 ("6", "L", "WORKER", "2"),
 ("7", "I", "BOSS",   "2"),
 # Remaining Leaf nodes
 ("8", "K", "WORKER", "3"), # Employee under F

 ("9", "J", "WORKER", "7"), # Employee under I

 ("10","G", "WORKER", "5"), # Employee under H

 ("11","D", "WORKER", "4"), # Employee under C
 ("12","E", "WORKER", "4")

The inserted rows has following Tree-Hierarchical-Relationship:

         A     <---ROOT-OWNER
        /|\
       / A \
      B     F
    //| \    \
   // |  \    K
  / | |   \
 I  L H    C
/     |   / \
J     G  D   E

I written a query to find relationship:

SELECT  SUPERVISOR.name AS SuperVisor,
        GROUP_CONCAT(SUPERVISEE.name  ORDER BY SUPERVISEE.name ) AS SuperVisee,
        COUNT(*)
FROM Employee AS SUPERVISOR
  INNER JOIN Employee SUPERVISEE ON  SUPERVISOR.SSN = SUPERVISEE.MSSN
GROUP BY SuperVisor;

And output is:

+------------+------------+----------+
| SuperVisor | SuperVisee | COUNT(*) |
+------------+------------+----------+
| A          | A,B,F      |        3 |
| B          | C,H,I,L    |        4 |
| C          | D,E        |        2 |
| F          | K          |        1 |
| H          | G          |        1 |
| I          | J          |        1 |
+------------+------------+----------+
6 rows in set (0.00 sec)

[QUESTION]
Instead of complete Hierarchical Tree, I need a SUB-TREE from a point (selective) e.g.:
If input argument is B then output should be as below...

+------------+------------+----------+
| SuperVisor | SuperVisee | COUNT(*) |
+------------+------------+----------+
| B          | C,H,I,L    |        4 |
| C          | D,E        |        2 |
| H          | G          |        1 |
| I          | J          |        1 |
+------------+------------+----------+

Please help me on this. If not query, a stored-procedure can be helpful.
I tried, but all efforts were useless!

I already addressed something of this nature using Stored Procedures : Find highest level of a hierarchical field: with vs without CTEs (Oct 24, 2011)
If you look in my post, you could use the GetAncestry and GetFamilyTree functions as a model for traversing the tree from any given point.
UPDATE 2012-12-11 12:11 EDT
I looked back at my code from my post. I wrote up the Stored Function for you:
DELIMITER $$

DROP FUNCTION IF EXISTS `cte_test`.`GetFamilyTree` $$
CREATE FUNCTION `cte_test`.`GetFamilyTree`(GivenName varchar(64))
RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN

    DECLARE rv,q,queue,queue_children,queue_names VARCHAR(1024);
    DECLARE queue_length,pos INT;
    DECLARE GivenSSN,front_ssn VARCHAR(64);

    SET rv = '';

    SELECT SSN INTO GivenSSN
    FROM Employee
    WHERE name = GivenName
    AND Designation <> 'OWNER';
    IF ISNULL(GivenSSN) THEN
        RETURN ev;
    END IF;

    SET queue = GivenSSN;
    SET queue_length = 1;

    WHILE queue_length > 0 DO
        IF queue_length = 1 THEN
            SET front_ssn = queue;
            SET queue = '';
        ELSE
            SET pos = LOCATE(',',queue);
            SET front_ssn = LEFT(queue,pos - 1);
            SET q = SUBSTR(queue,pos + 1);
            SET queue = q;
        END IF;
        SET queue_length = queue_length - 1;
        SELECT IFNULL(qc,'') INTO queue_children
        FROM
        (
            SELECT GROUP_CONCAT(SSN) qc FROM Employee
            WHERE MSSN = front_ssn AND Designation <> 'OWNER'
        ) A;
        SELECT IFNULL(qc,'') INTO queue_names
        FROM
        (
            SELECT GROUP_CONCAT(name) qc FROM Employee
            WHERE MSSN = front_ssn AND Designation <> 'OWNER'
        ) A;
        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                SET rv = queue_names;
            ELSE
                SET rv = CONCAT(rv,',',queue_names);
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;

    RETURN rv;

END $$

It actually works. Here is a sample:
mysql> SELECT name,GetFamilyTree(name) FamilyTree
    -> FROM Employee WHERE Designation <> 'OWNER';
+------+-----------------------+
| name | FamilyTree            |
+------+-----------------------+
| A    | B,F,C,H,L,I,K,D,E,G,J |
| G    |                       |
| D    |                       |
| E    |                       |
| B    | C,H,L,I,D,E,G,J       |
| F    | K                     |
| C    | D,E                   |
| H    | G                     |
| L    |                       |
| I    | J                     |
| K    |                       |
| J    |                       |
+------+-----------------------+
12 rows in set (0.36 sec)

mysql>

There is only one catch. I added one extra row for the owner

The owner has SSN 0
The owner is his own boss with MSSN 0

Here is the data
mysql> select * from Employee;
+-----+------+-------------+------+
| SSN | Name | Designation | MSSN |
+-----+------+-------------+------+
| 0   | A    | OWNER       | 0    |
| 1   | A    | BOSS        | 0    |
| 10  | G    | WORKER      | 5    |
| 11  | D    | WORKER      | 4    |
| 12  | E    | WORKER      | 4    |
| 2   | B    | BOSS        | 1    |
| 3   | F    | BOSS        | 1    |
| 4   | C    | BOSS        | 2    |
| 5   | H    | BOSS        | 2    |
| 6   | L    | WORKER      | 2    |
| 7   | I    | BOSS        | 2    |
| 8   | K    | WORKER      | 3    |
| 9   | J    | WORKER      | 7    |
+-----+------+-------------+------+
13 rows in set (0.00 sec)

mysql>


Collecting trace info of SQL Server 2005 using SQL Server 2008 R2 profiler I have a vs2005 application that uses SQL Server 2005. I was trying to collect the SQL trace generated by the application while executing. For collecting the trace I've connected to SQL Server 2005 through SQL Server 2008 R2 Profiler. To my disappointment I found no trace data were collected by the SQL Server 2008 R2 Profiler.

I'm using Win 7 64-bit

Am I missing anything ? Can anyone advise on this ?

It sounds like you might possibly be mistaking a server side trace and SQL Server Profiler which perform very similar functions but work slightly differently and store data in different places.

Show us your code on how you started the trace, or tell us what steps you took.  You could also look at the following:

Server Side Trace:
http://www.sqlservercentral.com/stairway/72363/

Profiler (not recommended unless you are collecting a small amount of data):
http://databases.about.com/od/sqlserver/ht/trace.htm

Is the problem that when you start up SQL Server Profiler and start collecting data nothing populates the screen?


How to prevent a highly dynamic PL-SQL query from injection using bind variables I have a procedure as you can see below:
create or replace procedure Injection_test(qry1         varchar2,
                                           qry2         varchar2,
                                           user_id      varchar2)
is
   final_query varchar2(1000) := '';
begin

  if (qry1 is not null) then
     final_query := 'select c_num from z_test_a where userid = ''' ||user_id || ''' and ' || qry1;
   if (qry2 is not null) then
     final_query := final_query || 'intersect  ';
   end if;
  end if;

 if (qry2 is not null) then
  final_query := final_query || ' select c_num from z_test_b where userid = ''' ||user_id || ''' and ' || qry2;
 end if;

--dbms_output.put_line(final_query);
  execute immediate 'insert into Z_final_result (c_num)'||final_query;
 commit;
end;

As you can see , the variable final_query is highly dynamic based on two input variablesqry1 and qry2. Since I'm new to Oracle pl-sql injection , I don't exactly know how to prevent this piece of code from sql injection . I know the basic concepts of injection and how it occurs
-for example when we have null or 1=1 for one of the input variables,all the records of the table will be inserted in the final table- but I do not know exactly how to change this procedure and usebind variables to prevent injection from happening.
I was wondering if you could help me out with this
Thanks in advance

I'm not sure there is a good answer. In general, string concatenation to build your SQL is always a bad idea and should be avoided whenever possible. That said, it would be difficult to avoid in your case except for replacing user_id references with bind variables. Other than that, you will have to come up with some custom logic to validate the content of qry1 and qry2 to ensure they only contain what you expect them to contain. Perhaps a length limitation, or a check of keywords or column names? Either that, or replace qry1 and qry2 with explicit values for the columns in each table, including wildcards, then turn those into bind variables.
As long as you have to accept actual SQL code snippets as your input, I would recommend something like this:
create or replace procedure Injection_test(p_qry1         varchar2,
                                           p_qry2         varchar2,
                                           p_user_id      varchar2)
is
   final_query varchar2(1000) := '';
begin

   -- put some logic here to validate the content of p_qry1 and p_qry2 to make sure
   -- they don't contain keywords like "select", "update", "delete", "1=1", ";" or
   -- equivalents, and/or that they do contain things you expect to see. Raise an
   -- application error anything unexpected is found

   if p_qry1 is not null
   then
      final_query := 'select c_num from z_test_a where userid = :user_id and '
                     || qry1;
      if p_qry2 is not null then
         final_query := final_query || ' intersect ';
      end if;
   end if;

   if p_qry2 is not null then
      final_query := final_query
                     || 'select c_num from z_test_b where userid = :user_id and '
                     || qry2;
   end if;

   if final_query is not null
   then
      final_query := 'insert into Z_final_result (c_num) ' || final_query;
      --dbms_output.put_line(final_query);

      if instr(final_query,'intersect') > 1
      then
         execute immediate final_query using p_user_id, p_user_id;
      else
         execute immediate final_query using p_user_id;
      end if;

      commit;
   end if;
end;


Extended Events filtering I am trying to create a trace in Extended Events filtering with logins, in profiler we can filter with login names but I do not see that option in XE. How do I do that?

In SQL 2016 there are 5 filter predicates you can use to filter logins.
For NT login/user you can use all 5 but SQL login/user you can use number 3,4 and 5 only.

sqlserver.nt_username-Collect NT username
sqlserver.session_nt_username-Get the current session NT user
sqlserver.server_principal_name-Get the name of the Server Principal in whose context the event is being fired.
sqlserver.username-Get the current username
sqlserver.session_server_principal_name-Get the name of the Server    Principal that originated the session in which the event is being    fired.

Using tsql, you need to change my code depending on  what kind of account and real value you are using.

    ALTER EVENT SESSION [test] ON SERVER
    ADD EVENT sqlserver.sp_statement_completed(
     WHERE ((((([sqlserver].[username]=N'')
       AND ([sqlserver].[session_server_principal_name]=N''))
       AND ([sqlserver].[server_principal_name]=N''))
       AND ([sqlserver].[session_nt_user]=N''))
       AND ([sqlserver].[session_server_principal_name]=N'')))
    GO

From GUI:

Once you pick select events, highlight event and click configure.

Under filter set your And/or condition, filed, operator, value.


Pending changes from a finished job I'm getting the following error:

Uncommitted changes pending for file MYTABLE in library MYLIB.
CPF325E
Cause . . . . . :   Uncommitted changes are pending for file MYTABLE in library MYLIB for commitment definition MYTABLE.
Recovery  . . . :   Do one of the following and try your request again:
-- Complete the commitment control process by doing a commit or rollback for commitment definition MYTABLE.
-- Change the commitment control option and compile the program again.
Technical description . . . . . . . . :   The commitment definition identifier is X'5CC4C6E3C1C3E3C7D9D7'. The job that initiated the change(s) is 954538/QUSER/QZDASOINIT. The logical unit of work identifier is *N. The lock space identifier is *N. The XID is X'5CD5404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040'.

Problem is, 954538/QUSER/QZDASOINIT is not an active job. It doesn't show up under active jobs and running this:
 WRKCMTDFN 954538/QUSER/QZDASOINIT

Gives this:

Job 954538/QUSER/QZDASOINIT not found.

...What is happening? How can a job that was finished be connected to pending changes? How can I rollback those changes?
EDIT: Responding to questions in an Answer:

Where are you seeing the message?

That specific one is when, in System iNavigator, I right-click MYTABLE and select Journaling. The original error that made me find the problem is when C# throws:

iDB2SQLErrorException: SQL0910 Object MYTABLE in MYLIB type *FILE has a pending change.

Likewise, I get a "changes are pending" error when trying to remove a referential constraint from MYTABLE.

Do you have *JOBCTL authority? It may be that the job isn't gone, you just don't have authority to see it.

I don't know, nor do I know how to check. If I run:
WRKCMTDFN QUSER/QZDASOINIT

Then a bunch of jobs show up, but 954538 is not one of them.

Where are you seeing the message?  With the job gone, I wouldn't expect to see that message in an active job.  Do you have *JOBCTL authority?  It may be that the job isn't gone, you just don't have authority to see it.


Is there anything a table-level constraint declaration can do that a column-level one can not do? Had an exam today. One question disturbed me:

  What can a table-level constraint do that a column-level constraint can't do?

My answer was that only a table-level constraint declaration permits setting up a composite primary key. I guess this is not the only difference. How could I have added to that?

You've forgotten about other types of constraint than primary key (also applies to unique, check, and foreign key constraints) but that's basically it.

A column-level constraint can only reference the column that it is declared next to. A table-level constraint can reference multiple columns.


change character_set_connection utf8mb4 mysql I want to change character_set_connection utf8mb4. Its showing me utf8. I have follow this https://mathiasbynens.be/notes/mysql-utf8mb4 article and changed
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Also fire this query. SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci but when I check current status in phpmyadmin using SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%'; result

character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

I have set alter tables and database

SET NAMES utf8mb4;
ALTER DATABASE openfire CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE ofOffline CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This is my connections string jdbc:mysql://localhost:3306/openfire?rewriteBatchedStatements=true&useUnicode=true
MySQL  5.6.33 I have check in windows with version 5.7.14 it's fine. Please give me any suggestions. Thanks in advance.

What client are you using?  You will need to specify that the connection is to be with utf8mb4.

What about the tables?  If you have existing tables, an ALTER ... CONVERT TO will be needed.


SSIS OLE DB Error on package execution Good day,
I have an SSIS package that runs on scheduled job for a while now. all was okay, until very recently, i started receiving some failed job executions. When I investigated it, I found the following errors:

Error: 0xC0202009 at PACKAGE_NAME, Source - Query [127]: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "Protocol error in TDS stream".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "Protocol error in TDS stream".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "Protocol error in TDS stream".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "Communication link failure".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "TCP Provider: A connection attempt failed because
the connected party did not properly respond after a period of time, or established
connection failed because connected host has failed to respond.".

Error: 0xC0047038 at PACKAGE_NAME, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on Source - Query returned error code 0xC0202009.
The component returned a failure code when the pipeline engine called PrimeOutput().
The meaning of the failure code is defined by the component, but the error is fatal and
the pipeline stopped executing.
There may be error messages posted before this with more information about the failure.

Can anyone help?

The semaphore timeout period has expired" is a Network error

Network issues between the source server and the server running SSIS?

A look in the Windows System logs of both machines might provide more information about any network issues. You will probably also find the specific windows error number, which will help in your research.

Locking/blocking ?

Using the dmvs dm_exec_connections, dm_exec_sessions and dm_exec_requests you can see if another query is blocking access to a resource your SSIS package is requesting from the source server.

select es.session_id, er.blocking_session_id, *
FROM sys.dm_exec_connections ec
LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id

Is the query too slow because of full table scans or lack of indexes?
Remote Query Timeout: Is the remote query timeout set on the source server set?

EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

At 0 the default value, there is no timeout.


Leap second in Database system (Postgresql and SQL Server) This night will be longer because of leap second: https://en.wikipedia.org/wiki/Leap_second

My impact will be minimal in my case, I don't even know if I will recive some weird data like 20150630 23:59:60. But I have monitoring systems from both windows, linux and other systems. In worst case, I will lose one second of data.

I tested conversion of this weird data in postgresql and SQL Server. These are my results:

This select in Postgresql:

SELECT '20150630 23:59:60.001 UTC'::timestamptz;

Is returning: 2015-07-01 02:00:00.001+02
I have the same result with:

SELECT '20150701 00:00:00.001 UTC'::timestamptz;

I think this can be dangerous in critical systems, because, if this data is handled by clients, data can be stored in the wrong order, adding a second to a legitimate data.

In sql server if I try:

SELECT CAST('20150630 23:59:60' AS datetime)

It give me a conversion error. So it is not possible, in sql server, to store this data.

One gives me an error, the other just add a second. I don't like both, because it is impossible to store events in the leap second.

Both are recent systems. SQL server is 2014 on a window 2012 machine. Postgresql is 9.3 on redhat machine (not so update, I think a 6.2).

What if someone ask me to store this data?
I read this and some workarounds: https://stackoverflow.com/questions/19751115/leap-second-handling-in-database

Has anyone faced this as a problem? I mean, this is just a theoretical question in my case, not a real problem (so don't waste time if you don't like theoretical questions)

EDIT:
Tested in Oracle 11.2, error is returned:

    ORA-01852: seconds must be between 0 and 59
    01852. 00000 -  "seconds must be between 0 and 59"

Here's a description of PostgreSQL's date handling I found on the pgsql mailing list from January 2012:

  As best I can tell, that document is talking about issues that are
  beyond Postgres' ken.  When you tell us a timestamp value is
  '2012-01-30 21:13:28.097017-05', that's what we store --- whether you
  meant it to be in TAI, UTC, UT1, or whatever is not our concern.
  If you then ask for that date plus one day, we're going to tell you
  '2012-01-31 21:13:28.097017-05'.  We do know about civil time zones and
  daylight savings transitions, and will adjust such answers for those,
  but not about leap seconds.  There's been very little user demand for
  leap-second-aware date arithmetic, and the difficulties with
  extrapolating such arithmetic into the future mean that we're not
  likely ever to try to support it. [emphasis mine]

As Daniel Vérité mentioned in the comments, the bug here isn't that PostgreSQL can't store leap seconds -- it can -- it's that it can't distinguish them from the following second, and can't do maths involving leap seconds.

Also, as dbafromthecold pointed out, some operating systems won't report leap seconds at all e.g. How the Windows Time service treats a leap second. Another example is Google's services, which apply a leap smear, slightly speeding up the server clocks before the leap second so as to skip over the leap second without ending up too far ahead of UTC.

So: it looks like most services are fine with a potential one-second error around leap seconds. If you need better accuracy around leap seconds, AFAICT you will need to implement your own UTC-based clock time.


Points that support creating a separate database for source(which is usually coming in through an API Request) I am doing an academic project with limited knowledge of data warehouse. I am using SQL Server to build that.

I have three different sources -
Sources - JSON Request, Web Crawling, Excel Data Source.

I Presume we need three destinations - Source Database, Staging Database, Data Warehouse.

The loading of the source database happens through SSIS - Execute SQL Task. I am running a python script in there, to pull the data and store it in the database tables.

I am a bit confused here - Should I change my script in such a way that the script does all the transformations and load them into the Staging Tables ? If I can do that, then there is no need for a Source database itself here.

Please share the best practices here - I understand the question is vague, but

  what are the points that could support my argument in having a
  separate database for source?

The value of having a separate source database is that if your transformation process fails, you don't have to go to the external sources to pull the data again.  You can simply fix the transformation issue and start at the transformation step, skipping the data load.

If the time that is required to pull the data from the external sources is insignificant, then it may not be worth the extra effort.  You simply have to weigh the various factors to determine what's best for the project:

How long does it take to extract the data?
If the transformation fails, how long will the original data still be available if you need to extract it again?
How quickly will you be able to address any transformation issues?
Is this a short term process, or will it be running indefinitely?  If it will be running indefinitely, you'll want to spend more time engineering so that you spend less time fixing problems, especially if someone else will be managing it eventually.

Using a source database could also speed up your testing processes since you can test the load of the source database and the transformation processes separately.


Is there any query to fetch the a single row data in chunks? The data stored in a row is just like "Phone Number:--- xxxxxxxxxxx Call Type:--- MISSED Call Date:--- 2013-06-13 Call duration in sec :--- 0 Phone Number:--- xxxxxxxxxxx Call Type:--- OUTGOING Call Date:--- 2013-06-14 Call duration in sec :--- 21 Phone Number:--- xxxxxxxxxxx Call Type:--- INCOMING Call Date:--- 2013-06-14 Call duration in sec :--- 79".

Is there any query that may help me to retrieve this row in parts or chunks.

I worked for a while on your request dude... your problem is that it's not really adapted for relational database (POOR PERFORMANCES), you will be obliged to use some scripting language, but there is some interesting results:

I've created a tale with one line:

CREATE TABLE mytable (mycolumn VARCHAR(1000));

Inserted your example in it:

INSERT INTO mytable VALUES ("Phone Number:--- xxxxxxxxxxx Call Type:--- MISSED Call Date:--- 2013-06-13 Call duration in sec :--- 0 Phone Number:--- xxxxxxxxxxx Call Type:--- OUTGOING Call Date:--- 2013-06-14 Call duration in sec :--- 21 Phone Number:--- xxxxxxxxxxx Call Type:--- INCOMING Call Date:--- 2013-06-14 Call duration in sec :--- 79");

Now with my query you can retrieve one line in your dataset. This query will split the result and return one line, you just have to modify the @i variable to choose your line (0 is the first line) :

SET @i = 0;
SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 3),' ', -1) AS "Phone Number",
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 6),' ', -1) AS "Phone Type",
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 9),' ', -1) AS "Call Date",
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 15),' ', -1) AS "Call Duration in sec"
FROM mytable;

Example with the third line:

[11:42:02] [DEV\MFO] mysql> SET @i=2;
Query OK, 0 rows affected (0.00 sec)

[11:42:08] [DEV\MFO] mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 3),' ', -1) AS "Phone Number", SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 6),' ', -1) AS "Phone Type", SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 9),' ', -1) AS "Call Date",  SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 15),' ', -1) AS "Call Duration in sec" FROM mytable;
+--------------+------------+------------+----------------------+
| Phone Number | Phone Type | Call Date  | Call Duration in sec |
+--------------+------------+------------+----------------------+
| xxxxxxxxxxx  | INCOMING   | 2013-06-14 | 79                   |
+--------------+------------+------------+----------------------+
1 row in set (0.00 sec)

Note that if you want to know how many lines you have in your dataset you can use:

SELECT (LENGTH(mycolumn) - LENGTH(REPLACE(mycolumn, ' ', '')) + 1) / 15 FROM mytable;

Let me know...

Max.


Why is the 'AS' table alias keyword required in Visual Studio (Database projects) Up till now I have been working directly on SQL Server instances, and am now playing around with how best to keep databases in source control. The visual studio database project seems to be exactly what I want.

However, on trying to build a fairly small database I get several hundred SQL71561 errors. The code is this:

CREATE VIEW [dbo].[View Name] AS
SELECT
    EA.colName,
    ...
FROM
    DbName.dbo.TableName EA

And the error is this:

  SQL71561: View: [dbo].[View name] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects:
  [DbName].[dbo].[TableName].[colName] or [DbName].[dbo].[TableName].[EA]::[colName].

If I add an AS to the table alias, then the code seems to be accepted. It seems that column aliases are allowed to skip the 'AS' keyword.

I think this is happening because you're qualifying the database name in the query.

So remove **DBName** form the select clause; Visual Studio would then accept it.


Implement a deduplication trigger in Oracle I am new in Oracle DB. We are using Toad for Oracle. I need to implement a deduplication trigger for our table. It's an alarms table and a third party vendor will be sending alarms to our database. Basically, it's a catch-all alarms. Duplicates are those with the same IP, alarm remarks and severity.

Sample data:

INSERT INTO t_alarms (IP, remarks, severity) VALUES ('10.10.10.10', 'VOICESERVER IS DOWN', '5')
INSERT INTO t_alarms (IP, remarks, severity) VALUES ('10.10.10.10', 'VOICESERVER IS DOWN', '5')
INSERT INTO t_alarms (IP, remarks, severity) VALUES ('10.10.10.19', 'VOICESERVER IS DOWN', '5')
INSERT INTO t_alarms (IP, remarks, severity) VALUES ('10.10.10.19', 'VOICESERVER IS DOWN', '5')

And then my table looks like this:

IP          REMARKS               SEVERITY TALLY FIRSTOCCUR         LASTOCCUR
10.10.10.10 'VOICESERVER IS DOWN' 5        2     2/21/2014 01:00:03 2/22/2014 02:00:03
10.10.10.19 'VOICESERVER IS DOWN' 5        2     2/21/2014 06:00:03 2/22/2014 06:30:03

where:
Tally is the count of how many times my alarms happen
FirstOccur is the date/time when it first inserted/happen
LastOccur is the date/time when it last occur

Is there a way or a script to implement this dedupe process through triggers?

This is not the sort of task that you would want to use triggers on tables for.

If you really wanted to use a trigger, you could declare t_alarms as a view, rather than as a table.  You could then write an INSTEAD OF trigger on the view that translated INSERT statements on the view into UPDATE statements against a new base table.

Were it me, though, I'd let the INSERT statements go into a table which allows duplicates and then create a materialized view on top of that table that does the aggregation.  You can have that materialized view refresh ON COMMIT if you want to ensure that the data in the materialized view is always in sync with the data in the detail table.


Strategy To Remove Old Records We are running Oracle 11.2.0.4.0.  It is a transactional database that has been online since 2011.  We have been tasked to clean up old records.

The table structure is (parent->child):

A->B-C->D->E

E has child tables F, G->H

All tables have dates on them but H.  There are PK-FK relations for the table structure that is described.

Right now all tables except for G have around 70 million records in them.  G has around 500 million records.

If I try I delete by joining the tables the query alone on a production backup takes several minutes to run just for the query.  Also, the requirements for delete will remove several million rows at once.  we have a limited time window to run deletes.

I've run some data analysis and currently the delete will remove about 40% of the data.  Also, the system cannot be taken down for any extended period of time.

What's the best strategy for removing the old records?

Possible Strategy/Approaches:

Seven deletes using sub queries/joins to get the ids to remove.  We would be joining up to 7 tables (lowest child).
Query each table for IDs to delete, store them in a temporary table.  The use that temporary table get the records for the next child.  Then once have all ids, to delete the records with something like this:

delete from H where PKID IN (Select PKID from tempH)
delete from G where PKID IN (Select PKID from tempG)

Would there be any other possible alternatives?  Most likely I would run this process several times deleting X amount of data.  (Week's worth/Months worth, etc.)  I don't think I can catch up with one massive delete.

It's not clear what you mean by "extended period of time" or why you associate deleting rows with "taking down" the system.  If it's acceptable (as a matter of policy) to delete rows while the system is in production use, provided the deletions don't negatively affect performance, then the trick is to nibble away at the data.

Take some unit of the data -- an account, a day, a month, something "reasonable" -- as a "unit of deletion".  The number of rows affected doesn't matter (as long as it's > 0); what matters is that the system can execute the deletion without "taking down the system" in terms of production response time.   Write your procedure as one DELETE for each table, taking the deletion unit value as a parameter.  You don't need to wrap them in a transaction if the process can pick up where it left off after an error. Write a loop to call that procedure for successive values.  If desired, sleep between iterations.

For example, start with the oldest month.  Remove associated data for H, G, F, E, D, C, B, and A.  Get the next oldest month, repeat.

Since these are data you only want to remove, there's no data contention with the production system.  It should be able to cook along happily.  Provided your transactions aren't "too big", each individual deletion won't induce resource contention for very long.  Choose well, and the production system should hardly notice.

But you might instead mean that you can't execute your deletion during working hours, that you'll be given the system for 30 minutes in wee hours to do your deed.  That means removing the maximum row/second, a metric which is highly system- and database-dependent.

Likewise, whether it's a good idea to delete millions of rows all in one go is system dependent.  Twenty years ago, that would have been preposterous on any system I'd ever seen.  These days, it's routine on large databases.  All depends on what you're working with.


The column was specified multiple times I am getting error when trying to run the following query :

INSERT stock_daily_data (item_code,date,quantity_used,stock_left) select
distinct item_code,'2017-11-07',0,stock_left from ( select *, row_number()
over (partition by stock_daily_data.item_code order by stock_daily_data.date
desc) as rn from stock_item_detail join stock_daily_data on
stock_item_detail.item_code = stock_daily_data.item_code ) tt where rn = 1;

Error is :

The column 'item_code' was specified multiple times for 'tt'.

I am using mssql 2012.
I am quite naive to SQL .
Thanks in advance for helpful advice.

It's very likely that when using * to select all columns in your subquery, you have 2 columns with the same name ITEM_CODE, one from stock_item_detail table and one from stock_daily_data table.

I suggest you explicitly list all the columns you want for this subquery and you should be fine.

I think something like the code below should do the trick:

INSERT stock_daily_data (item_code,date,quantity_used,stock_left)
select distinct
    item_code,
    '2017-11-07',
    0,
    stock_left
from (
    select
        stock_item_detail.item_code   -- I assume that these are the columns you want
        , stock_daily_data.stock_left
        , row_number() over (partition by stock_daily_data.item_code order by stock_daily_data.date desc) as rn
    from stock_item_detail
        join stock_daily_data
            on stock_item_detail.item_code = stock_daily_data.item_code
      ) tt
where rn = 1;


Will performance and speed improve when retrieving data from a .txt file instead of the Database? I have a site that averages 40k page views a day and I sometimes hit around 100 concurrent users at any given time.

When a user enters the landing page of the site, the database is called to retrieve 6 random items and they are displayed on the webpage.

The database table has nearly 10k rows and it's using a fairly simplistic statement:

SELECT id, name FROM table ORDER BY RAND() LIMIT 6

Even when the landing page is cached, it still calls a .php file that still pulls 6 random items.

The SQL query generally takes around 0.0137 seconds to execute and currently isn't using an Index.

Today I changed it so that the database is called once per hour trough a cron job and the results are stored in a .txt file. The file is then read when a user hits the landing page and the contents are displayed.

I'm having difficulty measuring a speed improvement as I have no methodology to test with. I'm mainly doing this to try reduce CPU usage on the website as a whole.

I have many more live calls that I can convert over to storing in a .txt file but my question is:

Would it be faster to set up an index on the database and continually call it for each concurrent user or should I proceed with writing to a .txt file once per hour and calling from that?

ORDER BY RAND() LIMIT 6 requires fetching the entire table, shuffling it, then delivering 6 rows.

Performance is depending on how many rows are hit -- it is 10K per request (to get only 6 rows).  Work on shrinking that.

There is no 'perfect' way to fetch 6 rows from a table, but this gives 5 pretty-good ways.  See which one fits best in your app for performance improvement.


Reference the database from a stored proc? I'm using this T-SQL to get index stats (edited for brevity):

SET @db_id = DB_ID(N'TestDB');
SET @object_id = OBJECT_ID(N'TestDB.dbo.Users');
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');

I want to be able to encapsulate this into a stored proc and call it for whichever database the stored proc is in, but the DB name is hard-coded, how can I get the database name dynamically?

Does T-SQL have anything like:

  SET @db_id = DB_ID(this);

If db_id() is called with no parameter it returns the id of the current database  - http://msdn.microsoft.com/en-us/library/ms186274.aspx

so you could just use:
set @db_id=db_id()

And having read the rest of the question, you could call object_id() without the db name. If the object exists you'll get the id, if not NULL. So:
SET @object_id = OBJECT_ID(N'dbo.Users');


Azure Data Factory 'Failed Validation' with Folder does not exist I uploaded whole set of .txt files in my blob storage with folder structure Year/Month/day.

I circled the folder structure.

Then in the pipeline I have following dataset created.

 "type": "AzureBlob",
        "linkedServiceName": "blob-store",
        "typeProperties": {
            "fileName": "{Day}.csv",
            "folderPath": "adf-data/sales/{Year}/{Month}",
            "format": {
                "type": "TextFormat",
                "columnDelimiter": "\t",
                "firstRowAsHeader": true
            },
            "partitionedBy": [
                {
                    "name": "Year",
                    "value": {
                        "type": "DateTime",
                        "date": "SliceStart",
                        "format": "yyyy"
                    }
                },
                {
                    "name": "Month",
                    "value": {
                        "type": "DateTime",
                        "date": "SliceStart",
                        "format": "MM"
                    }
                },
                {
                    "name": "Day",
                    "value": {
                        "type": "DateTime",
                        "date": "SliceStart",
                        "format": "dd"
                    }
                }
            ]
        },
        "availability": {
            "frequency": "Day",
            "interval": 1
        },
        "external": true,
        "policy": {}
    }

When I run the pipeline I am getting an error with folder does not exist.

Question:
What can I do to troubleshoot this further or fix?

I had wrong file extension.  Error message was misleading. Instead of cannot find file I thought the error was about missing folder.

I had to change this

 "typeProperties": {
            "fileName": "{Day}.csv",
            "folderPath": "adf-data/sales/{Year}/{Month}",
            "format": {
                "type": "TextFormat",
                "columnDelimiter": "\t",
                "firstRowAsHeader": true
            },

To (change file extension from csv to txt)

"typeProperties": {
            "fileName": "{Day}.txt",
            "folderPath": "adf-data/sales/{Year}/{Month}",
            "format": {
                "type": "TextFormat",
                "columnDelimiter": "\t",
                "firstRowAsHeader": true
            },


Write performance of Postgresql 9.1 with read-only slave I have a Postgresql 9.1 database that is being hosted by Heroku.  It currently has a read-only "follower".  I need to truncate several large tables (over 100GB) of data and reloads them.  Is the read-only follower going to cause an additional overhead to this operation?  Should I drop the follower and recreate it after the process is complete?

Truncate should be a logical operation that should not be too bad disk I/O-wise.  I would not expect a lot of problems there.


Using column size much larger than necessary I'm creating an SQL Server database with someone else.  One of the tables is small (6 rows) with data that will probably remain constant.  There is a remote possibility that a new row will be added. The table looks something like this:

CREATE TABLE someTable (
    id int primary key identity(1,1) not null,
    name varchar(128) not null unique
    );
INSERT INTO someTable values ('alice', 'bob something', 'charles can dance', 'dugan was here');

I'm looking at the char length of that name column, and I think that its values are probably never going to be larger than, say, 32 characters, maybe not even larger than 24. Is there any advantage to my changing this column to, for example, varchar(32)?

Also, is there any advantage to keeping default column sizes to multiples of 4, 8, 32, etc?

SQL Server uses column lengths when allocating memory for query processing.  So, yes, in short, you should always size columns appropriately for the data.

Memory allocations are based on the number of rows returned by the query multiplied by half the declared length of the column.

Having said that, in this case where you've got 6 rows you probably don't want to over optimize prematurely.  Unless you JOIN this table to another with millions of rows, there won't be a massive difference between a varchar(24) and a varchar(32), or even a varchar(128).

Your second question asks about aligning column lengths on binary multiples.   That's not required at all since SQL Server stores all data in 8KB pages, regardless of the length of each column.


Is a surrogate key better than a natural key in this case I copied this code from here:

CREATE TABLE records(
    email TEXT REFERENCES users(email),
    lat DECIMAL,
    lon DECIMAL,
    depth TEXT,
    upload_date TIMESTAMP,
    comment TEXT,
    PRIMARY KEY (upload_date,email)
);

CREATE TABLE samples(
    date_taken TIMESTAMP,
    temp DECIMAL,
    intensity DECIMAL,
    upload_date TIMESTAMP,
    email TEXT,
    PRIMARY KEY(date_taken,upload_date,email),
    FOREIGN KEY (upload_date,email) REFERENCES records(upload_date,email)
);

The first thing that caught my eyes was the use of natural composite keys as primary keys for both tables.

3 things I was able to extract from this piece of code:

The users table (not shown here) uses email as primary key of type text..
The records table uses a composite key of text + timestamp.
The samples table uses a composite key of 3 fields of type text+ timestamp+ timestamp.

Now in this case wouldn't a surrogate key be better of identification? I mean performance wise indexing an int should be better than indexing a text? Is there something that could make a surrogate key a bad choice?

Email is a particularly bad choice for any PK whether composite or single.  See my answer on this question on Stack Overflow for why:

https://stackoverflow.com/questions/3804108/is-email-address-a-bad-primary-key/3804174#3804174


How to list all users that have a specific Temporary Tablespace like as default I'd like how list all users that have a specific Temporary Tablespace Default.

You can use a select on dba_users

Example

select a.USERNAME
from dba_users a
Where
  TEMPORARY_TABLESPACE = '<NAME TEMP TABLESPACE>';


Mysql daemon failed to start: unknown variable password=xxx Mysqld don't restart anymore...In the log, the apparently obvious reason:

mysqld started
[Warning] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line
[ERROR] /usr/libexec/mysqld: unknown variable 'password=mypassword'
mysqld ended

/etc/my.cnf there is nothing relevant and there is no such variable, which by the way has the correct mysql root password value in the message (!!!?):

[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Mysql 5.0.95 on Centos 5.9. It worked ok before restarting!!

Tried also

 /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --no-defaults
Starting mysqld daemon with databases from /var/lib/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld ended

I followed the last suggestion here

cd /usr
./mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --ledir=/usr/libexec &

Now it started, but don't understand it...not very nice!!!


Select Unique Fields with Max Values in Each? I have a table with names, numbers, and timestamps on the data. I want to know how to select a unique instance of each name based on how recent the timestamp is.

Addy (text), NW (number), UDate (timestamp), Stance (text).

I want to pull only one instance of Addy, and I want the one I pull to have the most recent UDate value. I'd like to make this into a separate table so that I've got the original for historical information and a more usable table with the most recent intel.

I'm using Microsoft Access 2013.

This type of queries are sometimes called "greatest n per group" (there is even a tag in StackOverflow and DBA.SE). In this question, you want one row per group.

A common solution is to use window functions (but these ar enot available in Access) and another is to use GROUP BY in a derived table and then join back to the original table. Like this:

SELECT t.*
FROM
    TableName AS t
  INNER JOIN
    ( SELECT Addy, MAX(UDate) AS UDate
      FROM TableName
      GROUP BY Addy
    ) AS grp
  ON  grp.Addy = t.Addy
  AND grp.UDate = t.UDate ;


Equivalent to Azure SQL Threat Detection for on-premise database Due to GDPR, we are seeking a way to help identify malicious activity or data breaches from our production SQL Server.
It seems if we were using Azure, this is a flick of a switch.
What is available for on-premise servers?

Azure Security Center already monitors the security posture of non-Azure computers by on boarding VMs and computers running on-premises by simply installing the Microsoft Monitoring Agent on these machines. Currently this offering includes OS Advanced Threat Detection using behavioral analytics and machine learning to identify attacks and zero-day exploits on the Windows Operation System layer. As part of our road-map we plan to add SQL Advanced Threat Detection, which will detect anomalous database activities indicating unusual and potentially harmful attempts to breach or exploit SQL severs.

https://docs.microsoft.com/en-us/azure/security-center/security-center-onboarding


Understanding of SQL Server active/active cluster I have a vague understanding of SQL Server's active/active cluster. Can anyone help to confirm if my understanding is correct?

As I understand, active/active cluster use two or more Windows servers. Let's say we have two servers n1 and n2. Then we create a failover cluster on those two servers and join n1 and n2 into the cluster. Then we install a SQL Server instance i1 on n1 and n2. After that we install another SQL Server instance i2 on n1 and n2. Then we can start i1 on n1 and start i2 on n2 to make a active/active cluster. Later we can failover i1 from n1 to n2 and failover i2 from n2 to n1.

Is my understanding correct? Do we need to install the instances i1 & i2 on both n1 and n2? After the installation and configuration of the active/active cluster, how many SQL Server services are installed and running on each node?

You've essentially got it right.  Active/Active is really just a multi-instance SQL cluster.  When you install the Failover Cluster Instance (FCI) I1 on N1 of the Windows Server Failover Cluster (WSFC) you then have to run Add Node on N2 of the WSFC for I1, and vice versa for Instance I2. The SQL Server and Agent services are installed on both nodes, but will only be actively running on the node that actively has control of the SQL Instance.


should we keep the log file or the data file on the fastest drive? SSD = solid state drive

We have a specific question about tempDB performance on server CRM01 (Marketing CRM server - running large queries):

On CRM01,  tempDB data and log files reside on the solid state drive. The SSD total size is 400Gb.
Tempdb data files take up 300 Gb and the log around 100 Gb and may need increasing but there is no space left.

The question is theoretical. We do understand that:
-    if tempdb log files grow too much we should split our processes so that data should be processed in smaller chunks, or we should look at using possibly temporary physical tables instead of tempdb.

both data and log tempdb files should remain on the SSD to optimise performance

However, if we really had to take the decision of moving some of the tempdb files out of the SSD due to space constraint, which ones would you choose to move to a standard fast drive: the  data or the log files?

what is it was not the tempDB, it was a normal DB.
of course then I could partition tables, and create different filegroups, but still would I keep the data or the log in the fastest disk?

lets say the utilization is 50% read and 50% updates and inserts.

@Moderator, I agree the second part of the question is not objective, but it provides insights and ideas.

In my opinion putting the tempdb in the SSD drive without looking at the performance stats is the wrong way to go. SSD drives have high cost/GB and are a premium resource that should be used in the best possible way.

You will probably get better performance moving the most used database files to the SSD, given that you already have broken your database into multiple files and isolated the most problematic objects in their separate filegroup.

That said, SSDs are great for random reads and writes compared to traditional disks, which is the pattern you're seeing mostly in data files. Log files are more subject to sequential reads and writes, which mechanical disks perform just as well as SSDs.

It's true that tempdb is a critical resource for the instance and should be placed in a fast drive, however you're probably going to see a bigger performance gain if you added more RAM (much cheaper than SSDs).

You mileage may vary, but a generic question deserves a generic answer.


How to optimize 'group by' query and indexing on big table? My table has over 300+ million records. I created a key store_idx consisting of columns (store_region, store_format, store_code). This is my query:

select store_region,
       store_format,
       store_code,
       count(distinct basket_id),
       min(shop_date),
       max(shop_date)
from basket
group by store_region,
         store_format,
         store_code
order by store_region,
         store_format,
         store_code;

When I ran explain command this was the output:

+----+-------------+--------+------------+-------+---------------+-----------+---------+------+-----------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key       | key_len | ref  | rows      | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+-----------+----------+-------+
|  1 | SIMPLE      | basket | NULL       | index | store_idx     | store_idx | 3069    | NULL | 305100051 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+-----------+----------+-------+

This query is taking so long to run. How can I optimize?

Calculating three aggregates on a 300 million row table without a filter is going to take some time, especially if the rows have to read from disk. Without knowing anything about types and indexes, it is difficult to give any specific advice, but you may try to replace store_idx with an index like:

CREATE INDEX store_idx ON basket
    (store_region, store_format, store_code, basket_id, shop_date);


How to disable or delete an index I created a non-clustered index on a live server (SQL Server 2008 R2) and it turned out not to give the performance benefits that I was hoping for.  So I want to delete the index again.

Unfortunately I cannot get a lock on the index as it is in use. It's probably always going to be in use. Restarting SQL Server is not really an option.

I've also had this same problem in the past when I've wanted to rebuild an index, and I've had to work around the issue by reorganising instead (which is sometimes very inefficient).

Is there any way to tell SQL Server to stop using an index? Or is it possible to run a query that will attempt to obtain an exclusive lock on the index, wait indefinitely, and then delete the index? Any other options? Or do I just have to wait until there's some kind of outage and then hope I remember to remove it before everything comes back online?

Is there any way to tell SQL to stop using an index?

That would be disabling an index:

alter index IX_YourIndex
on dbo.YourTable
disable;
go

But that, too, will be running into concurrency issues on a highly transactional object, as that operation above still requires a schema-modify (Sch-M) lock on the object.

  Or is it possible to run a query that will attempt to obtain an exclusive lock on the index, wait indefinitely, and then delete the index?

That would be the default behavior of SQL Server.  The only "command timeout" is going to be enforced by client-side software.  I'm going to assume that you're using some sort of GUI to try to delete/disable the index (SSMS maybe?).  Either way, if you were to put this in a new query window (SSMS), it would wait indefinitely for the locked resource to become available so it can be granted the necessary locking:

drop index IX_YourIndex
on dbo.YourTable;
go

This is one of the reasons why it's almost always easiest to do these types of operations during a slow time, or a maintenance window.  That typically can ensure little to no user activity, making these simple tasks...simple and quick.

And if you want to see what/who your drop index request is waiting on, you can run a query similar to this:

select
    session_id,
    command,
    wait_type,
    wait_time,
    blocking_session_id,
    wait_resource
from sys.dm_exec_requests
where command = 'drop index';


How to get past account lock information in oracle database? Is it possible to query the history of previously locked/unlocked Oracle database user accounts? I've tried dba_user but there is no option to get history.

You can't. The database stores the actual status of an account and the time since it is expired or locked, but no history. The only history it stores is the password history, for enforcing password policies.


DBCC CheckDb on Secondary Asynchronous Replica Can I run Ola Hallengren's CheckDB job on a Secondary Asynchronous Replica  (DR) that is readable during business hours? Currently the Asynchronous Replica is not readable...

First question is if it is possible.
Secondly, is it safe?

I have a primary server that is Live and I am doing a CheckDB on a secondary server during business hours / off hours.

Can I run Ola Hallengren CheckDb job on a Secondary Asynchronous Replica (DR) that is readable during business hours?

Sure, assuming the routine doesn't try to update any command or history tables in that database (since it's read-only).

  ... and secondly if is safe as I have a primary server that is Live and I am doing a checkdb on a secondary during business hours/ off hours.

Much like sp_BlitzErik has stated, we can't tell you if it's "safe". This is going to depend on a host of factors such as disk space, redo queue size, send queue size on the primary, memory, current load, etc. Your best bet is to try it and see if you have no other metrics to go by - if people complain, you have your answer. If the website goes down, you have your answer. If no one says a word and everything goes well - you have your answer.


MySql split table on insert I am new so please go easy on me :)

I have the following table

CREATE TABLE `send_sms` (
    `sql_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `momt` ENUM('MO','MT') NULL DEFAULT NULL,
    `sender` VARCHAR(20) NULL DEFAULT NULL,
    `receiver` VARCHAR(20) NULL DEFAULT NULL,
    `udhdata` BLOB NULL,
    `msgdata` TEXT NULL,
    `time` BIGINT(20) NULL DEFAULT NULL,
    `smsc_id` VARCHAR(255) NULL DEFAULT NULL,
    `service` VARCHAR(255) NULL DEFAULT NULL,
    `account` VARCHAR(255) NULL DEFAULT NULL,
    `id` BIGINT(20) NULL DEFAULT NULL,
    `sms_type` BIGINT(20) NULL DEFAULT NULL,
    `mclass` BIGINT(20) NULL DEFAULT NULL,
    `mwi` BIGINT(20) NULL DEFAULT NULL,
    `coding` BIGINT(20) NULL DEFAULT NULL,
    `compress` BIGINT(20) NULL DEFAULT NULL,
    `validity` BIGINT(20) NULL DEFAULT NULL,
    `deferred` BIGINT(20) NULL DEFAULT NULL,
    `dlr_mask` BIGINT(20) NULL DEFAULT NULL,
    `dlr_url` VARCHAR(255) NULL DEFAULT NULL,
    `pid` BIGINT(20) NULL DEFAULT NULL,
    `alt_dcs` BIGINT(20) NULL DEFAULT NULL,
    `rpi` BIGINT(20) NULL DEFAULT NULL,
    `charset` VARCHAR(255) NULL DEFAULT NULL,
    `boxc_id` VARCHAR(255) NULL DEFAULT NULL,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `binfo` VARCHAR(255) NULL DEFAULT NULL,
    `meta_data` TEXT NULL,
    `priority` BIGINT(20) NULL DEFAULT NULL,
    `foreign_id` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`sql_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

I have two different applications talking to each other via these tables.

I would like to count the inserted rows (only 3 in below example, but could be 1000s at a time) and separate then into 3 other existing tables (same format)

So for Example:

INSERT INTO send_sms
( momt, sender, receiver, msgdata, sms_type, dlr_mask, dlr_url )
VALUES ( 'MT','1234', '447XXXXXXXX', 'Hello world4', 2, 27, 'test1' ),
( 'MT','Sender', '447XXXXXXXY', 'Hello world4', 2, 27, 'test2' ),
( 'MT','Sender', '447XXXXXXXY', 'Hello world4', 2, 27, 'test3' );

send_sms1 (would have test1)
send_sms2 (would have test2)
send_sms3 (would have test3)

Would need to be able to support if its not multiples of 3 example if its 10,000 rows send_sms1, send_sms2 should get 3,333 and send_sms3 should get 3,334 (doesn't matter which order). As this is a live system the table needs to be accessed at the same time (so during the move the table must be writable for other insert commands

I have tried things like like

INSERT INTO send_sms2(SELECT * FROM send_sms WHERE sql_id >= (SELECT COUNT(*) FROM send_sms_dump)/2);

the above was just a test to split data- this worked however there isn't any delete so the data wasn't moved. Just 50K copied from the table to the other table

Please point me in the right direction so I can do some more research :)

Update thank you Rick James for the solution.

I am now trying to create an update trigger

DELIMITER $$

CREATE TRIGGER my_trigger AFTER INSERT ON send_sms_dump
FOR EACH ROW
BEGIN
    -- Statement one
    INSERT INTO send_sms_dump2
    SELECT * FROM send_sms_dump
             WHERE sql_id % 3 = 0;
    -- Statement two
    INSERT INTO send_sms_dump3
    SELECT * FROM send_sms_dump
             WHERE sql_id % 3 = 1;
    -- More UPDATE statements
    INSERT INTO send_sms_dump4
    SELECT * FROM send_sms_dump
             WHERE sql_id % 3 = 2;
    -- More UPDATE statements
    DELETE FROM send_sms_dump;
END$$

I have the above the error is ... /* SQL Error (1442): Can't update table 'send_sms_dump' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. */ ...

I am guessing its because of the delete command, i didn't want to drop the table as the third party application will be writing again to that table - let me know if i should post a new question :)

thanks guys

You have an AUTO_INCREMENT sql_id, so you have the rows numbered 1,2,3, 4,5,6, ... (unless you are using a clustering or Multi-Master setup that sets auto_increment_increment to something other than 1).

So, load the data into a table, then copy thirds into the three desired tables, and finally DROP the initial table.

INSERT INTO table_1
    SELECT * FROM table_all
             WHERE sql_id % 3 = 0;   -- 0 for table_1. 1 for table_2, 2 for table_3

If you are adding to tables1/2/3, and they have an AUTO_INCREMENT, you can get unique ids by _skipping sql_id in the transfer:

INSERT INTO table_1 (b,c,d,e)
    SELECT b,c,d,e FROM table_all
             WHERE sql_id % 3 = 0;

This assumes the columns in table_all are

sql_id,b,c,d,e

And table_1/2/3 have another column that is AUTO_INCREMENT.