texts: Twice job results in sql server, last one is left in progress

texts:
I have the job MP - Create CSV which execute a package SSIS in sql server 2008, when I check for the job history I see the result at step 0, I expand and I have 2 results for the step 1.
Fist result have the icon for Success with the result :

  Message
  Executed as user: companyname\sa. The step succeeded.

The second one have the step 1 also, but with the icon for In Progress with the result :

  Message
  Microsoft (R) SQL Server Execute Package Utility
  Version 10.50.4000.0 for 64-bit
  Copyright (C) Microsoft Corporation 2010. All rights reserved.

  Started:  11:50:00 PM
  DTExec: The package execution returned DTSER_SUCCESS (0).
  Started:  11:50:00 PM
  Finished: 11:50:26 PM
  Elapsed:  26.645 seconds

My problem is I have a report to show all the job with their status not equal to 1 (success), my report do not work anymore because of this result "In progress".

My questions are why do I have 2 results for my step ? I think I should have only one. And what should I do when I have a job with a step status left "In progress" ?

For more information, my job is running daily and succeed every time, thank you.
User Josien have suggested me to ask my original question here.


texts: Checking if user owns child records (MySQL)

texts:
I am having a quite big problem with the following queries. The tables looks like:

A (Parent)

+------------+---------+
| id | user_id | value |
+------------+---------+
|  1 |     20 |      X |
|  2 |     20 |      Y |
|  3 |     12 |      Z |
+------------+---------+

B (Child)

+------------+-----------+
| id | parent_id | value |
+------------+-----------+
|  1 |         1 |     X |
|  2 |         2 |     Y |
|  3 |         2 |     Z |
+------------+-----------+

C (Childs of the Child records)

+------------+-----------+
| id | parent_id | value |
+------------+-----------+
|  1 |         1 |     X |
|  2 |         1 |     Y |
|  3 |         1 |     Z |
+------------+-----------+

I need two (separate) queries. The first for verification if records in the table B belongs to the user id = X and then if records in the table C belongs to the user id = X. So for example I have got IDs (1, 2, 3, 18, 329) of records from the table C and I need to check if these values belong to the user id = 20. The result should be 1, 2, 3. How can I do this? I don't work with SQL a lot so I am completely lost and I appreciate every help. Thanks


texts: PostgreSQL - VACUUM FULL does not free space back to the OS

texts:
On a Postgres 9.1 installation, I'm trying to reduce the amount of Postgres' disk usage by dropping records from tables and then running VACUUM FULL on those tables. My understanding of VACUUM FULL is that it should give disk space back to the OS, but I'm finding that this doesn't consistently happen. For one of the tables, the DELETEs and VACUUM FULL ran successfully, and the pg_database table reports a smaller size, but the disk space actually increased slightly. I can see why disk usage goes up temporarily during a VACUUM FULL operation, but the disk usage doesn't go back down afterward.

My question is, why is the disk space not being freed back to the OS after the VACUUM FULL completes? Is there something I'm missing?

edit (for posterity) This is running on CentOS 6.3, and WAL archiving is enabled.


texts: ORA-06512: at "SYS.DBMS_ADVISOR", line 201 PL/SQL: numeric

texts:
We have been using Oracle Database - Enterprise Edition 11.2.0.3 running on the IBM AIX on POWER Systems (64-bit). My question / problem is : When trying to run below command then I'm getting the following error.

My command :

SQL> exec dbms_space.auto_space_advisor_job_proc;
BEGIN dbms_space.auto_space_advisor_job_proc; END;

Here is my error message :

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_ADVISOR", line 201
ORA-06512: at "SYS.DBMS_SPACE", line 2276
ORA-06512: at "SYS.DBMS_SPACE", line 2538
ORA-06512: at line 1

Thanks and best regards,


texts:
I have a 3 tables, Employee, Languages, Employee Languages.

Now, Employee table is a profile table. Nothing special(Id, first name, last name etc.).

Languages table has two columns: Language ID column and Language name column. This table, basically is a list of all languages.

Employee Languages consists of three columns: Employee ID, Language ID and Proficiency.

So, when I want to show employee languages, I have to get language id, then correlate that id to language name and then show it to the end user.

I'm currently thinking about new scenario with two tables:

First would be Employee table.

Second would be Employee Languages, which consists of Employee ID, Language Name and Proficiency.

So, Language name and Proficiency would be populated in front end and would be shown as a drop down list.

My question is which scenario would be much more effective in my case?


texts: Run scripts on multiple SQL Server Express instances

texts:
I am asking for a solution to execute custom SQL scripts on multiple SQL Server Express instances on schedule.

Solutions that I have seen like ApexSQL Propagate and Redgate SQL Multi Script do not have a scheduler and I have more than 170 instances to run periodic jobs on them.

Any advice on how to optimally execute scheduled scripts will be deeply appreciated.


texts: Query results not as expected - Jsonb Postgres

texts:
We've created the following table:

CREATE TABLE public.influencers
(
    id integer NOT NULL DEFAULT nextval('influencers_id_seq'::regclass),
    location jsonb,
    gender text COLLATE pg_catalog."default",
    birthdate timestamp without time zone,
    ig jsonb,
    contact_info jsonb,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    categories text[] COLLATE pg_catalog."default",
    search_field text COLLATE pg_catalog."default",
    search_vector tsvector,
    ig_updated_at timestamp without time zone,
    CONSTRAINT influencers_pkey PRIMARY KEY (id),
    CONSTRAINT ig_id_must_exist CHECK (ig ? 'id'::text),
    CONSTRAINT ig_username_must_exist CHECK (ig ? 'username'::text)
)

And created the following index in order to filter a field inside ig that contains integers. This is the index we created:

CREATE INDEX idx_btree_ig_follower_count
  ON public.influencers
  USING BTREE ((ig->>'follower_count'));

The following query should return 427000 results. Instead, it returns a random number that nothing has to do with what it should return. This is the query:

SELECT count(*)
FROM "influencers"
WHERE ((ig ->> 'follower_count') >= '1000')
  AND ((ig ->> 'follower_count') <= '10000')
  AND (ig->>'follower_count') IS NOT NULL

We suspect that it is comparing strings (or text) instead of integers and that's why it's returning incomprehensible results.

However, the query that works (return expected rows) but doesn't use the idx_btree_ig_follower_count index is the following:

SELECT count(*)
FROM "influencers"
WHERE ((ig -> 'follower_count') >= '1000')
  AND ((ig -> 'follower_count') <= '10000')
  AND (ig->'follower_count') IS NOT NULL;

How can we create the query or the index in order to being able to query efficiently?


texts: MySQL innodb: if all the tuples are stored in the clustered Index then why do we call it index?

texts:
So not sure If I am confused or not, but  based on these websites :

https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/innodb-table-and-index.html

http://www.mysqltutorial.org/mysql-index/mysql-clustered-index/

  Every InnoDB table has a special index called the clustered index where the data for the rows is stored.

the actual table data is stored in the clustered Index

but i don't understand, i thought indexing was a key-pointer pair stored in a tree for faster lookups, and doesn't store the actual data hence the name Index, like a book index.

if the entire data is in the index then why call it Index?! and wouldn't this slow down a lot of things?

if the actual table is in a b+tree index then just adding a simple tuple or deleting one is logn, is this true or am i mistaking something here?

Considering .IBD files contain the actual data in the tables and .frm files are some sort of metadata (correct me If Im wrong), then which one of these is the clustered index aka the actual table?

and i couldn't find anything useful regarding this in their own documentation.


texts: at least one member of a list within another list when querying over a join

texts:
Assume the following layout in a MySQL database

Activity (id,etc) [id is primary key]
AcitivityMapper (activityID, targetYearID)  [composite key]
Years (targetYearID, targetYearName) [id primary key, name is unique]

I would like to, given a list of "year names", find the activities that can be mapped to at least one of the year names

Extra notes: using the above layout, an activity can be mapped to multiple years

Example:

Activity (1, bla1)
Year (1, year1)
Year (2, year2)
Year (3, year3)
Mapper( 1, 1)
Mapper (1, 2)

If given a list containing "year1" or "year2" i want to get activity 1

How would I do this using an SQL query?

Extra Note:

I'm using hibernate in java on spring. The objects I have are as follows:

My objects would be as follows:

@Entity @Table(name="Activity")
class Activity {
 @Id @Column(name="id") private Integer id;
 @ManyToMany @JoinTable(name="ActivityMapper",joinColumns={@JoinColumn(name="id")},inverseJoinColumns{@JoinColumn(name="targetYearID")}) private List<TargetYear> targetYears = new ArrayList<>();
}

@Entity @Table(name="Years")
class TargetYear {
 @Id @Column(name="targetYearID") private Integer id;
 @Column(name="targetYearName") private String name;
}


texts: database design - table with multiple nullable foreign keys

texts:
Context:

I have 3 unrelated entities (tables) Entity1, Entity2 and Entity3
I have a table called Operation, which can have multiple targets.
A target for an operation can be Entity1, Entity2 or Entity3.

To model this structure, I first modeled it like this (Approach 1):
Table OperationTarget

FK to Operation (Not Nullable)
FK to Entity 1 (Nullable)
FK to Entity 2 (Nullable)
FK to Entity 3 (Nullable)
A constraint that enforces only one of them will be set, and others will be Null

Then, I didn't like the idea of creating a table where at each row, 2 of 3 columns will always be Null, so I created a different approach (Approach 2):
Table OperationTarget

FK to Operation (Not Nullable)

Table OperationTargetEntity1

FK to OperationTarget(Not Nullable)
FK to Entity 1 (Not Nullable)

Table OperationTargetEntity2

FK to OperationTarget(Not Nullable)
FK to Entity 2 (Not Nullable)

Table OperationTargetEntity3

FK to OperationTarget(Not Nullable)
FK to Entity 3 (Not Nullable)

This approach feels better in terms of normalization, but in practice, this will make my codebase more complex, and also my queries more complex, as I will be needing joins. I need the parent table there for other application requirements.
I am currently going with approach 2. Is approach 1 something I should be avoiding at all costs, or when justified by simplicity and performance, it would be OK to go with it?
I've been reading on the topic for hours, some people don't like using Nullable columns at all, some say it's OK when application requires them (when it is possible you don't know the value for a column for a specific row, for instance where a user optionally can provide their birth date), but not sure if approach 1 is justifiable here, or should be avoided.


texts: Can you remove the redirect from transactional replication from an HADR publisher?

texts:
We have a pair of SQL Server 2012 availability groups that we have set up transactional replication to a 2012 distributor and subscriber.

We are moving databases for the (synchronized) secondary nodes to new faster less congested hard drives. Since these secondaries are synchronized, the transactions never commit and replication doesn't happen.

