texts: Multischema view for postgres , querying multiple databases

texts:
Im managing SaaS app where each customer has a separate database (Postgres 9.3) . I need to figure out the best way to aggregate and query this data ( READ-only ). I know postgres has problems with quering multiple databases, dblink and postgres_fdw might be an option though. Has anyone got other solution to this problem ?

So far I've managed to come up with :

DataBase A \           / Schema A \
             DataBase C             View
DataBase B /           \ Schema B /

I could pg_dump Database A/B on regular intervals then recreate them in Database C as Schema A/B . In Database C I would create a View which I hopefully be able to query (Django ORM).

As this is rather tidious I would welcome any input!

I don't need DataBase C to be in absolute sync with Database A/B , it can lag few hours behind.
Since DataBase A/B are built from the same template I don't know what is going to happen with Indexes in final View ?
Django ORM has no support for schemas (yet) , so Im guessing I would need a View.


texts: Why does `pg_lsclusters` not list my Postgres cluster?

texts:
I apt-get installed Postgres 9.2.4 (using PPA).
I used pg_dropcluster --stop 9.2 main to remove the default database cluster, because I intended on creating a new cluster on a separate SSD volume (a Rackspace block storage volume) that was just mounted.
I created that new cluster whose data resides on my SSD volume and  started it (ps shows all the normal Postgres processes running for that new cluster and I'm able to open a Postgres shell and run SQL - i.e., it's running fine). Now when I run pg_lsclusters there are none listed.

The main cluster listed just fine before I deleted it. Why won't my new cluster show up, even when it's running? I've even rebooted the server (just in case).


texts: Why/when does the SQL Server query optimizer use EQ_ROWS?

texts:
I'm trying to understand how exactly the statistics work.

I've got a clustered index on my date. I can see a convert_implicit is executed when I run the following query.

Don't know why it's doing that.

select date
from sales
where date = '20141204'

Statistics say:

Rows: 23818426
Rows Sampled : 216564
All density : 0,0008880995

So the density vector (that this query is not using) : 21153,13

Now for '20141204' my histogram is showing the following

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
2014-10-30  86723,38    38235,36    3   28770,91
2014-12-04  110856,1    36372,89    16   6902,33
2014-12-18   73389,8    68144,39    6   12180,92

I have my problem now understanding, why the optimzer chooses EQ_ROWS with the value of 36372,89 for my estimated rows. The actual rows are 9472.

I've freshly updated my statistics.

So now I'm wondering, if my histogram is just wrong because the I've not scanned all the rows with my statistics, but just 216564.

Sure enough when I query for 2014-12-05, I'll get the expected 12180,9 for estimated rows.

Why is the optimizer using EQ_ROWS for 2014-12-04?

And as for the convert_implicit - why is it converting? I thought the syntax 20141204 is a standard notation. Can we avoid the conversion in any way?


texts: Execute grant for MySQL stored procedure revoked when stored procedure is altered

texts:
I have created the following stored procedure in a MySQL database (MySQL 5.5).

DELIMITER $$

CREATE DEFINER=`remote_admin`@`%` PROCEDURE `usp_insert_deal_and_segment`()
BEGIN

    "code here"

END$$

This stored procedure is then used by multiple different scripts to insert data into the database.

There are a number of external scripts calling this stored procedure. These scripts use a different user when accessing the database (i.e. 'script_tradecap'@'127.0.0.1' rather than 'remote_admin'@'%').

So that the scripts are able to access this stored procedure the user 'script_tradecap'@'127.0.0.1' is given the following grants.

mysql> show grants for `script_tradecap`@`127.0.0.1`;
+------------------------------------------------------------------------------------------------------------------------+
| Grants for script_tradecap@127.0.0.1                                                                                |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'script_tradecap'@'127.0.0.1' IDENTIFIED BY PASSWORD '*59195B949A078D5B540E25E9800C85AF83344E9D' |
| GRANT EXECUTE ON PROCEDURE `gudrun`.`usp_insert_deal_and_segment` TO 'script_tradecap'@'127.0.0.1'                     |
+------------------------------------------------------------------------------------------------------------------------+

Note that I specifically want to limit this user so that it can only execute the usp_insert_deal_and_segment stored procedure. I don't want the user to be able to execute other stored procedures.

After that the scripts are able to access the stored procedure via this user.

The problem occurs when I alter the stored procedure. This results in the execute on procedure grant for usp_insert_deal_and_segment being revoked from the 'script_tradecap'@'127.0.0.1' user.

Why is this happening and is it possible to prevent the grant being revoked if the stored procedure is altered?

Thanks,

Gerard


texts:
I'm managing a 1.5Tb DB that is growing at a rate of ~37Gb/Week. I have been asked to split the DB into 500Gb files, but I cannot use Filegroups due to restrictions from the vendor.

My thinking was to create 3 devices and use the DBCC SHRINKFILE command to equally spread the DB across the devices. Could anyone comment on best practices? Any pit falls to my process?


texts: Are Postgres btree index keys compressed?

texts:
v8.4 and v9.2 (Yes, I know they're archaic and out of support, but there's nothing I can do about it.)

Some of the tables in the database which I manage (but don't design) have btree indexes on CHARACTER VARYING(256) fields.  Even when the field values aren't all blank, the length of the data in them is no more than 12 characters.

Yes, the physical table storage for these columns is highly compressed, but what about the btree indexes?  Would the indexes be more efficient if the columns were altered down to VARCHAR(12)?

Thanks


texts: How to import long strings at different column positions with SSIS?

texts:
Our provider stores our survey data in the following format

Columnnames for each survey

Example:

|v1         |v2|v3|t1
|CompanyName|v1|v2|v3|t1|

Data for each person

One dataset for survey1

|1        |2|1|I work in the metal industry

One dataset for survey2

|MyCompany|4|2|4|I work as a programmer

For each survey I get an identifier to consume the rest api and get the data in json. That is the way I know which data belong to which columninformation.

RestRequest("/datasources/{identifier}/columns", Method.GET)
RestRequest("/datasources/{identifier}/data", Method.GET)

I consume this with SSIS and my problem is that there are up to 400 columns and there are columns like t1 that can be at any position and hold up to 5000 character strings (t1 is an open question). Within SSIS I could now define each column as string with 5000 in length and set all columns in my stagging table to varchar(max) but this would have huge consequences to the performence. That is why I would like to ask if there is another way to handle this problem.


texts: Can Postgres timestamp be used to implement client side caching?

texts:
If I have a pool of connections in a multi-threaded client sending out requests to the database server, can I rely on transaction or statement level timestamps to determine which data is most current?
For example, let's say I have a pair of transactions T1 and T2 which both return the same row which is updated in some way. Suppose T1 starts first.  Is it possible for T2 to return stale data or complete earlier despite T1's timestamp being earlier?  For instance, if T1 is slow and expensive, can it finish later and/or return data that is somehow more current than the data returned by T2?
Specific use case: Let's say we have a web server, a database server, and two different user agents connected to the web server.  Let's say each user agent submits a mutation "simultaneously", and the web server wants to update the cache accordingly.  How can we cache the correct result on the web server?  By "correct," I mean the final result of these two mutations instead of the intermediate result.


texts:
This query works for me when I tried in PG admin query tool:

UPDATE bankdetails SET bank_details = 'mysore' ,"PAN"= 123,bank_acc = 456,
                       "UAN"=789,tax = 'myy'
WHERE  user_id = 79;
INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax)
       SELECT 79,'mysore',123,456,789,'myy'
       WHERE NOT EXISTS (SELECT 1 FROM bankdetails WHERE user_id=79);

Later I implemented in my code like this:

db.query('UPDATE bankdetails SET bank_details = $2 ,"PAN"= $3,bank_acc = $4,"UAN"=$5,tax = $6 WHERE user_id = $1; INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax) SELECT $1,$2,$3,$4,$5,$6 WHERE NOT EXISTS (SELECT 1 FROM bankdetails WHERE user_id = $1);', [companyuser.rows[0]._id, data.bankDetails.bank_details, data.bankDetails.PAN, data.bankDetails.bank_acc, data.bankDetails.UAN, data.bankDetails.tax],function(err,bank) {

                if (err) return callback(new Error('error'));
})

it says:

  cannot insert multiple commands into a prepared statement

Can some one guide me here?


texts: Automatically set value on child rows when deleting parent row?

texts:
Description

We have a table and data below :

Version: PostgreSQL 9.3
Table name: tree_data (id int, code text, name text, parent_id int)
Primary key: id
Foreign key: parent_id (refer to id)

and data:

insert into tree_data (id, code, name, parent_id) values (1, 'aaa','aaa', null);
insert into tree_data (id, code, name, parent_id) values (2, 'bbb','bbb', 1);
insert into tree_data (id, code, name, parent_id) values (3, 'ccc','ccc', 1);

 id | code | name | parent_id
 1    aaa    aaa      null
 2    bbb    bbb      1
 3    ccc    ccc      1

Here our query and result we want, it means: when deleting id = 1 (parent row), table will automatically set parent_id = null in child rows (first level) .

delete from tree_data where id = 1 ;
----> rows after deleting:
 id | code | name | parent_id
 2    bbb    bbb      null
 3    ccc    ccc      null

Our questions:

Can we use postgresql constraints to do it ?  If not, how we can do ?


texts: How to free postgres space after a DELETE without VACUUM

texts:
Is there anyway of freeing up disk space after a major delete without using VACUUM?

When I was learning about postgres I created a very large (unpartitioned) table that has since swollen to nearly 1 TByte.

To rectify this I decided I needed to partition the table by month.

I created a table for one months data and populated it with this....

WITH archived_rows AS
( DELETE FROM tabArc WHERE fixTime >= DATE '2018-02-01' AND fixTime <
DATE '2018-03-01' RETURNING * )
INSERT INTO tabArc_201802 SELECT * FROM archived_rows;

This worked fine, the new table has a months worth of data, the old table has a months less. The problem is that the disk it was on has now swollen to about 90% full, so there no room to do a similar process with a second months worth of data.

The reason isn't complicated - the big table hasn't freed up any space.

The catch is that I've never managed to do a VACUUM on this table because I simply run out of time, I've let the process run for over 24 hours and it hasn't completed.

Is there any other way to free up space? or to speed up the VACCUM process? I can't continue to partition the data until I have some more space!


texts: how to show some rows in multiple columns and one row?

texts:
I have two tables

land
===========
lid name meter address

owner
==========
ownerid  name family mobile lid

Every land can have maximum 12 owners.

The result that I need:

lid name meter address owner_name_1 owner_family_1 owner_mobile_1 owner_name_2 owner_family_2 owner_mobile_2 .... owner_name_12 owner_family_12 owner_mobile_12

I tried pivot but I can not write it correctly.


texts: Improve Connectivity To MySQL Databases Using Multiple Ports

texts:
I have a Ubuntu server with multiple websites, each website connects to its own MySQL database - each of my sites have a number of users accessing/updating the MySQL databases.

Currently all my websites are utilizing the the default MySQL port 3306.

I know I'm able to separate the connection of each website so that is uses its own dedicated port to connect to it's MySQL database.

