Shorthand for "UNIQUEIDENTIFIER" data type name? The data type keyword "UNIQUEIDENTIFIER" is a chore to type.  I want a shorthand replacement.
At first glance, it seems that SSMS provides two; the words "GUID" and "UID" are both rendered in blue in the query editor (using the default syntax color scheme).
But they are not listed as reserved or future keywords in Microsoft Docs.  I tried to use them like non-reserved data type keywords (such as "DATE" or "INT")
Declare @guid uid;

and got this error message:

Msg 2715, Level 16, State 3, Line 1
Column, parameter, or variable #1: Cannot find data type uid.
Parameter or variable '@guid' has an invalid data type.

I can create a UDT for UNIQUEIDENTIFIER named "UID".  The result appears to behave as a non-reserved keyword:
Create Type [uid] From uniqueidentifier null;
GO
Declare @guid uid = newid();
Print @guid;
GO

B398DA7A-0DF2-49B6-8415-82D31EAA9013

So I have two questions.

Are the words "GUID" and "UID" actually keywords, or does SSMS color them for a different reason?
What considerations/risks might come with using the words "GUID" or "UID" as UDT names?  I'm mostly worried about future tSQL language compliance.

The words guid and uid are coloured like any other object or possible column in a SQL statement in SSMS.
When typing out the columns in the following statement, each column is coloured blue, because it is a column from the tables I am referencing.
SELECT sao.name AS object_name,
       sao.type_desc AS object_type,
       sac.name AS column_name
FROM   sys.all_columns sac
       JOIN sys.all_objects sao
            ON  sac.object_id = sao.object_id
WHERE  sac.name LIKE 'guid'
       OR  sac.name LIKE 'uid'

Coloured as per my SSMS:

The above statement queries all objects in the sys views all_objects and all_columns and displays the corresponding object type.

+--------------------------------------+----------------------------------+-------------+
|             object_name              |           object_type            | column_name |
+--------------------------------------+----------------------------------+-------------+
| sysguidrefs                          | SYSTEM_TABLE                     | guid        |
| sysobjects                           | VIEW                             | uid         |
| sysusers                             | VIEW                             | uid         |
| systypes                             | VIEW                             | uid         |
| sysprotects                          | VIEW                             | uid         |
| sysprocesses                         | VIEW                             | uid         |
| cryptographic_providers              | VIEW                             | guid        |
| syscacheobjects                      | VIEW                             | uid         |
| fn_trace_gettable                    | SQL_INLINE_TABLE_VALUED_FUNCTION | GUID        |
| dm_cryptographic_provider_properties | VIEW                             | guid        |
| dm_xe_packages                       | VIEW                             | guid        |
+--------------------------------------+----------------------------------+-------------+

As you can see, there are a couple of columns named either guid or uid in a SQL Server instance, that could possibly be referenced in a statement and this is why SSMS will colour code them blue.


Set default space free in sql log We have a database that needs 40% free in the log files at all times or we get an alert.
I cannot find a way to set growth when a limit is reached.

Of course we can set autogrowth to a certain limit but then we would have to go into it weekly to add 40% based on last growth.

Any ideas?
SQL 2012

SQL Server doesn't have this functionality built in, but you could start with this DBA.se question:

Query to report disk space allocation and used space

Then based on the output, grow the log files appropriately.

I'll be honest though: I'm decent with T-SQL, and it'd take me longer to write that than it would to just change the threshold in my monitoring software so that it's more appropriate. Because here's the problem: if you start growing your log files like crazy, next thing you know, your monitoring software is going to be complaining about having less than 40% free space on your drives, hahaha.


what is a safe way to stop the logreader? Applying cumulative updates and service packs on sql server 2016 -

The server is involved in transactional replication, being a publisher,

The server is also a virtual machine.

I need to stop the logreader application in order to carry on with the updates.

what could be a safe way to get this done?

You can stop the log reader agent job in sql server agent. It is used by publications that are involved in t-rep. It basically copies the transactions marked for replication from the transaction log into the distribution database. Should be safe to stop it.


Cannot open table mysql/innodb_index_stats After a fresh install of XAMPP and import from my linux live db that is working to the windows Dev-stage, I started encountering problems with INSERT. Everything else seems to work well.

The errors I get in the mysql_error.log are:

2013-08-15 12:44:49 16c0 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-08-15 12:44:49 16c0 InnoDB: Recalculation of persistent statistics requested for table "sizaradb"."pages" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
2013-08-15 12:44:59 16c0 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-08-15 12:44:59 16c0 InnoDB: Recalculation of persistent statistics requested for table "sizaradb"."translations" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.

I cannot open table mysql/innodb_index_stats from the internal data dictionary of InnoDB though the .frm file for the table exists

I tried to fix it by googling the errors and trying to see fixes of other people, but it is still not working. I have been working on this for 2 days now.

Please help me solve it.

PROBLEM
In MySQL 5.6, ibdata1 includes 5 InnoDB in the mysql schema.
mysql> select table_name from information_schema.tables
    -> where table_schema='mysql' and engine='InnoDB';
+----------------------+
| table_name           |
+----------------------+
| innodb_index_stats   |
| innodb_table_stats   |
| slave_master_info    |
| slave_relay_log_info |
| slave_worker_info    |
+----------------------+
5 rows in set (0.00 sec)

mysql>

In MySQL versions before 5.6, if you shutdown mysql, delete ibdata1, and start mysql back up, ibdata1 gets recreated. If you do this with MySQL 5.6, these 5 tables are not recreated. Even if your deleted ibdata1, the following 10 files are still in /var/lib/mysql/mysql:

innodb_index_stats.frm
innodb_index_stats.ibd
innodb_table_stats.frm
innodb_table_stats.ibd
slave_master_info.frm
slave_master_info.ibd
slave_relay_log_info.frm
slave_relay_log_info.ibd
slave_worker_info.frm
slave_worker_info.ibd

I learned this early on. With a newly created or damaged ibdata1, there are no corresponding data dictionary entries for those 5 tables.
In your particular case, I suspect you copied ibdata1 from a Linux server to the Windows server. MySQL/Windows would be expecting the data dictionary to have the DOS Filename Format. Moving that ibdata1 to Windows, MySQL/Windows cannot relate to the Linux filename and path for pointing to the .frm and .ibd files.
SOLUTION
STEP 01 : Install MySQL on another DB Server
STEP 02 : mysqldump only those 5 tables
TABLELIST="innodb_index_stats"
TABLELIST="${TABLELIST} innodb_table_stats"
TABLELIST="${TABLELIST} slave_master_info"
TABLELIST="${TABLELIST} slave_relay_log_info"
TABLELIST="${TABLELIST} slave_worker_info"
mysqldump -uroot -p mysql ${TABLELIST} > mysql_innodb_tables.sql

STEP 03 : Copy mysql_innodb_tables.sql to the DB Server with bad tables.
STEP 04 : Execute mysql_innodb_tables.sql
STEP 05 : Run FLUSH TABLES; (Optional)
Give it a Try !!!


Is it OK to restore master db backup, after changing WSFC clustered instance into non-clustered instance I'm converting WSFC fail-over clustered SQL Server 2012 instance into non-clustered one.
My question in short: Is it OK to restore a master database backup taken from the clustered instance, into the newly installed un-clustered instance?
To achieve the conversion I've followed the instruction in this link.
Basically I've removed all the nodes from the cluster, remove the cluster, re-install standalone SQL Server, then re-mount the previously shared NTFS volume locally and attached all user databases.
Then, I've transferred logins using sp_help_revlogin, linked servers, jobs, server roles, some permissions as shown in this link.
But still some things were missing from the re-installed instance, like permissions for system procedures, and these were stored in the master db.
Is it OK to restore the entire master db from the old clustered instance, or do I need to avoid such restore when some server configuration was changed, in my case un-clustered?

To make sure I called up the Microsoft support and they said this might work, but not supported. They suggested not to do it. According to the support guy there are some difference in system databases when clustered or not, and the difference can cause an error, and there are known such cases too.
And they gave me this link.
So the answer seems to be no.


Sort values in MySQL using HAVING clause and IN function I have this portion of code with user's phone extensions:

GROUP BY IFNULL(mxuser.EXTENSION, mxuser_1.EXTENSION)
HAVING Ext IN (207, 213, 235, 214, 226, 218, 224, 227, 216)

It works fine, and it sorts values in ASC order ( 207, 213, 214..) Is it possible to sort values in different order? Something like this:

( 235, 207, 218 .. )

I'm not sure why you're putting the EXT IN a HAVING clause, but this should work.

WHERE IFNULL(mxuser.EXTENSION, mxuser_1.EXTENSION)
  IN (207, 213, 235, 214, 226, 218, 224, 227, 216)
GROUP BY IFNULL(mxuser.EXTENSION, mxuser_1.EXTENSION)
ORDER BY ext=235 DESC, ext=207 DESC, ext=218 DESC, ext;


How to interpret and modify a dBase (.dbf) file? I'm working with dBASE (.dbf) files for a project using a GIS software because it seems that they are more efficient. I've converted several .xml files to .dbf with OpenOffice 3.4 and loaded into the GIS however in some columns, the fields that appeared with different numbers in Open Office, turn to a series of one (1) in the GIS attribute table.

This is an example of my first row of one element:

Na,N,6,2

I think that changing the first row of every columns of my .dbf file I can set up the properties.

First question:

I would like to know what does it means every character. I know that the first (e.g. Na) is the name of the field, N is probably the type? Number? I've no idea about the third and the forth (red and blue arrows).

Second question:

Why if I write Na when I load the file it become NA, with capitalized letters?

Thanks a lot!

Do NOT directly manipulate the .dbf-files. There is an ODBC driver that does all the low-level work for you. Save yourself the headaches.


How to restrict column creation for existing tables? I have a table already created in a schema which has columns a, b, and c.  I want to restrict the user(s) from creating new columns.

How to do this?

I have gone through the manual pages about GRANT and REVOKE, but in the manual it is explained only for INSERT, UPDATE, SELECT, etc.

I want to de something like

REVOKE ALTER on Table schemaname.tablename  FROM user;

The way to prevent table modification is to change the ownership to a different role.  Tables can be altered only by the owner and superusers - so change the ownership to a non-superuser.  I find it practical to keep a database ownership role for solely this reason (which might make life easier elsewhere, too, like for example when setting default privileges).