According to the BOL sp_redirect_publisher should be used if the replica on the primary is taken offline. However it doesn't appear to have a scenario quite like what I'm looking for.

Has anyone had to move database files of an AG database to new drive media while that db was being published? If so what steps did you take?


texts: ORDER BY on aggregate field on a closure table giving unexpected results

texts:
I am updating my companies product database and I'm looking into different ways to do design our faceted navigation "selector" tables.  The idea of faceted navigation might look like this:

It's hierarchical data.  I have been reading through Bill Karwin's slides on different design patterns for hierarchical data.

I'm trying out the "Closure Table" pattern.  I have these two tables that look like this:

CREATE TABLE Selector (
  SelectorID  INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Name VARCHAR(32),
  SortOrder INT
);

CREATE TABLE SelectorPath (
  AncestorID INT NOT NULL,
  DescendantID INT NOT NULL,
  PathLength INT NOT NULL,
  PRIMARY KEY(AncestorID, DescendantID),
  FOREIGN KEY (AncestorID) REFERENCES Selector(SelectorID),
  FOREIGN KEY (DescendantId) REFERENCES Selector(SelectorID)
);

I have been testing this concept on SQLFiddle.com here: http://sqlfiddle.com/#!2/881b8f/8

An ERD for SQLFiddle is:

The problem I'm running into is the ability to sort siblings.  I'm not sure the ERD file will show up very big but the idea is that at a given node, there might be an arbitrary sorting pattern implemented by the marketing team.  They may want "white" to be the last color on the list or "Bamboo" to be the first item in the material column.

In the ERD, the yellow-ish nodes (at depth of 2 where "root" is at depth of 0) would be the category names such as "material" or "color" and the leaf nodes (green on ERD) would be the values in those categories.

The second ERD picture shows the leaf nodes in red that have been properly sorted based on the sort value given for that node.

The way I'm trying to do this sorting is by using GROUP_CONCAT on the sort value like so:

  GROUP_CONCAT(s.SortOrder separator '') AS SortPath

This creates a table with a path string of integers.  Below is the original inserted table structure as well as the desired sorted order.

My first attempt was to simply use ORDER BY SortPath.  This gives results I get are simply bizarre to me.  The ordering of the aggregate field ends up changing the actual SortPath value.  Here are bizarre results next to the original table:

According to this SO answer, it would seem like this should work.

Instead, I have to use a derived table and sort on the derived table to get the results I expect.

My ultimate question is, "Why is my SortPath field being changed when using ORDER BY?"

I speculate that ORDER BY actually happens before the aggregation, but I'm not finding much and I'm not sure if there might be something else in my query that's throwing things off.

Any additional comments on this design idea of sorting a Closure Table would be appreciated as well.  The current database structure is an Adjacency List.

All three SQL statements (table, sorted table, derived sorted table) are in my SQL fiddle.

My biggest concern is that of performance of doing a derived table.  It appears to take 8ms to do a derived table while only 4ms to sort the table.

I will be long term caching DB results at the application level, so in some ways the point is moot.  However, I'm looking to learn and experience what's going on here.

Thanks!


texts: Can you script the MDS database upgrade after SQL Patch applied

texts:
I have recently automated the SQL patching of ~200 development servers.

Currently we have 6 servers that run the Microsoft Master Data Services feature.

After a SQL patch is applied there is a manual step to go in and open Master Data Services Configuration Manager and do an upgrade of the schema.

Since this kills my automation for those 6 servers I am trying to find out if there is a programmatic way to run this upgrade process after applying the SQL patch.


texts: Can R Services be run on a SQLServer provisioned via Amazon RDS?


texts:
I have installed SSRS 2008 R2 on Windows 2012 server. It is up and running. In the Configuration Manager, I have set it to point to remote Database on same network.

Then on my own PC (also on network) I open Report Manager website. I can see all reports fine. However, I get an error when attempting to run report. I then click on Datasource giving error and get this...

I have checked the domain server and the server hosting SSRS is added.
In SSRS Configuration Manager, the Authentication Type is Service Credentials, and Username is domain\SQLService. When I test the Database connection from here, all is ok.

Any ideas why this is not working? Looks like Kerberos is not the issue.
Thanks.


texts: Can a FOREIGN KEY be referring to the same table as the "source"?

texts:
I was sure that I had done this countless times, but I can't find any previous use of me doing this. But it seems to make perfect sense, so I assume that it must be possible? However, the manual says:

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table.

Emphasis mine. Source: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
I have a table for bookkeeping, and whenever a transaction "corrects" or "updates" a previous one, I have a column called "corrects id" which is a bigint and, to me, makes perfect sense to have as a FOREIGN KEY as such:
..., FOREIGN KEY ("corrects id") REFERENCES bookkeeping (id)

Note: "corrects id" is a column in the "bookkeeping" table, and id is the primary key (bigserial) of the same table.
Surely this is correct? So why does the manual say that it has to be another table? And why is the name "FOREIGN" keys if you can refer to the same table?


texts: Difference between Full backup and Copy-only full backup

texts:
I saw in the SQL Server Central thread Does a full backup truncate the log? that full backup does not truncate the log:

  No. Neither Full or Differential backups truncate the transaction log. - Lynn Pettis
  No - a full backup does not truncate the log. - Chad Crawford

So what is the difference between full backup and copy-only full backup?

For the log backup there is copy-only backup which prevent the log chain from breaking without truncating the log. So what is copy-only full backup?


texts: Postgres Order By but only the most recent of a particular type

texts:
Assume I have a PET table with three columns: Name, Type, and Date. I want to order the three columns by Date but only display the ONE most recent of a specific particular type ("Dog" for this example), all for the rest. For example, my raw list of data:

Name        Type        Date
Nemo        Fish        June 1, 2016
Fido        Dog         January 1, 2016
Felix       Dog         February 1, 2016
Whiskers    Cat         April 1, 2016
Marlin      Fish        August 1, 2016
Shifu       Cat         March 3, 2016

Would look like this when the query was applied.

Felix       Dog         February 1, 2016
Shifu       Cat         March 3, 2016
Whiskers    Cat         April 1, 2016
Nemo        Fish        June 1, 2016
Marlin      Fish        August 1, 2016

I'm sure there are multiple ways to do this, but only a few that are the most optimal from an efficiency perspective.


texts:
Consider the following situation. I have a table "test" which is system versioned. I also have a table containing the last time I checked for modified rows in that table.
CREATE TABLE [Delta]
(
    Tablename NVARCHAR(50) NOT NULL,
    Proccess UNIQUEIDENTIFIER NOT NULL,
    LastDelta DATETIME2 NOT NULL
)

I now want to create a view that does this query
Fake syntax
SELECT *
FROM [test]
FOR SYSTEM_TIME AS OF (select LastDelta from Delta where TableName = 'Test')

Are the only options to supply a datetime a fixed value or parameter? A parameter won't be usefull with a view and a fixed value doesn't help either.
I could emulate the effect of "for system_time as of" with my own query, but I would like to know if there is a way I missed to do it as shown under "fake syntax" or similar.


texts: Dump single rows (with blobs) to single files

texts:
I am creating MySQLDumps for the sake of versioning of a database. The problem currently is a table with blobs.

My first approach would be to dump single tables to single files. But next, I want on this specific table to dump each row to a single file.

How can I accomplish this using MySQLdump?


texts: Why do we use Group by 1 and Group by 1,2,3 in SQL query?

texts:
In SQL queries, we do use Group by clause to apply aggregate functions.

But what is the purpose behind using numeric value instead of column
name with Group by clause? For example: Group by 1.


texts: Create a view showing total minutes per customer per month and change from previous month

texts:
I have an existing database (SQL Server 2012, on premise installation) and need to add a column to a view which gets displayed via a Crystal Report.

The issue I am having is with restricting the scope of the minute sum to a single month and a single customer. Currently I am either having an error due to including a column in the select clause but not the group by clause or an aggregate function, or else the query will return the total minutes per customer as 'MinuteTotal' across all months in the referenced table.

My question is, how can I restrict this query in order to get the total minutes per CustomerName and per Month, so that I can compute the difference from month to month?

USE Masscomm_XT;

WITH C0 AS (
SELECT CustomerName ,Minutes. ,year(Postingdate) AS PostingYear ,month(postingdate) AS PostingMonth ,PostingDate
FROM dbo.reptview_LDUsageTrend
),

C1 AS (
SELECT CustomerName ,SUM(minutes)OVER(PARTITION BY CustomerName ,PostingMonth ,PostingYear) AS MinuteTotal
FROM C0
)

SELECT C0.CustomerName ,C1.MinuteTotal ,C0.PostingMonth ,C0.PostingYear
FROM C1
JOIN C0 ON C0.CustomerName = C1.CustomerName
;


texts:
What are the benefits of table level locking which is used by the MyISAM storage engine? Row level locking has lots of benefits like concurrent updates and reads that do not lock the table.

Edit
Its widely considered that table level locking prevents from deadlocks. But how prevention of deadlocks at the cost of concurrency is worthwhile?


texts: Setting query to run as a stored procedure and email the results

texts:
I have the following query:

select COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections'
, COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as 'Domestic Metered'
, COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as 'Commercial Metered Low'
, COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as 'Commerical Metered High'
, COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as 'Domestic Keypad'
, COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as 'Generator'
, COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as 'Commercial Keypad'
, COUNT(*) as 'Total Live'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where New_AccountStage = 7
and AddressTypeCode is null

I want to set this up to automatically email the values on a daily basis. How can I convert this into a stored procedure and use. I am using SQL server 2008


texts: Array of template type in PL/pgSQL function using %TYPE

texts:
I have a PostgreSQL database and a PL/pgSQL function that currently has arguments with copied types (for example IN arg_1 table_1.column_1%TYPE).

I want to update it so one of those arguments is actually an array of elements with that copied type. If it were a "normal" type, I'd just add [] after the type, like IN arg_1 integer[]. Following that logic, I tried adding the square brackets right after %TYPE, but I get a syntax error.

I couldn't find anything about in the Postgres docs, nor did my google fu yield useful answers.
How can I achieve that?


texts: What is a good book to read to learn about RAC?  I am most interested in the practical end rather than the theory behind it.


texts: Percona Node fails to join: Operation not permitted

texts:
Joining a new Percona node fails with:

130521  7:15:44 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (7ff38373-c1d1-11e2-0800-d595d1800e6a): 1 (Operation not permitted)
     at galera/src/replicator_str.cpp:prepare_for_IST():436. IST will be unavailable.
