texts: Best practice - Log shipping / replication for archive / reporting database

texts:
We have a setup with a product that has a production database (many customer installations). The production database has been around many years and there are some performance issues that is getting worse. However the customers like to keep the data for generating different kind of reporting and statistics. Sometimes it also import to audit product logs.

So what we want to do is setup an archive / reporting database that the customer handles themselves and the production only holds "active" data and is maintained by a clean-up job that removes old dates.

There are many users +10000 that access the production database daily but very few that access the archive database.

What is the best way to achieve this, build in replication, log shipping or write a synchronisation mechanism?
Performance issues on the production database?

If we for instance decides for logshipping, is it possible to prevent that delete operations on the production database are propagated to the archive database?

Thanks,


texts: Store time series data efficiently without wasting space

texts:
I'm building an application to calculate different KPI metrics for customers of an ecommerce website e.g. (avg. order value, avg. items count and so on). KPIs are integer and or double values e.g. number of items bought, avg. order value, gross margin...
The application fetches orders data, calculate metrics and store them.
I'm using MySQL as a relational database.
about metrics:
I currently have 10 metrics to calculate for each customer.
Metrics can increase in the future but not so frequently so I can consider "10" as quite definitive. Anyway altering schema in the future is not a problem at all.
I need to calculate each metric on a weekly basis (at minimum).
Metrics are about customers.
about customers:
Customers are 30k and they are growing + 0.5k/month rate.
Not all customers buy with the same frequency. I can have occasional buyers but also heavy buyers.
I want to show a graph with the overall trend of a specific KPI in a given timespan.
I want to show a graph with the trend of a metric for a specific customer in a given timespan.
My entites are:

orders
customers
customers_kpi

I'm worried about storing a huge amount of useless data
52 weeks * 30k users * 4+ years = 6.2M rows at minimum
I have 2 questions:

Should I store rows for customers without orders for a given timespan (e.g. the row will be all filled with NULL)? Can avoid it somehow without affecting data visualization?

Which table structure is more efficient ("thin" table vs "fat" table) given that not all customers buy every week and number of metrics are unlikely to change often?

I'm in doubt among these 2 structures for customers_kpi table:

customer_id
kpi1
kpi2
kpi3
..kpiN
from
to

VS

customer_id
kpi_name
kpi_value
from
to


texts: How do you kick users out of a SQL Server 2008 database?

texts:
We need to do a restore, and cannot because other users are connected.
We thought we had disconnected every process, but apparently not.

How can we, from Management Studio, kick off everyone else so we can do this backup?


texts: What is the best approach to run a DELETE query on 92K of 4 million rows?

texts:
Options that have occurred to me:

One big delete statement. For example:

delete from x where (y = 'x' and x = 'y') or (y = 'a' and x = 'b')

Same as above, but chunk in smaller pieces, 10k at a time?
Create a stored procedure so all I'm sending are the the two query values.


texts: Running sys.dm_db_index_physical_stats in parallel

texts:
I am trying to get fragmentation information on a few tables which are over 500 GB and I am using the ...DETAILED option of sys.dm_db_index_physical_stats.  I am doing this on a restore copy of the production database on our pre-Prod server so I don't care about hurting any performance on the server.

I ran it and it looks like it is running in serial and it is taking forever. Is there a way to run dm_db_index_physical_stats() in parallel? Or is any other setting interfering with it?

I guess DBCC TRACEON (8649) and OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) are not available in SQL Server 2014.

Also tried DBCC SETCPUWEIGHT(1000); from Paul White's blog here. The blog mentions about parallelism inhibitor and one of which is system tables. Is dm_db_index_physical_stats() considered a system table?


texts:
When I assign a variable with

result := title || '', by '' || author;

it is taking more time (approx. 15 sec) to run the function.
However, when I assign the variable with

result = title || '', by '' || author;

it takes only 133ms.

Why is it taking more time for the first scenario? And what is the reason behind this?

Full function is given below.

CREATE OR REPLACE FUNCTION myschema.fn_get_res_no(reservation_no character varying)
  RETURNS character varying AS
$BODY$

DECLARE
    emd_status_firstcall varchar(2);
    emd_status_secondcall varchar(2);
    emd_status      varchar(6);
BEGIN
    SELECT firstwscomplete, secondwscomplete
    INTO emd_status_firstcall, emd_status_secondcall
    FROM myschema.mytable
    WHERE respkgconfirmid = reservation_no;

    emd_status = emd_status_firstcall || ', ' || emd_status_secondcall;
    RETURN emd_status ;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;


texts: How to Add Federated engine after installing Mysql

texts:
I have Mysql 5.5.18
and upon show engines:

show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+

as you see federated engine is not one of the list. i need to enable it!! any idea of why BLACKHOLE, FEDERATED, and ARCHIVE engines are missing from the list? and how can i add/enable them?
EDIT
I have ubuntu 11.10 and installed mysql from ppa ... i have added the following to /etc/apt/sources.list

deb http://ppa.launchpad.net/nathan-renniewaldock/ppa/ubuntu oneiric main
deb-src http://ppa.launchpad.net/nathan-renniewaldock/ppa/ubuntu oneiric main

Thanks for your help


texts: Is it possible for master, tempdb, model and msdb to have a database_id other than 1,2,3,4 respectively?

texts:
SQL Server System Databases, as far as I know, always have those same IDs and I have seen lots of maintenance scripts on the internet relying on the predicate WHERE database_id > 4 to exclude them from the actions of the script.

Also, if I run SELECT name, schema_id FROM sys.schemas; on a new user database, I get:

name                 schema_id
dbo                  1
guest                2
INFORMATION_SCHEMA   3
sys                  4
db_owner             16384
db_accessadmin       16385
db_securityadmin     16386
db_ddladmin          16387
db_backupoperator    16389
db_datareader        16390
db_datawriter        16391
db_denydatareader    16392
db_denydatawriter    16393

I ran that query on two different instances, one a SQL Server 2016 and the other a SQL Server 2005, and both returned that same result.

Questions:

Is there any situation (or sql server version) in which the system databases master, tempdb,
model and msdb would have a database_id other than 1,2,3,4
respectively?
Can I really trust on the fact that the schemas I listed will always have the same IDs on any instance of SQL Server so that I can write maintenance scripts based on those IDs?


texts: getting error incorrect syntax near @certificate

texts:
I have created a certificate and now I want to remove certificate signature and drop the certificate. When I supply the certificate name directly it is working but when I'm making use of local variables to supply the name its giving me syntax error.

I have tried with different datatypes as well

text, varbinary(max), char(200), varchar(200), nvarchar(200)

None of these datatypes are working.

declare @certificate nvarchar(200)
set @certificate = 'ITManagerCertificate'

begin transaction

use DBwork
-- remove signature from stored procedure
drop signature from Report_Manager
by certificate @certificate

-- drop certificate
--DROP CERTIFICATE N@certificate

if @@ERROR > 0
begin
   rollback transaction
end
else begin
   commit transaction
end


texts: What's the more efficient way of selecting with multi left joins?

texts:
I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities.

All tables have a id column as the primary key. student.id is varchar(64) (similar to a UUID in principle), the other 3 id columns are auto-increment integers. No index has been created yet. There are no foreign keys.

Now I want to query the list of students, along with their total activity number.

Here's what I tried, it works, but extremely slow.

SELECT
    `t1`.`id`,
    `t1`.`name`,
    COUNT(`t2`.`id`) AS `totalX`,
    COUNT(`t3`.`id`) AS `totalY`,
    COUNT(`t4`.`id`) AS `totalZ`,
FROM
    `student` AS `t1`
LEFT JOIN `student_x_record` AS `t2` ON (
    `t2`.`student_id` = `t1`.`id`
)
LEFT JOIN `student_y_record` AS `t3` ON (
    `t3`.`student_id` = `t1`.`id`
)
LEFT JOIN `student_z_record` AS `t4` ON (
    `t4`.`student_id` = `t1`.`id`
)
GROUP BY
    `t1`.`id`
ORDER BY
    `t1`.`id`

Currently, there are less than 100 students in the first table, and about 1K entries in the 3 records table.  The query runs in about 20 secs on a vps with 100% CPU consumption.

Is there any advice I can try to improve the performance?

Here's the execution plan:

+----+-------------+-------+------------+------+----------------------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys     | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+----------------------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | PRIMARY           | NULL | NULL    | NULL |   45 |   100.00 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL              | NULL | NULL    | NULL | 1166 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t3    | NULL       | ALL  | NULL              | NULL | NULL    | NULL |   32 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t4    | NULL       | ALL  | NULL              | NULL | NULL    | NULL |   41 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+----------------------------+------+---------+------+------+----------+----------------------------------------------------+

Seems horrible that all 4 queries were using ALL type. As a newbie, I think I am doing it the wrong way?


texts: Problem creating Oracle database manually

texts:
I am using Windows 7 and Oracle 11. When I want to create a database manually, it is giving me two problems:

I set my oracle_home to c:\app\best\product\11.2.0\dbhome_1. Under home I have a directory as database, in this directory I want to create the parameter file by edit command it is giving me an error: edit is not recognized as internal or external command. Can any body explain why?
Anyhow, I have created the initfinance.ora file by edit command in c:\ directory (here edit command is working) so I created it here and copy it to the database directory under oracle home. Then I set oracle home and set the sid as finance then I want to create the instance so I gave the command oradim -new -sid finance -startmode manual
here also it has given me the errors (cannot open the windows NT service control manager
access denied.. and it is not creating my instance please help me out in this situation and one more thing I am running this on my laptop machine only.


texts: SQL Server: can dynamic data masking be safe when providing a database backup?

texts:
I have a SQL Server 2016 SP1 with data that I am ready to share with a partner (developer), except for a few columns, example:

email varchar(64) MASKED WITH (FUNCTION = 'email()') NULL

I give the partner the credentials for logging in to the test copy of the database as a certain user, without unmask permission. I understand this is reasonably safe.
Now, we are considering sharing the database backup with our partners, so they can reload in their own server and environment.

I would probably need to have contained users, so that partner cannot create new users or connect them to existing logins (not sure about this).

CREATE USER user_name WITH PASSWORD = 'strong_password';

Is there a way that I can share a database backup, and still trust that the masked columns are safe, or do I have to physically delete the data?


texts: What is meant by "Stand-Alone Instance of SQL Server"

texts:
I am cloning a server to perform an in-place upgrade and I will have to change the name of that server - which has a SQL Server with just one instance (default instance) installed on it.

I am reading this article and it keeps referring to "stand alone" instance" of SQL Server. What is meant by stand alone here?


texts: How to inner join some tables and outer join others?

texts:
I'm certain I'm going to be embarrassed by the eventual answer, but I've been googling around and banging my head against the wall and can't figure it out.

I have four tables described below. I'm trying to get the query that shows all the products related to users (through the user_products table) AND all the categories.

Table definitions

CREATE TEMPORARY TABLE categories (
   id INT PRIMARY KEY NOT NULL
   , cat_name TEXT
 );

 CREATE TEMPORARY TABLE products (
   id INT PRIMARY KEY NOT NULL
   , category_id INT
   , prod_name TEXT
 );

 CREATE TEMPORARY TABLE users (
   id INT PRIMARY KEY NOT NULL
   , user_name TEXT
 );

 CREATE TEMPORARY TABLE user_products (
   id INT PRIMARY KEY NOT NULL
   , product_id INT
   , user_id INT
   , quantity INT
 );

INSERT statements

INSERT INTO categories VALUES (1, 'cat1')
                             ,(2, 'cat2')
                             ,(3, 'cat3')
                             ,(4, 'cat4');

 INSERT INTO products VALUES (1, 1, 'prod1')
                            ,(2, 1, 'prod2')
                            ,(3, 2, 'prod3')
                            ,(4, 3, 'prod4')
                            ,(5, 4, 'prod4');

 INSERT INTO users VALUES (1, 'user1')
                         ,(2, 'user2')
                         ,(3, 'user3');

 INSERT INTO user_products VALUES (1, 1, 1, 4)
                                 ,(2, 2, 1, 3)
                                 ,(3, 3, 1, 3)
                                 ,(4, 2, 2, 3)
                                 ,(5, 2, 2, 2)
                                 ,(6, 5, 2, 2);

I've tried several options, and this certainly feels like an outer join scenario. The most promising query THAT DOES NOT WORK is:

SELECT up.*, p.*, c.*, u.*
FROM user_products AS up
  JOIN users AS u ON u.id = up.user_id
  JOIN products AS p ON p.id = up.product_id
  RIGHT OUTER JOIN categories AS c ON c.id = p.category_id
WHERE u.id = 1 OR u.id IS NULL

Result from above query

 id | product_id | user_id | quantity | id | category_id | prod_name | id | cat_name | id | user_name
----+------------+---------+----------+----+-------------+-----------+----+----------+----+-----------
  1 |          1 |       1 |        4 |  1 |           1 | prod1     |  1 | cat1     |  1 | user1
  2 |          2 |       1 |        3 |  2 |           1 | prod2     |  1 | cat1     |  1 | user1
  3 |          3 |       1 |        3 |  3 |           2 | prod3     |  2 | cat2     |  1 | user1
    |            |         |          |    |             |           |  3 | cat3     |    |

The reason it does not work is that it fails to return 'cat4' from the categories table. Obviously this is because the WHERE clause is selecting it out of the result set. I'm at a loss to as to get a result set that includes all categories + the details of the products and quantities for which there are records in the specified user_products table.

The query should

return all categories
return data populated for user info iff the user has that product (a record exists in user_products)
not return rows with other user ids


texts: JOIN to obtain all the rows from the first table along with NULLs from the second table

texts:
I have two tables (tblReps and tblDailyWorkingTime). The tblReps table, i.e., the first one, returns 37 rows, but when I JOIN it with the second table I get 36 rows as the second table only has 36 rows that match the first table.

How do I go about getting the 37 rows back with a NULL result for the row that was not matched?

Here is the query that I have so far:

SELECT
    tblReps.[sName] AS 'RepName',
    tblReps.sNote AS 'Type',
    RIGHT(CAST(tblDaily.sDateAndTimeStart  AS smalldatetime), 8) AS 'DayStarted'

FROM
    [tblRepresentatives] AS tblReps

FULL OUTER JOIN
    tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode

WHERE
    tblDaily.sDate = CAST(GETDATE() AS DATE)
    AND tblReps.[sActive] = 'True'


texts: How to maintain trees and their roots in SQLite

texts:
I have a (SQLite) database with a table representing nodes in a forest (set of trees):

create table "Node" (
    "id" Text primary key,
    "parent" Text references "Node" ("id") -- parent node
        on update restrict on delete restrict,
    "root" Text references "Node" ("id") -- tree root node
        on update restrict on delete restrict
) without rowid;

It may happen that after inserting new nodes or updating existing ones, "root" attribute updates must be performed on the inserted nodes or nodes previously in the table, because the inserted nodes are connected to or existing trees.

Consider the following existing table:

"id" "parent" "root"
--------------------
'a'  null     'a'
'b'  'a'      'a'
'c'  'a'      'a'

Case 1: we want to insert/ignore the following nodes

"id" "parent" "root"
--------------------
'b'  null     'b'           -- ignore
'd'  'b'      'b'           -- insert

To end up with

"id" "parent" "root"
--------------------
'a'  null     'a'
'b'  'a'      'b'
'c'  'a'      'a'
'd'  'b'      'b'

Case 2: we want to insert/update (in the original table, without the case 1 changes)

"id" "parent" "root"
--------------------
'e'  null     'e'           -- insert
'a'  'e'      'e'           -- update

To end up with

"id" "parent" "root"
--------------------
'e'  null     'e'
'a'  'e'      'e'
'b'  'a'      'e'
'c'  'a'      'e'

Given these cases: what are insert/update statements (and perhaps triggers) that allow me to do obtain the correct resulting trees? One part is: when the node is already in the table, then I need to ‘merge’ it correctly with its version from the insert and then I need to propagate in some way the change of root.

One idea I had was to create a second table containing just a list of roots and then having "root" refer there, so that it could cascade on update. But then I don't know how to deal with updates that replace a root by an already existing one.


texts: Query databases that have MYISAM tables that do not have only fulltext index tables

texts:
I am trying to query from the information_schema where the database (table_schema) has MYISAM tables but does not have only fulltext index tables.

The statistics tables is very poorly designed for the query that I need. If you look, the table_schema and table_name appear multiple times.  Thus querying where table_name != 'fulltext' is not an option because there could be another row in statistics table for that table that is Btree which usually the case for PK indexes on tables. This results in that table that has a fulltext coming back using the above mentioned where clause in query.

So with that being said here is my Query so far:

SELECT DISTINCT
    (a.TABLE_SCHEMA)
FROM
    information_schema. TABLES a
INNER JOIN information_schema.statistics b ON a.TABLE_NAME = b.TABLE_NAME,
 (
    SELECT DISTINCT
        c.TABLE_SCHEMA,c.table_name
    FROM
        information_schema.statistics c
    WHERE
        c.index_type = 'FULLTEXT'

) AS fullTextTables
WHERE
    a. ENGINE = 'MyISAM'
AND a.table_schema NOT IN (
    'information_schema',
    'mysql',
    'performance_schema'
)
AND (a.Table_schema != fullTextTables.Table_schema AND b.table_name != fullTextTables.table_name);

The problem is that when I run this I am still getting back databases that only have 1 or 2 tables left in them that have not been converted from MYISAM; those tables still have Fulltext indexes. The Mysql version is 5.5 and not 5.6. Note: upgrading at this point is not an option.

Any help amending this query to get proper results would be appreciated.


texts: Can we resize a already capped collection in mongo db

texts:
I have a capped collection whose initial size i gave 3 GB.
Can I modify its size again i mean reducing or increasing both way as per need?
is there any command to do it.

db.HourwiseArticleCount.stats(1024*1024);
{
    "ns" : "DB.HourwiseArticleCount",
    "count" : 7055875,
    "size" : 2554,
    "avgObjSize" : 379,
    "storageSize" : 2662,
    "numExtents" : 2,
    "nindexes" : 2,
    "lastExtentSize" : 615,
    "paddingFactor" : 1,
    "systemFlags" : 1,
    "userFlags" : 0,
    "totalIndexSize" : 669,
    "indexSizes" : {
            "_id_" : 218
    },
    "capped" : true,
    "max" : NumberLong("9223372036854775807"),
    "ok" : 1
}
db.runCommand({"convertToCapped": "HourwiseArticleCount", size: 2362232012});
db.HourwiseArticleCount.stats(1024*1024);
{
    "ns" : "DB.HourwiseArticleCount",
    "count" : 5968662,
    "size" : 2161,
    "avgObjSize" : 379,
    "storageSize" : 2252,
    "numExtents" : 2,
    "nindexes" : 1,
    "lastExtentSize" : 205,
    "paddingFactor" : 1,
    "systemFlags" : 1,
    "userFlags" : 0,
    "totalIndexSize" : 184,
    "indexSizes" : {
            "_id_" : 184
    },
    "capped" : true,
    "max" : NumberLong("9223372036854775807"),
    "ok" : 1
}
db.runCommand({"convertToCapped": "HourwiseArticleCount", size:2147483648});
db.HourwiseArticleCount.stats(1024*1024);
{
    "ns" : "DB.HourwiseArticleCount",
    "count" : 5422132,
    "size" : 1965,
    "avgObjSize" : 380,
    "storageSize" : 2048,
    "numExtents" : 3,
    "nindexes" : 1,
    "lastExtentSize" : 0,
    "paddingFactor" : 1,
    "systemFlags" : 1,
    "userFlags" : 0,
    "totalIndexSize" : 167,
    "indexSizes" : {
            "_id_" : 167
    },
    "capped" : true,
    "max" : NumberLong("9223372036854775807"),
    "ok" : 1
}


texts: What DSN file is MS Access using for ODBC Linked Tables?

texts:
I set up a linked tables in Microsoft Access to connects to an remote database using ODBC with a File DSN. But which DSN file is Access using now? I want to examine it and compare it with the other versions.

The Linked Table Manager only lists the tables and the name of the database.

I tried renaming all of the DSN files I could find, and the ODBC connection still runs. Is Access storing the connection string itself?


texts:
We need to move our SQL Server 2016 standard License. Our plan is to install SQL Server on the new box with the same license key , test the application / databases and then delete the old SQL server after few days
Here is my question can the migration be done in parallel without uninstalling the old server  or we have to uninstall the old SQL server first then install the SQL server with the same key on another box.


texts: How to enable all the possible backup options of MySQL

texts: I normally use MySQL Administrator to backup my database. I want to enable all the backup options for the available backup types in MySQL. I noticed that Binary Logging is not running on my PC. How to enable it? I also want to know what all parameters to include in a command-line mysqldump command to take a perfect guaranteed backup.


texts: Additional Select statement within Cursor terminates looping unless Select statement returns a results for all cursor rows

texts:
I have a simple database called 'held_toys' (a held toy is a toy which is not on sale yet) which is comprised of a primary key and a foreign key for a Toy. I have a second table called 'toys_on_sale' which is also just comprised of a primary key and a Toy's foreign key.

I'm required to use a cursor to loop through all held_toys, inserting Toys which are not yet on sale into the toys_on_sale table, and deleting any Toys from held_toys which are already on sale.

While debugging my cursor, I noticed that the LOOP will only iterate once unless the Toys in held_toys are found (by Toy ID) in the toys_on_sale table. Thus the issue must come down to having the following SELECT statement within my LOOP:

DROP PROCEDURE IF EXISTS sp_Toys_On_Sale
$$
CREATE PROCEDURE `sp_Toys_On_Sale`(inToyIds text)
begin

DECLARE finished INTEGER DEFAULT 0;
DECLARE heldId BIGINT;
DECLARE heldToyId BIGINT;
DECLARE existingSaleId BIGINT;

DECLARE curHeldToys
    CURSOR FOR
        select ht.ID, ht.TOY_ID
        from held_toys as ht
        where find_in_set(ht.TOY_ID,inToyIds);

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN curHeldToys;

    getHeldToys: LOOP

        SET existingSaleId = 0;

        FETCH curHeldToys INTO heldId, heldToyId;
        IF finished = 1 THEN
            LEAVE getHeldToys;
        END IF;

        #LOOP only runs once if heldToyId is not found (even though there are 500 records in held_toy), I want the loop to continue regardless if the select below returns no results
        select
            tos.ID
        from toys_on_sale tos
        where tos.TOY_ID = heldToyId INTO existingSaleId;

        IF existingSaleId > 0 THEN
            DELETE FROM held_toys WHERE ID = heldId;

        ELSE
            INSERT INTO toys_on_sale (TOY_ID) VALUES (heldToyId);

        END IF;

        DELETE FROM held_toys WHERE ID = heldId;

    END LOOP getHeldToys;
    CLOSE curHeldToys;

end
$$

I know the issue is in:

#LOOP only runs once if heldToyId is not found (even though there are 500 records in held_toy), I want the loop to continue regardless if the select below returns no results
        select
            tos.ID
        from toys_on_sale tos
        where tos.TOY_ID = heldToyId INTO existingSaleId;

But I lack the knowledge of how to embed a SELECT such as this into a CURSOR which can be ignored if it returns zero results. Instead of causing the CURSOR to cease looping..

Any help is appreciated!


texts: Performing SQL counts with and without a WHERE clause from the same table

texts:
This is my first post so I apologize if I am not concise enough. I am trying to come up with an SQL query to identify data quality issues.
Here's the sample table:
DeviceOS    Bytes
Roku        10,000
AppleTV     -50000
SamsungTV   -100000
Roku        -100000
AppleTV      30000
Roku        -90000
AppleTV     -20000
AppleTV     -10000
SamsungTV   -100000

Output table:
DeviceOS   Total Count  bad_count
Roku       3            2
AppleTV    4            2
SamsungTV  1            1

Total_count field aggregates based on deviceOS and bad_count picks up only all the rows for which megabytes field is negative.
Essentially trying to do this->
select DeviceOS, count(*) from table group by DeviceOS
select DeviceOs, count(*) from table DeviceOS where megabytes < 0
How can I combine the above two queries and have the result of both of them displayed together similar to the output table?


texts: Query Performance Tuning on a huge table

texts:
I have the following table Transactions. The table looks like this:

CREATE TABLE [dbo].[Transactions](
[TransactionID] [bigint] IDENTITY(1,10) NOT NULL,
[PlayerID] [bigint] NOT NULL,
[BalanceTransactionTypeID] [int] NOT NULL,
[BalanceTransactionSubTypeID] [int] NULL,
[PointDelta] [money] NULL,
[TransactionDate] [datetime] NOT NULL,

The table contains the following constraints and indexes.

CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Transactions_PlayerID] ON [dbo].
[Transactions]
(
[PlayerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON   [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Transactions_TransactionDate] ON [dbo].
[Transactions]
(
[TransactionDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
 DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
 [PRIMARY]

I am writting a query where the goal is to return the top 50 point earners, and write the results using SSIS into a seperate table that will be looked at daily. The job is scheduled to run once a day. The main issue is there are over 3 billion rows and growing by about 300K per day. I know the ultimate solution is to partition it etc since its never used in game and its just historical. We have plans to roll it up as well. For right now however I need to make this query run in a reasonable time. I have tried using a CTE to get the player ids and the sum of the Delta then joining the result of that to another table by id to get the player details, I have tried using a straight up query with the join included all of which takes over 25 minutes to run the query. What can i do to tune this and make the query runable in a normal amount of time? If there is nothing let me know but I am really hoping there is some way to optomize this for the time being. As always thanks in advance for all help.


texts: SSIS: Scripts in package part not being executed when target server is 2016

texts:
I am having an issue with SSIS packages where scripts included in package parts ar not executed when the target server version is SQL Server 2016.

Create an SSIS project, set target server version to SQL Server 2016 (very important!)
Go ahead and add a simple script in the default Package. This should work fine.

In my case I have a script that sends an email using SendGrid service via SMTP. You can see the code here: https://pastebin.com/DW3hfBP3
However I tested this issue with other scripts Something like: MessageBox.Show("Great. Another bug. Thanks a lot guys..."); would do.

Create a package part.
Now just try re-creating the simple script from step 2 into the package part. Then add the package part onto the default Package or another package and execute the task. Even though the execution is successful, the script is not executed.

NOTE: If you try to copy the script from the default package into the package part, you should also notice a moronic error saying that the version is wrong. This is why I say to re-create the script in the package part.

I wonder if anyone could reproduce this issue and if there is a way around it? Worth noticing that this issue doesn't seem to happen when the target server is SQL 2017.


texts: How to Recover mysql Database From mysql data directory in Xampp

texts: I have reinstall Windows on my System.I forget to export databases.Now i have .frm and .ibd files in my xampp folder how can i recover my database From these Files?


texts: How to get the basetype of a domain in pg_type?

texts:
Basically,

if typbasetype = 0, the oid gives the base type.
Else
   recusively look at typbasetype joined with pg_type oid till we end up at the basetype.

How can this be done?


texts:
the institute I am working at wants to create a database for students. I have to find the best and easiest solution for this application and I need your opinions.

We would like to create a website/web application where students can enter their data into a form, without having access to the database.
Once the data has been entered, the students shouldn't be allowed to change the data entered.

The institutes employees should then be able to access the database and enter some more data, like thesis deadline.

It is fairly important to us to be able to export a student's data into a .pdf form
.

We would also like this data to be automatically deleted after a defined number of years(5 or 10 years), maybe after automatically sending a notification to the datamanager.

Because of hacking worries, the institute wants this website to be only accessed locally. What this means is that in order for a student to be able to enter his data, he will have to come and use the institute's computers.

We've been thinking about using MS Access but I guess a CMS would be the solution in this case. Now I only need your opinions on which CMS-platform to use for this kind of application. The main focus should be the ease of use, both for me creating it and the employees using it. I also need to be able to find documentation (books, tutorials, how-to's) for it.


texts: MySQL - Does using an explicit transaction affect query performance?

texts:
In MySQL 5.6, using InnoDB, I have a rather large table, call it A; (about 4 million rows) in which I need to update a field. The condition regarding what to update is held in another table, B; and the relation between the two is also held in another table, C; so I created my update statement with two joins, like so :

UPDATE A a JOIN (B b, C c) ON (conditions) SET a.myField = 'myValue'

Now I tested this statement on a dump of my target database, on a equivalent machine. Because I needed this dump for other purposes, I tested the thing using a transaction in order to rollback afterwards :

START TRANSACTION;
UPDATE [...]
ROLLBACK;

Which gave me pretty good performance (I think) for my update, about 17 seconds and 8 for the rollback.

My question is this: If I were to run this statement as is, not in a transaction, on my target database, would I obtain the same performance ? In other words, does using explicit transactions affect performance, and if yes, how ?

I tried to look the documentation on this but it is rather sparse on this topic.


texts: How do I recreate a deleted logfile on an Oracle database?

texts:
For my database assignment we were looking into "database corruption".

I was asked to delete the second redo log file, which I have done with the command: rm log02a.rdo this was in the $HOME/ORADATA/u03 directory.

Now I start up my database using startup pfile=$PFILE nomount.  Then I mount it using the command alter database mount;

When I try to open it with alter database open; it gives me the error:

ORA-03113: end-of-file on communication channel
Process ID: 22125
Session ID: 25 Serial number: 1

I am assuming this is because the second redo log file is missing. There is still log01a.rdo, but of course the file I deleted is missing.

How can I go about recovering the delete filed so that I can open my database again?

I have looked into the database CREATE scripts, and it specified the log02a.rdo file to be size 10M and part of group 2.

If I do select group#, member from v$logfile; I get:

  1
/oradata/student_db/user06/ORADATA/u03/log01a.rdo

  2
/oradata/student_db/user06/ORADATA/u03/log02a.rdo

  3
/oradata/student_db/user06/ORADATA/u03/log03a.rdo

  4
/oradata/student_db/user06/ORADATA/u03/log04a.rdo

So the missing file was part of group 2. If I try to add the log02a.rdo file again I get "already part of the database".

If I drop group 2 and then add it again with these commands:

ALTER DATABASE
  ADD LOGFILE GROUP 2 ('$HOME/ORADATA/u03/log02a.rdo')
      SIZE 10M;

Nothing. Supposedly alters the database, but it still won't start up.

Any ideas what I can do to re-create this and be able to open my database again?


texts: Query getting periodically stuck in 'copying to tmp table' state, never completes

texts:
I am running Wordpress on a dedicated server with a MySQL backend. I have a query that usually takes <1 second to execute, but periodically, this query will get stuck in a 'copying to tmp table' state and stay that way indefinitely until it is either killed or until mysqld is restarted. After restarting mysqld the problem goes away, the (identical) query once again takes <1 second to execute. This leads me to believe this is a configuration problem.

How do I go about solving this problem? The query itself is not too intensive, and my server is not experiencing any sudden traffic spikes. The tables themselves are all InnoDB format.

Here is my my.cnf: http://pastebin.com/9UMPxfAr

The query:

SELECT ctt.term_id AS catid, p.ID, p.post_title AS title, GROUP_CONCAT(tt.term_id) as terms_id
        FROM (SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' AND post_date > '2013-09-09 17:00:00' AND post_date < '2013-09-10 08:14:00') AS p
                JOIN wp_postmeta AS pm
                        ON (p.ID = pm.post_id AND pm.meta_key = 'wpo_sourcepermalink')
                JOIN wp_term_relationships AS ctr
                        ON (p.ID = ctr.object_id)
                JOIN wp_term_taxonomy AS ctt
                        ON (ctr.term_taxonomy_id = ctt.term_taxonomy_id AND ctt.taxonomy = 'category' AND ctt.term_id IN ('8','314','6'))
                JOIN wp_term_relationships AS tr
                        ON (p.ID = tr.object_id)
                JOIN wp_term_taxonomy AS tt
                        ON (tr.term_taxonomy_id = tt.term_taxonomy_id AND (tt.taxonomy = 'post_tag' OR tt.taxonomy = 'post_author'))
        GROUP BY tr.object_id
        ORDER BY pm.meta_value ASC;

An EXPLAIN of the query: http://pastebin.com/m5ndBfVX

And the output of "SHOW ENGINE INNODB STATUS" when a query is stuck in the 'copying to tmp table' state: http://pastebin.com/h0xv4Sfa

Indices of some of the tables in the query:

http://pastebin.com/XtTBL7Xr

http://pastebin.com/NamVSu4F


texts: How to use 2 auto increment columns in MySQL phpmyadmin

texts:
Is it possible to use 2 auto increment values ?

One starting from 0
Another starting from 4000400

Please help me


texts: How to setup Email alerts for Reporting jobs when it fails?

texts:
In SQL server 2008R2, I have a JOB which is running every month 1st week for Reports and I am frequently receiving Error message like "The job failed.  The Job was invoked by Schedule 14. The last step to run was step 1" and Failed step error details: "Message:  Unable to connect to SQL Server '(local)'.  The step failed" Finally I come to know Reports failing because of Database is not fully Restored at the time, when Report schedule starts. After I manually started the Process and Jobs run successfully.

I wan to know when Report jobs failure and when it happen I need to receive some Email alerts (2 types of Error E-mails needed -  one for Errors and another one for Informational etc..), Can I Setup E-mail alerts for Jobs?

SQL server Agent Notification and Setting up an Operator is good Idea? or there is any preferable ways to do that?


texts: when applying a sql server cumulative update or service pack on a virtual machine - what could be a safe way of stopping VMWaretools?

texts:
While applying cumulative updates to sql server 2016 SP1, there are a bunch of services and applications that need to be stopped for the update to go on.

Among those is the very core of the virtual machine, the VMware Tools core service.

Is it safe to stop all the instances of this application in order to go on with the updates?

What could be a safe way of doing it?


texts: Alter column fails due to "hidden" statistic

texts:
I'm trying to change a column data type from datetime2 to datetimeoffset. I have db owner permissions.

Whenever I run the query:

alter table dbo.<my-table>
alter column [TimeStamp] datetimeoffset

I get the following error message:

  The statistics 'Stat_a41cbd7f968a4cba86199c960f6f7c3a' is dependent on column 'TimeStamp'.

  ALTER TABLE ALTER COLUMN TimeStamp failed because one or more objects access this column.

However, it looks like this statistic 'Stat_a41cbd7f968a4cba86199c960f6f7c3a' is not present in my sys.stats table, which is very odd.

Is there any other location it might be defined?

Edit: There isn't a single entry in sys.stats named 'Stat_a41cbd7f968a4cba86199c960f6f7c3a'. In addition, I have never defined any custom statistics other than the automatic ones.


texts: Restore data from remote server using ".sql" file

texts:
I have a ".SQL" file which contains data at a remote server. I could download that file to my local machine and import data in my local DB, but because of network issues, and the size of ".sql" file (6.7GB) it's taking forever.

Is there any way I could execute the restore command on remote server and tunnel the output to my local machine DB ?

Thanks for help !


texts: Replicate SQL Server 2008 across domains

texts:
I have been tasked with migrating a database, size being up to 15Gb, from a SQL Server 2008 database in a 3rd party cloud VM, to SQL Azure. On go-live day, when we switch from the old host, the Azure, we have a 1 hr downtime allowance.

Unfortunately, the link speed is 1Mbps. So that would take around 30 hours to transfer. What I would like to do is create a Azure VM, with SQL Server 2008R2. Then, using either transaction replication, mirroring or log shipping, allow the database to replicate to my new VM from the source. I have no time limit for this, as the application migration is at my discretion. Could be a day, could be a few months.

My question is - how can I achieve this? I think transaction replication would be best, so that the database is basically up to date all the time. I need to demonstrate that it's possible, so I have created two VMs in Azure (ClientSourceVM and ClientStagingVM) in separate vnets (Trying to simulate across the internet replication).

The initial snap shot would take up to two days to transfer. Would this cause any adverse effect on the source database? Locking?

And is it possible to do replication across the internet? It seems I need to share folders and setup permissions, but not sure how that works with servers in separate domains across the internet. Is it possible?

With SQL Server 2008R2 - is there any better option that can serve my needs?

Note, once the database gets to 2008R2, I'll then hopefully migrate that to it's Azure SQL destination on go-live day. The replication is purely to allow an up to date database be available on the go-live day without waiting nearly 2 days for the backup file to copy.

Any advice and ideas would be greatly appreciated.

I could do on-prem SQL 2008 directly to Azure? And then on go live - simply stop the replication? I’d need to do more to the source database to make it a stand-alone? Or do I just remove replication - setup users and I’m good to go? I’d obviously do dry runs on a separate test environment - but the idea is do-able? We don’t have huge data spikes at all. It’s a database with only a few concurrent users. Less than 10.


texts: Attempt to add DQS Cleansing Connection Manager to SSIS results in error

texts:
I reopened an SSIS project in SSDT-BI (with VS2013 Shell) which had been dormant for ~3 months to see the dreaded red "x" had appeared on my DQS Data Flow Component:

I attempted to replace the existing connection manager with a new one, but both the existing and the new both resulted in the following error message:

  TITLE: DQS Cleansing Connection Manager

      Failed to connect to DQS server the connection manager 'local\mysampledb' as a result of the following value:
   A connection could not be made. Ensure that the server is running.
   System.Data.SqlClient.SqlException (0x80131904): An error occurred in the Microsoft .NET Framework while trying to load assembly id 65629. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
  System.IO.FileLoadException: Could not load file or assembly 'microsoft.ssdqs, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.

The package has been checked in to source control for the intervening period and was working correctly last time it was checked out.


texts: Mapping SQL Server integer to Oracle Datatype

texts:
Well I know, that an SQL Server integer is best represented by NUMBER(10).

But sometimes I have migrate some maintenance scripts to Oracle and I wonder if I must carefully
map integer to NUMBER(10) or if I can just use the ansi type integer in Oracle.

Is there any reason not to use integer?

EDIT:

I verified it against 10g.

create table BK_int_test(
i_col integer,
f_col float);

insert into BK_int_test values (1, 1);
insert into BK_int_test values (1.1, 1.1);

Select * from BK_int_test;

and got

     I_COL      F_COL
---------- ----------
         1          1
         1        1,1

2 rows selected.

Thanks that saves a lot of work.

The short form int of integer is supported as well.


texts: How to design tables for dynamic payroll items

texts:
I have to design a database for the payroll system. The requirements are as follows:

Admin can create payment items, such as Basic Salary, Overtime, Housing, Allowances at run time.
Each payment item will have its own calculation formula.
Some item's calculation formulas may depend on each other. For example, Housing is 20% of Basic.
Each employee will have different sets of payroll items.  For example an employee may have certain allowances that others don't have.
For each employee, some items may be processed monthly (e.g. salary), yearly (housing), anytime during the year(bonus).

The problem is that I cant see consistency between the items to create a suitable design. For example, Overtime takes monthly inputs (number of hours worked each month) to perform the calculations, while basic salary input is fixed.

Does anyone have any suggestions about how I could design this?


texts: How identify tables with millions of entries

texts:
On a debian server with apache and mysql, how can I find out if any one table gets spammed?

I have lots of different blogs, wordpress, wikis,... on my server of different customers.

It seems like some PHP aplications are not protected against spamming, so some tables get really big and slow down the whole server.

I would need a script, that monitors all tables. Or is there a simple tool I could install to get a report if something weird happens?


texts: SQL squash rows returned when joining several tables

texts:
I have a database which has a table for the main items, lets call it A with primary key 'id'.
Then there are a number of tables (B, C...), each corresponding to some attribute that each item can have 0 or more of. Each row in the attribute tables have a foreign key ('item_id'), specifing which main item it corresponds to, they also have a primary key 'id' for identification purposes.

I'm attempting to make a query which will get me all items with all attributes from a selection of the attribute tables. For instance if I want information about all attributes B and C, I tried something like

SELECT A.id, B.id, C.id
FROM A
LEFT JOIN B ON A.id=B.item_id
LEFT JOIN C ON A.id=C.item_id

However for an item which has 2 attributes in B and 3 in C, this returns 6 rows for that item:

----------------------------
| A.id  |  B.id  |  C.id   |
----------------------------
|    1  |     1  |     1   |
|    1  |     1  |     2   |
|    1  |     1  |     3   |
|    1  |     2  |     1   |
|    1  |     2  |     2   |
|    1  |     2  |     3   |
|    2  |  NULL  |     1   |
.
.
.

While in the best of worlds it would only require 3 rows for that item:

----------------------------
| A.id  |  B.id  |  C.id   |
----------------------------
|    1  |     1  |     1   |
|    1  |     2  |     2   |
|    1  |  NULL? |     3   |
|    2  |  NULL  |     1   |
.
.
.

Is there anyway to "squash" the rows in this way, so that the number of rows returned for each item is the maximum of number of attributes in the related tables for the item (or at least one row with NULL attributes if it doesn't have any)?
The number of returned rows for each item can grow exponentially with the number of attributes requested...

Or is the best way to gather the attribute information to make one query for each attribute table, with only one JOIN in each query?


texts: Possible RMAN orphan files in backup directory

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

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

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


texts: SQL Server IDENTITY column automatic re-seed

texts:
I work with huge datasets. Many of the transactions that take place in my database are enormous - trillions of rows, and more.

Some of the tables use IDENTITY columns, not for unique IDs, just because it's simple and fast, and to provide a concurrent solution to providing an incrementing number. However, when the IDENTITY column reaches its limit, I want it to automatically reseed immediately within the statement when it reaches its limit.

I appreciate this is odd behaviour for most, but it would make sense to at least have this functionality as an option, surely? You can't even do a reseed within a transaction, and I cannot use truncate (don't want to delete).

Why is this not possible? Has anyone else come across this as a problem before?

Here's the functionality: In SQL Server I have a table that acts as a sequential number generator, like a Sequence in Oracle. The maximum we want the number to be is 999999, after that, reset to 0. This number is added on to some other fields (one of them a datestamp) to generate reference numbers.

The system is highly concurrent and I need it to be fairly obvious when the reference number was generated. As it stands, there is a task that runs every day to reseed the IDENTITY column, but due to the large number of records daily, if there are > 999999 records processed, I get an error.


texts:
I'm guessing the answer is no, but I was wondering if there was anyway to tell innodb to not store fetched pages in the buffer pool?

The reason for looking at doing this is check summing tables.  I'd like to minimize the effects of trashing the useful cached data.


texts:
I am bit confused with BCNF and would appreciate any clarification.

I have a table

The primary key is a composite of all its attributes.
Is this table in BCNF?
My assumption it is not, because BCNF requires any determinant to be a candidate key. However, in this example there is dependency Subjects->Department, i.e. knowing subject we can say which department it is taught at. But, Subjects field is not a candidate key so it violates BCNF. It is assumed that one subject can be taught by only one department.
I might be misunderstanding something, so please anyone can clarify.

Thank you


texts: SQL Server 2012 Not Using Parallel Cores for Parallel Connections - Scheduler Problem?

texts:
I have a web system using SQL Server 2012 Enterprise Edition as a backend.

One of our queries is particularly heavy taking around 60 seconds. I have analysed this query heavily and know that the time is spent in a INSERT INTO tablevariable SELECT FROM table. This means that there should be a shared lock on the table but parallel processes should be able to run this query in parallel.

One web endpoint calls 3 of this procedures in parallel on separate processes. Since we have 4 cores I would expect that SQL server should run one process on a core so 3 run simultaneously giving us query times of approximately 60 seconds.

However most of the time it will switch between running 2 of the processes and then 1 of the processes around every 20-30 seconds. I can see the CPU jump between 25% and 50% usage (since a 4 core machine).

The processes that aren't running are in the RUNNABLE state so it seems they are just waiting on CPU time, no table locks causing the issue.

Occaisionally it will actually only run 1 at a time, and occasionally all 3 simultaneously so something is causing some decision making.

I have checked that the licensing sees all 4 cores (it does) and the processor affinity is set to use all. Since this isn't within a query I don't expect MAX_DOP to impact this but I have tried it at 0 and 2.

Any idea what influences SQL servers decision making here? and if we can change this behaviour?


texts: SQL Server RAM usage is increasing and query executions is slowing down with time

texts:
I'm using SQL server on a server with 48 GB of RAM.

When I start the SQL Server service, it's using 12% of the RAM at first, but after 24 hours it's reaching 90% and query executions begin slowing down. We have to restart the service so we can serve users.

What could be the problem and how we can fix it?

Example images:

and
(select value_in_use from sys.configurations where name = 'max degree of parallelism')

and my server cores are 47.


texts: How do I safely generate scripts for an existing MS SQL instance?

texts:
I've inherited a web application that talks to an MS SQL Server instance. It uses multiple databases and database users.

I'm setting up a new staging environment for this application. It must match the existing database schema, but use "dummy" data. I want the process to be repeatable, so I'm scripting it. The scripts will be kept in version control alongside the app, and therefore cannot contain things like hardcoded passwords.

So far I have something like this:

In the existing environment, use the "generate scripts" wizard to script all database objects in all databases except table data. Concatenate the results and start with that.
Script the creation of any necessary service logins, their permissions, and their passwords. Since I can't include passwords in version control, presumably I'll need to use a storedproc that asks for them.
Create dummy table data in .tsv files, and import them with a BULK INSERT loop or a batch file that calls bcp.exe

I haven't done anything like this before and want to be sure I'm not missing something important.

Am I doing this in the right order? I suspect logins may need to be created before schema initialization. But before schema initialization, any stored procs are unavailable.
Is there anything I need to leave out of the "generate scripts" step, to avoid dumping passwords or instance-specific settings that should not be cloned in this way?
Is there anything I need to include that I'm missing, for the new SQL instance to have everything it needs? (I am thinking of things like the system databases; I don't think I should carry those over, but I'm not sure, and I'm worried about the users-vs-logins distinction).


texts: Retaining earliest instances of duplicate entries from a table

texts:
We have a situation where duplicate entries have crept into our table with more than 60 million entries (duplicate here implies that all fields, except the AUTO_INCREMENT index field have the same value).  We suspect that there are about 2 million duplicate entries in the table.  We would like to delete these duplicate entries such that the earliest instances of the duplicate entries are retained.
Let me explain with an illustrative table:
CREATE TABLE people
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
phrase VARCHAR(40) NOT NULL DEFAULT '',
PRIMARY KEY (id)
);

INSERT INTO people(name, age, phrase) VALUES ('John Doe', 25, 'qwert'), ('William Smith', 19, 'yuiop'),
('Peter Jones', 19, 'yuiop'), ('Ronnie Arbuckle', 32, 'asdfg'), ('Ronnie Arbuckle', 32, 'asdfg'),
('Mary Evans', 18, 'hjklp'), ('Mary Evans', 18, 'hjklpd'), ('John Doe', 25, 'qwert');

SELECT * FROM people;
+----+-----------------+-----+--------+
| id | name            | age | phrase |
+----+-----------------+-----+--------+
|  1 | John Doe        |  25 | qwert  |
|  2 | William Smith   |  19 | yuiop  |
|  3 | Peter Jones     |  19 | yuiop  |
|  4 | Ronnie Arbuckle |  32 | asdfg  |
|  5 | Ronnie Arbuckle |  32 | asdfg  |
|  6 | Mary Evans      |  18 | hjklp  |
|  7 | Mary Evans      |  18 | hjklpd |
|  8 | John Doe        |  25 | qwert  |
+----+-----------------+-----+--------+

We would like to remove duplicate entries so that we get the following output:
SELECT * FROM people;
+----+-----------------+-----+--------+
| id | name            | age | phrase |
+----+-----------------+-----+--------+
|  1 | John Doe        |  25 | qwert  |
|  2 | William Smith   |  19 | yuiop  |
|  3 | Peter Jones     |  19 | yuiop  |
|  4 | Ronnie Arbuckle |  32 | asdfg  |
|  6 | Mary Evans      |  18 | hjklp  |
|  7 | Mary Evans      |  18 | hjklpd |
+----+-----------------+-----+--------+

On smaller sized tables the following approach would work:
CREATE TABLE people_uniq LIKE people;

INSERT INTO people_uniq SELECT * FROM people GROUP BY name, age, phrase;

DROP TABLE people;

RENAME TABLE people_uniq TO people;

SELECT * FROM people;
+----+-----------------+-----+--------+
| id | name            | age | phrase |
+----+-----------------+-----+--------+
|  1 | John Doe        |  25 | qwert  |
|  2 | William Smith   |  19 | yuiop  |
|  3 | Peter Jones     |  19 | yuiop  |
|  4 | Ronnie Arbuckle |  32 | asdfg  |
|  6 | Mary Evans      |  18 | hjklp  |
|  7 | Mary Evans      |  18 | hjklpd |
+----+-----------------+-----+--------+

Kindly suggest a solution that would scale to a table with tens of millions of entries and many more columns.  We are using MySQL version 5.6.49.


texts:
I'm using MySQL and InnoDB,
I have a table reserver with 2 columns : idReservation and idChambre
I have a table reservation and a table chambre

Now I want my query to return every "chambre.id" who is not between my dateDebut and dateFin.

Below is my actual query :

SELECT cre.id
FROM   reserver AS rer
       INNER JOIN reservation AS ron
         ON rer.idReservation = ron.id
       RIGHT JOIN chambre AS cre
         ON rer.idChambre = cre.id
WHERE (ron.dateFin < ?
    OR ron.dateDebut > ?
    OR (ron.dateFin = NULL AND ron.dateDebut = NULL))

My problem is that my return value only contains chambre when the id of the chambre is already in reserver.

My second problem is that "RIGHT JOIN" add every chambre to my query, but what I want is only chambre as : "ron.dateFin < ? OR ron.dateDebut > ?" or chambre who are not already on linked to reserver.

EDIT : here's some sample rows :

reserver :

id |idReservation | idChambre
---+--------------+----------
0  | 1            | 1

chambre :

id | idCategorie
---+------------
1  | 1
2  | 2

reservation :

id | date       | dateDebut  | dateFin
---+------------+------------+---------
1  | 03/04/2015 | 01/04/2015 | 06/04/2015
2  | 05/04/2015 | 08/04/2015 | 12/04/2015

I want my query to return id 1 and 2 for chambre but all I get actually is 1

P.S : Sorry, english is not my native language but stackexchange is such an amazing website.


texts: MySQL Return Column Types from Select Statement

texts:
Is is possible to get the column types and names from a select statement in MySQL?

E.g.,

SELECT t.first_col, t.third_col
FROM my_table t
WHERE t.id = 1

And then I would send that to MySQL and have it tell me.

column_name  |  type     | length
first_col    |  varchar  | 5
third_col    |  int      | null

I know I can do it in SQL Server I was hoping I can do it in MySQL too.


texts: ETL import more dimensions from only 1 column

texts:
I was requested at work to learn ETL.  I have already created a database without using ETL with some dimensions and consolidations, etc, containing above 1 billion numerical values.  Now I have to create and fill a similar database (at least 100 million values) using ETL through Jedox Palo Suite.  I read some manuals and looked on a sample project and I understand the principles but it looks like ETL is made only for well formatted data files that contain names of the elements on 1st row and their values on the following rows (or something similar).

My source data files consist of only a single column.  The structure is as follows (values are numerical, only 1 on line except 1st line):

1152 576       //sizes of following structure (longitude and latitude, x and y)
1st_value      //1st value, I need to store it with coordinate like [0,0,0]
2nd_value
..
1152th_value   //last value of the first y from 576, coordinate [1151,0,0]
1153th_value   //first value of the second y, coordinate [0,1,0]
..
663552th_value //last value of the last y, coordinate [1151,575,0]
1152 576       //again sizes (always 1152 576)
1st_value      //first value of next element in the 3rd dimension [1151,575,1]
..
..
last_value     //[1151,575,63]

I need to read the data and place it in the appropriate cell of my cube.  How can I do this in ETL?  I think the TableTransform can be used with its mode Normalization/Denormalization which can change rows to columns or vice-versa but it looks like this Transform is for different uses.  Another problem is how to count the rows.  RowNumber and getRowCount count the values and I can use some variables to store the number of cycles but I don't know how to increase them and also how to then increase the element of dimension.

There are 2 script languages possible to use: Groovy and JavaScript.

How can I best read the data from 1 column to different dimensions?


texts: Oracle Linked Server Long Preemptive_COM_Cocreateinstance Waits

texts:
I have a Linked Server using the OraOLEDB.Oracle provider from the 11g Oracle Client and it was working fine for a while. However, an Oracle 12c Heterogeneous Connection was setup recently. This caused the 11g Client to not be able to connect to the Oracle Instances because of the PATH variable using the 12c TNSnames instead of the 11g TNSnames.

The PATH variable was corrected and the 11g Client can now connect to the Oracle Instances using TNSPing or SQLPlus but when trying to use the linked server the SQL Server will go into PREEMPTIVE mode with the wait type PREEMPTIVE_COM_COCREATEINSTANCE. The linked server query will not make a connection to Oracle or error out and cause the SQL Server to not allow any new connections. They will get stuck trying to login with the wait PREEMPTIVE_OS_GETPROCADDRESS and wait for the Linked Server query to finish in order to login.

Any thoughts on why the Linked Server would be causing long PREEMPTIVE_COM_COCREATEINSTANCE waits?


texts:
Programmer here, be gentle.

I'm working in a MySQL database with only a single table, as shown by show tables:

mysql> show tables;
+----------------------+
| Tables_in_widget_web |
+----------------------+
| widget_signups       |
+----------------------+
1 row in set (0.00 sec)

At some point earlier in development there might have been other tables as well, but for what we were doing we decided that we were probably over-architecting and nixed them.

I noticed the other day that auto-complete within mysql was not working properly in this database. When I am using the particular database and I type SELECT * FROM widget<TAB> I wasn't getting my good old auto-complete that I was used to. Double tabbing to see possible auto-completions yields:

mysql> SELECT * FROM widget<TAB><TAB>
widget_date
widget_signups
[... columns on widget_signups ...]

Yet when I try to DROP this mysterious and previously deleted widget_date table, I predictably get errors saying the table doesn't exist.

My question is this: how can I repair my beloved mysql auto-complete by removing this phantom table? I would assume (wrongly?) that auto-complete suggestions within the mysql command line are drawn from a table within mysql. Where can I go to zap this safely - or do I just need to man up and get used to typing the entire table name?


texts: mysql - find duplicate sets of data grouped by foreign key

texts:
I have a large mysql table (500,000 records). I need to find sets of data that have all the same attributes (KEY, NAME AND VALUE). Some sets have 20 attributes others have 397 per each KEY.
The table looks like this. KEY is a foreign key.

ID, KEY, NAME, VALUE
1   87   Color  Red
2   87   Size   Big
3   87   Weight 6

4   85   Color  Red
5   85   Size   Big
6   85   Weight 6

7   96   Color  Red
8   96   Size   Small
8   96   Weight 7

I'm trying to write a query where if KEY=87 it finds KEY 85 matches all attributes and values like KEY 87. There are 397 different attributes rather than just the 3 I show here. I can do this in php but it's sloppy and I want to learn more about mysql but I can't get my head around it. Appreciate any help in advance.


texts: Sync data in SQL Server 2014 with Oracle database12c

texts:
I have installed Oracle database 12c enterprise on my laptop.
How can I implement data synchronization between Oracle 12c and SQL Server 2014 that whenever an INSERT,UPDATE,DELETE or ... happend on SQL Server tables I can have it on my Oracle database automatically in the shortest time it is possible?


texts: Requesting help with setting up design of a few tables and the correct foreign keys

texts:
needing some help setting up some tables and their foreign key constraints.

3 total tables:

UserTable - contains:

UserID
Other Fields

ShapeTable - contains:

ShapeID
UserID (from UserTable)
Other Fields

RecipientTable - contains:

RecipientID
UserID (from UserTable)

A user is able to draw a shape, or many shapes. A user can also add potentially many recipients. A shape can have 0 or many user's recipients added to it.

If a recipient is deleted by user, it should be removed from all stored shapes. If a shape is deleted by user, it should not delete recipients.

I'm struggling with how to design the "mappings" between the Shapes and the Recipients for each user.

When a user draws a shape, I want to present all the recipients for the user that drew the shape. The user will then be able to select 0, 1, or many of those recipients for that given shape. I'll need to be able to retrieve this later on as well. When a shape is deleted, I want it to delete the "mappings" to any recipients as well, but not necessarily delete the actual recipient record.

What is the best way to arrange the tables, should there be an additional "mappings" type table created, and what foreign key constraints should exist to enforce cascading and referential integrity?

Let me know if there's a better way to present the question if I left something out. I am using MySQL (InnoDB) and PHP (if that matters).


texts:
Can I attach a SQL 2008 R2 database to SQL 2008 that is not R2?

I have a database that I am detaching from SQL 2008 (not R2) and can attach this to SQL 2008 R2.

Can I then detatch the database from SQL 2008 R2 and attach it to SQL 2008 (not R2)?


texts: Searching between dates on a CHAR type field

texts:
I have a table that has a field in which dates are stored, the problem is that the field type is CHAR(10) and I'm being asked to query for data between certain dates.

I don't want to change the field type because I'm not sure how's that gonna affect the frontend application.

Is there a way of querying for results between dates without making changes to the data type?

Regards.


texts: What are the benefits of storing columns in JSON instead of traditional tables?

texts:
Are there any benefits in using JSON(B) over traditional table structures?

Imagine having a table structure like this:

 create table table1 (
        t_id int,
        first_name varchar(20),
        last_name varchar(20),
        age int
    )

What if you stored the same columns inside a JSON(B) field like this:

{
    "first_name":"name",
    "last_name":"name",
    "age":2
}

and have a table like this:

create table table2 (
    t_id int,
    attribute jsonb
)

Correct me if I'm wrong, but since both variants are causing a row to be completely rewritten if there have been any updates or deletes on that row, then both variants are identical in that regard.


texts: Distributing a single table across multiple disks of different performance?

texts:
I have a table that increases by several million rows per day. It holds over a year's worth of data.

The table is regularly SELECTed from, but the older the data is the less frequently it is accessed. The current data (i.e. data added in the last hour) is very frequently queried, but as the records get older they are less likely to be queried.

The database is currently on a traditional (magnetic) hard disk. I also have a much smaller solid state disk that is very fast, but not large enough to hold more than few days' worth of data.

I’d like to somehow spread the table across the two disks. I’d like new data to be INSERTed into the small, high performance disk, and then invisibly migrated to the slower but larger disk after a few days (when it’s less likely to be needed).

I know I could split the data into two tables (let's call then TABLE_SLOW and TABLE_FAST). I could insert into TABLE_FAST and then run an archiving process to transfer the oldest records into TABLE_SLOW every night, and then create a view based on a union of TABLE_SLOW and TABLE_FAST to use for queries – but I’m looking for a solution that wouldn’t require any changes to the application, only the database.

The data includes a field called ‘loadedTime’ so there’s no problem working out how old the data is.


texts: How does PostgreSQL handle Checkpoints in the middle of a WAL-enabled backup?

texts:
On a PostgreSQL v9.0 I've a WAL archiving system working. So WAL files a regulary archived (when 3 WAL are created or if a WAL is older than 15 minutes).

Now I add a binary packup of the PG_DATA directory (excluding the pg_xlog subdirectoy). To do that I perform a pg_start_backup(), the binary copy, and a pg_stop_backup().

I think I understand quite well what the pg_start_backup and pg_stop_backup are doing, the first one make a checkpoint and the last one ensure the last WAL file is archived.

From the official documentation we can see that for the binary data copy we should:

  Perform the backup, using any convenient file-system-backup tool such as tar or cpio (not pg_dump or pg_dumpall). It is neither necessary nor desirable to stop normal operation of the database while you do this.

So I'm quite perplex. This mean a Checkpoint could be performed while we make the copy. I saw a lot of documentation stating that the copy command should allow data changes while performing the copy, I'm Ok with that, simplya matter of finding the right tool. But My question is how postgreSQL will handle the recovery with a pg_data content containing some files which are inconsistent (some from before the checkpoint, some from after)?

By replaying the transaction logs Postgresql will be able to put all these files in the right state? I saw that create tables and drop operations are dangerous while the backup is performing, Isn't there some dangerous operations like vacuum commands? Does the pg_backup suspend the vacuum operations? Should I make a copy of the global/pg_control file in the end of in the start of the binary copy process? Should I use a snapshot-enabled filesystem (like with an xfs-freeze) to get a faster restore process?

I saw that a backup script crash will not launch a pg_stop_backup automatically, so there's a chance that my backup state live for a long time (until my nagios rings someone somewhere to fix the pg_stop_backup() ). So if anythingis different in PostgreSQL between these two comands I'd like to know it, to understand what impact it may have.

Enlighten me please.


texts: Very slow DELETE in PostgreSQL, workaround?

texts:
I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign keys referencing the main schema and not the other way around.

I just started filling the database with some real data taken from the previous version. The DB had reached about 1.5 GB (it's expected to grow to several 10s GB within weeks) when I had to do a bulk delete in a very central table in the main schema. All concerned foreign keys are marked ON DELETE CASCADE.

It was no surprise that this would take a long time but after 12 hours it became clear that I was better off starting over, dropping the DB and launching the migration again. But what if I need to repeat this operation later when the DB is live and much larger? Are there alternative, faster methods?

Would it be much faster if I wrote a script that will browse the dependent tables, starting at the table furthest from the central table, deleting the dependent rows table by table?

An important detail is that there are triggers on some of the tables.


texts: Need help to understand tuning up a slow SQL server query

texts:
For one of our databases the query like below is extremely slow.
Due to security reasons I am not able to share the actual query or plan but just to give an idea how query is written is below
SELECT [Id]
      ,[AboutMe]
      ,[Age]
      ,[CreationDate]
      ,[DisplayName]
      ,[DownVotes]
      ,[EmailHash]
      ,[LastAccessDate]
      ,[Location]
      ,[Reputation]
      ,[UpVotes]
      ,[Views]
      ,[WebsiteUrl]
      ,[AccountId]
  FROM [StackOverflow2010].[dbo].[Users]
  WHERE DisplayName IN (

 SELECT DisplayName from dbo.Users
 WHERE CAST(LastAccessDate AS DATE) = CAST ('20160814' AS DATE)
 AND CreationDate>= DATEADD (DAY, -30,LastAccessDate)
 AND CreationDate<= LastAccessDate)

In stackoverflow database this does not return any rows but for our existing 6 TBS database its really slow.
CreationDate and LastAccessdate columns are both datetime (10)
and DisplayName is Varchar(50)
Please suggest how can I improve performance if above can be re-written


texts: What is the purpose of the command-line options "-ANe" for MySQL?

texts:
I've recently changed how a mysqldump backup is set up, and happened across a helpful answer by RolandoMySQLDBA where his example:

  mysql ${MYSQL_CONN} -ANe"${SQL}"

Uses the -ANe command line switch to precede the query to execute.

I understand that these have the following effects:

  --no-auto-rehash, -A

  Disables auto-rehash, which is described as: Enable automatic
  rehashing. This option is on by default, which enables database,
  table, and column name completion. Use --disable-auto-rehash to
  disable rehashing. That causes mysql to start faster, but you must
  issue the rehash command or its # shortcut if you want to use name
  completion.
  --skip-column-names, -N

  Do not write column names in results.
  --execute=statement, -e statement

  Execute the statement and quit.

Obviously, I understand that since we are executing a query, the -e argument is required.

The query I am executing is changing the global variable to enable/disable the slow query log. I'm not quite sure how omitting column names in results is important in this case. I'm less clear on how disabling auto rehashing is necessary or precisely what it is doing. I understand from the description that it causes mysql to start faster, but since the mysql process is already running, isn't that ignored?

In short, what are the -A and -N arguments for in the context of changing a global variable via shell script?

MySQL 5.6.4 on Ubuntu 14.04 LTS


texts: PostgreSQL exit status when running a file

texts:
When running PostgreSQL with a single SQL command error codes are returned as expected:

% psql -c "SELECT * FROM AWDASDASDASDAS" my_db
ERROR:  relation "awdasdasdasdas" does not exist
LINE 1: SELECT * FROM AWDASDASDASDAS
% echo $?
1

But when running a file, the error is suppressed:

% psql -f test.sql my_db
psql:test.sql:1: ERROR:  relation "awdasdasdasdas" does not exist
LINE 1: SELECT * FROM AWDASDASDASDAS
% echo $?
0

Any idea how to get these errors back?


texts: Importing huge table exhausts UNDO extents in Oracle RDS (ORA-01628)

texts:
I'm attempting to do an impdp on RDS, Oracle 12c. I'm importing only one table for this particular impdp job but every time I try to import it, UNDO usage gets to about 50% and then the logs just say Resumable error: ORA-01628: max # extents (32765) reached for rollback segment.
Since this is RDS I cannot manually manage undo. I created a fresh RDS instance with a new 4TB UNDO tablespace to perform the import of just this table. I've read about creating one giant rollback segment and also about creating lots of small rollback segments to solve this problem. I've also read I can split the import into multiple parts, but I'd rather not do that if possible. Is there anything more I can do here to maybe stop the UNDO tablespace from running out of extents?


texts: Database pattern with two types of owners for an object. Like how users and groups can make posts in FB

texts:
Please help. There is a problem I have tried to resolve for some time now. I want to come up with a pattern where i have a table with advertisements on a web platform. These advertisements shall be owned either by individual users (accounts) or by an entity which represents a group of users (accounts) the members of which have permissions on the advertisement. There shall also be a manager of the advertisement. When the advertisement is owned by a group, the accounts dont matter and the info for the group shall be displayed. My problem is that I always come with anti patterns for this schema. Can you suggest me something?
 Your help will be highly appreciated.


texts: How to JOIN two table to get missing rows in the second table

texts:
In a simple voting system as

CREATE TABLE elections (
election_id int(11) NOT NULL AUTO_INCREMENT,
title varchar(255),

CREATE TABLE votes (
election_id int(11),
user_id int(11),
FOREIGN KEYs

for getting the list of elections a user has voted, the following JOIN is used

SELECT * FROM elections
JOIN votes USING(election_id)
WHERE votes.user_id='x'

but how to get the list of elections a user has NOT voted?


texts: SQL Server AD groups membership seems cached - how to flush to test?

texts:
I am configuring a new, fully AD group based security scheme on an existing SQL Server 2008.

When I change the group to which I belong to test my configs, it seems that there is quite a delay between the change and it appearing in SQL Server.

To try and improve this I do:

repadmin /syncall /Ade (to force full domain syncing)
DBCC FREESYSTEMCACHE(‘TokenAndPermUserStore’)

I've also tried disconnecting and reconnecting the user in SSMS.

Any idea how I can get the AD group permissions to apply faster? Testing once every ten minutes is painful!


texts: Getting Master Current Binlog and Binlog Position in Replication Backup

texts: I want to take the current binlog file and binlog position of the master server in a mysqldump on the slave server. I cannot use master-data=2 on slave - how can I do this nevertheless?


texts:
I have a big database that I need to extract all primary keys and foreign keys from each table.

I have pgAdmin III.

Is there a way to do this automatically and not go over each table manually?


texts: Automatically updating a record using count() of another collection

texts:
I have been looking at the docs and searching for videos on my problem, but I find nothing. I have a db called HPDASH where I have 2 collections, scripts and serverList. My problem is that I want to create a field for serverList that has the count() of the collection scripts.

I have tried this:
db.scripts.insert([{name: 'local_script'}, {name: 'qidsloader'}])
db.serverList.insert({script_count: db.scripts.count() })

The problem is that when I insert another record to scripts, the data on scripts_count stays as 2.

Is it possible to achieve this on MongoDB?


texts:
If I create schemata using...

CREATE SCHEMA [schema_name] AUTHORIZATION dbo

...is it possible to obtain a list of these user-defined schemata?

I can see that there's an INFORMATION_SCHEMA.SCHEMATA view but this doesn't offer any indication that I defined the schema.

There's also a sys.schemas view which lists the principal_id as 'dbo' - is it safe to assume that all schemata which reference 'dbo' as the principal_id are user-defined, or is there a safer means?


texts: How to add an extended property to a table type?

texts:
According to the MSDN documentation, 'TABLE_TYPE' is a valid value for the @level1type parameter as shown below.

EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'An INT value.',
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = N'TABLE_TYPE',  @level1name = AllTypesRecord,
@level2type = N'COLUMN', @level2name = 'Id'
GO

In Visual Studio 2013 in an SSDT project, the @level1type parameter name is underscored and has the error

  SQL70539: Invalid argument (@level1type)

Trying to execute the statement in a query window results in the error

  Msg 15600, Level 16, State 5, Procedure sp_addextendedproperty, Line 37
  An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.

How can I add an extended property to a table type?


texts: AWS DMS Fails with "Command failed to load data with exit error code 1"

texts:
I am trying to Migrate my Postgresql RDS to Aurora Postgresql using DMS.

All my tasks are failing, and I am unable to understand what can be done to help the tasks progress.

The error from cloud watch logs is

...
2019-05-29T15:05:48 [TASK_MANAGER ]I: Start loading table 'public'.'my_ios_device_table' (Id = 642) by subtask 3. Start load timestamp 00058A081DCF0B40 (replicationtask_util.c:707)
2019-05-29T15:05:48 [SOURCE_UNLOAD ]I: Calculated batch used for UNLOAD size is 1000 rows per fetch. (postgres_endpoint_unload.c:178)
2019-05-29T15:05:48 [SOURCE_UNLOAD ]I: REPLICA IDENTITY information for table 'public'.'my_ios_device_table': Query status='Success' Type='DEFAULT' Description='Old values of the Primary Key columns (if any) will be captured.' (postgres_endpoint_unload.c:191)
2019-05-29T15:05:49 [SOURCE_UNLOAD ]I: Unload finished for table 'public'.'my_ios_device_table' (Id = 642). 1589 rows sent. (streamcomponent.c:3392)
2019-05-29T15:05:49 [TARGET_LOAD ]I: Load finished for table 'public'.'my_ios_device_table' (Id = 642). 1589 rows received. 0 rows skipped. Volume transfered 1558848. (streamcomponent.c:3663)
2019-05-29T15:05:50 [TARGET_LOAD ]E: Command failed to load data with exit error code 1, Command output: <truncated> [1020403] (csv_target.c:981)
2019-05-29T15:05:50 [TARGET_LOAD ]E: Failed to wait for previous run [1020403] (csv_target.c:1578)
2019-05-29T15:05:50 [TARGET_LOAD ]E: Failed to load data from csv file. [1020403] (odbc_endpoint_imp.c:5648)
2019-05-29T15:05:50 [TARGET_LOAD ]E: Handling End of table 'public'.'my_ios_device_table' loading failed by subtask 3 thread 1 [1020403] (endpointshell.c:2416)
2019-05-29T15:05:50 [TASK_MANAGER ]W: Table 'public'.'my_ios_device_table' (subtask 3 thread 1) is suspended (replicationtask.c:2356)
...

I have checked the Postgresql Roles, Privileges and even disabled triggers using the
SET session_replication_role = 'replica';.

I am at my wits end as to why this is failing and how to rectify it.

I am migrating from RDS Postgres 9.5.16 to Aurora Postgres 10.7

Any help is appreciated. Thanks


texts: Can triggers be used to implement automated polymorphic association?

texts:
(Apologies for any bad formatting or ignorance of common practices within this community; this is my first question here.)

I'm attempting to (re)design a MySQL database for a personal social networking site I'm developing. Quite often, I run into situations where there is an element (such as a "like" button) that requires a globally unique id despite being associated with a variety of different "parent" element types (blog post, comment, user page, etc). Previously, my design had been giving every like button a column for blog_id, comment_id, and user_id, and leaving the irrelevant columns NULL; if a like button is on a blog post with id 5, blog_id would be set to 5 while comment_id and user_id were NULL.

This implementation seemed redundant and unmaintainable, so I searched up alternative solutions.

The best solution I found seemed to be to define a new table, say like_buttons, with a primary key like_button_id that would be provided in a foreign key column within all other significant tables (blog_posts, comments, users). Then, whenever a like button's data must be accessed, one can search for the like_button_id column of the appropriate parent element, and go on from there.

However, when defining new comments and blog posts, I do not wish to have to add another line to my code separately defining a new like button just to pass the id to the significant element. Consequently, I stumbled upon MySQL's trigger functionality in searching for a solution.

To avoid further protracted exposition, my question lies in the acceptability of the following example code:

TRIGGER insert_user_like_button
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO like_buttons VALUES();
  SET NEW.like_button_id = LAST_INSERT_ID();
END

This trigger would be repeated for every other parent element table, such as blog_posts and comments.

Assuming that like_buttons (in this example case) only has one id NOT NULL PRIMARY KEY AUTO_INCREMENT column, is this the best method for preserving uniqueness in ids across multiple tables of information? Is there any more elegant, efficient, extendable, or appropriate solution?


texts:
I have the below query which returns me the mode which is the most repeated value in a data set
select top 1 TargetPriceRec as mode
from PricingRecurring
Group By TargetPriceRec order by Count(*) desc

I get the most repeated value from this but the issue is that if there is no repeated value I am still getting the highest value in the dataset. How can I get an empty value if there are no repeating values in the dataset and also if there are multiple repeating values I still get an empty value since there is no single mode.
Any help is much appreaciated.
Thanks


texts:
SQL Server reported that our tempdb is out of space. So I had a quick google search and found this on technet

https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx

  Determining the Amount of Free Space in tempdb

  The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb.

    SELECT SUM(unallocated_extent_page_count) AS [free pages],
    (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
    FROM sys.dm_db_file_space_usage;

This is the result of the query:

free pages           free space in MB
-------------------- ---------------------------------------
1272                 9.937500

The strange thing is that when i go and check it in the ui of the ssms the size and the free space is perfectly fine

So here are my questions:

Why are they reporting two different sizes. Is this due that the script actually counts the page file size rather than "hole size"?
Why is the script dividing the sum by 128?

Any answer is greatly appreciated.


texts: I noticed that my MySQL slave's DB size is smaller than the Master's DB

texts:
I have a very simple setup of a master MySQL DB with one slave MySQL DB replicating from it.

Initially, when setting up the slave, I used mysqldump to get an initial DB dump from the master like this :

mysqldump --single-transaction --master-data  some_db > someSQL.sql

Then I go to my slave DB, created a brand new DB named "some_db" and then tried to reverse dump:

 mysql -uroot --one-database some_db < someSQL.sql

But then I did a comparison of the DB sizes on the MySQL prompt and noticed that the master's some_db's size is around 7.6 gigs, but that of the slave's is only 6.4gigs.

I find this is concerning because I am wondering if certain data got lost. From what I understand, the DB does not contain blobs or any triggers, so where did that missing 1.2 gigs probably go?

Please let me know what your thoughts are.

Thanks
IS


texts: Why does adding a transactional replication publication change all the tables and columns to "not for replication"?

texts:
I'm trying to get replication set up - this is my first time ever doing it, so I'm probably making some rookie mistake here. Right now I'm just working in the dev environment. I've been following the example given in this MSDN tutorial.

I've setup the instance to be it's own distributor, then I go to create a publication (which includes all but a few tables, and all but one view). The publication creates just fine, but afterwards, all of the tables and columns have been set to "not for replication"! So of course when I go to the snapshot agent status, I see the error "Explicit value must be specified for identity column in table either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column."

The first time this happened, I figured I had done something stupid, so I rolled back by selecting "Disable Publishing and Distribution", and did some digging. Before I started enabling & setting up replication, all tables are columns had "not for replication" set to "false", which is what I would expect. I redid the publisher & distributor setup, which didn't change anything on the table/column settings. But then I created the publication, and they had all been modified. I undid again, which reverted them all back to 'for replication', then generated but did not run a publication script (with SQL Server Management Studio's GUI), and executed each command one at a time, checking the state of the "not for replication" setting along the way. It's the call to sp_addarticle that changes the setting.

Each article is being added with the option @identityrangemanagementoption = N'manual' and @schema_option = 0x000000000803509F, which translates to options 0x1, 0x2, 0x4, 0x8, 0x10, 0x80, 0x1000, 0x4000, 0x10000, 0x20000, and 0x8000000 (see this MSDN article for what they mean).

The environment is SQL Server 2012 - dev is Developer edition, and the eventual production environment is Standard edition. I'm using this script to check the state of the "not for replication" setting:

SELECT
    tables.name AS table_name,
    columns.name AS column_name,
    COLUMNPROPERTY(columns.object_id, columns.name, 'IsIdNotForRepl') AS IsIdNotForRepl
FROM sys.tables
    JOIN sys.columns
        ON tables.object_id = columns.object_id
ORDER BY 3 DESC, 1, 2

Why would creating a replication publication actively exclude the subject objects from replication, and how do I change this behavior?


texts: Issue with refactoring columns and how ODBC query receives data

texts:
We just installed iSeries 7 box and migrated a database from i5.
The migration was to accommodate a new version of vendor's application.
They made some changes to the columns by increasing them from CHAR(2) to CHAR(5) and data in those columns was changed certain way.

When I was developing changes required for the client-side queries to work with the new data, the iSeries DBA opened each table in the 'green screen' and refactored the columns. Those changes immediately propagated to the client-side ODBC queries and everything worked fine.

In production he did not refactor - he took a backup from test environment and copied over to production. The problem arose: the queries would still see the column as CHAR(2) thus clipping data to the first 2 characters (i.e. instead of 12345 we would receive 12).

I tried restarting the IIS app pool and web site, rebooting the client machine, but it did not fix the issue. If I added a fake test record to the table, the query would return correct metadata for CHAR(5) but as soon as I deleted the test record, it would revert to wrong CHAR(2). If I added a fake column to the SELECT, same thing would happen, but as soon as I removed the fake column, the query would revert to CHAR(2) again.

It looks like wrong metadata is stuck on the actual iSeries box.

Surprising is the fact that JDBC JtOpen driver works fine with the same SQL select statement, it is completely unaffected by the way columns were refactored (that was one of the factors why this was so confusing).

Our select is very straightforward: something along the lines of SELECT A, B, C FROM T1 INNER JOIN T2 ON T2.X = T1.Y WHERE T1.Z = 'abc' i.e. nothing fancy. It is submitted from ASP.NET web application via OleDBConnection using IBMDA400 driver.

As a workaround I used CAST(B AS CHAR(5)) B and everything works, but we would like to remove the change to avoid potential problems if the columns would be further changed. To me it seems that adding a fake column would be pretty safe, but we want to get to the bottom of it, ideally with no changes to the original SELECTs.

How can we force iSeries to inform ODBC driver of refactoring?

I could only think of 2 methods:

Reboot iSeries.
Refactor the tables to CHAR(6), run the query on the web site (refresh the page) and refactor back to CHAR(5).

Appreciate any ideas!

PS: I also appreciate if anyone w/o iSeries experience would abstain from posting about other DBMS or IBM database flavours other than iSeries, as that would be not helpful. Sorry!


texts:
when I do:

SELECT ARRAY[4]

I get

array
integer[]
------
{4}

and if I do:

select *
from b
where b.ID = ANY (array [4])

That works.

Now, something else:

select array_agg(4)

I get:

array_agg
integer[]
-------
{4}

But if I do:

select *
from b
where b.ID = ANY (array_agg(4))

i get:

  ERROR:  operator does not exist: integer = integer[]

Any explanation How can it be?


texts:
I'm  currently looking into an issue where when running MySQL prepared statement I get an exception:

  SQL Exception: Can't create more than max_prepared_stmt_count statements (current value: 16382)

Looking into this it looks like I'm definetely leaking prepared statements somewhere shown by the global status using the following query:

show global status like 'com_stmt%';

Gives me back the following results:

Com_stmt_close          37693
Com_stmt_execute        54079
Com_stmt_fetch          0
Com_stmt_prepare        54079
Com_stmt_reprepare      0
Com_stmt_reset          0
Com_stmt_send_long_data 0

I'm trying to reset these counters so my app can go back to a normal running state, while I investigate where the prepared statements are being closed but I can't find any way to reset these counters.

I've tried being logged in to mysql and running flush status, I've tried sending flush-status from the mysql-admin command line utility and I've tried resetting the DB but I can never get it to reset.

I'm using MySQL 5.7


texts: Different execution plans depending on columns selected from CTE

texts:
I have the following table

CREATE TABLEA(
ID NUMERIC(19,0) IDENTITY(1,1),
COLUMNA varchar(256) NOT NULL,
COLUMNB varchar(256) NOT NULL,
COLUMNC varchar(256)  NOT NULL,
COLUMND varchar(max)  NULL,
COLUMNE bigint  NOT NULL,
COLUMNF numberic(19,0),
CONSTRAINT PK_ID PRIMARY KEY CLUSTERED
(
 COLUMNA ASC
)
) ON PRIMARY

CREATE NONCLUSTERED INDEX IDX_COLUMNE_COLUMNF ON TABLEA(COLUMNE, COLUMNF)

I am running the following CTE.

WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY COLUMNE ORDER BY COLUMNE) AS rownum,
COLUMNA,
COLUMNB,
COLUMNC,
COLUMND,
COLUMNE,
COLUMNF
WHERE COLUMNF = 1 AND COLUMNE >= 1472738400000 AND COLUMNE <= 1475244000000
)

Now the question. When I run

SELECT TOP 30000 * FROM CTE

SQL does an index seek on the nonclustered index with a key lookup on the clustered and takes 3 to 4 seconds to complete.

However, if I run the below SQL does a clustered index scan and completes in about 15 seconds.

SELECT TOP 30000 COLUMNA, COLUMNB, COLUMNC, COLUMND, COLUMNE, COLUMNF FROM CTE

To give some idea of distribution of values:

The table contains approx. ~13.7 million rows.
COLUMNF always has a value of 1.
Excluding the TOP 30000 the query returns 474296 rows, of which there are 92683 have a rownum value of 2 or higher.

Can anyone provide any insight as to why SQL would pick a different execution plan for the two queries?

Execution Plans

SELECT by column name
https://www.brentozar.com/pastetheplan/?id=HyJZeZlC

SELECT *
https://www.brentozar.com/pastetheplan/?id=r1BQgZl0