Why is my log file so massive? 22gb. I am running log backups I can't seem to figure out the answer. I've seen multiple answers like this:
Why Does the Transaction Log Keep Growing or Run Out of Space?

and everyone talks about running back ups on your log file so it shrinks down. I am doing that, but it doesn't shrink anything! I also don't believe I am running any super long transactions.

Server: SQL Server 2008

Recovery Mode: Full

I have a maintenance plan to store 5 days worth of backups. Task 1 backups up the databases with Backup Type Full, Task 2 backs up Transaction logs. Verify backup integrity is checked on both tasks.

My DB's normal .ldf file is 22gb. When I run the above task, the .bak file is 435mb, but the .trn. file is 22gb, same as the ldf. And after successfully running the .ldf doesn't shrink at all, despite everything I've read telling me it should?

What is going on here and why doesn't the log file ever shrink?

I've also tried running this command as mentioned in another answer:

select name, log_reuse_wait_desc
from sys.databases

And it says LOG_BACKUP for the db with the huge log file.

Based on an answer below I am confusing allocated with used space. These are my stats for:

For reasons I have no clue why, the initial size was set to 22gb...

You are confusing allocated space with used space.  After running the backup use this query to see the difference between allocated and used space.

select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'  --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

 from sys.database_files
 order by type_desc Desc, name

You can use the GUI to shrink the log file by changing the 'Initial size'

If you are having troubles shrinking the log, even when it looks mostly empty see my post here


Mysql database .frm and .ibd file read to understand I want to read .frm and .ibd files in mysql to understand how mysql database engine store data in files.
Which format those file does have, since when I open those files it show non-human readable characters.

Jeremy Cole in a series of posts explains InnoDB internals and on disk format in particular.
I'd start from https://blog.jcole.us/2013/01/02/on-learning-innodb-a-journey-to-the-core/ . Get his tool innodb_ruby, it's very helpful in the learning.


how to sync local mysql database and live server database? My plan is to store data in my local MySQL database and after a time interval it will update my live MySQL database remotely. Is it possible?

I'm planing a inventory management script in PHP MySQL.  The real problem is in our country net connection drops suddenly. So I want to first write to the local MySQL database and then it will update the real server.  In any situation like internet connection dropped the script will work locally to gather data and retrieve data. When the internet connection comes back it will update the data to the real server. I want to protect against internet connection failures.

Nothing will update the real server in the mean time because the users are only in the office where the internet connection will drop.  No one from the outside will able to update the database. Only the information can be retrieved from the live server to the shop's owner.

Hello you can sync your local database data with your live mysql server. I had done it making a local sync file that will present all the tables of the local database that I wanted to update to live database and then calling a call back function in footer file that function calls the function to sync local data to live server

Footer.php

    jQuery(document).ready(function($){
    working = false;
    var do_sync = function(){
        if ( working ) { return; }
        working = true;
        jQuery.post(
            "<?php echo $this->config->item("sync_url"); ?>",
            {},
            function(ret){
                working = false;
            }
        );
    }
    window.setInterval(do_sync, 10000);
});

Config.php

$config['sync_server_url'] = "http://livesite.com/index.php/sync_server";
$config['sync_url'] = "http://localhost/localsite/index.php/sync";

sync.php contents Also you need to on CURL extension in local site wamp so that it can communicate with the server side, If database record that is already synced to server edited then sync field in DB should be set to 0 so that on edit it can overwrite server DB again.

Sync.php

<?php
    class Sync extends CI_Controller {
        private $tables;
        function __construct() {
            parent::__construct();
            $this->load->database();
            $this->tables = array(
                "customers" => array("per_id"),
                "db_log" => array("id"),
                "inventory" => array("tr_id"),
                "material" => array("id_mat"),
                "mtracking" => array("id_tra"),
                "orecipie" => array("id_rec"),
                "moutlets" => array("id_st"),
                "opeople" => array("per_id"),
                "oreceivings" => array("rec_id"),
                "osales" => array("sal_id"),
                "osales_items" => array("sal_id", "itm_id"),
                "osales_payments" => array("sal_id"),
                "osales_sus" => array("sal_id", "emp_id"),
                "osales_sus_items" => array("sal_id", "itm_id"),
                "osales_sus_payments" => array("sal_id"),
                "osuppliers" => array("per_id"),
            );

            $this->id_store = $this->config->item('id_store');

        }

        function index() {
            echo '
'.date("H:i:s A");

            $newLine = "\r\n";
            $output = $update = "";

            foreach($this->tables as $table_name => $columns ){

                $col_check = "SHOW COLUMNS FROM `{$table_name}` LIKE 'is_sync'";
                $col_exists = $this->db->query($col_check);
                if ( $col_exists->num_rows > 0 ) { // is_sync column exists for this table
                    $sql = "SELECT * FROM {$table_name} WHERE is_sync = 0 LIMIT 500" ;
                    $rows = $this->db->query($sql);
                    if ( $rows->num_rows > 0 ) { // where is_sync = 0
                        foreach ( $rows->result_array() as $row ) {
                            $col_val = $update_col_val = $already_exists = array();
                            foreach($row as $name => $val) {
                                if ( is_null( $val ) ) {
                                    continue;
                                }
                                $val = mysql_real_escape_string($val);

                                $update_col_val[] = " `{$name}` = '{$val}' ";

                                if ( "is_sync" === $name ) {
                                    $val = 1;
                                }

                                if ( "id_store" === $name ) {
                                    $val = $this->id_store;
                                }

                                $col_val[] = " `{$name}` = '{$val}' ";

                                if ( $name === $columns[0] ){
                                    $already_exists[] = " `$columns[0]` = '{$val}'";
                                }

                                if ( $name === $columns[1] ){
                                    $already_exists[] = " `$columns[1]` = '{$val}'";
                                }

                            }

                            if ( is_array($col_val) && count($col_val) > 0 ) {

                                //echo $this->table_keys[$table_name];
                                $output .= "SELECT * FROM `{$table_name}` WHERE `id_store` = {$this->id_store} AND " . implode(" AND ", $already_exists) . " ||";
                                $output .= "INSERT INTO `{$table_name}` SET " . implode(",", $col_val) . " ||";
                                $output .= "UPDATE `{$table_name}` SET " . implode(",", $col_val) . " WHERE `id_store` = {$this->id_store} AND " . implode(" AND ", $already_exists);
                                $output .= " ;; ";

                                $update .= "UPDATE {$table_name} SET is_sync = 1 WHERE " . implode(" AND ", $update_col_val) . " ;; ";
                            }
                        }
                    } // if is_sync = 0

                } // if is_sync column exists
            }

            if ( !empty($output) && $this->post_data($output) ) {
                $update = explode(" ;; ", $update);
                foreach($update as $upq){
                    if (!empty($upq) ) {
                        $this->db->query($upq);
                    }
                }
            } else {
                echo $result;
            }

            echo date("H:i:s A");

        }

        function post_data($qry){
            $qry = htmlspecialchars(urlencode($qry));
            $data = "qry=" . $qry;
            $ch = curl_init();
            curl_setopt( $ch, CURLOPT_URL,$this->config->item("sync_server_url") );
            curl_setopt( $ch, CURLOPT_AUTOREFERER, 1);
            curl_setopt ( $ch, CURLOPT_HTTPHEADER, array ( 'Content-length: ' . strlen($qry) ) );
            curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1);
            curl_setopt( $ch, CURLOPT_POST,1);
            curl_setopt( $ch, CURLOPT_POSTFIELDS,$data);
            curl_setopt( $ch, CURLOPT_CRLF, 1);
            $result = curl_exec($ch);
            curl_close($ch);
            if ( 1 === intval($result) ) {
                return TRUE;
            } else {
                echo $result;
                return $result;
            }
        }

    }
?>

Then after that you need to make a controller on live server I named it sync_server.php

sync_server.php