130521  7:15:44 [Note] WSREP: Node 0 (db-02.arqwifi) requested state transfer from '*any*'. Selected 1 (db-01.arqwifi)(SYNCED) as donor.
130521  7:15:44 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 7101)
130521  7:15:44 [Note] WSREP: Requesting state transfer: success, donor: 1

Full error log:

Host /var/log/mysqld.log, https://gist.github.com/anonymous/1128435a85351a75574e
Host /var/lib/mysql/innobackup.backup.log, https://gist.github.com/anonymous/218f9ff1882bb0f7848a
Joiner /var/log/mysqld.log, https://gist.github.com/anonymous/92b5d29129b6ae72b3cd

Just in case, output of

ls -la /var/mysql/
ls -la /var/log/mysql/
ls -la /var/lib/mysql/
ls -la /var/run/mysqld/
ls -la /etc/my.cnf

Host, https://gist.github.com/anonymous/6bb685ce492a02ead7d4
Joiner, https://gist.github.com/anonymous/2dbf0a13121fd601940c

SELINUX=disabled is on both servers.

Host

[root@db-01 gajus]# xtrabackup --print-defaults
xtrabackup would have been started with the following arguments:
--character_set_server=utf8 --collation_server=utf8_unicode_ci --skip-name-resolve --innodb_file_per_table --log_slave_updates --server_id=155150 --wsrep_provider=/usr/lib64/libgalera_smm.so --wsrep_node_address=10.128.155.150 --wsrep_cluster_address=gcomm:// --wsrep_sst_method=xtrabackup --wsrep_cluster_name=live_cluster --wsrep_sst_auth=percona:[removed] --wsrep_slave_threads=1 --log_bin=/var/log/mysql/mysql-bin.log --binlog_format=ROW --default_storage_engine=InnoDB --innodb_locks_unsafe_for_binlog=1 --innodb_autoinc_lock_mode=2 --innodb_buffer_pool_size=4G --innodb_log_file_size=1G --query_cache_type=0 --query_cache_size=0 --join_buffer_size=4M --max_connections=1000 --table_cache=2000 --table_definition_cache=2000 --key_buffer_size=50M --sort_buffer_size=10M --read_rnd_buffer_size=1M --read_buffer_size=4M --tmp_table_size=500M --max_heap_table_size=1G --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql --user=mysql --symbolic_links=0 --default_time_zone=+00:00 --max_allowed_packet=128MB

Joiner

[root@db-02 gajus]# xtrabackup --print-defaults
xtrabackup would have been started with the following arguments:
--character_set_server=utf8 --collation_server=utf8_unicode_ci --skip-name-resolve --innodb_file_per_table --log_slave_updates --server_id=155151 --wsrep_provider=/usr/lib64/libgalera_smm.so --wsrep_node_address=10.128.155.151 --wsrep_cluster_address=gcomm://10.128.155.150 --wsrep_sst_method=xtrabackup --wsrep_cluster_name=live_cluster --wsrep_sst_auth=percona:[removed] --wsrep_slave_threads=1 --log_bin=/var/log/mysql/mysql-bin.log --binlog_format=ROW --default_storage_engine=InnoDB --innodb_locks_unsafe_for_binlog=1 --innodb_autoinc_lock_mode=2 --innodb_buffer_pool_size=4G --innodb_log_file_size=1G --query_cache_type=0 --query_cache_size=0 --join_buffer_size=4M --max_connections=1000 --table_cache=2000 --table_definition_cache=2000 --key_buffer_size=50M --sort_buffer_size=10M --read_rnd_buffer_size=1M --read_buffer_size=4M --tmp_table_size=500M --max_heap_table_size=1G --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql --user=mysql --symbolic_links=0 --default_time_zone=+00:00 --max_allowed_packet=128MB


texts: SQLite JOIN ... USING - rowid works, but then it doesn't

texts:
This works.

SELECT *
FROM Demographics
JOIN MeasuresofBirthAndDeath
ON Demographics.rowid = MeasuresofBirthAndDeath.rowid;

This does not.

SELECT *
FROM Demographics
JOIN MeasuresofBirthandDeath
USING (rowid);

Why?


texts: Finding Item's distribution over customer routes

texts:
Please help me to find a solution.

I have two tables [CustomerMaster and SalesData] as shown below.

CustomerMaster

| CODE |       NAME | ROUTE | DIVISION |
|------|------------|-------|----------|
| 1001 |  Customer1 |    R1 |     Div1 |
| 1002 |  Customer2 |    R1 |     Div1 |
| 1003 |  Customer3 |    R1 |     Div1 |
| 1004 |  Customer4 |    R2 |     Div1 |
| 1005 |  Customer5 |    R2 |     Div1 |
| 1006 |  Customer6 |    R2 |     Div1 |
| 1007 |  Customer7 |    R3 |     Div2 |
| 1008 |  Customer8 |    R3 |     Div2 |
| 1009 |  Customer9 |    R3 |     Div2 |
| 1010 | Customer10 |    R4 |     Div2 |

SalesData

| CUSTOMERCODE |               DATE | ITEM | BRAND | QUANTITY |
|--------------|--------------------|------|-------|----------|
|         1001 |  January, 01 2014  |  IT1 |   BR1 |        2 |
|         1001 | February, 01 2014  |  IT2 |   BR2 |        5 |
|         1003 |  January, 01 2014  |  IT1 |   BR1 |       20 |
|         1003 | February, 01 2014  |  IT3 |   BR1 |        6 |
|         1005 |  January, 01 2014  |  IT1 |   BR1 |        5 |
|         1005 | February, 01 2014  |  IT2 |   BR2 |       11 |
|         1007 |  January, 01 2014  |  IT1 |   BR1 |       15 |
|         1007 | February, 01 2014  |  IT2 |   BR2 |        7 |
|         1007 |    March, 01 2014  |  IT3 |   BR1 |        9 |
|         1007 |    April, 01 2014  |  IT4 |   BR2 |       18 |

I need to answer below questions

To get the Brand distribution over Route and Division

ex:BR1 reached 2 customers out of 3 in R1
ex:BR1 reached 3 customers out of 6 in Div1

To get the Item distribution over Route and Division

ex:IT2 reached 1 customers out of 3 in R1
ex:IT2 reached 2 customers out of 6 in Div1

I am looking for something like this

| Item  |  Brand|Route  | Division  | TotalCustomersCount   | BuyingCustomersCount|
|-------|-------|-------|-----------|-----------------------|---------------------|
| IT2   |BR2    |R1     | Div1      |                   3   |                   1 |
| IT2   |BR2    |R4     | Div1      |                   1   |                   0 |
| IT1   |BR2    |R1     | Div1      |                   3   |                   2 |
|       |       |       |           |                       |                     |

I am trying some queries like

select s.item,s.brand,c.division,c.route,
TotalCustomersCount=(select COUNT(distinct  x.CODE) from  CustomerMaster x where x.Route=c.route),
BuyingCustomersCount=(select COUNT(distinct y.CUSTOMERCODE) from  SalesData y where y.CustomerCode=c.Code and y.item=s.Item )
from CustomerMaster c join SalesData s on c.code=s.customercode

It will be a great if someone can help me.
My logic is wrong?


texts: How to mark a Sybase ASE DB as offline for startup