By doing this will this improve the speed of SQL queries? I'm guessing it will... What are other peoples thoughts on this?


texts: Get all of the rows involved between tables relationships

texts:
I have a SQL Server 2016 with the following tables:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            VARCHAR(20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [CommissioningFlag] TINYINT        NOT NULL,
    [ ... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC),
    CONSTRAINT [FK_Code_AggregationLevelConfiguration]
           FOREIGN KEY ([AggregationLevelId])
            REFERENCES [dbo].[AggregationLevelConfiguration] ([AggregationLevelConfigurationId])
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

Aggregation and AggregationChildren represent relations between rows in table Code.

An aggregation will have 1 or more aggregation children. And also, a code in aggregation children could have 0 or more aggregation children.

It is something like this:

Code1
|___________________
|                   |
Code2               Code3
|________           |________________
|        |          |        |       |
Code4    Code5      Code6    Code7   Code8

All of the above are Code.Serial values.

All of the Code.CodeId values for those serials are in Aggregation table for Code1, Code2 and Code3.

And, for those serials, Code2, Code3, Code4, Code5, Code6, Code7, and Code8 are in AggregationChildren table.

I need to update Code.CommissioningFlag for all of those serials.

I have created a stored procedure to called recursively. I pass Code1 as argument, and it will do a recursive call for Code2 and Code3.

This method works fine with a few codes, but now, I have to update 3,000 codes and it blocks SQL Server.

I've thought I have navigate throw the 'tree' and get a temp table with all of the codes, and the do and update for all of them, but I don't know how to 'navigate the tree' in SQL.

How can I get all the codes to update all of them at once?


texts: MySQL replication slave hangs after encountering SET @@SESSION.GTID_NEXT= 'ANONYMOUS';

texts:
I recently installed two identical default installations of MySQL 5.7 under Ubuntu Server 16.04, and configured them to do a binary log replication. Until now this has been working fine, but suddenly the replication stops continuing and the slave query thread runs at 100% CPU without doing any work.

After some searching I found that the slave status tells that it is way behind master. Using mysqlbinlog on the binlog file indicated by Relay_Master_Log_File and position Exec_Master_Log_Pos, I found out that the statement executed at this position is:

SET @@SESSION.GTID_NEXT= 'ANONYMOUS';

Somehow the slave hangs when trying to execute this statement, sending the CPU load to 100% (which is how I discovered the situation in the first place).

Except by having the slave skip that statement using SET GLOBAL sql_slave_skip_counter=1 it is unclear to me what is the actual cause of this issue, and how I should solve this.

Any help would be really appreciated!


texts: Access denied to user ODBC, but trying to login as root

texts:
I'm trying to do a backup of a MySQL database on Windows, but for some reason it's trying to log me in as ODBC instead of root - does anyone know why?

As you can see below the red line, I can login to MySQL no problem.


texts: SQL Server 2012/2014 insufficient memory available in the buffer pool

texts:
We have bunch of SQL Server 2012 Standard and SQL Server 2014 Standard database instances running on Windows 2012, each have different storage and memory. We are getting:

  Error Code: 802; There is insufficient memory available in the buffer
  pool.

from 3 database servers when we build large SQL Server indexes, and it's happening intermittently, once in 3 or 4 months. The same indexes run fine on servers with same version of SQL Server and only 1/3 of memory and CPU.

SQL Server 2012 Standard Servers

Memory 110GB allocated out of 128 GB. Version 11.0.6 - We get reported the following error when we run heavy index:

  Error Code: 802; There is insufficient memory available in the buffer pool.

Memory 78GB allocated to SQL out of 96GB. Version 11.0.6 - We get reported the following error when we run heavy index:

  Error Code: 802; There is insufficient memory available in the buffer pool.

Memory allocated 18GB out of 24GB. VM. Version 11.0.5

No indexing error

Memory 24GB out of 32GB. Physical. Version 11.0.5

No indexing error

SQL Server 2014 Standard Servers

Memory 56GB out of 64GB physical memory. Version 12.0.5 - We get reported the following error when we run heavy index:

  Error Code: 802; There is insufficient memory available in the buffer pool.

Memory 18GB out 24GB - VM. Version 12.0.5.

No indexing error


texts: Can't access MySQL after sourcing a database and reboot

texts:
I'm trying to set a local dev environment on a Debian VirtualBox and am now stuck for days with the following situation.

After setting up apache, php, mysql, pma and got eveything running, I open the mysql console with root user.
I create the empty database, "use" it then "source" the file I want to load.
When finished, I eventually exit from the client.
I can connect again, exit as many times as I want...
I also can access to phpmyadmin with the same root user, and confirm that the database was properly loaded.

Everything's fine, till I reboot debian. From now on, I stick to :

  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I've tried to access with different users, nothing changes.
Same thing for phpmyadmin.

As I work with Vbox snapshots,I've determined that the issue seems related to the database.
Rebooting after creating the empty database is fine, I'm still able to connect.

I've crawled many related topics accross the web, and couldn't come up with that particular situation, and all the approaching topics solutions start with connecting with root user - which I can't do.

What am I missing ?

Thanks for your help !


texts: Londiste PostgreSQL replication over slow connection

texts:
I've got a setup with a single master and two branches. Unfortunately, the network connection to the master is over a 3G wireless network. It's not terribly slow, but the latency is pretty high.

Replication is working well and has been for years, but if there's a network outage, recovery takes much longer than it should. A 1 hour outage can take 30+ minutes to catch up. Outages aren't common, but can last days.

I suspect that the reason may be that while the amount of data is small, there are many transactions (several thousand per hour each of about 200 bytes) and each transaction incurs a substantial amount of network overhead.

Any ideas on how to improve this situation? I saw the pgq_lazy_fetch setting, but I'm not sure this does what I need and looking at the source, it already defaults to 300.

Using postgreSQL 9.5 and skytools 3.2 on Debian Jessie.
Master database is actually floating in the middle of a large estuary.


texts: Return values as 1 or 0 together with percent

texts:
I need a SELECT that will result in a percentage and columns with 1 and 0 for exist or not exist.

The goal is to generate data for a usage meter (Percentage 0.0-1.0) and each measurable value as 1 or 0.
It also has to show a row for every user in a users table, even if that user has no items at all.

Scenario:

In one table, a user can have many items, thet all have a specific ItemType.
Like type A, B, C, D or E
If a user has items in all types, the result should be (5/5 100%):
Percentage  A  B  C  D  E
1.0, 1, 1, 1, 1, 1
If a user has none of the types (0/5 0%):
Percentage  A  B  C  D  E
0.0, 0, 0, 0, 0, 0
If a user has some of the types (3/5 60%):
Percentage  A  B  C  D  E
0.6, 1, 1, 1, 0, 0
SAMPLE:


texts:
I wrote a query in which I want to merge two tables and also create a log table for the data.

as you can see at first I declared the @DimvisitorLog. This is for data log
I checked all type of fields and the are all ok. I mean there is nothing wrong with matching datatypes but I face this error:


texts: Postgres SQL on AWS RDS how to grant permission for schema, select on tables to user

texts:
I have logged in as superuser and created a readonly user.

When I run grant command, it says that schema does not exists

I am logging in as root:

sql -h myhost_name -U root -d postgres -W

grant usage on schema autorsid to readonly ;
ERROR:  schema "autorsid" does not exist

while schema does exists  but schema owner is not root.

Isn't when you are superuser, we should be able to grant permissions for all database/schema/tables etc.


texts:
Do Indexes in the T-SQL Data Warehouse automatically go over and optimize into the SSAS Cube?
I am reading that applying indexes, will transfer into SSAS. I thought SQL and SSAS are totally different environments.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/acda0b00-b40c-47cf-b9ca-334ccf2e4212/how-indexing-affect-performance-on-analyses-service-cube?forum=sqlanalysisservices

  So the point is, create appropriate indexes on source tables to
  improve the performance of the query which SSAS fires while processing
  the cube or while retrieving data from the source. If you have access
  to the source data, you can use this DMV to identify missing indexes
  or you can use the Index Tuning Advisor for identifying and creating
  missing indexes on the source.

Additionally, do columnstore indexes translate into SSAS Cubes?


texts:
not sure why I can't even EXPLAIN this query.  Mysql refuses to EXPLAIN it.

Table contains a million meaningless test entries.  Full schema and data dump of the table available here:
http://runboard.com/institems.sql.gz  (under 7 Mb).

Thanks in advance for any help.

Query:

UPDATE institems SET
container_slot_1 = IF(container_slot_1 = 10001013764, 0, container_slot_1),
container_slot_2 = IF(container_slot_2 = 10001013764, 0, container_slot_2),
container_slot_3 = IF(container_slot_3 = 10001013764, 0, container_slot_3),
container_slot_4 = IF(container_slot_4 = 10001013764, 0, container_slot_4),
container_slot_5 = IF(container_slot_5 = 10001013764, 0, container_slot_5),
container_slot_6 = IF(container_slot_6 = 10001013764, 0, container_slot_6),
container_slot_7 = IF(container_slot_7 = 10001013764, 0, container_slot_7),
container_slot_8 = IF(container_slot_8 = 10001013764, 0, container_slot_8),
container_slot_9 = IF(container_slot_9 = 10001013764, 0, container_slot_9),
container_slot_10 = IF(container_slot_10 = 10001013764, 0, container_slot_10);

Table schema:

CREATE TABLE `institems` (
       `id` bigint(11) NOT NULL AUTO_INCREMENT,
       `item` int(10) unsigned NOT NULL DEFAULT '0',
       `charges` int(10) unsigned NOT NULL DEFAULT '0',
       `container_slot_1` bigint(20) unsigned NOT NULL DEFAULT '0',
       `container_slot_2` bigint(20) unsigned NOT NULL DEFAULT '0',
       `container_slot_3` bigint(20) unsigned NOT NULL DEFAULT '0',
       `container_slot_4` bigint(20) unsigned NOT NULL DEFAULT '0',
       `container_slot_5` bigint(20) unsigned NOT NULL DEFAULT '0',
       `container_slot_6` bigint(20) unsigned NOT NULL DEFAULT '0',
       `container_slot_7` bigint(20) unsigned NOT NULL DEFAULT '0',
       `container_slot_8` bigint(20) unsigned NOT NULL DEFAULT '0',
       `container_slot_9` bigint(20) unsigned NOT NULL DEFAULT '0',
       `container_slot_10` bigint(20) unsigned NOT NULL DEFAULT '0',
       `amount` int(10) unsigned NOT NULL DEFAULT '0',
       PRIMARY KEY (`id`),
       KEY `container_slot_1` (`container_slot_1`),
       KEY `container_slot_2` (`container_slot_2`),
       KEY `container_slot_3` (`container_slot_3`),
       KEY `container_slot_4` (`container_slot_4`),
       KEY `container_slot_5` (`container_slot_5`),
       KEY `container_slot_6` (`container_slot_6`),
       KEY `container_slot_7` (`container_slot_7`),
       KEY `container_slot_8` (`container_slot_8`),
       KEY `container_slot_9` (`container_slot_9`),
       KEY `container_slot_10` (`container_slot_10`),
       KEY `item` (`item`)
) ENGINE=MyISAM AUTO_INCREMENT=10001013765 DEFAULT CHARSET=utf8


texts:
I have developed a game recently and the database is running on MSSQL.

Here is my database structure

Table : Player

PlayerID uniqueIdentifier (PK)
PlayerName nvarchar

Table : GameResult

ID bigint (PK - Auto Increment)
PlayerID uniqueIdentifier (FK)
DateCreated Datetime
Score int
TimeTaken bigint
PuzzleID int

I have done an SQL listing Top 50 players that sort by highest score (DESC) and timetaken (ASC)
Sql below allowed me to get the result for each puzzle id. I'm not sure if it is 100% but I believe it is correct.

;with ResultSet (PlayerID, maxScore, minTime, playedDate)
AS
(
  SELECT TOP 50 PlayerID, MAX(score) as maxScore, MIN(timetaken) as minTime, MIN(datecreated) as playedDate
    FROM gameresult
    WHERE puzzleID = @PuzzleID
    GROUP BY PlayerID
    ORDER BY maxScore desc, minTime asc, playedDate asc
)
SELECT RSP.[PlayerID], RSP.[PlayerName], RSA.maxScore, RSA.minTime, RSA.PlayedDate
FROM ResultSet RSA
INNER JOIN Player RSP WITH(NOLOCK)
    ON RSA.PlayerID = RSP.PlayerID
ORDER BY
    maxScore DESC,
    minTime ASC,
    playedDate ASC

Question

1) I need to modify the SQL to do a cumulative rank of 3 puzzle ID. For example, Puzzle 1, 2, 3 and it should be sort by highest sum score (DESC), and sum timetaken (ASC)