<?php
    class Sync_server extends CI_Controller {
        function __construct() {
            parent::__construct();
            $this->load->database();
        }

        function index() {
            if (isset( $_POST['qry'] ) ) {
                $qry = htmlspecialchars_decode( urldecode( $_POST['qry']));

                //$this->connect_local_db();

                $qry = explode(" ;; ", $qry);

                foreach($qry as $q) {

                    $q = explode( "||", $q );

                    //print_r($q);

                    $exists = mysql_query( $q[0] ) or die ("
" . mysql_error() ."
" . $q[0]);

                    if ( $exists && mysql_num_rows( $exists ) ) {
                        mysql_query( $q[2] ) or die ("
" . mysql_error()."
".$q[2]);
                    } else{
                        mysql_query( $q[1] ) or die ("
" . mysql_error()."
".$q[1]);
                    }

                    //$this->db->query($q);
                }
                die("1");
            } else {
                echo "qry not found";
            }
        }

    }
?>


Is "Estimated Number of Rows" always per execution within SSMS execution plans? When I look at execution plans, I often compare "Estimated Number of Rows" with  "Actual Number of Rows", looking for discrepancies.  For example, when I hover over the bottom arrow in the picture below,  the estimate is 1 and the actual is 200, so at a glance it appears to be a terrible estimate.

However, I've come to wonder if this is actually the estimate for just one execution.  When I hover over the seek to get more detail, I see that there are 200 executions, and it estimated 199.98.  So if my hunch is correct, the estimate is actually dead on.

Can anyone confirm for me if this is the case?  And if so, am I the only one to think this is incredibly misleading (especially when hovering over the arrows)?  If it helps, here is the script to set up and reproduce the above plan:

-- Setup: Create 2 tables with 100K rows each.

CREATE TABLE T1(Id int NOT NULL PRIMARY KEY, X INT NOT NULL)
CREATE TABLE T2(Id int NOT NULL PRIMARY KEY)
CREATE INDEX Ix ON T1 (X, Id)

;WITH
    Pass0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
    Pass1 AS (SELECT 1 AS C FROM Pass0 AS A, Pass0 AS B),--4 rows
    Pass2 AS (SELECT 1 AS C FROM Pass1 AS A ,Pass1 AS B),--16 rows
    Pass3 AS (SELECT 1 AS C FROM Pass2 AS A ,Pass2 AS B),--256 rows
    Pass4 AS (SELECT 1 AS C FROM Pass3 AS A ,Pass3 AS B),--65536 rows
    Pass5 AS (SELECT 1 AS C FROM Pass4 AS A ,Pass2 AS B),--1048576 rows
    Tally AS (SELECT  ROW_NUMBER() OVER (ORDER BY C) AS Number FROM   Pass5)
INSERT INTO T1
SELECT Number, Number
FROM  Tally
WHERE Number <= 100000

INSERT INTO T2
SELECT Id
FROM T1

--Now join them

SELECT *
FROM T1
JOIN T2 ON T2.Id = T1.Id
WHERE T1.X <= 200

Yes, this is the case - SSMS does not always show you the estimated rows * number of executions, and this can obviously lead to some inaccurate assumptions if you don't dig deeper as you did. But technically the answer to your question is no. Sometimes SSMS will get this estimate right, depending on the operator and other semantics.

In SentryOne Plan Explorer we try to compensate for this by showing SQL Server's rows * number of executions in several cases instead of just blindly copying the row count from the plan XML. Your plan, for example:


How to use COUNT with multiple columns? How to use multiple columns with a single COUNT?

Assume that there is a table demo with these data:

id |  col1  |  col2  |
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
1  |'alice' | 'book1'|
2  |'bob'   | 'book1'|
3  |'alice' | 'book2'|
4  |'alice' | 'book3'|

I want to find the count of all the combination of user and book name.

I have tried that if i run with distinct, everything is alright.
select count(distinct col1, col2) from demo

However, when i remove distinct, then the sql statement will not be passed in mysql console.
select count(col1, col2) from demo

Question is how to use COUNT with multiple columns in MySql?

There are several things you can count with COUNT() function:

count(*) : rows
count(col1) : rows where col1 is not null
count(col2) : rows where col2 is not null
count(distinct col1) : distinct col1 values.
count(distinct col2) : distinct col2 values.
count(distinct col1, col2) : distinct (col1, col2) values combinations.

Tested at SQLfiddle:

    select
        count(*)             as count_rows,
        count(col1)          as count_1,
        count(col2)          as count_2,
        count(distinct col1) as count_distinct_1,
        count(distinct col2) as count_distinct_2,
        count(distinct col1, col2) as count_distinct_1_2
    from demo ;

But count(col1, col2) is not valid syntax:

select
    count(col1, col2)
from demo ;

gives an error:

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'col2) from demo' at line 2.


MySQL compare values of two large tables On MySQL 5.7 I want to get the records from table 1 that are not present in table 2.

I'm using this simple LEFT JOIN query that is very slow (more than 2 minutes), I don't know why.

SELECT * FROM t1 LEFT JOIN t2 ON t1.name = t2.name WHERE t2.name is null

When I do an explain of the query I get this result

id   select_type     table     partitions    type     possible_keys  key          key_len      ref     rows     filtered     Extra
1    SIMPLE          t1        NULL          index    NULL           NAME_UNIQUE  49           NULL    66387    100.00       Using index
1    SIMPLE          t2        NULL          index    NULL           NAEM_UNIQUE  37           NULL    2275410  10.00        Using where; Using index; Using join buffer (Block Nested Loop)

I have a unique index on both columns "name".
Here are the tables definitions:

CREATE TABLE `t1` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`NAME` char(12) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `NAME_UNIQUE` (`NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=50053 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `t2` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`NAME` char(12) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `NAME_UNIQUE` (`NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=2049985 DEFAULT CHARSET=utf8mb4;

I think it's something related to the use of "Using join buffer (Block nested loop)" but I tried to switch off with no effect:

SET SESSION optimizer_switch='block_nested_loop=off';

Any ideas to make this query perform much better?
Thanks!

Looks like a mismatch between the CHARSET on the two tables. Try setting the same CHARSET and COLLATION on both tables.


Restore MySQL DB from files I own a raspberry pi 1 running raspbian jessie (SYS_A). Two weeks ago I performed an update of the system (aptitude update && aptitude upgrade, not aptitude dist-upgrade, since I know it exists raspbian stretch) and an undesired situation cropped up.

I'm running a MySQL database, I guess version 5.5. After the upgrade, systemd didn't start the mysqld daemon, whose logs are the following:

user@rpi:~ $ sudo systemctl start mysql
Job for mysql.service failed. See 'systemctl status mysql.service' and     'journalctl -xn' for details.

user@rpi:~ $ systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql)
Active: failed (Result: exit-code) since mar 2017-10-10 10:13:11 CEST; 55s ago
Process: 7815 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

user@rpi:~ $ sudo systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql)
Active: failed (Result: exit-code) since mar 2017-10-10 10:13:11 CEST; 2min 46s ago
Process: 7815 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)
oct 10 10:13:10 rpi /etc/init.d/mysql[8535]: 0 processes alive and     '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
oct 10 10:13:11 rpi mysql[7815]: Starting MySQL database server: mysqld . . .     . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
oct 10 10:13:11 rpi systemd[1]: mysql.service: control process exited,     code=exited status=1
oct 10 10:13:11 rpi systemd[1]: Failed to start LSB: Start and stop the mysql     database server daemon.
oct 10 10:13:11 rpi systemd[1]: Unit mysql.service entered failed state.

Fortunately, I haven't removed the MySQL's files within /var/lib/mysql, so it seems all the data remain in there.

I've tried to open up the ibdataX files, but seem to be binaries...

What I've also tried is to set up another raspbian jessie (SYS_B) in another raspberry pi, install mysql, stop the service, backup the SYS_B's original /var/lib/mysql folder, replace it with the SYS_A's /var/lib/mysql folder and start again the mysqld daemon. But this resulted into the same startup error as in SYS_A.

I've got a wordpress database which I don't want to lose it. So, is there any way to restore it?

/var/logs/mysql/error.log

171010 10:12:36 [Warning] Using unique option prefix myisam-recover instead     of myisam-recover-options is deprecated and will be removed in a future release.     Please use the full name instead.
171010 10:12:36 [Note] Plugin 'FEDERATED' is disabled.
171010 10:12:36 InnoDB: The InnoDB memory heap is disabled
171010 10:12:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins
171010 10:12:36 InnoDB: Compressed tables use zlib 1.2.8
171010 10:12:36 InnoDB: Using Linux native AIO
171010 10:12:36 InnoDB: Initializing buffer pool, size = 128.0M
171010 10:12:37 InnoDB: Completed initialization of buffer pool
171010 10:12:37 InnoDB: highest supported file format is Barracuda.
171010 10:12:38  InnoDB: Waiting for the background threads to start
171010 10:12:39 InnoDB: 5.5.57 started; log sequence number 28689772
171010 10:12:39 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
171010 10:12:39 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
171010 10:12:39 [Note] Server socket created on IP: '0.0.0.0'.
171010 10:12:39 [ERROR] Fatal error: Can't open and lock privilege tables:     Incorrect file format 'user'

Summing up: I can't start mysqld, so I can't backup my databases, which I don't want to lose them, especially the related to wordpress. I haven't deleted the data placed on /var/lib/mysql, ibd files and so on. Could I restore mysql databases into another installation of mysql?

As provided by the OP:

As stated in here, the way to proceed was to initiate mysql daemon in safe mode, just as follows:

Start server bypassing privilege system
$ sudo mysqld_safe --user=root --skip-grant-tables
Just afterwards, you must be able to connect to your mysql instance by console (using mysql command) or by any gui client (e.g. MySQL Workbench).

My guess is that the database system became broken after the latest update (perhaps an unsafe power off, reboot, or so on...). As for now, my MySQL databases will be placed on the NAS, as MySQL won't be updated there. I'll also schedule backup tasks.


Can somebody please tell what is the difference between these two date comparisons? So I have 2 queries as follows:
f.DATE >= (GETDATE() - 3)

f.DATE >= CAST(CONVERT(VARCHAR(10),GETDATE() - 3,101) AS DATETIME)

I think they are the same but the second one gives me more rows than the first one does.
The data type of f.DATE is date though.
Can you please tell me where I get it wrong? Thank you much!!

The SQL Server GETDATE() function returns the exact date and time as it at the time of execution including the seconds/milisecond i.e. 2020-07-24 14:08:40.670. When the query executes, the

GETDATE()-3

evaluates the date/time as it at at the time of execution minus 3 days i.e.  2020-07-21 14:08:40.670 and thus only items with an f.Date greater then/equal to  2020-07-21 14:08:40.670 will be returned.
Using CONVERT with a Style of 101 on GETDATE() returns a date in the format of mm/dd/yyyy. In your example above of

CAST(CONVERT(VARCHAR(10),GETDATE() - 3,101) AS DATETIME)

the

CONVERT(VARCHAR(10),GETDATE() - 3,101)

evaluates the date/time as it at at the time of execution minus 3 days without any time portion i.e.  2020-07-21. When this is then CAST to a datetime the time portion is defaulted to 00:00:00 i.e. 2020-07-21 00:00:00.000.
As the derived expression in your second statement is earlier than that in the first you may have additional rows returned.


NoSQL : What is unstructured data? we are currently running at the edge of resources with our mssql server based solution.

We have now many traditional options regarding the next move to tackle the load:

buy faster CPUs and IO
split some customers to seperate server
move db to cluster

All are either expensive in terms of licensing and hardware or time. So, I want to add another option by moving the whole system to a scalable solution that nosql engine cassandra promises.

Yet, I am not sure and not experienced with noSQL databases, so I need to understand the structure of "unstructured" data.

In our application, we basically store data entered by users in various ways as "key-value" lists. There is a parent table, that contains the head element (like an Order) and there is a child table with the key-value pairs comprising the contents of the order (like Order_Lines).

Business-wise, Order and OrderLines are a unit. But due to the RDBMS, they are stored in tables and must be joined all the time.

During operations, we sometimes choose to load only the top part, but most of the time, we load the head row + some KVPs to display some useful information.

For example, in an overview list, we show the head identifier + some values to in columns for each row.

UPDATE: We store forms of any kind. So, basically we store "documents". Nevertheless, we have to prepare and search through these forms by any value, sort etc. Data access control adds another layer of compexity on the database.

As you may guess, the amount and availability of certain KVPs varies from object to object. There is no valid possibility to create single tables for each kind of object as we would have to create thousands of tables for the different data combinations.

Would this kind of "Dictionary" like datasets be better stored in a noSQL database? And will we have performance benefits from this?
Would cassandra model these head+KVPs as one dataset? Looking at the cassandra webpage and some tutorials, I have the impression, that there is not so much of a difference between our RDBMS and cassandra in terms of data organisation - leaving us with the same huge amount of joins if you wanted to select 5 KVPs for a list for each row.

Enlightenment is welcome, also pointers to papers explaining the issues are ok.

Despite the mainstream of noSQL databases IMHO the decision about adopting such technology should be made according to the achievements needed according to the information stored, not only attending to the performance you currently have. This means that maybe your best option is to stick to the SQL database and improve your HW.

But additionally I read something in your question that made me think. There is not much about the current status of your database but your sentence "we basically store data entered by users in various ways as "key-value" lists" makes me think about if the problem wouldn't be a poor data model rather than the lack of physical resources. I've managed really large tables (+10 billion rows) with incredible performance in "traditional" SQL databases.

I'm not saying it is wrong, just, since of course I cannot assess you in the right data model with such little information about your current solution, but just think about revisiting your data model as an additional option along with the rest since you may find some clue scratching there.

Usually key-value lists are fine as a trade-off when you cannot implement the model in its final state because you don't know the different keys you'll have to face, or when you will need the values of one of the possible keys for a certain element. But when implemented, I usually like to re-think such decisions after a while when you have gathered enough amount of information to identify the common case of use and decide whether data model decision is the best. If you know you'll have a certain number of keys, try to do some benchmark with a design of a regular table in the traditional way

CREATE TABLE benchmarkTable (
  element INTEGER,
  key1 VARCHAR(xx),
  key2 INTEGER,
  key3 DECIMAL(5,2),
...
);

...and adding the corresponding indices. Try it out and measure execution plans with both approaches. You may be surprised specially if you gather more than one key at a time, since, among other advantages the data block size should be reduced and thus the performance would be improved.

Hope this helps, or at least broadens the possibilities and opens a new line for investigation.


Identify changes across records for the field ID Consider the following table

TabName     ID      Gender    FirstName
Tab1        C1      M         John
Tab2        C1      M         John
Tab3        C1      F         John
Tab1        C2      F         Mary
Tab2        C2      F         Mary
Tab3        C2      F         Mary

I am looking to identify individual IDs where there is inconsistency across the 'tabs'. For example, ID 'C1' is not the same in tabs 'Tab1' and 'Tab2', however, has a change in the Gender field in 'Tab3'. On the other hand, 'Mary' is consistent across all three tabs.

A potential approach may be to find 'ID' where the count of duplicates records (each row is exactly the same) is not equal to the count of occurrences of each 'ID'.

However, I am not sure how I would write the above SQL. Additionally, I think there is probably a better and cleaner approach to this problem.

SELECT id
FROM table
GROUP BY id
HAVING COUNT(DISTINCT gender) != 1
    OR COUNT(DISTINCT FirstName) != 1


Database Trees with MySql I have a table of "admins" and what they control "units" each unit and admin are from a different table. I wanted to check if there is a connection between an admin and a unit. The number of levels down is unknown. This is my attempt so far. May I get some help?

DROP FUNCTION IF EXISTS `isadmin`;

DELIMITER //

CREATE FUNCTION isadmin(a_id INT(11), a_table VARCHAR(25), u_id INT(11), u_table VARCHAR(25))
    RETURNS INT DETERMINISTIC
BEGIN
    DECLARE rtn INT(1);

SET @a_id := a_id;
SET @a_table := a_table;
SET `rtn` := 0;

WHILE @a_id != '' AND @a_table != '' DO
        SELECT `unit_id`,`unit_table`
    INTO @u_id, @u_table FROM `admin`
    WHERE (`admin_id`,`admin_table`) IN (@a_id, @a_table);
        IF @u_id = `u_id` AND @u_table = `u_table` THEN
                SET `rtn` := 1;
            SET @a := '';
        ELSE
                SET `rtn` := 0;
            SET @a_id := @u_id;
            SET @a_table := @u_table;
        END IF;
END WHILE;

RETURN `rtn`;

END//
DELIMITER ;

SELECT isadmin("1","user","2","group");

I don't think this syntax will work:

IN (`a`)

Instead, you probably need to use CONCAT, etc to construct the SELECT, then PREPARE, EXECUTE, and DEALLOCATE it.

But, I suggest you back up -- Try the SELECTs by hand until you have some confidence that they will deliver the desired results.  Only then try to turn it into a Stored Procedure.


Teradata: Select Table With No Lock As I understand, when selecting from a table in Teradata the table is locked by default.
I would like to do a simple select * from table without locking the table. Meaning, i want to do a select and allow other users to select from the same table without having to wait for my select to be over.

I know that i can downgrade the select lock using LOCKING modifier as follows:

locking table my_table for access select * from my_table

But this will allow my operation not to wait for locks. To my understanding it will still lock the table and so other operations will have to wait for the lock.

If those other users only need to Select from that table neither your nor the other session must add a LOCKING modifier.

A Select applies a READ lock by default which doesn't prevent other Selects, only INSERT/UPDATD/DELETE & DDL is blocked.

If you want other sessions to be able to modify rows you should use LOCK ROW ACCESS which allows dirty reads, i.e. a 2nd session might modify data while you're reading.

Teradata supports two out of the four Standard SQL Transaction isolation levels, the least and the most restrictive:

Read Uncommited -> ACCESS LOCK
Read Commited
Repeatable Read
Serializable -> READ LOCK

Teradata 15.10 implements a new feature named Load Isolation which adds a kind of Read Commited.


Is there another source for untrusted foreign keys beside FK disable and BULK inserts? This question and its answer explain how bulk inserts will render FK as untrusted, as bulk inserts are not completely checked (only PK and UNIQUEs) . I also know that temporarily disable of a FK may lead to untrusted status.

My current project uses a rather small database (< 3GB) and typical table count is less than 200K rows. However, integrity is very important, so I rely heavily on FK constraints (integrity is much more important than speed).

Question: is there any other mechanism (besides disable and BULK INSERT) that can produce untrusted foreign keys?

Another source is a bug in MERGE that allows foreign key constraints to be ignored. This was reported by Paul White, closed as Won't Fix, and has been ignored ever since. It's one of the main reasons I've long been vocal against using MERGE at all.

MERGE query plans allow FK and CHECK constraint violations (also reported here)
MERGE with caution

There are other possible scenarios too - I haven't thoroughly checked out this one about check constraints to see if it could also affect FKs, and another one I didn't read is this one...

While she focused on the performance aspect, my co-worker Melissa Connors wrote a great post on finding and fixing untrusted foreign keys:

Trust issues hurting database performance?


MongoDB Storage Engine How can I change the storage engine of MongoDB permanently so that I don't have to run mongod without having to specify the storage engine every time? I am using shell version 3.2.7 on Windows 7 32-bit. I got an error that said:

  28663 Cannot start server. The default storage engine 'wiredTiger' is
  not available with this build of mongod. Please specify a different
  storage engine explicitly, e.g. --storageEngine=mmapv1., terminating.

Also does anyone know how to cleanly shutdown the server when I am done working? I use Ctrl+C but it causes an error, then I have to empty the data folder to get the process to start again.

You can set that in the storage.config setting within your YAML configuration file.

Example:

storage:
  dbPath: d:\data\db
  engine: mmapv1

If you're not already starting your MongoDB service using a configuration file, see here for the details on how do to that for Windows.


Permissions required for row counts to show up on Object Explorer Details I'm having a weird security issue.  I have a user that's using SSMS 18.2 on a SQL 2016 server.  They are a member of db_datareader but when they pull up the the Row Count column in Object Explorer Details it's blank. As best I can tell it is requiring DBO in order to get the row counts to show up.

User with read access:

User with dbo access:

Is this a bug or intentional? Does anyone know if there is a lower level of permissions that will give this row count? I know there are plenty of other ways to get the row count, sys.partitions for example, however the user insists they want to use the OED window.

Through a little experimentation, I discovered that rowcount requires "VIEW DATABASE STATE".

Methodology: Captured queries that SSMS runs and executed as a user with datareader permissions only. One in particular throws an error. After granting the permission, verified that rowcounts were now visible.


How do I find my current SCN? Given any version of Oracle:

How do I find my current SCN?
What is the maximum possible SCN?

Current SCN

Oracle 9i:

SELECT dbms_flashback.get_system_change_number as current_scn
FROM DUAL;

Oracle 10g and above:

SELECT current_scn
FROM V$DATABASE;

SCN Limits

SCN has a hard limit imposed by its format and a soft limit imposed artificially by Oracle, as described here. I've quoted the relevant portions below (emphasis added).

Hard Limit

  The architects of Oracle's flagship database application must have
  been well aware the SCN needed to be a massive integer. It is: a
  48-bit number (281,474,976,710,656). It would take eons for an Oracle
  database to eclipse that number of transactions and cause problems --
  or so you might think.

Soft Limit

  The soft limit derives from a very simple calculation anchored to a
  point in time 24 years ago: Take the number of seconds since 00:00:00
  01/01/1988 and multiply that figure by 16,384. If the current SCN
  value is below that, then all is well and processing continues as
  normal. To put this in simple terms, the calculation assumes that a
  database running constantly since 01/01/1988, processing 16,384
  transactions per second, cannot exist in reality.

SCN Limit Check

This script (Oracle 10g and above) will check how much of the hard and soft limits you have exhausted. Thanks to Rob for calling out the soft limit.

WITH limits AS (
  SELECT
      current_scn
  --, dbms_flashback.get_system_change_number as current_scn -- Oracle 9i
    , (SYSDATE - TO_DATE('1988-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * 24*60*60 * 16384
        AS SCN_soft_limit
    , 281474976710656 AS SCN_hard_limit
  FROM V$DATABASE
)
SELECT
    current_scn
  , current_scn/scn_soft_limit*100 AS pct_soft_limit_exhausted
  , scn_soft_limit
  , current_scn/scn_hard_limit*100 AS pct_hard_limit_exhausted
  , scn_hard_limit
FROM limits;


Datapump error in log file When I do this:

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.open( operation => 'EXPORT', job_mode => 'TABLE', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'tab1.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.add_file( handle => hdnl, filename => 'exp.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''USERS'')');
DBMS_DATAPUMP.METADATA_FILTER(handle => hdnl,name => 'NAME_EXPR',value => 'IN (''CA_FILE'')');
DBMS_DATAPUMP.start_job(hdnl);
END;
/

And I read:

select * from table(RDSADMIN.RDS_FILE_UTIL.READ_TEXT_FILE('DATA_PUMP_DIR','tab1.dmp'));

It gives me following:

Starting "AUSER"."SYS_EXPORT_TABLE_01":
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-31655: no data or metadata objects selected for job
Job "AGSUSER"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Tue Jan 26 20:28:37 2016 elapsed 0 00:00:01

I am trying to EXPORT 1 table. What's wrong?

This simply means you do not have any table called USERS.CA_FILE.


Reasons for occasionally slow queries? We are running MySQL 5.1 on Windows Server 2008 R2.

We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to log when we had queries that took a long time (> 2000ms). The results were surprising (and possibly an explanation for our deadlocks).

Occasionally queries, that normally take very little time (<10ms), are taking from 4 to 13 seconds. To be clear, these are queries that are running constantly (several times a second) and not suffering from these query time spikes.

We have gone through our indexes looking for any obvious mistakes and haven't had much luck.

Update

The people table:

| people | CREATE TABLE `people` (
`people_id` bigint(20) NOT NULL AUTO_INCREMENT,
`company_id` bigint(20) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`temp_password` varchar(10) DEFAULT NULL,
`reset_password_hash` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`phone` varchar(32) DEFAULT NULL,
`mobile` varchar(32) DEFAULT NULL,
`iphone_device_id` varchar(160) DEFAULT NULL,
`iphone_device_time` datetime DEFAULT NULL,
`last_checkin` datetime DEFAULT NULL,
`location_lat` double DEFAULT NULL,
`location_long` double DEFAULT NULL,
`gps_strength` smallint(6) DEFAULT NULL,
`picture_blob_id` bigint(20) DEFAULT NULL,
`authority` int(11) NOT NULL DEFAULT '0',
`active` tinyint(1) NOT NULL DEFAULT '1',
`date_created` datetime NOT NULL,
`last_login` datetime NOT NULL,
`panic_mode` tinyint(1) NOT NULL DEFAULT '0',
`battery_level` double DEFAULT NULL,
`battery_state` varchar(32) DEFAULT NULL,
PRIMARY KEY (`people_id`),
KEY `email` (`email`),
KEY `company_id` (`company_id`),
KEY `iphone_device_id` (`iphone_device_id`),
KEY `picture_blob_id` (`picture_blob_id`),
CONSTRAINT `people_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `people_ibfk_2` FOREIGN KEY (`picture_blob_id`) REFERENCES `blobs` (`blob_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4658 DEFAULT CHARSET=utf8 |

Indexes:

+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| people |          0 | PRIMARY          |            1 | people_id        | A         |        3502 |     NULL | NULL   |      | BTREE      |         |
| people |          1 | email            |            1 | email            | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
| people |          1 | company_id       |            1 | company_id       | A         |        3502 |     NULL | NULL   |      | BTREE      |         |
| people |          1 | iphone_device_id |            1 | iphone_device_id | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
| people |          1 | picture_blob_id  |            1 | picture_blob_id  | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

we have ~5000 rows in the table on the server that is giving us trouble.

The UPDATE queries in your previous two questions (Question1,Question2) are hitting the table 'people' by PRIMARY KEY with row level locking. This is what I stated back in Question1 on June 6, 2011 10:03 AM

All the transactions are traversing the PRIMARY key. Since the PRIMARY is a clustered index in InnoDB, the PRIMARY key and the row itself are together. Thus, traversing a row and and the PRIMARY KEY are one and the same. Therefore, any index lock on the PRIMARY KEY is a row level lock as well.

Something else has not been considered yet that can attribute slowness to indexes: The use of NON-UNIQUE indexes in InnoDB. Every indexed lookup in InnoDB using non-unique indexes also have the rowID of each row attached to the non-unique key. The rowID basically eminates from the Clustered Index. Updating non-unique indexes MUST ALWAYS interact with the clustered index EVEN IF THE TABLE DOES NOT HAVE A PRIMARY KEY.

Another thing to think about is the process of managing BTREE nodes in an index. Sometimes, it requires the page splitting of nodes. All entries in the BTREE node of non-unique indexes contain non-unique fields PLUS the rowID within the clustered index. To properly mitigate the splitting of such BTREE pages without disturbing data integrity, the row associated with the rowID must experience a row level lock internally.

If the 'people' table has a lot of non-unique indexes, prepare to have a large number of index pages in the tablespace as well as having tiny little rows locks sneak up on you from time to time.

There ia another factor which is not as obvious: Key Population

Sometimes when an index get populated, the key values making up the indexes could become lopsided over time and cause the MySQL Query Optimizer to switch from keyed lookups, to index scans, and finally to full table scans. That you cannot control unless you redesign the table with new indexes to compensate for the lopsidedness ot keys. Please provide the table structure for the 'people' table, the count of the 'people' table, and the show indexes output for the 'people' table.

Even if queries use only the PRIMARY KEY, lopsidedness of keys in non-unique indexes still needs BTREE balancing and page splitting to occur. Such BTREE management will produce a notable slowdown due to intermittent row level locks you did not intend to happen.

UPDATE 2011-06-14 22:19

Queries From Question 1

UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com',
phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>',
iphone_device_time = '2011-06-06 05:35:09', last_checkin = '2011-06-06 05:24:42',
location_lat = <lat>, location_long = -<lng>, gps_strength = 3296,
picture_blob_id = 1190,
authority = 1, active = 1, date_created = '2011-04-13 20:21:20',
last_login = '2011-06-06 05:35:09', panic_mode = 0,
battery_level = NULL, battery_state = NULL WHERE people_id = 3125

UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com',
phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>-<id>-<id>',
iphone_device_time = '2011-06-06 05:24:42', last_checkin = '2011-06-06 05:35:07',
location_lat = <lat>, location_long = -<lng>, gps_strength = 3296,
picture_blob_id = 1190,
authority = 1, active = 1, date_created = '2011-04-13 20:21:20',
last_login = '2011-06-06 05:35:09', panic_mode = 0,
battery_level = NULL, battery_state = NULL WHERE people_id = 3125

Picture the sequence in events

Find the row by PRIMARY KEY
Lock the row and clustered index
Create MVCC Data for all columns being update
Four columns are indexed (email,company_id,iphone_device_id,picture_blob_id)
Each index requires BTREE management
Within the same transaction space, steps 1-5 is trying to be repeated on the same row, updating the same columns (email the same in both queries, company_id the same in both queries, picture_blob_id the same in both queries, iphone_device_id different)

Queries From Question 2

UPDATE people SET iphone_device_id=NULL
WHERE iphone_device_id='iphone:<device_id_blah>' AND people_id<>666;

UPDATE people SET company_id = 444, name = 'Dad', password = '<pass>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@gmail.com',
phone = NULL, mobile = NULL, iphone_device_id = 'iphone:<device_id_blah>',
iphone_device_time = '2011-06-06 19:12:29', last_checkin = '2011-06-07 02:49:47',
location_lat = <lat>, location_long = <lng>, gps_strength = 66,
picture_blob_id = 1661,
authority = 1, active = 1, date_created = '2011-03-20 19:18:34',
last_login = '2011-06-07 11:15:01', panic_mode = 0, battery_level = 0.55,
battery_state = 'unplugged' WHERE people_id = 666;

These two queries are even more confusing because the first query is updating everything except people_id 666. Hundreds of rows are being painfully locked with just the first query. The second query is updating people_id 666 running the 5 sequence of events. The first query is running those same 5 sequence of events on every row involved except people_id 666 but the index for iphone_device_id is on an interecept course with two different queries. Somebody's gotta lock in the BTREE pages on a first-come-first-serve basis.

In the face of these two pairs of queries on a collision course to possibly lock the same BTREE pages within one index can be a gut-wrenching experience for InnoDB or any ACID-compliant RDBMS. Thus, an index slowdown is the destiny of these pairs of queries unless you can guarantee that the queries run with AUTOCOMMIT = 1 or by allowing dirty reads (although collisions like these make READ-COMMITTED and READ-UNCOMMITED a nightmare for MVCC).

UPDATE 2011-06-15 10:29

@RedBlueThing : In the queries from question 2, the first query is a range query, so a lot of row locks are being attained. Also notice both queries are trying to lock the same space id 0 page no 4611 n bits 152 is being locked in the PRIMARY KEY, aka clustered index.

In order to make sure you app is, at the very least, running based on the series of events you expect, there are two different options you could try:

Option 1) Convert this table to MyISAM (at least on a development server). Each UPDATE, INSERT, and DELETE will impose a full table lock on a first-come, first-serve basis.

Option 2) Try using the SERIALIZABLE isolation level. That will lock all intended rows in SHARED mode.

The sequence of events you expect will either break or be successful using these two alternative options. If both of these options fail, then you will need to look over your app and prioritize the order of execution of your queries. Once you establish that priority, you can simply undo these options (For option 1, go back to InnoDB, For option 2, go back to the default isolation level [stop using SERIALIZABLE]).


removing sql logins from one server, after migrating to another server, but all the databases were not migra Here are the details of the production environment:

I have 2 serevrs, A1 and A2.

I have completely transfered 100 logins from server A1 to A2.

Server A1 contains 5 DATABASES. But I moved only 2 databases from A1 to A2 during the migration of databases from server A1 to Server A2.

I want to drop the users, who are not required on server A2, because of remaining databases which were not migrated on it.

Please help with any scripts or dmvs to identify which users are not required on server A2 and thereby dropping them.

If a user has access to multiple databases, how can we remove the login of that  user whose concerned database was not migrated from server A1 to A2.

Thanks in advance, any valuable suggestions would be helpful for our POC.

Instead of migrating or transferring all the users from server A to server B, you should have identified the orphan users for the migrated databases.

Say from server 1 , as you said you moved database A and B:

1) Find orphan users on database A- Once you have found them , mapped the orphan users with help of script you used earlier to find the logins on server A

2) Find orphan users on database B- Once you have found them , mapped the orphan users with help of script you used earlier to find the logins on server A.