Unused Indexes - PostgreSQL Database I have used attached query to check unused indexes in my EDB9.5 DB. It's showing attached indexes are the unused indexes. Is it safe to drop below unused indexes?
SELECT
    idstat.relname AS TABLE_NAME,
    indexrelname AS index_name,
    idstat.idx_scan AS index_scans_count,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    tabstat.idx_scan AS table_reads_index_count,
    tabstat.seq_scan AS table_reads_seq_count,
    tabstat.seq_scan + tabstat.idx_scan AS table_reads_count,
    n_tup_upd + n_tup_ins + n_tup_del AS table_writes_count,
    pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size
FROM
    pg_stat_user_indexes AS idstat
JOIN
    pg_indexes
    ON
    indexrelname = indexname
    AND
    idstat.schemaname = pg_indexes.schemaname
JOIN
    pg_stat_user_tables AS tabstat
    ON
    idstat.relid = tabstat.relid
WHERE
    indexdef !~* 'unique'
    and idstat.relname='cwt_act'
ORDER BY
    idstat.idx_scan DESC,
    pg_relation_size(indexrelid) DESC;

Please suggest!

You are missing a few points:

there could be a GiST index supporting an exclusion constraint

an index on an expression can help the optimizer get better estimates for that expression, even if it isn't scanned