2) I also need an overall score population for all the possible 1 to 7 puzzle.

3) Each player only allowed to appear on the list once. First played and first to get highest score will be rank 1st.

I tried using CTE with UNION but the SQL statement doesn't work.

I hope gurus here can help me out on this. Much appreciated.


texts: Problem tracing Error: 8623 with extended events

texts:
From time to time we are getting SQL Server error:

Error: 8623, Severity: 16, State: 1
The query processor ran out of
internal resources and could not produce a query plan. This is a rare
event and only expected for extremely complex queries or queries that
reference a very large number of tables or partitions. Please simplify
the query. If you believe you have received this message in error,
contact Customer Support Services for more information.

To be able catch what is causing this error I have created an extended event (XE) session on XE event error_reported. The XE session seems to be working as other errors are logged but not 8623.
My XE session is created like this:
CREATE EVENT SESSION [ErrorReported]
ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)
    WHERE ([severity]> 15))
ADD TARGET package0.event_file(SET filename=N'C:\trace\ErrorReported.xel',max_file_size=(250),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

Anyone that help me to get XE to log also error 5623? Any help hugely appreciated.
We are currently running Microsoft SQL Server 2016 (SP2-CU10).


texts: MySQL "bad handshake" after entering password

texts:
I'm having problem to connect to the remote DB from the terminal as follow

mysql -u db_user -p -h 10.100.1.100
Enter password:

afer entering password I'm getting that error:

ERROR 1043 (08S01): Bad handshake

and I can connect to the DB from a PC using MySQL Workbench.

Running MySQL  Ver 14.14 Distrib 5.1.73 for CentOS6


texts:
Recently I attended one interview and this was the question:

Many Users reported that they are not able to connect to server.Where as I can connect to server.

My Answer :

I will check out their permissions in security context if their access is revoked or lost (But they still have permissions).
May be their accounts are locked (No their accounts are not locked).

The Interviewer still expects more answer. What am I missing here? Can anyone help.

I thought of telling trigger but I am not sure .


texts: How to force MySQL/MariaDB to show numbers in scientific notation?

texts:
I have a local MariaDB server 5.5.37 where I have some numeric data in it stored as DOUBLE. I need to perform some queries on this data (things like AVG(...) or STD(...)), and I also have a MySQL server 5.1.73, where I have similar data stored using the same schema.

Here's the problem: when I run the same query on the MySQL server, I get results in scientific notation (e.g. 1.267e-13), while the same query on the MariaDB server gives 0.000000000001267. I'd like to be able to explicitly determine the display format, and in particular, I want the scientific notation.

The query is something like:

SELECT AVG(some_field), STD(some_field) FROM some_table GROUP BY other_field;

(actually much more complicated than that)

This happens with both the mysql command line client, and with MySQL Workbench.

Update:

I ended up defining my own function to convert from DOUBLE to a string in scientific notation, based on the answers below, which should work correctly for negative numbers:

drop function if exists sci;
DELIMITER $$
create function sci (num DOUBLE) returns text deterministic language sql
begin
  set @exp = IF(num=0, 0, FLOOR(LOG10(ABS(num))));
  return CONCAT(FORMAT(num*POWER(10,-@exp),6),'e',@exp);
end$$
DELIMITER ;


texts: Is sharding effective for small collections?

texts:
It looks like database sharding is great if I have huge collections. What if I have lots of fairly sized collections ? Let's say that for 1 collection of 100 000 000 documents (not very big comments) sharding is effective. Is it also effective for 10 000 collections with 10 000 documents each ?

(I think this question is still valid for table oriented databases if you replace collections with tables and documents with rows. If possible I'd like to know the theoretical answer as well as the answer in the specific MongoDB scenario, if different from the theoretical answer.)


texts: Mac OS X 10.10 mySQL 5.6.26 accessible from CLI but not port 3306

texts:
I did a fresh install of mySQL 5.6.26 on my Mac today. I was able to access mySQL from Terminal.app as expected, however I could not get mySQL workbench to attach to 127.0.0.1 port 3306. What really tripped me up was I could not connect to this port even locally (nc -z 127.0.0.1 3306).

long story short, the mySQL system preference pane created a launch Daemon for mySQL on port 3307. /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

I'm not sure why this is...  every other install I've done was on 3306. Just though I'd drop a note to save others from needlessly digging into why OS X's firewall was "blocking" the port when it really was't. Also, it seems this LD will override the port in my.cnf, even if it is hard coded to 3306.

Hope this helps!

bash-3.2# defaults read /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
{
    Disabled = 0;
    ExitTimeOut = 600;
    GroupName = "_mysql";
    KeepAlive = 1;
    Label = "com.oracle.oss.mysql.mysqld";
    LaunchOnlyOnce = 0;
    ProcessType = Interactive;
    Program = "/usr/local/mysql/bin/mysqld";
    ProgramArguments =     (
        "/usr/local/mysql/bin/mysqld",
        "--user=_mysql",
        "--basedir=/usr/local/mysql",
        "--datadir=/usr/local/mysql/data",
        "--plugin-dir=/usr/local/mysql/lib/plugin",
        "--log-error=/usr/local/mysql/data/mysqld.local.err",
        "--pid-file=/usr/local/mysql/data/mysqld.local.pid",
        "--port=**3307**"
    );
    RunAtLoad = 1;
    SessionCreate = 1;
    UserName = "_mysql";
    WorkingDirectory = "/usr/local/mysql";
}


texts: Backing up / restoring a 20GB database with images stored as binary code

texts:
I am trying to backup and restore a 20GB database from a SQL Server 2012 to another SQL Server 2014, but I have come across the following issues:

The developers [against best practice] have stored multiple images in fields within the database as binary code.

This therefore exceeds the 65532 character limit in some fields, so even though the images do show [based upon the data saved within this field], I cannot find the data in the field beyond this 65532 limit, within SQL Server.

How can I export / locate this data after the 65532 character limit?
When I have attempted to restore the database I am getting this error message:

  Restore of database 'zapkam' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  Additional information:

  System.Data.SqlClient.SqlError: RESTORE detected an error on page
  (1:1592996) in database "zapkam" as read from the backup set.
  (Microsoft.SqlServer.SmoExtended)

I have managed to restore two other smaller databases using the same technique, but am wondering if it's an issue with the database itself.
I have uploaded this database to the new server using FileZilla FTP Client, but it has cut out, painfully at 80% + 90% on a couple of occasions.

Is there a better solution for uploading these big files that I could possibly use please? For example, uploading table by table or similar...


texts: Help with designing sales and inventory management system

texts:
I need to make a sale and inventory management system. I'm stuck on the purchasing price and stock for the product.
Let's say you bought 10 t-shirt for $10 per t-shirt and selling them at $20 per t-shirt.
You sold 8 t-shirt quickly and decided to buy 10 more. Now, the price is $12 per t-shirt.
Now , you've total 12 t-shirt in stock, where 2 t-shirts purchasing price is $10 and 10 t-shirts purchasing price is $12.
How can I make a product table which will keep track of the purchasing price at different times for the product and also the total stock ?


texts:
I have a json column with a json array in the form of

[{key : "key", value : "value"}, {key : "key2", value : "value2"}]

that I wish to convert to a new form

{"key" : "value", "key2" : "value2"}.

I can use the following query to get rows with key column and value column

SELECT json_array_elements(somejson)->'key', json_array_elements(somejson)->'value' FROM temp;

However I can't seem to come up with a way to convert that to a nice key-value JSON object. Since I'm on 9.3, I can't use json_object or json_build_object either.


texts: Should I copy data in this table when migrating to the other server?


texts: My database has been deleted suddenly on the server how to recover it?

texts:
I'm running an application on windows server that connects to a SQL Server database.

Today, when I opened SQL Server Management Studio, I was surprised the database is not in the list of the databases!

I don't know what's the reason. I searched in the server files but I can't find the database and also in the recycle bin.

I put my database in C:\db\myWeb.mdf and suddenly it's been removed!

I used stealer phoenix to find the database files deleted on disk but unfortunately I didn't find the db then I tried to add new database with the same name of the deleted db but SQL Server Management Studio said that the database MyWeb already exists! That means it's somewhere on the server but I couldn't find it...

Can anyone tell me how to recover the database?


texts:
After a downgrade from SQL 2008 R2 Enterprise to SQL 2008 R2 Standard. SQL Server after some time starts lagging and CPU use is around 95%-100% and I can't log in in "MS SQL server management studio", another application gets time outs from SQL Server.

The log files don't have any error, but all files are filled with this text:

Log file:

https://drive.google.com/file/d/0B8niPQ9GLmG-ZHFuR3hqQUphMUU


texts: can I give permission to insert into view, but not to referred table?

texts:
Here I have a view in public schema referring to the table in private schema:

CREATE OR REPLACE VIEW public.products AS
 SELECT products.id, products.product_id, products.title, products.company_id
   FROM private.products;

ALTER TABLE products
  OWNER TO postgres;
GRANT ALL ON TABLE public.products TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE products TO viewers;

CREATE OR REPLACE RULE "_INSERT" AS
    ON INSERT TO products DO INSTEAD  INSERT INTO private.products (product_id, title, company_id)
  VALUES (new.product_id, new.title, new.company_id);

the whole thing works fine,
but I want the client to only be able to operate in public schema and not in private. Is there a way to setup such permissions?


texts: Do IOTs generate significantly more undo per block for deletes?

texts: IOTs are an index-like structure. When deleting large contiguous chunks of data, how much undo is generated as compared to a similar contiguous delete from a heap?


texts: Oracle Tablespace not using 100% of available space

texts:
About a year ago, we added a new tablespace to our Oracle 11g RAC database and we are now using all of the space and are running out of room occasionally.  I check on the space available with this query:

SELECT
    df.tablespace_name TABLESPACE_NAME,
    totalusedspace USED_MB,
    (df.totalspace - tu.totalusedspace) FREE_MB,
    df.totalspace TOTAL_MB,
    ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) PCT_FREE,
    ROUND(100 * ( (df.MaxMegBytes-df.TotalSpace)/df.MaxMegBytes)) PCT_ROOM
FROM
(SELECT
    tablespace_name,
    ROUND(SUM(bytes) / 1048576) TotalSpace,
    ROUND(SUM(maxbytes) /(1024*1024)) MaxMegBytes
    FROM dba_data_files
    WHERE tablespace_name like 'MY_TABLESPACES%'
    GROUP BY tablespace_name) df,
(SELECT
    ROUND(SUM(bytes)/(1024*1024)) totalusedspace, tablespace_name
    FROM dba_segments
    GROUP BY tablespace_name) tu
WHERE
    df.tablespace_name = tu.tablespace_name and df.totalspace <>0
ORDER BY
    df.tablespace_name;

and it reports:

TABLESPACE_NAME                   USED_MB    FREE_MB   TOTAL_MB   PCT_FREE   PCT_ROOM
------------------------------ ---------- ---------- ---------- ---------- ----------
MOB_BIGDATA3                     47486837    1543307   49030144          3          6

When we were first adding data to this tablespace, the total MB was creeping up and the Percent Free was creeping down, except we are now stuck at 6%.  The Free MB can go to zero and the percent room stays at 6%.

I should add that the other tablespaces have zero percent room.

Could anybody suggest what to do to use this last bit of space?

Update:
It just happens that in my case, there is no need to sum the values since there is only one per tablespace.  With this query:

SELECT
    tablespace_name,
    bytes                   TotalSpace,
    maxbytes                MaxBytes,
    maxbytes -bytes    room,
    100*(maxbytes -bytes)/maxbytes percent_Room
    FROM dba_data_files
    WHERE tablespace_name like 'MY_TABL%';

I just got:

TABLESPACE_NAME              TOTALSPACE             MAXBYTES                ROOM PERCENT_ROOM
------------------ -------------------- -------------------- ------------------- ------------
MY_TABLESPA3         51,411,832,274,944   54,975,581,388,800   3,563,749,113,856      6.48242


texts:
doeas anyone knows how to update a table where column = @parameter WHERE SELECT

Basically I have a table with the following values. Table name is UserAttribute

ID|Name       |Properties|
17|Description|Student   |
17|Contact    |JohnDoe   |
18|Description|Worker    |
18|Contact    |MaryJane  |

I can update the fields with the following query:

UPDATE UserAttribute
SET Properties = 'Charlie'
WHERE Name = 'Contact' AND ID = 17

For the results I need I would need a query something like:

UPDATE UserAttribute
SET Properties = @parameter
WHERE Name = Contact AND ID = (SELECT HouseNo,ID FROM Table2 t1
JOIN UserAttribute t2 ON t1.ID =t2.UserID
WHERE HouseNo = @HouseNo)

SQL doesnt allows the execution of such a query. Any suggestion on how to write the select statement in the WHERE clause?

Thank you!

EDIT 1:
Maybe the question is not the clearest, but its a bit hard to formulate a question when the problem I have is also hard to understand. Therefore I will try to write some kind of pseudo-code, hoping that it will make it a bit more clear.

UPDATE table
SET Column = 'value'
WHERE Column2 = value AND column 3 =(SELECT column from other tables)


texts: Single row to multiple rows with column combination

texts:
I have a table like this:

╔═════╦═══════╦════╦════╦═════╦═════╦═════╦═════╦════╦════╗
║ ZIP ║ STATE ║ F  ║ C  ║ F1  ║ c1  ║ f2  ║ c2  ║ f3 ║ c3 ║
╠═════╬═══════╬════╬════╬═════╬═════╬═════╬═════╬════╬════╣
║ A   ║ AS    ║ AF ║ AC ║ AF1 ║ AC1 ║ AF2 ║ AC2 ║    ║    ║
║ B   ║ BS    ║ BF ║ BC ║ BF1 ║ BC1 ║     ║     ║    ║    ║
╚═════╩═══════╩════╩════╩═════╩═════╩═════╩═════╩════╩════╝

The output I am looking for is:

╔═════╦═══════╦═════╦═════╗
║ ZIP ║ STATE ║  F  ║  C  ║
╠═════╬═══════╬═════╬═════╣
║ A   ║ AS    ║ AF  ║ AC  ║
║ A   ║ AS    ║ AF1 ║ AC1 ║
║ A   ║ AS    ║ AF2 ║ AC2 ║
║ B   ║ BS    ║ BF  ║ BC  ║
║ B   ║ BS    ║ BF1 ║ BC1 ║
╚═════╩═══════╩═════╩═════╝

How can I achieve this?


texts: TSQL Script Execution Through Power shell, Special Characters Rendering Problem

texts:
We have a simple T-SQL script file (.sql), which we execute through Powershell. Everything works fine, except the script portion, where we are using some special characters as string (example: 'ô' ).

The same script works fine, if I just execute it through SQL Server Management Studio, but through Powershell it's not rendering these special characters properly.

Here how we access the script file to execute on all databases on instance

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
invoke-Sqlcmd -InputFile "C:\DailyPatch\run.sql" | Out-File -filePath "C:\DailyPatch\dbname.txt"
$text=Get-Content C:\DailyPatch\dbname.txt
$a = $text.length
$a=$a-3

$logFile = 'C:\DailyPatch\dblog.txt'

for ($i=3 ; $i -le $a ;$i++)
{
    write-host $text[$i]
    $error.clear()

    Invoke-sqlcmd -inputfile "C:\DailyPatch\Script4AllDBs.sql"-database $text[$i]-OutputSqlErrors $true|Out-File -filePath "C:\DailyPatch\dbname.txt"

    foreach ($rr in $error) {
        $text[$i] >> $logFile
        $rr >> $logFile
    }
}

How to set collation in Powershell to avoid such kind of issues?

ps. Unicode thing is already handled on script side

Added: Special characters are being changed to � (Replacement Character)


texts:
This code below is creating a primary key on table User, but Im not understanding the second ADD KEY (C_NATIONKEY), do you know what is doing? Its something about foreign keys?

ALTER TABLE USER
ADD PRIMARY KEY (C_USRKEY),
ADD KEY (C_NATIONKEY);


texts: can I get a list of all parameters and their values while inside my stored procedure?

texts:
I have a number of Oracle stored procedures whose activity I want to be able to audit, so I am having them INSERT some data into an auditing table.  It would be swell if I could list the variables and their values as well.

Is it possible to get this list in an automated way?  I am already using $$PLSQL_UNIT to get my procedure name.


texts: How necessary are primary and foreign keys when I truncate all the tables in my database daily?

texts:
I'm fairly inexperienced at database design, so I am trying to plan my database beforehand as much as possible. This database is to hold information about a VMware environment.

The database has 9 tables currently. Currently the idea is to truncate most of the tables daily and then use LOAD DATA LOCAL INFILE to populate the tables from CSVs scraped from each VMware vCenter. I'm using auto-increment columns for primary keys, though I could use composite keys consisting of the object's ID and the vCenter name (the object's ID is only unique per vCenter) if I were more confident in my database knowledge.

Since most of the tables will be truncated daily, is there any benefit in using foreign keys rather than just using WHERE logic? Are there other questions I should be asking that I might not know to ask?


texts: SSIS: Conditional operator in expression builder ignores previous text

texts:
Consider the following simple expression in SQL Server SSIS, where a variable is built with the following expression containing a conditional operator:

"Preamble
"
+
@[$Package::myParameter] == ""  ? "" : "Middle Text"
+
"
The End"

The logic is simple. If myParameter has a value, the "Middle Text" bit will be shown. If there is no value, then it will show nothing. While the conditional seems to work fine, I noticed that any text/expression I have prior to the operator is simply ignored. In this case, the "Preamble" text is never shown regardless if the boolean check gets to TRUE or FALSE.

Any ideas?


texts: Can I recover a SQL database from Standby/Read-Only without a standby file?

texts:
Is there any way to recover a SQL database from Standby/Read-Only mode if the "standby file" is missing?

(I see some references calling this a TUF file in a log-shipping scenario, but in my case, it was just a manual restore and the standby file had a BAK extension.)

Doing a "RESTORE DATABASE foo WITH RECOVERY" results in a fatal error:

During startup of warm standby database 'foo', its standby file
('path\filename.bak') was inaccessible to the RESTORE statement.
The operating system error was '5(Access is denied.)'. Diagnose the
operating system error, correct the problem, and retry startup.

The restore was done a while ago, and unfortunately the standby file was in the backup folder and got removed by our folder cleanup routine. No copy of the file exists on any other media. The original backups that were used to restore to this point are also long gone.

Fortunately this isn't a big deal for us (it was a temp restore), but I couldn't find any other useful references out there on this question, other than "don't remove that file".

If it came down to it, I suppose I could script out the entire database, or use SSIS to copy all objects to a new database container, since I have a read-only copy of the db in front of me.

Any way to fix this issue? I certainly don't expect that I'd be able to flip it back to "restoring" and continue doing subsequent restores or anything, I'd just like to force it into normal online mode in its current state, so that I can do things like change permissions, or change the recovery mode, or take a new full backup or something.


texts:
I have an exact same myisam table on master and slave. The indexes are not being used on Master server.

mysql> explain select count(date_time) from ox_data_summary_ad_hourly where  date_time = '2012-03-08 00:00:00' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ox_data_summary_ad_hourly
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 32212292
        Extra: Using where
1 row in set (0.00 sec)

The explain plan on the slave:

mysql> explain select count(date_time) from ox_data_summary_ad_hourly where  date_time = '2012-03-08 00:00:00' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ox_data_summary_ad_hourly
         type: ref
possible_keys: ox_data_summary_ad_hourly_date_time
          key: ox_data_summary_ad_hourly_date_time
      key_len: 8
          ref: const
         rows: 156769
        Extra: Using index
1 row in set (0.00 sec)

The master is 5.0 while slave is on 5.5 version if that matters.

Does it mean the index file on master is corrupt?


texts: Error creating foreign key from MySQL Workbench

texts:
I'm trying to synchronize schema changes from MySQL Workbench to my database.  I'm getting the following error when it tries to create a foreign key:

Executing SQL script in server
ERROR: Error 1005: Can't create table 'tomato.#sql-2730_1b8' (errno: 121)

Here's the statement it's trying to execute:

ALTER TABLE `tomato`.`ing_allergy_ingredient`
ADD CONSTRAINT `fk_ai_allergy`
FOREIGN KEY (`allergy_id` )
REFERENCES `tomato`.`ing_allergy` (`allergy_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION

Any ideas what this error means?


texts: MySQL / MariaDB : Many indexes vs. One multi-column index

texts:
I need to optimize my MariaDB tables index.
I've read a lot of very interesting things on StackExchange, but I'm not sure to have the answer for my case.

In my case, I have a table named TableA which contains more than 40 millions records (stats purposes) with these columns :

ID (PK)
A (INT)
B (INT)
C (INT)
D (INT)

I make different queries on this table, with many of the 4 columns in the WHERE clause.
For example, I can have these columns in WHERE clause of the same query :

A, B and C
A and B
A and D
A, C and D
B and D
B, C and D
C and D
only A
only B
etc.

I can encounter every case.
What would be the best way to index that table which count more than 40 millions records ?
Should I make only one INDEX with all 4 columns ? Or 4 different indexes ?
This table is used everytime in my app, and contains sensitive data...(for stats purposes).

Thank you for your help !

Joffrey


texts: Oracle 12c upgrade - backwards compatible with 11g?

texts:
Are there any application code changes required for SQL or PL/SQL to continue to work in 12c, or is it backwards compatible with 11g?  Are there any queries that would work in 11g that break in 12g?

I remember having to change some queries when upgrading 10g to 11g, but it turned out to be a bug in 10g that was fixed in 11g - the queries should have failed in 10g to begin with.  (See: https://stackoverflow.com/questions/4269205/why-doesnt-oracle-raise-ora-00918-column-ambiguously-defined-for-this-query)


texts: Very common mysql query sometimes does not use index

texts:
As far as I can tell, for some reason a small fraction of very common queries fail to use indexes. If all such queries failed to use indexes, the server would crash within 1 minute hence only a few such queries behave like this.

How should I prevent these occasional slow queries which in vast majority of cases work fine?

My create tables are:

CREATE TABLE msgs (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  sender text NOT NULL,
  receiver text NOT NULL,
  cont blob NOT NULL,
  img text NOT NULL,
  orient text NOT NULL,
  d_t datetime NOT NULL,
  convo text NOT NULL,
  u_code text NOT NULL,
  viewed datetime NOT NULL,
  stat int(11) NOT NULL,
  device text NOT NULL,
  addr text NOT NULL,
  PRIMARY KEY (id),
  KEY msg_u_code (`u_code`(24)),
  KEY receiver (`receiver`(24)),
  KEY sender (`sender`(24)),
  KEY img (`img`(28)),
  KEY convo (`convo`(49))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE usrs (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  usr_name text NOT NULL,
  img text NOT NULL,
  orient text NOT NULL,
  `password` text NOT NULL,
  u_code text NOT NULL,
  d_t datetime NOT NULL,
  stat int(11) NOT NULL,
  device text NOT NULL,
  addr text NOT NULL,
  PRIMARY KEY (id),
  KEY img (`img`(28)),
  KEY usr_code (`u_code`(24))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

And my slow query log entry is:

 # Time: 171115  6:26:37
 # User@Host: xxx[xxx] @ localhost []
 # Thread_id: 25524888  Schema: xxx  QC_hit: No
 # Query_time: 32.423430  Lock_time: 0.000425  Rows_sent: 1  Rows_examined: 30998008
 # Rows_affected: 0
 use xxx;
 SET timestamp=1510723597;
 select msg_cont, msg_u_code, msg_d_t, msg_viewed, usr_u_code, usr_name from
        (select
            msgs.id as msg_id,
            msgs.cont as msg_cont,
            msgs.u_code as msg_u_code,
            msgs.d_t as msg_d_t,
            msgs.viewed as msg_viewed,
            usrs.u_code as usr_u_code,
            usrs.usr_name as usr_name
            from msgs
            left join usrs on msgs.sender = usrs.u_code
    where  msgs.convo = 'aaaaaaaaaabfbaghdgcigfid_aaaaaaaaaabeiaccjfhjfach'
      and  (msgs.sender = 'aaaaaaaaaabfbaghdgcigfid'
              or  msgs.receiver = 'aaaaaaaaaabfbaghdgcigfid'
           )
      and  msgs.stat = '1'
      and  usrs.stat = '1'
      and  usrs.u_code not in('aaaaaaaaaabfaagfbgggiejh',
                              'aaaaaaaaaabfabgbjdfjigbd',
                ...... !!!!![here go 400 more usr_u_codes]!!!!!
                          )
      and  msgs.id > 30997997
        ) a order by msg_id asc;


texts: MongoDB in ReplicaSet databases showing different sizes and objects

texts:
I have a MongoDB in a ReplicaSet with 3 members, and last week the primary node got corrupted somehow and stopped. I had to resync the data from the other nodes.

It has been a week but I was checking this morning and the databases looks different somehow between the primary (that crashed) and the two secondary that were used for resync the data.

  PRIMARY (after crash)

  Secondary

I've seen other questions similar to this one but at the end the databases had the same amount of objects, however, my scenario it isn't the same.

Any idea? Or does it look ok?


texts: SQL query for getting Count of distinct items along with Total

texts:
I'm using MySql (hosted locally at xampp) to store sale information

I want the output something like this

Is there a single query that gets me the output in this format or should I write multiple queries to get the intended result?
I'm very new to SQL and I don't know how to implement it or what to look for in the internet.


texts: How to copy selected table data from sql server to mysql on a daily basis?

texts:
I am having a retail pos application with MSSQL database. Now I need to transfer selected table data from SQL server to MYSQL database on a daily basis like job.

How to achive this?


texts: Restoring an old backup to latest MySQL release

texts: Our old database is MySQL 5.1 server and is four years old. If I uninstall MySQL 5.1 Server and install the latest release and restore the backup, will I gain performance benefits? I guess the new version release says that there are many performance benefits.


texts: Where is SQL Server merge replication snapshot synchronization log location?

texts: I have an issue with SQL Server 15 -> 13 merge replication process. It got stuck locking MSsnapshotdeliveryprogress table. So I have to look into logs to be able to determine what is causing this, but hey! where are logs located?


texts: Execute Stored Procedure with Account that has only read access

texts:
I created a stored procedure that has TRUNCATE and INSERT statements in it that I use for an SSRS report.

I typically only use an account that is a member of db_datareader for SSRS reports, however this stored procedure will not execute with an account that only has those rights.   The following error is reported when attempting to execute the stored procedure:

  Cannot find the object "table_name" because it does not exist or you do not  have permissions.

Do I need to use an account who has db_datawriter members to execute this SP or is there a way for me to have my db_datareader account execute it?


texts: Postgres streaming replication to a different schema on another server?

texts:
We've got a system with about a dozen Postgres 9.3 databases representing a dozen different applications. In order to do reporting across systems, we have a script that dumps each database and loads it back into a single database (on a second server) under a schema with the same name as the original database.

So for example, we've got the following databases on the main server:

A
B
C

and on the reporting server, we'll load those into a single database across multiple schemas, which ends up looking like this:

reporting.A
reporting.B
reporting.C

Anyway, the script that moves the data takes way too long to run and I was wondering if anyone can suggest a way to use Postgres's replication capability in order to make this process a live, continuous operation?


texts: How can I run a PostgreSQL outside the standard directories on Linux, ie outside /var/run/postgresql etc?

texts:
I need to be able to run PostgreSQL databases outside the standard installation directories, ie run the existing postgres or pg_ctl process pointing to the alternative ports, configuration and data directories and get it running, with permissions etc working okay.

I don't want it to depend on the existing package based installation setup's structures in /etc/postgresql and /var/run/postgresql and the postgres user, but use their executables if possible.

I will not be using initdb much. The data will be unzipped from other installations. The only thing required is database compatibility with the existing package based installations, which I may be able to install if they are absent. Using executables of my own will be the last resort, due to the possibility of unexpected local dependencies.

Windows appears well covered in that respect with some downloads at EnterpriseDB but there doesn't seem so much available for Linux.


texts: Pentaho Kettle (Data Intergration) MongoDB Aggregation

texts:
I'm using kettle v5.2 which support the aggregation pipleline in MongoDB
when using MongoDB input the query works for small data set but I need to use option
allowDiskUse to the query can't figure how to add this in pentaho
while I tested this option in mongo shell and it's working as expected

http://docs.mongodb.org/manual/reference/method/db.collection.aggregate/

http://wiki.pentaho.com/display/EAI/MongoDB+Input#MongoDBInput-queryaggpipeline


texts: Enabling TDE on Postgres for an existing database

texts:
I’m trying to review my options for enabling Transparent Data Encryption on Postgres for an existing database.
The main issue is that we want to enable this but already have an existing database with tables and data.
I can find resources to enable it from scratch but not with existing data.


texts:
Using SQLite 3, I have some view columns which are defined with an expression, and so have no type affinity. Is it possible to define them in a way that forces them to have a certain type affinity?

e.g. if I have a tables t1, t2 and a view definition

create view v as select coalesce(t1.c1, t2.c1) c1 from t1, t2 where X;

is there some way of giving v1.c1 a type affinity? I've tried casting it

select cast(coalesce(t1.c1, t2.c1) as INTEGER) c1

but that doesn't seem to work.


texts:
I have been battling with this statement:

ALTER PROCEDURE [dbo].[transact_image_update]
-- Add the parameters for the stored procedure here
@transact_recordID_int int,
@image1_bin image,
@image2_bin image,
@transact_referenceNo_str nvarchar(25),
@userID_last uniqueidentifier,
@tableName nvarchar(50)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

DECLARE @sqlUpdt01 nvarchar(4000)
SET @sqlUpdt01  = '
        Update [dbo].[' + @tableName + '] SET [image1_bin] = '+ CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), @image1_bin), 2)
        + ', [image2_bin] = '+ CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), @image2_bin), 2)
        +', [userID_last] = '''+ convert(nvarchar(4000),@userID_last)
        + ''' WHERE (transact_recordID_int = '+convert(varchar,@transact_recordID_int) +')
            AND ([transact_referenceNo_str] = ''' +convert(varchar, @transact_referenceNo_str)
             +''' )
             AND (locked_bol = 0)
             '
exec sp_executesql @sqlUpdt01

Basically, I have many DB tables with similar schema but different names (for types of transactions) and would like this ONE procedure to make the update given the table name as argument.
This script compiles successfully but execution cannot update the image field.
Is there a conversion I'm missing?

Please help.


texts: How to get names form 3 different tables


texts: How to use a relative path in sqlcmd script

texts:
Can someone tell me how to swap this absolute path with a relative one for my sqlcmd upgrade script?

-- how can I get rid of the following absolute path variable from my script?
:SETVAR ScriptDir "C:\absolutePath\"

:SETVAR DeployLog "upgradeDb.out.log"
:SETVAR ErrorLog "upgradeDb.err.log"
:Error $(ScriptDir)$(ErrorLog)
:OUT $(ScriptDir)$(DeployLog)

:ON ERROR EXIT
print ' run scripts a and b'
:r $(ScriptDir)"a.sql"
:r $(ScriptDir)"b.sql"

PRINT 'finished'
GO

:EXIT
print 'problem...'
GO

Originally posted (in the wrong place) here: https://softwareengineering.stackexchange.com/questions/318033/how-to-use-a-relative-path-in-sqlcmd-script


texts:
I am having an issue with the syspolicy_purge_history SQL Agent job on a 2008 SQL Server system.  The server is using a non default port number and is in a cluster.  Before it was put in a cluster, it was a stand alone box and the job ran with no issues with the non default port number.  It is failing at step 3, which is

(Get-Item SQLSERVER:\SQLPolicy\VirtualServer\DEFAULT).EraseSystemHealthPhantomRecords()

I have already addressed an issue of using the computer node name instead of the virtual server name as we have other servers in clusters.

The only difference with this setup is that it is not using the default port of 1433.  The server is already  set to receive remote connections.

Here is the error.

The job script encountered the following errors.
These errors did not stop the script:
    A job step received an error at line 1 in a PowerShell script.
    The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\VirtualServer\DEFAULT).EraseSystemHealthPhantomRecords()'.
    Correct the script and reschedule the job.
    The error information returned by PowerShell is: 'SQL Server PowerShell provider error: Could not connect to 'VirtualServer\DEFAULT'. [Failed to connect to server VirtualServer. --> A network-related or instance-specific error occurred while establishing a connection to SQL Server.
    The server was not found or was not accessible.
    Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]


texts: Installing 32bit SSRS on 64bit Windows/IIS

texts:
I've inherited a situation where I have SQL Server 2005 Standard 32bit running on Windows Server 2008 R2 64bit.

When I come to install SSRS components I'm warned that "Failed to find the ASP.Net Version Registration with Microsoft Internet Information Services (IIS)."

In IIS 7 and above, each application pool can run in 32bit mode. If I change all application pools into 32bit mode will this solve the problem?

Are the better alternative solutions?

Thanks in advance.


texts:
Table A is linked to Table B , and Table B is linked to Table C using primary and foreign keys
My questions:

Should i link Table A to Table C too?? Or Table B can act as intermediate link between both??
If i linked between Table A to Table C will that affect on DB perfomance and data quiring positively or negatively??


texts: AutoFK index disabled during foreign key creation in pgadmin

texts: Trying to create a foreign key, but doesn't allow me to save the changes as it shows the error "Please specify covering index name." Cant specify the covering index unless i toggle the auto fk index switch. Which is disabled. I was able to create two other foreign keys with no issue.


texts: Database design: Two 1 to many relationships to the same table

texts:
I have to model a situation where I have a table Chequing_Account (which contains budget, iban number and other details of the account) which has to be related to two different tables Person and Corporation which both can have 0, 1 or many chequing accounts.

In other words I have two 1-to-many relationships with the same table Chequing account

I would like to hear solutions for this problem which respect the normalization requirements.
Most solutions I have heard around are:

1) find a common entity of which both Person and Corporation belong and create a link table between this and the Chequing_Account table, this is not possible in my case and even if it were I want to solve the general problem and not this specific instance.

2) Create two link tables PersonToChequingAccount and CorporationToChequingAccount which relate the two entities with the Chequing Accounts. However I don't want two Persons to have the same chequing account, and I don't want to have a natural person and a Corporation to share a chequing account! see this image

3) Create two foreign keys in Chequing Account which point to Corporation and Natural Person, however I would thus enforce that a Person and a Company can have many chequing accounts however I would have to manually ensure that for each ChequingAccount row not both relations point to Corporation and Natural person because a checquing account is either of a corporation or of a Natural Person. see this image

Is there any other cleaner solution to this problem?


texts: MySQL overhead of calling a Stored Procedure

texts:
Within Java, we access MySQL (v5.5.40) via JDBC -- a prepare statement, setting parameters then calling the stored procedure. It appears that every execute() has the overhead of 4 meta-calls (per MySQL query log) preceding the actual SP call:

SELECT name, type, comment FROM mysql.proc WHERE name like...
SHOW FUNCTION STATUS LIKE 'sp_one'
SHOW PROCEDURE STATUS LIKE 'sp_one'
SHOW CREATE PROCEDURE `db-name`.`sp_one`
CALL sp_one()

Is this the expected behavior on the server side? It just doesn't seem very efficient.

Here is the example of how we use the API:

  conn = DriverManager.getConnection(..);

  stmt = conn.prepareCall("{call sp_one(?,?)}");

  stmt.setString(1, "a");
  stmt.setString(2, "b");

  stmt.execute();

Please note that the following is true:

CallableStatement.getMetadata() is not used
Parameters are accessed by index, not by name

Thanks!


texts:
I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows:

autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum
autovacuum_vacuum_cost_limit = 10000 #Max value
autovacuum_vacuum_threshold = 50 #Default value
autovacuum_vacuum_scale_factor = 0.2 #Default value

I notice that the auto vacuum only kicks in when the database is not under load, so I get into situations where there are far more dead tuples than live tuples. See the attached screenshot for an example. One of the tables has 23 live tuples but 16845 dead tuples awaiting vacuum. That's insane!

Auto vacuum kicks in when the test run finishes and the database server is idle, which is not what I want as I would like auto vacuum to kick in whenever the number of dead tuples exceeds 20% live tuples + 50, as the database has been configured. Auto vacuum when the server is idle is useless to me, as the production server is expected to hit 1000s of updates / sec for a sustained period which is why I need auto vacuum to run even when the server is under load.

Is there anything that I'm missing? How do I force auto vacuum to run while the server is under heavy load?

Update

Could this be a locking issue? The tables in question are summary tables which are populated via an after insert trigger. These tables are locked in SHARE ROW EXCLUSIVE mode to prevent concurrent writes to the same row.


texts: Help with CTE Aggregating Children Recursively

texts:
I'm trying to create a query to calculate the "Available to Sell" qty of all items in inventory. In this case, an item can have current available inventory, but could also be a kit, which is comprised of raw materials that can be assembled to form that finished good. So the available to sell is the current available Finished Good (FG) qty plus the minimum of the components that can be made into the FG.

Example:

Say we are selling a laptop kit that is comprised of a laptop and a carrying bag. If we have 2 kits already made, and 6 laptops and 3 carrying bags, our available to sell of these kits is 2 + 3 = 5 total. The carrying bags are the limiting factor in this case. Even though we have 6 laptops, we can only make 3 more kits because of the bags limiting us.

I've gotten this far and the calculations work from the lowest level 2 up to level 1, but level 0 is not correct. So in this case, the calculation for the laptop kit is correct (11 on hand + we can make 4 more = 15 avail to sell). But the top level Laptop & Bag Kit is not correct. The least Avail to Sell of the direct children of the top level (Laptop & Bag Kit) is 15 + 3 of that kit is on hand = 18, not 14.

I'm thinking maybe I need to add a second recursive CTE instead of the left join I have in the final select?

SQL Fiddle

CREATE TABLE Item (
  Id INT,
  ParentId INT,
  DisplaySeq INT,
  DisplayText VARCHAR(30),
  OnHandQty INT
  );

INSERT INTO Item (Id, ParentId, DisplaySeq, DisplayText, OnHandQty) VALUES
(9, NULL, 0, 'Laptop & Bag Kit', 3),
(8, 9, 5, 'Laptop Kit', 11),
(7, 8, 10, 'Laptop', 5),
(6, 8, 15, 'Power Supply', 4),
(26, 9, 20, 'Bag', 23)
;

;WITH items AS (
  SELECT
    Id
    , 0 as ParentId
    , Id as RootId
    , 0 AS Level
    , CAST(DisplaySeq AS VARCHAR(255)) AS Path
    , CAST('---' AS varchar(100)) AS LVL
    , CAST(DisplayText as VARCHAR(255)) as DisplayText
    , OnHandQty
  FROM Item
  WHERE ParentId IS NULL

  UNION ALL

  SELECT
    child.Id
    , child.ParentId
    , parent.RootId
    , Level + 1
    , CAST(parent.Path + '.' + CAST(child.DisplaySeq AS VARCHAR(255)) AS VARCHAR(255)) AS Path
    , CAST('---' + parent.LVL AS varchar(100)) AS LVL
    , CAST(parent.LVL + child.DisplayText as VARCHAR(255)) as DisplayText
    , child.OnHandQty
  FROM
    Item child
      INNER JOIN items parent
      ON parent.Id = child.ParentId
  )

SELECT
  t.Path
  , t.RootId
  , t.Id
  , t.ParentId
  , t.Level
  , t.DisplayText
  , t.OnHandQty
  , COALESCE(s.MaxCanMake, t.OnHandQty) as MaxCanMake
  , t.OnHandQty + COALESCE(s.MaxCanMake, 0) as AvailToSell
FROM
  items t
    left join (
        Select
          ParentId,
          MIN(OnHandQty) as MaxCanMake
        FROM items
        GROUP BY ParentId
      ) as s
      ON t.Id = s.ParentId

ORDER BY t.Path


texts: How to check SqlSetPos compatibility on database?

texts: I have a database tool with the possibility to suppress the use of SqlSetPos. According to the documentation, some databases are not fully compatible with this function. How can I check if my database is compatible with this function or not?


texts: How to increase index row maximum size in Postgresql?

texts:
I want to perform KNN on 512-dimensional vector using the solution suggested here.

I couldn't create table with 512-d cube array. Solved by changing the CUBE_MAX_DIM in the source code.
Couldn't create index on table with dimensions > 200. Set block_size=32. Now works with dimensionality<=510.
Got new error after changing block_size and trying to create index on table with 512-d vectors ERROR:  index row requires 8208 bytes, maximum size is 8191

Is it possible to increase this limit?


texts: I have a huge  dump.sql Mysql database of about 3GB that has the structure and the data


texts: having problem in insering date in mysql database

texts:
On inserting date in table date is being displayed as 0000-00-00

$content = file_get_contents("http://api.themoviedb.org/3/movie/$id?api_key=c1259239b41c48480d727f85c677fbf6");

$moviedetailsArray = json_decode($content,true);

$budget = $moviedetailsArray['budget'];
$rlsdate = $moviedetailsArray['release_date'];

$query = "UPDATE `latestmoviesdetails` SET `budget` = $budget,`releasedate` = $rlsdate WHERE movieid = $id";

mysqli_query($link,$query);


texts: How do I rebuild single full text catalog (or why is "rebuild" full text catalog option disabled)

texts:
In SSMS. I have Full Text Catalog defined. If I select "Full Text Catalogs" folder - there is an option "Rebuild All" and it is enabled.
But if I select individual FTC - "Rebuild" option (in context menu) is disabled.

Question Why is it disabled? and how do I enable it?


texts: how to do a count on a grouped by value in Postgres 9.4

texts:
I have a list of orders and would like to output a list by day of when they were created at of (1) total orders and (2)orders that have a fulfillment_status of 'paid'

 id | fulfillment_status | created_at (timestamp witout time zone)
1                           2015-10-17 02:23:14.882131
2      paid                 2015-10-18 02:23:14.882131
3                           2015-10-17 02:23:14.882131

so that the output is:

date         total_orders total_paid_orders
2015-10-17     1             0
2015-10-18     2             1

I get this far:

 select count(*) from orders group by created_at ???

but ....


texts:
I'm using postgresql 11 and I have a JSONB column which looks like
{ "obj": {"nested_obj": {"firstname": "john", "surname":"smith"} } }

There's a GiN index on col and performance is good when using a query such as:
select col
from tab
where col -> 'obj' -> 'nested_obj' @> '{"surname":"smith"}'

But now I need to use a LIKE statement so my query looks like:
select col
from tab
where col -> 'obj' -> 'nested_obj' ->> 'surname' LIKE 'smit%'

and performance is terrible because the index isn't being used.
Is there a better way to write a query to use a LIKE statement on a JSONB nested object, or should I change the way I index?


texts: Header is added when exporting column of type image with BCP to files

texts:
I export a column of type image to files using this command:

EXEC master..xp_cmdshell
'BCP "SELECT file_name FROM file_table WHERE id = 11 "
queryout "C:\bcpdir\test.xml" -T -N'

In SQL Management Studio, I can see the hex value of the field starts with 0x3C3F786D6C which is correct for an XML file (<?xml).

However, the command above seem to add an 4 byte header to the file. How can I use BCP without this header?


texts: Postgres query column with comma-delimited strings for array of strings

texts:
I have a table that has data that looks like this:

id  oe
--  ---------------------------------------------------
 1  04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
 2  64526915083,64526953474
 3  04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
 4  F4DH8C607AA,F4DHAA
 5  F4DH8C607AA,F4DHAA
 6  04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
 7  64526915083,64526953474
 8  F4DH8C607AA,F4DHAA
 9  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
10  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
11  04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
12  64526915083,64526953474
13  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
14  F4DH8C607AA,F4DHAA
15  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA

How can I write a query where I can pass an array of values and return rows where that column contains any of the values in the array?

This works because I get another value from a lookup table and I'd rather not do it that way as there is no index on the hol column in the pubnet table.
[please don't suggest adding one as I cannot modify DB]

SELECT * FROM pubnet
where hol in (select hol from id2hol where id in ('F3DH19860AA', 'F4DHAA'))

This would be the result set:

id  oe
--  ---------------------------------------------------
 4  F4DH8C607AA,F4DHAA
 5  F4DH8C607AA,F4DHAA
 8  F4DH8C607AA,F4DHAA
 9  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
10  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
13  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
14  F4DH8C607AA,F4DHAA
15  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA


texts: Mysql slave status is completely perfect but data is not reflecting

texts:
I have setup replication using mysql utilities. Slave can able to read and execute master's position and binlog files perfectly. seconds_behind_master is also 0. But data is not reflecting into slave. what could be the reason ?
Anybody help e please


texts: AlwaysOn AG between 2 sql server failover clusters

texts:
I have 2 windows failover cluster and each of them consist of 2 nodes (virtual machines). And on each cluster has its own ms sql role which uses seperate shared discs :
1. wincluster1 (wsfcnode1 and wsfcnode2) - sqlclust1
2. wincluster2 (wsfcnode3 and wsfcnode4) - sqlclust2

For example on the sqlclust1 side I have Testdb database.

Question : Can I setup alwayson available group for this database between sql failover clusters?


texts: Select Permissions Across Multiple Schemas Within the Same Database

texts:
I have been asked by a client to implement some views for reporting purposes, the views will be accessed via PowerBI, Excel and SSRS. The designated user will only have access to the view and no underlying tables must be available to the user.

The problem I have encountered in that the SQL within the view touches 3 different schemas (All within the same database):

Pupil
Provider
Security

The view is:

CREATE VIEW dbo.vTestPermissions
AS
SELECT a.Column1,
       b.Column1,
       c.Column1
FROM Pupil.Table1 a
JOIN Provider.Table2 b ON a.Column1 = b.Column1
JOIN Security.Table3 c ON a.Column1 = c.Column1

The owners of the Tables/View are as follows:

Pupil.Table1 - Owner Pupil
Provider.Table2 - Owner Provider
Security.Table3 - Owner Security
vTestPermissions - Owner dbo

When I select from the view I get an error:

  The SELECT permission was denied on the object 'table3', database
  'TEST', schema 'Security'

I have tried giving SELECT permission with and without the GRANT option to the schema, and the tables, but that makes the underlying tables available to the user.

Any help on this will be greatly appreciated.


texts: When mysql innodb undo log write and fsync to disk?

texts: Before transaction commit , every dml operation will modify innodb page which will flush to disk and generate undo log , so when the undo log write and fsync to disk to ensure rollback ?


texts: How to use variables with RESTORE FILELISTONLY

texts:
Storing the result of RESTORE FILELISTONLY in a temporary table called #filepaths.

IF object_id('sp_restore') IS NOT NULL
    drop procedure sp_restore
    go

CREATE PROCEDURE sp_restore AS
BEGIN
    RESTORE FILELISTONLY FROM DISK = 'Z:\BACKUPS\my_database_backup.bak'
END
GO

insert into #filepaths
exec sp_restore

How can I get 'Z:\BACKUPS\my_database_backup.bak' into a variable? So that the script looks similar to this.

DECLARE @BACKUP_PATH as nvarchar(max) = 'Z:\BACKUPS\my_database_backup.bak'

IF object_id('sp_restore') IS NOT NULL
    drop procedure sp_restore
    go

CREATE PROCEDURE sp_restore AS
BEGIN
    RESTORE FILELISTONLY FROM DISK = @BACKUP_PATH
END
GO

insert into #filepaths
exec sp_restore

Thanks,
Craig


texts: MySQL - Delete row that has a foreign key constraint which reference to itself

texts:
I have a table in which I store all the forum messages posted by the users on my website. The messages hierarchy strucrue is implement using a Nested set model.

The following is a simplified structure of the table:

Id (PRIMARY KEY)
Owner_Id (FOREIGN KEY REFERENCES TO Id)
Parent_Id (FOREIGN KEY REFERENCES TO Id)
nleft
nright
nlevel

Now, the table is looking something like this:

+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| Id      | Owner_Id      | Parent_Id      | nleft      | nright      | nlevel      |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| 1       | 1             | NULL           | 1          | 8           | 1           |
| 2       | 1             | 1              | 2          | 5           | 2           |
| 3       | 1             | 2              | 3          | 4           | 3           |
| 4       | 1             | 1              | 6          | 7           | 2           |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +

Note that the first row is the root message, and the tree of this post can be displayed as:

-- SELECT * FROM forumTbl WHERE Owner_Id = 1 ORDER BY nleft;

MESSAGE (Id = 1)
    MESSAGE (Id = 2)
        Message (Id = 3)
    Message (Id = 4)

My problem occurs when I try to delete all the rows under the same Owner_Id in a single query. Example:

DELETE FROM forumTbl WHERE Owner_Id = 1 ORDER BY nright;

The above query fails with the following error:

  Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (forumTbl, CONSTRAINT Owner_Id_frgn FOREIGN KEY (Owner_Id) REFERENCES forumTbl (Id) ON DELETE NO ACTION ON UPDATE NO ACTION)

The reason is that the first row, which is the root node (Id=1), also has the same value in its Owner_Id field (Owner_Id=1), and it causing the query to fail due to the foreign key constraint.

My question is: How can I prevent this foreign key constraint circularity and delete a row that reference to itself? Is there a way to do it without first having to update the the Owner_Id of the root row to NULL?

I created a demo of this scenario: http://sqlfiddle.com/#!9/fd1b1

Thank you.


texts: Calculating time spent on query cache lock

texts:
How can I calculate the total time my MySQL database is spending  Waiting for query cache lock    ?

I've found two specific very simple tables that adding a SQL_NO_CACHE drastically improved the SELECT queries on them, and I want to analyze other tables in a more methodological approach.

My MySQL version is 5.5.15 and the InnoDB engine is 1.1.8
Query cache size is 536870912

Relevant Variables:


texts:
I have a database that is currently shipping log files to a standby server.

I ship the log from the primary to the standby every 5 minutes

Every 15 minutes, a restore Agent Job runs on the standby to import the transactions into the standby database.

I want to be sure I understand how to bring the standby server online in case the primary fails BEFORE I am in that scenario. Everything I have been reading online tells me that I simply need to run restore database }}dbname{{ with recovery to accomplish that.

Questions I have are:

1) Is that the correct code to run?
2) Does that automatically
restore any transaction logs  that have shipped since the last
restore Agent Job was ran?
3) Once the restore Agent Job runs, do I
need to keep the .trn files?

I have failed to locate any resources that fully explain the actual switch over process. Lots out there on how to set up log shipping though.

My ultimate goal is to get to a situation where I am doing a full backup each night, shipping it to the standby server and restoring it, deleting all the .trn files prior to the full back up,  then do log shipping each 5 minutes, restoring them at 15 minute intervals.


texts:
Context: For our testing environment, we were migrating from on-premise SQL Server 2017 to Docker Linux SQL 2017 container. There is a large number of TestScripts in windows that try to connect to SQLServer in windows via trusted authentication. This Windows Authentication mode doesn't work with Docker Linux SqlServer as only SqlAuth is supported.
Question:
Is there a reverse-proxy that I can use to accept TrustedConnection from TestScripts requiring TrustedAuthentication and redirect the requests to Docker Linux SqlDb that accepts only SqlAuth?

It is ok to pre-define SqlAuth for Docker Linux SqlDb in reverse-proxy.
It is ok to skip or bypass or fake-validate any form of authentication from clients. Our test scenarios are mostly positive, and we don't need to validate Sql login failures.
It is ok to run reverse-proxy in windows platform, for windows auth capability.

This was to avoid the work of re-writing/updating auth on large number of TestScripts (which are legacy). This is test code, and ok to ignore all the security implications.


texts: Set table name to variable in postgresql

texts:
I am trying to set the tablename dynamically in a postgresql query executed through a jdbc connection. Here is my code:

PreparedStatement query1 = null;
        Connection conn = null;
        //String xmlData = null;

                conn = postgresVoyateDBConnection();
                query1 = conn.prepareStatement("SELECT * FROM ? " +
                                             "WHERE id=? ");

                query1.setString(1, tableName);
                query1.setInt(2, id);

                ResultSet rs1 = query1.executeQuery();

However. when I run this, I get an error like this:

  org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

From my quick research I figured out it is not possible to place table names dynamically like this. Are there any workarounds? What does this $1 mean?


texts:
I have 11g 2-node RAC.
My alert.log contains several ORA-03137 errors:

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_10679.trc  (incident=149362):
ORA-03137: TTC protocol internal error : [12333] [4] [195] [23] [] [] [] []
Fri Jan 17 17:01:56 2014
Sweep [inc][149362]: completed

and incident files are created..

How to solve this error? It is generated almost in every 7-8 minutes.

Please Help.


texts: MS SQL running out of Disk Space - can FileGroups be used to free up some space?

texts:
Problem

We have a Sql Server 2014 with a 1TB attached disk (on Azure) that's running out of disk space. We have about 20GB's left (maybe a few weeks of space). As such, we need to move some data off the CURRENT disk and onto a NEW disk.

Details

Server:

Microsoft SQL Server 2014 - 12.0.2548.0 (X64)
    Jun  8 2015 11:08:03
    Copyright (c) Microsoft Corporation
    Web Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

MS-SQL 2014 is installed onto a classic Azure VM. This VM is in a classic VNET. The storage DISKS are also classic. As such, we couldn't just expand the existing disk. MS-Support said that we need to update all of these, if we wish to leverage the modern Azure Storage to allow disk resizing, using the newer SSD disk, etc. TL;DR; we can't take this offline for hours including all the other subsystems that communicate with the VM via IP address. Now, don't turn this into a flame-fest .. this is what we've been given to work with and will need to fix all of this up later.

So right now, an idea what to try and leverage FILEGROUPS and to move one or more tables into a FILEGROUP and push this FILEGROUP onto another DISK which we have attached.

So the questions here are:

Is this a really really crazy and lame idea, first of all?
If it's crap but OK, then will using FILEGROUPS actually move the data from the CURRENT disk onto the NEW disk (which frees up some disk space on the nearly full CURRENT DISK)?
If this is still possible, does moving these tables mean the data is locked/unavailable ... which means were still back to our initial problem :(
What about logs? Moving this data means the logs just get a copy of this? (we are doing hourly and weekly backups, I believe).

It's ok if the data is small, but here's a quick look at some of our tables...

That first table is massive (with respect to the rest of the data). 750GB ish.

I was thinking of moving maybe the 2nd, 3rd of 4th line, in the result image. Remember how I said the infrastructure is all on OLD classic stuff? This means the HD's are old and slow so copying data could take some time also.

As an example, I just tried to copy the .mdf's (this DB has 1 main mdf and 2 other small ones) from OLD over to NEW disks. that had a quick ETA of 24 hours.

Having the site offline for a few hours is totally acceptable. We can take stuff offline when our customers are asleep. but ... 24 hours .. that hurts. The 24 hour idea was a simple test for:

Create new 2TB Disk (if possible)
turn off sql server.
copy mdf + log files to new disk. (24 hours or so)
point filegroups from old location to new location
start sql server again.

Now, we're open to ideas and I know that stack exchange is not a site for 'opinions' so I'm trying to keep this on target with a suggested answer and to get feedback on it ... but we're open to other solutions to reduce the offline time.

So - can anyone help please?

Update 1

Here's a screen shot of the current FILES for this DB.


texts:
Does the size of the database depend on the amount of data stored in the column or on the type of data stored?

E.g: if I have  Varchar column and I'm just typing like word (Hello) in it, it will take the same size if I write 255 characters in it?


texts: Can't Login to SQL Server 2008 when running single user mode

texts:
I'm following the MS documentation here to migrate logins from one instance of SQL Server to another as part of the process described here. This involves putting the server into single user mode (which I've done) and then running scripts etc.

However, after restarting in single-user mode (with only the SqlServer service running) I am unable to log-in using either my Windows account or sa credentials. I get the error:

Failed to connect to <servername>

Login failed for user 'sa'. Reason: Server is in single user mode.
Only one administrator can connect at this time. (Microsoft SSQL Server, Error: 18461)

After restarting the server normally (ie. multi-user mode) all the logins work fine.

Can anyone explain what is going on and/or how to correct this?


texts: ORA-00959: tablespace does not exist but I can see it in select

texts:
So, I am connected as SYSDBA to Oracle and trying to run this command:

 create user C##demo identified by demopassword;

That ends with User created.

Then I run:

alter user C##demo default tablespace PAVEL_DATA temporary tablespace PAVEL_TEMP;

Which ends in error:

  ORA-00959: tablespace 'PAVEL_DATA' does not exist

But if I run this command:

SELECT TABLESPACE_NAME, STATUS, CONTENTS
FROM USER_TABLESPACES;

I can see the tablespaces:

  TABLESPACE_NAME          STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                 ONLINE    PERMANENT
SYSAUX                 ONLINE    PERMANENT
TEMP                   ONLINE    TEMPORARY
USERS                  ONLINE    PERMANENT
UNDOTBS2               ONLINE    UNDO
PAVEL_DATA             ONLINE    PERMANENT
PAVEL_TEMP             ONLINE    TEMPORARY

So, why am I getting the error tablespace does not exist when it is created?

EDIT

Continued according the comments, this is what I see when I run select tablespace_name, con_id from cdb_tablespaces :

TABLESPACE_NAME            CON_ID
------------------------------ ----------
PAVEL_DATA              1
PAVEL_TEMP              1
SYSTEM                  4
SYSAUX                  4
TEMP                    4
USERS                   4

TABLESPACE_NAME            CON_ID
------------------------------ ----------
SYSTEM                  3
SYSAUX                  3
TEMP                    3
USERS                   3
EXAMPLE                 3
PAVEL_TEMP              3
PAVEL_DATA              3

So, both tablespaces are created in DB$ROOT (id=1) and in pluggable datavase (id>2). But still, when connected to the pluggable database, I am getting the same error. It must be something stupid, but I am blind now...


texts: IntelliSense is not woking SQL Server 2017

texts:
I am using SQL Server 2017 and I had already installed Visual Studio 2019 before installing SQL Server. Sometimes when I create the first table in a database it gives me IntelliSense for that first table only and not for other tables I create later on and seomtimes it does not detect my tables and underline with a red line in my query.

I have tried the solution provided here (IntelliSense not working but it is enabled) with settings and clearing cache but it didn't work for me. The link was provided for a Microsoft solution which I did not try because it is for SQL Server 2008 .
please give me a solution


texts: I am using 5.5.41-MariaDB and ENGINE is MyISAM on CentOS 7. Please help me for Incremental backup command of mariaDB mysql on CentOS 7.


texts: How to create a Unique (Index) constraint across multiple tables?

texts:
Is there a way to create a unique index across tables in a SQL Server database?
I have two tables, Table A and Table B. In that I have a Column named ID, I want to make these Id columns as unique (combining Table A and B's Ids )

How can I do this ?


texts: How to see the value of a variable being used in an sql statement during a server trace?

texts:
I have a stored procedure which has SQL statements that take variables. For example,

update customer set cust_id = @newcust_id where issuedate < @transferdate and cust_id = @oldcust_id

The values of the variables are assigned earlier in the SP.

How do I show the values of these variables during a server trace?


texts:
First let me start off by saying I am in no way a DB expert. I know enough to be dangerous, but I am primarily a c# developer. I have a query off a view that is slow, but I am having problems figuring out the root cause. I have attached the query plan, hoping I can get some insight here.

https://www.brentozar.com/pastetheplan/?id=H1GT43J8e

Here is the view info as well:

CREATE VIEW [dbo].[ReSyncAdwords]
WITH schemabinding
AS
  SELECT dbo.currentadwordsmaxbid.maxcpc - dervtable.Bid AS Expr1,
         dbo.currentadwordsmaxbid.webid,
         dbo.currentadwordsmaxbid.maxcpc,
         dbo.currentadwordsmaxbid.adgroupid,
         dbo.currentadwordsmaxbid.keywordid,
         dervtable.SKU,
         dervtable.Bid,
         dbo.currentadwordsmaxbid.campaginid
  FROM   dbo.currentadwordsmaxbid
         LEFT OUTER JOIN dbo.WebSkuLookup
                      ON dbo.currentadwordsmaxbid.webid = dbo.WebSkuLookup.webid
         LEFT OUTER JOIN (SELECT SKU,
                                 Bid,
                                 Channel,
                                 DTStamp,
                                 campaginid
                          FROM   dbo.BidHistory AS ph1
                          WHERE  ( DTStamp = (SELECT MAX(DTStamp) AS Expr1
                                              FROM   dbo.BidHistory AS ph2
                                              WHERE  ( campaginid = ph1.campaginid )
                                                     AND ( ph1.SKU = SKU )) )) AS dervtable
                      ON dbo.WebSkuLookup.sku = dervtable.SKU
                         AND dbo.currentadwordsmaxbid.campaginid = dervtable.campaginid
  WHERE  ( dbo.currentadwordsmaxbid.maxcpc - dervtable.Bid <> 0 )

GO

Thanks.


texts: is there a DB client which supports oracle editions?

texts:
We are using a clever feature or oracle 11gr2 called edition-based redefinitions.

This means each stored proc and view looks different depending on the edition you set in the session.  There are multiple versions of each of these objects.

I'm trying to find a DB client (for browsing data and running SQL) which supports this properly - ie can show me visually the different editions, etc.

DBVisualizer doesn't seem to do this.  Here's an example:

my program code (which explicitly sets an edition) says:
ORA-04063: package body "MATT2.ER_BATCH_SUB_PKG" has errors

But when looking in dbvisualizer to the 'invalid objects' view, I see nothing.


texts: How much (estimated percentage) is going to worsen the response-time of the database if the logging level is lowered to "DEBUG". ?


texts: Can only execute mysqladmin as root account created with unix socket, all other accounts fail

texts:
I have a new install of MariaDB 10.2.27 on RHEL7. Our security scans have flagged the root account, so we have modified the name to orgdba, assigning all of the applicable privileges. I have not encountered any issues, except with mysqladmin. I know I have my password correct as I can connect via mysql using it. It is only erroring on mysqladmin. I ended up creating a root account specifically to execute mysqladmin commands. I cannot execute with that account either. It is not until I create the root account using the Unix Socket plugin that I can successfully execute mysqladmin commands. I cannot find anything in any documentation that expands on this topic. I have no idea if this is a bug in the version of MariaDB that I am using. Does anyone have any insight?
Error connecting to mysqladmin:
root@db01 $ mysqladmin -uorgdba -p status
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'orgdba'@'localhost' (using password: YES)'

root@db01 $ mysqladmin -u orgdba -p status
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'orgdba'@'localhost' (using password: YES)'

Same user/same password, successfully connects to mysql:
root@db01 $ mysql -u orgdba -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 52347
Server version: 10.2.27-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Any insight on this, or additional documentation on mysqladmin, would be appreciated.


texts: How to "insert into select from" skipping exceptions

texts:
1st post on SE/DBA; i am actually more of a DB developer but did not find a better suited forum, let me know if I picked the wrong one. I am new to PG but know Oracle.

I will need to report on JSON data, which is currently held in TEXT columns on Postgres 9.5. I plan to copy this data to proper JSONB fields so i can use the JSON parsing and SQL extensions of PG. Thus something akin to:

insert into T( id, jblob )
select id, jblob::jsonb from S;

Now this will fail if some of the existing rows are not valid JSON.
In Oracle I would either

insert into T select from S LOG ERRORS REJECT LIMIT UNLIMITED

which loads all it can and provides a log of failed rows to be reviewed (my 1st choice)

iterate over a  cursor and log errors myself in an exception handler

which is more work and usually slower

What is the best/most idiomatic way of achieving this with PostgreSQL (even in general, not necessarily for JSON content) ?

I tried "on conflict do nothing" but either used it wrongly or it is only meant to avoid duplicates and still raises an error in my case (with failed JSON casts).