For more on how to find orphan and map them read this article on Orphan users


How to JOIN more than two tables? Following is the schema of my Tables:

Marks_Table
(SubjectCode,TypeID,MarksObtd)
Subjects_Table
(SubjectCode, Subject)
Types_Table
(TypeID, Type)

I want to Select the Marks_Table table with SubjectCode and TypeID replaced with appropriate Subject and Type.

I wrote the following query:

SELECT [Subject] , [Type] , MarksObtd
FROM [Marks_Table]
INNER JOIN [Subjects_Table]
   ON [Marks_Table].SubjectCode = [Subjects_Table].SubjectCode
INNER JOIN [Types_Table]
  ON *XXXXXXXXXXXXXXX*.TypeID = [Types_Table].TypeID

What should I write in place of XXXXXXXXXXXXXXX ? What should be the name of the table that has not been created yet?

It can be done in two queries, but is it possible to do this like above? and how?

try this

SELECT [Subject] , [Type], MarksObtd
  FROM [Marks_Table]
  INNER JOIN [Subjects_Table]
  ON [Marks_Table].SubjectCode = [Subjects_Table].SubjectCode
  INNER JOIN [Types_Table]
  ON [Marks_Table].TypeID = [Types_Table].TypeID

As james added comment, I think it is a simple join and you need to change [TypeID] in select clause to [Type]