I recommend the query from my blog:
SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT i.indisunique   -- is not a UNIQUE index
  AND NOT EXISTS          -- does not enforce a constraint
         (SELECT 1 FROM pg_catalog.pg_constraint c
          WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;


Partition pruning with multiple date columns I have a large table in Oracle 11g database that holds historical data from several years, so I would like to partition it by year. The problem is that the table has multiple date columns and they are all used in queries, so I can't just pick one date column and use it as partition key.

Most of the time dates are close to each other, so I have created partitions for each year, plus one "overflow" partition that holds the rows that cross the year boundary. Here is a simplified example:

create table t (
  start_year int,
  end_year int,
  partition_year int as (case when start_year=end_year then start_year else 0 end),
  data blob
)
partition by range(partition_year) (
  partition poverflow values less than (1000),
  partition p2000 values less than (2001),
  partition p2001 values less than (2002),
  partition p2002 values less than (2003),
  partition p2003 values less than (2004),
  partition p2004 values less than (2005)
);

The problem with this approach is that partition_year must be explicitly referenced in queries or partition pruning (highly desirable because the table is large) doesn't take effect. This table is used for ad-hoc aggregate queries by multiple users; I can't expect that they all remember this logic.

This can be solved with a view

create or replace view v as
select *
from t
where partition_year=start_year
  and partition_year=end_year
  and partition_year>1000
union all
select *
from t partition (poverflow);

Now queries like this one

select * from v where start_year >= 2003 and end_year <= 2004;

Use correct partitions (5-6 + 1 in plan below):

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |  4030 |     2   (0)| 00:00:01 |       |       |
|   1 |  VIEW                      | V    |     1 |  4030 |     2   (0)| 00:00:01 |       |       |
|   2 |   UNION-ALL                |      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|      |     1 |  2041 |     2   (0)| 00:00:01 |     5 |     6 |
|*  4 |     TABLE ACCESS FULL      | T    |     1 |  2041 |     2   (0)| 00:00:01 |     5 |     6 |
|   5 |    PARTITION RANGE SINGLE  |      |     1 |  2041 |     2   (0)| 00:00:01 |     1 |     1 |
|*  6 |     TABLE ACCESS FULL      | T    |     1 |  2041 |     2   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("START_YEAR">=2003 AND "END_YEAR"<=2004 AND "END_YEAR">=2003 AND
              "START_YEAR"<=2004 AND "PARTITION_YEAR"<=2004 AND "PARTITION_YEAR"="START_YEAR" AND
              "PARTITION_YEAR"="END_YEAR")
   6 - filter("START_YEAR">=2003 AND "END_YEAR"<=2004)

The problem is that if I replace int types with dates, this doesn't work any more. I have tried to extract the year component from dates and add corresponding constraints to the view, but partitions are not pruned. Changing the type of partition_year to date also didn't help.

Is there any way how I could have multiple date columns in a table and still be able to use partition pruning?

Oracle is unable to do partition pruning when a function is applied to the partitioned column. From the docs:

  There are several cases when the optimizer cannot perform pruning. One
  common reasons is when an operator is used on top of a partitioning
  column. This could be an explicit operator (for example, a function)
  or even an implicit operator introduced by Oracle as part of the
  necessary data type conversion for executing the statement.

Your view has to apply some form of function to start and end dates to figure out if they're the same year or not, so I believe you're out of luck with this approach.

Our solution to a similar problem was to create materialized views over the base table, specifying different partition keys on the materialized views.

We've tailored ours to match common base queries so that we get query rewrite benefits as well. You may need to get users to use the MVs directly to ensure you get the partition pruning working as you need, rather than relying on query rewrite.

(Updated to remove incorrect example and add info regarding applying functions to partition columns)


How to Fetch A Record If Shipping And billing Address are Different Show New Orders with Address If shipping and billing are different. I am stuck with this query. Can someone guide me a bit. Thanks

SELECT  t.TripId_PK, ls.name StartLocationName, le.name EndLocationName
FROM    trips t
JOIN    locations ls
ON      ls.LocationId_PK = t.StartLocationId_FK
JOIN    locations le
ON      le.LocationId_PK = t.EndLocationId_FK


MySql is not optimizing the query properly I have a table structure as follows :

CREATE TABLE `sale_product_inventories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `size` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tier_number` int(11) NOT NULL DEFAULT '1',
  `sale_product_pool_id` int(11) DEFAULT NULL,
  `inventory` int(11) NOT NULL,
  `in_cart_units` int(11) DEFAULT '0',
  `size_display_order` tinyint(4) NOT NULL DEFAULT '0',
  `last_updated_by` int(11) DEFAULT '0',
  `created_by` int(11) DEFAULT '0',
  `status` enum('active','inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE` (`sale_id`,`product_id`,`tier_number`,`size`,`sale_product_pool_id`)
) ENGINE=InnoDB AUTO_INCREMENT=92872 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

NOTE : I have an Index UNIQUE = sale_id,product_id,tier_number,size,sale_product_pool_id

When I run this query :

select * from sale_product_inventories
where
sale_id in (502,504)  and
(sale_id, product_id) in ((502,2),(502,1), (502,3),(502,4) ,(504,2) ,(504,3) )

MySql Uses the index Unique and the execution time is 0.7 millisecond

BUT

when I run this query

select * from sale_product_inventories
where
(sale_id, product_id) in ((502,2),(502,1), (502,3),(502,4) ,(504,2) ,(504,3) )

MySql does not use the UNIQUE index and the execution time is 76 millisecond.

Mysql : 5.5.27 InnoDB Version : 1.1.8

My Question is Why is mysql behaving in such a way. Can some one please help me with this.

EDIT :
I came across this so thought it might be useful to add
MySQL generally can’t use indexes on columns unless the columns are isolated in the query. “Isolating” the column means it should not be part of an expression or be inside a function in the query.

The MySQL optimizer cannot optimize expressions in this format:

WHERE (col_1,col_2) IN ((a,b),(c,d),(e,f))

It's not a matter of getting the indexes right -- it appears that it's just not implemented.

The optimizer does not understand that this is equivalent to...

WHERE (col_1,col_2) IN ((a,b))
   OR (col_1,col_2) IN ((c,d))
   OR (col_1,col_2) IN ((e,f))

... or ...

WHERE (col_1 = a AND col_2 = b)
   OR (col_1 = c AND col_2 = d)
   OR (col_1 = e AND col_2 = f)

There's Bug #35819, which I originally found in this article, which was in turn mentioned in the comments on this post.

Unfortunately, I didn't find those until I had already broken out the new Optimizer Trace in MySQL 5.6 and run a few test cases through it.  It seemed like a safe bet that if 5.6 couldn't handle it, then previous versions couldn't handle it.

It turns out that MySQL 5.6 indeed cannot handle it.  The "set in set of sets" construct seems to simply not be something the optimizer catches on to.  So in this case, it's not a matter of the optimizer choosing a full table scan over other plans -- the optimizer actually concludes that there aren't even any other possible plans to consider.

This is only true for multiple "row constructors" on the right hand side of IN.  For a single expression, the optimizer does its thing and realizes this is equivalent to col_1 = a AND col_2 = b:

WHERE (col_1,col_2) IN ((a,b))    # is optimized correctly
WHERE (col_1,col_2) IN (ROW(a,b)) # is an equivalent expression in MySQL

Interestingly, your original EXPLAIN suggests the unique index was not being used in exactly the way that you may have believed it was being used, at any rate.  It was only being used to find rows with the desired sale_id... not both values.

You'll notice in your original EXPLAIN that the key_len is shown as 4, meaning only the leftmost 4 bytes of the index are going to be examined -- sale_id, a 4-byte INT would be the leftmost 4 bytes in that index.  The Using where means the optimizer realizes that additional filtering of the rows returned from the range scan may be needed to eliminate any rows that don't satisfy the rest of the WHERE clause -- all rows with sale_id 502 and 504 are being retrieved via the index regardless of their value for product_id, and then the resulting rows will be subsequently filtered to satisfy the additional constraints imposed by the WHERE.

The optimum path is probably to stick with (expr and expr) or (expr and expr) or (expr and expr) in your where clause.  It's logically equivalent and the optimizer understands it.

Additional note, regarding some of your comments... in light of what I've discussed above, index hints will not help, because the optimizer appears unaware of the equivalence of the expression you've used to other expressions that it could handle... but as a point of reference, the reason it was syntactically invalid is that you have to use the name of the index, not the list of columns in the index.  You've called your unique index 'UNIQUE', so the way to use this as an index hint would be in this format:

USE INDEX(`UNIQUE`)


Impact on Recovery Time Using A Single Perpetual Base Backup and WAL Archives What would be the impact on recovery time when restoring a Postgres database from a backup where one base backup was taken at the start of the backup strategy and then only applying WAL archives from anywhere between a week, to a month, to even 3 or 6 months?
From the perspective of an Oracle RMAN incremental backup strategy, Oracle recommends weekly level 0 backups combined with daily level 1 incrementals, however, I am unable to find any such recommendations with regards to Postgres.
All Postgres Continuous Archiving and Point-in-Time Recovery articles mention only one base backup and then archiving WAL from that point onward, but how would this impact recovery time compared to having a weekly base backup and daily WALs strategy?
The reason why I’m asking this is because Redstor (the cloud backup solution we use) supports deduplication and backup archiving, so we’re trying to find the best backup strategy that will provide PITR where the total size of the backup on the backup server’s filesystem will always reflect the total size of the database in its current state. When it comes to restoring a backup, we can choose any 24 hour time period to restore from and replay from the backed-up WAL files to any point in time within 24 hours to bring the system to a current state.

I think the answer is "it depends".
In my experience, the thing it depends upon mostly is the amount of data you are restoring, bearing in mind that the amount of data is the base backup plus all of the WALs since that base backup.
This means that:

if you have a large database (base backup) with few UPDATEs/INSERTs, then there will be very little WAL data to restore. In this case you can have a large period between base backups, with little impact on the restore time.
if you have a small database which is heavily UPDATEd/INSERTed then a large amount of your restore time will be spent replaying WALs. In this case having a large period between base backups, will lead to much increased restore times.


PostgreSQL : inconsistent result when using WHERE clause on VIEW This is my VIEW declaration
CREATE OR REPLACE VIEW inv_groups_vw AS
 SELECT gg.id,
    gg.sku,
    gg.name,
    gg.description,
        CASE
            WHEN count(i.*) = 0 THEN '[]'::json
            ELSE json_agg(i.*)
        END AS items
   FROM ( SELECT g.id,
            g.sku,
            g.name,
            g.description,
            m.item_id
           FROM inv_items g
      LEFT JOIN inv_items_group_members m ON g.id = m.group_id
     WHERE g.is_group = true
     ORDER BY m.item_index) gg
   LEFT JOIN inv_items i ON gg.item_id = i.id AND i.is_group = false
  GROUP BY gg.id, gg.sku, gg.name, gg.description;

And peforming SELECT * FROM inv_groups_vw result
{
   "id": 3
   "sku": "PVC-1861-J",
   "name": "PVC 18 oz (jaune)",
   "description": "Rouleau de PVC 18 onces de couleur jaune",
   "items": [
      {
         "id":1,
         "sku":"PVC-186150-J",
         "name":"PVC 18 oz 61 in 50 vg (jaune)",
         "description":"...",
         "is_group":false
      }, {
         "id":2,
         "sku":"PVC-1861100-J",
         "name":"PVC 18 oz 61 in 100 vg (jaune)",
         "description":"...",
         "is_group":false
      }
   ]
}

However, if I execute SELECT * FROM inv_groups_vw WHERE sku = 'PVC-1861-J'; result
{
   "id": 3
   "sku": "PVC-1861-J",
   "name": "PVC 18 oz (jaune)",
   "description": "Rouleau de PVC 18 onces de couleur jaune",
   "items": [
      {
         "id":2,
         "sku":"PVC-1861100-J",
         "name":"PVC 18 oz 61 in 100 vg (jaune)",
         "description":"...",
         "is_group":false
      }, {
         "id":1,
         "sku":"PVC-186150-J",
         "name":"PVC 18 oz 61 in 50 vg (jaune)",
         "description":"...",
         "is_group":false
      }
   ]
}

Why is ORDER BY m.item_index not respected?
Update
This may seem to work, but is it really?
CREATE OR REPLACE VIEW inv_groups_vw AS
 SELECT gr.id,
    gr.sku,
    gr.name,
    gr.description,
    json_agg(gr.item) AS items
   FROM ( SELECT gg.id,
            gg.sku,
            gg.name,
            gg.description,
                CASE
                    WHEN i.id IS NULL THEN '{}'::json
                    ELSE row_to_json(i.*)
                END AS item
           FROM ( SELECT g.id,
                    g.sku,
                    g.name,
                    g.description,
                    m.item_id,
                    m.item_index
                   FROM inv_items g
              LEFT JOIN inv_items_group_members m ON g.id = m.group_id
             WHERE g.is_group
             ORDER BY m.item_index) gg
      LEFT JOIN inv_items i ON gg.item_id = i.id AND i.is_group = false
     ORDER BY gg.item_index) gr
  GROUP BY gr.id, gr.sku, gr.name, gr.description;

The effect of ORDER BY is only mandatory on the same query level.
Your view acts as derived table and if you just SELECT * FROM inv_groups_vw, the order is preserved.
However, when adding another WHERE clause to the outer SELECT, you would have to add ORDER BY to the outer query as well.
In your particular case, the order of rows is not guaranteed, even for the simple query:
SELECT * FROM inv_groups_vw

Since the only ORDER BY in the view is in a subquery itself. It's an implementation detail that the sort order happens to be preserved. That can change any time without notice.
More importantly, your objective seems to be to order the values in items, which is a related, but different issue.
Complete rewrite
After feedback from your comments I think this should be best to fulfill your requirement:

items (i.e. array_agg(i)) are REQUIRED to be ordered by item_index.

CREATE OR REPLACE VIEW inv_groups_vw AS
SELECT g.id,
       g.sku,
       g.name,
       g.description,
       CASE
          WHEN count(i.id) = 0 THEN '[]'::json
          ELSE json_agg(i ORDER BY m.item_index)
       END AS items
FROM   inv_items                    g
LEFT   JOIN inv_items_group_members m ON m.group_id = g.id
LEFT   JOIN inv_items               i ON i.id = m.item_id
                                     AND NOT i.is_group
WHERE  g.is_group
GROUP  BY g.id;    -- assuming g.id is the pk!


Is it possible to query for the job schedule description? Working on putting together a script to analyze all of the active jobs and I have nearly everything I need, but if possible I would like to be able to query for the schedules description as well. Is this possible? I am able to query for the name and ID out of msdb.dbo.sysschedules but it doesn't seem to have the schedules description. Where can I find this information?

You can use sp_help_jobschedule which returns the schedule_description by calling another system proc, sp_get_schedule_description.

exec sp_help_jobschedule @job_name = 'Your Job Name', @include_description = 1

It'd probably be faster to roll this in a cursor instead of breaking apart that system proc which


Multiple operations using WITH Is there a way to execute multiple operations using the WITH statement?

Something like

WITH T AS
(
  SELECT * FROM Tbl
)
BEGIN
  OPEN P_OUTCURSOR FOR
    SELECT * FROM T;

  SELECT COUNT(*) INTO P_OUTCOUNT FROM T;
END;

I want to select some data and the count of it...

You can only have one statement after the CTE. You can, however, define subsequent CTEs based on a previous one:

WITH t1 AS (
   SELECT a, b, c
   FROM table1
)
, t2 AS (
   SELECT b
   FROM t1
   WHERE a = 5
)
SELECT *
FROM t2;

Given that you are trying to count the rows and populate a ref cursor from the same result set, it may be more appropriate to do one of the following:

create a view
stage temporary results in a temp table

Finally, if the query is simple enough, just write it once for the count and again for the cursor. Simplicity and readability trump the DRY principle in this case.


Wrap identifiers automatically with brackets in SSMS text editor Working in SQL Server Management Studio, when using the text editor:

Is there any way to automatically wrap schema names, table names, column names and other identifiers with brackets so I don't have to put them manually, just in the same way it does it when it auto-generates queries? Is there (perhaps) any Intellisense feature I'm missing?

EDIT: see accepted answer

There is no native function for this yet in SSMS. You can view the IntelliSense properties document here.

APEX makes a free formatting tool and there is another tool called Poor Man's T-SQL. RedGate also makes a tool that is paid, all three work nicely. I am sure there are other tools out there too.


Duplicates while doing multiple joins I have 4 MySQL tables

1. companies - {id*, company}
2. employees - {id*, fname, lname, id_company*}
3. technologies - {id*, technology}
4. company_technologies - {id_technology*, id_company*}

Field is indexed if it has '*'.

Companies can use multiple technologies and have multiple employees.

What I'm trying to do is write a query that would select all employees and their companies that use ANY of given technologies. The problem is that I have duplicates when I try to do something like this:

SELECT employees.*, companies.* FROM companies LEFT JOIN employees on companies.id = employees.id_company LEFT JOIN company_technologies ON companies.id = company_technologies.id_company WHERE company_technologies.id_technology IN(1,2,3)

If, for example, a company is using technology 1 and 2 - it will appear twice along with all people.

If I add 'GROUP BY companies.id' condition at the end - I only have one employee for each company, whereas I need all of them.

Tables 1, 2 and 4 have several million records, all tables are InnoDB.

You can convert the join to a semi-join, using either IN or EXISTS:

SELECT employees.*, companies.*
FROM companies
  LEFT JOIN employees
    ON companies.id = employees.id_company
WHERE EXISTS
      ( SELECT 1
        FROM company_technologies
        WHERE companies.id = company_technologies.id_company
          AND company_technologies.id_technology IN (1,2,3)
      ) ;

Another way would be to use GROUP BY or DISTINCT in a subquery and then join:

SELECT e.*, c.*
FROM companies AS c
  LEFT JOIN employees AS e
    ON c.id = e.id_company
  JOIN
      ( SELECT id_company
        FROM company_technologies
        WHERE id_technology IN (1,2,3)
        GROUP BY id_company
      ) AS ct
    ON c.id = ct.id_company ;

Once you have one or more queries working correctly, you can start worrying about efficiency. I would add 2 composite indexes on the junction table, one on (id_technology,id_company) and one on (id_company,id_technology) (and remove the single indexes.)

Then test all queries with various parameters and table sizes and check which indexes are used and what are the execution plans.


Best Practice for Passing and Working With Parameters Security/Performance Our senior programmer has been having me write stored procedures in the following format to protect against injection attacks.  He says that the best practice is to take the parameter in, then declare a new variable in the body and assign that var the parameter, that this step is extra protection against injection attacks as it forces any injection attempts to be considered data and not taken literally.  Is that true?  I would think this would slow down the query and use extra memory and not add any extra protection, but I could be wrong.  Example below.

    CREATE PROCEDURE [dbo].[sp_foo_GET]
        @parAcct            NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @varAcct            NVARCHAR(20) = @parAcct;

    SELECT [userid]
    FROM [tblAccounts]
    WHERE [accountID] = @varAcct;
END

EDIT  After talking with the programmer he seems to think that passing parameters to one variable and assigning them to a declared variable helps with security AND it will cause queries to run faster.

He seems confused. Using the same value from a local variable should be no different to using it from an input parameter. Is your colleague getting the wrong end of the stick with regard to using parametrised queries (sometimes called prepared statements) instead of full ad-hoc SQL?

For example:

resultSet = dbconnector.getRS('EXEC sp_foo_GET \''+inputVariable+'\');

could be an injection route and moving the value between variables inside the procedure will not affect it at all as the injected code will not be seen by the procedure. If inputVariable were to have the value xxx'; WAITFOR DELAY '00:10:00'-- the SQL sent to the server would be

EXEC sp_foo_GET 'xxx'; WAITFOR DELAY '00:10:00'--'

Your procedure would run with he input parameter set to xxx and would not know of the extra code at all, it is run after the procedure call has completed.

With a prepared statement, assuming your DB access library properly supports them and isn't doing ad-hoc silliness under the hood, this would not happen:

resultSet = dbconnector.getRSWithParams('EXEC sp_foo_GET ?', [inputVariable]);

Your procedure would be called with the value xxx'; WAITFOR DELAY '00:10:00'-- rather than that extra code being seen as something to execute.

Of course the technique might be part of a larger pattern he wants you to follow the purpose of which will become clear as you do more advanced things yet which actually does nothing useful (other than getting you into the habit of following the pattern) at this stage, but I suspect he is simply misunderstanding and therefore adding unnecessary work. Could you ask him to explain in more detail? Perhaps he could provide you with an example call that he thinks the pattern will protect against?

After Comment Re. Performance

As per your comment, while I doubt there is any security reason for this pattern the new explanation that it could make a performance difference is more valid though you might find in most cases it makes no difference and in  some it reduces rather than increases performance so benchmarking rather than blindly using the pattern is recommended.

The issue comes from query plan caching and how input parameters & literals are handled differently to local variables. I'll not go into it into massive detail here as we are straying away from the original question (search in the MS documentation and the web generally around the phrases "parameter sniffing", "query plan cache", and "OPTIMIZE FOR, then ask a new question if you need more clarity after reading a few things) but as a starter-for-ten: in the example you give above what you are doing with the local variable is effectively the same as declaring the procedure with OPTION (OPTIMIZE FOR (@parAcct UNKNOWN)). The affect of this, as with any optimisation hint, can vary a lot depending on the queries being run in the procedure and the balance of the data touched by them.

The difference between using input parameters, literals and local variables with respect to cached plans can be significant when debugging performance issues (it can mean the performance profile you see when debugging/testing is quite different to that seen in production if you are not aware of the issue). http://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/ covers the issue from this particular context.


Convert date and time I have a column with about 100 records in datetime (for example 2004-08-09 12:20:41:000) and I would like to convert all records or update same time just date only with format mm-dd-yyyy.

Thank you.

For SQl Server 2008 you can use convert function like below.

--For output format mm-dd-yyyy
declare @d datetime = getdate()
select convert(varchar(10), @d, 110)

--For output format yyyy-mm-dd
declare @d datetime = getdate()
select convert(varchar(10), @d, 120)

If you are using SQL Server 2012 and later, the new format function works fine too. Example below.

--For output format mm-dd-yyyy
declare @d datetime = getdate()
select format(@d,'MM-dd-yyyy', 'en-US')

--For output format yyyy-mm-dd
declare @d datetime = getdate()
select format(@d,'yyyy-MM-dd', 'en-US')


Trying to phpMyAdmin import a database with a different name I've been searching for answers and couldn't find anything suited for my exact situation :

I have a magento website and i'm trying to "duplicate" the website into another one selling other types of products ,as i just have to change some design-features like logo, some colors, etc.

Problem is this, i export the database from my original website and it's called superdupergen_db .

When i try to import it on the 2'nd website which has topcompresor_db database name, i get name colliding error.
Can someone help me by explaining how to bypass this ?

Thanks !

Chris

There are two possibilities here; the easier one is to use phpMyAdmin to change the name on export.

To do that, you'll need to select the "Custom" radio button on the export page, then under the "Output" section look for "Rename exported databases/tables/columns" -- by selecting that checkbox, you get a dialog box to rename the entire database or specific tables or columns.

The other way is to edit the .sql file and find the references to the old database and correct it with your text editor. It's probably only the initial CREATE statement and USE very shortly after that.


How to Select all child elements of a given Node in SqlHierarchyId? I have a Node as /1/2/, Here I want to select all child Nodes of '/1/2/'
Ex /1/2/1/ , /1/2/2/1/.../

A worked example of what Mark's talking about, using IsDescendantOf:

DECLARE @t TABLE ( hId hierarchyId PRIMARY KEY )

INSERT INTO @t VALUES
    ( '/1/' ),
    ( '/1/2/' ),
    ( '/1/2/1/' ),
    ( '/1/2/2/1/' ),
    ( '/3/' ),
    ( '/3/4/' )

DECLARE @hId hierarchyId = '/1/2/'

SELECT *, hId.ToString() hId_string
FROM @t
WHERE hId.IsDescendantOf(@hId) = 1


Single-row INSERT...SELECT much slower than separate SELECT Given the following heap table with 400 rows numbered from 1 to 400:

DROP TABLE IF EXISTS dbo.N;
GO
SELECT
    SV.number
INTO dbo.N
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 400;

and the following settings:

SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The following SELECT statement completes in around 6 seconds (demo, plan):

DECLARE @n integer = 400;

SELECT
    c = COUNT_BIG(*)
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE
    N.number <= @n
    AND N2.number <= @n
    AND N3.number <= @n
OPTION
    (OPTIMIZE FOR (@n = 1));

Note:@The OPTIMIZE FOR clause is just for the sake of producing a sensibly-sized repro that captures the essential details of the real problem, including a cardinality misestimate that can arise for a variety of reasons.

When the single-row output is written to a table, it takes 19 seconds (demo, plan):

DECLARE @T table (c bigint NOT NULL);

DECLARE @n integer = 400;

INSERT @T
    (c)
SELECT
    c = COUNT_BIG(*)
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE
    N.number <= @n
    AND N2.number <= @n
    AND N3.number <= @n
OPTION
    (OPTIMIZE FOR (@n = 1));

The execution plans appear identical aside from the insert of one row.

All the extra time seems to be consumed by CPU usage.

Why is the INSERT statement so much slower?

SQL Server chooses to scan the heap tables on the inner side of the loops joins using row-level locks. A full scan would normally choose page-level locking, but a combination of the size of the table and the predicate means the storage engine chooses row locks, since that appears to be the cheapest strategy.

The cardinality misestimation deliberately introduced by the OPTIMIZE FOR means that the heaps are scanned many more times than the optimizer expects, and it does not introduce a spool as it normally would.

This combination of factors means that performance is very sensitive to the number of locks required at runtime.

The SELECT statement benefits from an optimization that allows row-level shared locks to be skipped (taking only intent-shared page-level locks) when there is no danger of reading uncommitted data, and there is no off-row data.

The INSERT...SELECT statement does not benefit from this optimization, so millions of RID locks are taken and released each second in the second case, along with the intent-shared page-level locks.

The enormous amount of locking activity accounts for the extra CPU and elapsed time.

The most natural workaround is to ensure the optimizer (and storage engine) get decent cardinality estimates so they can make good choices.

If that is not practical in the real use case, the INSERT and SELECT statements could be separated, with the result of the SELECT held in a variable. This will allow the SELECT statement to benefit from the lock-skipping optimization.

Changing the isolation level can also be made to work, either by not taking shared locks, or by ensuring that lock escalation takes places quickly.

As a final point of interest, the query can be made to run even faster than the optimized SELECT case by forcing the use of spools using undocumented trace flag 8691.


Log Shipping always fails, after a while I have a problem with Transaction Log Shipping on the SQL Server 2014 edition.
I have to log ship 4 databases, 2 of them are fine and the other 2 ALWAYS fail but not at first. When it fails, SQL reports that one of the log backups in the chain "could not be verified" thus breaking the chain.
These 2 databases in question will also end up in a "Restoring" state. All the articles that I have read about this issue report that I should put the database in emergency mode and run a DBCC CheckDB, but I can't since the database is in restoring state...

Additionally, I have verified that the broken log backup in question is valid by running a RESTORE with VERIFYONLY, and no errors are reported.

I have set up log shipping on these two databases up at least 7 times now, always to have it break in a few hours. What can cause this behavior?

Here are some of the logs. This first one is the last successful restore after setting log shipping up again:

  2016-11-23 15:41:53.66 Restored log backup file. Secondary DB: 'XXX', File: 'XXX.trn'

And then the very next message is

2016-11-23 15:41:55.29    *** Error: Could not apply log backup file 'XXX.trn' to
secondary database 'XXX'.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: An error occurred while processing the log for database
'text.staging.fsintel.com'.
If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

Both instances are on SP2 GDR.

An error occurred while processing the log for database

'text.staging.fsintel.com'. If possible, restore from backup. If a backup is not

available, it might be necessary to rebuild the log.

Somehow your logs are getting miss matched.

If you have any Log backups job please disable it.
Avoid taking log backups while enabled log shipping, or just manually run the logshipping backup job for log backups.


VBA Maximize Access Application Main Window I need my access application window to open up in full screen. I have tried using:

DoCmd.RunCommand acCmdAppMaximize

and

DoCmd.Maximize (which I think only maximizes a Form).

Neither is working for me? I put the VBA code in the OnLoad event and I also tried putting it in the Click Event of a command button.

I think i need to set focus to the actual Access Application (main window) first and then run the DoCmd.RunCommand acCmdAppMaximize command but am not sure how to achieve this.

I noticed that if I click the command button with DoCmd.RunCommand acCmdAppMaximize nothing happens but If first I move the window around (by dragging it's title bar) and then I click the button it will actually Maximize the Window.

Anyone had any luck with this before?

Update:

Not sure why but the only way i can get the

DoCmd.RunCommand acCmdAppMaximize

to work, is by first calling the

DoCmd.RunCommand acCmdAppMinimize (minimize window first)

and then calling the DoCmd.RunCommand acCmdAppMaximize (and then maximize)

I've spend a while battling with this problem too, reading various blogs and posts. I've found a very simple way to achieve the desired outcome in VBA using just one line of code. In the VBA editor, place this code AFTER your On Load code:

[Your On Load code]

KeyCode = 16

This code achieves the equivalent of pressing F11, ie maximising the view of the currently open window.


What to do in times of data inconsistency in Master-Master Replication in MySQL? I am building an architechture consisting MySQL Master-Master replication with HAProxy in its front. For HAProxy i am using round_robin algorithm to distribute loads between two masters. My question is when there is a heavy write situation as
 Seconds_Behind_Master will be greater than 0, what should I do for the data inconsistency will arise when HAProxy will go divert to an unupdated Master?

First point is that you should only write to one Master at a time, with the other set as the 'backup' option in HAPROXY.

This will prevent you from writing to an out of date master (unless there is a problem with the main one, in which case you probably don't have an option anyway).
By writing to both masters simulataneously you are opening yourself up to a whole load of problems. If you need to write to both you should probably look at Group Replication or Galera Cluster.

Second point, I assume you have taken precautions such as setting the auto_increment_increment and auto_increment_offset values on the two databases? So that in the event you do write to them both (during testing failover etc) you won't get duplicate entries on your auto_increment values.

If you want to test for Seconds behind (and you have Linux servers) you can use a simple script adapted from here: http://sysbible.org/2008/12/04/having-haproxy-check-mysql-status-through-a-xinetd-script/

We use it to check various values to determine if a slave database is up to date and available for use.


Why my mongodb Shell not getting started even after my mongodb server is running? I am not getting the right problem what might be making the mongodb shell not getting initiated.
Here is the gist of my log of MongoDB:  https://gist.github.com/JafferWilson/c6f964060bc27014d25e22066ed5f292
I do not know why it is showing the Index Build 2% or 30%.
Kindly, suggest me what might I do to initiate my shell and starte performing. I am running out of time due to this delay. PLease suugest me.

You have managed to kill your mongod middle of index build.

  found 2 interrupted index build(s) on datastalk.stalk

Build of first index ({ v: 2, key: { _id: 1 }) didn't take long. Second index ({ v: 2, unique: true, key: { EMAIL: 1.0 }, name: "EMAIL_1", ns: "datastalk.stalk" }) is taking longer.. And at point of 35%, system got access violation when reading memory address 0x0000005648530000

Do your machine have enough memory and memory DIMMs are good?

You can try to solve this problem with wiredTiger command line tools. Use dump to get your data from database to file, re-create your mongodb instance (remove all files from dbpath OR point your dbpath to other directory and restart mongod) and then import data back to database (re-creating needed indexes)


MySQL : if server_id is not explicitly set, then get error `ERROR 1200 (HY000) at line 1: The server is not configured as slave;` Our mysql version is "mysql  Ver 14.14 Distrib 5.1.35, for portbld-freebsd7.2 (amd64) using  5.2"

We have two database servers with replication, in a simple master/slave relationship. mysql2 is the master, mysql1 is the slave. A year ago, mysql was the master and mysql2 was the slave. We had to reverse their roles during a hardware failure.

On the master, we explicitly set the server_id (server-id)

[root@mysql2 ~]# grep server-id my.cnf
server-id       = 2
[root@mysql2 ~]# mysql -e "SHOW VARIABLES LIKE 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+

But, this setting was not set in my.cnf on the slave.

[root@mysql1 ~]# grep server-id my.cnf

Whoops. This shouldn't matter, because the server_id is set to 1 by default.

[root@mysql1 ~]# mysql -e "SHOW VARIABLES LIKE 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+

If I restart mysql, then the replication will work fine.

However, if I manually "STOP SLAVE" "START SLAVE", I get an error:

[root@mysql1 ~]# mysql -e "STOP SLAVE;"
[root@mysql1 ~]# mysql -e "START SLAVE;"
ERROR 1200 (HY000) at line 1: The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Why do I get this error when I don't explicitly set a value in my.cnf ? Why does replication work when I restart, but not when I "STOP SLAVE;" "START SLAVE;"?

It appears that mysql assigned a default value to the variable server_id.
According the default my.cnf, the server_id will default to 1

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
#server-id  = 1

The fix is to explicitly set server-id in my.cnf, as suggested by Baron Schwartz at http://www.xaprb.com/blog/2007/08/01/why-mysql-server-not-configured-as-slave/

  server-id=1

Two servers are not allowed to have the same server_id. Also, If you omit --server-id from a master, the default ID is 0, in which case a master refuses connections from all slaves, and a slave refuses to connect to a master.

For more information, see Section 15.1.1.2, “Setting the Replication Slave Configuration”.

Even if both servers have different server_id values, please make sure the new master has binary logging enabled.


Why do large images stored in database hurt performance I have read on the internet that it is better to keep large images on file systems since storing them in the database hurts performance. My question is why do large images hurt db performance?

There's more to life than performance, like data integrity. If you only care about performance you should be writing all your code in Assembly.

There is a higher risk of screwing things up by using the file system:

If you keep files in the file system, you now have two things to backup instead of one.

Other stuff: dealing with orphaned files. Dealing with file path security issues (path traversal). Preventing users from accidentally deleting files. You need to replicate your db security to the file system now.

If you're dealing with pictures of cats, go ahead and use the file system. If you're dealing with x-rays, use the database.

If you're so concerned about performance, use the filesystem as a caching layer. Ex: GET /files/12234 -> if not on disk then fetch from db and put on disk -> read from disk.

Use incremental backup for your database.


MySQL Cluster work well but ndb_desc doesn't work I have deployed and configured MySQL cluster. It works fine.

ndb_mgm> show

Connected to Management Server at: localhost:1186

Cluster Configuration

[ndbd(NDB)]2 node(s)

id=2@192.168.56.2  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *)

id=3@192.168.56.3  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0)

[ndb_mgmd(MGM)]1 node(s)

id=1@192.168.56.1  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]1 node(s)

id=4@192.168.56.4  (mysql-5.6.19 ndb-7.3.6)

I am reading the manual section about how to use MySQL cluster program http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-programs.html

On SQL Node - 192.168.56.4, I am running:

shell> ndb_desc -c 192.168.56.1

I receive message:

Unable to connect to management server.

NDBT_ProgramExit: 1 - Failed

I did ping and telnet successful to management node - 192.168.56.1:1186 from sql node - 192.168.56.4 There is no firewall between them.

What is cause of the problem ?

Thank you in advance.

The cause of problem is no free nodeid to allocate for ndb_desc. ndb_desc will attempt to connect to management node as api node thus it need free node. To make free node for ndb_desc, management node need to have empty [mysqld] in global config config.ini (in production, I think the use empty [mysqld] is no safe because any client is possible to connect to managment node to get free nodeid)


Calculate time difference between a datetime and the current date & time I would like to calculate the difference between a past datetime and the current datetime with the result in [hh]:mm format.

Some other approaches - I started this many hours ago and only just got back to it.

Let's first take a look at how to extract some important date part information from this data:

DECLARE @dt DATETIME = '2014-06-12T12:40:12', @d INT;
SELECT @d = DATEDIFF(MINUTE, @dt, GETDATE());

-- at the time I ran this:
SELECT
  @d, -- 111,027 minutes
  -- or
  @d / 60, -- 1,850 hours
  @d % 60, -- and 27 minutes
  -- or
  @d / 1440, -- 77 days
  @d % 1440, -- and 147 minutes
  -- or
  @d / 1440,      -- 77 Days
  @d % 1440 / 60, -- and 2 hours
  @d % 60;        -- and 27 minutes

Want to discard anything more than 24 hours? Ok, just take the modulo in days (1440 minutes), we just want the remainder (2 hours, 27 minutes).

SELECT LEFT(CONVERT(TIME,DATEADD(MINUTE,@d%1440,0)),5);

-- 02:27

Want all of the hours on the left? ok:

SELECT CONVERT(VARCHAR(11), @d/60) + ':'
  + RIGHT('0' + CONVERT(VARCHAR(11), @d%60), 2);

-- 1850:27

Need the hh: suppressed when it's < 1 hour? ok:

SELECT COALESCE(NULLIF(CONVERT(VARCHAR(11), @d/60),0) + ':','')
  + RIGHT('0' + CONVERT(VARCHAR(11), @d%60) , 2);

-- 1850:27 in this case, but just 27 if input was 27 minutes ago

-- want days broken out separately? ok:

SELECT CONVERT(VARCHAR(11), @d/1440) + ' days, '
  + RIGHT('0' + CONVERT(VARCHAR(11), (@d%1440/60)), 2)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(11), @d%60) , 2);