texts: I need to load a database dump into a target database, on Sybase ASE 16. In order to do that, the target database must be offline. Sybase ASE doesn't have a command to put a database offline, so my only possibility is to stop the DB server and restart it, taking care that the target database is not brought online during startup. This is my question: which command/option do I need to set so that a specific database is not brought online at server startup? I couldn't find it in the official documentation :-(


texts: Index Defrags during a Full Database Backup

texts:
What are the performance drawbacks when running an Index Defrag during a Full Database Backup? Is there a way to pause/stop an Index Defrag when a backup starts?

I utilize Reorganize when frag is between 10 and 30%, I utilize Rebuild when frag is greater than 30%.


texts: Compare SQL Server high availability methods

texts:
I want to provide high availability for my SQL Server. It is important that the service is back as soon as possible, more than making sure that data isn't lost.
My SQL Server 2008 R2 is running on a VMware machine.

Which of the following ways will provide the highest availability?

SQL cluster
AlwaysOn (is it worth upgrading)?
VM level cluster (HA / VMotion)
Windows server cluster
Any other method

Regarding mirroring, I see this comment:

  Database Mirroring (SQL Server)
  This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use AlwaysOn Availability Groups instead.

What is replacing it?


texts: Two queries usually run fast but get slow when joined

texts:
There are those 2 tables

CREATE TABLE IF NOT EXISTS `media` (
  `mid` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `order_type` int(11) NOT NULL,
  `media_id` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `performed_by` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `flag` int(11) NOT NULL,
  PRIMARY KEY (`mid`),
  UNIQUE KEY `mid` (`mid`),
  UNIQUE KEY `media_id` (`user`,`media_id`),
  KEY `user` (`user`,`timestamp`),
  KEY `performed_by` (`performed_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1687306 ;

CREATE TABLE IF NOT EXISTS `media_resources_used` (
  `muid` int(11) NOT NULL AUTO_INCREMENT,
  `media_id` varchar(50) NOT NULL,
  `resources_used` text NOT NULL,
  PRIMARY KEY (`muid`),
  UNIQUE KEY `media_id` (`media_id`),
  KEY `media_id_2` (`media_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8_unicode_ci AUTO_INCREMENT=1507815;

They're connected by media_id. One row from media_resources_used can have n rows at media.

They both have over 1 million entries.

media_resources_used is used to determine what resources have been used on a media with a media_id and this works like a queue.

First I reserve a media row

UPDATE media SET performed_by = 'worker_id' WHERE performed_by = NULL LIMIT 1

Then I want to fetch the media row with the corresponding media_resources_used info

SELECT * FROM media AS me INNER JOIN media_resources_used AS au ON me.media_id = au.media_id WHERE me.performed_by = 'worker_id'

But this takes around 20 seconds to complete.

EXPLAIN shows

+----+-------------+-------+------+---------------+--------------+---------+-------+---------+-------------------------------------------------+
| id | select_type | table | type | possible_keys |     key      | key_len |  ref  |  rows   |                      Extra                      |
+----+-------------+-------+------+---------------+--------------+---------+-------+---------+-------------------------------------------------+
|  1 | SIMPLE      | me    | ref  | performed_by  | performed_by | 63      | const |       1 | Using index condition                           |
|  1 | SIMPLE      | au    | ALL  | NULL          | NULL         | NULL    | NULL  | 1439398 | Using where; Using join buffer (flat, BNL join) |
+----+-------------+-------+------+---------------+--------------+---------+-------+---------+-------------------------------------------------+

And it seems the index at media_id isn't even used.

When I run

SELECT * FROM media WHERE performed_by = 'worker_id'
SELECT * FROM media_resources_used WHERE media_id = '$media_id_from_above'

separated they both execute under 0.001 seconds.

What could be the issue there?


texts: Adding index only on read replica of MySQL RDS instance

texts:
I have created read replica of RDS production database for reporting purposes. Cross-team is using the replica for their use.

Consumers of this replica are looking for additional indexes for faster search, but the developers are not in favour of that (adding these indexes to master) concerning more time for the insertion of records in the master database.

Is it possible to add index only on read replica of MySQL database?
How this is practised in the industry? Or what other ways this problem could be solved?


texts: How much disk space was made available to Postgres by VACUUM that is not available to the OS?

texts:
VACUUM does not return disk space to the OS, instead it marks dead rows to be reused for future writes. My server has almost ran out of disk space, afterwards I ran DELETE FROM big_table to clear about 30% of rows, followed by a manual VACUUM.

No space was given back to the OS, which is fine. How many rows can I still insert before running out of space?

df -h is a useless metric for obvious reasons. Is there a query to see postgres' used disk space vs available to postgres' disk space?


texts:
I'm working on changing the permissions on our ETL to incorporate best practices.

This is our ETL in a nutshell:

Load 100+ tables from 5 different SQL Servers into stage in DW
Call procedures on stage and ODS databases to transform the data
Log the executions to a logging database

This is scheduled via a job.

What would be the appropriate permissions for this ETLUser account? Here are my thoughts:

Change to non-admin domain account
assign db_datareader on source databases
db_datareader, db_datawriter on Stage, ODS and Logging databases.
db_executor role (custom) on Stage, ODS and Logging
db_ddladmin on Stage (for truncating)
Is a proxy account on the job required?

SQL Server 2012


texts: Auto-Shrink, Encryption & Recovery Model properties in SQL Server 2008 R2

texts:
I am creating a new database in SQL Server 2008 Express R2. In the New Database dialog, I could see an option to set the Recovery Model, which is by default set to: Simple. If I set it to Full, what will happen?

I also see properties like: Auto-Shrink and Encryption. I checked online help for Auto-Shrink property where it shows information about the Compact Edition. But my database will reside on the Server and it is not a compact db. Should I enable it? What are the advantages of enabling it on a server-class database?

I also want to use the Encryption property but I worry that it might make the database unreadable if not handled correctly.

Please throw some light on these.


texts: Why is this user "Admin" created in all databases?

texts:
When I create database in SQL server Management Studio, user with name "admin" is also created for all databases:

Why this user is created? And how I can change this behavior?


texts: Select from a table where column row has Zero (0) or Zeros (000000)


texts: Select column from current table to a related table

texts:
I have this schema:
CREATE TABLE `players` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `email` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `email` (`email`)
);

CREATE TABLE `teams` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT ,
    `player_id` int(10) unsigned NOT NULL,
    `message` text NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

I need to query teams table, where player_id has email barcelonafc@outlook.com, and also sorting it by the last 30 days of creation in the database.
Right now I have this query:
SELECT *
FROM teams
WHERE created_at BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND NOW();

This query is fine for selecting from teams and for filtering by the last 30 days of creation.
But, I can't figure out how to actually access players from it, and then filter by email='barcelonafc@outlook.com' as well.
I know it might be a dumb question, but, any ideas?
Should I change the schema for this?
It is a MySQL database.


texts:
I found a 6-days rollback in a MySQL 5.6 community edition server installation.
It seems that some logs were lost, and I cannot understand why.

2013-12-22 20:38:54 380 [Note] Plugin 'FEDERATED' is disabled.
2013-12-22 20:38:54 380 [Warning] option 'innodb-autoextend-increment': unsigned value 67108864 adjusted to 1000
2013-12-22 20:38:54 388 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-12-22 20:38:54 380 [Note] InnoDB: The InnoDB memory heap is disabled
2013-12-22 20:38:54 380 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-12-22 20:38:54 380 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-12-22 20:38:54 380 [Note] InnoDB: Not using CPU crc32 instructions
2013-12-22 20:38:54 380 [Note] InnoDB: Initializing buffer pool, size = 86.0M
2013-12-22 20:38:54 380 [Note] InnoDB: Completed initialization of buffer pool
2013-12-22 20:38:54 380 [Note] InnoDB: Highest supported file format is Barracuda.
2013-12-22 20:38:54 380 [Note] InnoDB: The log sequence numbers 5988825 and 5988825 in ibdata files do not match the log sequence number 6057379 in the ib_logfiles!
2013-12-22 20:38:54 380 [Note] InnoDB: Database was not shutdown normally!
2013-12-22 20:38:54 380 [Note] InnoDB: Starting crash recovery.
2013-12-22 20:38:54 380 [Note] InnoDB: Reading tablespace information from the .ibd files...
2013-12-22 20:38:57 380 [Note] InnoDB: Restoring possible half-written data pages
2013-12-22 20:38:57 380 [Note] InnoDB: from the doublewrite buffer...
2013-12-22 20:39:07 380 [Note] InnoDB: 128 rollback segment(s) are active.
2013-12-22 20:39:07 380 [Note] InnoDB: Waiting for purge to start
2013-12-22 20:39:07 380 [Note] InnoDB: 5.6.13 started; log sequence number 6057379
2013-12-22 20:39:07 380 [Note] Server hostname (bind-address): '*'; port: 3306
2013-12-22 20:39:07 380 [Note] IPv6 is available.
2013-12-22 20:39:07 380 [Note]   - '::' resolves to '::';
2013-12-22 20:39:07 380 [Note] Server socket created on IP: '::'.
2013-12-22 20:39:12 380 [Note] Event Scheduler: Loaded 0 events
2013-12-22 20:39:12 380 [Note] C:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld: ready for connections.

Can you tell me why this occurs and how to restore that data? Is is possible?
Do you think MyISAM tables are safer and a switch could solve this kind of errors?

EDIT

More informations about this puzzle.

This machine has a RAID-1 volume.
The problem has occourred after a poweroff on 22 december. After this power off I got the previous log and my database had a many-days data rollback. I found the database exactly with data of 14 december.
From 14 to 21 december there were many graceful server shutdowns (every evening) and I found a full backup for every day from 14 to 21 december. The 22 december backup has the 8days data gap.

I think this is very strange. I can understand data loss after a poweroff, but I can not understand this so big rollback (8days) because during this 8 days there were many server shutdowns and mysqldump backup confirm that all data was correctly stored.
I supposed there was a innodb log flush problem, but why so big?

Thanks


texts: SQL Server service shutdown due to 'bad?' SSL cert

texts:
This morning it transpired that SQL Server 2012 service running on Win2012 had shut itself down.  When I logged in as the server admin and tried to access SSMS, after entering my credentials I got the 'transport' error.  On checking Windows Services, both SQL Server and Agent were stopped.  I tried to start them, but got a timeout error.
The SQL event log contains this entry:

The server could not load the certificate it needs to initiate an SSL
connection. It returned the following error: 0x8009030d. Check
certificates to make sure they are valid.

The Windows event logs contain these entries:

Log Name: Application
Source: MSSQLSERVER
Date: 14/03/2013 09:01:40
Description: Unable to load user-specified
certificate [Cert Hash(sha1)
"A1CF6EE1C352B7A185950EB400013269759C24BD"]. The server will not
accept a connection. You should verify that the certificate is
correctly installed. See "Configuring Certificate for Use by SSL" in
Books Online.
Description: TDSSNIClient initialization failed
with error 0x80092004, status code 0x80. Reason: Unable to initialize
SSL support. Cannot find object or property.
Description: TDSSNIClient initialization failed
with error 0x80092004, status code 0x1. Reason: Initialization failed
with an infrastructure error. Check for previous errors. Cannot find
object or property.
Description: Could not start the network library
because of an internal error in the network library. To determine the
cause, review the errors immediately preceding this one in the error
log.
Description: SQL Server could not spawn
FRunCommunicationsManager thread. Check the SQL Server error log and
the Windows event logs for information about possible related
problems.

The following procedure fixed the problem:

Start > SQL Server Configuration Manager
SQL Server Network Configuration > Protocols for MSSQLSERVER (Properties)
Uncheck General > Force Encryption
Start > Services > Start MSSQLSERVER service

Update:
The above only fixed the problem until the next reboot, whereby it happened again.  Apparently the SSL certificate needs permissions to be read by SQL2012, but I daren't keep rebooting until this gets fixed, so am accessing via RDC now instead of SSMS over SSL.


texts: Will replication commands build up if the distribution agent is stopped?

texts:
Assuming I have a transactional publication with a single article. There is a Publisher (PUB1) with two Subscribers (SUB1 AND SUB2), and a separate Distributor (DIST1) box is used. I would like to stop replication to one subscriber (SUB1), while the other continues to process commands (SUB2). Additionally, I would like to be able to start replication back up to SUB1 and have it pick up where it left off.

Based on this MSDN page, I would have Snapshot, Log Reader, and Distribution agents. I know I can stop the Distribution agent for SUB1 only and SUB2 will continue to get/process replication commands.

The thing that is unclear is what happens to the commands destined for SUB1. From my test, the commands seem to be applied when I start the Distribution agent. I'm concerned that they could get deleted from DIST1 after a set amount of time, even though all subscribers have not received the commands (yet).

So would the commands build up on DIST1 until both SUB1 and SUB2 are up to date and running, or is there the potential for commands to be lost? Also, is there a point at which I can't simply start the Distribution agent back up (i.e. does SQL Server decide at a certain point that it must re-initialize the articles in order to start synchronizing again)?


texts:
A have the following table in a SQL Server 2000 database (service pack 2), with about 600,000 rows:

Id [int, primary key]
Form [int]
Element [int]
Data [char(8000)]
MoreData [varchar(8000)]

(I should note: the "Data" column probably holds less than 30 characters for almost every row. No idea why char(8000) was chosen previously.)

A simple INSERT INTO query with a single row of data was timing out on my website, and attempting to perform the same query directly in SQL Server Management Studio was fruitless after over 20 minutes of waiting. I ran DBCC SHOWCONTIG and received the following:

DBCC SHOWCONTIG scanning '(Table Name)' table...
Table: '(Table Name)' (926730454); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 2555
- Extents Scanned..............................: 328
- Extent Switches..............................: 327
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 97.56% [320:328]
- Extent Scan Fragmentation ...................: 99.39%
- Avg. Bytes Free per Page.....................: 349.8
- Avg. Page Density (full).....................: 95.68%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Thinking fragmentation was the problem, I ran DBREINDEX on the table for over two hours with no luck. After searching further, I also executed sp_lock and found over a million EXT (extent) locks, the bulk being attributed to the SPID for the website (same credentials I used to connect to the database in Management Studio). Most of the remainder were by SPID 123, which didn't come up using sp_who.

Is that number of locks (a million, for a fairly low traffic website) strange? Could they be caused by my attempts to rebuild the index when I killed the Management Studio window two or three times (for not responding)? Or could this be a problem with the physical drive?

Since gathering the above information a few hours ago the number of locks has dropped to only 16, and the table is working normally again. If anyone can help I'd still like some insight into what caused the problem so that it can be prevented in the future.


texts:
First off let me say I am a newB when it comes to Mongo, so excuse my ignorance. With that being said:

Using db.copyDatabase() to copy a remote database:

What impact does this have on the database being copied. Please note the the database being copied is currently being use by a live application.

Would db.copyDatabase() lock the database?

What about performance, would it make the database run slower?

I don't really mind if while coping some new data is being written to the DB. The copy does not have to be exact per say, it just need to be as close as possible to the live DB. But I am really concern about performance issue, is it safe to do this on a live DB?


texts: Cannot open database properties dialog on a database NOT in an availability group

texts:
Here are four database servers. Database A is in an availability group. Database B is not - an instance exists on each server. On all but one of the servers, I am able to right-click on database B in SQL Server Management Studio and view its properties. On server 2, I get an error message that "the target database ('A') is in an availability group". This is true, but I'm not trying to access that database.

How can I view the properties of the instance of database B on server 2?

To re-iterate: I right-click on database B and select "Properties", and it tells me database A is in an availability group.

I'm logging into SSMS with Windows Authentication. I changed the connection details so my default DB is master, just in case that was the issue (it wasn't).

If it makes a difference, in the availability group for database A, this is the server that has a synchronized copy with automatic fail-over turned on.


texts: SqlPackage resolving table that doesn't exist

texts:
I'm trying to validate the integrity of one of our SQL Server 2016 databases by using sqlpackage.exe to extract the dacpac and validate the schema:
 ./sqlpackage.exe  /DiagnosticsFile:"diagnostics_users.txt`"   /Action:Extract /TargetFile: "users.dacpac"   /SourceConnectionString:"connstring"  /p:VerifyExtraction=true"

When I run it, I get the output
Resolving references in schema model
Successfully extracted database and saved it to file

However, when I check the source database, there is a stored procedure which references a table in a database that does not exist. If I try to run it in SSMS, I get the error: Invalid object name '[Old_Database].dbo.VIEWER_LOG'.  However, SqlPackage did not flag this as an error
When I search in the diagnostic logs, I do see the following:
Microsoft.Data.Tools.Diagnostics.Tracer Verbose: 1 : ColumnResolver: #34027998 ResolvedDescriptor for column:
ResolvedDescriptor: [DATE_TIME_STAMP]
Potentials(1):
    [SqlColumn : P1 - [OLD_DATABASE.dbo.VIEWER_LOG.DATE_TIME_STAMP]]

Available Column Sources:
ColumnSource VIEWER_LOG (affinity = 1) with no known columns

There is a table on the target database called Viewer_Log, but, not with the schema OLD_DATABASE.dbo.VIEWER_LOG.DATE_TIME_STAMP, since OLD_DATABASE.DBO does not exist.  Shouldn't SqlPackage be flagging this as an error when it resolves references? Is there a configuration available to detect and flag this?


texts: Can I copy the table structure of a #temp table to a new physical table?

texts:
I have a #tempTable that was created using

SELECT *
INTO #tempTable
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;Database=MyFileName.xls',
'SELECT * FROM [Sheet1$]')