First and Last of Month Calculations This is probably a rudimentary question for most, but for some reason I'm not getting it right.  I currently use two specific SET formulas for "1st day of last month" and "Last day of last month"

1st day of last month (11/1, November 1st):

SET @strFirstofMonth = (SELECT CONVERT(VARCHAR, DATEADD(m, DATEDIFF(m, 0, DATEADD(MM, -1, @dtDate)), 0), 112))

Last day of last month (11/30, November 30th):

SET @strLastofMonth = (SELECT CONVERT(VARCHAR, DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1, DATEADD(MM, -1, @dtDate))), -1), 112))

I'm now needing a formula for the 1st day of the month before last, and the last day of the month before last

Example.  If today is 12/6 (December 6), I need a formula for 10/1 (October 1st) and 10/31 (October 31st).

DECLARE @dtDate date = GETDATE();

-- month before last:
DECLARE @TwoMonthsAgo date = DATEADD(MONTH,-2,@dtDate);

-- first and last of that month:
SELECT
  FirstOfMonth = DATEFROMPARTS(YEAR(@TwoMonthsAgo),MONTH(@TwoMonthsAgo),1),
  LastOfMonth  = EOMONTH(@TwoMonthsAgo);

But then what are you going to do with that? You can't say:

SELECT cols FROM dbo.table
WHERE datecol BETWEEN @FirstOfMonth AND @LastOfMonth;