-- 77 days, 02:27


Are relay logs necessary for replication? I'm trying to understand when/where using relay log is beneficial. According the documentation, mysql captures and writes events from the master's binary log as the result of the I/O thread. Then, the SQL thread executes the statements from the slave relay log.

Currently, I have the relay logs disabled on the slave but it hasn't affected replication. It still performs well.

If replication requires the SQL thread to read statements from the relay log so they can be executed, why is it still working without a relay log?

Thanks

Replication does require relay logs. IF you have a replicating slave then it does use relay logs, and you have not disabled them. Please check the following:

SHOW SLAVE STATUS \G

Do you have Slave_IO_Running: Yes? Then your replication seems to be running. Please look for the Relay_Log_File column - what does it say? It should point out the current relay log file.

Look for this file. It should be by default on your @@datadir. Is it there? There's your replication relay logs.

You say you've disabled the relay logs -- I suggest you haven't (I don't even recall there's a way to disable the relay logs). How in your opinion have you disabled the relay logs?


How prepare and transfer database from mysql 5.7 to 8.0.19 since mysql_upgrade has been removed I noticed that the mysql_upgrade script is no longer available with mysql 8.0.16, and I'm running 8.0.19, so after creating a database backup from version 5.7 and imported to the version 8.0.19.

So, there's anything else I need to do to make sure to use the database in the new server running ubuntu 18.04?