Is there an easy way to copy the table structure of the #tempTable to a new physical table?

The temp table contains a lot of columns which contain numbers, dates, decimals, and strings, and I do not want to have to identify and manually define every column.

I am using SQL Server 2005


texts: Consolidate multiple rows into single row

texts:
In PostgreSQL 9.5, I have a table named reports:

CREATE TABLE public.reports (
  id BIGSERIAL PRIMARY KEY,
  id_station character(11) NOT NULL,
  date date NOT NULL,
  element character(4) NOT NULL,
  value smallint NOT NULL
);

For each station (id_station column) and each day (date column), I may have multiple value types (element column) : TMIN, TMAX, TAVG (sometimes these values don't exist: I may have only a TMIN and a TMAX for a given day).

Here is a (fake) sample:

22;"FR069029001";"1925-01-01";"TMAX";130
23;"FR069029001";"1925-01-01";"TMIN";-25
24;"FR069029001";"1925-01-01";"TAVG";0

I would like to consolidate these values in one single row for each station and each day using this table:

CREATE TABLE public.reports_con (
id SERIAL PRIMARY KEY,
id_station character(11) NOT NULL,
date date NOT NULL,
tmin smallint,
tmax smallint,
tavg smallint
);

I would like to achieve this result:

454;"FR069029001";"1925-01-01";-25;130;0

How to consolidate the data this way in PostgreSQL? With CREATE TABLE AS?

I know it has to be a recursive query like this (human language) :

For each day:
    For each station:
        Find values for TMIN, TMAX, TAVG
Insert the results in reports_con in a single row with day and station

I begin learning SQL and would like to achieve this within PostgreSQL (not with Python or a programming language). Can you please help me?


texts: How to normalize my db schema for a quiz application?

texts:
I am putting together a quiz application.   (I'm calling each quiz a "challenge")

I have a "challenge" table and a "challenge_question" table which is pretty straightforward.

challenge table:
id
name
num_questions (I want to randomize and only display a certain number of Qs per each challenge)

challenge_question table:
id
challenge_id
question (text of the question)
required (some questions will be required to be displayed per each challenge)
question_type (here I indicate what type of question: Mult. choice, T/F, Ordering, or Fill-in-the-blank)

Where I'm having trouble in normalization is with the types of question options:  Multiple choice, true/false, ordering, and fill-in-the-blank. I wonder, should I have a separate table for each type of challenge-question-option? Or is it possible to combine these into one somehow..

If I combined all possible options into a single table, it might look be something like this

challenge_options table:
id
challenge_question_id
option (text field, can be used for both multiple choice and for fill-in-the-blank style questions)
is_true (boolean field for true/false questions)
is_correct (boolean, used to determine multiple choice answer)
ordering_option_1
ordering_option_2
ordering_option_3
ordering_option_4 (these ordering fields would store the correct sequence of an ordering style of question)

This really strikes me as less than ideal.  Actually, I don't see how the ordering_options would work out correctly at all.  Wouldn't I need to have each of those be a separate option?  Surely there must be a better way.  Any suggestions/criticisms or alternate approaches?


texts: How to determine high tempdb disk IO that goes down after restart

texts:
(for context, I'm a developer, not a DBA)

Environment

Azure Windows VM
SQL Server 2016
Windows Server 2016 Datacenter
14GB RAM on machine, SQL set to use up to 8GB, but never gets over 2GB
Application DB Size 1.3GB
TempDB size grows to 400MB
all sql db data files (inc tempdb) on one disk and log files on another, disks are SSD
This is an operational web app that is used during the 6a - 4p work window M-F.

Summary of Problem

In the morning, when activity jumps on the app, tempdb disk bytes IO skyrockets, slowing down db response times for queries, some taking as long as 30sec!
Restarting the server fixes issue, and disk bytes IO stays low the rest of the day

Details

I have an issue that popped up only recently in the past couple of weeks with users complaining of our web application running slow. Investigation leads me to believe it is from high tempdb disk I/O. I'm seeing peaks like 22MB/s total (read/write). Disk queue lengths develop, etc.

A restart of the server brings it back down to almost nothing, and stays that way until the following morning, when everyone logs back in and activity jumps.

We do have 2 stored procs that explicitly use #temptables, but I'd think if they were the problem, the high I/O would continue after a restart, but it doesn't.

Any ideas on what type of issue would manifest itself in this manner?

Various Screenshots

This is queue length. Notice where I restarted the server and it stayed low after that until the next day (today). I did not restart today, and you can see, though not peaking as much as yesterday, is still relatively high, and is impacting user experience.

This shows the severer response time yesterday during the peak I/O period in the 6a hour. The red lines show the time that I restarted the server. You can see the response times dropped, as did the disk I/O in the previous images, but user requests are the same, so it is not an overall workload issue.

Wow, 8GB of disk I/O?

UPDATE 1

Just looked at this...seeing a lot of locks, so maybe other activity is spilling to TempDB? Will look into locks now...


texts: Quickly establishing if a remote MySQL connection is valid using PHP

texts:
I have a hosted website (Namecheap Pro shared hosting) that connects to a remote MySQL database using a read-only user privilege. This remote DB is updated by a separate system. The website works great until the remote DB goes down (Which it does now and again - cannot be resolved), at which point my website also fails with a PHP MySQL DB connect error.

To resolve this problem, I intend to create a copy of the remote DB locally on the hosted website, and synchronise this copy every hour. And then, when the website is reverting to the local copy, a message will appear on the screen informing the user that they are seeing cached data.

So far so good, except that I'm trying to get the website to test the remote DB first and if that fails, then connect to the local copy, but I can't figure out how to do a simple and quick test to establish this; my code just bombs out on a MySQL connect error after a page time-out when trying the remote DB first.

Of course, creating a replicated slave DB updated instantly when the remote DB is updated, and then pointing the website always and only at the slave would be ideal, but there are technical problems preventing this right now.

Any ideas on how to test a remote MySQL DB connection quickly so that a fallback local (copy) DB can be connected without much disruption the the user experience?


texts: How do I find the median value of a column in MySQL?

texts:
I can only imagine doing this with two database queries.  The first finds the number of rows in the database, and the second selects with an ORDER BY on the column I am interested in and LIMIT X, 1 where X is half the number of rows.

Is there a simple way to do this with only one query?

Right now I am using averages in my calculations, but I think the mean would be better; there is no upper bound to the values and they are bounded from below by 0.

EDIT: yes, I meant to say 'median' but was having some brain error & searched for 'mean'. I have now found the answer over at stackoverflow


texts: attach database - and rebuild log AT NEW LOCATION

texts:
I have a running database that I cannot take offline. I also have an mdf-file copy of that same database (snapshot of a HDD backup taken at earlier time), but no ldf file.

I need to attach this database MDF file under a new name on the same server.

So I need to attach the DB without the log file and rebuild the log file at a new location (b/c the current log file location is obviously in use).

How do I do that? I tried this, but didn't work:

CREATE DATABASE [dbname] ON
(FILENAME = N'E:\db_copy.mdf' ), --this file DOES exist
(FILENAME = N'E:\log_copy.ldf' ) --this file DOES NOT exist
FOR ATTACH_REBUILD_LOG


texts: Resume log shipping from a specific transaction

texts:
Is it possible to force SQL Log Shipping to run from a given .trn file onward?

I have two databases, where one ships to another this was working fine. However I had to restore the primary database and in the interim another .trn file was created. (This would be rubbish given the DB was restored and so couldn't be applied anywhere.) For the next transaction I manually backed up the whole database, and could manually restore it to the logship destination. (13.trn below)

The next transaction file that was created could also be manually restored. However the jobs are stuck on the transaction before the full backup, (12.trn below) and of course the job is failing as it is behind the destination db.

If I can set it to try 15.trn, it should (probably) work! I've tried changing msdb..log_shipping_secondary_databases to no avail.

So, is it possible to set which file sqllogship.exe uses next?

Basic example below!

10.trn
11.trn
<DB Was Restored>
12.trn
13.trn (contains full backup)
14.trn
15.trn


texts: I am working in a joint venture of four different contractors doing one big project. Many of the application will have to use the different ADFS servers each of the four companies have. Some are desktop application connecting directly to SQL which will be hosted in IaaS. Can SQL Server grant permissions to four different domains?


texts: Aggregate function to count occurrences of specific word

texts:
I have a table upon which I'm performing a GROUP BY statement. One of the columns in my table can contain one of 5 different words (essentially an enumerated type). Is there a way I can use an aggregate to get the occurrences of each enum, but display each count in a new column?

For example, given the table:

+---------+
|id | enum|
+---------+
| 1 | A   |
| 1 | B   |
| 1 | C   |
| 1 | A   |
+---------+

I'd like to get something like this after grouping by id:

+------------+---------+---------+
|id | a_count| b_count | c_count |
+------------+---------+---------+
| 1 |   2    |    1    |    1    |
+--------------------------------+


texts: Design The Cube Dimension for Group/Member OR "Many to Many" type relationship

texts:
I am looking for the right approach how to implement group/member like relationship in the cube dimension. i have attached the smaller version of my tables currently I have. Table-1 is SecurityTable, Table-2 is the CompositeSecurity table and Table-3 is PositionHolding Table.

CompositeSecurity is also Security that presents in Security table. but main difference is they contain another Securities and/or CompositeSecurities with their weight respect to parent.

for example

In Table-1 IBM,MSFT,My1,My2,My3 are listed Securities.

But My1,My2 are also listed in Table-2 under ParentID so they are ComositeSecurity.

ParentID=3 which is CompositeSecurity "My1" made up of 40%-IBM and 60%-MSFT

ParentID=4 which is CompositeSecurity "My2" made up of 20%-IBM and 80%-My1
Again "My1" is CompositeSecurity so We need to break down it until we reach the leaf level.

In the Table-3, I have current holding by Security.

in the result what user want to see is, when he query for total Quantity for any Security,
if that security is CompositeSecurity then break down the composite in to leaf level Securities and find the respective Quantiy value using the percentage Weight available.

For Example "What is My Total IBM Holding?"

Table-3 has

IBM as Direct holding of 100

MSFT is not a CompositeSecurity.

My1 is CompositeSecuriy and My1 has Direct Holding of IBM as 40% of My1.

My2 is CompositeSecurity and My2 has Direct Holding of IBM as 20% of My2
My also has aother CompositeSecurity My1 as 80% of My2

So membership link is My2-->My1-->IBM with respective weight of 100%-->802%-->40%

I have attached the sample result how the break down should come up on cube when user select Quantity as measure and IBM on rows.

What I need help with is how do I design this Security and CompositeSecuity as Dimension so that I can achieve the break down. I do have currently Secutity DIM and Position fact table with Table-1 and Table-3. now I have to introduced Table-2 in this.

things that have thought of so far

1)
create a new combine view from t-1 and t-2 for new DIM. the probelm with this is, Security DIM is link to FACT with ID column and when I join table-1 and Table-2 i will have repeated ID values so PK on Security DIM is not possible.

2)
tried to design table-1 and table-2 as Parent/Child DIM but not able to complete.


texts: Grant permissions on database except specific tables

texts:
I've just started out with MS-SQL, so be patience with me :)
I have 3 databases and I need to grant read access to user on all 3 databases, the challenge for me is how I can give him the permissions except for specific tables?


texts: Efficient way to handle multiple CASE statements in SELECT

texts:
I run a report in which I have a situation where based on a column value which is basically a Key or Id, I need to fetch corresponding value from the mapping Id table. Something like below:

    SELECT
     (case when [column1='A'] then (select value from Table1)
           when [column1='B'] then (select value from Table2)
           when [column1='C'] then (select value from Table3)

           and so on uptil 35 more 'when' conditions ...

  ELSE column1 end) Value
from Table1

More precisely:

SELECT
(case when [A.column1='1']
then (select value from B where B.clientId=100 and A.column1=B.Id)
when [A.column1='2']
then (select value from C where C.clientId=100 and A.column1=C.Id)
when [A.column1='3']
then (select value from D where D.clientId=100 and A.column1=D.Id)
...
and so on uptil 30 more 'when' conditions
...
ELSE column1 end)
FROM A

In Tables B,C,D..so on, we maintain data for all clients. Each client has a specific ClientId and these tables B,C,D etc have index in place on Id and ClientId columns.

Is there an efficient way to handle this in SQL Server?


texts: SQL Server Timeout exception for multiple Users using SQL Stress

texts:
We have a stored procedure which takes around 3-4 secs to execute as it has many joins and unions. But if we try to execute that stored procedure using SQL Stress with 100 threads, it gives many time out exceptions. The message is..

  Timeout expired. The timeout period elapsed prior to obtaining a
  connection from the pool. This may have occurred because all pooled
  connections were in use and max pool size was reached
  .

This is on a production server.

If we execute the same thing using SQL Stress in our local environment it does not give any exception.

It might be an SQL Server Configuration issue. It shows 5031 MB used in the task manager max and does not go beyond that, however the System memory is 64 GB.

What steps should I take to investigate further?


texts: Is it safe to use mysqldump to backup and restore a master-master cluster

texts:
Not sure if the specifics matter but just to get it out of the way I am talking about a Galera cluster on XtraDB (MariaDB).

I have a master-master setup and am interested in backing up and restoring the database if necessary.  On a single instance of MySQL I do the run of the mill

 mysqldump -u USER -p DB_NAME > DB-DATE.sql

 -and to restore-

 mysql -u USER -p < /path/to/dump.sql

I am not sure how these commands will fare in a clustered environment.  I'm going to divide this into two parts with my comments in each:

Backing up

The only issue I can think of as far as backing up goes is during the backup process some data could be written to the cluster, a query happens, my backup runs, then another related query happens leaving my data in an inconsistent state.  Let's say I have two tables users and counts.  When a user submits a form a PHP script updates the users favorite color in users as well as increments a field in counts called times_color_changed.  This simple script may look like:

<?
    //Yes, these are vulnerable to SQL injection but this is to demonstrate a point.
    $query1 = "UPDATE `users` SET `favorite_color` = ".$_POST['color']." WHERE id = $user_id";
    $query2 = "UPDATE `counts` SET `times_color_changed`  = `times_color_changed` + 1 WHERE id = $user_id";
    mysql_query($query1);
    mysql_query($query2);
?>

My worry is that while running the mysqldump in parallel the queries run as follows:

 QUERY_1 runs
 MySQL_DUMP
 Query_2 runs

 -or-

 Query1 from remote server commits on local server
 MySQL_DUMP
 Query2 from remote server commits on local server

Either of these can lead to an inconstant state.  I could always stop the SQL server but then I can't run MySQL dump.

There is an xtrabackup command but I'm not familiar with it. I've always done my dumps through mysqldump

Restoring

When restoring my dump how can I be sure that this replicates to the remote server?  Will the cluster think my dump is the "old" data and not replicate it?


texts: Why does my SSAS cube takes eternal time to process?

texts:
I have a cube like this :
After 10 mn processing it, I killed the visual studio processus.
Little dimensions A->I have in average 10 members, which do not change over time. I created dedicated dimensions because I would like to perform queries in SSRS by their Name attribute. I had a look at junk dimensions but the number of members of each dimension seems to be too high.
Do you know if the excessive processing time is caused by the number of members in DIM DATE times the number of relations between FACT 2 and DIM DATE?
Or is it caused by the little dimensions I added?
More generally, do you have an advice concerning this cube design?
thank you.
EDIT : Fact2 is also defined as a dimension(the new cube wizard suggested me to add this as a dimension, so I did it). After the removal of this dimension processing times became normal again. Why this behavior about FACT2? Why this very long processing time?


texts: What type of data ingestion does Azure Data Factory use?

texts:
This article data-ingestion says that there are two types of data ingestion

  Data can be streamed in real time or ingested in batches.

What type of data ingestion does Azure Data Factory's pipelines use when moving data from On-Premises/Cloud sources services to Azure Services (Azure Blob Storage, Azure Data Lake Store, etc)

While going through the documentation I found the following:

  ADF provides the services and tooling to compose and integrate data,
  build data pipelines, and monitor their status in real time

Does that mean that Data is streamed in real time?


texts:
I am getting the "wrong input table name" when use TRUNCATE as following:

TRUNCATE `stats`

The table should bans.

How to recover stats table?

I am using CentOS 6, and MySQL-Server.


texts:
I have simple question.
why would :

select regexp_like( (select 'https://www.hotmail.com' from dual) ,'(f|ht)tps?:') from dual ;

not work?


texts: Business logic in stored proc vs middle layer (c#)

texts:
This is more of an architecture question. I once worked at a financial company that had a web application.

Front end javascript.
Middle layer was WebAPI to access the backend SQL Server.
Back End SQL Server database.

The company put all its business logic in stored procedures. After talking with other companies, I began to hear that most put their business logic in the middle layer.

I once asked someone at the financial company why was there such a big use of stored procs but didn't get a good answer. Recently, at a job interview, they asked what the advantages and disadvantages were of putting all the business logic in the database vs the middle layer. I didn't have a good answer.

Can anyone provide some thoughts on why this is or is not a good idea?


texts: Backup & Restore Error in MS SQL Server 2016

texts:
I have a backup from SQL Server 2016. I want to restore it in my pc but I am getting this error.

  The database was backed up on a server running version 13.00.1400.
  That version is incompatible with this server, which is running
  version 11.00.5343.

I have searched in Google and have try to upload some patches but it's still the same.How can I fix it.


texts: Does postgres efficiently re-use space from deleted rows?

texts:
From the docs:

  In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row [...] But eventually, an outdated or deleted row version is no longer of interest to any transaction. The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM.

When vacuum runs, does it efficiently free the space occupied by the deleted rows by rewriting the entire block, or does block fragmentation occur with new rows fitting into available 'holes' only if they are smaller than the deleted row that has made way for them?


texts:
I have 4 tables:

Project: id name

Keyword: id, name

ProjectKeyword: project_id, keyword_id (project has many keywords, keywords has many projects)

ProjectReport: project_id, keyword_id, position(keyword, project has many project_reports)

ProjectCompetitorReport: project_report, keyword_id, position, competitor_idposition`(keyword, project, competitor has many project_reports)

With the help of those tables I need to produce something like this output:

Keyword.name, project_report.position, project_competitor_report.position

The problem that I have is repetition, I don't now how to group the result.

SELECT
    k.name,
    pr1.position,
    pr2.position
from
    project_keywords p1
    JOIN
    keywords k on p1.keyword_id = k.id
    JOIN
    project_reports pr1 on
        p1.project_id = pr1.project_id AND p1.keyword_id = pr1.keyword_id
    JOIN
    project_competitor_reports pr2 on
        p1.project_id = pr2.project_id AND p1.keyword_id = pr2.keyword_id;

I need to group the result produced by that query so that for each keyword i get all the positions from project_reports and all positions from project_position_reports.

As an exemple:
keyword1 has position with value: 1 and 2 on project_report
keyword1 has position whth value: 2 on project_competitor_reports

For this the output should be:

keyword1 1,2 2

At the moment I get them like this:

keyword1 1, 2

keyword1 2, 2


texts:
What is the correct way to design my application, for a Hotel reservation system.

So far i've got 3 tables

Customer

id pk

Reservation

id pk
customer_id fk
check_id_date
check_out_date

Room

id pk

And the premise is: a customer can have multiple reservations.
A reservation could include more than one room.
I understand the relationships between them are 1:many for Customer-Reservation.

My question is how should i model the relationship between Reservation & Room.
If i set it as 1:many, i dont take into account the date for the reservation.


texts: Does SQL check each And condition if first condition fails?

texts:
For a query of below kind does sql process data for each And condition if first And condition fails? If Emp_ID is not 12 for a row then does it check condition of Emp_Name?
So how SQL process this kind of queries?

SELECT [Emp_ID],[Emp_Name],[Emp_Sal] FROM [gyh].[dbo].[Employee_Demo] where Emp_ID='12' And Emp_Name like '%ab%'


texts: Is the BACPAC from a TDE encrypted DB also encrypted?

texts:
If you create a BACPAC of an encrypted database, will the data in the BACPAC be encrypted?
We're currently using on-prem SQL Server and use TDE to encrypt our customer databases.  We also have a requirement to maintain customer directed backups for up to 10 years.  A backup of an encrypted DB and retention is not an issue.  We're looking to migrate our platform to Azure SQL DB and thus will lose the ability to do customer directed backups.  I'm exploring the use of BACPAC to meet our needs.  If you create a BACPAC of an encrypted database, will the data in the BACPAC be encrypted?


texts: mySQL service stops after a number of hours

texts:
I am still pretty new to running databases, but after my last upgrade from mySQL 5.5.62-0+deb8u1 to 5.6.48 on my Debian machine, it appears that the mySQL service stops running a number of hours after being initially started. It is sometimes able to recover if I manually "service mysql start" it, but then in some cases, it won't let any client connect to the database until I restart the entire VPS machine. I've tried a reinstallation of mySQL 5.6.48, but to no avail and I can't understand what is going wrong based on the log files. I have a feeling it could be to do with the fact that I went from a Debian specific version of mySQL to a normal one, but if that was the case, how would mySQL manage start in the first place?
If anyone is able to advise me what to do, that would be great.
Log file - https://pastebin.com/W3emUDg4


texts: Where are queries against a linked server actually processed?

texts:
How is a query against a linked server processed? Does the linked server use its optimizer when it receives a query from a remote source? There are a few scenarios about which I'm wondering:

Scenario 1:

A stored procedure/view relies entirely upon the linked server for a result set. Does the linked server process all of this and subsequently stream the result set back to the primary server via the network?

Scenario 2:

A stored procedure/view uses local and linked data. I'm assuming that the linked server returns a result set that the local server then executes against its own, local data. I'm also assuming that the local optimizer is not in play here because it has no knowledge of anything on the linked server until it's presented with a data set.


texts:
We are about to move away from SQL 2008R2 to a much newer version. We upgraded to the Enterprise Edition of 2008R2 just for TDE, but I was just browsing here: https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15 and it appears to say that SS2019 includes TDE in the Standard Edition (the 2017 and older pages confirm it’s Enterprise Edition only).

Is this a typo or did I miss the memo and my life just got a LOT cheaper?


texts: Moving SSRS reports from one Server to other

texts:
Is there any way to move all SSRS reports from one server to other, other than downloading each .rdl file and uploading them one by one to the new one?

I followed the steps in this forum post on MSDN, but no use.


texts: SSRS - select where parameter and fixed value

texts:
So there is a lot of stuff about using SSRS parameters to select multiple values but nothing about using both a parameter and a fixed value using the in select statement.

So what I'm trying to do is this:

select * from table where id in (Parameter_1, 'total')

Yet when I do that it complains its can't find 'Parameter_1'.

I've gotten another report to work when its just:

select * from table where id = Parameter_2

but this this isn't good enough in this use case.


texts: How do I remove backslashes from records in MySQL

texts:
Long story short, I have backslashes in my data due to PHP magic quotes. I have straightened out what was wrong there, but have some records now which have unnecessary backslashes.

1 | This is an example of \' single, \" double, and \\ backslash (in the messed up records)
2 | This example is how it gets stored now \ backslash ' single " double

When I want to output my data, I need to use strip slashes to fix the issue and get the following output:

1 | This is an example of ' single, " double, and \ backslash

But then this one is wrong:

2 | This example is how it gets stored now backslash ' single " double

Thus stripslashes in my php is removing a slash which isn't technically used to escape anything.

I want to not have to use strip slashes, since my data should be clean like this:

1 | This is an example of ' single, " double, and \ backslash (how data should be)

How can I query to replace \' and \" and \\ with ' " and \?


texts: How to know SQL Server backup file BLOCKSIZE from .bak?

texts:
When I am trying to restore filelist from azure blob container I get an error.

command executing:
RESTORE FILELISTONLY FROM URL = N'http://<azureaccount>/<container>/mybak.bak'
WITH CREDENTIAL = 'mycreds'

error:

Msg 3268, Level 16, State 1, Line 15
Cannot use the backup file 'http://<azureaccount>/<container>/mybak.bak' because it was originally formatted with sector size 512 and is now on a device with sector size 65536.
Msg 3013, Level 16, State 1, Line 15
RESTORE FILELIST is terminating abnormally.

How to know the BLOCKSIZE value from .bak file?


texts: Is there a term for a table that has an "effective date" column?

texts:
I work with a system which uses a lot of tables whose primary keys include

a foreign key to another table and
a column indicating the date that record became/becomes "active".

Example tables:

employee                    (primary key is employee_id)
    employee_id    INT
    hire_date      DATE

employee_salary             (primary key is employee_id, start_date)
    employee_id    INT      (foreign keyed to employee.employee_id)
    start_date     DATE
    salary         TINYINT

When viewing an employee in our application UI, we would display all the information from employee and whichever employee_salary record had a start date most recent before the date we are interested in.

In my working group, we would refer to employee as a "base" table and employee_salary as a "repeating" table, since we consider the salary to merely be an aspect of the "base" entity, the employee.  We also say that the employee_salary table uses an "effective date".

But these don't seem to be terms in widespread use.  Are there standard terms for

tables that represent an entity, and
tables that represent a changing aspect of one of those entities?


texts:
The table has around 7 million rows.
The following query takes around 7 seconds and it seems to use none of the related indexes:

select
lead_id as id ,
user_id ,
cmol_status_id ,
crm_status_id ,
product_status_id ,
callmeback ,
followup_date ,
lead_attempt_id ,
allocated_to ,
new_id ,
leadpriority
  from gen_que
 where lead_attempt_id > '1'
   and followup_date <= curdate()
   and lead_type in ('User', 'LenderOffer')
   and crm_status_id in ('147', '180', '181', '182')
   and product_family_id in ('1', '2', '21', '23', '19')
   and product_status_cat in ('01', '02', '05', '06', '07','021', '022', '023')
   and new_id in ('11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '29', '30', '31', '32', '33', '35', '36', '37', '39', '40', '41', '42')
order by lead_id desc limit 5;

Explain extended result:

**id select_type table       type
  1  SIMPLE      gen_queview index

possible_keys
igen_queview_attemptallocated, igen_queview_followup_date, igen_queview_newcmol_id,
igen_queview_crmstatus_id, igen_queview_prodstatuscat, igen_queview_prodfamiliyid

key     key_len ref rows filtered Extra
PRIMARY 8       \N  1023 2921.7   Using where**

Instead of checking for multiple values in 'In' clause, if I use one value, then the result is in .03 secs.

Can anyone help out? Thanks in advance.


texts:
Should master, model and msdb reside on drives with 64k AU size? Is it necessary? Recommended?

All of our SQL servers are on VMware. If necessary, I would like to be able to recover a VM from a snapshot that restores OS, SQL executables and system databases. Then restore application databases from SQL backups.

I'm trying to determine if I should install SQL server to C: (4k AU) and place the system databases on D: (64k AU). And application databases on E:, F:, etc. (64k AU). Log files on G: (64k AU).  TempDB on T: (64k AU).  Would snap C and D.


texts: In MySQL, what's the most efficient way to query an entire table in chunks?

texts:
If I have a table User is it faster to do something like:

SELECT * FROM user WHERE id > ? ORDER BY id LIMIT 10000

(With each subsequent query using the previous last row's id as the param)

Or to use a LIMIT with an offset, like this:

SELECT * FROM user ORDER BY id LIMIT ?, 10000

(With total number of records retrieved so far being the offset).

As an additional complication, what if I wanted to perform a similar query based on a linking table join? Like say a user could belong to multiple roles, so I wanted a query like

SELECT u.*
 FROM user u
 INNER JOIN user_roles ur ON ur.user_id = u.id
 WHERE ur.role_name IN ('Admin', 'SuperUser', 'etc.')
 LIMIT ?, 10000

Does a join affect the decision as to which style query is preferred?


texts: The best way to limit server hog using resource governor

texts:
I have a scenario where I have a couple of users that run intensive queries where it uses up most of CPU resource. I've repeated told them to change the queries but doesn't seem to be moving anywhere. I can't really take away their access due to company policy.

What I want to do is to divide good users vs. bad users, and allocate bad users max of 25% CPU (with IO throttling), so when no one else is running the query, the bad users get 25% of CPU power. When good users are running queries, then bad users have the lowest priority thus may not even get 25%.

Is this possible?


texts:
I'm building a chat application (Yes, another one :P) and am really curious about the best way to store a chat's history(String username, String Message, Time, and possibly string channel depending on how I end up setting it up.

Since I'm using PostgreSQL anyway, I was tempted for going with it for chat history. However, I don't see a sane approach for using it to do this without generating a new row for every message, something obviously not very scale-able.

I know some MongoDB and was thinking that using a json array for each channel and storing the history in that would work well.

Is using two entirely databases in the same application a terrible idea?

Are there any other alternatives I should take a look at? (I'm open to pretty much anything)

Is there a popular PostgreSQL way of doing this? I took a look at a JSON columns, but it seemed fairly new and limited at the moment.


texts: Authentication failed when deploy an Analysis Services project

texts:
I am getting the following error on Analysis Services project on Visual Studio 2012 and SQL Server 2014:

  The project could not be deployed to the 'MyServerName\InstanceName'
  server because of the following connectivity problems :
  Authentication failed.  To verify or update the name of the target
  server, right-click on the project in Solution Explorer, select
  Project Properties, click on the Deployment tab, and then enter the
  name of the server.

On Project-> properties -> Deployment -> Server it has the ServerName\InstanceName properly, using the same ServerName\InstanceName on SSMS I can connect without problem.
On Data Source I tested the connection and it passed successfully
Changed the Impersonator user to my user which is Sysadmin

I google it, but all references I found isn't Autentication failure.

Can anyone help me?

EDIT
If I execute

telnet ipServer 2382

successfully connects.

I tried with VS2019 and had the same error.

Tried using service account for impersonator, and the user services is administrator on server and sysadmin but also get the same error.

Also I checked this guide and no one of those fixes helps me.


texts: Select XML values when attribute involved

texts:
I have a table that stores XML values in field: OtherData.

An XML value from one row:

< file path="C:\WINDOWS\system32\svchost.exe" />

I would like to select all rows that have an attribute path like '%svchost%'

What is the right SQL query to do this?


texts:
I quit not fully understand how exactly MySQL Cluster does to bring faster queries and faster databases operations.

I have

1 MGM cluster (management cluster)
1 MySQL API cluser (mysqld)
2 Storage clusters (node database)

To get fast queries, should I add more storage clusters or more MySQL API?

Thanks.


texts:
We currently have Windows Server 2008 with MS/SQL 2008R2: the default instance and two named instances.
We are spinning up a Windows 2016 Server onto which we will install MS/SQL Server 2016 Standard default instance and two named instances.  If I understand what I've read so far I can detach the databases from 2008R2, copy the files to the new 2016 environment and attach to them...  enable query store and up the comparability.  What I need to know is:

How to migrate the user records with their passwords
How to migrate the Linked Servers with their passwords

I have read posts similar to SQL Server Upgrade 2008 -> 2016 / 100 to 130 compatibility slow queries, new CE killing me but find no info' specific to Users and/or Linked Servers


texts: MySQL Stored Procedure - 0 rows affected

texts:
I have got two tables i_mast and exp_mast. The structure of both tables are same.

+-----------------+
|id INT(5)        |
|name VARCHAR(30) |
|e_date DATE      |
+-----------------+

When the e_date matches current date i need to insert those rows to exp_mast and then delete those inserted rows from i_mast. I wrote the below stored procedure for this:

CREATE PROCEDURE is_expired()
BEGIN
  DECLARE id INT(5);
  DECLARE name VARCHAR(30);
  DECLARE e_date DATE;
  DECLARE n INT;
  DECLARE i INT DEFAULT 0;
  SET n = (SELECT COUNT(*) AS num FROM i_mast WHERE e_date = DATE_FORMAT(NOW(), CURDATE());
  WHILE i < n DO
  SELECT id FROM i_mast WHERE e_date = CURDATE() LIMIT 1 INTO id;
  SELECT name FROM i_mast WHERE e_date = CURDATE() LIMIT 1 INTO name;
  SELECT e_date FROM i_mast WHERE e_date = CURDATE() LIMIT 1 INTO e_date;
  INSERT INTO exp_mast VALUES(id,name,e_date);
  DELETE from i_mast WHERE e_date = CURDATE() LIMIT 1;
  SET i = i + 1;
  END WHILE;
END;

It help me create routine named is_expired(). I can see it under routines tab in phpmyadmin. After that i tried executing and it gave me this result 0 rows affected by the last statement inside the procedure.. I have got two records that needs to be deleted today from i_mast, while making a copy in exp_mast. But it's not having any effect. Please help me solve this issue.


texts: Getting "unknown variable postgresqlini" when updating PostgreSQL

texts:
I am getting an error when attempting to upgrade a PostgreSQL installation. The full error is:

  A data directory is registered in ***unknown variable postgresqlini*** but is neither empty or a recognized data directory

I've already had a look at this issue, which seems to be the only other one I can find which is related, but it did not help me. As you can see, my paths are surrounded by quotation marks.

Here is the full command that is run (separated to multiple lines to make it readable)

"C:\ProgramData\Package Cache\44103BBE02C9C99CB868F4767B0CC3B5D50B6C09\postgresql-10.11-1-windows-x64.exe"
--unattendedmodeui minimal
--mode unattended
--servicename RM_Postgresql
--servicepassword xyz
--superpassword xyz
--prefix "C:\Program Files\\company\postgres"
--datadir "C:\Program Files\\company\data"
--disable-components stackbuilder

This command is part of an installer which launches the PostgreSQL installer. I've been running the same command for nearly 2 years without any issues (using different versions of PostgreSQL on different computers). For some reason, it seems to now be failing on one of our machines, and I am unsure as to why.

The currently installed version of PostgreSQL on the computer where it is failing is 10.6-1, and I am attempting to update to 10.11-1 if that makes any difference.

This now seems to cause issue with the previous installation as well:

initializeDatabase fail on opening postgres msg=could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?
QPSQL: Unable to connect


texts: Transactional replication altering tables and adding stored procedures

texts:
I have a database that is replicated in several locations. It is setup as transactional replication. However, now I need to add more columns to the table. Add more stored procedures and etc. Hence, if I make the change in the publisher, will it replicate across the subscribers or will it negatively impact the replication?

Suggestions will be very helpful.


texts:
After doing some change As Mr Vérace told me here in this question, by changing the id names of each table into table_name_id and using the singular notation, I still need your opinion about this database and if it is ok to start with for a dentists application.

Here is some information about it

I added a table called dentist_info to add information about each dentist.

Login Table contain login information for dentists (it may be used by multiple dentists online)

The id_logged column is a foreign key of the id of logged in dentists, and it should be in all tables. (To let dentists see only their own patient info and details).

The patient_id column is a foreign key too. And it is taken from patient table and used as foreign key in med_history that represents the general medical information of the patients, patient_info that represents the general information, and the reason of visit and what tasks to do, and in the appointment table.

I have 2 questions:

1- Is there more than that information to add for a dentist database?

2- Is this database correct? Or are there errors?


texts: SQL Server becomes slower over time until we have to restart it

texts:
We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite acceptable, but the memory consumption gets more and more until all available memory (30GB) is exhausted. After that, the system gets slower and slower.

Commands like Dbcc freeproccache have no effect.

There are not many transactions in select * from sys.dm_tran_session_transactions (not more than when the system is fine), some times this list is empty.

The first result of dbcc memorystatus is

VM Reserved               42136628
VM Committed               1487176
Locked Pages Allocated    24994048
Reserved Memory               1024
Reserved Memory In Use           0

A restart of SQL Server solves the problem for a while.

What causes this behavior? How can it be avoided?
If a real solution for the cause is too difficult, is there a command that forces SQL Server to actually release all memory without a complete DBMS restart?

The server is running on dedicated hardware (not a VM). We had some scheduled jobs, but we disabled them for a while, with no change. There are other mid tier applications running on the same server, but they use no more than 2GB memory, negligible CPU, and almost no I/O. We restarted all such applications with no change.


texts: Can replication be set up when already mirroring?

texts:
We currently use SQL Server 2012 Standard Edition and we mirror from one SQL Server instance to another for automatic failover purposes.

I want to replicate the database which is mirrored, to another SQL Server instance, for reporting purposes.  The replication will need to be as close to real time as possible.

Questions

Can you have SQL Server replication working alongside mirroring?
If so, how does it function and does it have constraints / impact on the mirroring?


texts: 60GB in version store in short amount of time, allow snapshot isolation is disabled

texts:
We had a situation where the version store went from zero to 60 GB in just a few minutes.  It stayed high for hours and due to the performance impact of this on tempdb (disk queue was 1000), almost everything became super slow and we ended up failing over to a secondary to get back to a reasonable state of performance.

Snapshot isolation is not enabled on any of the databases, and I can see from the session captures that everything is read committed or read uncommitted.   NOLOCK is used extensively (please refrain from going down this rabbit hole unless it is 100% relevant to the issue at hand) to manage blocking.

I have found references stating that online index rebuilds will use version store, but there were no rebuilds running.  There was a REORGANIZE running on a 122 GB table, but according to my understanding this would not use any version store as a reorg just moves one page at a time.  Additionally, this had been running for hours before the version store usage escalated, so I'm quite sure this was not causing the issue.

There were no sleeping sessions with open transactions.

I have found some other references about MARS and triggers using version store, but not seeing that those were factors here as there are only two triggers for auditing on some small user tables.

The one thing I've noticed that started at the time the version store exploded was a column data type change was started on a load table that had ~30 million rows:

ALTER TABLE dbo.some_table ALTER COLUMN UAI_ID BIGINT NOT NULL

And it was still running 5 hours later. (Please refrain from asking why this would be done as I don't know.)  There have been no recent changes to the process and it hasn't caused a problem before, so I don't see that this would cause the version store to go nuts, either.

I've seen references to reviewing the "internal space" used by sessions, but these only add up to ~10 GB, so I'm not seeing a correlation there.

Prevailing wait types were page latch and CXPACKET shortly after the version store usage went high.  As time went on and things started to pile up, SLEEP_TASK and CXPACKET prevailed.

What am I missing here?  What other types of activity could use so much version store so fast?


texts: Database design for students and families/parents

texts:
I'm designing a database to hold our students and their parents/guardians. Heres what I have so far:

Conditions

I need to identify student siblings (ie; brothers/sisters that are part of the same family)
A family can have an unlimited number of guardians
Brothers/Sisters always have the same set of guardians
Guardians may share the same address
Guardians may have no address, could just be an email or phone number

My joining table [families] seems a little redundant to me. Is this the best way to do it? Even though its sparse now I can see us having to add family level information in the future. For example invoices are sent out on the family level, not per student or guardian.

Should I store email/phone numbers in a seperate table?