Why? Because if datecol also has time, you miss everything after midnight on the last day of the month.

Much better to just find the beginning of the current period, and from there it is trivial to find the beginning of the next period, and use an open-ended range instead of close-ended BETWEEN:

WHERE datecol >= @FirstOfMonth
  AND datecol <  DATEADD(MONTH, 1, @FirstOfMonth);

Incidentally, I just gave a whole presentation on this topic this morning.

Some tips for further reading:

Don't use variable types like varchar without specifying the length
Don't use shorthand like m - if you mean month, type MONTH
Don't use BETWEEN for date range queries


creating PKs for existing tables - altering column constraints I am quite new to DB2, so I googled around how to ask questions correctly and so I hope I can provide all necessary information.

I have an existing table A1230922.CALSONW with several (195) columns but no primary key. Now I want to connect an ORM that needs a primary key, I guess for row identification.

Googling brought me the result to use the following queries:

add a column with NOT NULL, and DEFAULT 0 (works fine):

ALTER TABLE tablename ADD COLUMN orm_id INTEGER NOT NULL WITH DEFAULT 0;
add automatic increasing key values (does not work, as it is not compatible with column):

ALTER TABLE tablename ALTER COLUMN orm_id SET GENERATED BY DEFAULT AS IDENTITY;

I only have German error messages (and I cannot change the interface language), saying that the attributes are not compatible with the existing column.

When I try to remove the default 0, or the NOT NULL, I get the error message (translated by myself):

ALTER table A1230922.CALSONW alter column orm_id DROP NOT NULL

An error occurred during the implicit system action "3". The returned information conaint the SQLCODE "-421", the SQLSTATE "42826", and the message token "[...] full documentation.

The docs for this SQLCODE and SQLSTATE say:

Short Description: THE OPERANDS OF A UNION OR UNION ALL DO NOT HAVE THE SAME NUMBER OF COLUMNS
The operands of a UNION or UNION ALL must have the same number of columns. System action: The statement cannot be executed. Programmer response: Correct the SQL statement so that there are exactly the same number of columns in each operand.

Obviously, I am not doing anything with UNION in this context.

Next steps would be to REORG TABLE, SET orm_id = DEFAULT, and ADD CONSTRAINT pk PRIMARY KEY(orm_id).

Version information:

Client: Win 7, 32 Bit, DB2 code release"SQL09075", "08060107".

Version: "DB2 v9.7.500.702", "s111017", "IP23286", FixPak "5".

Server: Win XP (yes, development only), exactly same version

My expertise is on DB2 for i, not really LUW. That said, I will still offer my thoughts.

I don't think you want your ID field to have a default of zero and say GENERATED BY DEFAULT.

Since this column is giving you so much trouble and is one you added, I might be tempted to drop it, and start from scratch, if practical.

I believe the commonly recommended definition for this field would be

ALTER TABLE tablename
  ADD COLUMN orm_id   INTEGER   NOT NULL
                                GENERATED ALWAYS AS IDENTITY
                                CONSTRAINT tablename_pk PRIMARY KEY;

But someone wiser may adjust this advice.


Tracing the origin of noTimeout cursors I'm currently managing a Mongo DB instance that has several applications using it. It's been accumulating noTimeout cursors, and, while it's easy enough to cull them if they start getting overwhelming, it's a waste of memory and I'd like to track down the culprit process.

Is there some way to view the origin of noTimeout cursors? Any information, whether it's the original query, the collection it's on, or even the data that's left over, would be immensely useful.

According to these docs, it appears that the only information I can get on the cursors is in db.serverStatus().metrics.cursor and includes only counts of the cursors, not any data about the individual cursors.

Is there a way to get additional information?

My apologies if this isn't appropriate for DBA, it didn't seem like the right kind of question for SO and seemed too dev focused for ServerFault. Please let me know if this is an issue.

I ended up using this command:

mongo --quiet --eval "var c=new Mongo();var o=[];db.adminCommand('listDatabases').databases.forEach(function(d){o=o.concat(c.getDB(d.name).currentOp(true).inprog.filter(function(d){return d.op=='query';}).map(function(d){return d.query;}));});o" > cursor.json

The expanded form of the actual js in there is

var conn = new Mongo();
var cursorInfo = [];
db.adminCommand('listDatabases').databases.forEach(function(d) {
    var database = conn.getDB(d.name)
    cursorInfo = cursorInfo.concat(database.currentOp(true).inprog.filter(function(d) {
                return d.op == 'query';
            }).map(function(d) {
                return d.query;
            }));
});
cursorInfo

The command will get the information from db.currentOp(true).inprog about current querying cursors for every db and save it into the file cursor.json.


Advantages to column constraints over table constraints Is there any advantages whatsoever, other than style to writing this,

CREATE TABLE foo (
  a int PRIMARY KEY,
  b int
);

And then,

CREATE TABLE bar (
  a int REFERENCES foo,
  c int
);

Over,

BEGIN;
  CREATE TABLE bar (
    a int,
    c int
  );
  ALTER TABLE bar
    ADD FOREIGN KEY (a)
    REFERENCES foo;
COMMIT;

I'm trying to build a DDL generator, so I'm wondering if pays to keep constraints on the column (where I was generating them before), or to move them all outside to the table? I know these results produce the same table, I'm just wondering if there is any advantage under the hood -- less wal? etc?

There's no difference both DDLs result in the same database structure.

The second form (or the following form) is required when the FOREIGN KEY relationship uses a compound key.

CREATE TABLE bar (
    a int,
    c int,
    FOREIGN KEY (a,c) REFERENCES foo(a,c)
);


How to generate an error when backing up to an existing file I'm using SQL Server's BACKUP command and I want the operation to generate an error if I attempt to write to an existing backup file.

By default, SQL Server seems to append to an existing file.  There are various media options like INIT,SKIP,FORMAT - but no combination of those seem to do what I'm looking for, unless I'm reading it wrong:
https://msdn.microsoft.com/en-us/library/ms186865.aspx

INIT will simply overwrite the existing file if it already exists, rather than append. However, you can prevent this from happening if you use the RETAINDAYS option, and set it to a really long time (you will have to maintain and manage these files as they get older, of course). If all of your backup files are initially set to last 10 years, and you always use INIT, you can't overwrite or append.

-- succeeds:
BACKUP DATABASE model TO DISK = 'c:\temp\m.bak'
  WITH INIT, RETAINDAYS = 3652;

-- fails:
BACKUP DATABASE model TO DISK = 'c:\temp\m.bak'
  WITH INIT, RETAINDAYS = 3652;

Error message:

  Msg 4030, Level 16, State 1The medium on device 'c:\temp\m.bak' expires on Apr 21 2025  9:12:27:000AM and cannot be overwritten.
  Msg 3013, Level 16, State 1
  BACKUP DATABASE is terminating abnormally.