Do a logical dump with mysqldump instead of physical backup with xtrabackup or similar tool.


wal_level set to replica at database level but i dont see that in configuration file wal_level is commented in postgresql.conf and i dont see any entry in auto.conf either. but at the databsae level i see its set to REPLICA. Is there any other place whee it could have been set?
postgres@postgresqlmaster:/etc/postgresql/12/data$ psql -p 5433
psql (12.3 (Ubuntu 12.3-1.pgdg18.04+1))
Type "help" for help.

postgres=# select name, setting, sourcefile, sourceline from pg_settings where name = 'wal_level';
   name    | setting | sourcefile | sourceline
-----------+---------+------------+------------
 wal_level | replica |            |
(1 row)

postgres@postgresqlmaster:/etc/postgresql/12/data$ cat /var/lib/postgresql/12/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
shared_buffers = '200MB'
synchronous_standby_names = 'pgstandby_synch1'

replica is the default value for wal_level.
“Default” means the value that is used if the configuration files fail to provide a setting for the parameter. It is a value that is hard coded into the postgres executable.
postgresql.conf contains most parameters “commented out” and with their default values. This may be confusing at first, but documents both the parameters and their default values.


Primary Key Index on a non-filtered column has a very high total lock wait time I'm seeing the

  Aggressive Indexes: Total lock wait time > 5 minutes (row + page) with
  short average waits

warning via BlitzIndex on one of our tables, and I cannot quite understand what could be happening. The index in question is related to a primary key on our table:

ALTER TABLE [Authentication].[Tokens]
ADD CONSTRAINT [PK_Authentication.Tokens]
PRIMARY KEY CLUSTERED ( [TokenID] )

And for completeness, here's the gist of the table structure:

CREATE TABLE [Authentication].[Tokens](
    [TokenID] [int] IDENTITY(1,1) NOT NULL,
... other columns here)

BlitzIndex reports the following stats: Reads: 10,066,849 (2,259,000 seek 934,476 scan 6,873,373 lookup) Writes:1,399,277, 314 rows; 1.1MB

I'm somewhat confused. We don't query/filter off of this column (like SELECT ... WHERE TokenID = 1). My only guess is maybe the table is being read quite often, and lots of queries are slamming against each other using the default SQL locking strategy?

I'm open to any advice or feedback here, for a table that seems simple and benign.

Notice that you're talking about a PRIMARY KEY CLUSTERED (which means it's the table itself) so you don't need a query like WHERE TokenID = 1 to hit that index. From your scenario most of the reads are from lookups (do you understand what is a lookup read method?) what means that most of the queries are something like SELECT TokenID, Column2, Column3 WHERE Column2 = 1 and you have an index for Column2 that doesn't include TokenID, Column2 and Column3.

Here's how I would interpret that output you got:

Reads: 10,066,849 (2,259,000 seek 934,476 scan 6,873,373 lookup)

2,259,000 seek: It's a considerable value, but a seek is usually the best method of accessing an index and there's little room for
improvement here.
934,476 scan: It's usually the worst access method, but this value compared to the others might not have the biggest impact to be
the first fixed.
6,873,373 lookup: By far the biggest number and deserves some attention as this could be caused by a few queries repeatedly hitting nonclustered indexes that could be improved to do seeks instead.

Writes: 1,399,277, 314 rows;

These can cause blocks on the reads or get blocked because of them,
so if I can make those reads finish their job faster (like tuning the
lookups to become seeks by improving either the indexes or the
queries) the time a read would have to wait for the write to
finish (or vice versa) would be reduced.

With that information I would search among the queries hitting the table [Authentication].[Tokens] what are doing the lookup read. Once they are improved I would run the sp_BlitzIndex again to measure the results of the tuning.

Further reading:

Brent Ozar explains the sp_BlitzIndex Aggressive Indexes and How to Fix sp_BlitzIndex Aggressive Indexes Warnings.

Pinal Dave has an article on SQL SERVER – Removing Key Lookup


How to get snapshot of changed data and store them on daily basis I have a requirement from my client. He wants to store a snapshot of what data has changed for some given tables, mainly financial transaction tables, on daily basis. This is for audit purposes. Data is to be stored table wise, so that we can develop some reports based on it to keep track of changes on daily basis. What are the possible techniques for this in SQL Server 2008 R2 Enterprise Edition? I am also interested in the latest available version as we are planning to migrate to latest SQL Server version.

Enable Change Data Capture on the tables of interest. Copy the rows thus flagged to another table that matches the definition of the first, but with an additional datetime column to show when it was written. Essentially a slowly changing dimension, type 2 in the Kimball nomenclature.

SQL Server 2016 introduced temporal tables. These combine the features of CDC and an audit table I mention above. Once defined, the system itself copies row versions to a history table as data changes. All history is retained until explicitly deleted. Each row has a time interval recorded showing when it was valid.


In Oracle what is the difference between STDEV and STDEV_SAMP functions? We have a query that should return NULL when doing standard deviations in groups that have only 1 row. The normal STDDEV function returns zero. However I found another function called STDDEV_SAMP which returns NULL when there's only one row. It seems to return the same result as normal STDDEV in all other respects, and the Oracle documentation (see: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions159.htm) says:

  STDDEV...differs from STDDEV_SAMP in that STDDEV returns zero when it
  has only 1 row of input data, whereas STDDEV_SAMP returns null.

But my question is, is this the ONLY difference?

In fact you have STDDEV, STDDEV_SAMP and STDDEV_POP.

Sample and population are standard definitions for STDDEV. When calculating a standard deviation one needs to know if the data set is a sample or the entire population.

There is plenty of explanation on the web about this. See for example Standard Deviation which states:

  The standard deviation is a measure of the spread of scores within a
  set of data. Usually, we are interested in the standard deviation of a
  population. However, as we are often presented with data from a sample
  only, we can estimate the population standard deviation from a sample
  standard deviation. These two standard deviations - sample and
  population standard deviations - are calculated differently. In
  statistics, we are usually presented with having to calculate sample
  standard deviations, and so this is what this article will focus on,
  although the formula for a population standard deviation will also be
  shown.

If you look at how a sample standard deviation is calculated versus a population standard deviation you can see 1 is subtracted from the population

Sample STDDEV:

Population STDDEV:

Since one is subtracted from the population when calculating a standard deviation you end up with a division by zero, and oracle gave you the option to choose whether you want NULL or zero in that case.

I think there are mostly historic reasons for the illogical naming, you see that in for example Excel's stddev functions too. The stddev is the sample one, and then later on a stddev.p and stddev.s was added. I guess Oracle took the opportunity to make both aliases behave a bit differently


how to resolve mssqlsystemresource.mdf corruption? Desperate for help, but fearing the worst here :(

Something crashed SQLSERVER 2005 this afternoon approx 3pm. Since then I have been trying to get running again.

Original log file messages told that master log file was corrupt. I had no recent backup so used the following command;

  start /wait \setup.exe /qn INSTANCENAME= REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=

Which seems to get me further down the road to getting SQL server started, but I now get the following message in the log;

  2014-08-0818:35:14.96 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
  ...
  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
  2014-08-0818:35:14.96 Server Registry startup parameters:
  2014-08-0818:35:14.96 Server -d R:\MSSQL.1\MSSQL\DATA\master.mdf
  2014-08-0818:35:14.96 Server -e R:\MSSQL.1\MSSQL\LOG\ERRORLOG
  2014-08-0818:35:14.96 Server -l R:\MSSQL.1\MSSQL\DATA\mastlog.ldf
  ...
  2014-08-0818:35:18.09 spid5s Starting up database 'mssqlsystemresource'.
  2014-08-0818:35:18.09 spid5s Error: 17207, Severity: 16, State: 1.
  2014-08-0818:35:18.09 spid5s FCB::Open: Operating system error 3(error not found) occurred while creating or opening file 'R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf'. Diagnose and correct the operating system error, and retry the operation.
  2014-08-0818:35:18.09 spid5s Error: 17204, Severity: 16, State: 1.
  2014-08-0818:35:18.09 spid5s FCB::Open failed: Could not open file R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf for file number 1. OS error: 3(error not found).
  2014-08-0818:35:18.10 spid5s Error: 5120, Severity: 16, State: 101.
  2014-08-0818:35:18.10 spid5s Unable to open the physical file "R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf". Operating system error 3: "3(error not found)".
  2014-08-0818:35:18.12 spid5s Error: 17207, Severity: 16, State: 1.
  2014-08-0818:35:18.12 spid5s FileMgr::StartLogFiles: Operating system error 2(error not found) occurred while creating or opening file 'R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.
  2014-08-0818:35:18.12 spid5s File activation failure. The physical file name "R:\aSQL\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf" may be incorrect.
  2014-08-0818:35:18.14 spid5s Error: 945, Severity: 14, State: 2.
  2014-08-0818:35:18.14 spid5s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
  2014-08-0818:35:18.14 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

There is no indication in event log what the problem is. I tried copying an older version of mssqlsystemresource.mdf & ldf to no avail.

Any help greatly appreciated as I cannot find anything more on the net.

Error message says mssqlsystemresource is not found.,you can try copying  resource database mdf and ldf to its place from another sql server instance.

Source version from which you will copy resource database mdf and ldf should be same as destination version

Below link may help you in knowing how to check resource database version and location of resource database
http://blog.sqlauthority.com/2010/01/31/sql-server-location-of-resource-database-in-sql-server-editions/

always its good to find out why all of a sudden this happened and add these files to as part of DR solution.


Implications of Changing to a Static Port I currently have an SQL Server hosting two production instances which have numerous web apps accessing them. As far as I know, none of these apps are using a specific port in their connection strings, nor am I aware if this even matters.

My questions is, what are the implications of assigning theses instances to static ports? Will this break my apps? Are there other adjustments I need to make or consider before doing this?

Reason for asking? My infrastructure team has requested that I assign static ports for the purpose of assigning SPNs for Constrained Delegation rights. Thanks. ;)

The SQL Server Browser service will help the client connect to each instance even with static port numbers.  As long as that service is running on your SQL server, then your web applications should not have any issue connecting.

You can read more about how the Browser service works here.


SQL Log Shipping Read Only/Standby Different users I have a primary OLTP server on Server A and Log Shipping in Standby/Readonly mode on Server B.

Log Shipping Server is refreshed every 4 hours, and utilized for BI Reporting Analysis, to reduce load from OLTP.
I want a different set of users on LogShippingDb, which Cannot access Primary OLTP.
Everytime, I try to add user on LogShipping Db, it says "Failed to Update database because the database is read-only."

How do I resolve this issue, and have a different set of users on Log Shipping?

Can I also try orphaned users on Primary server? example Winad BI UserGroup on OLTP, without a login, but user, and then have both Login and User on Destination server?

Orphaned users may not be best way either, may cause issue down the line, trying to refrain from view synonym db also, we have lot of tables 1000+

This can easily be achieved if you disable the login on the primary:

Add the login on the primary
Add the user to the database and grant permissions
Copy the login to the secondary
Disable the login on the primary


What does maintenanceMode -7 mean on a mongo primary? I've recently promoted a new member to primary of a mongo replicaset.  Things seem to be running smoothly for several days and am not seeing any application symptoms of any problems.  However peeking at rs.status() today I noticed it's listing maintenanceMode -7.  I didn't think this should be possible as it's the primary.  I'm having trouble finding anything useful in the docs on what this means.