If you want to avoid the extra maintenance involved with making your backups "last" extraordinarily long, you'll need to check for the file name yourself before specifying it (and possibly delete it first, or just pick a new name that doesn't conflict).

(The documentation suggests that you could do this using a unique NAME for the backup set, but on SQL Server 2014, I could not get that method to work.)

You could, of course, avoid the issue entirely by using a unique filename. Many people build the filename dynamically so that it is guaranteed to be unique and reflects - at least roughly - the time of the backup.

DECLARE @db SYSNAME = N'model', @filename NVARCHAR(255);

SELECT @filename = N'C:\temp\' + @db + N'_'
  + REPLACE(REPLACE(GETDATE(), ' ', '_'),':','_') + '.bak';

SELECT @filename; -- C:\temp\model_Apr_22_2015__9_12AM.bak

BACKUP DATABASE @db TO DISK = @filename
WITH INIT; -- shouldn't need INIT but
-- better to overwrite than append IMHO


Good way to call multiple SQL Server Agent jobs sequentially from one main job? I've got several SQL Server Agent jobs that should run sequentially. To keep a nice overview of the jobs that should execute I have created a main job that calls the other jobs with a call to EXEC msdb.dbo.sp_start_job N'TEST1'. The sp_start_job finishes instantly (Job Step 1), but then I want my main job to wait until job TEST1 has finished before calling the next job.

So I have written this small script that starts executing right after the job is called (Job Step 2), and forces the main job to wait until the sub job has finished:

WHILE 1 = 1
  BEGIN
    WAITFOR DELAY '00:05:00.000';

    SELECT *
    INTO   #jobs
    FROM   OPENROWSET('SQLNCLI', 'Server=TESTSERVER;Trusted_Connection=yes;',
           'EXEC msdb.dbo.sp_help_job @job_name = N''TEST1'',
           @execution_status = 0, @job_aspect = N''JOB''');

    IF NOT (EXISTS (SELECT top 1 * FROM #jobs))
      BEGIN
        BREAK
      END;

    DROP TABLE #jobs;

  END;

This works well enough. But I got the feeling smarter and/or safer (WHILE 1 = 1?) solutions should be possible.

I'm curious about the following things, hope you can provide me with some insights:

What are the problems with this approach?
Can you suggest a better way to do this?

(I posted this question at StackOverflow first, because I was focusing on improvement of the code. Still valid. But my guess is that people here will in general have smarter things to say about why I should not try to do this the way I'm doing it now, or provide good alternatives.)

EDIT (July 25)
Apparently there is not too much wrong with my script, according to the low number of answers pointing out problems with it :-) The alternative to this kind of scripting seems to be to use a tool that is designed for these tasks (like SQL Sentry Event Manager or ...) - or to write such a tool yourself. We won't be buying such a tool at my current company, so for now I'll just stick with the script.

SQL Sentry has a facility built exactly for this: to chain jobs and arrange them in various workflow orders.

I started using SQL Sentry years ago, before I ever joined the company, to do exactly this. What I wanted was a way to start a restore job on our test server immediately after the backup on production had finished.

What I had originally implemented was just a substantial buffer between the backup job start time and the restore start time. This wasn't exactly foolproof; since backup times varied the buffer often left us with wasted time where a restore hadn't started even though it could have. And occasionally the buffer wasn't enough.

What I implemented next was similar to what you have - I wrote a job on the test server that started shortly after the scheduled backup, and kept polling to see when the job was finished. That was later amended to just have a second step in the backup job that updated a table on the test server. Not really much different, except the restore job only had to watch a table locally instead of monitoring the job history remotely. Thinking back this could have been a trigger on that table that called sp_start_job so the job didn't have to run every n minutes (or be scheduled at all).

The final solution was to chain jobs together ... when the backup on server A finishes, Event Manager starts the restore job on server B. And if there was a third job, and a fourth job, or conditional logic based on what to do when a job fails vs. succeeds, etc., this can all be accounted for. The workflow designer will remind you quite a bit of SSIS:

The underlying mechanics of what I'm describing is not rocket surgery, of course. You could write this type of chaining wrapper yourself if you sat down and did it. Just providing you one alternative where you don't have to.


Am I clearing the query cache correctly? I'm trying to optimize a query but need to make sure the query cache is cleared properly for obvious reasons, however it doesn't seem to be working.

I ran a query the first time and it took about 3.5 seconds and all additional times it took approx 0.25 seconds; however I made some changes to it and now want to test it again without relying on the cache.

I ran:

RESET QUERY CACHE;

...and then reran the query, but it doesn't seem to have had much affect as the execution times I am getting on the first run are approx 0.35 seconds and once again approx 0.25 seconds on any additional runs.

Is there another way to do this or do you think the first ever run was an outlier?

To bypass the query cache you need to change your SELECT to SELECT SQL_NO_CACHE.

This will allow you to properly benchmark your query performance.

Be aware, that this does NOT affect the InnoDB buffer pool. Whether or not the pool is populated will also affect performance.

A good rule of thumb is to run one query (with SQL_NO_CACHE), discard the result, and then run the query 3 more times to get a good idea of how fast the query is with the buffer pool populated.


Restore bak file to smaller mdf and ldf database files I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have:

MyDatabase.mdf: 24.8GB
MyDatabase.ldf: 114.6GB

This database is backed up to a .bak file every night and shipped over to our reporting server, where it's restored. The .bak file is much smaller at only 1.8GB.

However, when I try to restore it on the reporting server, it fails due to insufficient space. There are around 100GB free on the server, and it's trying to eat up the entire 139.4GB that the files consumed on the original server. Unless my knowledge of compression is horrifically wrong, I'm fairly confident that the 1.8GB file is not actually expanding by 7400%.

My Question: Is there any way to tell SQL Server to restore this backup file without reserving that space upfront? I do not care about any of the logs; I just need the data to be there. I understand databases from the development and schema perspective, but I am by no means any sort of DBA.

This is on SQL Server 2008 R2. Thanks for any help or suggestions.

The backup contains two pieces of information:

the database files layout and metadata, ie. RESTORE FILELISTONLY ...
the actual data

The actual data in the backup is those 1.8 GB. But the metadata describes the layout of files as on the original production server. While it looks like compression, is not actual compression. Is just metadata vs. content. the problem is that during RESTORE the reporting machine creates a layout as described in the metadata, therefore 114.6GB + 24.8GB. Now for the real question: can you restore it w/o recreating the original DB file layout? No. However, there is another solutions to your problem: bring the original DB back in check. Shrinking the Transaction Log covers the steps you need to do, since the log is your major pain point. Next I would deploy log shipping instead of backup/restore.


"relation does not exist" trying to import mysql dump into postgres environment:

ubuntu 10.04
mysql server 5.1.69
postgres 9.2

Here's the sequence of steps:

created a new postgres database, myDatabase
executed this command: mysqldump -u root -p --compatible=postgresql myDatabase > mydump.sql
executed this command: psql -h localhost -d myDatabase -U postgres
At postgres prompt, executed these commands:

    SET standard_conforming_strings = 'off';
    SET backslash_quote = 'on';
    \i mydump.sql;

At this point, the process began, and after getting tons of

HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
psql:mydump.sql:196: WARNING:  nonstandard use of escape in a string literal
LINE 1: ...012053',50.18,50.36,49.4,49.69,59592,184824),(115,'\"2012060...

I get

psql:mydump.sql:196: ERROR:  relation "pricedata" does not exist
LINE 1: INSERT INTO "pricedata" VALUES (113,'19981102',0.6275,0.6275...

I checked the mydump.sql file, and it has a CREATE TABLE "pricedata", but obviously this didn't have the desired effect on the postgres db.

I'm not sure what I'm supposed to do to get this to work.  Any assistance would be greatly appreciated.

This won't work. Don't even try feeding mysqldump output directly into psql. You'll need to dump schema and data separately, convert the schema either by hand or with a tool, load the converted schema into PostgreSQL then load the dumped data.

mysqldump's compatibility flags are moderately useful for dumping data but pretty useless for dumping schema definitions.

The "relation does not exist" error will be a follow-up error caused by a prior one. Try running with psql --set ON_ERROR_STOP=1 .... .


MySQL permission denied for database I have a remote MySQL server running on x.x.x.x. And there is a database named abc.

I can do the following

mysql -h x.x.x.x -uroot
use abc

But when I try to

mysql -h x.x.x.x -uroot --database=abc

I get

ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'abc'

Why is that? And how do I fix it?

edit:

I have now resolved the issue by doing the following:

ssh in the box
run mysql -uroot
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

But I still don't understand why.

mysql> SELECT USER(),CURRENT_USER();

+-----------------------------+----------------+
| USER()                      | CURRENT_USER() |
+-----------------------------+----------------+
| root@Maxs-MacBook-Air.local | root@%         |
+-----------------------------+----------------+

mysql> SHOW GRANTS FOR root@'%';

+------------------------------------------------------------------+
| Grants for root@%                                                |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%'                                 |
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'root'@'%' WITH GRANT OPTION |
+------------------------------------------------------------------+

mysql> SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) from mysql.user WHERE user='root';
ERROR 1142 (42000): SELECT command denied to user 'root'@'Maxs-MacBook-Air.local' for table 'user'

Max

You cannot run

SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) from mysql.user WHERE user='root';

because you do not have remote rights to the mysql database.

You only have remote rights to mydb

GRANT ALL PRIVILEGES ON `mydb`.* TO 'root'@'%' WITH GRANT OPTION;

When you ran

mysql -h x.x.x.x -uroot --database=abc

you evidently do not have remote rights to database abc.

When you ran

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

you should now have rights to every database.


Why does MySQL not have hash indices on MyISAM or InnoDB? I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up with that?

Many databases don't support hash based indexes at all.

In order for a hash table to be efficient you need to know the number of rows that are likely to be present otherwise the base hash table will be far too large (many empty entries, wasting space and potentially disk IO) or too small meaning that indirection is often used (possibly multiple levels of indirection, or even worse if the hash implementation is single-level you could end up performing a linear search over a fair number of records) at which point things are probably no more efficient then a tree based index anyway.

So to be generally useful (i.e. usually better than the alternative) the index needs to be rebuilt occasionally as data grows (and shrinks) which could add a significant intermittent overhead. This is usually fine with memory based tables as the rebuild is probably going to be pretty fast (as the data is always going to be in RAM and is not likely to be massive in any case), but rebuilding a large index on disk is a very heavy operation (and IIRC mySQL doesn't support live index rebuilds so holds a table lock during the operation).

Hence hash indexes are used in memory tables as there they are generally better performers, but disk based tables don't support them as they could be a detriment to performance not a bonus. There is nothing to stop hash indexes being made available for disk based tables of course, no doubt some databases do support the feature, but presumably they are not implemented in ISAM/InnoDB tables as the maintainers do not consider the feature worth adding (as the extra code to write and maintain is not worth the benefit in those few circumstances that it makes a significant difference). Perhaps if you strongly disagree you could talk to them and make a good case for the implementation of the feature.

If you are indexing large strings then implementing your own pseudo-hash index (by storing a hash of the value as well as the actual value, and indexing that has column) may work, but this is only definitely more efficient for large strings (where computing the hash value and searching the tree index by this value is always likely to be faster then just searching a tree index using the larger values for comparison, and the extra storage used is not going to be significant) so do some performance analysis before implementing this in production.


Building Dynamic SQL-Server Where Clause Let us review this dba.exchange Oracle question for SQL-Server.

This is SaUce's code, after a little formatting:

CREATE PROCEDURE GetCustomer
    @FirstN nvarchar(20) = NULL,
    @LastN nvarchar(20) = NULL,
    @CUserName nvarchar(10) = NULL,
    @CID nvarchar(15) = NULL
as
begin
    DECLARE @sql nvarchar(4000)

    SELECT @sql = 'C_FirstName, C_LastName, C_UserName, C_UserID
FROM CUSTOMER
WHERE 1=1 '

    IF @FirstN IS NOT NULL
        SELECT @sql = @sql + ' AND C_FirstName like @FirstN '

    IF @LastN IS NOT NULL
        SELECT @sql = @sql + ' AND C_LastName like @LastN '

    IF @CUserName IS NOT NULL
        SELECT @sql = @sql + ' AND C_UserName like @CUserName '

    IF @CID IS NOT NULL
        SELECT @sql = @sql + ' AND C_UserID like @CID '

    EXEC sp_executesql @sql, N'@C_FirstName nvarchar(20), @C_LastName nvarchar(20), @CUserName nvarchar(10), @CID nvarchar(15)', @FirstN, @LastN, @CUserName, @CID
end
go

SaUce mentioned in his second note, that he had 1000 lines of code to prevent SQL-Injection. My feeling is that he needn't do this to shield his stored procedure.

My Question

is this SQL-Server Procedure immune
against SQL-Injection by itself
is it as satisfying solution with
respect to performance.

According to Erland's Sommarskog's article about dynamic sql, the use of parameterized queries should be the best point in fighting against sql injection.
About performance, I also see no problem, because the execution plan of dynamic sql is reused since MSSQl 2005, if I remember correctly. Only in sql 2000 and older there was a problem with plan reuse.

His points in taking over sql injection are:
"

* Never run with more privileges than necessary. Users that log into an application with their own login should normally only have EXEC permissions on stored procedures. If you use dynamic SQL, it should be confined to reading operations so that users only need SELECT permissions. A web site that logs into a database should not have any elevated privileges, preferably only EXEC and (maybe) SELECT permissions. Never let the web site log in as sa!

* For web applications: never expose error messages from SQL Server to the end user.

* Always used parameterised statements. That is, in a T-SQL procedure use sp_executesql, not EXEC().

The first point is mainly a safeguard, so that if there is a injection hole, the intruder will not be able to do that much harm. The second point makes the task for the attacker more difficult as he cannot get feedback from his attempts."


Script SQL Server Agent jobs to file I am busy making a backup of all Agent Jobs by scripting them all to file.`How would i rewrite the below to use BCP instead

        BEGIN
        SET @EchoCommand = 'ECHO ' + @EchoField + ' >> ' + @Path
        + @JobName
        + REPLACE(REPLACE(CONVERT(VARCHAR(200), GETDATE()), ' ',
        ''), ':', '') + '.sql'

Something similar to the below

        SET @EchoCommand = 'bcp
        "exec['+DB_NAME()+'].dbo.CreateBackUpFromJob' +
        @JobName + '" queryout "\\ServerName\SQL_Backups\' + @JobName +
        '.sql" -c -UTF8 -T -SLOCALSERVER'

It's very easy to script out SQL Server Agent Jobs using Powershell (if that is an option).  The following is an example of how you can use Powershell to script the Agent Jobs and also delete previous scripted jobs that were created over a number of days ago.  This script generates a file per Agent Job

You can play around with the code to see if it meets your needs.

# Set sql server
$Instances = "localhost"

foreach($Instance in $Instances)
    {
    # Create sql server and db objects
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Instance;

    # Write out the job names
    #$srv.JobServer.jobs|foreach-Object {Write-Host "$_.name" }

    # Script out the Jobs
    $CurrentDate = get-date -f "yyyy-MM-dd"
    $Instance = $Instance.Replace("\","-") + "-" + $CurrentDate
    $srv.JobServer.jobs|foreach-Object {$_.script() > "C:\Temp\SqlJobs\$Instance-$_.txt"}
    }

# Purge files older than 2 weeks
$cutoff = (get-date).AddDays(-14)
dir "C:\Temp\SqlJobs\"|Get-ChildItem -incl '*.txt' -rec|Where{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer}|remove-item


Numbered stored procedures: introduction and deprecation? SQL Server and Sybase ASE both support the highly dubious feature of grouping stored procedures by appending a number:

CREATE PROCEDURE Foo;1 AS ...
CREATE PROCEDURE Foo;2 AS ...

The only tangible benefit is that DROP PROCEDURE Foo will drop all of these procedures together -- and that's it; to call them you still need to append the number explicitly. The drawbacks of allowing a semicolon to be part of a procedure name and having non-obvious DROP semantics should be obvious, and indeed many tools don't (properly) support this format and things like contained databases outright forbid them.

In what version of SQL Server and/or Sybase ASE was this feature introduced, and from what version onwards was it deprecated? I'd consult the documentation, but documentation that goes back far enough does not appear to be available. I've found the following:

SQL Server Books Online 2005 already mentions them as deprecated.
SyBooks Online for ASE 12.5 mentions it as being available there as well, but having a flag to disallow them.

This leads me to think the feature might have existed in very early versions indeed, possibly before the split between Sybase and Microsoft, but still doesn't tell me what the earliest introduction and deprecation was. The asteroid to kill this dinosaur is still in orbit, but how old is the dinosaur?

Looking at the SQL Server 2000 T-SQL help, there is no mention of deprecation so it seems numbered stored procedures were deprecated with the release SQL Server 2005.

I don't recall numbered procs ever not being in the Microsoft SQL Server product on Windows so I'm pretty sure the feature stems from it's Sybase roots and is in the 4.21a Windows release. I can't speak to the OS/2 version since I dodged that bullet :-)


Is the limit on the number of databases in an Azure SQL managed instance a hard limit? I see the documentation for Azure SQL Managed Instance lists a limit of 100 databases per managed instance.   I would like to understand if this is a soft limit that is adjustable on request or a hard limit.

This is a hard limit that cannot be changed at this time.

The limit is imposed to ensure seamless operation of the instance and its performance.


How to decipher the transaction message in innodb status log. I have several transactions that seem have stalled. Looking at the innodb status log, I found several messages similar to the following:

---TRANSACTION 69DBBB, ACTIVE 947 sec starting index read, thread declared inside InnoDB 373
mysql tables in use 4, locked 0
MySQL thread id 820, OS thread handle 0x7ef64c454700, query id 625198....

Trx read view will not see trx with id >= 69DBBC, sees < 6699C2

I need help understanding exactly what it means.

Thanks

The output is truncated when there are many running transactions. The size depends on your version. See "Standard InnoDB Monitor output is limited..." for your version on https://dev.mysql.com/doc/refman/5.5/en/innodb-monitors.html


Error on CDC enabled Table I am trying to create an SP on a CDC enabled database. SP contain an update statement for CDC enabled table using UPDATETEXT command but CREATE PROCEDURE statement has generated an ERROR Message.

  "UPDATETEXT is not allowed because the column is being processed by a concurrent snapshot or is being
      replicated to a non-SQL Server Subscriber or Published in a publication allowing Data Transformation Services (DTS) or
      tracked by Change Data Capture."

I have followed the instructions on a post that disable CDC for the table for which sp has command UPDATETEXT and then create SP and after sp creation re- enable CDC for that table

Question:

Whats the reason behind this Error ?
Will UPDATETEXT command effect logging in future? because we are thinking to use CDC for auditing change.

Whats the reason behind this Error ?

It is in the error message,

  "UPDATETEXT is not allowed because ...  or  tracked by Change Data
  Capture.

You cannot use it on columns tracked by CDC.

Testing

Create table & insert sample data

CREATE TABLE dbo.tablea
(id int identity(1,1) PRIMARY KEY NOT NULL, UpdateTextTest TEXT)

INSERT INTO Dbo.tablea(UpdateTextTest)
VALUES('bBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbB')

Test updatetext without CDC

DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(UpdateTextTest)
FROM dbo.tablea

UPDATETEXT   tablea.UpdateTextTest  @ptrval 8 1 'c';

This runs fine

Result

id  UpdateTextTest
1   bBbBbBbBcBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbB

The 9th value was changed to 'C'.

Enable CDC on the db and table

use my_test
EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'tablea',
@role_name     = NULL ,
@filegroup_name = N'PRIMARY',
@supports_net_changes = 1;
GO

Try to run the same query

DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(UpdateTextTest)
FROM dbo.tablea

UPDATETEXT   tablea.UpdateTextTest  @ptrval 8 1 'c';

The same error

Msg 7137, Level 16, State 1, Line 41
UPDATETEXT is not allowed because the column is being processed by a concurrent snapshot or is being replicated to a non-SQL Server Subscriber or Published in a publication allowing Data Transformation Services (DTS) or tracked by Change Data Capture.

By the way, updatetext might be removed in future versions, and TEXT,NTEXT and IMAGE datatypes are also deprecated

  This feature is in maintenance mode and may be removed in a future
  version of Microsoft SQL Server. Avoid using this feature in new
  development work, and plan to modify applications that currently use
  this feature. Use the large-value data types and the .WRITE clause of
  the UPDATE statement instead.

Source

What could you do?

I would advise to try and change the column to either nvarchar(max), varchar(max) or varbinary(max) depending on the deprecated datatype used, and then using .write .

Testing with text:

UPDATE tablea
SET UpdateTextTest .WRITE (N'c',9,1)

Msg 258, Level 15, State 1, Line 44
Cannot call methods on text.

As it is now, I cannot use .Write since the datatype is text.

Changing the datatype and testing the query again, with CDC enabled.
    Alter table tablea
    alter column UpdateTextTest varchar(MAX)

UPDATE tablea
SET UpdateTextTest .WRITE (N'c',9,1)

Result:

id  UpdateTextTest
1   bBbBbBbBccbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbB

the 10th value was also changed to 'c'.

Extra info

Readtext works with cdc

DECLARE @ptrval varbinary(16);
SELECT @ptrval = TEXTPTR(UpdateTextTest)
   FROM tablea pr

READTEXT tablea.UpdateTextTest @ptrval 1 1;

Result:

 UpdateTextTest
B

Writetext does not work with CDC

DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(UpdateTextTest)
FROM tablea pr
WRITETEXT tablea.UpdateTextTest @ptrval 'DDDDDD';

Error:

Msg 7138, Level 16, State 1, Line 92
The WRITETEXT statement is not allowed because the column is being replicated with Data Transformation Services (DTS) or tracked by Change Data Capture.


What is CP1 in SQL_Latin1_General_CP1_CI_AI? I was wondering what the "CP1" means in the collation "SQL_Latin1_General_CP1_CI_AI".

This should be the codepage, but doesn't exist a codepage 1 (for ex. I know it exists a CP850, but never heard about a CP1).

CP1 denotes code page 1252 as described in the SQL Server Books Online.  You can also use the query below to identify the collation code page.

SELECT name, COLLATIONPROPERTY(name, 'CodePage') AS CodePage
FROM fn_helpcollations()
ORDER BY name;


Can I use the "import" tool on PHPMyAdmin multiple times? I am moving databases between a couple of shared hosting accounts, and both of them only give access to the MySQL databases through phpmyadmin.

The importing account's upload limit is 50MB. I would like to export the database in parts (by selecting only a few tables to export at a time), using the export tool, then use the import tool more than once to get around the limit. Is this possible?

Yes this is possible, although you need to watch out for the following:

a) Export using extended inserts so that the extended SQL syntax is used which will reduce the size of your SQL files (If you are exporting using PHPMyAdmin select both complete and extended insert options, as it creates complete inserts with extended insert syntax for large tables)

b) Try using the GZip format which will compress the SQL files and make them smaller (depending on the hosting provider setup this may be an issue so you need to test it out with a small table)

c) Export the tables in such an order that tables with foreign keys are imported last


PostgreSQL : FATAL: could not open log file "/path/to/log/filesystem/postgresql-Sun.log": No such file or directory I am new to PostgreSQL and stuck on an issue that PostgreSQL isn't starting up. It reported:

FATAL:  could not open log file "/path/to/log/filesystem/postgresql-Sun.log": No such file or directory
06:52:32.530 UTC [7440] LOG:  database system is shut down

I tried to find the solution online and changed the permission of the postgres user using chown, but that also didn't help or maybe I missed something.
Please advise.

User, which runs postgresql, often postgres, must be able to traverse to this /path/to/log/filesystem/ (often /var/log/postgresql), specified by log_directory config option and be able to write files there. If this path does not exist, you will get No such file or directory error.
You can try switching to this user (e.g. su postgres) and check cd /path/to/log/filesystem (whatever this path really is) and if that throws error, then create the directories (e.g. mkdir -p /path/to/your/log_directory).
There's also a possibility that service manager changes mount points for the service, like systemd is capable of.


Problem with a surrogate key? I am working with an application for students management. For the student's table I used a surrogate key (auto increment id). The problem I face with the surrogate key is when the user wants to show information about a student he must enter the id but the user knows only  the name of the student (and I can't use the name as a primary key because it's not unique). How can I solve this problem?

There are a few things of interest here (note: I currently work in this field, so I'm not going to go into too much depth with regards to system functionality and requirements).

Avoid exposing database internal identifiers to the outside world. The field values in an integer IDENTITY column have no meaning outside the database. The absolute farthest they should go is the client-side data access layer.
Normally, students (people) are identified within a jurisdiction by a unique number (i.e., student number) assigned by the jurisdictional authority (i.e., department of education). In some jurisdictions, students (people) need to exist in the system without a unique number because the numbers aren't assigned synchronously as students (people) are registered. I don't know if you need to deal with that or not. If that is the case, students (people) without the unique number are determined to be unique by a business key, such as the combination of first name, last name, gender and birth date. Everyone, though, will have a unique surrogate key value in the database, which you (not the user) will use to uniquely identify a record.
You will have to implement functionality to search for students. Most of the time users of the system will be searching based on a name (or partial name), not a student number. The resulting search should display students to which the user "has access." For example, if the user is a teacher, they should only have access to the students they teach. The search interface will only allow the user to select a single student -- which will give you the internal identifier you need -- before proceeding.

So in summary, I think you're on the right path with the database design, it's just a matter of implementing some extra functionality on the front end for the user to be able to uniquely identify a student.


Join two tables brings mismatch values MYSQL I have two tables with >4million records i need to make a select query with where two columns match bring both tables value on this match and then i will insert that into a 3rth table:
This is table A: (bitfinex)

This is table B: (Kraken)

I need to do a  SELECT where timestamp and exchange_pair matches, as you can see in this image from table B the same timestamp can have multiple exchange_pair, what i need to do is match those with the other table columns and bring both table content

this was the query i used at once but when analyzing the results i had some mismatch values on the joins ( i did not found the relation, it was not either timestamp or the exchange pair) i wonder if my query syntax is right for the result im looking for.
INSERT IGNORE INTO spreads(timestamp,exchange_pair,platform_a,platform_b,a_bid_price,b_bid_price, high_value,spread_percentage,spread)
SELECT B.timestamp, B.exchange_pair,'bitfinex' as platform_a,'kraken' as platform_b,
 B.bid,
 K.bid_price,
 (SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)),
 (SELECT (((ABS(K.bid_price - B.bid))*100)/(SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)))),
  ABS(K.bid_price - B.bid)  AS spread
FROM bitfinex AS B
JOIN kraken AS K
 ON B.timestamp = K.timestamp
JOIN kraken AS K2
 ON B.exchange_pair = K2.exchange_pair
WHERE K.exchange_pair = B.exchange_pair AND B.timestamp = K.timestamp ;

im still confused if using inner join or just join, also i added the "Where" clause to make sure exchange pair and timestamp matches.

Point 1 : INNER JOIN & JOIN  both are same thing.
Point 2 : No need to do an extra join on K2
You can use this :
INSERT IGNORE INTO spreads(timestamp,exchange_pair,platform_a,platform_b,a_bid_price,b_bid_price, high_value,spread_percentage,spread)
SELECT B.timestamp, B.exchange_pair,'bitfinex' as platform_a,'kraken' as platform_b,
 B.bid,
 K.bid_price,
 (SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)),
 (SELECT (((ABS(K.bid_price - B.bid))*100)/(SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)))),
  ABS(K.bid_price - B.bid)  AS spread
FROM bitfinex AS B
JOIN kraken AS K
 ON B.timestamp = K.timestamp
and
 K.exchange_pair = B.exchange_pair  ;


Frustrating SQL Server and VS Express 2013 Issue I recently reinstalled Windows and am trying to set up SQL Server again with an existing database. I've tried installing SQL Server 2012 and 2014, and continuously get the same error. The error says that the database is version 782 and this version of SQL Server is only compatible with 706 and below. I get this error with both SQL Server 2012 and 2014. I thought 2014 was compatible with 782+.

How can I attach the database?

Sql Server DB verion 782 is for SQL Server 2014 and 706 is for SQL Server 2012.

and SQL Server 2014 will support all the previous version, but SQL Server 2012 will not support DB version 782(SQL Server 2014 version)

Probably something wrong with installation of SQL server 2014 and you still have SQL server 2012 version, Please check the SQL server version

SELECT @@version


How does SSIS Balanced Data Distributor decide to split records? I have been using the balanced data distributor control in SSIS, but am coming across an issue in which my parallel destinations (Salesforce bulk api via TaskFactory) are getting in lock conflicts.  I was told by Salesforce support to try sorting such that there are no conflicting locks in separate batches.

How does the BDD control distribute?  Does it split using fetch/limit, or does it do a modulus split across all records?

You don't really have much control over how the SSIS Balanced data distributer control distributes data, but you could influence the order in which the data by the BDD is received.

From the docs (emphasis mine):

  This transform takes a single input and distributes the incoming rows
  to one or more outputs uniformly via multithreading. The transform
  takes one pipeline buffer worth of rows at a time and moves it to the
  next output in a round robin fashion.

The pipeline buffer size can be influenced by properties such as the DefaultBufferMaxRows and DefaultBufferSize or AutoAdjustBufferSize. Check the docs on data flow performance features for details on how it's all calculated.

So in short, You'll have a hard time figuring out exactly how many records are sent out each time the BDD switches to a new output.

Knowing how the BDD distributes data by just taking whatever is in the incoming buffer and sending it to an output destination you could maybe figure out ways to make sure the incoming data is sorted in a way that you can avoid locks later on, but remember that each flow after the BDD will probably have some processing in it too, and there is no way you can tell if at the end the data will be processed in the same amount of time by each path the data takes, so sorting it before the BDD doesn't guarantee it's still in the same order later on.