What does this mean?

    {
        "_id" : 26,
        "name" : "mongohost5:27032",
        "health" : 1,
        "state" : 1,
        "stateStr" : "PRIMARY",
        "uptime" : 106966,
        "optime" : Timestamp(1379627877000, 77),
        "optimeDate" : ISODate("2013-09-19T21:57:57Z"),
        "maintenanceMode" : -7,
        "self" : true
    }

Maintenance mode is used when running certain commands, generally the compact command.  What you have here is the bug mentioned in SERVER-9338 whereby I am guessing you have run the compact command against your nodes several times.  There are some circumstances, especially when run on a primary when it will usually cause a change of state (i.e. it's not primary when the compact finishes), that cause maintenanceMode to be erroneously decremented.

As of writing this answer this is fixed in the development branch and has been marked for backport, though it has not (yet) been targeted for a release in the earlier versions.  Until the fix is released it should be safe to ignore the value, but I will do some testing to confirm and report back.


Audit user activity in SQL Server Please advice how can I audit all actions of specific user in SQL Server 2008 EE? If it can be traced and written to log file? If the audit has the levels?

With regards!

If i understand correctly, you want to see all activity from a single user.

The quickest would be a profiler trace filtered to that user, start a trace and in the Event Selection and Column Filters add the user under "Like" in the LoginName filter, or NTUserName if appropriate.

Beware of overhead when using profiler in production it can be resource hungry.

If you want something more long term, you can look into auditing. Here is a useful tutorial for Select Statements but there is much more you can do with it.

Auditing Select Statements 2008


Postgres log files not rotating I have a postgres system with a hot standby server. They use the same config file. I've setup the log rotation to use log_rotation_size and log_rotation_age so that the master server is rotated every 10MB and the standby logs are rotated every day.
My parameters are as follows:

postgres=# select name,setting,unit from pg_settings where name like '%log%';
            name             |            setting             | unit
-----------------------------+--------------------------------+------
 log_autovacuum_min_duration | -1                             | ms
 log_checkpoints             | on                             |
 log_connections             | on                             |
 log_destination             | stderr                         |
 log_directory               | /apps/postgres/pg_log          |
 log_disconnections          | on                             |
 log_duration                | off                            |
 log_error_verbosity         | default                        |
 log_executor_stats          | off                            |
 log_file_mode               | 0600                           |
 log_filename                | postgresql-%Y-%m-%d_%H%M%S.log |
 log_hostname                | on                             |
 log_line_prefix             | %u %h %p                       |
 log_lock_waits              | on                             |
 log_min_duration_statement  | 10000                          | ms
 log_min_error_statement     | error                          |
 log_min_messages            | warning                        |
 log_parser_stats            | off                            |
 log_planner_stats           | off                            |
 log_rotation_age            | 1440                           | min
 log_rotation_size           | 10240                          | kB
 log_statement               | ddl                            |
 log_statement_stats         | off                            |
 log_temp_files              | -1                             | kB
 log_timezone                | Australia/NSW                  |
 log_truncate_on_rotation    | off                            |
 logging_collector           | on                             |
 syslog_facility             | local0                         |
 syslog_ident                | postgres                       |

My problem is that the logfiles in the production database aren't being rotated at all, while the standby logs are being rotated as expected every day. This is a high availability application so I'm keeping it running at present by copying the logfile to a new file then echoing "" to the current logfile.
Is there anyway I can fix this problem without an outage to the system?

Try the following log parameters. After modify them, remember to reload the configuration file ($PGDATA/postgresql.conf).

# These are only used if logging_collector is on:
log_directory = '/var/applog/pg_log/1921'               # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
                                        # can include strftime() escapes
log_file_mode = 0600                    # creation mode for log files,
                                        # begin with 0 to use octal notation
log_truncate_on_rotation = on           # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
log_rotation_age = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
log_rotation_size = 10MB               # Automatic rotation of logfiles will


Parent/Child Relationship Table Design - What's The Best Practice? I have a single table for storing 'Tasks'. A task can be a parent and/or a child.
I use the 'ParentID' as the FK referencing the PK on the same table. It is NULLABLE, so if it is NULL it does not have a parent task.

Example is the screenshot below...

It has been argued in my team, that it would be much better (for normalization/best practices) to create a separate table to store the ParentIDs and so avoid having NULLS in the table and lead to better normalization design.

Would this be a better option? Or will it be more difficult with querying and cause performance issues?

We just want to get the design right from the beginning rather than finding issues later.

SQL-DDL code for the existing table:

CREATE TABLE [Tasks].[TaskDetail]
(
    [TaskDetailID] [int] IDENTITY(1,1) NOT NULL,
    [TaskName] [varchar](50) NOT NULL,
    [TaskDescription] [varchar](250) NULL,
    [IsActive] [bit] NOT NULL CONSTRAINT [DF_TaskDetail_IsActive] DEFAULT ((1)),
    [ParentID] [int] NULL,
    CONSTRAINT [PK_TaskDetail_TaskDetailID] PRIMARY KEY CLUSTERED ([TaskDetailID] ASC),
    CONSTRAINT [FK_TaskDetail_ParentID] FOREIGN KEY([ParentID]) REFERENCES [Tasks].[TaskDetail]([TaskDetailID])
);

The technique you are describing for representing task hierarchy is called 'Adjacency list'. Although it is the most intuitive to humans, it doesn't lend itself to very efficient querying in SQL. Other techniques include path enumeration (aka materialized paths) and nested sets.
To learn about some other techniques, read this post or search the web for numerous articles on these techniques.

SQL Server offers a native hierarchy representation for path enumeration. This is most likely your best bet...


How to use same stored procedure for different tables and avoid using dynamic sql I have quite a few tables which contain some common columns (same name and datatype). On these columns I need to perform the same operations and I would like to do that via a stored procedure. However the only way I can make it work at the moment is using dynamic sql, which I'd rather avoid.

Is there any way to use a stored procedure for different tables (where the table name is handed over as parameter) without using dynamic sql?

The other thing you could do is create a view that unions the like tables together, and include a defined column with the table name, then just do a select against that view with the tablename in the where clause.

CREATE VIEW tablesviews AS
SELECT columns, 'TABLE1' as tablename
FROM Table1
UNION ALL
SELECT columns, 'TABLE2' as tablename
FROM Table2
...

SELECT {some columns}
FROM  tableviews
WHERE Tablename=@table and [other where clauses]


single mysql query that show a particular parent and child value I am facing problem to show a particular parent child value and under all child value as a relationship. I have created single table tree and three column id, child and parent.

I created a tree relationship but I don't understand how to fetch a particular parent child and his child and his child value for tree. Can you help me?

By the way the same question has been already asked/answered and I would recommand you reading the article of the answer.

The article propose another solution which is less expensive to list parent/children : "The Nested Set Model" method which use these columns : id, left, right.
You will have to manage by yourself (or by your application) the left/right value.


MySQL: What field types for large numbers? I need to store some high numbers, some with decimal points (2 digits).

965.630,5       INT
47.320.500      INT
-62.188,0       INT
32,8%           FLOAT
225.165,7       DOUBLE
715.100,7  DOUBLE
6.929.850,0 DOUBLE

I am not sure what types are right. I don't get the difference between DOUBLE and FLOAT. And when do I use DECIMAL/NUMERIC? Hope someone can help. Please don't link to MySQL docs. I don't understand what they wrote :-(

First, if you are wanting to use decimal points, you can scratch INT off your list.

Next, you need to understand that FLOAT and DOUBLE are similar because they store the value in approximate value, but that DOUBLE is 8-bytes, and FLOAT is 4-bytes. So for larger numbers you would want DOUBLE instead of FLOAT.

Now, the choice is between DOUBLE and DECIMAL (DECIMAL can have 65 digits). MySQL tells us that for DOUBLE columns, the value will be rounded to fit into the column size. It also says that for DECIMAL, the behavior is left up to the operating system. It will either ROUND or truncate the digits that don't fit into the column.

My suggestion is to choose the column type from the above description that you think meets your needs (either DECIMAL or DOUBLE), and insert some test data to see if the storage behavior is as you expect.

Also, if you are doing any comparison on these columns, make sure the comparison is as you expect: 2.145<>2.140


DB select query performance when queried by partial key of primary key My primary key is composite - {a,b}. Will there be any performance gain if I query only {a} or DB is going to scan the whole table as there is no index only on {a}. How much storage overhead would be, if I add an index for {a}?

Assuming your DBMS is using BTree indexes, there is a chance that there may be a small speed-up in certain situations.

To perform a lookup on a BTree the system starts at the root node, follows links through the intermediate nodes to the leaf node.  The fewer columns there are in the index the more index keys will fit in a page and the fewer levels will be needed in the BTree. With fewer levels there will be fewer page reads for each lookup.

There are a lot of caveats to this. For example, in most realistic scenarios the fanout in a BTree is such that for medium-large table the index becomes 3 levels deep and stays that way for a very long time, irrespective of the keys in the index. Also it is likely most of your index pages are in memory already so the additional read time is trivial.

Against the slim possibility of increased read speed you must balance the absolute certainty of decreased write speed. Each row inserted must now be written to the the table, the {a} index and the {a,b} index.

Most optimisers will consider indexes where the leading keys are matched in the query. Say you define and index on {a,b,c,d}. That would be considered for queries that have predicates on a, a and b, a and b and c, or all four columns. (I say "consider" and not "used". Choosing which index to actually use is a whole different discussion.) So for a wide class of queries you get some potential benefit without the addition write overhead. Here's more detail on that.

How much storage will an index take?  Order-of-magnitude I'd say the size of the columns times the number of rows.  So if you have an integer (4bytes) and 1 million rows, the index will take about 4MB.  It might be 3MB, it might be 10MB, depending on a lot of things.  But it won't be 1KB and it won't be 1GB.

As a rule of thumb I would avoid keys with duplicate leading indexes.  If you identify a very specific use case, after careful testing, and having considered the load on the system as a whole, then you may think of adding some. Not as a matter of course, however.


Need help in optimizing a slow Postgres Query So, I need to calculate a rolling Monthly active users on everyday for last 100 days. In output, one column should represent the dates of last 100 days from today and against every single day, number of distinct values of users who occur in the activity table in past 30 days from that day should be calculated.

The query that i have written is returning the desired output. But is too slow. Infact doesn't work for 100 days.

Need Help with optimization !!

Actual Postgresql Code #

Select A.Daily,count(distinct da.user) as Users
from
    (
        select
            date(d) as Daily
        from generate_series(current_date - Interval '10' DAY, current_date - Interval '1' DAY, '1 day') d
        group by 1
        order by 1
    ) as A
join public.downloads_activity da
on date_trunc('day',da.created_at) between A.Daily - Interval '30' DAY and A.Daily - Interval '1' DAY
group by 1
order by 1

Expected and Returned Result

      Daily                  Users
January 12, 2020, 12:00 AM    35,654
January 13, 2020, 12:00 AM    35,465
January 14, 2020, 12:00 AM    35,777
January 15, 2020, 12:00 AM    35,980
January 16, 2020, 12:00 AM    35,953
January 17, 2020, 12:00 AM    35,899
January 18, 2020, 12:00 AM    35,829
January 19, 2020, 12:00 AM    35,627
January 20, 2020, 12:00 AM    35,348
January 21, 2020, 12:00 AM    35,564

Query Plan

GroupAggregate (cost=25.16..70992118.48 rows=200 width=26)
  Group Key: (date(d.d))
  -> Nested Loop (cost=25.16..69869352.15 rows=224552867 width=26)
       Join Filter: ((date_trunc('day'::text, ast_d.created_at) >= ((date(d.d)) - '30 days'::interval day)) AND (date_trunc('day'::text, ast_d.created_at) <= ((date(d.d)) - '1 day'::interval day)))
       -> Sort (cost=25.16..25.66 rows=200 width=8)
            Sort Key: (date(d.d))
            -> HashAggregate (cost=15.02..17.52 rows=200 width=8)
                 Group Key: date(d.d)
                 -> Function Scan on generate_series d (cost=0.02..12.52 rows=1000 width=8)
       -> Materialize (cost=0.00..571429.19 rows=10104879 width=30)
            -> Seq Scan on asset_downloads ast_d (cost=0.00..451827.79 rows=10104879 width=30)

Query Plan With Analyze Buffers

GroupAggregate (cost=25.16..71011891.94 rows=200 width=26) (actual time=24608.996..182045.961 rows=10 loops=1)
  Group Key: (date(d.d))
  Buffers: shared hit=325977 read=24911 dirtied=28, temp read=552011 written=118170
  -> Nested Loop (cost=25.16..69888811.94 rows=224615600 width=26) (actual time=2213.459..93364.594 rows=16514694 loops=1)
       Join Filter: ((date_trunc('day'::text, ast_d.created_at) >= ((date(d.d)) - '30 days'::interval day)) AND (date_trunc('day'::text, ast_d.created_at) <= ((date(d.d)) - '1 day'::interval day)))
       Rows Removed by Join Filter: 84818776
       Buffers: shared hit=325969 read=24911 dirtied=28, temp read=488070 written=54229
       -> Sort (cost=25.16..25.66 rows=200 width=8) (actual time=0.081..0.093 rows=10 loops=1)
            Sort Key: (date(d.d))
            Sort Method: quicksort Memory: 25kB
            Buffers: shared hit=3
              -> HashAggregate (cost=15.02..17.52 rows=200 width=8) (actual time=0.044..0.052 rows=10 loops=1)
                 Group Key: date(d.d)
                 -> Function Scan on generate_series d (cost=0.02..12.52 rows=1000 width=8) (actual time=0.028..0.033 rows=10 loops=1)
       -> Materialize (cost=0.00..571588.53 rows=10107702 width=30) (actual time=0.006..3572.397 rows=10133347 loops=10)
            Buffers: shared hit=325966 read=24911 dirtied=28, temp read=488070 written=54229
            -> Seq Scan on asset_downloads ast_d (cost=0.00..451954.02 rows=10107702 width=30) (actual time=0.016..3797.284 rows=10133347 loops=1)
                 Buffers: shared hit=325966 read=24911 dirtied=28
Planning time: 8.678 ms
Execution time: 182102.426 ms

Select A.Daily,
       count(distinct da.user) as Users
from
    (
        -- calculate all constants needed once
        select date(d) as Daily,
               date(d) - Interval '30' DAY as DailyStart,
               date(d) - Interval '1' DAY as DailyEnd
        from generate_series(current_date - Interval '10' DAY, current_date - Interval '1' DAY, '1 day') d
    -- GROUP BY by unique value makes no sense
    -- ORDER BY in subquery is ignored
    ) as A
                                 -- avoid fn(table.field)
join public.downloads_activity da on da.created_at >= A.DailyStart
                                 and da.created_at < A.DailyEnd
group by 1
order by 1


DROP command denied to user 'root'@'localhost' Okay. Apparently someone removed the DROP privilege from root (they have every other privilege granted), presumably to prevent accidental deletion of tables. Problem is now no users have the privilege. Upon trying to reassign the privilege, mysql responds with

mysql> GRANT ALL ON *.* TO 'root'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I tried creating a new user with the same privilege; same error.

Error creating account root@[hostname]: Access denied for user 'root'@localhost (using password: YES)

I double checked my who I'm authenticated as: root@localhost.

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>  select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

MYSQL Grants

SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion
FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

returns

+------------------+--------------+
| MySQLGrantsCount | MySQLVersion |
+------------------+--------------+
|               43 | 5.6.34       |
+------------------+--------------+
1 row in set (0.00 sec)

How then do I grant the privilege?

OK, then the problem is simpler than I expected.

It's time to hack the mysql.user table.

CREATE TABLE mysql.user_new LIKE mysql.user;
INSERT INTO mysql.user_new SELECT * FROM mysql.user;
UPDATE mysql.user_new SET drop_priv='Y',grant_priv='Y'
WHERE user='root' AND host='localhost';
UPDATE mysql.user A INNER JOIN mysql.user_new B USING (user,host)
SET A.drop_priv = B.drop_priv;
FLUSH PRIVILEGES;

Then, run this to verify:

SHOW GRANTS FOR 'root'@'localhost';

The reason you could not just run the GRANT command to fix this is the fact that you cannot grant a privilege that you currently do not own. Similarly, you can't use RENAME TABLE mysql.user TO mysql.user_old,mysql.user_new TO mysql.user; because RENAME requires the DROP privilege.


Import data from Oracle to SQL Server I have an Oracle database in a remote server. I want to import data from it to SQL Server on a local machine using a stored procedure or job shedule but I don't know how can I do it.

Here you have nice article how to link oracle to your SQL Server and query it.

For data import you can you SELECT INTO statement or make SSIS package for that.


\COPY not returning count psql I am trying to copy CSV data into tables. Also I want to print the count.
The command I'm using is:

echo "begin; delete from a; \copy a from a.csv CSV HEADER; end;" | psql -hlocalhost -dpostgres -Upostgres

Expected Output:

BEGIN
DELETE 2
COPY 2
COMMIT

Actual Output:

BEGIN
DELETE 2
COMMIT

Any ideas on what I'm missing here?

Why the COPY 2 is missing

As said in the doc about \copy:

  Unlike most other meta-commands, the entire remainder of the line is
  always taken to be the arguments of \copy

Clearly your intention is that COPY and END commands are sent consecutively, but in fact the SQL command that is sent to the server and successfully executed is the compound statement:

COPY a FROM STDIN CSV HEADER; end;

That does work, but when dealing with the results of compound statements, psql only handles the last one, as mentioned in Backslash-semicolon:

  psql prints only the last query result it receives for each request

The expected COPY 2 command tag is not displayed, because it's the first of two results of a compound statement: COPY and COMMIT.

Workarounds

The simplest one is probably to use -1 or --single-transaction as an argument to psql to make it enclose the sequence of commands inside a transaction:

echo "delete from a; \copy a from a.csv CSV HEADER" | psql -1 [other options]

With this form, the end of the line is clear so \copy can consume it. Plus that's why -1 is made for.

Another option if you had to put other commands after the \copy anyway: use the heredoc syntax:

$ psql [options] << ENDOFCOMMANDS
begin;
delete from a;
\copy a from a.csv CSV HEADER
-- other commands
end;
ENDOFCOMMANDS


MS Azure remote back up with SqlPackage I wanted to do an export of a .bacpac file from MS Azure database, from local machine, with command like this:

sqlpackage.exe /Action:Export /ssn:tcp:<ServerName>.database.windows.net,1433 /sdn:<DatabaseName> /su:<UserName> /sp:<Password> /tf:<TargetFile> /p:Storage=File

The DB was apparently found and the tool reported it started extracting the schema from the database, but then it fails:

Extracting schema
Extracting schema from database
Time elapsed 00:00:05.17
*** An unexpected failure occurred: .NET Core should not be using a file backed
model..

To my surprise, I couldn't find anything similar on the web. Is this some kind of an MS Azure bug?

You can remove /p:Storage=File. This worked for me.

/p:Storage=File : is used to redirect the backing storage for the schema model used during extraction, this helpful with large databases that may cause out-of-memory exception if the default memory location is used.

If this is not an issue for you, just omitting the parameter should fix your issue.


SQL Server Storage for multiple data files Recently, our company wants to add 4TB on the drive size for SQL Server. We have two drives now which have different size and data files. In order to make sure our SQL Server has the best performance, where should I add those space:

G: Drive: 19TB (16 Data files)
H: Drive: 3TB (3 Data files)

Should I add the space on H drive and create more data files in it?
Any suggestions?

Space will not be the only factor in where you place the new space. If you are talking about expanding the capacity of the existing drives you should look at what do the underlying volumes have backing them. I.e how many physical drives are assigned to the drive arrays and what the RAID levels are for those arrays.

For HDDs the more drives you have in an array the better it will perform. The amount of improvement will be a "Your mileage will vary" answer based on what your hardware is and how it is configured.

As an example of how to start at making that determination let assume the following:

Drive G has 20 1TB drives in a RAID 10 configuration.
Drive H has 22 146GB drives in a RAID 10.

Your new drives are another 28 146GB drives. You might want to add those to the H drive array as they are the same drive spec.

If your new drives are not the same to the drives already being used in one of the two arrays I would do some capacity planning to see how to break down the new drives into multiple arrays and spread some of the existing data files across things based on the access pattern of your data.

You can also check out some posts from Brent Ozar and team on disk io

For SSD disks there is a completely different way of looking at things. Check out the video from Brent